Month-End Variance Analysis

Variance analysis is an easy to use management tool that helps users achieve business goals and improve operational efficiencies. Performing a month-end variance analysis that your business or project is moving ahead according to plan.

In this guide we discuss the benefits of a month-end variance analyses and how to perform one. We will also share tips for best practice and explain how versatile features of MODLR helps you make the most of your data for routine monthly variance analysis purposes.

What is Variance Analysis?
Limitations of Month-end Variance Analysis in Excel
Doing Your Month-end Variance Analysis in MODLR

What is Variance Analysis?

Month-end variance analysis financial statements (The difference between what happened and what we thought would happen i.e. the Plan Budget/Forecast)

Variance analysis is simply the process of comparing two or more sets of data. Businesses use month-end variance analyses to gain a deeper understanding of how different aspects of their business operations are performing. The variance analyses helps you see what is going right in your business according to expectations, project milestones or budgets and what is not.

Using routine variance analyses on weekly, monthly or quarterly operational results can help identify potential trouble spots before it is too late. Being forewarned due to significant variances can help you be proactive in dealing with potential issues including disruptions, delays, discrepancies, fraud and control potential losses.

What are favourable and unfavourable variances?

In a variance analysis you will encounter three things: figures that are favourable, those that are unfavourable and those that have not changed from the budget, milestone or from previous period.

Favourable variances

Favourable variances are discrepancies from the expectation that are beneficial to the business. Examples of favourable monthly variances may be:

Sales volumes achieved in June are higher than the budgeted volumes.
Material purchase costs for June were lower than what was budgeted.

Unfavourable variances
Unfavourable variances are discrepancies from expectation that are non-beneficial and adverse to the business. Examples of unfavourable monthly variances may be:
- Staff turnover (numbers leaving the company) in June were much higher than the norm, in any month or compared to June the previous year.
- Sales returns in a certain month are significantly higher compared to previous or comparative time periods in a year.
What does the materiality of a variance mean?
Every business expects some level of variance in all their key performance indicators from one reporting period to the next. For example, sales revenues may go up and down. Manufacturing and purchase costs may show fluctuations. More employees may tend to leave a company more during certain periods of the year and more new entrants will join at other times. There will be higher sales and consequently higher levels of sales returns during the Christmas season.
This means that each line item in the budget, and each milestone is a project will have variances of some degree or another. That is to be expected. The role of variance analysis is to highlight the more significant variances that may spell good news or bad news for the business.
To know what is significant, and what variances can be ignored, each company must set its own materiality threshold. This may be a percentage or range within which the variance does not raise a flag for alarm.
Here are some examples:
- We can tolerate a 5 percent variance up or down in our monthly sales.
- We should expect upto 10 percent increase in purchase costs this quarter purely because the cost of our materials has gone up in the global market.
- Our travel, transport and shipping costs are going to be much higher this year because global energy costs are up. And we should be able to adjust our usual 5% threshold for this expense element up to a more practical level. (And then we should revise it downwards again when global energy prices come down eventually).
- Our normal staff turnover is between 2.5 percent and 5 percent in any reporting period. Anything over 5 percent should be investigated.
All such thresholds are subject to change, with changes that are taking place both within the company and in the business environment.
How do I use a monthly variance analysis?
Variance analyses are nothing more than a comparison of actual figures and expected figures. Business decision makers and managers can use monthly variance analysis in many ways to be in better control over their operations, to improve their business performance, productivity and effectiveness.
For performance measurement purposes
Monthly variances can be part of a company’s performance measurement system. Each manager's performance can be judged on how they have performed according to agreed Key Performance Indicators (KPIs). The variances can highlight the ability of a manager/unit head to be effective and efficient.
You can do performance analyses on any of the following and more.
- Profit margins
- Sales volumes
- Product pricing
- Regional and product line sales
- Monthly expense schedules
- Productivity indicators
- Service quality indicators
  - Number of complaints
  - Time taken to address issues
