Pro Forma Three-Statement Projection: Building a Model from Scratch

A pro forma three-statement projection builds in this order: (1) Project the INCOME STATEMENT line by line — revenue → COGS → operating expenses → EBITDA → D&A → EBIT → interest → taxes → net income. (2) Project the BALANCE SHEET — driven from the income statement (retained earnings updates from net income), assumption-driven items (PP&E, working capital), and debt schedule. (3) Build the CASH FLOW STATEMENT mechanically — start with net income, add back non-cash items (D&A), adjust for working capital changes, subtract CapEx, add/subtract financing activities. The cash flow statement's ending cash balance flows back to the balance sheet's cash line, completing the loop. The balance sheet must balance every year — Assets = Liabilities + Equity. If it doesn't, find the linking error before continuing.

Revenue is the most important projection — every other line scales from it. Three common methodologies: • GROWTH RATE: project next year's revenue as historicals × (1 + growth rate). Simple but blunt — assumes the past trajectory continues. Best for companies with stable historical growth. • UNIT ECONOMICS: project price × quantity, where each component is modeled separately. Better for companies where price and volume are independent levers (e.g., subscription companies — average revenue per user × subscriber count). • SEGMENT BUILD: project revenue by business segment or product line, then sum. Best for diversified companies where different segments grow at different rates. Worked example: a SaaS company with $50M ARR growing at 30% annually, projected through year 5: Year 1 $50M, Year 2 $65M, Year 3 $84.5M, Year 4 $109.9M, Year 5 $142.8M. With expansion revenue (NRR 110%) and new logos, this might be modeled as Net New ARR = Existing ARR × (NRR − 1) + new logo ARR. COGS, operating expenses, and other variable costs are typically projected as a percentage of revenue, with margin trends informed by historicals.

Most balance sheet items project from operating ratios: • ACCOUNTS RECEIVABLE = Revenue × DSO / 365. If DSO holds at historical 45 days, AR projects from revenue. • INVENTORY = COGS × DIO / 365. If DIO holds at 60 days, inventory projects from COGS. • ACCOUNTS PAYABLE = COGS × DPO / 365. If DPO holds at 30 days, AP projects from COGS. • PP&E = Beginning PP&E + CapEx − Depreciation. CapEx is a separate assumption (often as % of revenue or absolute dollar plan). • OTHER ASSETS / OTHER LIABILITIES = often held flat or project at general inflation rate (3-4%). DEBT and EQUITY are typically projected via separate schedules: • Debt schedule: beginning debt + new borrowings − repayments + interest expense. Often involves a circular reference because interest depends on average debt balance. • Equity: Common Stock (held flat unless equity raises projected) + Retained Earnings (Beginning RE + Net Income − Dividends). Treasury stock and APIC held flat unless explicitly modeled. Worked example: revenue projection of $100M with DSO at 45 days produces AR projection of $100M × 45 / 365 = $12.3M.

The cash flow statement is built mechanically from changes in balance sheet items. Indirect method: Operating Cash Flow: Net Income (from income statement) + Depreciation & Amortization (non-cash, add back) − Increase in AR (cash tied up in receivables) − Increase in Inventory (cash tied up in inventory) + Increase in AP (cash held back from suppliers) + Other working capital changes = Cash from Operations Investing Cash Flow: − CapEx (purchase of PP&E) + Sale of PP&E or investments (if any) − Acquisitions (if planned) = Cash from Investing Financing Cash Flow: + New debt issuance / borrowing − Debt repayment + Equity raises − Dividends paid − Share repurchases = Cash from Financing Net Change in Cash = Operating + Investing + Financing Ending Cash = Beginning Cash + Net Change in Cash This ending cash flows back to the balance sheet cash line. If the balance sheet doesn't balance after this, there's a linking error somewhere — start with whether the cash flow statement and the change in balance sheet cash agree.

Interest expense depends on average debt balance during the year. Debt balance depends on cash flow needs (which need to fund operations). Cash flow depends on net income (which depends on interest expense). This is the classic financial modeling circular reference. Resolutions: • Iterative calculation in Excel — turn on iterative calculations (File → Options → Formulas → Enable iterative calculations). Excel resolves the circular reference by iterating until values converge. Standard but creates risk of model breaking when small changes don't converge. • Average interest method — calculate interest on AVERAGE debt balance, which uses the average of beginning and ending balances and creates a more stable circular ref. • Beginning-balance interest — calculate interest on BEGINNING debt balance only. Simpler, slightly less accurate but breaks the circular reference. Common in academic and quick-build models. • Targeted iteration — model interest as a manually-iterated calculation: project once with a guess, observe ending debt, recalculate interest, repeat 2-3 times until stable. More transparent than Excel's iterative calc. For most pro forma models, beginning-balance interest is acceptable — the slight inaccuracy is far smaller than other assumption uncertainties. For high-leverage companies or where interest is a major P&L line, iterative or average methods are preferred.

Two critical validation checks for every projection year: CHECK 1: Balance sheet balances. Total Assets = Total Liabilities + Equity in every year. If not, there's an error somewhere — common culprits: (a) cash from CFS doesn't reconcile to balance sheet cash change, (b) retained earnings update missed, (c) debt schedule not linked to balance sheet debt, (d) plug item missing. CHECK 2: CFS reconciles. Beginning cash + change in cash from CFS = ending cash on balance sheet. If not, the CFS is incomplete — typically missing financing activity (forgot to deduct dividends, missed equity raise) or working capital change (calculated from wrong period). Best practice: include explicit balance-sheet-check and CFS-check rows in the model that compute the discrepancy. Both should equal zero in every year. If they're non-zero by even $1, find and fix before presenting the model. Three-statement integrity is the foundation of credibility — a model that doesn't balance is worse than no model. FinanceIQ generates pro forma three-statement projections from historical financials with linked checks built in, plus assumption flexibility for revenue methodology, working capital ratios, CapEx schedule, and debt structure. This content is for educational purposes only and does not constitute financial advice.