Project Burn Rate
Purpose and Scope
The Burn Rate Dashboard is designed to provide a comprehensive, project-level view of sponsored projects' fund utilization over time. It is intended for Principal Investigators (PIs), department administrations, and financial leads to ensure transparency and agility in managing project finances. The dashboard enables stakeholders to monitor spending patterns, identify potential funding utilization issues, and pinpoint data inaccuracies efficiently. The scope includes tracking financial data from the initiation of each project (as recorded in PeopleSoft) up to the most recent closed financial period, allowing users to make timely decisions and adjustments.
Data Sources and Structure
The dashboard pulls its data from several key sources, ensuring a robust and accurate representation of project finances:
- PeopleSoft: The primary source for project-level financial details, including award information, budgets, and expenditures.
- PeopleSoft Query:
- UTS_OSPA_BURN_RATE: Main query contain sponsored project budget, expense and encumbrance details
- UTR_OSPA_PROJECT_INFORMATION2: Comprehensive sponsored project information
- UTR_PRJ_AND_AWARD_LISTING: Sponsored project listing
- UTR_OSPA_RSRCH_EXPENSE: Expenditures data containing expense account info which is used to identify sub award
- Calculated Fields: Metrics such as % $ Available, % $ Committed, and % Time Elapsed are calculated within the dashboard based on data pulled from these sources.
The structure of the data integrates these sources to present a unified view of financial performance and anomalies, helping users easily navigate and understand their project's financial status.
Key Metrics and Visualizations
The dashboard includes several key metrics and visualizations that aid in monitoring project burn rates:
- Key Metrics:
- % $ Available: The percentage of remaining funds relative to the project's total budget.
- % $ Committed: The percentage of funds that have been spent or encumbered.
- % Time Elapsed: The proportion of time passed compared to the project's total duration.
- Total Balance, Budget, and Expense: Financial summaries showing the project's budget, expenses incurred, and total balance.
- Visualizations:
- Tables: Present project details such as Project ID, PI, Department, and Financial Status, allowing users to view project information based on applied filters.
- Scatter Plot: A visual representation of fund utilization compared to the time elapsed for projects, categorized by financial zones.
- Bar Charts: Displays the distribution of anomalies across projects, enabling users to quickly identify the most common financial issues.
These metrics and visualizations provide users with an intuitive way to track and manage project funds, ensuring that they remain within budget and on track for completion.
Data Dictionary Overview
Data Fields | Data Sources | Description | Formula |
---|---|---|---|
% $ Available | Calculated | Percentage of funds remaining for direct and indirect costs. |
% $ Available = Total Balance / Budget |
% $ Committed | Calculated | Funds that have been spent or encumbered. |
% $ Committed = (Expenses + PreEncumbrance + Encumbrance) / Budget |
% Time Elapsed | Calculated | Percentage of total project time elapsed relative to the project's total duration. |
% Time Elapsed = (Today's Date - Start Date) / (End Date - Start Date) |
AwardTitle | PeopleSoft | Descriptive field identifying the award for the project. | N/A |
Budget | PeopleSoft (KK) | Current budget for the project, including budget revisions. | N/A |
Total Balance | Calculated | Total available funds associated with a given project. |
Total Balance = Budget - Expenses - PreEcumbrance - Encumbrance |
Zone | Calculated |
Custom value based on OSPA-defined business logic and derived from other calculated values (budget, expenses, % Time Remaining, % $ Committed, etc.) Possible values: F+ (very fast spend) F (fast spend) N (normal spend) S (slow spend) S- (very slow spend) Overspent Zero Balance Expired-Underspent Expired-Overspent Expired-Zero Balance |
F+ (very fast spend): 80% <= [% $ Committed] < 100% and [% Time Elapsed] < 80% and [% $ Committed] > (0.7 * [% Time Elapsed] + 0.3) F (fast spend): [% $ Committed] < 80% and [% $ Committed] > (0.7 * [% Time Elapsed] + 0.3) N (normal spend): condition 1: [% $ Committed] < (0.7 * [% Time Elapsed] + 0.3) and [% $ Committed] > (1.43 * [% Time Elapsed] - 0.43) condition 2: 80% <= [% $ Committed] < 100% and 80% <= [% Time Elapsed] < 100% S (slow spend): [% $ Committed] <= (1.43 * [% Time Elapsed] - 0.43) and [% Time Elapsed] < 80% S- (very slow spend): 80% <= [% Time Elapsed] < 100% and [% $ Committed] < 80% and [% $ Committed] < (1.43 * [% Time Elapsed] - 0.43) Overspent: [% $ Committed] > 100% and [% Time Elapsed] < 100% Zero Balance: [% $ Committed] = 100% and [% Time Elapsed] < 100% Expired-Underspent: [% $ Committed] < 100% and [% Time Elapsed] = 100% Expired-Overspent: [% $ Committed] > 100% and [% Time Elapsed] = 100% Expired-Zero Balance: [% $ Committed] = 100% and [% Time Elapsed] = 100% |
This data dictionary provides essential details on the metrics and data fields used in the dashboard, ensuring users can understand the source and calculation of each metric.
Data Fields | Data Sources | Description | Formula |
---|---|---|---|
% $ Available | Calculated | Percentage of funds remaining for direct and indirect costs. |
% $ Available = Total Balance / Budget |
% $ Committed | Calculated | Funds that have been spent or encumbered. |
% $ Committed = (Expenses + PreEncumbrance + Encumbrance) / Budget |
% Time Elapsed | Calculated | Percentage of total project time elapsed relative to the project's total duration. |
% Time Elapsed = (Today's Date - Start Date) / (End Date - Start Date) |
AwardTitle | PeopleSoft | Descriptive field identifying the award for the project. | N/A |
Budget | PeopleSoft (KK) | Current budget for the project, including budget revisions. | N/A |
Total Balance | Calculated | Total available funds associated with a given project. |
Total Balance = Budget - Expenses - PreEcumbrance - Encumbrance |
Zone | Calculated |
Custom value based on OSPA-defined business logic and derived from other calculated values (budget, expenses, % Time Remaining, % $ Committed, etc.) Possible values: F+ (very fast spend) F (fast spend) N (normal spend) S (slow spend) S- (very slow spend) Overspent Zero Balance Expired-Underspent Expired-Overspent Expired-Zero Balance |
F+ (very fast spend): 80% <= [% $ Committed] < 100% and [% Time Elapsed] < 80% and [% $ Committed] > (0.7 * [% Time Elapsed] + 0.3) F (fast spend): [% $ Committed] < 80% and [% $ Committed] > (0.7 * [% Time Elapsed] + 0.3) N (normal spend): condition 1: [% $ Committed] < (0.7 * [% Time Elapsed] + 0.3) and [% $ Committed] > (1.43 * [% Time Elapsed] - 0.43) condition 2: 80% <= [% $ Committed] < 100% and 80% <= [% Time Elapsed] < 100% S (slow spend): [% $ Committed] <= (1.43 * [% Time Elapsed] - 0.43) and [% Time Elapsed] < 80% S- (very slow spend): 80% <= [% Time Elapsed] < 100% and [% $ Committed] < 80% and [% $ Committed] < (1.43 * [% Time Elapsed] - 0.43) Overspent: [% $ Committed] > 100% and [% Time Elapsed] < 100% Zero Balance: [% $ Committed] = 100% and [% Time Elapsed] < 100% Expired-Underspent: [% $ Committed] < 100% and [% Time Elapsed] = 100% Expired-Overspent: [% $ Committed] > 100% and [% Time Elapsed] = 100% Expired-Zero Balance: [% $ Committed] = 100% and [% Time Elapsed] = 100% |
This data dictionary provides essential details on the metrics and data fields used in the dashboard, ensuring users can understand the source and calculation of each metric.
- Filters and Slicers:
- SubAward: Allows filtering by sub-award type.
- Anomaly: Filters to show specific types of anomalies.
- Contract Type: Filters by contract types like COST_REIMBURSE, CRB_LOC, FIXED_PRICE, PREPAID.
- Month Remaining Category: Filters projects by the remaining months in categories like <=1, >1 & <=2, etc.
- Project Status: Filters based on the project's approval or closure status.
- Search Colleges/VP Units: Filter projects by college or VP unit.
- Search Departments/Units: Further filters the project list by department or unit.
- Cost Center: Filters based on the associated cost center.
- Search PI: Filters by the Principal Investigator (PI).
- Project ID: Allows users to search for specific projects by ID.
- Key Performance Indicators (KPIs):
- # of Projects: Shows the total number of projects listed.
- Projects Ending in 30, 60, and 90 Days: Displays counts of projects nearing their end dates.
- Cards:
- Budget, Expense, Encumbrance, and Total Balance: Highlights financial variables such as the total budget, expenses, encumbrances, and the total balance.
- Visuals:
- Table of Colleges/VP Units: Displays a summary of projects per college/VP unit categorized by the time remaining until project end.
- Anomaly Projects Distribution: Bar chart displaying the distribution of anomalies, such as negative balances or zero expenses.
- Overall Functionality: This tab provides an overview of the project status across different colleges and VP units. It enables users to monitor projects approaching their end dates and identify financial anomalies quickly.
- Filters and Slicers:
- Similar filters to the Summary tab, including project status, cost center, and search options for PIs and project IDs.
- Key Performance Indicators (KPIs):
- % $ Committed vs. % Time Elapsed: Scatter plot showing the percentage of funds committed relative to the time elapsed for each project.
- Cards:
- Financial summary cards at the bottom showing Budget, Expense, Encumbrance, and Total Balance.
- Visuals:
- Scatter Plot: Displays individual projects as dots based on their fund utilization and time progress, categorized by zones (e.g., overspent, zero balance).
- Zones: Helps users visually distinguish projects that are overspending or on track.
- Overall Functionality: This tab allows users to identify trends and outliers in fund utilization over time. It visually categorizes projects based on their financial status, helping users quickly identify and address overspending or anomalies.
- Filters and Slicers:
- Zone: Categorizes projects into zones like F, F+, N, Overspent, S, S-, and Zero Balance based on fund utilization.
- Project Status: Filters projects by approval and closure status (GM - Approved, GM - Closed, GM - Frozen).
- Contract Type: Filters by contract types (COST_REIMBURSE, CRB_LOC, FIXED_PRICE, PREPAID).
- SubAward: Filters by award type (e.g., Awards, SubAwards (Outgoing)).
- College/VP Unit: Filters projects based on the college or VP unit.
- Department: Filters the projects by the department.
- PI (Principal Investigator): Allows users to filter projects by PIs.
- Sponsor: Filters based on sponsors.
- Project ID: Search option for specific projects.
- Key Performance Indicators (KPIs):
- # of Projects: Displays the total number of projects listed.
- Cards:
- Last Updated: Shows the last date when the dashboard was updated.
- Visuals:
- Project Table: Displays a detailed list of projects including Project ID, Total Balance, and % Time Elapsed. Users can download data by hovering over the top-right corner of the table and selecting the "Export Data" option.
- Overall Functionality: This tab provides an interactive filter experience for users to narrow down project information based on various criteria such as department, college, sponsor, and more. It allows users to view the financial details and timelines of projects in detail and export data as needed.
- Filters and Slicers:
- Anomaly: Filters to show projects with specific anomalies, including negative balances (direct, total, or indirect), cost share with no expenses, and others.
- SubAward: Filters by award types (e.g., Awards, SubAwards (Outgoing)).
- Project Status: Filters by project status (Approved, Closed, Frozen).
- Search Colleges/VP Units: Filters projects by college or VP unit.
- Search Departments/Units: Allows filtering by department.
- Cost Center: Filters projects by cost center.
- PI (Principal Investigator): Search filter for projects by PI.
- Project ID: Search option for specific projects.
- Key Performance Indicators (KPIs):
- # of Projects: Shows the total count of projects listed in the table.
- Visuals:
- Anomaly Table: Provides a comprehensive view of projects that have anomalies. It includes columns like ContractNum, Project ID, Cost Center, PI, Department, and College/VP Unit. The table can be filtered to focus on specific types of anomalies.
- Overall Functionality: This tab allows users to monitor and investigate projects with anomalies, enabling them to take corrective actions. It offers detailed insights into projects with financial irregularities and other issues, ensuring project administrators can proactively address discrepancies.
- Filters and Slicers:
- SubAward: Filters by award types (e.g., Awards, SubAwards (Outgoing)).
- Project Status: Filters by project status (Approved, Closed, Frozen).
- Search Colleges/VP Units: Filters projects by college or VP unit.
- Search Departments/Units: Allows filtering by department.
- Cost Center: Filters projects by cost center.
- PI (Principal Investigator): Search filter for projects by PI.
- Project ID: Search option for specific projects.
- Notification Category: Filters by project pre-closeout periods (30 days, 60 days, 90 days)
- Notification Sent: Whether an email notification has been sent to each sponsored project
- Key Performance Indicators (KPIs):
- # of Projects: Shows the total count of projects have received pre-closeout email notifications.
- Visuals:
- Pre-Closeout Notification Table: Provides a comprehensive overview of projects requiring pre-closeout email notifications. Columns include Days Remaining, Email Sent for 30/60/90-Day Period, Project ID, End Date, Project Status, Total Budget/Expense/Encumbrance/Balance, Principal Investigator (PI), Department, and College/VP Unit. The table is filterable to highlight specific project types.
- Notification Sent Over Time: Tracks the daily count of email notifications sent by notification category
- Project Status after Notification Sent: Monitors changes in project status following the dispatch of email notifications, allowing for insights into the impact of notifications on project progression.
- Overall Functionality: This tab offers thorough view of sponsored projects approaching 30/60/90 days closeout, tracking key info such as days remaining, budget details, and project status. It records the status and impact of pre-closeout email notifications, including daily notification counts and any subsequent changes in project status, with filterable options to focus on specific project types.
- Filters and Slicers:
- SubAward: Filters by award types (e.g., Awards, SubAwards (Outgoing)).
- Project Status: Filters by project status (Approved, Closed, Frozen).
- Search Colleges/VP Units: Filters projects by college or VP unit.
- Search Departments/Units: Allows filtering by department.
- Cost Center: Filters projects by cost center.
- PI (Principal Investigator): Search filter for projects by PI.
- Project ID: Search option for specific projects.
- Notification Category: Filters by project pre-closeout periods (30 days, 60 days, 90 days)
- Notification Sent: Whether an email notification has been sent to each sponsored project
- Key Performance Indicators (KPIs):
- # of Projects: Shows the total count of projects have received pre-closeout email notifications.
- Visuals:
- Notification Sent Table: Displays detailed information for each email notification sent, including the sent date, Principal Investigator (PI), list of copied recipients (grants accountants, college administrators, and 50%-50% contacts), email content, and associated project college.
- Overall Functionality: This tabs captures essential info for pre-closeout notification emails sent for each sponsored project, ensures transparent record-keeping of the communications.
- Filters and Slicers:
- Contract Type: Filters by contract types such as COST_REIMBURSE, CRB_LOC, FIXED_PRICE, and PREPAID.
- Zone: Categorizes projects into various financial zones like F, F+, N, Overspent, S, S-, and Zero Balance.
- Month Remaining Category: Filters projects based on the number of remaining months until the end date (e.g., <=1, >1 & <=2).
- Project Status: Filters based on the project’s current status (Approved, Closed, Frozen).
- Search Colleges/VP Units: Filters projects by the college or VP unit.
- Search Departments/Units: Further filters by department.
- Cost Center: Filters by the associated cost center for projects.
- PI (Principal Investigator): Filters by PIs for specific project information.
- Project ID: Allows users to search for specific projects.
- SubAward: Filters to include awards and sub-awards.
- Anomaly: Allows users to filter projects based on specific anomalies.
- Key Performance Indicators (KPIs):
- # of Projects: Shows the total number of projects in the download table.
- Cards:
- Last Updated: Displays the date of the most recent data update.
- Visuals:
- Data Table: Provides a detailed table of projects including ContractNum, Project ID, Cost Center, PI, Department, and College/VP Unit. This table allows users to filter down to specific project details and download the data by hovering over the table and selecting the "Export Data" option.
- Overall Functionality: This tab is designed for users to view and download detailed project information. By applying various filters, users can narrow down the data to specific projects and anomalies, making it easy to export and analyze information outside the dashboard.
- Filters and Slicers:
- Data Sources: Filters based on data sources such as Calculated fields, PeopleSoft, PeopleSoft (KK), and PeopleSoft/Routing Form.
- Data Fields: Allows users to search and filter for specific data fields like % $ Available, % $ Committed, Budget, AwardTitle, and more.
- Key Performance Indicators (KPIs):
- Last Updated: Displays the date when the data dictionary was last updated.
- Visuals:
- Data Fields Table: Provides a detailed description of each data field used in the dashboard. It includes columns like Data Fields, Data Sources, Value Type, Description, and Formula. Users can scroll through the table to find detailed explanations of each metric and calculation used in the dashboard.
- Overall Functionality: The Data Dictionary tab is essential for users who need to understand the metrics and calculations behind the visuals and KPIs in the dashboard. It serves as a reference guide to provide clarity on the data sources and formulas used, ensuring that users have a comprehensive understanding of how the dashboard's information is derived.
Access and Support
Access to the dashboard is restricted to authorized personnel, including PIs, department administrators, and financial leads who manage sponsored projects. Users can log in through the institution's secure login system to access the dashboard.
For technical support or inquiries related to the dashboard:
- Contact Information: Users can reach out to the ResearchIT support team at ResearchIT@utrgv.edu for assistance with access, data issues, or other technical difficulties.
- Training and Resources: Training sessions and user guides are available upon request to help users navigate the dashboard and utilize its features effectively.
If you have any additional questions or need further support, please contact researchIT@utrgv.edu for further assistance.