This blog was first published in 2022 and updated on October 25, 2022.
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:
- 4 best practices for visualizing variance data in Excel
- 3 common problems in variance analysis and reporting
4 Best Practices for Visualizing Variance Data in Excel
1. Vertically Align Variance Amounts With Budgets and Actuals
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:
- Which expenses have the highest cost increases
- Which product lines have the lowest sales increases
- Which territories have the highest employee tenure decreases
To vertically align variance amounts with budgets and actuals:
- With your two charts (one for variance amounts, the other for budgets and actuals) completed, hold the alt (shift on Mac) key to align your variance amount chart underneath your budgets and actuals chart.
- Delete the title and x axis labels from your variance amount chart.
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.
2. Apply Conditional Formatting
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:
- Select your variance amount chart to open the Format Data Series menu.
- Check off the Invert If Negative box (located under the Fill heading).
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.
3. Insert Comments
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:
- What’s causing our insurance costs to rise much quicker than projected?
- What’s causing our electronics line to sell significantly fewer than planned?
- What’s causing our employees in Australia to resign so much faster than predicted?
To provide possible answers, add comments by right-clicking the cell and then select ‘Insert Comment.’
Your comments should address:
- What you think has caused the variance.
- Whether you predict it’s a trend or an anomaly.
- Whether you believe it’s positively or negatively impacted your company.
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.
4. Track Forecasts With Thermometer Charts
After addressing your management’s possible questions, they’ll want to know:
- Your company’s performance up-to-date performance
- Whether or not your company’s on pace to meet year-end goals
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:
- Right-click on your budgets and actuals chart.
- Open the Format Data Series menu.
- Drag the Series Overlap slider to 100% (Note: Ensure that your chart has multiple series within one category. Your bars won’t overlap correctly if your chart is plotting multiple categories within one series. To verify this, right-click on your chart and select Select Data. Select Switch Row/Column to toggle and ensure that your Budgets and Actuals are correctly listed as Legend entries.)
- Select the Budget portion of the overlapped bar.
- Select the Format menu tab at the top.
- Select ‘Shape Fill’ and then select ‘No Fill’ to remove the color.
- Select the Format menu tab at the top.
- Select ‘Shape Outline’ and then select the color that matches the color of the Actual bar.
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.
3 Common Problems in Variance Analysis and Reporting
1. Time Delay
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.
2. Disparate Data Sources
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.
3. Historical Budgeting Logic
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.
Variance Analysis Visualization
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.
Variance Analysis Software
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.