FIRMS Derivation Model Overview

FIRMS

• Financial Information Records Management System (FIRMS)

• A database that stores a variety of data submissions from the campuses that assist the Chancellor’s Office in completing systemwide analysis and reporting tasks.

• The process for quarterly accounting submission and GAAP submission is the focus of this presentation and is commonly the meaning of this reference for this audience.

CDIP

• The Corporate Data Integrity Package (CDIP) was implemented with Peoplesoft 8.4.

• NACUBO program code is required for FIRMS reporting.

• NACUBO issues industry standards on the functional classification of expenses.

• CDIP standardized the assignment of NACUBO program code to campus data by using a series of rules based on Peoplesoft account type.

• The derivation model uses separate business units for external reporting of the derived data.

Derivation Concept• Three PeopleSoft Business Units

CMP Business Unit

Legal Basis All six ChartFields

can be used Campus Business

Needs ChartField Values

based on Campus Information Needs

CSU Business Unit Legal Basis NACUBO Program

Code derived into PS Program ChartField

Only Three ChartFields – Fund, Account, Program Code

GAP Business Unit GAAP Basis Four ChartFields used

– Fund, Account, Program Code and Class

All ChartFields are derived

Peoplesoft Design

Derivation Concept, Cont’d.• All derived values are based on attribute values defined by FSAC

• All derived data values will be based on the CSU standards established by FSAC

• Original data will be stored in PeopleSoft Tables along with the derived data. (Audit Tables)

Peoplesoft Design

Derivation Concept

Peoplesoft Design

CSU Business Unit Derivation

• Derives the NACUBO Program Code• Only posts 3 ChartFields to CSU Business Unit

• Fund• Account• Program Code

• Derivation is based on a set of Rules in combination with the Account Type

CSU Derivation

Summary of NACUBO Program Code Derivation Rules

• Rule 1• Account Type determines Program Code

• Rule 2• Fund Processing type + AAT Key determines Program Code

• Rule 3• FNAT Key determines Program Code• If not on FNAT Key, then PS Fund determines Program Code

• Rule 4• Department ID determines Program Code

CSU Derivation Rules

The NACUBO Program Code derivation rules are processed in a specific order for each

account type

CSU Derivation Rules

Assets, Liabilities, Equity

• These account types use Rule 1 only• Program Code is assigned based on Account

Type• Program Code 9002 is assigned to all account

types for assets, liabilities and equity

CSU Derivation Rules

Revenue

• These account types will first use Rule 2• Fund Processing Type + AAT Key

• Sample: Fund 0948 + Trans In from DCF= 1301

• If not found in Rule 2 then the program code associated with Rule 1 will be used

• Account Type R = 5000

CSU Derivation Rules

Expense

• This account type will first use Rule 2• Fund Processing Type + AAT Key

• Sample CSU Operating Fund + Utilities = 0704

• If Rule 2 is not found it will look to Rule 3• FNAT Key has a program code• If no program code on FNAT key then PS FUND Attribute

• If Rule 3 is not found it will look to Rule 4• Department has a program code

CSU Derivation Rules

Derivation Concept Review

Peoplesoft Design

GAAP Business Unit Derivation (Look Up)

• Derived from CSU Business Unit – Actuals Only• Derives Net Asset Category from PS Fund

• Looks up Net Asset Category based on FNAT

• Derives Natural Classification from PS Account• Looks up Natural Classification based on AAT

• Derives Program Group based on Program Code• Derives Class code from CSU fund on FNAT key

GAAP Design

Audit Tables

• Each Derivation will store the original value and the new derived value in a set of Accounting Line Tables

• Both the CSU Business Unit and the GAP Business Unit will be posted via the journal generation process within PeopleSoft

• GAAP Financial Statements will be produced directly from the PeopleSoft GAAP Ledger

• Lead Sheets will be produced using the audit tables

Audit Tables

CO FIRMS Database Extracts

• Quarterly and year-end legal basis data will be extracted from the CSU Business Unit

• Includes data from Actuals, Encumbrances and Budgets• GAAP Data will be extracted from the GAP Business Unit

• GAAP Beginning Balances will be in Period 0 of the GAP Business Unit

• Legal data will be in Acctg Periods 1-12 of the GAP Business Unit

• GAAP adjusting entries (including reclassifications) will be in Acctg Period 998

• GAAP closing entries will be in Acctg Period 999

FIRMS Extracts

