Self-service headcount analytics using Microsoft BI: payroll analysis

Dan Bassett

Dan Bassett

As a CFO, you are responsible for budgeting and forecasting. Your organization’s ability to accurately budget and forecast helps drive investor and stakeholder confidence. In theory, one of the more basic costs to budget should be your employees and payroll, right?

You know how many employees you have, their relative cost, and how many employees you plan to hire. You also probably know how frustrating it can be to review monthly headcount cost reports (budget vs. actual) that contain large unexplainable variances throughout the year.

Payroll analysis_HR quarterly payroll anlaysisIt is not uncommon to see executives irritated by these variances in their budget to actual headcount cost comparisons. What will stakeholders infer if they see this? Do you know how to manage your workforce? If you cannot accurately forecast the cost of headcount, how can you forecast more complex, ambiguous parts of your business?

As a leader, you ask your Finance and HR organizations to understand why this monthly variance exists and request a resolution. You assume employees have the payroll budget and actual details at their fingertips to quickly perform this exercise.

However, without quick access to an accurate data set or proper analysis tools, employees scramble to coordinate a large manual data consolidation and cleanup effort. This leaves little remaining time for analysis, often resulting in a shallow and quick manual fix to the budget. Did you really get to the root cause of your variance for a long-term fix? Was it an issue with your organization’s budget process or assumptions? Was it due to external factors driving employee cost decisions?

While budgeting and forecasting should be an exciting time for your business to strategically plan and predict the future, it is often a dreaded exercise requiring planning to manage time, handoffs, personalities, and approvals. To employees, the only task worse than budgeting is being asked to revisit the budget, which requires a large manual effort to view data for all geographies and business units in order to organize the data properly. The payroll analysis story below was built using the Microsoft BI solution stack and can be implemented with any company that captures payroll information.


What if your organization had the proper business intelligence tools to not only capture the details of your payroll and budgeting activities but also enable you to quickly perform detailed payroll analysis and better understand your costs throughout the year? What if you could mitigate budget variances from occurring, and start to predict new variances with changing market conditions?

At Slalom, we were approached with such a scenario. By leveraging Microsoft BI and tapping into detailed payroll data, we worked with our client to develop a robust data model which supports a self- service headcount analytics solution. This solution enables analysts to quickly create powerful visuals to present valuable insights to their leadership on payroll measures and trends, ultimately improving the:

  • Forecasting business processes
  • Data capture & consistency
  • Assumptions & accuracy
  • Reporting & awareness

From the onset, analysts were empowered to easily build dashboards to show payroll costs (earnings, deductions, taxes, and workers comp) and budget vs. actual variances across many common company dimensions. For example, analysts could view data by month, location, leader, business unit, and account.

Payroll analysis_Dashboard payroll costs

The dashboard’s drill-down analysis enabled our client to understand and account for Q1 variances in budgeted payroll vs. actual payroll. The solution’s quick-filtering capabilities revealed that the budget assumed a straight-line employee 401K contribution throughout the year, when in reality, many employees maxed their contributions in the first quarter of the year, causing a discrepancy between budget and actual.

Payroll analysis_dashboard payroll costs 2A simple report would have taken weeks and multiple groups to reactively investigate this payroll variance, making the organization look uninformed and incompetent. But now, within minutes they’re proactively providing analytical solutions to drive stakeholder confidence in the abilities of the organization to know and predict their workforce costs.

HR and payroll analysis are often not priorities in an organization’s business intelligence strategy, but being such a significant part of the cost structure, it’s something companies should start considering. This type of solution allows organizations to quickly analyze the payroll details and trends for improved forecasting, but also provides the ability to assess new opportunities in payroll structures to support the changing workforce and drive value for the business.

To learn more about our self-service headcount analytics solution using Microsoft BI, check out our previous posts in the series:

About bassettdt
Dan is a Consultant in Slalom Chicago's Information Management & Analytics (IM&A) practice. Over the last 6 years, Dan has developed a technology agnostic functional skill set focused on IM&A projects as a Business Analyst or Project Manager. Working with client executives, analysts, architects & developers, Dan supports team efforts to ensure successful delivery of complex information solutions focusing on data integration, data modeling, reporting, dashboards & analytics.

One Response to Self-service headcount analytics using Microsoft BI: payroll analysis

  1. 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: