Ledgers OL GL Detailed Universe Guide

1
Ledgers OL GL Detailed Universe Guide

• Last update February 2006

2
Section 1 Understanding Operating Ledger and
General Ledger data
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
  CFOAPALs.
• 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
Financial Ledgers Overview
See Appendix 2 for additional info
Operating Ledger Tracks budget, revenue, and
expenses over a state fiscal year Source of the
Universitys operating statement General Ledger
Tracks assets, liabilities, and fund
balance Source of the Universitys balance
sheet Source of depreciation Only place Cash
recorded Encumbrance Ledger Tracks by
encumbrance document what the University has paid
against what it owes
5
Banner Posting Process
2. Post Detail
3. Post Summary
1. Create Document
Operating Ledger Detail AND General Ledger
Detail sometimes Encumbrance Ledger
Detail Document TypeJV J00000089 Rule Class
JS1 Processing Code Accounting Line Date Fiscal
Year/Period D/C//- C-FOAPAL (not always
same) Sequence Number Op. Ledger
Only Amount
General Ledger Summary Chart Fund Account
(not always same) Fiscal Year/Period Debit
Balance Credit Balance
Accounting Entries based on Rule Class
Journal Voucher J00000089 Rule Class JS1JV
State Funds Accounting Line (transactions
per doc) Date Fiscal Year/Period D/C//- Chart F
und Organization Account Program Activity Location
Amount Description Other Data
Operating Ledger Summary C-FOAPAL (usually
same) Fiscal Year/Period Sequence Number YTD
Balances Original Budget Budget
Adjustments YTD Activity Encumbrances
Purchase Reqs
Sequence Number determined by Rule Class
Validation based on Rule Class
6
General Operating Ledger Detail
Individual transactions are found in the Ledger
detail data

• General Ledger
• Debits Credits (you need to know the normal
  balance of each type of account)
• Operating Ledger
• Permanent Original Budget
• Permanent Budget Adjustments
• Temporary Budget Adjustments
• Revenue
• Expense
• Reservation
• Encumbrance
• USAGE NOTES I suggest a condition on Fiscal
  Year, Fiscal Period and some parts of the CFOAPAL

7
General Operating Ledger Detail
Some general information for consideration when
utilizing transaction detail data

• General Ledger
• When writing queries against general ledger data,
  CFOAPAL conditions should generally be placed on
  the Chart, Fund and Account segments of the
  CFOAPAL.
• There are over 70 million general ledger
  transactions. You can reduce the amount of time
  it will take your query to return results if you
  condition on some segment of time. Generally,
  one fiscal year and one or more fiscal
  periods/months is advisable.
• Operating Ledger
• When writing queries against operating ledger
  data, CFOAPAL conditions may be placed on any of
  the CFOAPAL segments. Generally, one should
  always have a condition on Chart and at least one
  other segment.
• There are over 20 million operating ledger
  transactions. You can reduce the amount of time
  it will take your query to return results if you
  condition on some segment of time. Generally,
  one fiscal year and one or more fiscal
  periods/months is advisable.
• When looking at budget transactions, remember
  that some types of funds have budgets posted to
  expense accounts and some types of funds have
  budget transactions posted to revenue and expense
  accounts. You should consider a condition on
  some range of accounts to ensure you are not
  double counting.

8
General Operating Ledger Summary

• Summaries by CFOAPAL, Fiscal Year Period
  (month) are found in the Ledger Summary data
• General Ledger
• Debit Credit Period Totals
• Operating Ledger
• Permanent Original Budget Period Total
• Permanent Budget Adjustment Period Total
• Temporary Budget Adjustment Period Total
• Revenue Period Total
• Expense Period Total
• Reservation Period Total
• Encumbrance Period Total

9
Operating Ledger Summary YTD Buckets
Additional summaries by CFOAPAL, Fiscal Year
Period (month) in ledger summary data

• YTD Revenue
• YTD Expense
• YTD Encumbrance
• YTD Reservation
• BBA
• Unencumbered BBA
• Permanent Budget
• This is equal to the Permanent Original Budget
  plus Permanent Budget Adjustments.
• Operating Budget
• This is equal to the Permanent Original Budget
  plus Permanent Budget Adjustments plus Temporary
  Budget Adjustments.
• USAGE NOTES You must condition on a Fiscal Year
  and a Fiscal Period when using these objects
