Clinical Trials Revenue and IDC Reconciliation Report
The Clinical Trials Revenue and IDC Reconciliation Report is based on the Award Contract and Project Summary from the Contract Management Dashboard. This report incorporates additional GL amounts for revenue and IDC that are useful for Sponsor Initiated Clinical trial awards.
The Clinical Trials Revenue and IDC Reconciliation Report is available from the Business Analytics Hub (bah.ucsd.edu) under the Financial Management tab.
Access
Access has been provisioned to anyone with the Oracle BI Consumer JR role. (Staff who do not have access should request the Oracle role.) Access failure appears as error "Unable to load requested view. Displaying home view instead.". To access the dashboard:
- Navigate to bah.ucsd.edu.
- Select Budget & Finance.
- If using the List View, look for Clinical Trials Revenue and IDC Reconciliation Report in the list or use the search bar at the top right and click the dashboard name
- If using the Card View, click the Financial Management tab and click Launch on the Clinical Trials Revenue and IDC Reconciliation Report tile
- Use your Active Directory credentials to sign in, if prompted.
Navigation
The Cognos and Oracle Tips & Tricks page shares various recommendations on navigating report functionality. Search for a specific contract using the contract number prompt.
Summary
Business Scenarios
- Which contracts are currently over budget?
- Which contracts have a cash shortfall after accounting for all expenses and commitments?
- Which awards are approaching the end of their funding and need a final invoice or close‑out?
- Where do invoiced amounts exceed the amount the sponsor has agreed to fund?
- Which contracts have outstanding commitments that are not yet covered by received payments?
- Which projects have indirect costs that are disproportionately high compared with direct costs?
- Which awards are at risk of running out of funds based on current spending trends?
- Which projects show a mismatch between recorded revenue and the actual expenses incurred?
Report Views
The default Report view displays summary information for the contract with project-level information just below the contract information.
Users may click on a Contract Number or Project Number to view details related to that contract and/or project on the Contract Invoice and Payment Details report.
|
Column Header |
Description |
|---|---|
|
Sponsored Award Number |
Sponsor Award Number |
|
Sponsor Name |
Sponsor Name |
|
PI Name |
PI Name |
|
A: Total Budget |
From the current baselined budget entered in the Awards module per the award agreement |
|
B: Contract Funding Amount |
Funding amount from the Awards module per the award agreement |
|
C: Contract Revenue Amount |
For cost reimbursable contracts (e.g. Letter of Credit NIH and NSF awards), revenue is recognized based on billed expenses incurred up to the contract funding amount. For all other contracts, revenue corresponds to billing events posted as invoices up to the contract funding amount on the contract in the Contracts module. See KB0033415 for more information on award revenue recognition. |
|
D: Contract Invoiced Amount |
Total amount of invoices on the contract from Contracts module. |
|
E: Contract Payment Amount |
Total payments applied associated with the contract from Receivables module. |
|
F: Contract Expenditures |
Costs posted to projects associated with the award (A in POETAF) in PPM Project Costs. This does not include costs posted to Internal Funding Sources. |
|
G: Contract Committed Costs |
Total Commitments associated with Award and Projects in PPM Project Costs. |
|
A-F: Contract Budget Balance |
Total Budget - Contract Expenditures |
|
C-F: Contract Financial Balance |
Contract Revenue Amount - Contract Expenditures |
|
E-F: Contract Cash Balance |
Contract Payment Amount - Contract Expenditures. This amount should match the sum of all contract lines on the Award Trial Balance |
|
E-F-G: Contract Cash Balance with Committed Costs |
Contract Payment Amount - Contract Expenditures- Contract Committed Costs |
|
Project Number |
Project Number |
|
Project Budget Amount |
Total Budget Amount for the Project |
|
Project Expenditures |
Total Costs for the Project excluding the costs associated with internal funding sources. |
|
Project Committed Costs |
Total Commitments for the Project excluding the costs associated with internal funding sources. |
|
Project Total Direct Costs |
Total Costs for the Project excluding the costs associated with internal funding sources and Indirect Costs (538000* expenditure types) |
|
Project IDC Costs |
Total Project Indirect Costs (538000* expenditure types) |
|
Project GL Revenue |
Total Revenue (4* accounts) in the GL based on the project segment |
|
Total GL IDC by Project |
Total Indirect Expenses (53800* accounts) in the GL based on the project segment |