Balance Sheet Modeling
Build assets, liabilities, and equity schedules that reconcile via retained earnings and cash flow linkages. Master the checks that keep Assets = Liabilities + Equity true across every forecast period.
Analyst Objective
Learn to build a clean, balanced forecast balance sheet by driving working capital, PP&E, and retained earnings from assumptions instead of hard-coded plugs.
What You'll Learn
1Structure & Historical Setup
Start with 3–5 years of historical balance sheets. Standardize line names and groupings so subtotals align year to year. Always keep the identity Assets = Liabilities + Equity.
Core Identities
Balance
Retained Earnings
PP&E Roll-Forward
Setup Goal
Compute historical working-capital days (DSO, DIO, DPO) and average them. These will drive receivables, inventory, and payables in the forecast.
2Retained Earnings Link (NI & Dividends)
Net Income from the Income Statement closes into Equity via Retained Earnings; dividends reduce RE. This is the primary link between IS and BS.
Formula
Worked Example
Best Practices
- Use an explicit Dividends line (assumption as % of Net Income or fixed dollars).
- Share buybacks affect APIC/Treasury Stock, not RE (but reduce shares outstanding).
- Ensure RE ties to the Cash Flow Statement’s financing section via dividends paid.
3Modeling Cash (from CFS)
Ending cash comes from the Cash Flow Statement. In early builds you can proxy cash with a minimum cash assumption if a full CFS isn’t ready.
Formulas
Replace placeholder once the CFS is connected in Lesson 5.
Circularity Note: Interest income depends on average cash, which depends on cash flow. Treat interest income as a simple assumption here; resolve full circularity later.
4Working Capital: AR, Inventory, AP
Model receivables, inventory, and payables using days-based drivers (DSO, DIO, DPO). These affect cash via changes in net working capital.
Days Formulas
Accounts Receivable
Example:
Revenue = $220M, DSO = 45 days
AR = ($220M × 45) / 365 = $27.1M
Change in AR = AR_t − AR_{t-1}
Inventory
Example:
COGS = $132M, DIO = 60 days
Inv = ($132M × 60) / 365 = $21.7M
Change in Inv = Inv_t − Inv_{t-1}
Accounts Payable
Example:
COGS = $132M, DPO = 40 days
AP = ($132M × 40) / 365 = $14.5M
Change in AP = AP_t − AP_{t-1}
Complete Working Capital Example
Year-over-Year Changes:
(Negative impact on cash flow - company is growing faster than it collects)
Working Capital Best Practices
- DSO (Days Sales Outstanding): Lower is better - means faster collection
- DIO (Days Inventory Outstanding): Balance between too high (holding costs) and too low (stockouts)
- DPO (Days Payable Outstanding): Higher is better - delays cash outflow
- Cash Conversion Cycle = DSO + DIO - DPO: Target: minimize this number
Working Capital Shock: AR Increases
AR ↑ 8 → customers take longer to pay, so cash goes down.
Rule of thumb: if more cash is tied up in working capital (ΔNWC > 0), UFCF goes down. If working capital is released (ΔNWC < 0), UFCF goes up.
5PP&E: Capex & Depreciation
Use a simple roll-forward if you don't have a full fixed-asset schedule yet. Anchor Capex as a % of revenue or to growth needs; depreciation should link to the IS.
Core Roll-Forward Formula
Complete Worked Example:
(Note: PP&E declining because depreciation exceeds capex)
Capex Driver Options
Best for maintenance capex
Ties to growth investments
Most realistic for known projects
Depreciation Methods
Quick approximation
Industry benchmarks
Most accurate (advanced)
Three-Statement Linkages
PP&E Modeling Best Practices
- Industry Norms: Compare Capex/Revenue to peer companies (typically 3-8%)
- Growth vs Maintenance: High-growth companies need higher capex ratios
- Asset-Light vs Asset-Heavy: Tech = low, Manufacturing = high PP&E intensity
- Sanity Check: If PP&E growing faster than revenue, question the logic
- Depreciation Shield: Higher D&A reduces taxes but not cash (tax benefit)
6Intangibles & Goodwill
Intangibles amortize; goodwill generally does not (subject to impairment). Keep simple unless an M&A schedule is required.
Formulas
Amortization flows through IS; impairment is a non-cash charge (usually below operating income).
7Accrued Liabilities & Other Current Liabilities
Model as % of revenue or as days-based metrics if disclosure allows. These lines capture payroll taxes, accrued expenses, deferred revenue (if current), etc.
Common Drivers
8Debt & Interest Linkages (summary)
Keep a high-level debt roll-forward for now. Full schedules and circularity resolution arrive later.
Debt Roll-Forward
Interest Expense_t ≈ Avg(Debt) × Rate
Net Debt
9Equity: Share Capital, Buybacks, Dividends
Equity increases with issuances, decreases with buybacks; retained earnings roll-forward captures profitability and dividends.
Equity Roll-Up
10Balance Check & Key Ratios
Add reasonableness checks and liquidity/leverage metrics.
Balance Check
should be 0
Current Ratio
≥ 1.0 for safety
Quick Ratio
excludes inventory
Net Debt
negative means net cash
Debt / EBITDA
watch covenants
Working Capital Days
lower frees cash
Interactive Balance Sheet Demo
Watch how each line item flows through the balance sheet step-by-step, from assets to liabilities to equity, demonstrating the fundamental equation: Assets = Liabilities + Equity.
Step 1: Cash
Starting with cash from the Cash Flow Statement
Pro Tip: When the balance check isn't zero, trace differences through the three roll-forwards: Working Capital, PP&E, and Retained Earnings. The balance sheet must always balance!
9Complete Balance Sheet Example
| Line Item | 2023A | 2024E | 2025E | Formula |
|---|---|---|---|---|
| Cash | 20 | 25 | 28 | from CFS |
| Accounts Receivable | 40 | 44 | 48 | =Rev×DSO/365 |
| Inventory | 35 | 38 | 41 | =COGS×DIO/365 |
| Other Current Assets | 8 | 9 | 10 | assumption |
| PP&E | 100 | 105 | 110 | roll-forward |
| Total Assets | 203 | 221 | 237 | sum |
| Accounts Payable | 30 | 32 | 34 | =COGS×DPO/365 |
| Accrued & Other CL | 12 | 13 | 14 | % of Rev |
| Debt | 60 | 62 | 64 | roll-forward |
| Common Stock & APIC | 21 | 21 | 21 | flat |
| Retained Earnings | 80 | 95 | 106 | =Prev+NI−Div |
| Total Liabilities & Equity | 203 | 221 | 237 | sum |
Key Insight: NWC changes drive cash needs; PP&E and RE roll-forwards are the other two pillars of a balanced forecast.
11Practical Modeling Techniques
Balance Check Cell
Create a dedicated cell that flags any non-zero difference between Assets and Liabilities+Equity.
Driver Transparency
Keep DSO/DIO/DPO and Capex% assumptions on a single Assumptions tab with named ranges.
Roll-Forward Blocks
Group PP&E, Intangibles, and Debt as discrete roll-forward modules for auditability.
Scenario-Friendly
Reference all drivers via inputs so scenarios instantly cascade to BS and CFS.
Reasonableness Bands
Flag if Current Ratio < 1.0, Net Debt/EBITDA > 4.0×, or Working Capital Days swing > ±10 days.
Key Takeaways
BS must balance every period; enforce with a visible check cell
Retained Earnings links Net Income and Dividends into Equity
Working capital days drive AR, Inventory, and AP — major cash drivers
PP&E requires a clean roll-forward; Depreciation flows to IS and add-back to CFS
Keep debt simple now; resolve interest circularity when full schedules are built