Balance Sheet Substantiation

Prerequisites

Prior to reading the standard Balance Sheet Substantiation, it is beneficial to review the below sections to gain foundational information:

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 .

Illustration of a balance sheet and the different supporting tabs

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:
1. 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.
2. 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.
3. 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.
4. 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.