Prerequisites
Prior to reading the standard Balance Sheet Substantiation, it is beneficial to review the below sections to gain foundational information:
- Accounting Fundamentals Section
- Chart of Accounts and General Ledger Section
- Financial Statements Section
Preface
This section discusses the elements of the balance sheet substantiation document and how it is used internally within Indiana University. Information presented below will walk through what balance sheet substantiation is, how to complete this closing procedure, and requirements and best practices related to this process. For further information on how to pull the balance sheet report to conduct a balance sheet substantiation, refer to the Financial Statement Reports instructions.
Introduction
The balance sheet substantiation is key during quarter and year-end close as it helps the responsibility center (RC), campus, and IU ensure the accuracy of their financial statements. Balance sheet substantiation within IU, refers to detailed documentation or work papers substantiating the non-system generated object codes balance sheet amounts. Non-system generated object codes are object codes manually added by a user and are not part of the initial system generated object codes. The supporting documentation or substantiation should be detailed enough that a person without extensive knowledge of the entity can review the support, understand the nature of the balance, and tie it to the amounts on the balance sheet. To ensure accuracy of balances, compliance with all local, state and federal requirements, auditors request substantiation showing how balances on the balance sheet are derived. In order to ensure all fiscal officers are familiar with the requirements for balance sheet substantiation, examples of appropriate support to provide are discussed below.
Importance and Impact of Balance Sheet Substantiation
A balance sheet provides a snapshot of an entity’s financial position or health at a given time. As a result, IU strives to be as accurate as possible in its financial reporting. To help increase accuracy, the Office of the University Controller (UCO) requires all non-system generated balance sheet amounts above a threshold (as noted in the closing checklist) be supported by detailed documentation at the object code level. System generated object codes are not included because they have system integrated internal controls in place and UCO has the ability to access the sub-ledgers to substantiate their balances. Supporting documentation for system generated object codes is only required when manual adjustments have been made by the entity. By providing this information, executive management is able to provide greater assurance that the financial statements are accurate, improve transparency, and help to reduce risk.
In addition, having the substantiation readily available assists in the audit both internally and externally. Balance sheet substantiation is a crucial internal control in financial reporting which is heavily reviewed in the audit process. Without substantiation, auditors are unable to verify balance sheet balances which could lead to a misrepresentation of the balance sheet and audit findings with major negative impacts. This may lead to issues concerning internal controls or the integrity of the financial statements which impacts future funding from government organizations, creditors, or individual donors.
How to Perform a Balance Sheet Substantiation
All university entities with accounts that have non-system generated asset and liability balances must be able to provide substantiation for the balances on their balance sheet. It is encouraged that entities complete balance sheet substantiation within five business days after the close of each fiscal quarter.
Instructions to Substantiate Balances
- Run the entity balance sheet for the required period. For instructions on how to run the balance sheet, please see Financial Statement Reports instructions.
- Highlight all non-system generated object codes. Please refer to the list of system generated object codes below to determine which object codes do not need to be substantiated.
List of System Generated Object Codes:
| Object Code | Object Code Name |
|---|---|
| 8000 | Cash |
| 8116 | Payroll Deduction/Benefits Internal Receivable |
| 8118 | Accounts Receivable – Non-Student |
| 8160, 8163 | Bursar Accounts Receivable |
| 86--, 89-- | Plant Level Note: Substantiation for 8900 and 8950 (allowances) still required |
| 9045 | Invoices Payable |
| 9050 | Accrued Payroll |
| 9056 | Accrued Vacation Liability |
| 9058 | Accrued Sick Liability |
| 9060, 9065 | Bursar Payables |
| 9120 | Notes Payable-Lease Purchase |
| 92-- | Bond Payable Level |
| 9465 | Bursar-Deferred Income |
| 96-- | Notes Payable-Capital Lease |
| 98-- | Fund Balances |
A balance sheet substantiation template has been made available for users to reference and use during this process. The balance sheet substantiation template breaks out the most common non-system generated balances that need substantiation. Included in this template are examples of substantiation that the UCO deems acceptable for the most common non-system generated balances. This substantiation support should be placed in the same Excel file as the balance sheet as shown in the example below such that all information is kept in one place.
Acceptable Substantiation
For reference of what is considered sufficient/appropriate substantiation and what is poor substantiation, please see the table below:
| Examples of Sufficient Balance Sheet Substantiation | Examples of Insufficient/Poor Balance Sheet Substantiation |
|---|---|
| Actual allowance calculations | KFS Screenshots |
| Supplier invoices or statements | Payroll Deduction/Benefits Internal Receivable |
| Inventory counts | Post-it® Notes |
| Aging Reports | Calculations in PDF format |
| Sub-ledger transactions |
Below is an example of the Excel document containing the organization’s balance sheet at year-end and the subsequent tabs housing substantiation documents for the non-system generated object codes .
Campuses may have additional requirements and submission deadlines in place. Verify submission guidelines with the specific campus office and fiscal officer.
In addition to the template available in the Reference Materials Library, this section will present examples of the most common non-system generated balances that need substantiation along with the UCO approved substantiation, calculations and additional support for those balances. For any additional questions, contact the campus office or the Accounting and Reporting Services team at uars@iu.edu.
Cash In Transit
8005 Balance as of June 30th: $3,800
UCO recommended substantiation:
An Excel spreadsheet with columns indicating the date, cash sales, credit card sales, and total (as shown below) is the recommended substantiation for Cash in Transit. The total should account for the object code balance.
| Date | Cash Sales | Credit Card Sales | Total |
| June 28 | $800 | $400 | $1,200 |
| June 29 | $750 | $800 | $1,550 |
| June 30 | $550 | $500 | $1,050 |
| Total | $2,100 | $1,700 | $3,800 |
Accounts Receivable – Non-KFS Subsystem
Object Code 8119 Balance as of June 30th: $50,000.00
UCO recommended substantiation:
Accounts receivable substantiation should always provide a list of the invoices that comprise the receivable balance. The invoices list should include the customer’s name or number, invoice number, date, and amount. An aging report should be completed for the entity’s largest accounts receivable object codes and cover at least 75% of the balance (see example below).
| Customer Name | Amount | Invoice # | Current | 1-30 days | 31-60 days | 61-90 days | Over 91 days | Total |
| Jones | $15,000 | AB123 | $1,000 | $2,000 | $4,000 | $1,000 | $7,000 | $15,000 |
| Smith | $2,000 | CC899 | $500 | $1,500 | $2,000 | |||
| Brown | $13,000 | FB778 | $10,000 | $1,100 | $1,900 | $13,000 | ||
| Williams | $5,000 | OU156 | $250 | $4,750 | $5,000 | |||
| Green | $15,000 | SC462 | $5,000 | $3,000 | $1,000 | $6,000 | $15,000 | |
| $50,000 | $16,750 | $9,750 | $6,100 | $4,400 | $13,000 | $50,000 |
Accounts Receivable – KFS System Showing Unbilled Activity
Object Code 8122 Balance as of June 30th: $25,000.00
UCO recommended substantiation:
An Excel spreadsheet or listing indicating the customer name, amount and date of service as highlighted below. These amounts can be pulled from the entity’s subsidiary detail ledger. The total should account for the object code balance.
| Customer Name | Amount | Date of Service |
| Ball | $9,000 | June 20 |
| Burns | $4,500 | June 25 |
| Freeman | $6,000 | June 26 |
| Short | $5,500 | June 28 |
| $25,000 |
Allowance for Bad Debt
Object Code 8900 Balance as of June 30th: ($4,368.40)
UCO recommended substantiation:
Allowance for Bad Debt substantiation should always provide an Excel spreadsheet showing the Bad Debt Allowance percentage and Actual Write-off calculation. For the balance ending June 30th, the current year write-off percentage would be calculated by finding the average of the prior 3 years write-off percentages as shown below.
| 20xx | 20x1 | 20x2 | |
|---|---|---|---|
| Actual write-offs | $3,870 | $5,125 | $3,469 |
| Total annual revenue | $240,000 | $259,500 | $262,700 |
| Write-off % | 1.61% | 1.97% | 1.32% |
Average write off %: 1.63%
To get the dollar amount, multiply the new write-off percentage by the total credit sales for the current fiscal year.
| Total credit sales for current fiscal year | $268,000 |
| Average Write Off %: | 1.63% |
| Total estimated bad debt allowance | $4,368.40 |
Total credit sales for current fiscal year x average write-off= Total estimated bad debt allowance ($268,000 x 1.63% = $4,368.40)
Inventory
Object Code 8300 Balance as of June 30th: $3,264.21
UCO recommended substantiation:
Organizations that carry inventory must state in their support the date of the last physical count. When an inventory count is conducted, an inventory schedule listing the part number (if available), item description, units on hand, average cost per unit and total value should be submitted. Otherwise, please provide the last page of the inventory report showing totals from the sub-system. Entire inventory reports should be available upon request. Below is an example of an inventory physical count spreadsheet.
| Valuation Date: | 06/30/20XX | |||
| Part Number | Part Description | On Hand | Avg. Cost | Value |
| D0172630 | Item 1 | 0.00 | $10.00 | $329.70 |
| D0101000 | Item 2 | 1.00 | 10.39 | 10.39 |
| D0110000 | Item 3 | 0.00 | 115.00 | 0.00 |
| D0110003 | Item 4 | 7.00 | 6.38 | 44.66 |
| D0110004 | Item 5 | 143.00 | 7.60 | 1,086.80 |
| D0113200 | Item 6 | 37.00 | 2.23 | 82.51 |
| D0115000 | Item 7 | 255.00 | 1.95 | 497.25 |
| D0115001 | Item 8 | 3.00 | 1.95 | 5.85 |
| D0115002 | Item 9 | 332.00 | 1.95 | 647.40 |
| D0115003 | Item 10 | 287.00 | 1.95 | 559.65 |
| Inventory value per physical count 6/30/XX | $3,264.21 |
Allowance for Inventory
Object Code 8955 Balance as of June 30th: ($401.15)
UCO recommended substantiation:
Allowance for Inventory substantiation should always provide an excel spreadsheet indicating the Actual write-off calculation. For the balance ending June 30th 20x3 of $401.15, the current year write-off percentage would be calculated by finding the average of the prior 3 years write-off percentages as shown below.
Allowance % calculation (3 year average of inventory shrinkage %):
| 20XX | 20X1 | 20X2 | |
| Actual write-offs | $570 | $460 | $250 |
| Total inventory value at physical count | $3,150 | $3,820 | $2,300 |
| Inventory shrinkage /obsolescence % | 18.10% | 12.04% | 10.87% |
To get the dollar amount, multiply the new average inventory shrinkage/obsolescence: 13.67% by the total Inventory value for the current fiscal year as below.
| Inventory value | $2,934.51 |
| Estimated allowance % | 13.67% |
| Total estimated inventory allowance | $401.15 |
Prepaid Expense
Object Code 8400 Balance as of June 30th: $30,000
Assumptions: Purchased 3 year equipment maintenance contract Original cost: $36,000 Purchased January 1, 2007 Expense $1,000/month ($36,000 / 3 years / 12 months)
UCO recommended substantiation:
UCO recommends providing an Excel spreadsheet detailing the calculations relating to the prepaid expense. For the example above, the entity has a prepaid maintenance contract of $36,000 for 36 months. Monthly expense is therefore $1,000/month ($36,000 / 36 weeks).
*Note: Contracts/invoices should be kept by the entity and made available upon request. They are not required for submission.
The table below supports a June 30, 20XX prepaid expense balance of $30,000. The table shows the monthly expenditure and prepaid balance for each month.
| January | February | March | April | May | June | |
| Expense | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 |
| Prepaid Expense: | ||||||
| Beginning Balance | $36,000 | $35,000 | $34,000 | $33,000 | $32,000 | $31,000 |
| Less: expense incurred | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 |
| Ending Prepaid Expense Balance | $35,000 | $34,000 | $33,000 | $32,000 | $31,000 | $30,000 |
Example of calculation:
| Purchase price: | $36,000 | |
| Contract period in months: | 36 | |
| Contract start date: | Jan 1, 20XX | |
| Months remaining on contract | 30 | (as of June 30, 20XX) |
| Prepaid expense balance | $30,000 | (months remaining * 1,000) |
The following year the calculation would be as follows:
| Months remaining on contract | 18 | (as of June 30, 20X1) |
| Prepaid expense balance | $18,000 | (months remaining * 1,000) |
Accounts Payable (excluding 9041 & 9045)
Object Code 9000 Balance as of June 30th: $35,730
UCO recommended substantiation:
Accounts payable substantiation should provide a list of outstanding invoices that make up the payable balance. This can be done in Excel and should include supplier name, invoice date and amount. The invoices should be kept by the entity and made available upon request. They are not required for submission.
| Supplier_Name | Invoice_Date | Amount |
|---|---|---|
| ABC Corp | 6/09/XX | $11,500 |
| GFS | 5/22/XX | $16.230 |
| KLM Vending | 6/25/XX | $3,400 |
| Service LLC | 6/30/XX | $4,600 |
| Total | $35,730 |
Deposit Liability
Object Code 9118 Balance as of June 30th: $11,500
UCO recommended substantiation:
Deposit Liability supporting documentation should include a list of Customer Names and how much their deposit balance is, as shown below. These amounts can be pulled from the detail ledger and must be presented to UCO in an Excel spreadsheet.
It is the fiscal officer's responsibility to ensure these balances should not have been refunded, expired or escheated. Please provide us with the last reconciliation date to provide assurance these are monitored on a regular basis.
| Customer Name | Deposit Amount | |
| Adams | $1,500 | |
| Cummings | $3,000 | |
| Newton | $2,500 | |
| Smith | $2,500 | |
| Williams | $2,000 | |
| Total | $11,500 |
Deferred Income
Object Code 9400 Deferred Income Balance as of June 30th: $60,000
Assumptions: Organization receives a mandatory student fee for summer term Fee is earned and recognized over the entire term Summer term equals 14 weeks Total mandatory fee received in May: $140,000
UCO recommended substantiation:
UCO recommended Deferred Income supporting documentation includes an Excel spreadsheet detailing calculations related to the deferred income, if any. For the example above, the entity earns $10,000/week ($140,000 / 14 weeks).
*Note: Contracts/invoices should be available upon request, but are not required for submission.
The table below supports a June 30th deferred income balance of $60,000.
Income and deferred income are calculated as follows:
| May | June | July | August | Total | |
| (4 weeks) | (4 weeks) | (4 weeks) | (2 weeks) | ||
| Income (based on $10,000 week) | $40,000 | $40,000 | $40,000 | $20,000 | $140,000 |
Deferred Income Calculation:
| Beginning balance | $140,000 | $100,000 | $60,000 | $20,000 |
| Less: amount earned | $40,000 | $40,000 | $40,000 | $20,000 |
| Ending deferred income balance | $100,000 | $60,000 | $20,000 | $0 |
At the end of the summer term, deferred income balance is zero. Income of $140,000 was recognized over the four month period.
Requirements and Best Practices
This section outlines requirements related to the Closing Procedures – Balance Sheet Substantiation, as well as best practices. While not required, the best practices outlined below allow users to gain a better picture of the entity’s financial health and help identify potential issues on a more frequent basis. This allows organizations to identify errors, mistakes and pitfalls which can be remedied quickly and prevent larger issues in the future.
Requirements
- Run the balance sheet and perform a balance sheet substantiation review quarterly.
- Provide substantiation for any non-system generated asset and liability object code balances on your balance sheet. The minimum university threshold for all balance sheet balances requiring substantiation is noted in the Fiscal Year-End Closing Checklist.
- Ensure the accuracy, reliability, and completeness of the balance sheet substantiation. The (RC) fiscal officer is responsible for ensuring the balance sheet substantiation is correct.
Best Practices
- Run the balance sheet and perform a balance sheet substantiation review on a monthly basis. Please refer to the Financial Statement Reports instructions for more information on how to pull a balance sheet.
- Provide substantiation for any non-system generated asset and liability object code balances on your balance sheet. Ensure that the supporting documentation is detailed and can be traced to the balance. Explain any discrepancies.
- Review and analyze the substantiation documentation and working papers of the entity. This is the responsibility of the fiscal officer. Analyzing the balance sheet substantiation allows the fiscal officer to determine if the current internal controls are efficient and effective enough for accurate recording of transactions. The questions that need to be asked will vary depending on the needs; however, the following questions are some common examples:
- Were there any manual adjustments done to the system generated object codes? In general, system generated objects codes should not be manually adjusted; however, if any manual adjustments were done, the supporting documentation is required.
- Are all non-system generated object codes over threshold substantiated? Only the object codes listed above are system generated; any object code not on the list should have supporting documentation for the balance.
- Are the closing balances equal to the supporting documentation balances? All balance should equal each other. If any discrepancies between closing balances and supporting documentations are found, the fiscal officer should review to determine what is causing the difference and correct.
- Are there any controls the department may require to increase the accuracy of the non-system generated object codes? The department needs to evaluate if there are any internal document controls they may need to have in place to substantiate the balances better.