Grants Streamlined Detailed Universe Guide

1
Grants Streamlined Detailed Universe Guide

• May 2008

2
What is Grants data?
3
Grants Data General information

• Contains Banner Grants data beginning in July
  2003 to present
• Converted Grant data At June 30, 2003 budgets
  were entered into the system equal to expenses
  (rendering the balance available equal to 0).
  The remaining unexpended budget was entered in
  FY04 -- period 00 resulting in the correct
  balance available starting July 1, 2003
• All Banner Grants have a fund code that is the
  same as the grant code. Therefore, when listing
  funds on a grant, consider using a condition on
  the Financial Fund data entry indicator Y.
  This will help to ensure you obtain a list of
  6-digit data enterable funds

4
Grants Data General information

• Banner Grants data represents the efforts of
  University researchers. Please treat the
  available data with respect on behalf of the
  investigators and intellectual credit it
  represents.
• Data is loaded every evening-please verify
  finance data is loaded before running reports!
• http//www.ds.uillinois.edu/web/Home/AboutData/Dat
  a.aspx
• Grant data quality issues are documented in the
  Quick Reference Guide. Please review them
  carefully.

5
Grant Universe Overview
6
Finance Grants Streamlined Universe

• A universe to list Grants and answer questions
  like
• How many grant do I have associated with my
  department?
• What was the total grant expenditure by PI in my
  college over the last state fiscal year?
• How many grants are currently active for a
  Principal Investigator?
• On how many Grants is a particular Investigator
  currently listed?
• How many grant dollars did we spend last year
  from a sponsor?

7
Finance Grants Streamlined - Description

• This Universe contains
• C-FOAPAL
• Grant start end dates
• Grant budget, revenue expenses
• Grant sponsors/agencies
• Principal Investigator information
• C-FOAPAL reflects current information only

8
Finance Grants Streamlined Intended Uses

• Intended use(s) of this Universe
• Listings of Grants and related information
• Analysis of Grant Expenses
• Analysis of Grants by research area or sponsor

9
Finance Grants Streamlined Universe Limitations

• This Universe is NOT designed to
• Combine Operating Ledger transaction detail
  Grant Summary Ledger in the same query
• Original Document Number should only be used
  with the Grant Ledger Detail class

10
EDW - Finance Grants Streamlined Universe
11
Grants Data universe objects
Grant Code The Banner assigned code that
identifies each grant. Format is Q1234. Where
the first letter indicates a responsible campus
and the category of grant funding.
Principal Investigator Every Banner grant has an
assigned principal investigator. In addition,
grant funds also have an assigned principal
investigator and may also have co-investigators.
12
Grants Data - universe objects

• Grant Year
• Each grant has its own year based on its start
  date.
• NOTE The first fiscal period begins in the
  month and year of the start date.
• Relative Grant Year
• The year, starting at 1, of the grant relative to
  its start date.
• Grant Period
• A two-digit code corresponding to the month
  relative to the grant year.
• Period 00 of the first grant year summarizes
  pre-award activity
• Periods 01-12 are relative to the grant year
• Calendar Year
• The calendar year of a particular grant period.
  If this record is for pre-award activity (i.e.,
  where Grant Period is '00' for the first year of
  the grant) then calendar year is set to 'PRE'.
• Calendar Month
• The calendar month (2 digit number 01Jan,
  02Feb12Dec) corresponding to a particular
  grant period. If this record is for pre-award
  activity (i.e., where Grant Period is '00' for
  the first year of the grant) then calendar month
  is set to 'PRE'.

13
Grants Data Concepts
Say we have two grants Grant A starts in August
2003 Grant Period 01August 2003 Grant Period
02September 2003 Grant Period 03October
2003 Grant Period 04November 2003 Grant B starts
in November 2003 Grant Period 01November
2003 Grant Period 02December 2003 Grant Period
03January 2004 Selecting Grant Period 03 for
these two grants would select fiscal activity
from different periods of time. Selecting
Calendar Month 11 (November) and Calendar Year
2003 compares data from the same time period.
Tips Tricks When selecting Grant
Ledger data for several grants,
select by calendar year and
month, not grant period.
14
Grant Data universe object classes
Grant Ledger Detail There is no transaction level
data in the Grant ledger. All grants
transactions take place in the Operating Ledger
(which creates data in state fiscal year and
period) . Therefore, grant transaction detail
objects are identical to operating ledger detail
objects. Grant Ledger Summary The Banner
grant/research ledger contains summaries of
operating ledger transactions and groups them by
Grant period and grant year. USAGE NOTE It is
important to note that lack of activity on a
Grant CFOAPAL when crossing grant years will make
some CFOAPALs appear to drop out of the summary
data. Use the summary data with caution.
15
Grants Ledger - universe objects

