Budget to Actual Variance Analysis

Budget to Actual Variance – Excel Template

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 Budget to Actual Variance Analysis

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.