Consolidated Expenditure Reporting (CER) Dashboard
The CER dashboard displays expenditure details using data from Oracle Grants Accounting (GA), iJournals, Consolidated Budget (Cardinal Planning & Budgeting (CPB)), iBudgets, GA Budgets (Sponsored and Capital Projects) and Hard Commitments. The dashboard provides details on expenses that have been incurred over a period of time for analysis, reconciliation and comparison to plan.
Launch OBI Consolidated Expenditure Reporting Dashboard
Select this link... | ...for reports about |
---|---|
Expenditure Balance | Summary and detail on budgeted vs actual expenditures with the remaining balance. Hard commitments can be optionally included to calculate a projected remaining balance |
Monthly Expenditure Details Statement | Non-customizable, BI Publisher summary of fiscal-to-date/project-to-date (FTD/PTD) expenditures containing details of the latest month, with budget, commitment and remaining balance (formerly the RM3 FIN_EXP_285 report). |
Expenditure Award to Date and Award Detail | Summarized report of budget, actual expenditures, prepaid expenses and commitments (equivalent to the RM3 FIN_EXP_179). |
Expenditure Trend | Summary and detail of expenditures pivoted by time periods such as GL period, fiscal quarter and fiscal year. |
Expenditure Variance | Summary and detailed comparison of total expenditures incurred between two ranges of dates. |
Expenditure Details | Summary and detail of total expenditures for the fiscal year to date, project to date or between any two GL Periods |
Expenditure Details Data Download | Expenditure Details Data Download with the exact same fields and sequence as in RM3 279 download report (formerly the FIN_EXP_279). |
Sponsored Awards Actual Expenditures - by MTDC, Exclusions, Indirect Expense | Sponsored Awards Expenditure amount by MTDC (equivalent to the RM3 FIN_EXP_109). |
iJournals | Summary and Detail including approval history for feeder and iJournals transactions. |
Commitment Details | Expenditure Hard Commitment (requisitions, purchase orders (POs), invoices and salary) transaction details. |
Expenditure Balance
Displays budget, expenditures and remaining balance as of the specified GL Period for the Fiscal Year to Date or Project Year to Date based on your selection criteria. Hard commitments can be optionally included to calculate a projected remaining balance.
Demo of Expenditure Balance reports within the CER Dashboard in OBI
Sample Business Questions
- Are actual to date expenses in alignment with the annual budget?
- What is the projected remaining balance, with or without commitments?
- What are the actual expenditures affecting large variances from budget?
- What are the actual expenditure trends FTD/PTD and what is the budget incremental variance for these time frames?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
- For Include Budget Sources, the Budget Management Tool refers to the annual Consolidated Budget created in Cardinal Planning & Budgeting (CPB), Grants Accounting refers to sponsored project budgets and capital project budgets, and iBudgets refers to mid year adjustments created in the iBudgets system.
- Commitments data is only returned if the ending GL Period requested is the last closed or current open period.
- Non-sponsored awards will return FTD budget and actual expenditures, regardless of the Period Selection chosen.
- For sponsored awards, use Project To Date for the period selection to prevent potentially misleading budget amounts.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
Expenditure Balance Summary
There are two reports for the Expenditure Balance Summary section. Both reports compare summarized expenditures up to the final specified date for FTD or PTD, displays the balance, and if including commitments, the projected remaining balance.
Expenditure Balance Summary
The Expenditure Balance Summary is a pivot table that displays summarized expenses for the period specified; shows summarized expenses to budget for FTD or PTD; and calculates the balance and percentage of budget consumed. The summary will provide total salary expenditures, even if you do not have salary access. Refer to the following screenshots.
- To include hard commitments to show projected expenditures, balances and budget consumption, use the Budget with Expenditure and Commitment view.
- For reconciliation by PTA, use the Expenditure Reconciliation view.
Expenditure Balance Summary Trend by GL Period
The Expenditure Balance Summary Trend by GL Period displays a monthly trend of expenditures and includes the budget, commitments and both the remaining balance and projected remaining balance.
- The trend can be displayed by GL Period Name or by GL Fiscal Year.
Budget Incremental Variance Graph and Summary by GL Period
By month, displays the incremental budget, actual expenditures, incremental variance, and FTD/PTD running incremental variance.
- In the selection criteria, uncheck the Display Budget Variance Graph checkbox to prevent this section from displaying.
If there are negative budget adjustments, the grand total for Budget Total Amount may not be accurate.
Expenditure Balance Details
The Expenditure Balance Details section displays the detailed expense data that supports the summary reports listed above. If you do not have salary access, salary detailed data will not be included. In this section, you can include the column Expenditure Transaction Long Description to see the full business purpose and PO description. Refer to the following screenshots.
For reconciliation with your Expenditure Detail report, choose the GL Period Names for your selection criteria and use the Expenditure Reconciliation view.
Understanding the Data
Period Selection Impact on Results Displayed on Summary Reports
The two Balance Summary reports and the Budget Incremental Variance Summary by GL Period will display budget values that do not correspond to the Period Selection (PTD/FTD) chosen based on the type of award.
- For non-sponsored, the budget and the actuals will always display FTD, regardless if PTD is selected. This is consistent for all the summary reports.
- For sponsored, the budget displayed will be PTD regardless if FTD is selected for the two Balance Summary reports. It will display the budget as FTD for the Budget Incremental Variance Summary by GL Period.
The tables below summarize the results based on sponsored vs. non-sponsored and the time period selected.
Exporting to a CSV file
When exporting the Expenditure Balance Details by PTAE data into a csv file, the data in the fields Expenditure Item Account Reference Text and Expenditure Item Department Reference Text will be hyperlinks, as these are drillable fields in the report. To retrieve the actual reference text, use the fields Expenditure Item Account Reference Plain Text and Expenditure Item Department Reference Plain Text when analyzing your data.
- For a list of fields available to include in report customizations, refer to Data Fields Available in CER Dashboard.
- To understand how expenditure types roll up into higher level groupings and how they compare to object codes and consolidated budget rollups, refer to CER Expenditure Type Roll-ups.
- For information about how the different commitments are created, refer to Commitment Management.
Monthly Expenditure Details Statement
Non-customizable, BI Publisher summary of Fiscal-to-Date(FTD) and/or Project-to-Date (PTD) expenditures containing details of the latest month, with budget, commitment and remaining balance. This report is similar to the RM3 FIN_EXP_285 report.
Sample Business Questions
- Are actual to date expenses in alignment with the annual budget?
- If hard commitments are included, what is the projected remaining balance?
- What are the actual expenditures affecting large variances from budget?
Selection Criteria
- The Selection Criteria section is located on the left hand side of the screen. Fill all fields with either the % wildcard character or specific values for selection criteria. You must enter a date parameter near the bottom, either Fiscal-To-Date, Project-To-Date or Period Begin and End Date.
- For additional information, refer to BI Publisher Reports.
Report Views
You can select All Detail (salary and non-salary), Non-Salary Detail or Compensation by Person.
Expenditure Award to Date and Award Detail
Summarized report of budget, actual expenditures, prepaid expenses and commitments (equivalent to the RM3 FIN_EXP_179). This report is owned and supported by the Office of Research Administration.
Sample Business Questions
- What are the actual to date expenses for a principal investigator’s (PI’s) portfolio of awards and what are the remaining balances with respect to their budgets?
- When factoring hard commitments, what are the projected remaining balances?
Selection Criteria
- To retrieve results, follow the Selection Criteria instructions near the top of the screen.
Report Views
The Expenditure Award to Date Expenditure and Award Detail report displays summarized budget compared to actual expenditure by award.
- For sponsored and plant accounting awards, budget (column A) and actual expenditure (column B) will be Project-to-Date.
- For non-sponsored awards, budget and actual expenditure will be Fiscal-to-Date. A graph of budget and actual expenses over time is displayed by individual award.
Expenditure Trend
Summary and detail of expenditures by time periods such as GL period, quarter and year. It allows you to analyze changes in expenditures over time.
Demo of Expenditure Trend reports within the CER Dashboard in OBI
Sample Business Questions
- What was the spending pattern for my organization, award or PTA over the periods specified?
- What are the patterns in my expenditures over time (e.g., trending up, trending down, erratic)? By Expenditure High Level Group? By Tasks within my Award?
- Are there unusual expenditures in this period (e.g., fiscal quarter, fiscal year) compared to the same period last year and what are the sources?
- What is my rate of spending for the period specified (e.g., burn rate)?
- What are my PIs expenditures by year for the last 3 years?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
The Expenditure Trend reports allow you to view in summary and detail expenditure amounts grouped in columns by the time period you specify, allowing you to analyze changes in expenditures over time.
Expenditure Trend Summary
The Expenditure Trend Summary section is a pivot table that displays summarized expenses for the time range you specify, allowing you to analyze changes in expenditures over time.
Expenditure Trend Detail
The Expenditure Trend Detail section displays more detailed expense data that supports the Expenditure Trend Summary.
- These reports can be run for any range of period, even across fiscal years.
- You can view by fiscal period, fiscal quarter and year, calendar quarter and year and project or fiscal year to date.
- The two reports can be viewed using the same or different period buckets.
Understanding the Data
- For a list of fields available to include in report customizations, refer to Data Fields Available in CER Dashboard.
- To understand how expenditure types roll up into higher level groupings and how they compare object codes and consolidated budget rollups, refer to CER Expenditure Type Roll-ups.
Expenditure Variance
View and compare actual expenditures for any two General Ledger (GL) period ranges by amount and percentage.
Demo of Expenditure Variance reports within the CER Dashboard in OBI
Sample Business Questions
- What are my actual expenditures in this quarter compared to the same quarter last year?
- What are my actual expenditures in this time period (e.g., fiscal month, fiscal quarter, fiscal year) compared to another time period?
- How do my expenditure totals to date compare to the previous year's totals?
- What are the largest sources of variances that require further analysis (for example, for high-risk expenditure categories)?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
Values must be entered for all four date period prompts. The first date range must be earlier than the second date range for the report to run properly.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
The Expenditure Variance reports allow you to compare the summary and detail expenditure amounts incurred between the two date ranges entered. The calculated variance amount and variance percent are displayed.
Expenditure Variance Summary
The Expenditure Trend Summary section is a pivot table that displays summarized expenses for the time ranges you specify, allowing you to compare expenditures and calculates the variance.
Expenditure Variance Detail
The Expenditure Trend Detail section displays more detailed expense data for the two time periods for comparing and variance analysis.
For both reports, comparison can only be done between two date ranges, and not between different organizations, PTAs or SPOs.
Understanding the Data
For a list of fields available to include in report customizations refer to Data Fields Available in CER Dashboard.
To understand how expenditure types roll up into higher level groupings and how they compare object codes and consolidated budget rollups, refer to CER Expenditure Type Roll-ups.
Expenditure Details
The Expenditure Details report contains details on expenditures within a range of GL periods. This report can be used for expenditure review and reconciliation.
Demo of Expenditure Detail reports within the CER Dashboard in OBI
Sample Business Questions
- What has been charged to my PTAs?
- What are my total expenditures across organizations for a parent organization?
- What are my actual expenditures by expenditure type or expenditure category? Award type? Award or SPO number?
- What are my actual expenditures by task manager or task/award owner (PI)?
- Which of my task managers has the largest expenditures in a high-risk expenditure category (e.g., travel/entertainment/food)?
- What are the transactions that I need to reconcile by source system?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
- Transactions displayed can be for Fiscal Year-to-Date, Project-to-Date or between a Beginning and Ending Period. Last Closed designates the most recent closed fiscal period.
- Expenditure Transaction Source Long Name allows you to select expenditures related to the type of transaction or module interface. You can further refine your search by entering specific text in Expenditure Item Account Reference Text or Expenditure Item Department Reference Text. For more information, refer to the Source Code Reference Table. Using these selection criteria fields affects only the data displayed in the Expenditure Detail by PTAE section and will not affect what is displayed in the Expenditure Summary report section.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
Expenditure Summary
The Expenditure Summary report is a pivot table that displays high level totals for the date range selected. It subtotals by Expenditure High Level Group Description by default, but you can select other aggregate levels to summarize the expenditures. The summary will provide total salary expenditures, even if you do not have salary access. Refer to the following screenshot.
By selecting one of the Consolidated Budget Reporting CoA levels, you can summarize your expenses to the same level needed for the university budget tool.
Expenditure Detail by PTAE
The Expenditure Detail by PTAE report displays the detailed expense data by Project-Task-Award-Expenditure (PTAE) that supports the Expenditure Summary report. Each expenditure line will typically have one or more fields that you can drill on to find additional details about the transaction. If you do not have salary access, salary detailed data will not be included. Refer to the following screenshots.
You can include the column Expenditure Transaction Long Description to see the full business purpose and purchase requisition description.
Table view allows you to easily export the report section into an Excel file that will have the same columns as displayed.
The legacy view contains columns in the same sequence as the RM3 FIN_EXP_279 report.
This view summarizes salary expenses by Job Classification Subtype Code and Job Classification Codes.
This view is designed to use when doing monthly reconciliation. It splits the report into sections based on PTA, uses Expenditure Transaction Long Description and for transaction date uses Reporting Invoice or Expenditure Date. If the Expenditure Transaction Source is AP Invoice, then the date is the Invoice Date, for all other transactions it is the Expenditure Item Date.
Understanding the Data
Expenditure Dates
Expenditure Item Date refers to the date a good was originally received or a service originally performed. These dates are generated by the various systems in Oracle Financials or user input. In the case of iProcurement, the dates are associated with the creation and approval of the purchase requisition, which can widely differ from when the item is received and invoiced.
To give clarity on the billing pattern of a purchase order, Reporting Invoice or Expenditure Date displays the invoice date on purchases that have been posted for transactions identified by Expenditure Transaction Source Long Name equal to APINV-AP Invoice. For all other transactions, the date displayed refers to Expenditure Item Date. Reporting Invoice or Expenditure Date is an include field in Expenditure Details.
Expenditure Item Reference Text Fields
When exporting Expenditure Details by PTAE data into a csv file, the data in the fields Expenditure Item Account Reference Text and Expenditure Item Department Reference Text will be hyperlinks, as these are drillable fields in the report. To retrieve the actual reference text, use the fields Expenditure Item Account Reference Plain Text and Expenditure Item Department Reference Plain Text when analyzing your data.
Differentiating Between the Expenditure Transaction Description Options
In the OBI Consolidated Expenditure Reporting (CER) Dashboard, you have the option to include any of the following expenditure transaction descriptions when using Expenditure Balance and Expenditure Details reports.
Expenditure Transaction Description:
- Expense Request: Business purpose
- Purchase Order: Supplier name
- PCard: Business purpose (first 20 characters) appended by: supplier name
- iJournal: First 60 characters of journal line description
- Salary: Last name, first middle - organization code
Abbreviated Expenditure Transaction Description
- First 30 characters of the Expenditure Transaction Description. This version is consistent with the RM3 279 report.
Expenditure Transaction Long Description
- Expense Request: Business purpose
- Purchase Order: Purchase requisition description
- PCard: Business purpose
- iJournal: First 60 characters of journal line description
- Salary: Last name, first middle - organization code
Expenditure Transaction Description | Abbreviated Expenditure Transaction | Expenditure Transaction Long Description | |
---|---|---|---|
Expense Request | Thermo Fisher Scientific visit to assess new transmission electron microscope Who: Jane Doe. What: Trip to verify microscope capabilities for possible purchase. When: 12/10 - 12/12/2018. Where: Thermo Fisher Scientific, Hillsboro, or Why: Verify capabilities of M100-T. | Thermo Fisher Scientific visit | Thermo Fisher Scientific visit to assess new transmission electron microscope
|
Purchase Order | Lawson Lab Management Systems LLC | Lawson Lab Management Systems | To provide ongoing support for the Lawson Lab Management Software application in use by the Stanford Metrology Lab Facilities. Support period: 10/1/18 to 3/31/19. |
PCard | Working lunch Who: J:COUPA CAFE Y2E2 | Working lunch Who: J:COUPA CAF | Working lunch Who: Joe Sanchez What: lunch When: 12/6/2018 Where: Coupa Cafe Why: A meeting with Dr. Joe Sanchez and Dr. Tom Latimer to discuss upcoming projects. |
iJournal | Bechtel I-Center Visa transaction fee 2018-19 | Bechtel I-Center Visa transact | Bechtel I-Center Visa transaction fee 2018-19 |
Additional Resources
- For a list of fields available to include in report customizations, refer to Data Fields Available in CER Dashboard.
- To understand how expenditure types roll up into higher level groupings and how they compare to object codes and consolidated budget rollups, refer to CER Expenditure Type Roll-ups.
Expenditure Details Data Download
Expenditure Details Data Download has the exact same fields and sequence as in the RM3 FIN_EXP-279 download report.
Sample Business Questions
- What is my expenditure data in a format suitable for external analysis that I can also merge with external data?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
The expenditure details data download is intended for external analysis (in tools such as Excel) and returns the exact same 57 columns of data (in the same column order) as the legacy RM3 FIN_EXP_279 Transaction Details Report data download.
Understanding the Data
- Some column names are slightly different from the RM3 FIN-EXP-279 report.
- It uses the abbreviated expenditure transaction description field, which is limited to 30 characters like the legacy report.
- Salary data will not be returned to users who do not have salary view authority for a PTA.
Sponsored Awards Actual Expenditures - by MTDC, Exclusions, Indirect Expense
Displays the Modified Total Direct Costs (MTDC), along with the exclusions and indirect expenses associated with a sponsored PTA.
Launch OBI Sponsored Awards Actual Expenditures - by MTDC, Exclusions, Indirect Expense
Sample Business Questions
- What was the modified total direct costs, exclusions and indirect expenses associated with sponsored research last year?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
Sponsored Awards’ Actual Expenditures reports allow you to see the modified total direct costs (MTDC), exclusions and indirect expenses associated with a sponsored project PTA. Refer to the following screenshots.
Understanding the Data
For more information on MTDC, exclusions and indirect expenses, refer to DoResearch 15.1 Facilities and Administrative (Indirect Cost) and Fringe Benefits Rates.
iJournals
This report provides details of iJournals in any range of GL Periods.
Sample Business Questions
- Which journals posted in a specified period?
- What are the iJournals including manual and feeder journals for a time period?
- Where is my journal in the approval process?
- What kind of supporting documentation is attached to a particular journal?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
The iJournals reports allow you to view summary, detail and approval history for iJournals in the range of GL periods that you specify. This report includes fund transfers and iJournals that have not yet been posted. Refer to the following screenshots.
By right clicking on the Journal Number in the Journal Header Information section you can view the journal in Oracle by selecting Oracle iJournals Query. By selecting Oracle iJournals Attachment Query, a new tab will open the attachment window for that journal. Users must have logged into iJournals prior for this feature to work successfully.
Understanding the Data
In the Journal Header Information report, the include field Journal Attachment Indicator displays whether there is an attachment for the journal by either a Y or N value.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Commitment Details
This report provides details on hard commitments for salary, requisitions, purchase orders and AP invoices.
Demo of Commitment Detail reports within the CER Dashboard in OBI
Sample Business Questions
- What are my hard commitments?
- What are the outstanding commitments as of today?
- What are my committed future expenses?
- What are the commitment details by type (salary, requisition, PO, Accounts Payable (AP) invoice)?
Selection Criteria
To retrieve results, follow the Selection Criteria instructions near the top of the screen. Refer to Using Selection Criteria for OBI Reports for more guidance.
Commitment data is only returned if the ending GL Period requested is the Last Closed or Current Open period.
For a list of the CER common selection criteria, their associated list of values and when to use them, refer to CER Selection Criteria List of Values.
Report Views
The Commitment Details report shows commitments in based on type: Salary, Requisitions, Purchase Orders and AP Invoices. Refer to the following screenshots.
The Salary Commitments report will display student aid and tuition allowance, in addition to salary for graduate student assistantships and postdoctoral scholars.
Understanding the Data
- For information about how the different commitments are created, refer to Commitment Management.
For a list of fields available to include in report customizations, refer to Data Fields Available in CER Dashboard.