Certified Annual Report (CAR)

The Certified Annual Report (CAR) is a report of financial data required by the Iowa Code to be filed annually with the Department of Education by each school district and area education agency.

Menu Path

Choose Fund Accounting from the main navigation menu, then choose State. Click Certified Annual Report (CAR).

Submission Period

Yearly (no later than September 15)

Selection Rule

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

Setup

Before generating the Certified Annual report, set up the following:

• Certified Annual Report (CAR)#Reporting Structure

• Certified Annual Report (CAR)#Crosswalk

Reporting Structure

Before running the Certified Annual report for the first time, provide some detail about the accounting structure. For each level of accounting, specify the level and the starting and ending positions.

You can define your district's reporting structure on the IA Certified Annual Report (CAR) - Setup - Reporting Structure page.

1. Choose Setup, then Reporting Structure.

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

3. To edit or delete a record, click the ellipsis in the Actions column, and select the appropriate option.

4. To download the information, click Download, then select Download PDF or Download as an Excel Document.

Crosswalk

You can set up crosswalks for the following on the IA Certified Annual Report (CAR) - Setup - Crosswalk page:

• Fund

• Facility

• Function

• Program

• Project

• Object

• Source

• Account

1. Choose Setup, then Crosswalk.

2. Select the Crosswalk Type.

3. Filter records by the following criteria:

   • Local Code

   • State Code

4. To specify filter criteria, choose an option from the list, enter a keyword or select an option in the following field, then click Add.

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

6. To edit or delete a record, click the ellipsis in the Actions column, and select the appropriate option.

7. To download the information, click Download, then select Download PDF or Download as an Excel Document.

Process

To create a report:

If needed, update the related setup to reflect any changes.

1. Load the data for the report.

2. Create the report file and verify that data was loaded correctly.

   • Review the log file for possible errors.

   • Use the maintenance options to add or delete records or to correct data. Note that any changes that you make here must be duplicated in the source data.

     

     If errors require changes to setup or source data, you must purge the loaded data, then repeat steps 1 and 2.

3. Once the data is validated, create the Certified Annual report.

Download the Certified Annual report and follow the State's submission procedures.

Load Data

Balance sheet accounts for the fiscal year you enter are selected from the general ledger table, including entries with zero balance. Only the portion of the account code defined by the beginning and ending positions on the IA Certified Annual Report (CAR) - Setup - Reporting Structure page is used as the account in the state structure. When a user-defined crosswalk defines a fund or account, the state code is used for the state account structure.

The facility, project, and program portions of the state account structure must be defined as a crosswalk-only record in the Categorical State Fund Balances table. Amounts of Liability and Equity accounts defined in the Fund Accounting profile are reversed (multiplied by –1) before being stored. Credit balances are stored as negative amounts. Also, records are created for Categorical State Fund balances based on the non-crosswalk records in the Categorical State Fund Balances table. Refer to Certified Annual Report (CAR)#Categorical State Fund Balances for more information.

Fiscal year revenues are selected from the revenue ledger table. The organization level and beginning and ending positions on the setup page determine the CAR fund, facility, function, program, project, and source. If any part of the accounting structure is defined in a user-defined crosswalk, the state code is used for the state account structure.

Fiscal year expenditures are selected from the revenue ledger table. The organization level and beginning and ending positions on the setup page determine the CAR fund, facility, function, program, project, and object. If any part of the accounting structure is defined in a user-defined crosswalk, the state code is used for the state account structure.

Encumbrance amounts, which should be zero at year-end, are bypassed for revenues and expenditures. In addition, all control accounts defined in the Fund Accounting profile are also ignored when loading revenue and expenditure data.

Amounts for all 13 periods on a record are summarized. Each record on the CAR table contains an account ID indicating a balance sheet, revenue, or expenditure record.

You can load data based on Fiscal Year and District ID.

1. Choose Load.

2. Select the Fiscal Year.

3. Enter the District ID.

4. Select Load Using Fund Groups to use fund groups.

5. Select Report Optional Revenue Function to report the optional revenue function.

6. Click Load, then Yes.

7. Save the IA CAR Load Summary report, then click OK.

8. Navigate to the folder containing the summary report file.

9. Inspect the file. Update data, if needed.

10. Repeat steps 1 to 9 until all data is loaded.

Maintain Data

You can correct loaded data before submitting the report. However, any corrections to the application information must also be updated in the source data.

Since the data submitted for the report must be consistent with the district's audited financial statements, records should only be added, changed, or deleted if they reflect changes made to the financial statements after the table has been loaded. If necessary, you can reload the table, but all changes are lost.

If a record is deleted or an account code is changed, the original record is retained in the table to print the record on the Audit Log. The record is ignored and not displayed on the IA Certified Annual Report (CAR) page.

1. On the IA Certified Annual Report (CAR) page, you can filter records by the following criteria:

   • Year

   • Account ID

   • BUDGET-CODE

   • Account

   • State Fund

   • District ID

   • CAR Fund

   • CAR Facility

   • CAR Function

   • CAR Program

   • CAR Project

   • CAR Object

   • CAR Source

   • CAR Account

   • Amount

   • Exclude Zero Amount

