Your most recent budgets have substantially high variances—well over 10%. Your management team will be concerned as to why, and will ask questions like:
- In November, how much did we budget for Production Volume Costs?
- What was the difference between what we budgeted and the actuals?
- Why is it so high at 17%? What happened in the month of November?
As a strategic advisor, you’ll need to know how to construct a variance analysis report in Excel to respond to their questions and identify plausible causes. Your report should be simple to produce, easy to read and illustrate the data to tell a story, giving your management team insights to help them with their analysis and decision-making.
In the following blog, we’ll go over four practices for visualizing one of the most important excel functions for finance – variance data in Excel,, three common issues with variance analysis and reporting, and how by automating these processes you’ll be able to provide management with the decision-making insights they need.
4 Best Practices for Visualizing Variance Data in Excel
1. Vertically Align Variance Amounts With Budgets and Actuals
Your management needs to see the variance amounts in relation to their budgets and actuals, as well as other variance amounts in relation to those budgets and actuals. Only by comparing these differences can they get insights to help them make better decisions.
The following are some examples of possible insights:
- 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, do the following:
- With your two completed charts (one for variance amounts, the other for budgets and actuals), 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 narrative and one story that provides your management additional insight.
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, follow these steps:
- 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 guessing which are adverse and which are favorable.
3. Insert Comments
Insert comments after you’ve used conditional formatting to answer your management’s concerns about what’s causing the variances. They’ll have questions (you know they’re coming) so be ready with answers in your report.
Some of the questions they might ask are:
- What’s causing our insurance costs to rise much quicker than projected?
- What is causing our electronics line to sell far less than expected?
- What is causing our Canadian employees to resign so much faster than expected?
- To provide possible answers, add comments by right-clicking the cell and then select ‘Insert Comment.’
Your remarks should address the following points:
- What do 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 the company.
While some organizations focus solely on adverse variances, it’s also crucial to consider positive variants. This is due to the fact that not all adverse variances have a negative impact on a business, and not all favorable variances have a positive one. While you may already be aware of this, don’t assume your management does.
For example, if your Total Production Volume Costs for November 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. Alternatively, if your Total Production Volume Costs saw a favorable variance—13% lower than anticipated—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 you’ve addressed all of your management’s inquiries, they’ll want to know:
- Up-to-date information about your company’s performance
- Whether or not your company is on track to accomplish its year-end objectives
For example, if an expense item had a favorable variance—22% less than expected—does that suggest your company discovered cost-cutting efficiencies 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, follow these steps:
- 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.
To track your forecasts, thermometer charts effectively and easily visualize performance and progress. Your management can now examine the company’s current performance (in real-time) and whether or not it is on track to reach year-end objectives.
Now that you’ve learned four best practices for creating an Excel variance analysis report, it’s time to learn about the most common issues that arise when analyzing and reporting variances.
3 Common Issues 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 provide an outdated report to your management, from which they’ll analyze and then make decisions—but it’s already too late. Your organization needs to close last month’s books as quickly as possible so that you can produce a timely report that will help your management make timely decisions.
2. Disparate Data Sources
Without data integration, it will be challenging to discover the reasons 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. Before you can create a comprehensive variance analysis report and obtain the full picture, you’ll need to integrate your data sources, such as ERPs, GLs, and HRISs.
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, therefore if each budget evolved from prior versions, you need to understand the historical budgeting logic and your organization must be able to retain budget calculations. Examining and comprehending the evolution of budgets and variances can assist you in creating a historically accurate report that your management can rely on.
Variance Analysis Visualization
Every variance analysis report should provoke questions. If your leadership doesn’t ask questions regarding your report, it’s an indication that it’s not well visualized. As a strategic advisor, the way you present your variance data and build your report—in a clear and easy-to-understand format—should add value to your company by highlighting areas for improvement and subsequent actions.
Automate Your Variance Analysis
Every finance department knows how challenging building a variance report can be. Regardless of the budgeting approach your organization adopts, it requires big data to ensure accuracy, timely execution, and of course, monitoring.
Datarails is a financial planning and analysis platform that automates financial reporting and planning while enabling finance teams to continue benefiting from the familiar spreadsheets and financial models of Excel.
Automating time-consuming manual processes, such as variance analysis reports, paves the way for finance teams to spend more time analyzing data and less time gathering it.