PA Annual Financial Report – Revenue

Pennsylvania School Districts needed the ability to create a file that can be processed by the Data File Load (DFL) option of the AFR package provided by the State of Pennsylvania. The file will contain revenue information in the format required by the AFR Access database.

To access the PA Annual Financial Report – Revenues, select the following menu path: Fund Accounting -> State -> State -> PA Annual Financial Report - Revenues.

Report Specific Set-up

There are a few pieces of information that must be setup about your district in order for the AFR Reporting to be accurate.

AFR PROFILE

To update the districts profile for the AFR Reporting, select the 'AFR Profile' option from the ribbon bar on the main window.

Enter the information as specified in the table below and click 'OK'.

Field Name	Description
District Type	Select the type of district for the school
General Fund	The district's General Fund.
Organizational Level of Function	The Function Level of the district's budget code structure as defined on the Fund Accounting Profile
Organizational Level of Funding Source	The Funding Source level of the district's budget code structure as defined on the Fund Accounting Profile, which defines the source of the funds to be either federal or non-federal.
Federal Funding Low and High Ranges Columns	Enter a low and/or high funding source code that represents a code or range of codes defined as federally funded.
Organizational Level of Institution	The Organizational level of institution of the district's budget code structure as defined on the Fund Accounting Profile, which defines the institution or instructional code.
Instructional Information Low and High Ranges Columns	Enter a low and/or a high institution code that represents a code or range of codes to which the defined elementary and secondary percentages apply. A minimum of one entry is required in this section.
Revenue Code Level	The revenue level of the district's budget code structure as defined on the Fund Accounting Profile.
Revenue Code Starting Position	Enter the starting position of the revenue code.

AFR Revenue Crosswalk

This will allow the district to maintain the state defined list of reportable revenue codes for various fiscal years. There are three ways to maintain the AFR Revenues Crosswalk codes, manually enter/maintain the codes, import them from an external text file or you can copy the codes from one fiscal year to another fiscal year. Each option is explained as follows.

To update the AFR Revenue Crosswalk for the AFR Reporting, select the 'AFR Revenue Crosswalk' option from the ribbon bar on the main window.

Enter the information as specified in the table below.

Field Name	Description
Year	This is the fiscal year that will be associated with the codes.
Revenue Code	State defined revenue code.
School District	This revenue code applies to School Districts
Charter School	This revenue code applies to Charter Schools
Career and Technology Center / Area Vocational Technical School	This revenue code applies to Career and Technology Center / Area Vocational Technical Schools
Special Program Jointure	This revenue code applies to Special Program Jointures

To import the state defined revenue codes from a text file provided by the PDE, select the 'Load External' option from the ribbon bar at the top of the window.

The plain text file for 'Revenue Data File Codes' can be downloaded from the Pennsylvania Department of Education's
website.

Enter the information as specified in the table below and click 'OK'.

Field Name

Description

File Location

Client PC – indicates that the file is located on a local PC

Server – indicates that the file is located on the PowerSchool ERP server

File Name

Depending on the 'File Location' chosen, either Windows Explorer screen will pop-up for you to select the file or you will have to enter the location on the PowerSchool ERP server and name of the file.

Year

Enter the fiscal year to be associated with the expenditure function/object codes.

Select the Server radio button.

There is only one dat file for all district types – afrrevcodes.dat. The file is provided for you as.dat file and was installed on your system as indicated below – enter the path for your version as part of the File Name field.

The files for version 5.0 and 5.0 SP1 are located in:

D:/spi/spifps/fam5.0/bin/afrrevcodes.dat

The files for version 5.1 are located in:

D:/spi/spifps/fam5.1/bin/afrrevcodes.dat

To copy the state defined revenue codes from one fiscal year to another fiscal year, select the 'Copy' option from the ribbon bar at the top of the window.

Enter the information as specified in the table below and click 'OK'.

Field Name	Description
Year To Copy	Enter the fiscal year of the revenue records to be copied from.
Year To Create	Enter the fiscal year of the revenue records to be copied to.

Loading the Data

Select the 'Load' option from the ribbon bar on the main window.

Select the fiscal year of the revenue records to be loaded and then click on the 'OK' button.

If records already exist for the selected fiscal year, a window will open and inform the user that current records for the year selected will be deleted. The user can click the 'Cancel' button to exit the process or the 'OK' button to proceed with the load.

