Ledgers OL GL Detailed Universe Guide

1. Ledgers OL GL Detailed Universe Guide Last update: February 2006 CFOAPALCFOAPAL

2. Finance Data Ed: OLGL DUG-2005 2 Section 1: Understanding Operating Ledger and General Ledger data

3. Finance Data Ed: OLGL DUG-2005 3 OL & GL Data � General information Banner Finance data began in July 2003 Each Banner FOAPAL segment is independent. This is by design. Do not attempt to validate FOAPAL combinations. There is not a guaranteed one-to-one relationship between former UFAS accounts and Banner CFOAPAL�s. Transaction date is the date used to post a financial document and its related transactions to the ledgers. Remember, a transaction date can be altered by a user at time of input. For example, future dating or back dating of transactions.

4. Finance Data Ed: OLGL DUG-2005 4 Financial Ledgers Overview

5. Finance Data Ed: OLGL DUG-2005 5 When you�re using the ledgers and financial transactions, an understanding of how financial posting works will help you understand how the data relates. Say you create a Journal Voucher because you need to move some state funds. You�ll create a journal voucher document in Banner and click the �Complete� button. The next time the posting process runs, your completed journal voucher will post (because, of course, you created your JV without any errors!). What happens during Financial Posting? The rule class code on the document, in this case JS1, tells Banner how to create detail records, for all the necessary ledgers. In this case the JV will affect the Operating (we used to say �subsidiary�) and General ledger. Detail records also called �accounting entries� for a transaction say how much to money to add or subtract from what c-foapals. For those of you with an accounting background you�ve probably guessed by now that these detail records will include the direct and indirect ledger entries. The rest of us will understand that the set of detail records for one document will always balance each other out. In a separate place the document as the user entered it is recorded; that the box above called �Posted Transaction�. At the same time summary information, I.e., the ledgers also update. So in the General Ledger summary data the debit and credit balance update for the same fund and account as were in the general ledger detail records. Likewise in the Operating Ledger summary data, all the affected balances update for the same C-FOAPALS as were in the operating ledger detail records. Now, why do you care about this� Basically, it�s this �using the detail record data for any ledger can be tricky unless you limit the data to be very narrow, say to a specific C-FOAPAL or specific document type. Go to detail data when you need to understand all of the accounting entries for a document or when you want to see the details of transaction that affected a particular C-FOAPAL over a set period of time. The Posted transaction information has only what the user entered so you won�t have to consider the indirect accounting entries. The Summary ledger data is great to find out totals for fiscal periods. Next Let�s talk about what summary ledger data you�ll find in the Data Warehouse.When you�re using the ledgers and financial transactions, an understanding of how financial posting works will help you understand how the data relates. Say you create a Journal Voucher because you need to move some state funds. You�ll create a journal voucher document in Banner and click the �Complete� button. The next time the posting process runs, your completed journal voucher will post (because, of course, you created your JV without any errors!). What happens during Financial Posting? The rule class code on the document, in this case JS1, tells Banner how to create detail records, for all the necessary ledgers. In this case the JV will affect the Operating (we used to say �subsidiary�) and General ledger. Detail records also called �accounting entries� for a transaction say how much to money to add or subtract from what c-foapals. For those of you with an accounting background you�ve probably guessed by now that these detail records will include the direct and indirect ledger entries. The rest of us will understand that the set of detail records for one document will always balance each other out. In a separate place the document as the user entered it is recorded; that the box above called �Posted Transaction�. At the same time summary information, I.e., the ledgers also update. So in the General Ledger summary data the debit and credit balance update for the same fund and account as were in the general ledger detail records. Likewise in the Operating Ledger summary data, all the affected balances update for the same C-FOAPALS as were in the operating ledger detail records. Now, why do you care about this� Basically, it�s this �using the detail record data for any ledger can be tricky unless you limit the data to be very narrow, say to a specific C-FOAPAL or specific document type. Go to detail data when you need to understand all of the accounting entries for a document or when you want to see the details of transaction that affected a particular C-FOAPAL over a set period of time. The Posted transaction information has only what the user entered so you won�t have to consider the indirect accounting entries. The Summary ledger data is great to find out totals for fiscal periods. Next Let�s talk about what summary ledger data you�ll find in the Data Warehouse.

