Annual Financial Report - File Layout and Data Mapping
Upload and Submission Guidelines
According to ISBE requirements, follow these guidelines when submitting the spreadsheet:
Do not remove, reinsert, or rename tabs. Such forms may be returned to you for corrections and, in extreme cases, need to be re-entered.
Do not copy/paste or cut/paste within the form. Cutting/copying/pasting will result in errors.
Do not reference other workbooks/leave links to other workbooks. This will likely cause a Reference Error and will not always show the appropriate amount(s). Such forms may be returned for corrections and, in extreme cases, need to be re-entered.
Only enter whole numbers in cells requesting numbers/amounts. Do not use decimals/cents.
Cells containing anything but numbers cannot be used in a calculation. Doing so will cause a Value Error and prevent the calculated total from showing up.
Cells containing decimals will need to be updated to remove decimals.
For more information, refer to the AFR Instructions on the ISBE website.
Spreadsheet Tabs
The following sections describe the tabs in the afr-23-form.xlsx spreadsheet:
Statement of Assets and Liabilities Arising from Cash Transactions
Estimated Operating Expenditures Per Pupil (OEPP) and Per Capita Tuition Charge (PCTC) Computation
Cover Page
In the spreadsheet, this tab is named COVER.
Select the school district from the drop-down list on Line 17. To help locate the district, refer to the School District Lookup Tool or School District Directory on the cover page.
Include all required signatures on the printed copy of the AFR cover page. If signatures are omitted, the AFR will be returned. Signatures are required for the school district superintendent/joint agreement administrator, regional superintendent, and township treasurer (in Cook County only). You must provide the township treasurer’s name and location.
The link to the new submission system is listed on the AFR cover page.
Filing Status - Submit electronic AFR directly to ISBE via IWAS -School District Financial Reports system (for auditor use only)
TOC
This is the Table of Contents tab. No entry is required.
Auditor’s Questionnaire
In the spreadsheet, this tab is named Aud Quest 2.
Check all statements that apply to this audit. Use the comments box for additional explanations.
The Audit Questionnaire Responses will be compiled for the Regional Offices of Education and sent to the appropriate regional superintendent the year following the audit.
The regional superintendent is responsible for following up with the districts, requesting an explanation, and verifying that the district has a Corrective Action Plan to correct the finding.
The district must submit the Corrective Action Plan in writing to resolve stated issues. The regional superintendent must then reply to ISBE with an explanation summarizing follow-up reviews with the district.
A signature is required on the Audit Questionnaire. You may embed a signed copy of the audit questionnaire on the Opinion-Notes tab.
Financial Profile Information
In the spreadsheet, this tab is named FP Info 3.
This page calculates the district’s Financial Profile score.
This page does not apply to joint agreement entities.
Section A
Enter the one-year prior Equalized Assessed Valuation and tax rates for the operating funds.
Enter the tax rates as six-digit (e.g., .015000 instead of 1.50).
Section B
Results of Operations do not require entries. This automatically calculates summaries of revenue and expenditures from pages 7-9. The Excess (Deficiency) amount indicates revenues are less than expenditures for the fiscal year. The fund balance is the accumulation of the dollars remaining as of June 30.
Section C
The short-term debt amount calculated on page 26. Any changes must be made to page 26.
Section D
The Long-Term Debt information is used to calculate the Debt Margin Remaining on the Financial Profile Score on page 4:
Debt Margin (debt allowed)
Debt Remaining
Debt Outstanding
Check the appropriate box to calculate the Long-Term Debt Allowances for Elementary and High School Districts (6.9%) or Unit Districts (13.8%). In some cases, the debt outstanding is greater than the debt allowed. Districts that go over the debt limit must explain and disclose how they can do so in the audited financial notes. This does not affect the profile score.
Section E
The Material Impact on Financial Position questions help determine if reasons exist that significantly impact the current and future profile score calculation. Check the applicable items.
Comments can be entered in the space provided at the bottom of the page concerning any items on page 3.
Estimated ISBE Financial Profile Score/Designation Summary
In the spreadsheet, this tab is named Fin Profile 4.
No entries are required on this page. This report provides the district with a preliminary Financial Profile score and designation. ISBE will generate the final score and designation.
For additional information about the Financial Profile Score/Designation, refer to the School District Financial Profile. Select Financial Profile Score Database, and then select Financial Profile: Calculations and Definitions of the Designations.
Statement of Assets and Liabilities Arising from Cash Transactions
In the spreadsheet, this tab is named Assets-Liab 5-6.
Entries are allowed in all cells except for the total lines highlighted in yellow or gray.
Accrual-only items are allowed if Accrual Basis of Accounting is selected on the cover page. If amounts are entered in these lines for Cash Basis of Accounting, an error message will appear on the AUDITCHECK page.
Assets
Each cell (columns C through K) is populated with the total from the summation of general ledger records where:
genledgr.yr = year entered on program screen
genledgr.fund = fund of specific column
genledgr.account = account of the specific row
Cell Total = sum of genledgr.gl_bal1 thru genledgr.gl_bal13
Any non-highlighted white cells are manual entry.
Liabilities
Each cell (columns C through K) will be populated with the total from the summation of general ledger records where:
genledgr.yr = year entered on program screen
genledgr.fund = fund of specific column
genledgr.account = account of the specific row
Cell Total = sum of genledgr.gl_bal1 thru genledgr.gl_bal13
Any non-highlighted white cells are manual entry.
Statement of Revenues Received/Revenues, Expenditures Disbursed/Expenditures, Other Financing Sources (Uses), and Change in Fund Balances (All Funds)
In the spreadsheet, this tab is named Acct Summary 7-9.
The Receipts/Revenues summary and Disbursements/Expenditures summary on page 7 are linked from the detailed pages for Receipts/Revenues (pages 10 through 15) and Expenditures/Disbursements (pages 16 through 24).
On Behalf of Payments amounts are entered for account (3998) and are linked automatically to account (4180). These are payments estimated to be paid by the government on behalf of the district employees. Some districts may be an exception to this rule.
Other Financing Sources (Uses), such as Source Accounts in the (7000) and Use Accounts in the (8000) categories, permit certain permanent transfers between funds. For more information, refer to Illinois School Code references for each transfer.
Accounts are included for Principal on Bonds Sold (7220), Premium on Bonds Sold (7230), Accrued Interest on Bonds Sold (7230), Sale or Compensation for Fixed Assets (7300), and ISBE Loan Proceeds (7900).
The Beginning and Ending Fund Balances are reported on Lines 79 and 81.
Rows for accounts 3998, 7110-7990, and 8110-8990 will be populated with the total from the summation of revenue ledger records where:
revledgr.yr = year entered on program screen
revledgr.key_orgn = orgn.key_orgn
orgn.fund = fund of specific column
revledgr.account = account of the specific row
Cell Total = (sum of revledgr.exp1 thru revledgr.exp13) + (sum of revledgr.enc1 thru revledgr.enc13)
Row 79 - Fund Balances – July 1, <YYYY>
Each cell in the row will be populated with the total from the summation of general ledger records where:
genledgr.yr = Previous year (Year entered on program screen – 1)
genledgr.fund = fund of specific column
genledgr.account = value in the Fund Balance field on the Fund Accounting profile (fam_prof.fund_bal)
Cell Total = sum of genledgr.gl_bal1 thru genledgr.gl_bal13
Statement of Revenues Received/Revenues
In the spreadsheet, this tab is named Revenues 10-15.
Enter revenues in appropriate accounts. Total lines highlighted in yellow are calculated automatically, and entries are not allowed.
Revenues are divided into four categories called Sources -- Local (1000 accounts), Flow-Through (2000 accounts), State (3000 accounts), and Federal (4000 accounts).
The Flow-Through Revenues are payments one LEA makes to another LEA representing mini-grant projects and flow-through grants-in-aid to be used for specific grant-related purposes. For more information, refer to Accounting Rules Part 100.
The following example shows how money flowing from one LEA to another should be entered.
District A receives Account (4400) federal money for its district and a Joint Agreement B of $100,000. District A will record the money it will keep of $50,000 in the Revenue Account (4400), and the other $50,000 will be entered in the Flow-Through Revenue Account (2200). In the Expenditures, the amount going to Joint Agreement B will be recorded as one of the (4300) accounts and Other Objects (600) for $50,000. Joint Agreement B will record the $50,000 as a Revenue Account (4400).
Funds 10-90
Accounts are according to the specific row.
Row for Designated Purposes Levies (1110-1120) will include accounts 1110-1115, 1117, 1118, and 1120.
Each row will be populated with the total from the summation of revenue ledger records where:
revledgr.yr = year entered on program screen and orgn.yr = revledgr.yr
revledgr.key_orgn = orgn.key_orgn
orgn.fund = fund of specific column
revledgr.account = account of the specific row
Cell Total = (sum of revledgr.exp1 thru revledgr.exp13) + (sum of revledgr.enc1 thru revledgr.enc13)
The spreadsheet calculates some cells on this sheet based on entries on other tabs. Calculated cells in the spreadsheet have a light yellow background. The application will populate all cells in columns C through K that are not protected or calculated.
Statement of Expenditures Disbursed/Expenditures
In the spreadsheet, this tab is named Expenditures 16-24.
Enter expenditures in appropriate functions and objects. Totals are automatically calculated and highlighted in yellow; entries are not allowed.
Expenditures are categorized by function -- Instruction (1000), Support Services (2000), Community Services (3000), Payments to Other Districts and Government Units (4000), and Debt Services (5000). Expenditures are to be entered in the function and object that best describes the expenditure.
For example, Special Education Teacher Salaries would be entered under Instruction Special Education Programs, Salaries.
Funds 10-90
Functions are according to the specific row.
Each row will be populated with the total from the summation of expenditure ledger records where:
expbudgt.yr = year entered on program screen and orgn.yr = expbudgt.yr
expbudgt.key_orgn = orgn.key_orgn
orgn.fund = specific fund for a range of rows
orgn.orgn<specific level of function> = specific function for row
expbudgt.account = specific account for row
Cell Total = (sum of expbudgt.exp1 thru expbudgt.exp13) + (sum of expbudgt.enc1thru expbudgt.enc13)
expbudgt.yr, expbudgt.key_orgn, expbudgt.account linked with orgn.yr, orgn.key_orgn to get the correct fund. Also delimited where orgn.orgn<specific level of function> = specified function for a row
The Budget column will be populated with the total from the summation of expenditure ledger records for the account/function combination where:
expbudgt.yr = year entered on program screen and orgn.yr = expbudgt.yr
expbudgt.key_orgn = orgn.key_orgn
orgn.orgn<specific level of function> = specific function for row
orgn.fund = fund for a range of rows
expbudgt.account = list of accounts in columns C - J
Cell Total = (sum of expbudgt.bud1 thru expbudgt.bud13)
Schedule of Ad Valorem Tax Receipts
In the spreadsheet, this tab is named Tax Sched 25.
The description of tax receipts is located in column A.
Entries in column B are propagated from page 10, Lines 5 to 11, and are for taxes the district received for the 2022 Levy and Prior Levies.
Entries in column C are for taxes received from the 2022 Tax Levy only.
Column D subtracts column C from B, resulting in taxes received in fiscal year 2022 and prior. (No entries are allowed.)
Entries in column E are for the estimated total tax receipt anticipated from the 2022 Levy.
Column F subtracts column C from column E and should be the remaining anticipated payments for the 2022 taxes.
For the Accrual Basis of Accounting purposes, formulas in column B can be removed by replacing entries in the cells.
Schedule of Short-Term and Long-Term Debt
In the spreadsheet, this tab is named Short-Term Long-Term Debt 26.
Schedule of Short-Term Debt
Column C: Enter the Outstanding Balance as of 07/01/2022. This must equal the ending outstanding balance as of 06/30/2022.
Column D: Enter debt issued between 07/01/2022 through 06/30/2023.
Column E: Enter debt retired between 07/01/2022 through 06/30/2023.
Column F: Automatically calculates the Total Ending Outstanding Balance as of 6/30/2023.
Schedule of Long-Term Debt
All leases must be transferred to Debt Services and reported on the long-term debt tab due to Governmental Accounting Standards Board (GASB) Statement No. 87. For both operational and capital leases, list them on “Part A: GASB 87 Leases Only” on the long-term debt tab.
Parts A & B
Column A: Enter the identification name and number of the original debt (bond) issued.
Column B: Enter the date when the original debt (bond) was issued.
Column C: Enter the amount of the original debt (bond) issued.
Column D: Enter the corresponding number from the bottom of the page describing the debt type issued. Use other if not listed. Part A: GASB 87 Leases Only has been populated for you.
Column E: Enter the beginning outstanding debt (bond) as of 07/01/2022.
Column F: Enter the new debt (bonds) issued between 07/01/2022 and 06/30/2023.
Column G: Enter any differences in the amount issued (column F) and page 7, Line 33, account (7210) Principal on Bonds Sold. These amounts could be for defeasance or other fees that would cause a difference in the two amounts. Describe and itemize on the Itemization page.
Column H: Enter the amount of debt (bond) retired between 07/01/2022 and 06/30/2023. This amount must equal page 20, Line 174, account (5300) Payments of Principal on Long-Term Debt.
Column I: The amount automatically calculated for the ending outstanding balance as of 06/30/2023 should equal the amount on page 6, Line 36, account (511) Long-Term Debt Payable.
Column J: Enter the amount provided for future payments on Long-Term Debt. This must equal page 6, Line 23 account (350) Amount to be Provided for Payment on Long-Term Debt.
Schedule of Restricted Local Tax Levies and Selected Revenue Sources/Schedule of Tort Immunity Expenditures
In the spreadsheet, this tab is named Rest Tax Levies-Tort Im 27.
Schedule of Restricted Local Tax Levies and Selected Revenue Sources
All Tort Fund Revenue and Expenditures are linked to those reported on the respective Revenues 10-15 and Expenditures 16-24 tabs and are automatically entered on this schedule.
Line 3 Column G through K: Enter the cash basis beginning fund balance for the funds listed as of 07/01/2022. This should match the ending fund balance from last year’s AFR (06/30/2022). The amounts on this line can never be negative. Explain any differences on the Itemization page.
Line 5: Enter “Ad Valorem Taxes Received by District” from account (1100) Revenues tab, Line 12.
Line 6: Enter “Earnings on Investments” from account (1500), Revenues tab, Lines 67.
Line 7: Enter “Drivers Education Fees” from account (1970), Revenues tab, Line 103.
Line 8: Enter “School Facility Occupation Tax Proceeds” from account (1983), Revenues tab, Line 105.
Line 9: Enter “Drivers Education” from account (3370), Revenues tab, Line 150.
Line 10: Enter "Other Receipts" pertinent to this section from all other accounts (1999), (3999), and (4998), the Revenues tab, and Lines 109, 170, and 269.
Line 11: Enter “Sale of Bonds” for the fund types listed and from accounts (7200), Acct Summary tab, Lines 33 through 35.
Line 14: Enter “Instruction” Expenditures from account (1000), Expenditure tab, Line 34, and Line 233.
Line 15: Enter “Facilities Acquisition & Construction Services” from account (2530), Expenditure tab, Lines 127 and 298.
Line 16: Enter the total “Tort Immunity Services” expenditures from Tort Fund, Line 423 on the Expenditure tab.
Note: This amount is automatically entered into the schedule.Line 18: Enter “Interest on Long-Term Debt” from account (5200), Expenditure tab, Line 173.
Line 19: Enter “Payments of Principal on Long-Term Debt” from account (5300), Expenditure tab, Line 174.
Line 20: Enter “Debt Services Other” from account (5400), Expenditure tab, Line 175.
Line 22: Enter “Other Disbursements” from other accounts (2999-3999-4999), Expenditure tab.
Line 24: Ending Cash Fund Balance June 30, 2023; Line 24 cannot be negative. A deficit balance should only be reflected if tax anticipation warrants or notes are outstanding against the specific restricted tax levy due to late receipts of local taxes or if the district has a concurring legal opinion to reduce the deficit with the proceeds of a subsequent tax levy year. The Reserved Fund Balance on Assets-Liab, Line 38, should reflect a positive balance.
Line 25: Enter “Reserved Fund Balance” from account (714), Assets-Liab, Line 38.
Line 26: Automatically calculates “Unreserved Fund Balance.”
Schedule of Tort Immunity Expenditures – “Rest Tax Levies-Tort Im 27” tab
This table is required to be completed if Restricted Tort Taxes are levied and expenditures are incurred from the tax levy proceeds realized in the fiscal year and any Tort Fund balance utilized.
Itemize all Tort Fund expenditures. The Total itemized expenditures must agree with Total Tort Fund expenditures, Line 423 from the Expenditures tab.
CARES, CRRSA, ARP Schedule
In the spreadsheet, this tab is named CARES CRRSA ARP 28-35.
For more information, refer to CARES, CRRSA, and ARP Instructions.
Schedule of Capital Outlay and Depreciation
In the spreadsheet, this tab is named Cap Outlay Deprec 36.
Column C: Enter the Beginning Cost as of 07/01/2022 for each asset listed in Lines 3 through 15.
Column D: Enter the cost of assets acquired in the current fiscal year.
Column E: Enter the cost of assets sold or scrapped in the current fiscal year.
Column F: Calculates the amount of cost of assets at the end of 06/30/2023. This total must equal the amount of general fixed assets on page 6, Lines 15 through 20.
Column H: Enter the beginning accumulated depreciation for the described assets as of 07/01/2022.
Column I: Enter the addition of accumulated depreciation for the current fiscal year.
Column J: Enter the deduction of depreciation for the current fiscal year.
Column K: Calculates the amount of accumulated depreciation ending balance as of 06/30/2023.
Column L: Calculates the amount of un-depreciated assets at the end of 06/30/2023.
Line 18, column I, Allowable Depreciation is propagated to the Total Allowance for Tuition Computation on the PCTC OEPP tab, Line 197, for calculating the Estimated Per Capita Tuition Charge.
Estimated Operating Expenditures Per Pupil (OEPP) and Per Capita Tuition Charge (PCTC) Computation
In the spreadsheet, this tab is named PCTC-OEPP 37-39.
Line 98
Enter the amount of the 9-month Average Daily Attendance (ADA). ADA is calculated through the Student Information System (SIS). School districts must provide their auditor with their 9-month ADA from SIS.
Lines 192 and 193
Go to the webpage Evidence-Based Funding Distribution Calculation.
Under Reports, open the FY 2023 Special Education Funding Allocation Calculation Details and the FY 2023 English Learner Education Funding Allocation Calculation Details.
Use the respective Excel file to locate the amount in column X for the Special Education Contribution and column V for the English Learner Contribution for the selected school district.
Enter 0 if the district does not have allocations for Lines 192 and 193.
Contracts Paid in Current Year
In the spreadsheet, this tab is named Contracts Paid in CY 40.
This schedule calculates the amount allowed on contracts obligated by the school district for the Indirect Cost Rate calculation. The contracts should be only for purchase services and not for salary contracts. Do not include contracts for Capital Outlay (500) or Non-Capitalized Equipment (700) on this schedule. They are excluded from the Indirect Cost Rate calculation.
To determine the applicable contracts for this schedule, they must meet all three qualifications:
The contract must be coded to one of the combinations listed on the "Fund-Function-Object” Chart.
The contract must meet the qualifications on the "Subaward & Subcontract Guidance” and "Indirect Cost Rate Plan” (Sub-agreement for Services).
Only list contracts that were paid over $25,000 for the fiscal year.
Do not include contracts for Capital Outlay (500) or Non-Capitalized Equipment (700) on this form; they are excluded from the Indirect Cost Rate calculation.
Estimated Indirect Cost Rate Data
In the spreadsheet, this tab is named ICR Computation 41.
This schedule is used to calculate the Indirect Cost Rates. Several school districts rely on these rates when completing their federal grant program budgets and Child Nutrition Annual Financial Reports.
Lines 7-14
Entries are allowed to determine the restricted and unrestricted rate. Do not enter total expenditures for these functions.
Exclude Object (500) Capital Outlay and Object (700) Non-Capitalized Equipment.
Do not enter negative numbers. Negative numbers prevent rates from being calculated accurately and ultimately result in a zero rate.
Line 9
Only include maintenance work. Do not include capital projects in the Operation & Maintenance Fund.
For example, Capital Project items are the replacement of windows, heating, cooling, and contracted work.
Line 10
Enter only the cost of food.
Line 11
Enter the value of food commodities received for FY 2023. The value of commodities will be included when determining the $750,000 federal expense threshold for the Single Audit requirement.
This is an estimated Indirect Cost Rate and is for informational purposes only. ISBE will calculate the final Program Year Indirect Cost Rates by June of the next fiscal year.
Report on Shared Services or Outsourcing
In the spreadsheet, this tab is named Shared Outsourced Services 42.
Complete this report if the school district/joint agreement shares services with any other LEA.
Line 8
If there are no shared services, check the box for “Check if this schedule is not applicable.”
Line 9
Enter an “X” in the cell if a deficit reduction plan was required in the fiscal year stated in columns C, D, or E.
Line 11-33
Enter an “X” if any of the listed functions or services shared with one or more LEAs as described above in the prior fiscal year or current fiscal year or expect to share services in the next fiscal year.
Column F
Enter the name of the entity. Additional space is available in Lines 36 and 41 for further explanation and description.
Limitation of Administrative Costs Worksheet
In the spreadsheet, this tab is named AC 43.
This page does not apply to joint agreement entities.
The “Actual Expenditures,” Lines 1-6, are linked from the Statement of Expenditures Disbursed/Expenditures (pages 16-24) and require no entries.
Enter Budget Expenditures from the 2023-24 School District Budget (ISBE Form 50-36) for the Education, Operations & Maintenance, and Tort funds.
In line 7, enter Early Retirements or Other Pension Obligations (required by state law) in both the Actual Expenditures and Budget Expenditures sections.
If Line 9 is greater than 5%:
Check the first box if ISBE ranks the district in the lowest 25th percentile of like districts in administrative expenditures per student (4th quartile) and will waive the limitation by board action after a public hearing.
Check the second box if the district will seek a waiver from the General Assembly under the School Code Section 2-3.25g. Waiver applications must be postmarked by a specific date. Refer to the form for those dates.
Note: Information on the waiver process can be found on the ISBE Waivers webpage.Check the third box if the district will amend its budget to comply with the limitation.
The Limitation of Administrative Costs Worksheet may be submitted with the AFR or separately, but it must be completed by November 15 of the reporting year.
Itemization
In the spreadsheet, this tab is named Itemization 44.
This page must be completed when comments and explanations are requested in the statements or schedules of the AFR. Explain all account numbers and functions where “Describe & Itemize” appears. Errors that are indicated but uncorrectable should be explained on this page. Explanations that are included in the financial notes should be referenced on the Itemization page.
REF
In the spreadsheet, this tab is named REF 45.
This page provides additional information when lines within the AFR have footnote references.
Opinion-Notes
In the spreadsheet, this tab is named Opinion-Notes 46.
Embed or insert your Word (doc) or Adobe (.pdf) files into this sheet by following the instructions below. Instructions are also included in the Opinion-Notes worksheet.
Most programs allow files to be converted to PDFs, but if you have problems, you may send the attachments with the AFR, and they will be embedded for you.
Select: Insert from the menu bar
Select: Object
Select: Create from file tab
Select: Browse
Select: The file that you want to embed
Check: Display as icon
Select: OK
Deficit Annual Financial Report Summary Information
In the spreadsheet, this tab is named DeficitAFRSum Calc 47.
This report calculates the amount of deficit for each district’s financial report by subtracting the total amount of expenditures from the total amount of revenues in the operating funds only (Education, Operations and Maintenance, Transportation, and Working Cash).
If the result is a deficit (expenditures exceed revenues), add the total fund balance for the same funds as the above. The total fund balance must be at least three times the total deficit amount.
If the fund balance is less than three times the total deficit amount, a three-year Deficit Reduction Plan (pages 23-27 of the 2023 Budget Form) must be completed. (Refer to Illinois School Code 17-1 [105 ILCS 5/17-1].)
If the FY 2023 budget has already been submitted, procedures to amend the budget may be required.
Audit Checklist
In the spreadsheet, this tab is named AUDITCHECK.
Scroll down the sheet and check for any “ERROR” messages. All error messages must be resolved before forwarding to the LEA and Regional Office of Education. Some error messages are unavoidable and cannot be resolved. In most cases, a note explaining the reason on the Itemization page will be sufficient. If explanations are not included with the AFR, the district and auditor will be notified by phone or email to resolve the issue. Another submission of an amended AFR may be required, depending on the circumstances.
Single Audit and GATA Information
In the spreadsheet, this tab is named Single Audit & GATA Information.
The Single Audit report section is to be completed if the LEA federal expenditures are $750,000 or greater.
Contact the ISBE for Single Audit questions.
The Single Audit tabs have been removed from the AFR. However, the Single Audit and GATA Information tab will guide you through completing and submitting the Single Audit.