Annual Statement of Affairs 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 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 ASA Instructions on the ISBE website.

Spreadsheet Tabs

The following sections describe the tabs in the ASAform.xlsx spreadsheet:

Cover Page

Choose the district name from the drop-down menu on line 9. This will auto-populate the district RCDT, district type, address, and county name. ASAs missing the district name/RCDT will not be considered submitted/received by ISBE and may be subject to a past-due penalty.
Enter the local newspaper's name in which the statement was published on line 13.
Refer to the statement under Assurance and select X from the drop-down beside the YES box (cell F16).
Capital Assets (cells E20 through E25):
The district's Capital Assets information from the Capital Assets (200) section on the Assets-Liab 5-6 tab of the same fiscal year’s Annual Financial Report (AFR) is included.
Number of Pupils Enrolled Per Grade (cells E29 through E39 and E41 through E45):
Enter student enrollment by grade. The total line is automatically calculated.
Fall Enrollment reflects students enrolled as of the last school day in September and is taken from the Student Information System (SIS) after October 15.
Student Enrollment is also found on the ISBE Fall Enrollment Counts webpage under the 2022-2023 drop-down.
Joint agreements MUST report enrollment if they work directly with student instruction.
Cells I19 through I21:
Enter the square mileage, number of Attendance Centers, and the 9 Month Average Daily Attendance (ADA).
To obtain the ADA, log in to SIS via IWAS and select Average Daily Attendance from the SIS navigation menu.
Number of Certificated and Non-Certificated Employees (Cells I23 through I24 and I26 through I27):
The number of certificated and non-certificated employees who are full-time and part-time is retrieved from the Salary Schedule tab.
Note: Substitute teachers should be part-time and certificated if they hold a certificate/license to teach. A substitute teaching license does not qualify as a certificate/license to teach. This would be considered non-certificated.
Cells I29 through I42:
Enter the tax rates by fund from the tax statement received from the county clerk. This would be taxes from the prior year paid in the current year.
Cell I43:
Enter the district’s Equalized Assessed Valuation (EAV) from the AFR, page 3 (FP Info 3 tab), cell J7.
If the AFR is unavailable, locate the EAV on the tax statement received from the county clerk.
Cell I46:
Enter total long-term debt outstanding from the AFR, page 26 (Short-Term Long-Term Debt 26 tab), cell I49. The EAV per pupil and the percentage of long-term debt currently obligated will be calculated automatically. If the AFR is unavailable, the long-term debt outstanding/obligated is in the district's accounting records.

Assets & Liabilities

The Statement of Assets and Liabilities tab includes the district’s Assets and Liabilities from the AFR, using figures excluding student activity funds. The student activity funds are included on lines 40, 42, and 43. If the AFR is unavailable, use the district’s accounting records.

The Change in Cash Position section is from the prior year’s Assets and Liabilities tab in the AFR. You must enter the previous year’s cash and investments on Row 62.

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 the specific column
genledgr.account = account of the specific row
Cell Total = sum of genledgr.gl_bal1 through 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 the specific column
genledgr.account = account of the specific row
Cell Total = sum of genledgr.gl_bal1 through genledgr.gl_bal13

Any non-highlighted white cells are manual entry.

Revenues & Expenditures

This is the Statement of Revenues Received/Revenues, Expenditures Disbursed/Expenditures, Other Sources/Uses of Funds, and Changes in Fund Balances section.

The district’s Receipts/Revenues, Disbursements/Expenditures, Other Sources/Uses of Funds, and Beginning/Ending Fund Balances from the AFR are included, using figures excluding student activity funds.

The student activity funds are included on lines 34, 36, and 38.

If the AFR is unavailable, use the district’s accounting records.

Revenues

Funds: 10-90

Accounts: As per the specific row.

Each row is 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 the specific column
revledgr.account = range of AFR accounts that roll up to a parent ASA account
Cell Total = (sum of revledgr.exp1 through revledgr.exp13) + (sum of revledgr.enc1 through revledgr.enc13)

The spreadsheet calculates some cells on this sheet based on entries on other tabs. Calculated cells have a light yellow background. The program will populate all cells in columns C through K that are not protected or calculated.

Expenditures

Funds: 10-90

Functions: As per the specific row.

Each row is 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 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, and expbudgt.account are linked with orgn.yr and orgn.key_orgn to get the correct fund. Also delimited where orgn.orgn<specific level of function> = specified function for a row.

Fund Accounts

Rows for accounts 7000 and 8000 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 specific row
Cell Total = (sum of revledgr.exp1 thru revledgr.exp13) + (sum of revledgr.enc1 thru revledgr.enc13)

Fund Balances

You must manually enter values for rows 30, 31, and 34.

The spreadsheet calculates some cells on this sheet based on entries on other tabs. Calculated cells have a light yellow background. The program will populate all cells in columns C through K that are not protected or calculated.

Published Summary

This is the Annual Statement of Affairs Summary for Publication section.

You must submit this tab to the local newspaper for publication.

Enter the district’s telephone number and office hours. The remaining data is automatically populated.

Salary Schedule

This is the Salary Payments section.

You must submit this tab to the local newspaper for publication.

Certificated and non-certificated personnel are included based on their annual salary through check history. Certificated employees have a Primary certificate only. The Exclude from Annual Statement of Affairs Report option for the certificate type on the following pages should be unchecked:

Substitute teachers should be certificated if they hold a certificate/teaching license. A substitute teaching license does not qualify as a certificate/teaching license; it would be considered non-certificated.

On the Salary Schedule tab, you must state whether another school district pays the employees' salaries and enter the total amount on the appropriate vendor payments tab. Contract employees and vendors should be listed on the appropriate vendor payments tab, not the Salary Schedule tab.

The following are the vendor payments tabs:

Paym over $2,500

This is the Payments over $2,500.

You must submit this tab to the local newspaper for publication.

This tab includes vendors and individuals who are not district employees who were paid over $2,500 based on their vendor checks with the P - Payable type and transaction codes 20 or 21.

If there are no vendors or non-district employees to report, the Person, Firm, or Corporation column displays None, and the Aggregate Amount column displays 0.

Paym $1000 to $2,500

This is the Payments of $1,000 to $2,500.

This tab includes vendors and individuals who are not district employees who were paid between $1000 and $2,500 based on their vendor checks with the P - Payable type and transaction codes 20 or 21.

If there are no vendors or non-district employees to report, the Person, Firm, or Corporation column displays None, and the Aggregate Amount column displays 0.

Paym $500 to $999

This is the Payments of $500 to $999.

This tab includes vendors and individuals who are not district employees who were paid between $500 and $999 based on their vendor checks with the P - Payable type and transaction codes 20 or 21.

If there are no vendors or non-district employees to report, the Person, Firm, or Corporation column displays None, and the Aggregate Amount column displays 0.

Contracts Exceeding $25,000

This is the Reports on Contracts Exceeding $25,000 Awarded section. You must manually enter values on this tab.

Select Contracts Exceeding $25,000 Guidance on this tab to view instructions about qualifying contracts.

If qualifying contracts over $25,000 were awarded, enter values for:

The total number of contracts
The total value of all contracts
The total number awarded to specified businesses
The total value awarded to specified businesses

If no qualifying contracts over $25,000 were awarded, enter 0 in each row.

Error Checks

This is the error-checking section that lists the errors in the spreadsheet.