6. Finance Data Ed: OLGL DUG-2005 6 General & Operating Ledger Detail [Review the slide] Questions? [Review the slide] Questions?

7. Finance Data Ed: OLGL DUG-2005 7 General & Operating Ledger Detail [Review the slide] Questions? [Review the slide] Questions?

8. Finance Data Ed: OLGL DUG-2005 8 General & Operating Ledger Summary [Review the slide] Questions? [Review the slide] Questions?

9. Finance Data Ed: OLGL DUG-2005 9 Operating Ledger Summary YTD �Buckets� [Review the slide] Questions? [Review the slide] Questions?

10. Finance Data Ed: OLGL DUG-2005 10 Calculating BBA�s in the EDW That�s what the question BBA columns will help you answer. You have a choice The first calculation here is the conservative route�it takes into account your encumbrances, what you�ve promised to pay for but haven�t yet, as well as your reservations, what you�re planning to spend. The Unencumbered BBA is more literal-what have you actually expended. That�s what the question BBA columns will help you answer. You have a choice The first calculation here is the conservative route�it takes into account your encumbrances, what you�ve promised to pay for but haven�t yet, as well as your reservations, what you�re planning to spend. The Unencumbered BBA is more literal-what have you actually expended.

11. Finance Data Ed: OLGL DUG-2005 11 Information about Labor Encumbrances That�s what the question BBA columns will help you answer. You have a choice The first calculation here is the conservative route�it takes into account your encumbrances, what you�ve promised to pay for but haven�t yet, as well as your reservations, what you�re planning to spend. The Unencumbered BBA is more literal-what have you actually expended. That�s what the question BBA columns will help you answer. You have a choice The first calculation here is the conservative route�it takes into account your encumbrances, what you�ve promised to pay for but haven�t yet, as well as your reservations, what you�re planning to spend. The Unencumbered BBA is more literal-what have you actually expended.