www.calstate.edu

Viewing and Confirming FIRMS Derivation Rules

Review• Run the attribute build• Rule 2

• Run Query• Compare with Spreadsheet

• Rule 3• Run Query • Compare with Spreadsheet

• Rule 4• Yearly

Review Rule 2

• Run query for current Rule 2 values• CSU_CDIP_RULE_2_REVIEW

• Compare with Spreadsheet

Records in Rule 2 Review Query

Fields in Rule 2 Review Query

Review Rule 2

Compare results of Query to Systemwide Summary of Rule 2

Rule 2 Review – Query vs Spreadsheet

Rule 3 Review

• Run Rule 3 Query• CSU_CDIP_FUND_PROC_TYPE

• Compare with Spreadsheet

Records in Rule 3 Review Query

Fields in Rule 3 Review Query

Rule 3 Query Results

Rule 3 Spreadsheet of Rule 3

Rule 3 Review – Query vs Spreadsheet

www.calstate.edu

FIRMS Process

FIRMS• Review the Derivation Rules• Open Accounting Periods in CSU Business Unit• Derivation Process• Journal Generation Process• Allocations for Technology Program Codes• Run the BU in synch report• Extract & Submit• Delete Batch and Redo

Open Accounting Periods

• Open accounting periods in the XXCSU Business Unit when ready to derive

• The only required open period Tran Type for the XXCSU business unit is DEF

• Open Budget and Actuals Ledger

Open Accounting Period cont.

Open Accounting Period

Derivation Process• Run the derivation after XXCMP BU is closed• Many campuses derive on a monthly basis to run edits

and correct problems in a timely manner• Chartfields may be inactivated throught out the year and

would have to be re-inactivated if derivation was done quarterly – could be tedious

• Actual submission due quarterly• At year end use cloned databases or the batch delete

process to run the FIRMS process multiple times before submitting

Derivation Run Control

Journal Generation• One run control for each type of Journal

• Actuals• Budget• Encumbrance

• Journals need to be edited, budget checked and posted

• Use either online or batch processing depending on the size of the journal

Journal Generate – Save Run Control – Save Time

Journal Generate 3 or 4 Journals

Journal Generation cont.• Actual Journals

• Actuals• Contains the data from the Ledger Actuals• Also contains FTE data• Use batch edit, budget check and posting.

• Encumbrance Journals• Contains information from Ledger KK_DTL_ENC• Use batch edit and budget check

Journals Generated

Journals Generated

Journals Generated

Journals Generated

• Budget Journals• Contains information from the Ledger_Budg

• Journal can be processed on-line

Journals Generated

Allocations for new Technology Program classifcations• 616 Allocation

• Starting July 1, 2004 there were new program codes to be used for information and technology expenses within each program code.

• An allocation was developed to record activity in the 616 FIRMS object code series in these new program codes

• 616001-IT Communications• 616002-IT Hardware• 616003-Misc. Info Tech Cost

616 Technology Allocation• This allocation looks at any expenses in a

616XXX account that is in a program code other than the appropriate information and technology program code.

• A journal is created to move those expenses from the incorrect program code to the correct one.

616 Technology Allocation

616 Technology Allocation

616 Allocation

Example Program Group 01

Allocation Pool

Allocation Target

Allocation Process• Run the allocation

Query results before Allocation

Allocation cont.

Allocation cont.

Technology Allocation Verification

How do you find them before they happen?

• Run the CDIP Reports to find discrepancy’s

Business Units (BU) In Synch Report

BU In Synch Report Output

Extract

• Run the FIRMS Extract

• Submit the file to the FIRMS database

• Run Edits

• Correct Errors

Extract

Delete FIRMS and GAAP Derivation Batches Criteria

In order for FIRMS or GAAP derivation data to be deleted, one of the following criteria must be met:

1.Batch has not yet been journal generated

2.Batch has been journal generated but journal was deleted before posting

3.Batch has been journal generated and posted but then unposted

67676767

Delete FIRMS and GAAP Batches

How? Navigation:

CSU BU Derivation>

Derivation Processes >

Delete CSU Batch

Parameters:

• Required• Business Unit

• Optional• CSU Batch Number• Journal Template• Accounting Date• Journal ID

− Select CSU Batch

68686868

Delete FIRMS and GAAP Batches

Click the Trash Can icon to delete batch

69696969

www.calstate.edu

FIRMS Edit Errors

