Downloadable Spreadsheets

Editorial Staff

TOOLS FOR QUANTITATIVE ANALYSIS OF PUBLIC FINANCE ISSUES

These models are not designed nor meant to replace a thorough financial analysis by a certified actuary or certified public accountant and are intended for educational purposes only.

A Method to Estimate the Pension Contribution and Pension Liability for Your City or County

With this spreadsheet, the user may evaluate the official data provided by the pension funds for any participant group – including an entire state, or any given city or county – and come up with a variety of estimates based on changing key assumptions. The user can then compare these estimates to the officially reported amounts for any pension fund’s unfunded liability as well as for its required annual catch-up and normal contribution. This spreadsheet’s default data is the based on the consolidated financial statements of California’s public employee pension plans, as compiled by the state controller.

Download Spreadsheet:  Impact-of-Returns-and-Amortization-Assumptions-on-Pension-Contributions.xlsx
Discussion/Tutorial:  A Method to Estimate the Pension Contribution and Pension Liability for Your City or County

How Lower Earnings Will Impact California’s Total Unfunded Pension Liability

This spreadsheet calculates the impact of new credit evaluation standards, proposed by Moody’s Investor Services to take effect in 2014, on the calculation of a pension plan’s liability. Using the most recent data that consolidates all state and local pension plans in California, provided by the California State Controller’s Office, this study revalues the accrued actuarial liability according to new criteria provided by Moody’s Investor Services.

Download Spreadsheet:  Impact-of-Discount-Rate-on-Pension-Liability.xlsx.
Discussion/Tutorial:  How Lower Earnings Will Impact California’s Total Unfunded Pension Liability

A Method to Evaluate Whether or Not Annual Contributions into a Pension Fund are Adequate

These spreadsheets help analyze whether or not annual total contributions are sufficient to ensure the long-term solvency of a pension fund. Using methods recommended in July 2012 and finalized in April 2013 by Moody’s Investor Services, these spreadsheets perform what-if scenarios, estimating the size of a pension’s unfunded liability at various rates of return. The spreadsheets also analyze whether or current contributions, both normal and catch-up, add sufficient assets to a pension fund to ensure solvency, and how much contributions would need to increase using more conservative assumptions regarding return on investment and payback periods.

Download Spreadsheet: Analysis-of-CalSTRS-Pension-Liability-and-Contributions.xlsx.
Discussion/Tutorial: Are Annual Contributions Into the California Teachers Retirement System Adequate?

Download Spreadsheet:  Analysis-of-OCERS-Pension-Liability-and-Contributions.xlsx.
Discussion/Tutorial:  Are Annual Contributions Into the Orange County Employees Retirement System Adequate?

Download Spreadsheet:  Analysis of Los Angeles Fire and Police Pension Plan
Discussion/Commentary:  How Much Do Los Angeles Police Officers Make?
Discussion/Commentary:  Los Angeles Police Average Total Compensation $157,151 Per Year

A Pension Analysis Tool for Everyone

This spreadsheet allows users to do their own “what-ifs” on pensions. Because this model has distilled the mechanics of a pension fund to a single page of data and calculations, it offers a glimpse of how pensions operate that is relatively understandable and extremely transparent. This model is not intended in any way to replace the far more complex models used by actuaries, but it can be quite useful to illustrate, for example, how very sensitive the required annual contribution to a pension is to any change in other assumptions – especially the rate of return. While this model is only designed to show the pension fund performance by year for one person, it is important to understand that pension funds that aggregate pension contributions and allocate pension benefits for thousands of people follow the same rules.

Download Spreadsheet: Pension-Analysis-Model.xlsx
Discussion/Tutorial:  A Pension Analysis Tool for Everyone

Application of this Spreadsheet to Compare Social Security, DB & DC plans:  Comparison_SS_DB_DC_Plans.xlsx
Discussion/Commentary:  Why Middle Class Private Sector Workers Are NOT “Ripping Off the Next Generation”

How Much Do CalPERS Retirees Really Make?

This study analyzes data from CalPERS, using nearly a half-million records obtained from CalPERS for 2012. In particular, this study presents data showing, by year of retirement, what the average pension benefits were in 2012. The study then normalizes these benefits to account for full careers using two benchmarks – the public sector “full career” expectation of 30 years, and the private sector “full career” expectation of 43 years.

