Budget to Actual Variance Analysis

Role of Budget to Actual Variance in FP&A

The purpose of budget to variance analysis is to provoke questions such as:

• Why did one division, product line or service perform better (or worse) than the others?
• Why are selling, general and administrative expenses higher than last year?
• Are variances being caused by execution failure, change in market conditions, competitor actions, an unexpected event or unrealistic forecast?

The basis of virtually all variance analysis is the difference between actuals and some predetermined measure such as a budget, plan or rolling forecast. Most organizations perform variance analysis on a periodic basis (i.e. monthly, quarterly, annually) in enough detail to allow managers to understand what’s happening to the business while not overburdening staff.

How to Perform Budget to Actual Variance Analysis

Variances fall into two major categories:

1. Favorable variance: Actuals came in better than the measure it is compared to.
2. Negative variance: Actuals came in worse than the measure it is compared to.

When explaining budget to actual variances, it is a best practice to not to use the terms “higher” or “lower” when describing a particular line time. For example, expenses may have come in higher than planned, but that produces a negative variance to profit.

In addition, variances are relative to an organization’s key performance indicators (KPIs). If the organization utilizes a driver-based, flexible budget or plan where production costs come in higher in a period due to increased sales volume, than that may have a positive effect on organizational profit and show that in the budget to actual variance analysis.

Most variance analysis is performed on spreadsheets (Excel) using some type of template that’s modified from period to period. Most enterprise systems have some type of standard variable reporting capability, but they often do not have the flexibility and functionality that spreadsheets provide. Given the very ad hoc nature of variance analysis, spreadsheets are a very useful tool.

Related: FP&A Job Description and Responsibilities

Budget to Actual Variance – Excel Model Template

AMEX

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

The Classic: Budget to Actual Variance

You can never go wrong with a classic. Most people are familiar with what an actual to budgeted/plan variance analysis looks like. It is, as its name states, the comparison of actual results to budgeted/planned results. (And by adding some conditional formatting as in the screenshot below, you can more quickly identify where areas of opportunity lie.)

Variance to Prior Period and Same Period Prior Year

Taking the classic variance analysis one step further, an analyst can compare actuals to the period immediately prior and to the same period the prior year. Analyzing variances in this way will help bring to light potential changes in seasonality and timing changes that can help to correct future forecasts. (As a side note, it’s good practice to write notes on the variances directly on the variance analysis to the right of the data, as in the screenshot below).

Year-to-Date (YTD) and Forecast

An FP&A professional who has setup a variance analysis with the steps above might think she has a good handle on things, but there are two things management really wants to know that she can’t yet answer:

1. How are we tracking against budget/plan so far?
2. Are we going to hit, miss or exceed our annual targets based on new information?

To do this, the analyst needs to compare YTD actuals against YTD budget/plan as well as the full year budget/plan against the full year updated forecast, which would look like this:

How to Interpret the Variances

After performing the variance analysis steps above, an FP&A analyst needs to put on the “investigator hat,” go to the business areas, and ask what and why: What is driving the variances? Why were targets missed, hit or exceeded?

To test the underlying business logic, an analyst can:

• Analyze the elasticity of inputs (i.e. what is the impact of a 1 basis point change in iPhone margins on profit margins?).
• Look for offsetting or magnifying variances that may cause an inaccurate model to appear accurate at points in time.
• Using the same model, forecast the time horizon using only historical data. (What would have happened and why?)

The use of dashboards, sensitivity analysis and scenario analysis are an essential part of this interpretation process.

The analyst should also set levels of materiality. Does it matter if million dollar + line item is $100 off? Probably not.

In addition, it is important that, when seeking variance explanations, an FP&A analyst should come to the table prepared with as much data as possible. If you can help answer a variance explanation question with further data analysis utilizing the organization’s information systems, you will be much more appreciated than if you simply ask the question and expect a quick response.

Lastly, don’t just settle for an answer. Probe further to find out how you can help the business with whatever the relevant teams are struggling with. For example, if a business unit did not hit targets because it was unable to hire qualified staff in time, talk to Human Resources and find out if any initiatives are in place to correct this.