FIRMS Derivation Model Overview

1. FIRMS Derivation Model Overview

2. 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.

3. 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.

4. 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 • Three PeopleSoft Business Units

5. 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)

6. Peoplesoft Design Derivation Concept

7. CSU Derivation 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

8. CSU Derivation Rules 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

9. CSU Derivation Rules The NACUBO Program Code derivation rules are processed in a specific order for each account type

10. 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

11. 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

12. 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

13. Peoplesoft Design Derivation Concept Review

14. GAAP 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

15. Audit Tables 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

16. FIRMS Extracts 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

17. www.calstate.edu

18. Viewing and Confirming FIRMS Derivation Rules

19. Review • Run the attribute build • Rule 2 • Run Query • Compare with Spreadsheet • Rule 3 • Run Query • Compare with Spreadsheet • Rule 4 • Yearly

20. Review Rule 2 • Run query for current Rule 2 values • CSU_CDIP_RULE_2_REVIEW • Compare with Spreadsheet

21. Records in Rule 2 Review Query

22. Fields in Rule 2 Review Query

23. Review Rule 2

24. Compare results of Query to Systemwide Summary of Rule 2

25. Rule 2 Review – Query vs Spreadsheet

26. Rule 3 Review • Run Rule 3 Query • CSU_CDIP_FUND_PROC_TYPE • Compare with Spreadsheet

27. Records in Rule 3 Review Query

28. Fields in Rule 3 Review Query

29. Rule 3 Query Results

30. Rule 3 Spreadsheet of Rule 3

31. Rule 3 Review – Query vs Spreadsheet

32. www.calstate.edu

33. FIRMS Process

34. 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

35. 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

36. Open Accounting Period cont.

37. Open Accounting Period

38. 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

39. Derivation Run Control

40. 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

41. Journal Generate – Save Run Control – Save Time

42. Journal Generate 3 or 4 Journals

43. 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

44. Journals Generated

45. Journals Generated

46. Journals Generated

47. Journals Generated • Budget Journals • Contains information from the Ledger_Budg • Journal can be processed on-line

48. Journals Generated

49. 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

50. 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.