Quick Compensation Cost Modeling

A not-for-profit organization that serves the mentally disabled employs more than five hundred service personnel to care for its clients. Market conditions pressure the organization to re-evaluate its policies for compensating its nurses, trainers, and general service workers.

The human resources department, the finance department, and especially the board of directors wanted to know how changes in the compensation policies would affect the organization. The organization does not have the resources to perform multiple "what if" scenarios and analyze the costs. A compensation consultant was called in to help quantify and rationalize the new policy assumptions. But, calculating two simple scenarios with the new assumptions takes a month to complete.

I was hired to build an efficient compensation model that would reduce the turnaround time of calculating the impact of the new policy decisions.

• Met with the lead compensation consultant to understand the current situation and the desired deliverables
• Listened intently to the team discussion on the current and future compensation policies
• Built a compensation model that compared the cost of the two policies (current vs. the future) over five years
• Presented the model results to the team
• Enhanced and adjusted the model based on the ensuing team discussions

The final model performed calculations for six job grades in an unlimited number of scenarios. The team could easily adjust the parameters of the model (e.g. Yearly Raise Percentage or Turnover Percent) by changing cells in a spreadsheet model. The costs were instantly calculated and displayed in graphs and reports. The compensation team was able to make informed policy decisions and understand the tangible business costs of their actions.

I didn't know much about compensation policies and human resource issues when I began the project. (Job grades, wage ranges, turnover percentages, rates and bonuses, etc.) However, I was able to add significant value to the project after only three hours of discussion with the stakeholders and subject experts. I produced a relevant and insightful model a few hours after receiving the base employee information. Furthermore, the solution - an Excel spreadsheet - was ideal for their technical level.