Reporting Tools

In accordance with Financial Policy #2106 the Responsible Business Offices within the School or Center will generate and provide periodic statements of project expenditures to the responsible principal investigator. It is the responsibility of the principal investigator and his/her business administrator to review each statement for accuracy and completeness and to initiate corrections, when appropriate, no later than 90 days from month end.

In addition to providing reports for the PI, a BA should be reviewing higher level grant management reports for their entire departmental grant portfolio.  PSOM-ORSS recommends utilizing Web Intelligence (often referred to as Webi or Business Objects) to do so. Additionally PI's can review their own portfolio at any time with the Grant Financial Management (GFM) tool

The warehouse repository collection of reports can be overwhelming so highlighted here are some of the most useful.  They can be found within the Folder/Schools and Centers/Schools/Perelman School of Medicine/ORSS-Office of Research Support Services. 

Go to Webi and Request Access to Data Warehouse Collections.

Reports highlighted below with an active link provide an example of the report.

Org Summary

Gives a summary of all grant funds for which the ORG is the RESP ORG. Listed by fund is Sponsor, Fund Desc, PI, Acct End, PBIL, Operating Budget, Expenditures to date, Surplus/Deficit, Cash Position and Cash Received.  There are several added tabs to identify receivable issues, overdrafts, funds frozen/freezing, analysis of expired funds, etc.

GrantMonitor

Provides exception-type reporting to isolate issues.  It can also be used to focus on budget periods coming to an end where a progress report may be due prior to the start of the next budget period.

FCOI Pending

Identifies awards from PHS that have been awarded, but not set up yet due to noncompliance with the PHS-FITS policy.

AIS (full search)

AIS stands for Account Information Sheet, but this query is a very powerful tool when you want to search awards based on optional criteria like PI, Sponsor, Sponsor Description, and Date Range of issuance of the AIS.  Think about anytime someone references a sponsor # like 5-R01-MH-123456-01 and you just need to know the fund#, inst#, or PI.  This query will help you find just about anything quickly and can provide future-year commitments in a matrix.

Cost transfers - Time Range-Org Audit

This can be utilized to monitor cost transfers performed in your department to/from grants. Periodic reviews should be conducted to ensure you have proper documentation to justify the transfer and have provided the direct benefit to the grant being charged and an explanation as to why the transfer was needed.

Protocols

This query provides the IRB (Humans) and IACUC (Animals) protocol numbers and approval dates. There are separate tabs for both Animal Protocols and Human Protocols sorted by investigator's last name.  Additional tabs identify expired protocols on active grant
funds, protocols expiring in the next 60 days, and terminated protocols.

Current Costing Allocations Search

A query directed toward the HCM (Human Capital Management) Universe that allows optional searches on Primary Cost Center Org, Employee Name, Fund #, or PI Name.  The query looks for any Employee Names that have a Payroll costing allocation set up based on the optional prompts in the search criteria and returns the full costing allocation for each Employee Name.  While HCM data has the Job allocation % for each Name and Job, this query will provide the Monthly pay amount for Salaried individuals and Weekly pay for Hourly paid individuals based on default hours scheduled.  If an individual has multiple Job appointment it will also show the % of pay to all jobs and if an individual is over the NIH cap an equivalent effort % is provided.

FY Grant ORG Expense Analysis

A powerful analytical tool for all FY grant expenses posted to any range of selected ORGS. Simply provide a range of Posted ORG's, the first day of the FY you are querying, and an Accounting Period.  You will find out the sources and how the grant money is being spent for the University Fiscal Year to Date.

Effort Report - Home Dept Org

Recommended that you run this to assist with the semiannual certification of online effort reporting. While online effort reporting indicates NIH sources of funding it still only shows the % of total pay charged to the grant.  This query can show the NIH cap equivalent effort and that you will need to show the difference as cost sharing during pre-review.  A variable  <NIH Cap> can be edited to have calculations based on updated cap values.

ORG Wayward Summary

The first of two queries required to identify wayward transactions. The other query is ORG Wayward Detail. ORG Wayward Summary is a query that is used to alert the user of the ORG/Fund wayward transactions that have occurred and require attention. The ORG Wayward Detail provides the line item detail for the ORG/Fund combination shown in the Org Wayward Summary.  In the query you will find 2 tabs where one shows your Org's summary charges on other Resp Org's grants and the other shows Other Org's charges on your grants when your dept is the Resp Org.

fundsummary (with date range)

Gives a summary of the entire grant fund for a prompted Accounting Period and provides detail for any range of dates selected.  This is the most powerful query to look into all aspects of monitoring a grant fund.  It seeks out all individuals paid on the project and comes up with an equivalent effort provided based on every source they have been paid from.   Access to PennERA, Human Capital Management,and  Financial Data Universes is needed to obtain all the benefits provided by this query.

fundsummary

A watered down version of the above.  It is provided because it will run faster and will provide FY detail of expenditures.  Remember the query above is searching all sources of funding for individuals that have been paid on the grant for any range of time so it is going to take longer to run.

Effort Report-Individual

Used to show summary Total Research Effort for all research projects over any time period.  It can also be used to pre-review effort in the Effort Reporting System. NIH Cap equivalent effort is shown when applicable.

Grant Reports

Provides a PI-friendly view of a grant fund (broad expense categories are provided instead of object codes) for a selected accounting period.  Payroll and expense detail is also provided for each CNAC/ORG/PROGRAM/CREF combination, but only for the selected Accounting Period. 

Grant Reports - PI Awarded

A variation of the Grant Reports mentioned above and will provide the same type of reports except it can provide reports for multiple funds of the PI for any grant the PI holds as PI or participates on with an active subaccount.  Additional screenshots are provided to demonstrate additional summary reports it can also provide.  It filters on active awards.  It can also provide summaries for non grant activity too when the PI's CREF is being utilized to account for charges.

Fund Detail

Provides accounting period summaries of all expenditures and payroll for a given fund and any period of time. Separate tabs provided for: Accounting Period Summary, Payroll Period Summary, Payroll by Object Code, Payroll by Person, Exp Detail by Obj, Detail by ORG, Detail by CREF, Detail by Acct Period, and Detail by PO/User. 

Fund Detail-PI

Provides accounting period summaries of all expenditures and payroll for all grants of a prompted PI for any range of time. Separate tabs provided for: Fund Activity Summary, Pay Summary, Accounting Per. Summary(PB) by Fund, Pay by Fund, Payroll by Person, Exp Detail by Obj, Detail by ORG, Detail by CREF, Detail by Acct Period, and Detail by PO/User.

Object Code by Fund - Detail

Allows one to enter a range of time, posted orgs, and posted object codes for those instances when you want to review activity across funds for a certain type of charge (object code range) or can't remember where something was charged.  Can be helpful in identifying 4827 voluntary cost sharing  (overdraft write-offs)

Detail Org - Vendor

Allows you to search for PO's associated with and analyze payments for a prompted vendor for all funds

Detail Org - Subcontract

Allows you monitor all subcontract PO's and payments over a given time range focusing on activity in obj codes 5332 and 5333