Skip to main content
Skip table of contents

Annual Financial Report

The Illinois Board of Education requires that districts submit an annual financial report (AFR) spreadsheet to the Regional Office of Education by October 15th. The AFR is due to ISBE by November 15th.

You can download the AFR spreadsheet from https://www.isbe.net/Pages/Annual-Financial-Report.aspx.

The Annual Financial Report option populates information in the AFR spreadsheet using application data. You can review and update the information in the spreadsheet after the data is populated. After the information is reviewed and updated, you can submit the spreadsheet to ISBE through the ISBE website.

Menu Path

Fund Accounting > State > State - IL > Annual Financial Report

Submission Period

Annually (October 15th)

Selection Rule

Annual Financial Information is extracted based on the Reporting Structure and Crosswalks set up for the selected reporting period.

Setup

Before loading the fiscal year financial data table and generating the files to be submitted to the Department of Education, you must define relationships between organizations and accounts used in the Fund Accounting system and state account codes. This only needs to be done once, unless a relationship changes.

Set up the following:

Reporting Structure

Before running the Annual Financial Report for the first time, provide some detail about the accounting structure. For each level of accounting, you need to specify the level and the starting and ending positions.

This setup is the same as the IL Budget Form and IL Site Based Expenditures Report. If you completed the setup for either of these reports, it does not need to be done for the year you are processing.

You can define your district's reporting structure on the IL Annual Financial Report - Setup - Reporting Structure page.

  1. Select Setup > Reporting Structure.

  2. Filter records using the following criteria:

    • Year

    • State Level Title

    • Local Organization Level

    • Starting Position within level

    • Ending Position within level
      To specify filter criteria, select an option from the list, enter a keyword or select an option in the following field, and click Add. The filter is added.

  3. To add a record, click Add Record. Specify valid information in the Required fields and click Save. Click Yes.

    • To edit a record, click the ellipsis in the Actions column, and select Edit.

    • To delete a record, click the ellipsis in the Actions column, and select Delete. Click Yes.

Data Crosswalk

Set up crosswalks for any district Fund, Account, Program, Location, or Function that does not exactly match those used by the ISBE in the IL Budget Form.

This setup is the same as the IL Budget Form and IL Site Based Expenditures Report. If you completed the setup for either of these reports, it does not need to be done for the year you are processing.

You can set up crosswalks for the following on the IL Budget Form - Setup - Data Crosswalk - Account page:

  • Account

  • Function

  • Fund

  • Program

  • Location

  1. Select Setup > Data Crosswalk.

  2. Select the Crosswalk Type, then select a Year.

  3. To add a record, click Add Record. Refer to Field Descriptions, enter valid information in the required fields, and click Save. Click Yes.

    • To edit a record, click the ellipsis in the Actions column, and select Edit.

    • To delete a record, click the ellipsis in the Actions column, and select Delete. Click Yes.

Field Descriptions

The following table describes the fields for the following crosswalk types:

  • Account

  • Function

  • Fund

  • Program

Field

Description

Year

The fiscal year for which the crosswalk data applies.

District Type

Either School District or Joint Agreement. (NOTE: If your accounting system is used for both School Districts and Joint agreements, you will create separate crosswalks for each).

Original Account / Function / Fund / Program

The local value. Note that the name of this field is based on the Crosswalk Type selected.

State Account / Function / Fund / Program

The value defined by ISBE and used in the Budget Form. You may map multiple district values to the same state value. Note that the name of this field is based on the Crosswalk Type selected.

The following table describes the fields for the Location crosswalk type:

Field

Description

Year

The fiscal year for which the crosswalk data applies.

Location

The value defined by ISBE and used in the IL Budget Form Report. You may map multiple district values to the same state value. Note that the name of this field is based on the Crosswalk Type selected.

Site Code

The local value. Note that the name of this field is based on the Crosswalk Type selected.

Centralized

To be selected if the location account is a district centralized account.

Crosswalk Scenarios

For each district account, there are three possible scenarios for crosswalk setup:

  1. One district account exactly matches one state account. No crosswalk entry is needed.

  2. One district account maps to one state account. Enter one row for that account. For example, if the account designated '1110' by the state is called 'R1110' by the district, you crosswalk account R1110 to state account 1110, as shown in the screenshot above.

  3. Multiple district accounts map to a single state account. Enter a row for each district account that rolls up into the state account. For example, if the district has accounts 2000 and 2001, and then both roll up to the district account 2000, you would enter two rows – one row mapping account 2000 to state account 2000, and another row mapping account 2001 to state account 2000.

