How to Forecast a Balance Sheet
By Newtown Analytics · · 7 min read
For finance, fp&a & accounting managers
A balance sheet forecast is not a guess at each line; you roll each one forward from its opening balance using the driver that changes it, and let cash fall out of the cash flow statement. Receivables follow sales and collection days, inventory follows cost of sales and days of stock, payables follow purchases and payment terms, fixed assets follow capital spending and depreciation, debt follows borrowing and repayment, and equity follows profit and dividends. This guide shows the method with a full worked example whose balance sheet balances, tests what happens when customers pay later, adds ratio and covenant checks and lists the lines people forget. It is part of budgeting and forecasting and one piece of the three statement financial model, alongside forecasting the income statement, model structure and the model audit checklist.
The principle: roll-forward
Every balance sheet line obeys the same identity:
Closing balance = Opening balance + Additions - Reductions| Line | Additions | Reductions |
|---|---|---|
| Fixed assets | Capital expenditure | Depreciation, disposals |
| Debt | Borrowings | Repayments |
| Equity (retained earnings) | Net income | Dividends |
| Receivables | Sales on credit | Cash collected |
| Inventory | Purchases | Cost of goods sold |
| Payables | Purchases on credit | Payments to suppliers |
Where a roll-forward is hard to see (working capital), use days or percentages tied to the income statement.
Working capital: the days method
| Item | Formula | Driver |
|---|---|---|
| Receivables | Revenue × DSO / days in year | Days sales outstanding |
| Inventory | Cost of goods sold × DIO / days in year | Days inventory outstanding |
| Payables | Cost of goods sold × DPO / days in year | Days payables outstanding |
This example uses a 360-day year so the arithmetic is round; use 365 if that is your convention, and use it consistently.
A worked example
A hypothetical company (thousands of dollars) had Year 0 revenue of 10,800 and cost of goods sold of 6,480. For Year 1 it forecasts revenue of 12,000, cost of goods sold of 7,200 (60 percent), net income of 700, depreciation of 450, capital expenditure of 600, debt repayment of 200 and dividends of 200. Collection days (DSO) are 45, inventory days (DIO) 60 and payment days (DPO) 40, the same as last year.
Working capital balances
| Item | Year 0 | Year 1 | Change |
|---|---|---|---|
| Receivables | 10,800 × 45/360 = 1,350 | 12,000 × 45/360 = 1,500 | +150 |
| Inventory | 6,480 × 60/360 = 1,080 | 7,200 × 60/360 = 1,200 | +120 |
| Payables | 6,480 × 40/360 = 720 | 7,200 × 40/360 = 800 | +80 |
| Net working capital | 1,710 | 1,900 | +190 |
Net working capital rises by 190 even though days are unchanged, because the business grows by 11 percent. Growth consumes cash.
Opening balance sheet (Year 0)
| Assets | Liabilities and equity | ||
|---|---|---|---|
| Cash | 800 | Payables | 720 |
| Receivables | 1,350 | Debt | 2,000 |
| Inventory | 1,080 | Equity | 3,510 |
| Fixed assets, net | 3,000 | ||
| Total | 6,230 | Total | 6,230 |
Cash flow for Year 1
| Line | Amount |
|---|---|
| Net income | 700 |
| Depreciation | 450 |
| Increase in receivables | (150) |
| Increase in inventory | (120) |
| Increase in payables | 80 |
| Cash from operations | 960 |
| Capital expenditure | (600) |
| Debt repayment | (200) |
| Dividends | (200) |
| Net change in cash | (40) |
| Closing cash (800 - 40) | 760 |
Closing balance sheet (Year 1)
| Assets | Liabilities and equity | ||
|---|---|---|---|
| Cash | 760 | Payables | 800 |
| Receivables | 1,500 | Debt (2,000 - 200) | 1,800 |
| Inventory | 1,200 | Equity (3,510 + 700 - 200) | 4,010 |
| Fixed assets, net (3,000 + 600 - 450) | 3,150 | ||
| Total | 6,610 | Total | 6,610 |
Assets: 760 + 1,500 + 1,200 + 3,150 = 6,610. Liabilities and equity: 800 + 1,800 + 4,010 = 6,610. The balance sheet balances, and cash was never a plug; it came from the cash flow statement.
Test: customers pay five days later
Suppose DSO rises from 45 to 50 days.
- Receivables = 12,000 × 50/360 = 1,666.7, which is 166.7 higher than the base case.
- Cash from operations falls by 166.7, and closing cash falls to 760 - 166.7 = 593.3.
- Net income is unchanged. Total assets are unchanged at 6,610, because 166.7 of cash became receivables.
Five extra days of collection cost 167, or 22 percent of the year-end cash balance, with no change in profit. The reverse is also true: cutting inventory days from 60 to 50 reduces inventory to 7,200 × 50/360 = 1,000, which releases 200 of cash. See cash flow management for the levers behind each of these days.
Annual, quarterly or monthly
The same method works at any frequency, but the answer differs. A year-end balance sheet can look comfortable while the middle of the year is tight, especially in a seasonal business that builds inventory ahead of its peak and collects afterward. If cash matters (it usually does), forecast the balance sheet monthly, or at least at the seasonal low point, and set the days for each month from last year's pattern. A good test is to compare your forecast balances for each month last year with the actual balances: if the forecast method would have missed the seasonal peak in inventory by 30 percent, it will miss it again this year. See adjusting forecasts for seasonality.
The other lines
| Line | How to forecast it |
|---|---|
| Prepaid expenses | % of related expense, or schedule by contract |
| Accrued expenses | Days or % of the related expense (wages, interest, utilities) |
| Taxes payable | Tax expense less cash tax paid, by payment calendar |
| Deferred revenue | Billings in advance less revenue recognized |
| Intangibles and goodwill | Roll-forward with amortization |
| Right-of-use assets and lease liabilities | Per lease schedule; follow accounting policy |
| Provisions | Policy and history |
| Share capital | Planned issuance or buyback |
| Minimum cash and revolver | Draw when cash would fall below the minimum |
Omitting accruals and prepaid items is a common reason forecast cash differs from actual cash.
Ratio and covenant checks
Forecast the ratios that lenders and management watch, using the closing balance sheet from the example.
| Ratio | Calculation | Year 1 |
|---|---|---|
| Current ratio | Current assets / current liabilities = (760 + 1,500 + 1,200) / 800 | 4.33 |
| Quick ratio | (Cash + receivables) / current liabilities = (760 + 1,500) / 800 | 2.83 |
| Debt to equity | 1,800 / 4,010 | 0.45 |
| Days working capital | Net working capital / revenue × 360 = 1,900/12,000 × 360 | 57 days |
(The example treats all debt as long-term for simplicity, so current liabilities are payables only. In practice, include the current portion of debt.) Compare the ratios with covenant limits in every period and under the downside case. See scenario planning for finance teams.
Link to operations
Working capital days come from operating decisions: credit terms, collection practice, purchasing policy, safety stock and supplier terms. A balance sheet forecast made without talking to purchasing and sales is a guess. Where a company holds inventory, link the forecast to the supply plan and demand plan. See demand planning and inventory optimization.
Link to profitability
Returns on capital depend on both profit and the balance sheet needed to produce it. A product with a high margin but long inventory days may earn less on capital than a lower-margin product that turns quickly. See unit economics.
Checks
- Does the balance sheet balance in every period without a plug?
- Does closing cash agree with the cash flow statement?
- Does each roll-forward use the right opening balance?
- Are days and percentages consistent with history?
- Are accruals, prepaid items and tax payments included?
- Are covenants tested in the base and downside cases?
Common mistakes
- Making cash the balancing item.
- Holding working capital flat while revenue grows.
- Using revenue instead of cost of goods sold for inventory and payables.
- Inconsistent day counts.
- Forgetting depreciation in the fixed asset roll-forward.
- Ignoring the current portion of debt.
- Not testing a slower-collections case.
FAQs
How do you forecast a balance sheet?
Roll forward each line from its opening balance: fixed assets with capex and depreciation, debt with draws and repayments, equity with profit and dividends, and working capital with days or percentages. Cash comes from the cash flow statement.
What are DSO, DIO and DPO?
Days sales outstanding, days inventory outstanding and days payables outstanding: the average days to collect, hold stock and pay suppliers.
Why should cash not be a plug?
Because it hides errors. If the balance sheet balances only because cash was forced, something in the logic is wrong.
How does growth affect the balance sheet?
Growth increases receivables and inventory and consumes cash even when profit is positive.
Which ratios should I forecast?
Those in your loan covenants, plus liquidity and leverage measures management monitors.
Keep reading
This article is for general information. It is not accounting, tax, legal or investment advice, and examples use illustrative numbers rather than client data. Confirm decisions that carry financial or legal consequences with a qualified professional.