Put to proper use as a management tool a monthly variance analysis—can help a manager and a business perform at their best. Depending on the nature of your business or project, you can perform variance analyses at any other frequency, such as daily, weekly, quarterly or annually as suited.
To highlight developing issues and problems
Monthly variance analyses can help highlight developing operational problems and get to the root of underlying causes for them. In fact, this is the most important use of monthly and other more frequent —eg, weekly or daily—variance analysis exercises.
To flag discrepancies and potential fraud
Those companies and managers who take their monthly variance analysis reports seriously and investigate the reasons for material discrepancies will be able to correct emerging problems and potential fraud to take timely action. There is no point closing the barn door after the horse has bolted. Timely action could help prevent losses and correct performance issues.
To monitor competitiveness
Monthly variances can alert companies to changes in their competitiveness. You may be able to spot from your sales variances what actions by direct competitors and substitute products and services are affecting your sales performance adversely. Keeping an eye on trends in sales volumes, prices and margins of various product lines can flag how each is performing in the market. Once you can spot the emergent trends, its time for changing marketing strategies and other measures.
To monitor business productivity and profitability
Monthly variances may be used to monitor business productivity and overall profitability.
For auditors’ to perform analytical reviews
Auditors may routinely ask for data to perform variance analyses as part of their analytical review processes. They may even ask to see the results of your own variance analyses. Auditors use variance analyses as a way to ensure that the figures of a period under audit are free of obvious discrepancies.
Perform your own variance analyses at each month end. Knowing the reasons for unusual or significant variances ahead of time. This is a good way to get your audit done quickly. That way, instead of seeking explanations months later, you would have the answers to auditors’ queries ready on hand.
Important factors when performing a monthly variance analysis
To be actioned
Variance analysis is not an end in itself. Material—accounting and auditing speak for significant—variances beyond acceptable thresholds need to be investigated, their underlying causes understood and corrective action taken. Performing a monthly variance analysis means nothing if it does not get looked at as a regular practice.
Some companies perform variance analyses just for the benefit of their internal and external auditors. But businesses need to understand that monthly variances are not about merely looking back. Performing a variance analysis, on a monthly basis or otherwise, is about looking back in order to understand and correct our path towards better business performance in the future.
Variances are interrelated
All aspects of your business are interrelated. Changes in the profit and loss account, in assets and liabilities in the balance sheet and cash flow movements all impact others. This reality naturally extends to variances as well. To make better use of variance analysis, it is important to recognize that variances too may be interrelated. You cannot take individual expense items or KPIs on their own in order to make a sensible judgment.
Example 01
The month of December may show higher levels of sales returns (in value and number of returns) purely because there were more sales in that month. A more meaningful variance to check would be to compare the percentage of sales returns (by value or number of returns) against the value of sales and number of sales transactions. Over months and years, a company will know what levels of returns are to be expected when sales volumes increase at the year end. And this can then be a guidance for future variance analysts and managers.
Example 02
A new or inexperienced Sales Manager may see a 40 percent drop in January sales compared to the month of December. While it may seem as a cause for concern or panic to the new employee, the whole company or some product lines may be experiencing this variance each year in January and most sales managers and executives may be used to this trend. Similar trends may be particularly relevant for companies with seasonal sales patterns, such as luxury goods, toys and school supplies.
Variance analyses are only as good as the underlying figures
Variance analyses compare the actual results against a budget or the results or figures of a given period (most often the current or most recently ended) against a previous reporting period. Hence variance analyses are only as good as the underlying budgets and milestones you use in them.
The earlier example—of a 40 percent drop in actual January sales vs December sales—highlights the need for better budgeting that takes into consideration real life factors that may impact variance analysis results. Your variance analyses are only as good as the facts and budgets underlying them. You’ve heard of the saying that there are “lies, damn lies and statistics”. Be sure to make sensible use of figures and statistics when performing a variance analysis.
Instead of budgeting by increasing January sales to be a certain percentage over December sales, a more realistic January budget for items that show seasonality would have taken into consideration the lower sales levels that occur every January as a result of higher sales in preceding December.
The materiality thresholds, ranges and percentages are subject to change
When performing a monthly variance analysis, it is important to know that the materiality thresholds, ranges and percentages are subject to change. Bearing this in mind will help you find more nuanced explanations and to make better sense of what is going on.
Do not panic over just one variance, such as the one explained above of a 40 percent drop in January sales from December figures. Instead, knowing the interrelatedness of variances, seek to explain it with movements in other variances.
Need for frequent reality checks
To perform a reality check against your underlying figures used in a monthly variance analysis, you must understand the factors that are controllable by an individual or unit when setting KPIs. If your company fails to do that, you will demotivate your team members by judging them on factors that are beyond their control.
For example, there is little sense in asking the Transport Manager why the fuel expenses are out of the charts in the last quarter. Everyone knows that global energy prices have gone up significantly. And that is totally beyond the Manager's control.
However, in such instances the company may be able to manage these variances better by taking on future contracts or some other mitigatory measures.
In the post-COVID-19 world, the global supply chains are disrupted and some materials are in short supply; or their deliveries are delayed, causing production disruptions. There is little value in picking on the Purchasing Manager. Instead, explore what steps can be taken to remove the adverse impacts of this situation so that your business can operate without issues and disruptions.
How to set up month-end variance analysis in Excel
For a long time people have been performing their variance analyses using MS Excel worksheets. Despite its limitations, Excel has helped to get the work done. Within the past few years, more sophisticated software like MODLR has made doing the variance analyses and using them for taking meaningful management action faster and so much easier.
However, before getting into how MODLR can add immense value to the variance analysis process, let us explore how it can be done in Excel.
Decide on Goals
The first step before you get bogged down in your Excel worksheets is deciding why you are performing a variance analysis.
The common goals for performing a variance analysis includes:
- To measure and monitor performance
- For more efficient project management
- To get better operational control over the business
- To highlight developing issues and problems
- To flag discrepancies and potential fraud
- To monitor business competitiveness
- To monitor business productivity and profitability
- To provide to the internal or external auditors
Most companies use their variance analyses to serve multiple purposes, from among the above.
Depending on your goals, the exact format of your variance analysis sheet, and the source for your data will vary.
Gathering Data
The data gathering process, and what data is necessary depends on what your goals are. To perform any kind of variance analysis you need at least two data points.
For example, if your goal is to use the monthly variance analysis for performance measurement in the sales function, you may need two or more of the following to achieve your goal.
- Monthly sales budget figures in value (dollars) as well as in sales volumes/items
- Monthly budgeted sales variance parameters. That is, how much of a variance up or down the company tolerates from previous month sales (in value or percentage terms), corresponding figures from corresponding month in the previous year (level of change tolerated comparing 2022 June to 2021 June etc)
- Monthly actual sales figures for current year and for the previous year
- Actual monthly variances noted from current year (other months) and previous year
- Current month (most recently completed) sales figures in dollars and sales volumes/numbers
- If you sell a number of product lines and variants, it will be necessary to get the above data for each of them.
- Data for different product lines and sales departments and individual sales people.
- Data for online, instore, branch and other sales channels.
Challenges in Data Gathering
One of the biggest challenges in data gathering for a month end variance analysis is getting data from various sources into one Excel sheet. While we can pass it off as “gather your data”, the actual process of getting this done with an Excel sheet can be extremely challenging and time consuming.
Sometimes your management reporting system may be incompatible with Excel and therefore, exporting data becomes a challenge. Frequent updates on the system will then have to be exported again and again. Even when it can be imported there may be issues of getting them in the necessary format. Some variance analysts will find that they need to re enter data into Excel, which is time consuming. Date entry can lead to data accuracy and impact data integrity, making resulting variances meaningless.
These common limitations in Excel can be mitigated to a large degree by shifting your variance analysis (and all your management reporting) to MODLR. Those who do not plan to do so, or those who resist abandoning their Excel sheets can still benefit from MODLRs real-time data integrations. These make the variance analysis process faster, more accurate and efficient.
Define Variance Parameters
Variance by definition means how much something has varied from another data point. So before you can get started on your monthly variance analysis, you need to define the variance values, thresholds, ranges and percentages that you find acceptable.
Let us explain:
You want to simply compare your budgeted June sales against actual June sales.
- Variance value means how much of a variance (up or down) from budget you can tolerate. Are you okay with a $1,000 less or more from the budgeted figure of $50,000?
- Variance range and threshold means what range of a variance you are able to tolerate. You can express it as a range between $1,000 and 5,000 before it becomes a concern. If this is the case, you would be happy with a June actual sale of $45,000 or $52,000 or $55,000. But below $45,000 would be considered a cause for concern.
- Variance percentage means how much of a variance in percentage terms you can tolerate. You can say five percent up or down is tolerable. That is, in dollar terms.
Calculating Variances with Excel Formulas - Examples
Let us take a couple of examples on calculating monthly variances.
Example 01: Sales Variances
Here’s a Sales variance analysis for a business.
You can draw many observations based on this.
- The variance calculation is performed as: Actual sales - Budgeted sales. Therefore, any positive variance (where sales exceed budgets) is calculated and shown as a positive figure and positive percentage.
- Company achieves their sales targets most months.
- The company considers sales figures over or under $1,000 to be a normal occurrence. That is, their threshold of concern is left at $1,000 in absolute terms, negative or positive.
- We can see that all percentage variances above 10%, positive and negative, are highlighted as matters of concern. These figures vary by over or below $1,000 and over or below 10%.
- In April and May, the actual sales have exceeded the dollar threshold and the percentage threshold of 10%. They are highlighted in order to find out why.
  - Did anything special happen?
  - Were there any promotions?
  - What other reasons led to actuals being significantly higher?
  - And the company may be having special sales incentives for staff above this threshold.
