Self-service headcount analytics using Microsoft BI: cost-benefit analysis
March 17, 2014 2 Comments
An attractive employee benefits package that is affordable to a company can assist in recruitment and retention, reduce employee out-of-pocket expenses, and increase employee morale and loyalty. To keep employees healthy and provide a good work-life balance, executives review the standard components of healthcare options, retirement plans, and other incentives annually.
But a big challenge for most organizations lies in keeping employees engaged with their benefits and ensuring they take advantage of the benefits offered. The benefits story below was built using the Microsoft BI solution stack and can be implemented with any company that captures benefits information from source systems or through employee surveys.
Health benefits analysis
We first begin by reviewing the overall health rankings by city in each of the company’s markets. This information has been obtained externally and integrated directly into the headcount analytics solution. Using Excel and Power View, we can review the performance of each market compared to the national average targets in four key areas:
An average of the total population that have some form of health insurance coverage.
An average of the total population that eat the recommended 5 servings of fruits and vegetables per day.
An average of the total population that gets at least 30 minutes of physical activity daily.
An average of the total population that are environmentally conscious by biking, walking, and/or taking public transit for their daily commute.
At first glance, we notice that the Dallas office is struggling with three health goal metrics noted in red, including Health Insurance Utilization and the Public Transit Program Utilization. We then drill down further to get a better view of the Dallas office healthcare benefits usage.
On average, Dallas’s healthcare costs are significantly lower than the Portland office, which was the company’s healthiest city from the previous dashboard. This would indicate that Dallas employees are not taking advantage of company benefit programs. Let’s drill into Dallas’ healthcare deductions to see if we can understand why.
As the bottom chart shows, Dallas is accounting for only a fraction of the company’s healthcare costs. Executives believe that the Dallas office lacks understanding of the healthcare benefits that are made available by the company. It may be in the company’s best interest to send pamphlets or set up webinars to increase awareness of the benefits offered.
Now that we have worked to improve health benefits usage, let’s do some analysis on the company’s financial benefit package. The company recently increased its 401k matching options and wants to see if this change has driven more employees to utilize the retirement program and increase their contributions.
The time series above shows how employees contribute to their 401K based on tenure and position over time. We gain deeper insight into which positions in the company are taking advantage of the increased company 401K match. It appears that positions with less tenure are actually contributing more to their 401K than employees in positions that have been with the company for longer periods. Executives suspect this is due to a push by the recruiting team to use the new 401k matching as a selling point when hiring new employees. This drives the executives to work with the HR department to send out a reminder to existing employees about the company’s new and improved benefits. The data in the dashboard lead to an action which will help improve usage of the company’s retirement benefits package.
To learn more about our self-service headcount analytics solution using Microsoft BI, check out our previous posts in the series: