Self-service headcount analytics using Microsoft BI: cost-benefit analysis

Veronica Butler Slalom Consulting

Veronica Butler

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:

Health insurance

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.

Physical fitness

An average of the total population that gets at least 30 minutes of physical activity daily.

Green living

An average of the total population that are environmentally conscious by biking, walking, and/or taking public transit for their daily commute.

Cost-benefit analysis_health rankings by office

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.

Cost-benefit analysis_healthcare costs by city

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.

cost-benefit analysis_healthcare costs by city

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.

Retirement planning

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.

cost-benefit analysis_401K 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:

About veronicabutler
I have been a consultant with Slalom for 3 years and have over 10 years of data warehousing and Microsoft SQL Server experience in different industries, including Food Services, Real Estate, and Construction. During my time, I have developed enterprise data warehouses and managed data integration projects. I am involved in all aspects of delivering Business Intelligence and take the time to fully understand my clients' business needs to ensure maximum value in solutions delivered.

2 Responses to Self-service headcount analytics using Microsoft BI: cost-benefit analysis

  1. Pingback: Self-service headcount analytics using Microsoft BI: payroll analysis | The Slalom Blog

  2. Pingback: Self-service headcount analytics using Microsoft BI: salary analysis | The Slalom Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: