Here's how to build projections that close without manual plugs.
The Architecture: Schedules vs. Assumption Drivers
Every line item on a projected balance sheet comes from one of two places: a supporting schedule or a ratio/assumption applied to a P&L driver. Confusing these is where most models go sideways.
Schedule-driven items carry their own mechanics. Debt, equity, PP&E, and deferred revenue all need a roll-forward: opening balance + additions - reductions = closing balance. The closing balance feeds the balance sheet; the additions and reductions feed the cash flow statement.
Assumption-driven items are simpler. Accounts receivable, inventory, and accounts payable are almost always projected as days metrics applied to a P&L line. AR = (DSO / 365) × revenue. No separate schedule needed - just a consistent assumption and a clean cross-tab reference.
If you're building across multiple tabs, the discipline matters more than the mechanics. Your balance sheet tab shouldn't contain any assumptions. It should only contain references:
='Working Capital'!C18 // AR closing balance, period-end
='Debt Schedule'!F12 // Long-term debt, net of current portion
='PP&E Schedule'!D24 // Net PP&E after D&A
Working Capital: The Core Three Lines in Any Balance Sheet Projection
Accounts receivable, inventory, and accounts payable drive roughly 80% of the working capital variance in most operating models. Get these 3 right and the rest of the current assets/liabilities section is rounding.
Accounts receivable. Project with DSO. If your company collects in 47 days and revenue is $18.4M annualized, AR sits at $2.4M at period-end. The formula across tabs:
=ROUND(
(Assumptions!$C$12/365) * 'P&L'!C8,
0
)
Where Assumptions!$C$12 is your DSO input and 'P&L'!C8 is the revenue line for that period. Lock the assumption reference, keep the P&L reference relative so it moves across columns.
The cash flow linkage. The change in AR flows into operating cash flow as a source (if AR falls) or use (if AR grows). This is where most models break. A lot of analysts hard-code the cash flow impact rather than calculating it as the delta:
='Balance Sheet'!C14 - 'Balance Sheet'!B14 // AR change: prior period - current period
A 5-day DSO compression on $18.4M revenue releases roughly $252K in cash. That number should appear automatically in your cash from operations section - not as a manual entry.
Accounts payable. Same mechanics, inverted. Project with DPO against COGS. At 61.4% COGS margin on $18.4M revenue, COGS is approximately $11.3M. At 45 DPO, AP sits around $1.4M. If your payment terms stretch to 55 days, that's a $310K cash benefit that flows through operating cash flow automatically - but only if your AP delta formula is wired correctly.
Inventory. Days inventory outstanding (DIO) against COGS. For product companies this is the most volatile line and the one most worth stress-testing. A +10 day swing in DIO on $11.3M COGS costs you about $310K in cash. Model it separately and let it flow.
The Single Most Common Way Balance Sheet Projections Break
The balance sheet doesn't tie. The gap is always somewhere between $1 and $500K, and it doesn't move when you change assumptions. That's the signature of a wiring error, not a formula error.
The usual culprits:
Cash isn't the plug - it's calculated. Cash should equal: beginning cash + net change from operations + net change from investing + net change from financing. If you have a revolver and you're using it as the plug, that's fine, but the mechanism has to be explicit. See the table below.
Retained earnings isn't linked to net income. Retained earnings closing balance = opening balance + net income - dividends. If your equity section has a static opening balance with net income bolted on as a separate line but not rolling forward, you'll accumulate errors across periods.
The deferred tax line is orphaned. Most simplified models ignore deferred taxes or hard-code them. When you eventually add them, the balance sheet gaps by exactly your deferred tax provision. Build a placeholder for it from the start, even if it's zero.
| Approach | How it works | When to use it |
|---|---|---|
| Cash plug | Cash = Assets - Liabilities - Equity; cash is the residual | Simple operating models, no revolver |
| Revolver plug | Revolver draws/repays to maintain minimum cash balance | LBOs, leveraged operating models |
| Manual check | =IF(ABS(Assets-L&E)>1,"CHECK","OK") in a visible cell | Always, regardless of which plug you use |
The check formula should sit at the top of your balance sheet tab, not buried at the bottom. If you have to scroll to find it, you won't catch the break until your CFO does.
PP&E and Debt: The Two Schedules You Can't Skip
PP&E roll-forward. The basic structure:
Opening PP&E (gross)
+ Capex (from cash flow / assumptions)
- Disposals
= Closing PP&E (gross)
- Accumulated depreciation
= Net PP&E
Depreciation flows out of this schedule into two places: the P&L (as a COGS or OpEx item) and the cash flow statement (as an add-back to net income). If you're not pulling both from the same source, your D&A will eventually drift.
Debt schedule. For a leveraged model, you need draws, repayments, and ending balance for every debt tranche - separately. Blending a term loan and revolving credit into one line is fine for reporting; it's not fine for modeling. Mandatory amortization, cash sweeps, and PIK interest all have different cash flow treatment.
At a 14.2x EBITDA entry multiple, a $17M enterprise value swing from getting the debt schedule wrong isn't hypothetical - it's one rounding error per quarter, compounded across a 5-year hold. Keep the schedules clean.
Where AI Helps (and Where It Doesn't)
The mechanical parts of balance sheet projection - building roll-forwards, writing cross-tab references, checking the tie-out - are exactly what AI tools handle well. ModelMonkey can read your P&L and assumptions tabs, write the delta formulas for working capital, and flag when your check cell shows a break. It won't build the business logic for you, but it will save 45 minutes of formula-writing per model build.
What AI can't do is tell you whether your DSO assumption is realistic for your customer base, or whether your DPO reflects actual vendor terms. Those judgments still live with the analyst.
The Check Infrastructure That Prevents Late-Night Fire Drills
A model that ties today will break the next time someone edits the assumptions tab without updating the cash flow wiring. Build the checks in from the start.
Balance check. =Assets - (Liabilities + Equity) - should always be zero. Put this in a named range called BS_CHECK and reference it from a summary dashboard.
Cash flow to cash balance check. Beginning cash + net cash flow = ending cash on the balance sheet. A separate check:
=ABS('Cash Flow'!C45 - 'Balance Sheet'!C5) > 0.5
If this returns TRUE, something is double-counted or omitted in the cash flow statement.
Retained earnings bridge. Opening RE + net income - dividends = closing RE. Another one-line check that catches a surprisingly large class of errors.
As of May 2026, I haven't seen a single three-statement model in the wild that didn't benefit from at least one of these checks being formalized. Most models have an informal "it looks right" verification. That's not a check.