To create a spreadsheet that calculates (1) the total all in cost to an employer for a given employee (salaried) based on compensation package assumptions and and state of employment in the US, and (2) the gross value that the employee receives.
Total Employer Cost for an Employee
- Before any type of model can be constructed, extensive data has to be collected on state tax rates, laws surrounding PTO in each state, and average benefits provided to salaried employees either in the U.S. overall, or by state.
- The process needed to determine the employer labor burden includes data on salary, employer paid taxes, employer paid insurance, and other employer paid benefits.
- According to a tool provided by Quickbooks, employee costs should also include building overhead, utilities, property taxes, and equipment and supplies.
- State unemployment tax rates can vary based on many factors including whether an employer is new or established, what industry they are in, and how many employees have filed for unemployment. In many states, the rate is personalized to the employer and can only be obtained by requesting one from the state.
- This source provides a range of tax rates for all U.S. states.
- This resource provides valuable survey data on employee benefits from 2018.
Employee Compensation Statement
- An employee compensation statement is used to quantify benefits and perks, along with wages, that employees receive, so employees are able to see the full value of what their employer provides for them. This appears to be the same as providing the gross value that the employee receives.
- There are several free tools available that provide details on total employee compensation packages. CalcXML provides a tool here, and Jo Landers provides a limited version here.
- Jo Landers also offers a version based on an Excel spreadsheet that is available for purchase for $15.
Proposed next steps:
You need to be the project owner to select a next step.