In this case, it is necessary to map the district account to the same value in the state account (e.g., district account 2000 to state account 2000). Once a crosswalk is provided to a state account, the software will only look for accounts explicitly mapped to that state account.

Federal Funding

Before running the Annual Financial Report for the first time, provide some detail about federal funding. For each level of the reporting structure defined with a State Level Title of Federal, you need to specify the level and the starting and ending position.

This setup is the same as the IL Site Based Expenditures Report. If you completed the IL Site Based Expenditures Report setup, it does not need to be done for the year you are processing.


You can define your federal funding structure on the IL Annual Financial Report - Setup - Federal Funding page.

  1. Select Setup > Federal Funding.

  2. Filter records using the following criteria:

    • Year

    • Level Value
      To specify filter criteria, select an option from the list, enter a keyword or select an option in the following field, and click Add. The filter is added.

  3. To add a record, click Add Record. Specify valid information in the Required fields and click Save. Click Yes.

    • To edit a record, click the ellipsis in the Actions column, and select Edit.

    • To delete a record, click the ellipsis in the Actions column, and select Delete. Click Yes.

Exclusions

To exclude levels, create exclusion records.

This setup is the same as the IL Site Based Expenditures Report. If you completed the IL Site Based Expenditures Report setup, it does not need to be done for the year you are processing.

You can define your exclusion levels and values on the IL Annual Financial Report - Setup - Exclusions page.

  1. Select Setup > Exclusions.

  2. Filter records using the following criteria:

    • Year

    • Exclusion Level

    • Level Value
      To specify filter criteria, select an option from the list, enter a keyword or select an option in the following field, and click Add. The filter is added.

  3. To add a record, click Add Record. Specify valid information in the Required fields and click Save. Click Yes.

    • To edit a record, click the ellipsis in the Actions column, and select Edit.

    • To delete a record, click the ellipsis in the Actions column, and select Delete. Click Yes.

Copy

Use the Copy option to copy all Setup and Crosswalk data from one year to another year. This action will need to be performed when transitioning to a new year of AFR processing.

  1. Select Setup > Copy.

  2. To copy profile and crosswalk data from one year to another, select the Year to Copy and Year to Create. Click Copy.

Purge

Use the Purge option to delete the Setup and Crosswalk data for a selected year. This could be useful for deleting unwanted data from past years or for removing erroneously entered data. You cannot recover the data that has been deleted.

  1. Select Setup > Purge.

  2. Select the Reporting Year. Click Submit.

AFR Definition Tables

There are two tables defined behind the scenes that the program uses to map data to the correct Tab and Cell positions in the AFR spreadsheet. While you will not need to maintain these tables, be aware of the following:

  • Tab and Cell data are copied from year to year as part of the Copy action if there is not already data defined for the new year. If the Tab and Cell positions defined in the Budget Form for the new year have not changed, this data will be correct.

  • Whenever the Budget Form provided by the ISBE does have changes to the tabs or cells populated by this program: PowerSchool will provide updated table values for this new year.

Create File

The program will automatically populate as much information in the spreadsheet by reading data from the database and inserting the values into the appropriate rows, columns, and cells in the spreadsheet.

You will need to enter some information before having the spreadsheet populated. After all of the information is entered, you must click the OK action item or press the Enter key to begin the process.

The actual process of populating the spreadsheet is:

  • Collecting data using the year entered on the report page.

  • Populating the appropriate cells with data.

  • Unprotecting the tabs that are allowed to be unprotected.

  • Saving the populated spreadsheet in the same location and with the same name that is specified in the File Name field on the report and displaying it to the user.

After you have populated the spreadsheet, you will be able to review it and make changes/additions before submitting it.

Running the program multiple times will cause any changes to data populated by the program to be lost and replaced by values calculated by the program. Any empty fields that were modified after running the program will have the data retained.

  1. Download the appropriate AFR form from https://www.isbe.net/Pages/Annual-Financial-Report.aspx.

  2. From the menu, select Fund Accounting > State > State - IL > Annual Financial Report.

  3. Select Choose File and select the AFR form downloaded in Step 1.

  4. Select the Fiscal Year and District Type.

  5. Click Create, then click Yes.

    Depending on the browser type, the report file is displayed within the browser, or you are prompted to select a download location.

    If you receive an error that the Excel file was not updated, verify that you selected the correct spreadsheet. For example, you will get this error if you choose the Budget Form spreadsheet when processing an AFR. If you have chosen the correct file, then there may be a configuration problem on your server.

  6. Save the file and click OK.

  7. Navigate to the folder containing the report file.

  8. Inspect the files. Update data, if needed.

  9. Repeat steps 2-8 until all required data is included.

