Project Closeout
Project Closeout
Purpose and Scope
The Sponsored Project Closeout Dashboard is designed to streamline and support the timely closeout of sponsored awards at UTRGV. It serves as a centralized tool that integrates automated data and closeout tracking across multiple PeopleSoft modules. Primary stakeholders for this dashboard are the Grants Contracts and Grant Accounting team, who are responsible for ensuring compliance and financial accuracy at the end of a sponsored project.
Key Features and Scope:
- Centralized Tracking: Offers one unified platform to monitor the closeout progress of all active sponsored projects.
- Automated Data Integration: Pulls real-time data directly from PeopleSoft systems to reduce manual data entry and improve accuracy.
- Reconciliation Automation: Calculates reconciliation tasks automatically, including comparisons between PC and GL expenses, billed vs. unbilled amounts, and F&A base validation.
- Mentor-Style Workflow: Guides users through the closeout process with step-by-step instructions and tracking of key actions.
- To-Do List & Status Tracking: Enables users to track the completion status of closeout checklist items per project.
- Urgency Indicator: Highlights projects nearing closeout deadlines (based on completion date set two months after project end).
- Source Data Download Capability: Enables access to raw PeopleSoft query data for transparency and deeper review.
The dashboard supports stakeholders by providing an efficient and transparent way to monitor and manage project closeouts. By pulling data directly from the PeopleSoft system, it minimizes manual effort and ensures data accuracy. The dashboard enhances oversight through real-time tracking of financials data and closeout progress, helping users quickly identify discrepancies and required actions. With built-in tools like completion status tracking and urgency indicators, it supports better coordination, improves turnaround time, and ensures that closeout tasks are completed in line with institutional and sponsor expectations.
Data Sources and Structure
The dashboard pulls data from PeopleSoft, integrating reports from 3 modules Grants Management, General Ledger, and Commitment Control. It is structured to support reconciliation of expenses, validate billing and revenue, and assess budget performance across modules. It categorizes financial transactions by analysis type, accounting date, and expense account to monitor project progress, and ensure compliance with closeout requirements.
Grants Management
- UTR_GM_TRANSACTION_DETAIL - Provides detailed expense transactions posted to sponsored projects.
- UTZ_GM_TRIAL_BALANCE - Summarizes project-level balances including expenses, revenue, and billing.
- UTZ_GM_INVOICE_PAYMENT_REV - Shows invoiced amounts, payments received, and recognized revenue.
- UTR_GM_PRJ_AND_AWARD_LISTING - Lists all active projects and associated award information.
- UTR_GM_AR_DETAIL - Displays detailed accounts receivable transactions for sponsored projects.
- UTZ_GM_AWD_MILESTONES - Tracks award milestones and associated events.
General Ledger
- UTR_GL_TRANSACTIONS_EXPENSE - Captures detailed GL-level expense transactions by account.
- UTZ_GL_ALL_ACCTS_BY_TREE - Maps GL accounts using tree structures to support financial categorization.
Commitment Control
- UTS_OSPA_BURN_RATE - Monitors budget vs actuals to track spending progress against project budgets.
Key Metrics and Visualizations
- Key Metrics: KK/GL/PC Expense, Awarded Amount, Encumbrance, Available Balance, FFR/Final Invoice Expense, Billed Amount, PTT Expense, Manual F&A, AR/UAR/AP/DEF REV, BP Invoiced/Payment.
- Visualizations: Interactive tables and charts displaying project expense and billing amounts with detailed breakdowns by analysis type, accounts and accounting items etc.
User manual: Project Closeout
Overall Functionality: This page outlines the dashboard’s structure, providing a clear table of contents to help users navigate key sections, from summary views to detailed reconciliation reports and raw data sources.
Filters and Slicers:
- Data Sources: Filters based on data sources (PeopleSoft module & query report)
- Reconciliation Report, Data Fields/Metrics: Allows users to search and filter for specific calculation metrics and data fields
Visuals:
- Data Fields Table: Provides a detailed description of each data field used in the dashboard. It includes columns like Data Fields, Data Sources, 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 page defines key metrics, fields, and data sources used throughout the dashboard to ensure clarity, consistency, and accurate interpretation of the information presented.
Section 1: Award Summary & Closeout Task Checklist
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Project Status: Filters based on the project’s current status (Approved, Closed, Frozen).
- Award Status: Filters based on the award's current status (ACP, HOL, CLS).
- Processing Status: Filters based on the project’s current processing status (Active, Inactive).
- Completion Date Urgency: Filter based on closeout completion urgency (7, 30, 60 days)
Key Performance Indicators (KPIs):
- Completion Date Urgency: Calculates the number of days from today to a project's set completion date and categorizes it into defined time buckets to indicate the level of urgency.
Visuals:
- Completion Date Urgency Summary Table: Displays the number of projects grouped by defined time buckets based on how close each project is to its completion date, providing a quick overview of overall urgency levels.
- Project List Table: Presents detailed information for each project such as status, sponsor, PI, and contract details, sorted by urgency level from highest to lowest to prioritize closeout actions.
Overall Functionality: This page provides a prioritized view of sponsored projects approaching their completion dates, combining urgency levels with key project details to support effective tracking, planning, and timely closeout actions.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
Key Performance Indicators (KPIs):
- Reconciliation Metrics
- PC Expense: Total project expense recorded in Project Costing transaction detail report
- KK Budget: Total posted project budget
- Avail Balance: Total project budget minus expense
- Overspent or Underspent: Total project budget minus expense and future encumbrance
- FFR/Final Invoice: Total invoiced expense recorded in AR report
- PeopleSoft Billed Amt: Total invoiced amount recorded in Grants Management invoice payment report
- Collected: Total payment amount recorded in Grants Management invoice payment report
- Billing Limit: Total posted project budget
- GL Expense: Total expense recorded in General ledger transaction expense report
- BLD + BIL + DEF + OLT: Total billed amount recorded in Project Costing transaction detail report
- AR Balance: Total balance of AR accounts recorded in Trail Balance Report
- Billed + Collected + Refund: Difference between billed and payment amount recorded in Grants Management invoice payment report
Visuals:
- Award Summary Table: Displays critical project information, such as end date, sponsor, contract type, and IDC base/rate for the selected project.
- Reconciliation Chart: Displays more than ten reconciliation metrics from various PeopleSoft module reports along with their calculated variance amounts.
Overall Functionality: This page consolidates multiple reconciliation metrics from diverse PeopleSoft modules into a single view, enabling comprehensive comparison and variance analysis across financial data.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
Key Performance Indicators (KPIs):
- Project Information: Award ID, Contract Type, Sponsor, Awarded Amt, F&A base and Rate, Status
- Reconciliation Metrics
- SPSR: Budget details
- FFR/Final Invoice: Total invoiced expense recorded in AR report
- PC Expense: Total project expense recorded in Project Costing transaction detail report
- GL Expense: Total expense recorded in General ledger transaction expense report
- KK Budget: Total posted project budget in Commitment Control budget report
- PTT (Post Term Transaction): Transaction with accounting date past the project end date recorded in Project Costing transaction detail report
- F&A: Manual F&A calculation based on F&A base account exclusion and F&A rate
- TB Balance: AR/UAR/AP/DEF REV Accounts balance recorded in Grants Management trial balance report
- BP & AR: Total invoiced amount recorded in Grants Management invoice payment report
Visuals:
- Project Checklist Summary Table: Lists all relevant PeopleSoft module reports along with their reconciliation status, source query details, and the underlying calculation logic used to verify financial accuracy.
Overall Functionality: This page offers a financial reconciliation snapshot by summarizing key check items across multiple PeopleSoft module reports, helping users quickly identify matches or discrepancies.
Section 2: Detailed Reconciliation Reports
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Account: Budget Accounts group by direct and indirect costs
Key Performance Indicators (KPIs):
- Budget: Total posted awarded amount
- Expense: Total posted project spending
- Encumbrance: Funds that have been reserved for anticipated expenses, essentially commitment to spend money on a future transaction
- Available Balance: Total awarded amount extract posted expense and future encumbrances
- % Available Balance: Total available balance in percentage
Visuals:
- Budget Overview: Displays project-account level budget allocation, spending, and encumbrance details, enabling users to monitor financial performance and identify available funds.
Overall Functionality: This page provides a consolidated financial report of each sponsored project, summarizing budget, actual expenses, encumbrances, and remaining balances.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Entry Type: Indicates the type of transaction recorded (INV, CR)
- Template: Indicates the template type (BIBILING)
- Item ID: Invoice Item ID
- Line No.: Line number for each entry (0)
Key Performance Indicators (KPIs):
- Final Invoice/FFR Expense: Total project expenses reported on the final invoice, based on reconciled data from the AR detail report
Visuals:
- Final Invoice/FFR Overview: Lists all invoice-related entries by Entry Type and Template, and calculates the total Final Invoiced Expense based on included transactions.
Overall Functionality: This page reconciles invoiced expenses from the AR detail report with the total project expenses reported in other modules to ensure financial alignment before closeout.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Account: Budget Accounts types describe fund usage type
Key Performance Indicators (KPIs):
- KK Budget: Total posted awarded amount
- KK Expense: Total posted project spending
- KK Encumbrance: Funds that have been reserved for anticipated expenses, essentially commitment to spend money on a future transaction
- KK Sync: Indicate whether expense in KK report reconciles with other module reports
Visuals:
- Budget Overview: Provides a detailed view of budget, expense, and encumbrance data breakdown by budget accounts.
Overall Functionality: This page reconciles budget details from the commitment control report with the total project expenses reported in other modules (PC, GL) to ensure financial alignment before closeout.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Account: Budget Accounts types describe fund usage type
Key Performance Indicators (KPIs):
- F&A Manual Calculated Base: Manually calculated F&A-eligible expense total, derived by excluding specific accounts based on the F&A exclusion reference table
- F&A Manual Total: Expected F&A amount by applying the project’s F&A rate to the manually calculated eligible base
- G4180 F&A Total: Actual F&A expense recorded in the system under the L4 G4180 F&A account
- Difference: Variance between the manually calculated F&A amount and the system-recorded G4180 F&A
Visuals:
- F&A Manual Base Account Exclusion: Displays the list of expense accounts excluded from the F&A base calculation, based on project F&A type.
- F&A Manual Calculation: Summarizes the manual F&A calculation by showing the eligible base, applied rate, and resulting F&A amount.
Overall Functionality: This page enables users to validate the accuracy of system-recorded F&A charges by comparing them against a manually calculated F&A amount based on policy-driven account exclusions and applicable rates.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Expense Account: Expense Accounts types describe fund usage type
Key Performance Indicators (KPIs):
- PC Expense: Total project expenditures from the Grants Management transaction data, filtered to include only analysis types that reflect actual spending (ACT, GLE, PAY, SFA, SFE)
- PC Billed: Total amount billed on the project, based on billing related analysis types (FBD, BIL, BLD, OLT)
Visuals:
- PC Expense Overview: Displays detailed expenditure amounts broken down by account (rows) and analysis type (columns) for all expense-related transaction types.
- PC Billed Overview: Displays detailed expenditure amounts broken down by account (rows) and analysis type (columns) for all billing-related transaction types.
Overall Functionality: This page helps users evaluate whether project expenditures (or awarded amounts, depending on contract type) are appropriately billed by comparing PC expense and PC billed totals, supporting billing accuracy and financial compliance.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Expense Account: Expense Accounts types describe fund usage type
Key Performance Indicators (KPIs):
- GL Expense: Total project expenditures from the General Ledger transaction expense data.
Visuals:
- GL Expense Overview: Displays detailed transactional expenditure amounts broken down by expense account (rows).
Overall Functionality: This page reconciles expense details from the general ledger report with the total project expenses reported in other modules (SPSR, FFR, PC) to ensure financial alignment before closeout.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Expense Account: Expense Accounts types describe fund usage type
- Accounting Date: Date on which a financial transaction is officially recorded in the accounting system
Key Performance Indicators (KPIs):
- Post Term Transaction Expense: Total project expenses with accounting dates recorded after the official project end date
Visuals:
- PTT Analysis Overview: Summarizes total project expenditures from Grants Management transaction data, filtered for actual spending analysis types (ACT, GLE, PAY, SFA, SFE).
- Expense Details: Lists individual transactions in descending order by accounting date, breaking down by analysis type (column).
Overall Functionality: This page helps users identify and review any project expenses recorded after the official project end date, ensuring timely resolution of post-term transactions during closeout.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Cost Center: Default at blank
- Account: Categorized record used to track all financial transactions of a specific type, such as assets, liabilities, revenues, or expenses
Key Performance Indicators (KPIs):
- UAR Balance: Balance of Unbilled Accounts Receivable (Account #11820), reflecting revenue earned but not yet invoiced
- AR Balance: Total balance of Accounts Receivable accounts, showing amounts invoiced to sponsors but not yet collected
- AP Balance: Balance of Accounts Payable (Account #20110), representing obligations to vendors or other parties pending payment.
- DEF REV: Balance of Deferred Revenue (Account #23020), indicating funds received but not yet earned according to project milestones or reporting
- Revenues: All recognized income related to sponsored projects (Account #4%), reflecting earned funds during the reporting period
- Expenses: All project-related expenditures recorded in relevant accounts (Account #5%s + 6%s +7%s), tracking the consumption of resources
Visuals:
- Trial Balance Overview: Provides a summarized view of key account balances across revenue, expense, asset, and liability categories.
- AR Balance Table: Displays detailed accounts receivable balances, allowing users to monitor outstanding amounts owed from sponsors.
Overall Functionality: This page offers a comprehensive financial snapshot by consolidating key account balances, enabling users to review revenues, expenses, receivables, payables, and deferred revenue for effective project financial oversight and reconciliation.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Entry Type: Indicates the type of transaction recorded (INV, CR, MT, PY)
- Item ID: Invoice Item ID
Key Performance Indicators (KPIs):
- BP Payment: Total payments received as recorded in the Grants Management Invoice/Payment/Revenue Report
- BP Billed: Total amount invoiced to sponsors in the Grants Management Invoice/Payment/Revenue Report
- AR Payment: Total payments recorded for sponsored project under analysis Type PY
- AR Billed: Total invoiced amount recorded for sponsored project under analysis Type INV and CR
- AR Refund: Total refunded amount recorded for sponsored project under analysis Type RC
Visuals:
- Billing Payments: Compares invoiced amounts versus payments received for each invoice item.
- AR Details: Breaks down amounts recorded by analysis type for each invoice item.
Overall Functionality: This page enables users to monitor and reconcile billing and payment activities by comparing invoiced amounts against payments received, ensuring accurate cash flow tracking and revenue recognition.
Filters and Slicers:
- Project ID: Unique identify for UTRGV sponsored billing projects, sync across all pages
- Contract Type: Classification of the funding agreement (e.g., Fixed Price, CRB, LOC) that determines the terms and conditions of the project
Key Performance Indicators (KPIs):
- Direct Cost Budget/Expense: The total budget or expense recorded under all direct cost accounts.
- IDC Excluded Budget/Expense: The total budget or expense for accounts excluded from F&A cost recovery.
- Indirect Cost Budget/Expense: The total budget or expense for the G4180 IDC indirect costs account.
- Actual Funds Received: The total billed amount collected, as reported in the Accounts Receivable (AR) report.
- F&A Pending to be Charged: The uncharged portion of the G4180 IDC account, calculated as budget minus expense.
- Funds Remaining Direct Cost: The unspent balance of the direct cost accounts.
- Residual Transfer Balance Category: Categorizes the residual budget balance into three defined ranges, each with a corresponding next-step action.
- Investigator Shared Credit: The credit percentage for each PI/CPI, used to determine their share of the residual transfer.
Visuals:
- Residual Transfer Balance Summary and Action: Displays each project’s residual balance, categorizes the amount, and outlines the corresponding next-step action.
- Residual Income Worksheet and Budget Details: Provides a detailed breakdown of the residual transfer calculation and related budget details.
- Investigators Distribution Details and Cost Center Information: Shows each PI/CPI’s shared credit, distribution amount, and associated cost center for the transfer.
Overall Functionality: This page provides a comprehensive overview of remaining project funds, categorizes residual balances, outlines required actions, and details the distribution of funds to investigators and their associated cost centers.
Report Pages:
- KK SRC (UTS_OSPA_BURN_RATE)
Extracts project budget, expense, and encumbrance details with breakdowns by budget account, enabling users to verify available balances and spending compliance.
- PC SRC (UTR_GM_TRANSACTION_DETAIL)
Provides transaction-level project costing data including analysis type and accounting date, used to track billed amounts and detailed project expenses.
- GL SRC (UTR_GL_TRANSACTIONS_EXPENSE)
Contains transactional expense details by expense account, supporting total expense validation and cross-checking with other modules.
- TB SRC (UTZ_GM_TRIAL_BALANCE)
Includes full trial balance data with breakdowns by account and account type, used to reconcile revenues, expenses, and balance sheet items like AR, UAR, AP, and Deferred Revenue.
- BP SRC (UTZ_GM_INVOICE_PAYMENT_REV)
Lists invoiced and payment records by item ID, helping users reconcile billed versus paid amounts and identify outstanding accounts receivable.
- AR SRC (UTZ_GM_INVOICE_PAYMENT_REV)
Contains invoiced and payment records by item ID and entry type, supporting reconciliation by entry category to detect open or unmatched AR items.
- FFR SRC (UTZ_GM_AR_DETAIL)
Displays Accounts Receivable details by entry type and template, used to calculate the final invoice billing amount for sponsored projects.
Overall Functionality: The section allows users to download filtered extracts directly from the underlying PeopleSoft queries, providing transparency and flexibility for deeper reconciliation and analysis.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 support team for further assistance.