12. Finance Data Ed: OLGL DUG-2005 12 State Fiscal Year & Fiscal Period [Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?[Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?

13. Finance Data Ed: OLGL DUG-2005 13 Two types of Fiscal Periods [Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?[Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?

14. Finance Data Ed: OLGL DUG-2005 14 General & Operating Ledger transactions [Review the slide] Questions? [Review the slide] Questions?

15. Finance Data Ed: OLGL DUG-2005 15 Business Objects Sample Reports:

16. Finance Data Ed: OLGL DUG-2005 16 Finance Ledgers OL GL - Description This Universe contains the financial transactions (direct and indirect) after the application of the Banner rule codes and the Banner posting process. Individual transactions as well as summaries by period can be found here. All transactions are by Fiscal year and Fiscal period. C-FOAPAL reflects current information only [Review Slide][Review Slide]

17. Finance Data Ed: OLGL DUG-2005 17 Finance Ledgers OL GL � Intended Uses Intended use(s) of this Universe: Listing of financial transactions by C-FOAPAL within Fiscal year and period (similar to University standard Finance reports) Listing of financial persons and their campus address related to a particular F, O, or P Compute balances (Example: Revenue minus Expenses) Build similar totals as on the standard Finance reports [Review Slide][Review Slide]

18. Finance Data Ed: OLGL DUG-2005 18 Finance Ledgers OL GL � Universe Limitations This Universe is NOT designed to: Combine Operating Ledger & General Ledger in the same query Combine Operating Ledger detail and summary in the same query Combine General Ledger detail and summary in the same query [Review Slide][Review Slide]

19. Finance Data Ed: OLGL DUG-2005 19 Tips for Combining Objects in the Universe [Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?[Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?

20. Finance Data Ed: OLGL DUG-2005 20 Tips for Understanding Objects in the Universe [Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?[Review the slide] Now do the fiscal periods and �buckets� make sense? Questions? Would you like me to show you these ideas with a Business Objects report?

21. Finance Data Ed: OLGL DUG-2005 21 EDW - Finance Ledgers OL GL Universe Example Query 1: Business Question: I would like a listing of my expense transactions by CFOAP by Fiscal Period

22. Finance Data Ed: OLGL DUG-2005 22 Finance Ledgers OL GL Universe Example:

23. Finance Data Ed: OLGL DUG-2005 23 Finance Ledgers OL GL Universe Example:

24. Finance Data Ed: OLGL DUG-2005 24 Finance Ledgers OL GL Universe Example

25. Finance Data Ed: OLGL DUG-2005 25 Finance Ledgers OL GL Universe Example

26. Finance Data Ed: OLGL DUG-2005 26 Finance Ledgers OL GL Universe Results

27. Finance Data Ed: OLGL DUG-2005 27 EDW - Finance Ledgers OL GL Universe Example Query 2: Business Question: I would like a listing of my cash transactions by Fiscal Year and Period

28. Finance Data Ed: OLGL DUG-2005 28 Finance Ledgers OL GL Universe Example

29. Finance Data Ed: OLGL DUG-2005 29 Finance Ledgers OL GL Universe Example

30. Finance Data Ed: OLGL DUG-2005 30

31. Finance Data Ed: OLGL DUG-2005 31 What is QCH? DS Query Clearinghouse A place to share report templates Any EDW user can post Business Objects files for others to use as a starting point Share and work together! Query Clearinghouse link: https://www.ds.uillinois.edu/Reports/Authentication/Login.aspx?ReturnUrl=%2freports%2fQCH%2fQCHBrowser.aspx

32. Finance Data Ed: OLGL DUG-2005 32 Query Clearinghouse

34. Finance Data Ed: OLGL DUG-2005 34 Resources for Data Warehouse Users DS Monthly Practice Labs On each physical campus FAC from Student, Finance, and HR present to answer questions and provide hands on assistance in creation of custom reports View Practice Lab dates: http://www.ds.uillinois.edu/events.asp

35. Finance Data Ed: OLGL DUG-2005 35 Resources for Data Warehouse Users

36. Finance Data Ed: OLGL DUG-2005 36 How to Contact DS By filing an on-line Help Desk case http://onlinesupport.uillinois.edu/ds.html. To complete the form: Authenticate via Bluestem Click "Start Here" on the left-hand margin Select "New Case"� Be sure "Decision Support" is listed on Help Desk line (first line of New Case entry form). If it does not, click "Change Help Desk" and select Decision Support from the list of options. Enter your contact information and the nature of your problem Click "Create Case" to submit the case to Decision Support for response Create a Help Desk Ticket Phone the AITS Help Desk: Chicago: (312) 996-4806 Urbana: (217) 333-3102 Springfield: (217) 333-3102 Email the AITS Help Desk: helpdesk2@uillinois.edu (one address for all 3 campuses to use) If you have access to Clarify, Send a Clarify Ticket directly to the �Decision Support� Queue�we�ll take it from there; the appropriate person will respond.

37. Finance Data Ed: OLGL DUG-2005 37 Appendix 2: Handy Accounting Concepts

38. Finance Data Ed: OLGL DUG-2005 38 General Ledger Concepts My version of CFOAPAL! � Accounting info � General Ledger My version of CFOAPAL! � Accounting info � General Ledger

39. Finance Data Ed: OLGL DUG-2005 39 General Ledger Concepts My version of CFOAPAL! � Accounting info - General Ledger T accounts My version of CFOAPAL! � Accounting info - General Ledger T accounts

40. Finance Data Ed: OLGL DUG-2005 40 Operating Ledger Concepts My version of CFOAPAL! � Accounting info � Operating Ledger My version of CFOAPAL! � Accounting info � Operating Ledger

41. Finance Data Ed: OLGL DUG-2005 41 Operating Ledger Concepts My version of CFOAPAL! � Accounting info - Operating Ledger T accounts My version of CFOAPAL! � Accounting info - Operating Ledger T accounts

42. Finance Data Ed: OLGL DUG-2005 42 Appendix 3: Handy Reference Materials

43. Finance Data Ed: OLGL DUG-2005 43

44. Finance Data Ed: OLGL DUG-2005 44 Original Document Number � What do those letters mean? Standard Banner document codes have single prefixes: R Requisition P Purchase/Change Order I Invoice J Journal Voucher S State 1099 Invoice/Proposal F Interface Document Number (often payroll documents) T Origination Tag Num (Fixed Assets) E Encumbrance Number L Budget Line Item P Permanent Tag number Y Receiving G Deferred Grant Calculations JV

45. Finance Data Ed: OLGL DUG-2005 45 Original Document Number � What do those letters mean? The GL Feeders have two prefixes: PCARD PC PCard Transactions DEFICENC IC Def IDC Encumbrance 1BECKMAN BI UIUC Beckman Institute Billing 1CBUDGET BU UIUC Central Budget 1CHEM CH UIUC Chem Stores 1CITESIT CT UIUC CITES Information Technology 1CSTORE1 GS "UIUC Central Stores, General" 1ELECENG EE UIUC Electrical & Computer Eng 1MECHENG ME UIUC Mechanical Engineering 1OPSPHTO PH UIUC Printing Services- Photo Store 1OPSQCC QC UIUC Printing Services- Quik Copy C 1PCM1 CP UIUC PC&M Carpool 1PCM2 PJ UIUC PC&M Champs 1VMHOSP VM UIUC VetMed Hospital/Clinic 1VMSTOR VH UIUC VetMed Hospital Storeroom

46. Finance Data Ed: OLGL DUG-2005 46 Original Document Number � What do those letters mean? The GL Feeders have two prefixes: 2AUXACCT FS "UIC Aux Services, Accounting" 2AUXCASH CA "UIC Aux Services, Cashiering" 2AUXSERV PS "UIC Aux Services, Publications" 2CBUDGET BC UIC Central Budget 2HOSP1 HM UIC Medipac-Hospital;MileSquare 2HOSP2 HP UIC Hospital-Payroll Reclass; Pay A 2HOSP3 HA UIC Hospital-Payable Accruals 2HOSPBUD BH UIC Hospital Budgets 2HOSPMM MM UIC Hospital Materials Mgmt 2MAIL MS UIC Mailing Center 2MSP1 TV UIC Medical Service Plan (MSP) 2MSP2 KV UIC Medical Service Plan (MSP) 2MSP3 SQ UIC Medical Service Plan (MSP) 2OFFSUPL OS "UIC Aux Services, Office Supply" 2PHYSPL1 JP UIC Physical Plant 2PHYSPL2 MP UIC Motor Pool 2TELECOM TL UIC Telecom

47. Finance Data Ed: OLGL DUG-2005 47 Original Document Number � What do those letters mean? The GL Feeders have two prefixes: 4CAMSVCS CS UIS Campus Services 4CBUDGET BS UIS Central Budget 4UISTLCO ST UIS Telecom 9ALASCD AC UI Grants Allocations 9ALASIC AI UI Grants Assessments 9CBUDGET BA UI Central Budget 9NDOWBUD BE University Endowment 9OBFSCCR CD UI OBFS CDRecon 9OBFSCRD CC UI OBFS Cybercash 9OBFSFEE CF UI OBFS Student Credit Card Conveni 9OBFSINV ID UI OCMI Investment & Banking Activi 9OBFSLN1 LU UIUC OBFS Student Loans ECSI 9OBFSLN2 LC UIC OBFS Student Loans AFSA 9OBFSLN4 LS UIS OBFS Student Loans ECSI 9STCLRGS SC UI OBFS State Clearing 9UIF FD UIF FFAS 9UIFBUD BF UIF Budget For a complete & up to date listing of document feeder codes, please visit the OBFS website: http://www.obfs.uillinois.edu/banner/index.htm

48. Finance Data Ed: OLGL DUG-2005 48 Fund & Budget detail information Generally speaking, budgets (revenue and/or expense) are assigned to Funds based on fund type. There are no absolute rules, but here are some guidelines: State Funds � expense budgets ICR Funds � sometimes expense budget sometimes expense & revenue budget Revolving Funds � expense & revenue budget that are equal Grants Funds � expense & revenue budget that are equal Bottom line: When you are listing detailed budget transactions, be sure you include the account section of the CFOAPAL so you can accurately evaluate the data!