Follow the state's submission procedures.

Upload and Submission Guidelines

As per ISBE requirements, the following guidelines regarding the spreadsheet must be followed when submitting the spreadsheet:

  • Do not change the formatting.

  • Do not change the tab names.

  • Do not delete any pages in the spreadsheet.

  • Do not disable the protection of the pages. Exception: A-133 sheets from pages 38-47 may be unprotected if necessary.

  • Do not disable copy/paste features.

  • Do not disable decimals.

  • Break the links in the spreadsheet before sending it to ISBE.

AFR Spreadsheet Data Mapping

Tab Name

Action Needed

Description

COVER

Manual entry / School District or Joint Agreement checkbox populated.

The program places an 'X' in either the School District checkbox or Joint Agreement checkbox, based on the user's selection. All other cells on the cover sheet require manual entry.

TOC

No entry needed


Aud Quest 2

Manual entry


FP Info 3

Manual entry/automatic spreadsheet calculation


Fin Profile 4

Automatic spreadsheet calculation


Assets-Liab 5-6

Manual Entry/Populated by the program.

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 specific row

Cell Total = sum of genledgr.gl_bal1 thru genledgr.gl_bal13

NOTE: 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 specific row

Cell Total = sum of genledgr.gl_bal1 thru genledgr.gl_bal13

NOTE: Any non-highlighted white cells are manual entry.

Acct Summary 7-8

Manual Entry/Populated by the program.

Rows for accounts 3998, 7110-7990, 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 specific row

Cell Total = (sum of revledgr.exp1 thru revledgr.exp13) + (sum of revledgr.enc1 thru revledgr.enc13)

(Row 79) Fund Balances – July 1, 2012:

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

Revenues 9-14

Populated by the program.

Funds: 10-90

Accounts: As per 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 specific row

Cell Total = (sum of revledgr.exp1 thru revledgr.exp13) + (sum of revledgr.enc1 thru revledgr.enc13)

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

Expenditures 15-22

Manual Entry/Populated by the program.

Funds: 10-90

Functions: As per 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 range of rows

expbudgt.account = list of accounts in columns C - J

Cell Total = (sum of expbudgt.bud1 thru expbudgt.bud13)

Tax Sched 23

Populated by program.


Short-Term Long-Term Debt 24

Manual entry/automatic spreadsheet calculation


Rest Tax Levies-Tort Im 25

Manual entry/automatic spreadsheet calculation


Cap Outlay Deprec 26

Manual entry/automatic spreadsheet calculation


PCTC-OEPP 27-28

Automatic spreadsheet calculation


Contracts Paid in CT 29

Manual entry/automatic spreadsheet calculation


ICR Computation 30

Automatic spreadsheet calculation


Shared Outsourced Services 31

Manual entry


AC Tort 32-33

Populated by the program.

Funds: 10, 20

Functions: As per specific row.

Actual Expenditures

Actual Expenditure columns are calculated in the spreadsheet based on values entered on other tabs of the spreadsheet.

Budgeted Expenditures

Each row will be populated with the total from the summation of expenditure ledger records where:

expbudgt.yr = Next year (Year entered on program screen + 1)

expbudgt.key_orgn = orgn.key_orgn

orgn.fund = specified fund for column

orgn.orgn<specific level of function> = specified function for row

Cell Total = (sum of expbudgt.bud1 thru expbudgt.bud13)

NOTE: Unprotected cells in columns H and I will be populated by the program.

Itemization 34

Manual entry


REF 35

No entry needed


Opinion-Notes 36

No entry needed


DeficitAFRSum Calc 37

Automatic spreadsheet calculation.


AUDITCHECK

Automatic spreadsheet validation


Single Audit Cover

Manual entry. Unprotected by the AFR program.


Single Audit Checklist

Manual entry. Unprotected by the AFR program.


SEFA Reconcile

Manual entry and automatic spreadsheet calculation. Unprotected by the AFR program.


SEFA

Manual entry. Unprotected by the AFR program.


SEFA NOTES

Manual entry. Unprotected by the AFR program.


SF&QC Sec-1

Manual entry. Unprotected by the AFR program.


SF&QC Sec-2

Manual entry. Unprotected by the AFR program.


SF&QC Sec-3

Manual entry. Unprotected by the AFR program.


SSPAF

Manual entry. Unprotected by the AFR program.



JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.