Your recent budgets had significantly high variances--higher than 10%. Your management team will want to know why and they'll be asking questions such as:
How much did we budget Production Volume Costs for in December?
What was the difference between what we budgeted and the actuals?
17%--why is it so high? What happened in December?
You're the strategic advisor. To answer their questions and to provide possible causes, you need to know how to create a variance analysis report in Excel. Your report should be quick to create, easy to read and visualizes the data to tell a story, providing insights to your management team to inform their analysis and decision making.
In this blog, we'll cover:
A variance report compares planned financial outcomes with the actual financial outcome that occurred - it's the difference between what was budgeted and planned vs what actually happened.
Your management needs to see variance amounts with their respective budgets and actuals--alongside other variance amounts and those respective budgets and actuals. Only when these variances are analyzed comparatively, can they gain insights to inform their decision making.
Some examples of insights they may glean include:
To vertically align variance amounts with budgets and actuals:
Your two charts are now one--one chart that tells one story, and one story that provides multiple insights for your management.
Watch the video to learn from Excel nerd Chandoo how to vertically align variance amounts with their respective budgets and actuals to gain decision-making insights.
To watch the full Advanced Excel Charts for Finance Professionals workshop on demand, become a Plan To Grow member and join the community. Don't miss out on mastering data storytelling.
Now that you've aligned your two charts together to tell one story, apply conditional formatting so that your management can quickly view which variances in your chart are adverse and which are favorable.
To apply conditional formatting:
You now have the option to select your two colors--one for negative values--to fill the bars in your chart so your management won't lose time to guessing which are adverse and which are favorable.
Notice how you can apply conditional formatting to quickly distinguish adverse variances from favorable variances.
Bonus Time-Saving Tip: Watch how you can use this "secret" Excel menu to replicate the style of formatting from one chart to another.
After applying conditional formatting, answer your management's questions about what's caused these variances by inserting comments. They'll have questions--you know they're coming--so be ready with answers in your report.
Some questions they may ask include:
To provide possible answers, add comments by right-clicking the cell and then select 'Insert Comment.'
Your comments should address:
While some organizations analyze only adverse variances, it's important to evaluate favorable variances as well. That's because not all adverse variances negatively impact a business and not all favorable variances have a positive impact. While you may already know this, don't assume your management does.
Example: If your Total Production Volume Costs for December saw an adverse variance--17% higher than planned--perhaps your sales were higher than forecasted. Simply, it cost your company more to manufacture that month because your customers were buying more. Conversely, if your Total Production Volume Costs saw a favorable variance--13% lower than planned--perhaps your sales were lower than forecasted.
Insert your comments to get ahead of your management's incoming questions.
Our software consolidates your financial, HR and operational data into one central location so it’s easy to find the data that matters.
After addressing your management's possible questions, they'll want to know:
Example: If an expense item saw a favorable variance--22% lower than planned--does that mean your company found efficiencies to save on costs, or is a project behind schedule? The answer is in your forecasts.
To illustrate progress and monitor performance, create a thermometer chart out of your budgets vs actuals chart to overlap your budget and actual numbers. To do this in Excel:
Thermometer charts effectively and quickly visualize performance and progress to track your forecasts. Now your management can see--instantly--the company's up-to-date performance and whether your company is on track to meet year-end goals or not.
Notice how you can create a thermometer chart to track forecasts and quickly illustrate progress.
You've learned four best practices for creating a variance analysis report in Excel, so now it's time to find out which common problems may occur when analyzing and reporting variances.
If last month's books still haven't closed, you can't create a report with only fresh numbers. You'll send an outdated report to your management, from which they'll analyze and then make decisions--but it's already too late. Your company needs to close last month's books faster so you can create a timely report that allows your leadership to make timely decisions.
Without data integration, you can't identify causes of high variances through a big-picture lens. Sure, your management may analyze your report, but neither you nor your leadership will feel confident that data isn't missing. You'll need to integrate your data sources--such as your ERPs, GLs and HRISs--before you can create a complete variance analysis report to get the full picture.
If you don't know how previous budgets were set, you can't deliver a report with any value to your management. Your variance analysis report derives its logic from the budget, so if each budget evolved from previous versions, you need to know the historical budgeting logic and your company needs to be able to retain budget calculations. Looking at and understanding the progression of budgets and variances will help you create a historically informed report which your management can trust.
Every variance analysis report should provoke questions. If your leadership doesn't ask questions about your report, that's a sign that it's not well visualized. As a strategic advisor, how you illustrate your variance data and create your report--in a clear and easy-to-read presentation--should provide value to your organization, by spotlighting areas of improvement and subsequent actions to take.
When you're inevitably asked, "What happened in December?" by using vertical alignment, conditional formatting, comments and thermometer charts in Excel, you'll be able to answer your management team's questions and provide them with the decision-making insights they need.
With Vena's Excel-based Variance Analysis software, you can automate your variance reporting. By consolidating your spreadsheet data and automating the addition of your actuals at the end of every period, your company can eliminate user errors, retain your calculations and distribute your variance analysis reports faster.
As Senior Director of Content and Communications, Jonathan Paul leads content strategy and execution at Vena, overseeing the development of owned media and content experiences that help finance professionals fuel business health, as well as their personal and professional growth. When he's not dreaming up new ways to offer audiences value through content creation, Jonathan loves to lose himself in an immersive video game with a solid narrative, lose golf balls pretending to be good at golf and lose time dreaming about time travel.