• USAGE NOTES BBA, Unencumbered BBA, Permanent
  Budget, Operating Budget amounts are only
  calculated for expense accounts (not revenue
  accounts)

10
Calculating BBAs in the EDW
2 Budget Balance Available (BBA) calculations
BBA PERMANENT ORIGINAL BUDGET PERMANENT
BUDGET ADJUSTMENTS TEMPORARY BUDGET
ADJUSTMENTS -YEAR TO DATE EXPENSES - ENCUMBRANCES
- RESERVATIONS  UNENCUMBERED_BBA PERMANENT
ORIGINAL BUDGET PERMANENT BUDGET ADJUSTMENTS
TEMPORARY BUDGET ADJUSTMENTS -YEAR TO DATE
EXPENSES Available in the Operating Ledger
Summary only calculated for expense
accounts Remember to specify a Fiscal Year and
a Fiscal Period(s) when using BBAs Note This
calculation differs from the one used on the
University Standard Reports. That calculation
uses Accounted Budget and includes a revenue
component.
11
Information about Labor Encumbrances

• Banner encumbers labor on the State Fiscal year
  July 1st thru June 30th
• Exception is a University modification to Banner
  for 9 over 12 salaried employees that sets
  encumbrance end dates to June 15th
• Labor encumbrances represent academic salaried
  civil service employees
• Labor encumbrances for salaried employees will
  end on June 15th or 30th regardless of a Fund end
  date
• Example For a fund related to a grant, the labor
  obligation will generally begin on July 1st and
  end on June 30th - regardless of the grant dates.
  
• Generally speaking, HR Labor encumbrances are
  performed by Banner as follows
• Start date is the latest of
• Beginning of the State Fiscal Year (July 1)
• Job Start date
• End Date is the earliest of
• Ending of the State Fiscal Year (June 15th or
  30th)
• Job End date

12
State Fiscal Year Fiscal Period
General, Operating and Encumbrance Ledger Fiscal
Year 2005 four-digit year(s) General,
Operating and Encumbrance Ledger Fiscal
Periods 00 trail-in, such as rollover
balances from previous year 01-12 the twelve
monthly periods - where 01 July and 12 June 14
a period open after period 12 closes (to regular
activity) that will hold the final adjustments to
the fiscal year.
13
Two types of Fiscal Periods
Operating, General, and Encumbrance Ledger Detail
Periods Posted Period--The two-digit State
Fiscal Period Code to which this Operating Ledger
detail transaction was posted Budget Period-- A
two-digit period indicating when a budget amount
is available (as opposed to the period
posted) Example used when future dating budget
transactions
14
General Operating Ledger transactions

• Where can I find a universe to list Budget,
  Revenue, Expense, Reservation and Encumbrance?
• Listings of Expenses by CFOAP
• Permanent Original Budget by CFOAP
• Budget Adjustments by Fiscal year
• Journal Voucher transactions where my
  Organization was used?

15
Business Objects Sample Reports
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

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

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

19
Tips for Combining Objects in the Universe
You can use objects in the CFOAPAL folder with
any other objects in the universe Document
number should be used with Operating Ledger
Detail or General Ledger Detail objects Of the
last four object classes, use objects from one at
a time. For example, use CFOAPAL with Operating
Ledger Detail Objects or use CFOAPAL with
Operating Ledger Summary objects. Do NOT combine
Operating Ledger Detail and Operating Ledger
Summary objects in the same query. When using
period objects in either of the Ledger Summary
folders, you will generally want to specify a
Fiscal year and one or more Fiscal periods. When
using the following objects (located in Operating
Ledger Summary) YTD Revenue, YTD Expense, YTD
Encumbrance, YTD Reservation, BBA, Unencumbered
BBA, Permanent Budget, Operating Budget, you MUST
specify a Fiscal Period AND a Fiscal Year.

20
Tips for Understanding Objects in the Universe
Toggle this button
When you single click to highlight an object,
there is often a definition of the object as well
as usage tips. (see definition box below). To
reveal this definition box, toggle it open by
pressing the button with the little blue i in
the upper left hand corner of your query panel.
Reveal object definitions usage tips
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

Helpful Hints Expenses can be found in the
Operating Ledger or OL Operating Ledger
transactions can use all parts of the
CFOAPAL Original Document number will give you
the Banner document that originated the
transaction
22
Finance Ledgers OL GL Universe Example
23
Finance Ledgers OL GL Universe Example
24
Finance Ledgers OL GL Universe Example
25
Finance Ledgers OL GL Universe Example
26
Finance Ledgers OL GL Universe Results
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

Helpful Hints Cash can be found in the General
Ledger or GL General Ledger transactions only
require the Chart, Fund, and Account parts of the
CFOAPAL Original Document number will give you
the Banner document that originated the
transaction
28
Finance Ledgers OL GL Universe Example
29
Finance Ledgers OL GL Universe Example
30
Finance Ledgers OL GL Universe Example
Want to play with these sample reports? Want
something similar for your use? Then go visit
Query ClearingHouse and get a sample report with
which to begin!
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.uillinoi
  s.edu/Reports/Authentication/Login.aspx?ReturnUrl
  2freports2fQCH2fQCHBrowser.aspx

32
Query Clearinghouse
33
Appendix 1 DS Resources Contact Information
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
Resources for Data Warehouse Users
For additional documentation and information
about universes, objects, tables, loading of EDW
data, training, data models, and metadata search,
please visit the DS website http//www.ds.uillin
ois.edu/web/Home/Metadata.aspx
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 [phone]
• Urbana [phone]
• Springfield [phone]
• Email the AITS Help Desk
• helpdesk2_at_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 Queuewell take it from there the
  appropriate person will respond.

37
Appendix 2 Handy Accounting Concepts
38
General Ledger Concepts
Accounting concepts in laymans terms

• Assets Things that we have or own that add
  value to the University. Some examples are
• Cash
• Accounts Receivable ( people owe us)
• Land Buildings
• Liabilities Things that the University owes.
  Some examples are
• Accounts Payable ( we owe people)
• Loans
• Fund Balance
• Sum of all Revenue, Expense, Transfers, Fund
  Additions and Deductions

FORMULA Assets Liabilities Fund balance
39
General Ledger Concepts
Accounting concepts in laymans terms

• In Banner, the General Ledger works with Debits
  and Credits.
• Each type of account has a Normal Balance
  indicator to tell you whether you are adding to
  or subtracting from its value.

Fund Balance
Asset
Liability
Debit (more)
Credit (less)
Debit (less)
Credit (more)
Debit (less)
Credit (more)
FORMULA Assets Liabilities Fund balance
40
Operating Ledger Concepts
Accounting concepts in laymans terms

• Income or Revenue Money that the University
  receives or will receive. Some examples are
• Tuition Fees
• Sale of products or services
• Gifts
• Expenses Things that the University buys or
  pays for. Some examples are
• Office Supplies
• Payroll

FORMULA Income Expenses 0 (Profit or Loss)
41
Operating Ledger Concepts
Accounting concepts in laymans terms

• In Banner, the Operating Ledger works with and
  -.
• Each type of account has a Normal Balance
  indicator to tell you whether you are adding to
  or subtracting from its value.

Income
Expense
- Debit (less)
Credit (more)
Debit (more)
- Credit (less)
In this case, a means less of. Do not
confuse less of with negative. They are not the
same in the world of accounting.
FORMULA Income Expenses 0 (Profit or Loss)
42
Appendix 3 Handy Reference Materials
43
3 Options for Saving your Business Objects
document

• Option 1 File gt Save
• This is the Business Objects default. This saves
  the document to your hard drive. The location is
  under My Documents gt My Business Objects
  Documents gt User Docs
• Option 2 File gt Save As
• This allows you to choose the location to save
  the document. This location could be on your
  hard drive, on a CD or USB drive or on a network
  share, for example. This is also the option used
  to save to a different file format like an
  Excel .xls file.
• Option 3 File gt Send To gt Personal Documents
• This sends the document to be stored on the
  Decision Support server. You can then retrieve
  the document from any location or computer.
• This option is very useful when you have created
  the document at a workstation that is not your
  own and you want to be able to retrieve it back
  at your desk or you create the document at your
  desk and want to be able to retrieve it in a lab
  setting.

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
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 PCM Carpool
• 1PCM2 PJ UIUC PCM Champs
• 1VMHOSP VM UIUC VetMed Hospital/Clinic
• 1VMSTOR VH UIUC VetMed Hospital Storeroom

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-HospitalMileSquare
• 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

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

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!