Publication number: ELQ-27995-1
View all versions & Certificate
Budget vs Actual Model—Driver-Based FP&A Reforecast with Price/Volume/Mix, Rolling Forecast & Scenarios | Excel & Sheets
A 17-tab Excel & Google-Sheets FP&A model: driver-based revenue (units × price), three scenarios, a rolling latest estimate, and a price/volume/mix split
by Hoda ElmorshidyUAE Financial Controller | Filing FTA VAT201 Returns Since January 2018 | 9 Years UAE Finance ExperienceFollow
Further information
Build a driver-based annual operating budget you can defend line by line, then keep it live as the year closes.
The model plans revenue from units × price per line rather than as a typed total, flexes the entire plan — revenue, expenses, reforecast, dashboard, sensitivity and the board narrative — from a single scenario cell, and rolls a latest estimate that swaps closed months to actuals while open months stay at plan. Its core objective is to answer why revenue moved, not just that it moved: every revenue variance is decomposed into price, volume and mix on the CIMA turnover basis, phased and scenario-flexed so the comparison is like-for-like, with a check column on the tab that re-derives the total independently and proves the three components sum back to the revenue variance exactly.
Beyond the variance work it allocates cost across four cost centres with a 100%-allocation guard, extends the base year into a three-year plan, stress-tests EBITDA on a two-way volume × cost-inflation grid whose base cell is wired to equal Annual Summary EBITDA in every scenario, converts the accrual budget to cash via DSO/DPO with the reconciliation shown, and produces a board one-pager whose narrative sentence writes itself from your own figures.
17 tabs, 829 live formulas, built from exactly thirteen functions so it runs unchanged in Excel 2016+ and Google Sheets. Ships pre-filled with a complete, tied-out worked year you can study before overwriting.
Best suited to a single operating entity in one currency that sells identifiable product or service lines at a unit price — up to four revenue lines and nine expense categories, on a 12-month fiscal year starting in any month. It fits practitioners who close and reforecast monthly and have to explain a variance to a board, a lender or a client: fractional CFOs and finance consultants running budgets across a client book (the multi-client commercial licence is included), FP&A managers and analysts, founders and operators, and bookkeepers extending into budgeting work. It applies best where units and price are separable in your source data, because that separation is what makes a genuine price/volume/mix decomposition possible — you cannot recover it from a revenue total after the fact. It also suits mixed Excel and Google Sheets environments, teams on older Excel installs, and any setting where macros or add-ins are blocked, since a formula-identical Sheets edition is included and the workbook uses no XLOOKUP, LET, LAMBDA, dynamic arrays or VBA. Cost profiles where COGS behaves as a percentage of revenue and the remaining costs are direct monthly lines will map cleanly; so will businesses on collection and payment terms of roughly a month.
The profit line is EBITDA by construction, so interest, tax and depreciation are not modelled and there is no balance sheet or three-statement build
— add below-the-line items yourself if you need statutory net income. Actual COGS is derived at the budget rate rather than entered, so no separate gross-margin variance appears; under a scenario cost factor other than 1.00 the resulting COGS movement is an artifact of that derivation, not cost performance, and the Actuals tab states this directly. Single entity and single currency only: no consolidation, no FX, no headcount or payroll module. The Cash Bridge is a working-capital approximation, not a treasury forecast
— it opens at zero receivables and payables, applies a single-month lag and caps DSO and DPO at 30 days, so 45- or 60-day terms need to be modelled separately. The variance split is valued at budgeted price (turnover basis), which is what makes it tie back to the revenue variance exactly; a contribution-basis bridge answers a different question and is not included. Structural limits are four revenue lines, nine expense categories, four cost centres and one budget year with an optional three-year extension. One operating rule matters: enter actuals only through the month set as Current month on Setup
— months keyed beyond it misstate every year-to-date figure until you advance it, which is why the Actuals tab raises a REVIEW warning exactly when that happens. If your general ledger gives you only a revenue total for a line, you can still use the decomposition by backing into a units-weighted average price, but a simple average will distort the split.
