# Budget vs Actuals Tracker Example A 12-month variance tracker that compares planned budget against recorded actuals across revenue, COGS, gross profit, opex, and EBITDA - with per-month dollar and percent variance plus a YTD summary in traffic-light status. - Canonical: https://finamodel.com/examples/budget-vs-actuals - Excel download: https://finamodel.com/templates/budget-vs-actuals.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Beginner - Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Controllers, Operators - Tags: budget, actuals, variance, fp&a, p&l ## Overview A budget vs actuals tracker compares a planned monthly P&L against recorded actuals across revenue streams, COGS, gross profit, opex categories, and EBITDA, then surfaces dollar variance, percent variance, and a traffic-light status against user-set thresholds. The model is built as four parallel sheets - Budget, Actuals, Variance, Summary - that share the same row layout so every figure in the variance report ties cleanly back to its source cell. The Budget sheet is driven by editable assumptions: month-1 base values for each revenue and cost line plus a monthly growth rate that compounds across the 12-period horizon. The Actuals sheet starts as Budget × (1 + actual variance) per line so the report works out of the box, but in live use those formulas are replaced with bookkeeping data; the variance machinery recalculates immediately. The Variance sheet shows per-cell Actual − Budget with a divide-by-zero-guarded percent variance below it. The Summary sheet sums YTD figures and applies an On track / Watch / Off track status using the absolute percent variance against thresholds the user sets in the Assumptions sheet. CFOs, FP&A teams, controllers, and operating leaders use this template for monthly close reviews, re-forecasting decisions, and one-page operating snapshots for executives or boards. The traffic-light convention turns a wall of numbers into a focused list of lines that need explanation, and the parallel-sheet layout means anyone reading the report can pivot from a flagged Summary line straight back to the underlying monthly trend. ## What's included - 12-month Budget driven by editable revenue, COGS, and opex assumptions - Matching Actuals sheet with same row layout for clean cross-reference - Per-month dollar variance and divide-by-zero-guarded percent variance - YTD Summary with budget, actual, dollar variance, percent variance, and traffic-light status - User-set on-track and watch thresholds for status logic - Simulated actual-variance percentages so the report works out of the box - 12-month Budget sheet driven by editable revenue, COGS, and opex assumptions - Matching Actuals sheet that mirrors the Budget layout for easy cross-reference - Variance sheet with dollar and percent variance per line and per month ## Budget vs Actuals Tracker: A 12-Month P&L Variance Model This budget vs actuals tracker compares planned figures against recorded actuals for an operating P&L over 12 months. It shows dollar and percent variances per line, flags favorable or unfavorable swings, and rolls up to a year-to-date summary with traffic-light status, helping CFOs and FP&A analysts assess performance against plan. ### Operating Drivers Behind the Budget The model's budget is driven by assumptions for each revenue and cost line. Monthly revenue starts from a base amount and grows at a specified monthly rate, compounded over the 12 periods. - Cost of goods sold is tied to revenue through a percentage, reflecting variable cost behavior. Operating expenses, such as salaries and marketing, are also built from base amounts with their own growth rates. - This structure allows users to adjust drivers and see how the plan changes. The budget acts as the benchmark for all variance analysis, ensuring that comparisons are consistent and grounded in a clear set of operating assumptions. ### From Actuals to Variance Calculation Actuals mirror the budget's line items but are populated with recorded numbers. In the template, actuals are generated by applying a variance multiplier to each budget cell, simulating real-world deviations. - Users can replace these with bookkeeping data. The variance sheet then computes the dollar difference (Actual minus Budget) and the percent difference (Actual divided by Budget minus one), with a safeguard against division by zero. - Sign conventions are consistent: positive variance on revenue is favorable, while positive variance on costs is unfavorable. This systematic approach ensures that every line item is evaluated on the same basis, highlighting where performance diverges from plan. ### Summarizing Performance with YTD and Traffic Lights The summary sheet aggregates year-to-date figures for each line, summing the 12 monthly columns for both budget and actuals. It calculates YTD dollar and percent variances, then assigns a traffic-light status based on user-defined thresholds. - The status logic uses the absolute percent variance, so both large favorable and unfavorable swings are flagged appropriately. EBITDA performance versus plan is also highlighted. - This roll-up provides a quick read on overall financial health, helping users spot areas that need attention without scanning every monthly detail. ### Practical Use and Scope Considerations This tracker is designed for a CFO or FP&A analyst to monitor an operating P&L. It focuses on revenue, COGS, gross profit, opex, and EBITDA, leaving capex and working capital out of scope. - The model includes validation checks to ensure internal consistency, such as actuals equaling budget times the variance multiplier and YTD totals matching monthly sums. Common pitfalls are avoided, like divide-by-zero errors and incorrect compounding. - Users can stress-test reporting by keeping simulated variances or input real data, making it a flexible tool for variance analysis. ## Built for monthly close When the books close, the only number that matters is plan vs actual. This template lays them side-by-side and surfaces the lines that need explanation. ## Designed for FP&A handoff Every figure ties back to a clear source cell. The Variance and Summary sheets re-aggregate from the Budget and Actuals sheets, so dropping in real bookkeeping data updates everything. ## Audit-friendly mechanics Every input is a named range, every formula is one or two operations, and the workbook passes static-value, self-reference, and dead-assumption scans. ## Built for monthly close When the books close, the only number that matters is plan vs actual. This template lays them side-by-side and surfaces the lines that need explanation. ## Designed for FP&A handoff Every figure ties back to a clear source cell. The Variance and Summary sheets re-aggregate from the Budget and Actuals sheets, so dropping in real bookkeeping data updates everything. ## Audit-friendly mechanics Every input is a named range, every formula is one or two operations, and the workbook passes static-value, self-reference, and dead-assumption scans. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: budget bases, growth, simulated actual variance, traffic-light thresholds. - Month-1 base for each revenue and cost line - Monthly growth rate for revenue and opex - COGS percentages by stream - Simulated actual-variance percentages per line - On-track and watch thresholds in absolute percent ### Budget 12-month plan from compounded base values per line. - Three revenue streams: Product, Service, Other - COGS by stream and gross profit - Five opex lines: Salaries, Marketing, Rent, Utilities, Other - Total opex and EBITDA ### Actuals 12-month recorded numbers; replace with bookkeeping data in live use. - Same row layout as Budget for one-to-one comparison - Each line = Budget × (1 + actual variance) - Subtotals and EBITDA reaggregate from the line items ### Variance Per-line, per-month dollar variance and percent variance. - Dollar variance block: Actual − Budget - Percent variance block: Actual / Budget − 1, with divide-by-zero guard - Same row order as Budget and Actuals ### Summary YTD aggregates and traffic-light status across all 15 lines. - YTD Budget, YTD Actual, YTD $ variance, YTD % variance - Status: On track / Watch / Off track - Threshold logic uses absolute percent variance ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: budget bases, growth, simulated actual variance, traffic-light thresholds. - Month-1 base for each revenue and cost line - Monthly growth rate for revenue and opex - COGS percentages by stream - Simulated actual-variance percentages per line - On-track and watch thresholds in absolute percent ### Budget 12-month plan from compounded base values per line. - Three revenue streams: Product, Service, Other - COGS by stream and gross profit - Five opex lines: Salaries, Marketing, Rent, Utilities, Other - Total opex and EBITDA ### Actuals 12-month recorded numbers; replace with bookkeeping data in live use. - Same row layout as Budget for one-to-one comparison - Each line = Budget × (1 + actual variance) - Subtotals and EBITDA reaggregate from the line items ### Variance Per-line, per-month dollar variance and percent variance. - Dollar variance block: Actual − Budget - Percent variance block: Actual / Budget − 1, with divide-by-zero guard - Same row order as Budget and Actuals ### Summary YTD aggregates and traffic-light status across all 15 lines. - YTD Budget, YTD Actual, YTD $ variance, YTD % variance - Status: On track / Watch / Off track - Threshold logic uses absolute percent variance ## Features - **Side-by-side P&L variance:** Plan and actual sit on parallel sheets with the same row layout, so every line in the Variance and Summary sheets ties back to a clear source cell. - **Traffic-light status:** YTD summary tags each line On track, Watch, or Off track based on user-set absolute-percent thresholds. - **Drop-in actuals:** Replace the formula-driven Actuals cells with bookkeeping data and the Variance and Summary sheets recalculate immediately. ## Use cases - **Monthly close review:** Pull the close numbers into Actuals, eyeball the variance traffic lights, and walk into the close meeting with the off-track lines already isolated. - **Re-forecasting trigger:** Use YTD percent variance against the watch threshold to decide whether to roll forward the original plan or open a re-forecast. - **Operating reviews:** Hand the Summary sheet to the CEO or board as a single-page operating snapshot anchored in plan vs actual. ## Frequently asked questions ### What is a budget vs actuals tracker? A budget vs actuals tracker is a report that compares planned monthly figures against recorded results across each P&L line, then quantifies the gap in dollars and percent so finance teams can flag problem areas during the monthly close. ### How do I plug in my own actuals? Replace the formulas on the Actuals sheet with the recorded values from your bookkeeping. Keep the same row layout and the Variance and Summary sheets recalculate automatically. ### What variance threshold should I use? Most stable businesses run a 5% on-track / 10% watch threshold. Growth-stage companies often loosen revenue thresholds to 10% / 15% while keeping opex tight at 3% / 5%. ### How does favorable vs unfavorable work? Variance dollar is reported as Actual minus Budget without sign flips. Positive on a revenue line is favorable; positive on a cost line is unfavorable. The Summary status uses absolute percent variance, so the threshold logic is line-agnostic. ### Can I extend it beyond 12 months? Yes. The builder is parameterised by NUM_PERIODS - bump it and rerun, then update the YTD SUM ranges on the Summary sheet to cover the longer horizon. ## Related templates - [3 Statement Model](https://finamodel.com/templates/3-statement-model) - [Cashflow Model](https://finamodel.com/templates/cashflow-model) - [Startup Financial Model](https://finamodel.com/templates/startup-financial-model)