UAFR - Annual Year-End Fact Sheets - Accounts Receivable Reference Tool
This reference tool provides definitions, examples, and explanations which will hopefully be helpful when completing the Accounts Receivable worksheet within the Fact Sheet Excel Attachment. If you have any questions as you read through this document, feel free to reach out to the applicable subject matter experts within University Accounting and Financial Reporting (UAFR) located in the Fact Sheets section of UAFR’s Who To Ask list.
Accounts Receivable – Definition
Accounts receivable are general ledger assets used to record sales made on credit to external customers (not other university units) for goods or services for which the customer has not yet paid as of June 30.
NOTE: Accounts receivable on self-supporting funds are required to be reported through Banner AR unless the unit has an approved Banner AR exemption. Accounts receivable should only be listed on the Fact Sheet Excel Attachment if the unit: (1) has an approved Banner AR exemption, or (2) typically reports their receivables within Banner AR, but has missed the Account Receivable critical cutoff deadline. See below for policies related to this topic:
Examples of What Should and Should Not Be Reported as Accounts Receivable
The following are some common examples of transactions that should be recorded as accounts receivable on the Year-End Fact Sheet:
- Amounts owed to the university for the sale and delivery of goods (e.g., t-shirts, publications, etc.) to an external customer on or before June 30.
- Amounts owed to the university for the sale and provision of services (e.g., testing services, consulting services, etc.) to an external customer on or before June 30.
- Credit memos, rebates, and/or refunds owed to the university from vendors that we no longer expect to make purchases from in the future.
It is also important to be aware of common examples of transactions that should NOT be recorded as a year-end receivable, as these have been erroneously reported as accounts receivable in the past. See below for examples of transactions which should NOT be reported as accounts receivable:
- Deposits in transit as of 6/30 should NOT be reported as accounts receivable on the Year- End Fact Sheets. Deposits in transit account for situations where the university has received payment from the customer as of 6/30 but has not yet made the appropriate deposit by the year-end deposit deadline. UAFR is responsible for making the appropriate year-end accounting adjustments for these deposits in transit. However, these deposits in transit should NOT be reported as an accounts receivable on the Fact Sheets, as the customer has paid us as of 6/30 (even though we have not deposited the payment). Thus, the customer does not owe us for that amount as of 6/30 and it is not a receivable.
- Receivables from other university units should NOT be reported as accounts receivable on the Year-End Fact Sheets. In order to prevent overstating revenue on the university’s financial statements, we do not record receivables from other university departments, and we only record receivables from customers and individuals external to the university (such as students, external customers, employees who owe us money, etc.)
- Receivables which have already been recorded to the applicable 53nnn receivable account codes in Banner as of 6/30 should NOT be reported as accounts receivable on the Year- End Fact Sheets. For example, units who use the Banner AR system to record their accounts receivable should NOT be reporting their receivables again on the Year-End Fact Sheets, unless they missed the Banner AR critical cutoff deadline (calendar date 6/30). However, if the unit did not miss that deadline, then the receivable has already been recorded in Banner and thus we do not want those to be reported again on the Year-End Fact Sheet, as that would create a duplication of the Banner AR entry.
- Credit memos, rebates, and/or refunds owed to the university from vendors that we fully expect to make purchases from in the future should NOT be reported as an accounts receivable. Instead, these should be reported as negative payable on the Accounts Payable tab of the Fact Sheet Excel Attachment to ensure that they can be properly offset with other payables or future purchases from these vendors.
Uncollectible Accounts Receivable - Definition
Uncollectible accounts receivable (also known as an allowance for doubtful accounts) are general ledger contra assets which offset total accounts receivable. They are used to record receivables that are unlikely to be collected due to a variety of reasons (such as a customer who is unable or unwilling to pay).
Keep in mind that the uncollectible accounts receivable should continue to be reported in the accounts receivable totals as well, as they have not yet been written off. For example, if a unit has a total of $1,500 of accounts receivable, where $500 of that receivable total is considered to be uncollectible, then that unit should report a total receivable of $1,500 along with an uncollectible receivable of $500. That way, the total receivable balance along with the related contra asset can all be properly recorded to Banner for full transparency.
Also keep in mind that uncollectible accounts receivable cannot be written off until you work with University Bursar and UAFR to ensure the write-off follows the necessary State of Illinois protocols.
Example of Uncollectible Accounts Receivable:
The following are some common examples of transactions that could be recorded as an uncollectible accounts receivable:
- Amounts owed to the university where no payments have been received for one year or more, and where future payments appear to be unlikely.
- Amounts owed the university by a customer who has recently come onto a financial hardship, and where future payments appear to be unlikely.
Completing Your Fact Sheet
Now that you have had an overview of the various definitions and examples which apply to this area, we are now ready to complete the Fact Sheet Excel Attachment. However, before we start to go through these required fields, please keep in mind the tips below:
- Make sure you are using the most recent version of the Fact Sheet Excel Attachment, which can be downloaded the following the Year-End Fact Sheets webpage.
- When copying data over from another spreadsheet, paste the information into the Fact Sheet Excel Attachment using the Paste Values option. This will help ensure the formatting and formulas on the Fact Sheet Excel Attachment are not altered.
Step 1: C-FOAPAL Information
Enter the complete C-FOAPAL in Columns A through G. This should be the same C-FOAPAL that you will use to deposit the funds whenever you eventually receive payment from the related customer.
- The full C-FOAP (i.e., the chart, fund, organization, account, and program code) is required to complete the year-end adjusting entries to Banner. However, the activity code and location code are optional.
- Enter an account code which accurately reflects the activity of the transaction generating the receivable. For example, if you sold merchandise to your customer, then we would typically expect to see an appropriate revenue account code (such as 304200, “Merchandise and Commodity Sales Revenue”) listed on the Fact Sheet Excel Attachment to accurately reflect the nature of that transaction. If you need assistance in finding an appropriate Account code to use, refer to the Account Code Search application located in the Banner Account Codes webpage.
- See the table below for an example of what we would expect to see listed for a full C- FOAP. As you can see in these examples below, each field is properly completed, and an appropriate revenue account code is listed on each line to accurately represent the nature of the transaction. In this example below, the first line item was for the sale of university publications (305200, “University-Produced Publication Sales Revenue”), the second line item was for the sale of conference registration fees (305300, “Seminar and Conference Revenue”), and the third line item was for the sale of training services (307820, “Training and Education Services Revenue”):
Chart | Fund | Organization | Account | Program | Activity (if applicable) |
Location (if applicable) |
---|---|---|---|---|---|---|
9 | 303000 | 123000 | 305200 | 123001 | ||
9 | 303000 | 123000 | 305300 | 123001 | ||
9 | 303000 | 123000 | 307820 | 123001 |
Step 2: Reason for Reporting Receivables on Fact Sheet Instead of Banner AR
Select from the drop-down menu in Column H the reason the receivables are being reported on the Fact Sheet instead of through Banner AR. Banner AR is the University of Illinois System’s centralized billing and collections system. Per Section 5.1 and Section 5.2.1 of Business and Financial Policies and Procedures, all units must record their receivables in the Banner AR system unless an approved exemption has been granted.
The three drop-down menu options are:
- Exempt from Banner AR: This would be selected for the units who have unique circumstances (such as healthcare providers) where they received an exemption from using Banner AR.
- Missed 06/30 Banner AR Deadline: This would be selected for the units who use Banner AR, but missed the 06/30 deadline for reporting their receivables; and,
- Do Not Use Banner AR and No Exemption: This would be selected for the units who do not use Banner AR and who have not received an exemption from using Banner AR.
Step 3: Customer Name and Description of Customer Base
Enter the customer’s name in Column I. Or, if there are multiple customers of the same type (such as conference attendees) that would be too difficult to list individually given the volume, then at least provide a general description of that customer base.
Also, list only one customer (or customer type) per line, and do not group different customers or different customer bases together on the same line. We need proper separation to ensure clear and transparent adjusting entries within Banner.
In Column J, enter whether the customer is a State of Illinois agency by selecting YES or NO from the drop-down menu. This is required, as we need to report any receivables from other State of Illinois agencies to the Illinois Office of the Comptroller.
See the examples listed in the table below for an illustration of some common scenarios that we would typically see within these columns of the Fact Sheet Excel Attachment:
Customer Name or Description of Customer Base | Is this Customer a State of Illinois Agency? Select YES or NO from the drop-down menu in the rows below. |
---|---|
ABC Company | NO |
Various Conference Attendees | NO |
Illinois Department of Corrections | YES |
Step 4: Gross Receivable Amount
In Column K, enter the gross receivable amount. This is the total receivable amount invoiced to the external customer for which they have not yet paid as of 6/30. For example, if you invoiced a customer on May 31 for $500 and they have only paid you $200 as of June 30, then your gross receivable would be $300 ($500 - $200 = $300).
Also, keep in mind that even if the receivable relates to a service that you provided which crosses fiscal years, you should still report the total amount due from the customer in Column J. For example, if your customer owed you $1,500 for a service your unit provided from June 25 – July 5, then you would still list the entire $1,500 in Column K, even if a portion of that amount relates to services which were provided from July 1 – July 5. The Fact Sheet Excel Attachment is smart enough to be able to calculate the portion of the receivable which relates to the fiscal year that is closing based on embedded formulas present within the spreadsheet. So, do not worry about calculating this yourself – simply enter in the gross receivable and let the spreadsheet do the rest!
See below for an example of what we are discussing above:
Gross Receivable Amount |
---|
$300.00 |
$1,500.00 |
Step 5: Description of the Receivable
Enter a clear and transparent description of the receivable in Column L to accurately describe what was sold to the external customer. A clear description will help enable us to make a clear and transparent adjusting entry within Banner.
See the examples in the table below for some common descriptions that would be acceptable:
Description of the Receivable |
---|
Sale of Training Manuals |
Conference Registration Fees |
Sale of Consulting Services |
Step 6: Start Date and End Date of Service Period Covered by the Sale (or the Date the Goods Were Sold)
Enter the start date and end date of the service period covered by the sale in Columns M and N. Or, if you were selling goods instead, then enter the date that the goods were sold in both columns. See below for some additional tips:
- The service dates should begin on or prior to June 30 of the fiscal year which we are closing. If the start date was any time after June 30, then the transaction should not be listed as a receivable as that sale would relate to the subsequent fiscal year.
- If the receivable relates to the sale of goods, then the start and end date would typically be the same date (i.e., the date that the sale was completed where the customer took possession and ownership of the goods).
See the table below for some common examples, where the first line item is for the sale of services crossing a date range spanning fiscal years, and where the second line item is for the sale of goods where the customer took ownership on the date of the sale:
Start Date of Service Period Covered by the Sale or Date the Goods Were Sold | End Date of Service Period Covered by the Sale or Date the Goods Were Sold |
---|---|
6/25/20YY | 7/5/20YY |
6/29/20YY | 6/29/20YY |
Step 7: Uncollectible Accounts Receivable
In Column O, enter either YES or NO from the drop-down menu to indicate whether or not full payment is expected to be received from your customer(s). This question is to help determine whether an Allowance for Doubtful Accounts adjusting entry needs to be recorded in Banner for any receivables which are expected to be uncollectible. See below for additional guidance:
- If NO is selected, then enter the estimated uncollectible amount in Column P.
- Any amount entered into Column P will be recorded as an Allowance for Doubtful Accounts in Banner as of 6/30. This adjusting entry will record a contra asset on the general ledger and a bad debt expense on the operating ledger.
- If this does not apply to your receivables (i.e., you expect to receive full payment from your customers), then simply leave this field blank.
For example, consider a situation where your unit provides professional services to an external company, but that company goes bankrupt before they are able to pay you the full $10,000 that they owe. However, you feel that they may be able to pay at least $1,000. So, if that were the case, you would enter in the full $10,000 as your Gross Receivable amount, and you would factor in the $9,000 uncollectible amount as shown in the table below:
Do You Anticipate that the Customer Will Pay You in Full for the Amount They Owe? Select YES or NO from the drop-down menu in the rows below. |
If You Answered "NO" to the Previous Question, What is the Estimated Amount Which Could Go Unpaid? |
---|---|
NO | $9,000.00 |
Step 8: Review the Calculated Amounts in Columns Q - X
Once you have completed all the necessary columns on this spreadsheet, your final step is to review the amounts which the spreadsheet has calculated in the areas with yellow shading within Columns Q through X. These are the calculated amounts which UAFR will be using to post the final adjusting entries to Banner. It is helpful to review these to make yourself familiar with the amounts that will be recorded to your fund in Banner, as well as to make sure the amounts do not greatly differ from what you were expecting to see.
Step 9: Save and Upload the Fact Sheet Excel Attachment to the Fact Sheet Application
The final step in this process is to save your updated Fact Sheet Excel Attachment (e.g., to your desktop or shared drive) and to then upload the completed Fact Sheet Excel Attachment to the Fact Sheet Application.
Also, before officially uploading your spreadsheet, be sure to review the other tabs of the Fact Sheet Excel Attachment to see if you have any other accruals, deferrals, or inventory for resale to report as well.
Then, once you have confirmed that all items have been properly added to your Fact Sheet Excel Attachment, you would then need to upload the updated spreadsheet to the applicable fund’s Fact Sheet within the Fact Sheet application and then submit to UAFR for review.
Note that UAFR commonly has questions, so also make sure that you are available for any subsequent questions that may come from UAFR.