What is a flexed budget?

It is the budget restated at the volume actually made. A fully variable line budgeted at 12,000 for 1,200 units is allowed 10,000 when only 1,000 units are made, so an actual of 10,800 is 800 over. Against the static 12,000 it would look 1,200 under. The workbook flexes each line by its own variable share.

How do I choose a cost line's variable share?

Estimate it from how the budget was built: the part that is per unit of volume. Metal is 100%, depreciation 0%, a line budgeted as 20,000 fixed plus 80 per tonne at 1,500 tonnes (120,000 variable) is 120,000 / (20,000 + 120,000), about 86% variable. The Setup list holds it per line and every month uses it.

When is a line flagged Out of limits?

When its spending variance is above both Setup thresholds: the amount (2,500 in the example) and the share of the flexed budget (5%). A 3,000 overspend on a 40,000 line (7.5%) is flagged; a 3,000 overspend on a 300,000 line (1%) is not. A line far under both is flagged Check, often a timing or accrual error.

What is the volume effect?

Flexed budget minus static budget: how much the allowed spend moved because volume differed from plan. With 900 tonnes made against 1,000 budgeted, a fully variable 20,000 line has a volume effect of -2,000. Spending variance plus volume effect equals the total variance against the static budget.

Can I load the whole year's budget before the actuals?

Yes. Enter budget rows for all twelve months and leave Actual blank until each month closes. Rows without an actual show no variance, and a blank report month on the Dashboard means the latest month that has actuals.

Is there a CSV version?

Yes. The CSV with live formulas holds the Monthly Results log with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Spending variance, volume effect, total variance and the flag are live formulas; the group, variable share, volume ratio and flexed budget are typed values because they come from Setup and the Volume sheet. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.

Finance and Investment

Plant Budget vs Actual Excel Template

Compare each month's plant spending with a budget flexed to the volume you actually made, and see which cost lines are out of limits and why.

Overview

This plant budget vs actual template is a monthly cost review workbook for plant managers and plant controllers. It ships with nine months of example data for a fictional iron foundry: twelve cost lines from metallic charge and melting electricity to depreciation, with volume running below budget from May, an electricity tariff rise and an emergency furnace reline, so the Dashboard has a real story to tell.

A static budget is set for a planned volume. When the plant makes less, the metal and energy it buys should fall too, so comparing actual spend with the static budget hides overspends. The workbook flexes every line: budget x (1 - variable share + variable share x volume ratio), where the variable share is yours to set per cost line. The spending variance, actual minus flexed budget, is what the plant controls; the volume effect, flexed minus static budget, is what volume did. Together they make the total variance.

Each line is flagged in words: Out of limits when it is over its flexed budget by more than both of your thresholds, Check when it is far under, otherwise Within limits. A line out of limits must carry a comment or the Check column says so. The Dashboard picks any month, shows the month and the year to date, cost per unit of volume and the biggest overspend, and breaks the year down by cost line, group and month.

What this template includes

Setup for the plant, currency, volume measure, first month of the budget year and two variance thresholds (an amount and a share of the flexed budget)
A cost-line list with each line's group and variable share: how much of its budget moves with volume
Volume sheet: budget and actual volume for each month, and the volume ratio that flexes every budget line
Monthly Results log: one row per cost line per month with budget and actual, flexed budget, spending variance, volume effect, total variance and a variance flag in words
A Check column that asks for a comment on every line out of limits and names missing actuals, missing volume and lines entered twice
Dashboard with a report-month picker: spend in the month and year to date against the flexed budget, cost per unit, the biggest overspend and the volume effect
Two charts: spend by month against the flexed and static budget, and spending variance by cost line for the year to date
Tables by cost line, by cost group and by month, with year-to-date flags against your thresholds

Suggested use case

Use it for the monthly cost review of a plant or department: to separate what volume did to the budget from what the plant spent, to see which lines need an explanation, and to keep that explanation next to the number.

How to use it

On Setup, type your plant name, currency, volume measure, first month of the year and your two variance thresholds, then list your cost lines with the share of each that moves with volume.
On Volume, type the budget volume for every month and the actual volume as each month closes.
On Monthly Results, add one row per cost line per month with its budget and actual; paste them from your ledger export.
Write a comment on every line the Check column asks about.
Open the Dashboard and pick the report month, or leave it blank for the latest month with actuals.

Frequently Asked Questions

What is a flexed budget?: It is the budget restated at the volume actually made. A fully variable line budgeted at 12,000 for 1,200 units is allowed 10,000 when only 1,000 units are made, so an actual of 10,800 is 800 over. Against the static 12,000 it would look 1,200 under. The workbook flexes each line by its own variable share.
How do I choose a cost line's variable share?: Estimate it from how the budget was built: the part that is per unit of volume. Metal is 100%, depreciation 0%, a line budgeted as 20,000 fixed plus 80 per tonne at 1,500 tonnes (120,000 variable) is 120,000 / (20,000 + 120,000), about 86% variable. The Setup list holds it per line and every month uses it.
When is a line flagged Out of limits?: When its spending variance is above both Setup thresholds: the amount (2,500 in the example) and the share of the flexed budget (5%). A 3,000 overspend on a 40,000 line (7.5%) is flagged; a 3,000 overspend on a 300,000 line (1%) is not. A line far under both is flagged Check, often a timing or accrual error.
What is the volume effect?: Flexed budget minus static budget: how much the allowed spend moved because volume differed from plan. With 900 tonnes made against 1,000 budgeted, a fully variable 20,000 line has a volume effect of -2,000. Spending variance plus volume effect equals the total variance against the static budget.
Can I load the whole year's budget before the actuals?: Yes. Enter budget rows for all twelve months and leave Actual blank until each month closes. Rows without an actual show no variance, and a blank report month on the Dashboard means the latest month that has actuals.
Is there a CSV version?: Yes. The CSV with live formulas holds the Monthly Results log with the example rows, units in every header and the settings the formulas read listed below the data, so it opens in any spreadsheet app. Spending variance, volume effect, total variance and the flag are live formulas; the group, variable share, volume ratio and flexed budget are typed values because they come from Setup and the Volume sheet. The dashboard, charts, dropdowns and self-check are in the .xlsx. Excel in a locale that uses semicolons or non-English function names may need Data > From Text/CSV; the .xlsx works everywhere.