• Grant Summary Ledger Dollar Buckets
• On the grant ledger you will find similar dollar
  buckets as on the Operating Ledger Summary
• Permanent Original Budget Period Total
• Permanent Budget Adjustments Period Total
• Temporary Budget Adjustments Period Total
• Revenue Period Total
• Expense Period Total
• Reservation Period Total
• Encumbrance Period Total
• NOTE Period totals are derived by subtracting
  the Banner ITD balance for the grant period from
  the ITD balance for the previous period

16
Grants Ledger additional data

• Grant Summary Ledger Additional Dollar Buckets
• On the grant ledger you will also find additional
  dollar buckets for ITD (Inception To Date).
  These columns are NOT currently available in the
  universe.
• Permanent Original Budget ITD Balance
• Permanent Budget Adjustments ITD Balance
• Temporary Budget Adjustments ITD Balance
• Revenue ITD Balance
• Expense ITD Balance
• Reservation ITD Balance
• Encumbrance ITD Balance

17
Grant Ledger YTD Buckets

• Grant BBA
• Grant Unencumbered BBA
• Grant Permanent Budget
• This is equal to the Permanent Original Budget
  plus Permanent Budget Adjustments.
• Grant Operating Budget
• This is equal to the Permanent Original Budget
  plus Permanent Budget Adjustments plus Temporary
  Budget Adjustments.
• Remember to specify a Year and a Period(s) when
  using these objects
• Note These amounts are only calculated for
  expense accounts

18
Grant Ledger YTD Buckets

• Grant BBA
• Grant Unencumbered BBA
• 2 Usage Caveats
• Remember Labor Encumbrances are calculated on the
  fiscal year. This includes grant labor
  encumbrances.
• Lack of activity on a Grant CFOAPAL when crossing
  grant years will make some CFOAPALs appear to
  drop out of the summary data
• Remember to specify a Year and a Period(s) when
  using these objects
• Note These amounts are only calculated for
  expense accounts

19
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 Ledger Summary
only calculated for expense accounts Remember
to specify a Year and a Period when using BBAs
20
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

21
Business Objects Desktop Intelligence Sample
Report
22
EDW - Finance Grants Streamlined Universe Example
Query

• Business Question I would like a listing of
  Grants in my unit by PI name with funds and start
  end dates

Helpful Hints A Grant code can have multiple
fund codes associated with it Utilize the Active
Grants predefined condition to obtain grants that
have not yet ended. Responsible Unit Organization
code denotes the unit associated with the Grant
NOT the O in CFOAPAL
23
Finance Grants Streamlined Universe Example
24
Finance Grants Streamlined Universe Example
25
Finance Grants Streamlined Universe
Make sure all three buttons are pressed. The
first button allows the Classes and Objects
window to be seen (usually depressed by
default).The second button allows you to see
the definitions for the objects that are
selected.The third button will permit selected
objects to wrap in the Result Objects window.
26
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
27
Objects in the Universe
The Grant Agency, Grant Persons, and Grants
folders contain all the basic information to list
grants. This includes data like sponsor name,
principal investigator, grant number, grant
title, responsible organizations, start and end
dates, grant fund type (federal, state, private,
etc), grant research area, etc.
The Grant Ledger detail folder contains operating
ledger transactions for budget, revenue, expense,
encumbrance, etc. amounts with state fiscal month
year as well as calendar month year.
The Grant Ledger summary folder contains summary
amounts from the Banner Grants ledger amounts
with state fiscal month year, calendar month
year, and grant month and year. Use this data
with CAUTION
28
Tips for Combining Objects in the Universe

