What is a variance report? (and how to create one)

Creating a budget is an essential task for any well-run business. It gives executives and management teams an estimate of what the company income and expenses will be for an upcoming quarter or year. The word “estimate” is the key term in that description. Budgets don’t portray the actual results—think of them as an “educated guess.”

In other words, the final amounts of income and expenses at the end of the period will differ from what is projected in the budget. That difference is called a “variance,” and it’s important for getting a fuller picture of the financial well-being of a company. Variances can be positive or negative, indicating where budget projections exceed or fall short of the actual numbers.

What is a variance report?

A variance report is a written document that shows the deviations between the projected income and expenses listed on the budget and the actual numbers reported at the end of the period. The variance report lists the difference for each line item as both a number and a percentage.

With accurate reporting, a variance report is a valuable spend management tool that can be used to adjust cash outflows and provide realistic revenue expectations to company executives and business owners. It informs decision-making on how to cut costs, increase operating income, and create a more accurate budget going forward.

Variance reporting should be done regularly as part of the overall budgeting process. The exact cadence is dependent upon the type of business. Long waiting periods between variance reports can lead to wide disparities on budget line items. Companies that forego this process often find that their existing budgets are inaccurate after several reporting periods have gone by—this can cause a cascading effect across the business.

How to create a variance report

The easiest way to create a variance report is with an Excel spreadsheet. You can use the “data analysis” plugin if you prefer or opt to build the report from scratch. There are also Excel templates that might suit your company needs. If you choose to custom build in Excel, begin by creating a spreadsheet with these five column headers.

‍

The first three columns are for entering data, so no formulas are required for them. The "Description" column is to label the line item. “Forecast” comes directly from the budget. “Actual” is the actual number for that line item at the end of the period. Columns D and E are where the variance is calculated and displayed as either a number or percentage.

As an example, let’s assume that cell B1 contains a budget item for “office supplies” with a forecasted amount of $1000, but cell C1 has the actual amount spent on office supplies for that period at $1200. That’s a variance of 20%, or $200 from the budget. Since this is an expense, the variance would be listed as a negative number because the company went over budget.

Separating revenue and expenses on a variance report

A variance report needs to be readable for it to be effective, so presentation is important. For best results, it’s recommended that you place revenue line items and expense line items in separate sections. They can be on the same page, but there should be a clear distinction between the two. For instance, the revenue section would have the following:

• Revenue
• Cost of Goods Sold (COGS)
• Gross Profit
• EBITDA
• Net Income

Position the revenue section at the top of the variance report because it will garner the most attention. The “Expense” section, with line items that come directly from your company’s expense report, goes below that because it will be longer and therefore more tedious to go through in an executive business meeting. As a general point, most execs want to concentrate on revenue.

Calculating variance percentage and dollar amount

Let’s go back to the office supply example above.

‍

The forecasted cost for office supplies was $1,000 (Column B, Cell 1) and the actual cost (Column C, Cell 1) was $1,200. To calculate the variance as a percentage, the formula would be [(C1/B1)-1] * 100. Plug in the numbers and that’s 1200 ÷ 1000 = 1.20 – 1 = .20 * 100 = +20% over budget.

The same formula works for a number coming in under budget. Let’s assume that the actual number was $800 spent on office supplies for the period instead of $1000. The math looks like this: 1000 ÷ 800 = 0.80 – 1 = -0.20 * 100 = -20% under budget. If you enter the formula once and use autofill, Excel will calculate each line item for you.

As for the absolute value for column E, that’s done by simple subtraction. In this example, it would be C1-B1, so in the first case it’s 1200-1000 = +$200. In the second, it’s 800-1000 = -$200. Reading those numbers side-by-side with a percentage can tell you how much you need to increase or decrease expenses. Figuring out how to do that happens in the analysis phase.

How to analyze a variance report

‍

Some companies add a column between columns A and B where they can record the actual number from the previous period. This is done for analysis purposes and can be expanded to include multiple quarters or years if that’s deemed necessary. Being able to track variance across time can be valuable in determining how effective budget changes have been.

One of the goals of a variance report is to detect excessive spending and eliminate waste. It can be used for cost analysis and tail spend management, kept as a record of historical changes in revenue, or used by business owners to prepare their budget. To accomplish all that, it needs to include an analysis.

Analyzing a variance report involves more than just entering numbers and calculating percentages. A true analysis also includes an explanation for each variance. Why did expenses go up in that area? Is there a reason revenue is down this quarter? These are some of the many questions a variance report analysis is expected to answer.

Variances come in two categories, positive and negative, and most companies allow an acceptable margin for both that can be attributed to inflation, cost increases, or unforeseen market conditions. If this margin exists, it should be stated clearly on the variance report, along with the reasoning and methodology behind incorporating it.

The final step is to outline the potential effects of each variance on the company. If that effect is negative, include a proposed solution to correct it. If it’s positive, explore ways to expand on the trend and include those suggestions in your report. You won’t need to get too detailed, but at least offer a starting point for the business owner to develop a strategy.

What positive variance means

A positive variance, also called favorable variance, is when actual revenue is higher than budgeted revenue and actual expenses are lower than budgeted expenses. These numbers normally work in concert with one another, but it is possible to have a positive variance on revenue with a negative variance on expenses. That’s why the two sections should be analyzed separately.

This is important. Seeing a positive variance in revenue and just assuming expenses are down could be costly to the business. A positive variance, like a negative variance, should be investigated. Businesses should review the transaction data for accuracy, examine price changes, and look for areas where cost reductions may have led to the variance.

What negative variance means

A negative variance is the opposite of a positive variance. Revenue is less than what was forecasted, and expenses are more. In this case, it’s up to the analyst to determine the cause of these scenarios and offer potential solutions. For instance, how can the company cut costs in their office supply orders? Perhaps using a new supplier would solve that problem.

Organize the presentation carefully before sharing it with ownership. If a solution for a negative variance is complicated, draft an addendum and attach it to the report rather than trying to outline it on the spreadsheet. The analyst can’t be held responsible for the variance, but they can be faulted for a shoddy presentation. Be meticulous and organized.

How to simplify variance reporting with automation

The accuracy of expense and revenue numbers is critical to compiling an effective variance report. Using tools that offer expense automation and auto-generated revenue reporting can ensure accuracy and save time when it comes time to put reports together. The alternative is to rely on humans to reference paper reports. Unfortunately the margin for error is higher with that.

Variance reporting is important because it helps business owners track how their money is being spent and where changes need to be made. It’s not a one-time exercise. Companies that incorporate variance reporting into their budget processes tend to have higher profit margins and better cost control. Those are both keys to long-term success.