- Whatever the reason, it makes sense to find out why, and either learn something positive for the future or adjust budgets next year accordingly. All action depends on the exact explanation received for the variance.
- In December, the company achieved sales figures significantly less than the budget, and well below their $1,000 threshold and a variance above 10%.
- This calls for explanations and what needs to be done in order to avoid a shortfall. Or it may just be excessive, overly optimistic budget figures. To be sure, you may want to check this with previous years’ sales for December, a few years running into the past. Either way, explanations and actions are needed.
Example 02: Sales Expenses Variances
The table shows a monthly Sales Expense Variances for the same company.
Here are the basic rules.
- The company expects sales expenses to amount to one third of their sales figures. As such the budgeted expenses are calculated as Budgeted Sales x 33%.
- The percentage threshold is considered 7%. Variances less than 7% either way are considered acceptable.
Here are the observations:
- In most months the company expense variance percentage is less than 7. There is little concern over the sales expense variances in such months.
- In January, the actual exceeded the 7% threshold. It is worthwhile to find out why.
- In April, the sales expenses are over budget by 39%. It is worthwhile finding why, especially considering that the sales increase that month is just 12% over budgeted figures.
- In May, while the 20% variance in expenses raises a flag, it is exactly the same increase as in the May sales figure. As such, there is little concern on that account. The variance is fully explained by the sales figures for the month.
- In December, the sales expenses exceeded the budget by 41%. At the same time, December sales were a shortfall of 11% from the budget. What is the reason for this?
  - Could it be that sales promotions, such as steep discounts and price cuts failed to deliver results?
  - Perhaps the expenses were spent on activities that failed to deliver expected returns.
  - While seeking the explanation for this figure, it may be critical to also consider the interactions between sales figures and sales expenses.
