📌 Stage 6 · Steering the Plan · Plan vs Actual · monthly close routine · 30–40 min
Keeping the forecast honest all year
Learning goals
- Run the monthly close routine against the Plan vs Actual sheet
- Apply the standing tie-out checks across the workbook
- Roll the plan forward when reality drifts from the drivers
- State the workbook's boundaries and handoff obligations
Concepts
The monthly close routine
Once a month, on a fixed day after the bookkeeping close: (1) type the month's actuals into the matching actual column on Plan vs Actual; (2) read the variance column against a threshold you set in advance; (3) write a one-line explanation for every breach; (4) change a driver only when the explanation points to a durable cause; (5) re-check the covenant row. Fifteen minutes a month keeps a forecast alive; a quarterly scramble keeps it fictional.
The standing checks
Four tie-outs catch almost every error this kind of workbook can have:
Monthly P&L O8 (revenue) must equal Revenue Plan O10 — $690,000.00.
Cash Flow O6 must equal Revenue Plan O14 (collections) — $689,156.25.
Cash Roll-Forward N8 minus C5 must equal Cash Flow O19 (net cash flow) — $80,465.15 − $38,000.00 = $42,465.15.
Revenue Plan O13 − O14 must equal N15 − C12 (the receivables tie) — $843.75.
A number that breaks a tie-out means a formula was overwritten or an input was mistyped; fix it before the next close.
Rolling the plan
When actuals drift, the plan is rolled, not rewritten: the same ten sheets re-point to updated drivers. If a retainer leaves, change retainer clients on Assumptions from 3 to 2 — revenue, contractor cost, collections, cash, and the covenant all re-compute together. If clients start paying slower, move the collection curve. Nothing is patched at the formula layer, because patched formulas are how forecasts quietly stop agreeing with themselves.
Boundaries and handoff
This workbook is a single-user planning tool: no multi-user permissions, no approval workflow, no audit log, no automatic backups — keep your own dated copies. It is not accounting software: the bookkeeping system remains the system of record, and the actuals you type each month should come from it. Amounts exclude sales tax — Texas generally does not tax purely graphic design services, but tangible deliverables and combined contracts can be, so confirm your state's treatment with your state Department of Revenue or your CPA. Payroll taxes use a blended planning rate, depreciation is simplified straight-line, and the loan uses the lender's quoted payment. The file intentionally contains no macros or VBA. Hand your CPA the actuals and the variance notes; they will want both.
Practice
Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. This lesson runs the whole review in the workbook: no cell to retype, only numbers to confirm.
- Open Plan vs Actual and run the Q1 review — The exercise opens on Plan vs Actual. Read the Q1 column (N) down all seven lines: revenue +$1,200.00, contractor cost +$1,130.00, salaries $0.00, operating expenses −$1,100.00, net profit +$995.00, collections −$1,000.00, closing cash −$1,039.59.
- Run the four standing checks — Confirm Monthly P&L O8 = Revenue Plan O10 = $690,000.00; Cash Flow O6 = Revenue Plan O14 = $689,156.25; Cash Roll-Forward N8 − C5 = Cash Flow O19 = $42,465.15; Revenue Plan O13 − O14 = N15 − C12 = $843.75.
- Rehearse a driver change — As a drill, set Assumptions C5 to Worst, note Monthly P&L O27 (−$84,614.03) and the covenant flags, then type Base back in. That is the monthly “is the plan still true?” question, answered in one move.
- Walk the workbook in tab order — Click all ten sheets once, top to bottom, and confirm each one still tells its part of the story: Read Me, Assumptions, Staff & Payroll, Revenue Plan, Operating Expenses, CapEx & Loan, Monthly P&L, Cash Flow, Cash Roll-Forward, Plan vs Actual.
- Take the workbook offline — Use the download card to save budget-cash-flow-forecast-case.xlsx (the finished case) and budget-cash-flow-forecast-template.xlsx (inputs cleared for your own studio). Both are macro-free .xlsx; open them in Excel, Google Sheets, or LibreOffice Calc.
Online exercise
The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.
Practice files: budget-cash-flow-forecast-case.xlsx, budget-cash-flow-forecast-template.xlsx (see the attachments section at the bottom of this page).
Checklist
Work through each item; when every box passes, this lesson is done:
- ☐ Q1 review ties out: revenue +$1,200.00, collections −$1,000.00, net profit +$995.00 against the Base plan
- ☐ All four standing checks pass, including the $42,465.15 cash identity and the $843.75 receivables tie
- ☐ FY figures verified: revenue $690,000.00, net profit $56,865.97, Dec 31 closing cash $80,465.15, Dec 31 receivables $47,868.75
- ☐ Downloaded both xlsx files and can describe the monthly close routine