Creating a budget is an essential part of any business. It gives executives and management teams estimated income and expenses. But these two, in the end of the period, will differ from what was projected in the budget. This result is the “Variance”.

Once a budget is made and passed down by management, there are mechanisms where organizations check its success. A variance report is one of the most commonly used accounting tools, and it’s essential to get the full picture of the overall company’s finance. 

Throughout this guide, we’ll look at its function, how to make variance reports correctly, and how to analyze them.

Variance reports compare forecasted financial results with actual results regarding a specific period. It highlights the difference in value between projections and tangible business outcomes. 

With accurate reporting, a variance report is a valuable spend management tool that is often seen for controlling future costs and providing realistic revenue expectations to company executives and business owners. 

These variances are reported both in percentage and absolute terms, allowing accountants to identify the severity of the variance with context. Also, it should be done regularly as part of the overall budgeting process, although the cadence of reporting will vary depending on the type of your business.

Variance reports help organizations be proactive in making changes to their operations. These reports are useful to identify when changes are required, highlight management concerns, and add legitimacy to managerial sentiment.

Variance reporting always starts with a budget and forecast. Without these two, the actual results have nothing to be compared.

Regardless of the approach taken, all budgets will result in a financial plan. This serves as the foundation against which actual results are compared.

All kinds of variance reports typically follow the same steps, as we’ll see below.

Organize your budgeted information into columns allowing you to aggregate the data points you wish to compare easily. 

Then, in the columns next to your forecasted data sets, aggregate the actual data for the period. This would be the actual balance sheet, income statement, and cash flows for financial variance reports. Ensure that the columns are clearly labeled.

In the next column, determine the difference between your budgeted and actual data. 

Pay close attention to the direction of the formula. For example, if expenses are higher in the forecasted timeframe than the actual, it is positive. 

Then, in the next column, calculate the percentage difference. It can be done using the formula (Actual Results / Forecasted Results)-1x100.

This is the most critical aspect of variance reporting. All variances should be investigated and explained in the final management report. 

Some companies add a new column where they can record the actual number from the previous period. Tracking variance across time can be valuable in determining how effective budget changes have been.

One of the objectives of a variance report is to detect excessive spending. It can be used for cost analysis and tail spends management, kept as a record of historical changes in revenue numbers, or used by any business owner to prepare their budget. To achieve all of this, it needs to include analysis.

At last, a variance analysis outlines the potential effects of each variance in the company. They can come in two categories, positive or negative. Most organizations allow an acceptable margin that can be attributed to external factors. 

If a variance comes out as negative, include a proposed solution to correct it. If the variance is positive, explore any meaningful way to expand the trend and write those suggestions in your report. Offer a starting point for the business owner to develop a strategy.

A positive variance is when actual revenue is higher than budgeted and actual expenses are lower than budgeted expenses. These numbers work in tandem with one another, but it is possible to have a positive variance in revenue with a negative variance in expenses.

A positive variance, like a negative one, should be investigated. Businesses should review the transaction data to ensure accuracy, examine price changes, and look for areas where cost reductions may have led to the variance.

In a negative variance, revenue is less than forecasted, and expenses are more. It’s up to the analyst to determine the cause of these scenarios and offer potential solutions. 

Organize the presentation carefully before sharing it with ownership. If the solution for a variance is complicated, you can draft an addendum and attach it to the report.

In a variance report, some basic parameters keep shifting regularly. Depending on the variance result, it will fall under different types of variance categories.

When the actual price of the material differs from the standard price, you get this variance. This is also a result of the difference in the actual output and usage levels compared to the standard ones. In this case, keep in mind some factors:

• Actual usage. Sometimes you can get quantity discounts when buying larger quantities, so more materials get used to accommodating the price difference.
• The actual price is lower or higher than the standard market rate.
• Quality of materials. 
• The availability of products. It can add a different dimension to the overall variance report in a significant manner and alter the final deductions.

When the usage levels are different from the standard rate, it leads to the formulation of the following report:

• Replacing the standard material with an alternative can affect usage.
• The relative yield of the material needs to conform to expected levels. In case there is an anomaly here, it results in a variance.
• The scrap rate from the material used can also play a crucial role in the ultimate deductions.

It refers to the cost of labor. At times it differs from the standard or assumed rate. There can be several reasons for this making a difference in the variance report:

• The standard wage rate is significantly below or above the existing ones.
• Actual labor hours against standard labor hours can also affect quality and quantity.
• Impact of special situations (strikes, lockouts, etc.)
• The overall efficiency of the labor.

It refers to the overhead costs in a business. Technically it can be anytime and anything. The trick is all about incorporating the potential variables:

• Sudden, one-time expenses or emergencies have a bearing on the overall input cost.
• Potential external factors that might affect the demand for the final product.
• Labor unrest or special steps are taken to offer additional financial compensation to the laborers for a specific year.
• Faulty production due to any oversight will have to be rejected, and the company has to digest the expense.

An Excel spreadsheet is one of the easiest ways to create a variance report. You can look for free templates on the internet. You just need to download the one you like that fits your needs and import it. 

If you need a key for Excel, there is one available in RoyalCDKeys where you can get the whole Office 2021 package at a low price.

You can make one of your own if you already have some knowledge of Excel.

Consider making the first tab a menu. This will help provide context to yourself or the end-user by providing information on the budget period. 

On this tab, enter the budget period's start date and end date. These cells will be referenced in later parts of your workbook, and label the sheet as “Menu.”

You will enter the budget forecast amount for the specified time period on a new sheet. Across your column, headings should be dates.

Link the starting date of your budget forecast to the start date in your “Menu” worksheet. You can create dynamic and flexible dates in Excel by using the formula “=DATE(Year(The start date cell), Month(The start date cell)+1,1)”. 

After that, simply drag this cell across the desired number of columns, and the dates will auto-populate.

Sum each budget category and the totals in each column using the function “=SUM(starting cell:ending cell). Then, drag it across the specified date range.

To get the most from your budget forecast, sum your revenue lines and expense lines, and create a separate row for net income using the formula “=TOTAL Revenue cell - TOTAL Expense cell.”

This tab will contain your budget's actual amounts for each period and category. This is where you can drop a raw data export if you have one. 

If you don’t have a similar format using raw data, model this tab after your “Forecast” tab and create a new tab as “Data.” Simply copy and paste the raw data into this new tab and use reference formulas, like VLOOKUP and INDEX MATCH, to pull the values into your “Actual” tab.

Model this fourth worksheet exactly after your “Forecast” sheet. In this tab, you will pull in the actual values for the periods you have them for and the forecasted amounts for the periods that have yet to happen. You can do it with the formula “=IF(Date cell>=Today(), Forecasted value cell, Actual value cell).”

In a final worksheet, copy the same layout and format you have for your “Actual” and “Forecast” tabs.

You can automatically calculate variances in the cells as actual data becomes available with the formula: =IF(Actual value cell=””,0,Actual value cell/Corresponding Forecast value cell-1).

You can choose to format the cells further using the “conditional formatting tool” provided in Excel. For example, if actual revenue amounts are less than forecasted amounts, the conditional formatting would be “highlight red if<0”, meaning the cell will be highlighted red for any negative amounts.

Variance reporting and analysis will help you manage risk on future sales. Well-thought variance reports will check the actual performance and accurately determine the next steps in the finance department. 

You will see which parts are failing, change their course in time for the next project budget and improve your business financial statement.