- Sometimes sales drive expenses. At others, sales promotions and advertising expenses may be driving sales. It all varies from company to company, industry to industry and even between product lines and variants of the same company. it may be that a new product was launched with must pomp and pageantry but failed to achieve expected sales levels.
- The bottomline is that there is much to learn from a variance analysis.
Comments in Analysing Variances
Asking why figures are the way they are and recording the commentary and discussion around the reasons is a critical element of a variance analysis exercise. When variance analyses are done using MODLR, you get the opportunity to keep a record of the ongoing commentary and discussion in one place, as opposed to being confined to an isolated version of a workbook on Excel.
The next step, beyond finding explanations, is to take meaningful corrective action in case of negative (disadvantageous) variances. When the variances are positive, it is useful to learn what went right in a big way and try to emulate the lessons to improve results in the future as well. With MODLR, all these decisions and actions can be recorded in an easy to refer format for future reference.
Compiling Management Reports
Reporting on variance analysis also becomes easier when using MODLR’s management report integrations and versatile data analytics.
Adjusting forecasts
As always, at the end of performing a monthly variance analysis, it pays to revise and adjust baseline figures as well as variance forecasts and parameters to reflect ground realities that change from month to month. This process is also easier on MODLR, as the decisions can be recorded for future reference without getting lost in a version of an Excel workbook, and difficult to locate later.
Limitations of Month-end Variance Analysis in Excel
Budgets aren’t always realistic
In Excel and otherwise, unrealistic budgets can lead to nonsensical results in a variance analysis. This is why the need for defining goals accurately and setting variance parameters carefully is critical for good outcomes and useful variance analysis results.
Variance analysis is labor-intensive
Variance analysis using Excel and even Google Sheets can be time consuming and labour intensive. Automating the process, for example, with an integration with MODLR can help overcome this limitation.
Commentary remains in the Excel workbook
Comments are critical to the variance analysis process because making sense and determining actions are dependent on the discussion around it. Anyone who uses Excel workbooks knows the hassle of sharing workbooks up and down. In many instances, due to confusion with different workbook versions, useful comments can fall by the wayside and be lost. This limitation in Excel can make the monthly variance analysis process unwieldy and time consuming.
Analysis results come too late to make a difference in future actions
Excel worksheets need to be shared around and inputs from different parties need to be consolidated into one version accurately. It can be time consuming and outright frustrating since the discussion around each variance gets stretched across a long time period rather than become a dynamic, real time process. As a result, the analysis results may come too late to make a difference.
All of these limitations can be overcome by using MODLR which can facilitate easy, fast, real-time dynamic discussions. MODLR enables large numbers of geographically dispersed users to engage in the discussion process in real time. Let us explore these features in greater detail.
Doing Your Month-end Variance Analysis in MODLR
How to perform month-end variance analysis on MODLR
For those using MODLR as their primary reporting software platform, performing the month-end variance analysis becomes an easy task. However, even those not directly using MODLR can still benefit from MODLR’s versatile features and easy integration with diverse accounting software and ERP platforms.
MODLR’s seamless data integration capabilities can help automatically import and process the latest information saving time. Using these you can connect your favourite platforms with MODLR and automate your data imports, streamline workflows and reduce manual processes. MODLR integrates with Excel and Google Sheets as well as accounting software including MYOB, Sage , Quickbooks and Xero. MODLR also offers integration with various enterprise resource planning (ERP) and customer relationship management (CRM) software including Netsuite and Zendesk. MODLR also offers seamless integration with database management systems including MySQL, PostgreSQL, Microsoft SQL, Oracle DBMS, SAP Hana, IBM's DB2 and Google BigQuery.