You can use objects in the CFOAPAL folder with
most other objects in the universe Document
number should be used with Grant Ledger Detail
objects only Of the last two object classes, do
NOT combine Grant Ledger Detail and Grant Ledger
Summary objects in the same query. When using
period objects in the Grant Ledger Summary
folder, you will generally want to specify a year
and one or more periods. When using the following
objects (located in Grant Ledger Summary) BBA,
Unencumbered BBA, Permanent Budget, Operating
Budget, you MUST specify a Period AND a Year.
29
Grants Universe predefined condition
For the Grants Universe, we created a predefined
conditionActive Grants When you use this
condition, only grants with an End date greater
than or equal to todays date will be returned.
30
Tips for Conditioning in the Universe

If you want to place a condition on a single
Investigator (PI or CoI) name or UIN, use the
Principal Investigator UIN object in your
conditions. If you want to place a condition on
a particular grant(s), use the Grant Code object
in your conditions. If you want to see a list of
grants in your college or department, Responsible
College or Responsible Department. There is no
need to use them both. Choose one and combine it
with the Responsible Chart. If you want to
categorize your grants by the type of funding
(Federal, Private, State or Other) utilize the
Grant Fund Type Code object.
31
Consider your Question
Grants is most likely defined with Grant code
and title..
Choose a Responsible Chart and College or
Department.
I would like a listing of Grants in my unit by
PI, with funds and Start and End dates
Investigators can be Primary/Principal or Co.
Grant start and end dates are located under
GrantsgtGrant Dates
32
Finance Grants Streamlined Universe Example
33
Finance Grants Streamlined Universe Example
34
Finance Grants Streamlined Universe Results
35
Business Objects Universe Common Report
formatting
Use the slice dice panel apply sorts, breaks,
and sums. Once you have selected your format,
you must click the Apply button!
A to Z sort
Sum or Calculator
Section Break
USAGE NOTE Be sure you apply section breaks
BEFORE you apply the sum/calculator. If you
dont, you may get strange format results.
36
Saving your Document
37
3 Options for Saving your 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 Export to Repository
• 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.

38
Option 3 Export to Repository
Select File Select Export to Repository You
will then be able to retrieve this document from
any desk.
39
Retrieving your Document
40
To retrieve a Document
Select File Select Import from
Repository You will then be able to retrieve a
saved document.
41
Solution Library and Query Clearing House
42
When getting information from the Data Warehouse,
you have these options

• Write your own report from scratch
• Look for a pre-authored report to use or modify
  in
• Solution Library
• Query Clearinghouse

43
What is Solution Library?

• DS Solution Library
• Users asked for pre-assembled report samples to
  use as a starting point for learning and
  customization
• Frequently asked user reporting questions
• DS Authored step by step instructions and sample
  Business Objects reports designed to meet
  specific reporting needs
• Solution Library grows based on user feedback
• Solution Library link http//www.ds.uillinois.ed
  u/web/Home/SolLib.aspx

44
What is Query Clearinghouse (QCH)?

• DS Query Clearinghouse
• A place for university users to share report
  templates
• Any data warehouse user can post Business Objects
  files for others to use as a starting point
• QCH contains everything in the Solution Library
  and more!
• Share and work together!
• Query Clearinghouse link https//www.ds.uillinoi
  s.edu/Reports/Authentication/Login.aspx?ReturnUrl
  2freports2fQCH2fQCHBrowser.aspx

45
Accessing Solution Library
46
Accessing Solution Library
47
Available Solutions on the DS website
48
Available Solutions on the DS website
49
No DS Solution? Try Query Clearinghouse!
50
Press on the Login Using Bluestem
51
Query Clearinghouse
52
Query Clearinghouse - Finance
53
QCH Finance gt Grants
Try All Grants for a PI. Its a great report!
54
DS Resources Help with Report writing
55
What if there is no pre-authored report that
answers your question?

• Write your own report from scratch
• Look for a pre-authored report to use or modify
  in
• Solution Library
• Query Clearinghouse

56
Who ya going to call?
57
Who ya going to call?
Check for dates and times of scheduled monthly
practice labs (help sessions)
58
Who ya going to call?
Check for dates and times of scheduled monthly
practice labs (help sessions)
59
Email a FAC (Functional Area Coordinator)

• DSFAC_at_helpdesk.uillinois.edu

Have you emailed your FAC today?
59
60
But I really want to talk to you

• Beth Ladd Finance FAC
• hessgill_at_uillinois.edu

61
Appendix Handy Accounting Concepts
62
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
63
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
64
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)
65
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)