Download Spreadsheet:  CalPERS-2012_Analysis_normalized-pensions-by-year-of-retirement.xlxs
Discussion/Tutorial:  How Much Do CalPERS Retirees Really Make?

Related study:

Download Spreadsheet:  CalSTRS-2012_Analysis_normalized-pensions-by-year-of-retirement.xlxs
Discussion/Tutorial:  How Much Do CalSTRS Retirees Really Make?

Public Employee Total Compensation Analysis – Total Workforce Evaluations

The following spreadsheets show truly representative averages for public employee compensation, because they use a template that allows the user to make adjustments to each payroll record, flagging those which don’t represent full-time workers. These three spreadsheets calculate average total compensation for employees of ALL California’s cities, ALL California’s counties, and ALL of California’s state agencies. They also breakout total compensation averages between miscellaneous employees vs. public safety employees. They do not however break out total compensation averages by department, for spreadsheets and tutorials that accomplish departmental breakouts, scroll down to the “Per Agency Evaluations” spreadsheets. These templates may serve as a model for further analysis, using for data any public employee payroll information.

Download Spreadsheet:  2012_Payroll_All-CA-Cities_CA-Controller-Data_CPPC-ANALYSIS.xlsx (44 MB)
Download Spreadsheet:  2012_Payroll_All-CA-Counties_CA-Controller-Data_CPPC-ANALYSIS.xlsx (52 MB)
Download Spreadsheet:  2012_Payroll_All-CA-State-Agencies_CA-Controller-Data_CPPC-ANALYSIS.xlsx (35 MB)

Discussion/Tutorial:  How Much Do California’s State, City and County Workers Really Make?

Public Employee Total Compensation Analysis – Per Agency Evaluations

Most publicly available databases showing public employee compensation don’t differentiate between part-time and full-time employees, or employees who only worked part of the year under analysis. This skews average rates of pay sharply downwards from what is truly representative. Frequently, these compensation databases don’t include the employer paid benefits, which further skews downward the reported average rates of total compensation. The following spreadsheets show truly representative averages for public employee compensation, because they use a template that allows the user to make adjustments to each payroll record, flagging those which don’t represent full-time workers, for example. These templates may serve as a model for further analysis, using for data any public employee payroll information.

Download Spreadsheet: Eureka_2013_Compensation-Analysis.xlsx
Discussion/Commentary: Eureka Faces Pension Headwinds – Just Like Every Other California City

Download Spreadsheet:  LAUSD_2013_Compensation-Analysis.xlsx (10 MB)
Discussion/Commentary:    LAUSD Offer Worth $122,938 Per Year – Will They Strike Anyway?

Download Spreadsheet:  Irvine_Total_Employee_Cost_2012.xlsx
Discussion/Tutorial:  City of Irvine 2012 Compensation Analysis

Download Spreadsheet:  Orange_County_Fire Authority_Total_Employee_Cost_2011.xlsx
Discussion/Commentary:    The Average Orange County Firefighter’s Total Compensation is $234,000 per Year

Download Spreadsheet:  Costa_Mesa_Total_Employee_Cost_2011.xlsx
Discussion/Tutorial:  City of Costa Mesa 2011 Compensation Analysis

Download Spreadsheet:  Anaheim_Total_Employee_Cost_2011.xlsx
Discussion/Tutorial:  City of Anaheim 2011 Compensation Analysis

Download Spreadsheet:  San_Jose_Total_Employee_Cost_2011.xlsx
Discussion/Tutorial:  City of San Jose 2011 Compensation Analysis

Download Spreadsheet:  Desert_Hot_Springs_Total_Employee_Cost_2011.xlsx
Discussion/Commentary:  Desert Hot Springs, California – Average City Employee Makes $144,329 Per Year

Download Spreadsheet:  Palo Alto_Total_Employee_Cost_2011.xlsx
Discussion/Commentary:  City of Palo Alto Faces Strike

Download Spreadsheet:  Redondo Beach_Total_Employee_Cost_2011.xlsx
Discussion/Commentary:  City of Redondo Beach Fights Unions

Download Spreadsheet:  San-Bernardino_2012-and-2011_Payroll.xlsx

Download Spreadsheet:  Stockton_2012-and-2011_Payroll.xlsx

MORE GREAT ARTICLES

The California Policy Center is a 501c3 non-profit public charity. CA Corp. # 3295222. Federal EIN 27-2870463.
Copyright © California Policy Center 2017. All rights reserved. Developed by The Liberty Lab, Inc.