These powerful features save the time you and your team can focus on explaining variances and taking meaningful decisions and action based on the discussion.
MODLR can help highlight the biggest variances. The tools and features of MODLR created to facilitate simultaneous workflows and collaborative advanced planning can be used for speedy completion of variance analysis exercises. MODLR’s versatile analytical tools can help focus attention on the most critical items.
Visualising Month-end Analysis in MODLR. MODLR's customisable data visualisations offer flexibility and ability to customise dashboards to suit your exact needs. And you can do all this with no coding skills. You can perform the month-end variance analysis by using a range of visualising tools including waterfall analysis, bar graphs and side by side charts. You can also use colours to automatically highlight the biggest variances.
MODLR Faciliates a Dynamic Discussion Process
Making sense of variances necessarily requires inputs from various people.
MODLR enables smooth and easy collaboration to complete your monthly variance analysis. MODLR's large scale, multi-site, collaborative planning with no limits of scalability enable geographically dispersed teams to engage in rich discussions and reach faster decisions regarding highlighted and concerning variances.
MODLR can store the commentary generated during the monthly variance analysis. In contrast to Excel, this leads to a well documented discussion and recording of ideas explaining variances for easy future reference. Rather than relying on comments made in various versions of Excel worksheets, with MODLR, you can see the explanations for variances as well as the discussion and the decisions that were outcomes of the monthly variance analysis.
MODLR helps evaluate potential decision options to address variances. The modelling, analysis and “what-if” features in MODLR enable decision makers to intuitively build complex models and different scenarios on-the go. leading to faster decision making. With MODLR you can build infinite scenarios to gain an in-depth, comprehensive understanding of factors that bear on the highlighted variances. These features lead to flexibility and arriving at well-informed decisions.
MODLR’s Versatile Reporting Tools
MODLR helps generate commentary for board and management reporting. The same features explained above also supports generating reports for board and management discussions and decisions, where board approval is necessary.
Schedule a demo of MODLR's Variance Analysis solution
To see the MODLR Cloud in action and discover its variance analysis solution for yourself, schedule a demo here.

Annual Budgeting

Sales Planning

Management Scorecards

Management Reporting

Logistics

Financial Services

Construction

Retail & FMCG

News & Articles

Tutorials

Demos

Documentation

White Papers

Datasheets

Case Studies

Community Forum

User Group