1 / 11

Real-Time Financials

This overview provides information about the Payroll Estimates and Financial Aid Award Estimates tables in the U-M Data Warehouse, including details on payroll estimates, FICA details, benefits details, and financial aid estimates.

jamesdowning
Download Presentation

Real-Time Financials

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Real-Time Financials Phase I: Financial EstimatesU-M Data Warehouse Descriptions

  2. Payroll Estimates – Overview The Payroll Estimate (PAY_ESTMT) table in the U-M Data Warehouse Payroll data set contains the detail data for the following financial estimates for regular employees: • Gross Pay (implemented as of 2002) • University cost for FICA • University cost for Benefits • Medical • Dental • University Group Life • Long-Term Disability • Retirement Contributions

  3. Payroll Estimates – Estimate Calculation • The payment date determines the Fiscal Year and Accounting Period. • The estimate process reads all the effective-dated rows for that Fiscal Year and Accounting Period in the following HR tables: • Job • Department Budget Earnings (Dept_Budget_Ern) • Additional Pay Data (Addl_Pay_Data) • The ShortCode and the Distribution Percentage in the Dept_Budget_Ern table determines the estimated pay per ChartField combination. • Estimates are adjusted for on-time and retroactive HR changes to the: • Funding source • Job data • Department Budget Earnings • Additional Pay • The calculation provides data for the previous, current, and subsequent Fiscal Year.

  4. Payroll Estimates – FICA Details The FICA estimate represents the University cost of the FICA expense based on eligible earnings defined by the Earnings Code and employee. • The FICA year-to-date gross amount is determined on a calendar year basis to calculate the FICA maximum. • The FICA estimate does not include: • Pre-FICA tax deductions • Earnings that do not “add to gross” (i.e., EARNINGS _TBL.ADD_GROSS =N) • OAS and Medicare split sub-totals

  5. Payroll Estimates – Benefits Details The Employee Benefit estimate represents the University cost for monthly benefit expenditures based on actual enrollments. • The Benefit estimate is determined by employee and Plan Type (e.g., Medical). • The Benefit estimate does not include: • Partial-month charges • Costs for Fellowship students

  6. Payroll Estimates Table Enhancements • New ERNCD (Earnings Code) value of “F” for FICA. • PLAN_TYPE field added to the table. Identifies by a code the type of benefit plan in which the employee is enrolled (e.g., 10 = Medical). • ESTMTD_ERNINGS_AMT field renamed ESTMTD_PAY_AMT (Estimated Pay Amount) to reflect the addition of FICA and benefit estimates.

  7. Financial Aid Estimate – Overview The Financial Aid Award Estimate (FA_AWARD_ESTMT) table in the U-M Data Warehouse Financial Aid / Student Financials data set contains the detail data for financial aid commitments. • The Financial Aid (FA) estimate includes: • Offered and accepted Financial Aid awards • Students with a net disbursement balance • The calculation provides data for the prior and current Aid Year. • FA estimates are distributed according to the ChartFields (ShortCode) associated with the Item Type. • The most recent effective-dated row for the disbursement date is used for the estimate summary.

  8. Financial Aid Award Estimates Table • AID_YEAR field value identifies the current financial aid year (e.g., 2007 = September 2006 – August 2007). • DISBURSEMENT_DATE field value is the date the award disbursement was made to the student or the date it is scheduled to be made. • ESTMTD_AWD_AMT (Estimated Award Amount) field displays the encumbered amount for the award disbursement to the student for the remaining Accounting Periods in the current Aid Year.

  9. Estimates Summary Ledger – Overview The Estimates Summary Ledger (ESTMT_SUMMARY_LEDGER) table in the U-M Data Warehouse Financials data set contains the summarized data for the following financial estimates: • Gross Pay (implemented as of 2002) • University cost for FICA • University cost for Benefits • Financial Aid

  10. Estimates Summary Ledger – Refresh • The refresh process interfaces with the Payroll Estimates and Financial Aid Award Estimates tables to summarize and load the data in the Estimate Summary Ledger table. • Payroll and Benefit detail estimates are incrementally refreshed daily and fully refreshed weekly. • Financial Aid detail estimates are fully refreshed weekly. • General Ledger summary estimates are fully refreshed based on the Payroll and Financial Aid refresh schedule. • A complete U-M Data Warehouse refresh schedule is located on the MAIS Web site at: http://www.mais.umich.edu/reporting/datasets_refresh.html

  11. Estimates Summary Ledger Table • General Ledger Payroll Estimate Summary (PAY_ESTMT_SUMMARY) table renamed ESTMT_SUMMARY_LEDGERto reflect the additional availability of estimate data. • LEDGER_EST_SRCE field added to the table. Identifies the source of the estimate data by a code (e.g., FA = Financial Aid). • ESTMTD_EARNINGS_SUMMARY_AMT field renamed ESTMTD_LEDGER_SUMMARY_AMT. This field displays the sum of all gross pay and benefit costs estimated for regular employees and financial aid awards within a specific set of ChartFields within a specific timeframe.

More Related