Where did you miss budget,
and does it matter?
Put the budget and the actual for each line in one file. See the variance in dollars and percent for every line, which lines are over, under or on budget, and which few lines account for most of the movement.
The formula and the sign
Variance is actual minus budget. A positive number means the line came in above budget, a negative number below. A line budgeted at $50,000 that came in at $56,000 is $6,000 over, which is 12 percent.
Whether that is good or bad depends on the line. Above budget is bad for a cost and good for revenue, so the tool reports over, under or on budget and leaves the favorable or unfavorable call to you.
Three questions this answers
- Which lines are over, under and on budget?
- Which lines account for most of the movement?
- Is a flat total hiding large swings that cancel out?
What your file needs
- One row per line item: an account, category or department.
- A budget column and an actual column for the same period, as numbers.
- Lines that appear more than once are added together.
- At least 3 usable rows and 2 distinct line items.
What you get back
- Total budget, total actual and total variance.
- The variance in dollars and in percent for every line.
- Each line marked over, under or on budget. The tool counts a line within 1 percent of its budget as on budget.
- The biggest swings ranked by dollar impact, with each line’s share of all the movement.
- The method named and the R code.
Questions people ask
How do I calculate budget variance?
Subtract the budget from the actual for each line, then divide by the budget for the percentage. The tool does this for every line and ranks the results.
What is a favorable variance?
One that helps the result: revenue above budget or a cost below it. The tool does not guess which kind each line is, so it reports the direction and you read it against the line type.
What variance is material?
There is no universal threshold. The tool treats a line within 1 percent of budget as on budget and ranks lines by dollar impact, so the lines that matter most to the total come first. Set your own cut-off for your size of business.
Static or flexible budget?
This compares actual with the fixed budget in your file. A flexible budget restates the budget for the volume you actually sold, and this tool does not do that. For a volume-driven line, put the flexed budget in the budget column yourself.
How do I do this in Excel?
Add two columns, actual minus budget and that over budget, and sort by the absolute dollar difference. This tool does the same for every line, adds the classification and the share of total movement, and names the method.
Have your budget and actuals ready?
Try the example file first, then drop in your own.