Auxiliary Standard Operating Procedures

SUBJECT: Balance Sheet Backup for Year-End
SOURCE: Auxiliary Accounting, Office of the Treasurer
ORIGINAL DATE OF ISSUE: September 2007
DATE OF LAST REVISION: May 2012
ASOP NO: 44.0
RATIONALE: To outline the requirements of auxiliary organizations for balance sheet backup submitted to Auxiliary Accounting as elements of the year-end closing.
ASOP: As part of the fiscal year-end Auxiliary Accounting requires that all nonsystem-generated balance sheet amounts be supported by detailed documentation at the object code level. Documentation for backup is essentially the unit’s detailed work papers for a given object code. Submission of the KFS document for an Auxiliary Voucher (AV) is not sufficient backup. The listing and supporting documentation should equal balances on the balance sheet; if not, any discrepancies must be explained. If estimates are the supporting detail, then please provide the criteria and methodology used.

Organizations that carry inventory must advise the consultant as to the date of the last physical count.

Supporting detail for all balance sheet object codes must be received by the unit’s auxiliary consultant by July 31st to close the ended fiscal year and begin the next fiscal year effectively. However, certain campuses have their own backup submission guidelines to follow; i.e. Bloomington campus is required to submit backup documentation to their campus administrator before July 15th. Then the Bloomington campus administrator will send it to Auxiliary Accounting by the appropriate deadline. Verify submission guidelines with your campus administrator. For more information on the Year-End Closing Process, see ASOP 16.

Please see Appendix A for examples of acceptable supporting detail.

If adjustments have not been made by the organization, then documentation for the following object codes should not be included as they are “system-generated”.  Also, documentation for Fund Balance items should not be included.

Backup documentation must be provided for all object codes NOT listed below.

System-Generated Object codes – EXEMPT FROM BACKUP DOCUMENTATION

8000 – Cash
8118 – KFS Receivable
8160, 8163 – Bursar Accounts Receivable
86**, 89** - Plant Level Note: Backup for Accounts receivable and inventory allowances still required
9041 – Invoices Payable—EPIC
9050 – Accrued Payroll
9056 – Accrued Vacation Liability
9058 – Accrued Sick Liability
9060, 9065 – Bursar Payables
9120 – Notes Payable-Lease Purchase
92** – Bond Payable Level
96** – Notes Payable-Capital Lease
98** - Fund Balances

Your auxiliary consultant may be contacted with further questions regarding balance sheet backup.

CROSS REFERENCE: ASOP 16.0 - Year-End Closing Process
Who is my auxiliary consultant?
RESPONSIBLE ORGANIZATION: Auxiliary Accounting Department

APPENDIX A
EXAMPLES OF ACCEPTABLE DETAIL

The below examples are for illustration purposes only. Each organization may have different accounting processes that may change the way the backup is presented. For help on tailoring your backup to your organization’s unique circumstances, please contact your auxiliary consultant.

CASH IN TRANSIT

8005 Balance as of June 30th:  $3,800

Unapproved CRs for Daily Sales:

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

Accounts receivable backup should always provide a listing of the invoices that comprise the receivable balance. The invoices should include invoice number, customer name or number, invoice date and amount. An aging schedule (below) is not required, but is preferred.

8119 Balance as of June 30th:  $50,000

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
Total
$50,000   $16,750 $9,750 $6,100 $4,400 $13,000 $50,000

ACCOUNTS RECEIVABLE - KFS SYSTEM SHOWING UNBILLED ACTIVITY

8119 Balance as of June 30th:  $25,000

Customer Name Amount Date of Service
Ball $9,000 20-Jun
Burns $4,500 25-Jun
Freeman $6,000 26-Jun
Short $5,500 28-Jun
Total
$25,000  

ALLOWANCE FOR BAD DEBT

8900 Balance as of June 30th:  ($4,368.40)

Allowance % calculation (3 year average of bad debt %):

  2009 2010 2011
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%

Total credit sales for current fiscal year $268,000
Average Write Off %: 1.63%
Total estimated bad debt allowance $4,368.40

INVENTORY


Please provide the last page showing totals from subsystem.
Entire report should be available upon request.

8300 Balance as of June 30th:  $2,934.51

Valuation Date:  6/30/20XX

Part Number Part Description Quantity 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

8955 Balance as of June 30th: ($401.15)

Allowance % calculation (3 year average of inventory shrinkage %):

  2009 2010 2011
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%

Average inventory shrinkage/obsolescence: 13.67%

Inventory value (Support required as above) $2,934.51
Estimated allowance % 13.67%
Total estimated inventory allowance $401.15

PREPAID EXPENSE

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)

The table below supports a June 30, 2007 prepaid expense balance of $30,000.

  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: 1-Jan-07  
Months remaining on contract 30 (as of June 30, 2007)
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, 2008)
Prepaid expense balance $18,000 (months remaining * 1,000)

ACCOUNTS PAYABLE (excluding 9041)

Accounts payable backup should always provide a listing of the invoices to be paid that comprise the payable balance.

9000 Balance as of June 30th:  $35,730

Vendor 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

9118 Balance as of June 30th:  $11,500

Customer Name Deposit Amount
Adams $1,500
Cummings $3,000
Newton $2,500
Smith $2,500
Williams $2,000
Total $11,500

DEFERRED INCOME

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
Earn $10,000/week ($140,000 / 14 weeks)

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.