Types of Error Messages

• Validation edits can be categorized as one of the following:

• Valid values• Business Rules-Accounting & Budget

• Comparison Errors

FIRMS Error Examples Combination Error

Comparison Error

ERROR-E0035-AB Invalid Fund,Program Code data combination. Line no: 6099 Rec Typ:10 CSU Fund:246 Prgm Grp:07 Prgm Code:0706 Obj Code:660006 Acct Type:60 Amt:+000001631199.69 FTE:+0000.00 ERROR...Unclassified Salaries and Wages not allowed for Last Quarter Line no:2469 Rec Typ:10 CSU Fund:001 Object Code:601000 Prog Code:0404 Approp Yr:2002 Amt:-000000167980.33 FTE:+0000.00 ERROR-E0151D-A Objects 506026 and 680026 must balance within each state fund. STATE FUND NUMBER 0660 CSU FUND CD OBJ CD AMOUNT 310 506026 -4400970.01 310 680026 4402804.76 ------------- TOTAL AMOUNT FOR STATE FUND 0660 1834.75

Business Rule

Valid Values

• Makes sure all the values are valid and in the correct form

• Program Codes• Object Codes• Project Code• Data Elements

How to Fix an error of a Valid Value

• Fix error in CSU• If needed fix business rules in CMP

• Combo Edits• Rule 2• Rule 3• Rule 4

Business Rules-Accounting & Budget• These rules check that data complies to standard

business practice• Scholarship and fellowship program group can only use

financial aid object codes• FTE can only be on salary and wages object codes• Comparison Errors

• Due To/From = 0• Reimbursed Activity = 0• Campus Budget = Central Validation Table

Know where to find the CSU FIRMS Information

• http://www.calstate.edu/es/intranet/applications/fob/firms/documents/validation_rules.pdf

Validation Rules

Know where to find the CSU FIRMS

• This web-site gives definitions for all attributes found in the FIRMS files.

• http://www.calstate.edu/es/intranet/applications/fob/firms/firms-data-element-dictionary/index.shtml

Existing Tools to Combat FIRMS Edit Errors

• Enhancements were added in 2006-07 to assist with locating errors

• CSU_FIRMS_FILE Table

• Populated by the extract process, it shows the FIRMS file line number along with the campus local Fund, Account and Department that were summarized to create the line.

• +/- .01 FTE is added to the extract file when the FTE read by the extract is 0.00.

79797979

8080

FIRMS Edit Error Example - FTE

8080

8181

CSU_FIRMS_FILE Table

8181

8282

CSU_FIRMS_FILE vs. Posted LEDGER FTE

CSU_FIRMS_FILE Results

LEDGER Table Results

8282

• The +/- .01 FTE is added to the extract file when the FTE read by the extract is 0.00 for each combination of object code-CSU fund-program code.

• In the instance shown above, the campus will need to enter a journal to increase the FTE on the positive dollar line. 8383

Understanding the FTE Enhancement and How to Fix FTE Errors

8383

How do you find them before they happen?

• Comparison Errors -Know what the comparison is between

• Most comparisons involve campus data only – Due To/From, Transfers In/Out, A/R, and Reimb. Activity

• Comparisons using a central validation table: Fund Balance Clearing, Equity and Budget Validation

How do you find them before they happen?• Delivered queries to verify data follows business

rule for comparison• CSU_GL_DUETO_DUEFROM_RECON• March 2006 Release several queries added with

tables attached so results include SCO and CSU values

• Due To/From• Transfer In/Out

Error options• Accrue in XXCSU – reversing next period

• Process in XXCMP in next period

• Underive XXCSU• Process in XXCMP in Current Period• Re-derive

• If program code only• Process correcting journal in XXCSU Only• Correct Derivation rule in error for next time

What are the options for correcting?

• Depending on error• If a monthly “check” fix in XXCMP in the

subsequent month.• If a submission month make determination based

upon type of error• Ex. If the A/R Reserve account is more than the

actual A/R account fix in XXCSU as a reversal entry and then fix in XXCMP the subsequent month.

What are the options for correcting?

• A decision to reopen the BU is a decision made by the Financial Managers based upon the severity of the problem and how it will affect the reporting.

What are the options for correcting?

• At year end the production environment is cloned • Check errors in the clone• Once clean we close the BU and submit• Any errors after this point the business unit would

be opened to make corrections

www.calstate.edu