While records are being loaded, a progress window will appear and track the progress of the records.

The 'Load' option generates the revenue ledger records (revledgr) for the selected fiscal year received by the General Fund with the associated data needed for the Data File Load (DFL). The first character of the revenue ledger account code is removed if it contains an alphabetic character.

The amount is calculated as the sum of the year to date receipts through period 13 for the specified fiscal year. Each four-digit revenue account is then compared to the list of acceptable state defined revenue codes in the AFR Revenue Crosswalk. If the revenue code is not found, the process attempts to roll-up the account by replacing the last non-zero digit of the revenue account with a zero and searches the AFR Revenue Crosswalk again. This process continues until the AFR revenue code is determined.

If errors occurred during the load, they will be reported in the file AFRrev_load.log. The information in the log will be grouped by error message. It is important to review the information in the error log and take the appropriate action.

A window will appear displaying the name and location of the log file. The user must acknowledge the error log by clicking on the 'OK' button to close the window.

After the load has finished, the user will be taken to the back to the progress window. An OK will now appear at the bottom of the window. The user must be clicked on the 'OK' button to exit the load process.

The details of the AFR revenue code and calculated amount are now saved for each revenue key organization and account.

NOTE: The current and prior year accrual amounts will not be loaded. These figures will need to be manually maintained after the data is loaded.

Maintaining the Data

After records have been loaded for a fiscal year, the data may be updated, new entries may be added or records may be deleted via the PA Annual Financial Report – Revenue Detail window.

Generating Reports and Files

To generate the PA Annual Financial Report – Revenue and file to be submitted to the state click the 'Reports' button in the Actions section of the ribbon bar.

This window can be used to create a Revenue Summary or Detail report or the Revenue AFR File. The Summary report for revenues summarizes the data based on the state defined revenue function found in the State Revenue Crosswalk table.

To print a report or create the AFR file (Data Load File), select the appropriate Report Type and enter the fiscal year to be reported. Check the 'Include Accounts with Zero Dollars' if accounts that have no dollar amounts associated with them should be included. The 'Include Accounts with Zero Dollars' option is not available when the AFR File Report Type is selected. The AFR File will always summarize the data and exclude entries with zero dollars.

The default File Name for the Revenue AFR File is AFRRevFile.txt. The default directory will depend upon client-based system variables.

File Layouts and Data Mapping

Valid Revenue Data File Format for DFL:

Format: Comma-delimited ASCII

Zero in amount fields if not reported

No commas in amounts

Minus sign before negative amounts

Record length: Variable

Sort: Ascending by Function code and Object Code

Revenue Data File Format (PDE-2057)

Data Item	Field Name	Description	Length	Position	Location
Fund Code	Afr_fund	Fund Code – Two digits required	2	1^st element	Pa_afrrev
Revenue Code	state_code	Revenue Code – Four digits required	6	2^nd element	pa_afrrev
Revenue Amount	amount	Revenue Amount – Format 9(10).99	13	3^rd element	pa_afrrev
Current year accrual amount	curr_accr_amt	Current year accrual amount – Format 9(10).99	13	4^th element	pa_afrrev
Prior year accrual Amount	prior_accr_amt	Prior year accrual amount – Format 9(10).99	13	5^th element	pa_afrrev

Purging the Data

To purge the PA Annual Financial Report - Revenue data for a specific year click on the 'Purge' option in the actions area of the ribbon bar.

This window allows for the purging of Revenue data for the specified fiscal year.

Enter the year of the Revenue data to be deleted and click on 'OK'.

After the OK button has been clicked, the user will be asked to confirm their intentions before the data is purged.

The Revenue data for the specific year will be deleted from the Pennsylvania revenue table, pa_afrrev.

Cautions and Restrictions

This application only provides the revenue information required for the Pennsylvania Annual Financial Report for school districts (PDE-2057). Each district is responsible for creating the remainder of the report using the AFR package provided by Pennsylvania Department of Education.

The integrity of the data output to the file is dependent upon the profile information being setup correctly.

This method assumes that the site is using the state defined revenue codes and that they can be cross-walked to the state accepted codes using the state definitions.

This application is NOT currently designed to create the Annual Financial Report for Intermediate Units.

As of October of 2015, the web link to information about the Annual Financial Report – PDE 2057 found on the PA Department of Education's web site is:

http://www.portal.state.pa.us/portal/server.pt/community/annual_financial_report_-_school_district/7310