2. To specify filter criteria, choose an option from the list, and enter a keyword or select an option in the following field.

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

4. To edit or delete a record, click the ellipsis in the Actions column, and select the appropriate option.

5. To download the information, click Download, then select Download PDF or Download as an Excel Document.

Create Files

Certified Annual Report

1. Choose Reports, then Certified Annual Report.

2. Create a hard copy of the report to verify data before you create the electronic file:

   1. Select the Fiscal Year.

   2. Under File Type, select Hard Copy.

   3. Under File Format, select PDF or Excel.

   4. Under Detail / Summary, select Detail or Summary.

   5. Select Exclude Zero Amounts to exclude zero amounts from the report.

   6. Click Create, then Yes.

3. Save the IA CAR report and navigate to the folder containing the file.

4. Inspect the file. Update data, if needed.

5. Repeat steps 1 to 4 until all data is included.

6. Create the electronic file:

   1. Select the Fiscal Year.

   2. Under File Type, select Electronic File.

   3. Click Create, then Yes.

7. Save the file and click OK.

The default file name is CAR<YYYY><District ID>.csv.

Audit Log

The Audit Log shows the changed values of records that were added, deleted, or had amounts changed. For records that had an account code changed, both the original and new records are printed.

1. Choose Reports, then Audit Log.

2. Select the Reporting Year.

3. Under File Format, select PDF or Excel.

4. Click Create, then Yes.

5. Save the file and click OK.

The default file names are:

• PDF: IA Certified Annual Report (CAR)_Audit Log_<MMDDYYYY>_<hhmmss>.pdf

• Excel: IA Certified Annual Report (CAR)_Audit Log_<MMDDYYYY>_<hhmmss>.csv

Notes about the IA Certified Annual Report

• The report is a comma-delimited file. If a field is not required for a certain record, the comma delimiter directly follows the preceding delimiter. For example, if the facility field is not used for this record, the record would be:
  0001,2,03,,0005,006,0007,008,0009,010,12345.67

• The amount field is up to 18 characters long, with two decimal positions. Since ',' is the delimiter, do not include commas but do include the decimal point. For negative values, add the "-" sign ahead of the number. Example: 12345.67 or –321.01 or 123 are valid, but 123,456 causes an error because of the comma.

• Funds 11-19 must roll up to fund 10. Districts or Area Education Agencies (AEAs) should not use funds 11-19.

• Funds 41-49 must roll up to fund 40.

• Drop funds 01 and 02 from the upload. Use of funds 01 and 02 are optional locally.

• Drop all locally-defined dimensions not listed in the record layout, including any revenue or expenditure accounts used locally for agency funds. It is not necessary to roll up duplicated account codes created when the locally defined dimensions are deleted.

• Drop all accounts with zero amounts.

• Show all amounts as positive numbers except contra accounts and accounts specifically allowed to be negative. All contra accounts are negative numbers.

Categorical State Fund Balances

The records on the IA Certified Annual Report (CAR) - Categorical State Fund Balances page serve one of two purposes:

• To act as a crosswalk to assign facility program and project codes to balance sheet entries when loading data.

• To create balance sheet entries when loading data for Categorical State Funds, including amounts from multiple revenue and expenditure records.

Copy

Use the Copy option to copy all Categorical State Fund Balances data from one year to another.

1. Choose Copy.

2. Select the Year to Copy and Year to Create. Click Copy.

   

   Both crosswalk-only and non-crosswalk items are copied to the new year. For non-crosswalk records, a new beginning balance is calculated.

Maintain Data

You can correct loaded data before submitting the report. However, any corrections to the application information must be duplicated in the source data.

1. On the IA Certified Annual Report (CAR) - Categorical State Fund Balances page, filter records by the following criteria:

   • Year

   • Crosswalk Only

   • Beginning Balance

   • Fund

   • Facility

   • Program

   • Project

   • Account

   • CAR Fund

   • CAR Facility

   • CAR Program

   • CAR Project

   • CAR Account

2. To specify filter criteria, choose an option from the list, and enter a keyword or select an option in the following field.

3. To add a record, click Add Record. Refer to Certified Annual Report (CAR)#Field Descriptions and specify valid information. Click Save, then Yes.

4. To edit or delete a record, click the ellipsis in the Actions column, and select the appropriate option.

5. To download the information, click Download, then select Download PDF or Download as an Excel Document.

Field Descriptions

The following table lists the fields on the Categorical State Fund Balances Data dialog box.

Create File

The Categorical State Fund Balances report shows the beginning balance of each of the non-crosswalk records. After that, each of the revenue and expenditure amounts that make up the fund and the key organization and account for that amount is listed. Lastly, a total of all revenue and expenditures and the ending balance of the fund is printed.

1. Choose Report.

2. Select the Fiscal Year and Fiscal Period.

3. Under File Format, select PDF or Excel.

4. Click Create, then Yes.

5. Save the file and click OK.