Creating Dynamic Projections

Static spreadsheets with hard-coded numbers are brittle and useless. The moment an assumption changes, you're rebuilding half the sheet. A well-designed projection model is dynamic: change the revenue growth rate in one cell, and gross profit, operating income, free cash flow, and intrinsic value recalculate instantly. This article covers the techniques for building flexible, responsive projection models that scale to 5-year, 10-year, or perpetual forecasts without manual recalculation.

Quick definition: Dynamic projections are forecast models where all line items (revenue, expenses, cash flows) are calculated using formulas that reference input assumptions, so that changing assumptions automatically updates the entire forecast.

Key takeaways

• Use relative growth rates (e.g., revenue growth = 10% per year) rather than absolute numbers for flexibility. Formula: Year 2 Revenue = Year 1 Revenue × (1 + growth rate).
• Build the projection in two stages: first, project top-level metrics (revenue, EBITDA) using growth rates; then, build supporting detail (balance sheet, working capital, capital expenditures) that feeds into cash flow.
• Use conditional logic and toggles (e.g., "Is this a mature company?") to allow different projection structures without duplicating formulas.
• Always project more years than you explicitly need for the valuation. If you're discounting 5 years + terminal value, project 7–10 years so you can see the trajectory into stability.
• Test your projections by changing assumptions and verifying that the output moves in the expected direction. A model where halving growth doesn't lower valuation has a formula error.

The Two-Stage Projection Build

Professional valuation models separate forecasting into two stages:

Stage 1: Revenue and operating metrics. Start with historical revenue, apply growth assumptions, and project revenue forward. Then apply margin rates (gross margin, operating margin) to derive operating profits. This stage is high-level and focuses on the company's trajectory.

Stage 2: Balance sheet and cash conversion. From operating profits, subtract taxes and add back non-cash charges (depreciation, amortization). Then account for working capital changes and capital expenditures to derive free cash flow. This stage translates profits into cash.

The two-stage approach is clearer than trying to build everything at once. It also mirrors how management typically communicates forecasts: "We expect 15% revenue growth and 300 basis points of margin expansion," not "Free cash flow will be $47.3M in Year 3."

Building Revenue Projections

Start with historical revenue (imported from financial data, as covered in the prior article). Then set a revenue growth rate in the inputs section.

For simplicity, use a constant growth rate:

• Year 1 Revenue = Historical Revenue × (1 + growth_rate)
• Year 2 Revenue = Year 1 Revenue × (1 + growth_rate)
• Year 3 Revenue = Year 2 Revenue × (1 + growth_rate)
• And so on.

For realism, use declining growth:

• Year 1 growth = 20% (from inputs)
• Year 2 growth = 18%
• Year 3 growth = 15%
• Year 4 growth = 12%
• Year 5+ growth = 5% (terminal growth)

Implement declining growth with a decay factor or by specifying each year's rate. In Excel or Sheets:

Year 1 Revenue = historical_revenue * (1 + inp_year1_growth)
Year 2 Revenue = Year1_Revenue * (1 + inp_year2_growth)
(etc.)

Or, use a formula that declines each year:

decay_rate = (terminal_growth - initial_growth) / forecast_years
Year N Revenue = Year(N-1)_Revenue * (1 + (initial_growth - decay_rate * N))

The declining approach is more realistic: most companies don't maintain 25% growth forever, but they don't drop instantly to 3% either.

Margin and Operating Profit Projections

Once revenue is projected, apply margin rates to derive gross profit, operating income, and EBITDA. Use named ranges:

Gross Profit = Revenue × inp_gross_margin
Operating Income = Revenue × inp_operating_margin (or subtract SG&A separately)
EBITDA = Operating Income + inp_depreciation_and_amortization_rate × Revenue

Key decision: Do you project margins as percentages or dollar amounts?

For most businesses, margins as percentages of revenue are more realistic. A grocery store with 30% COGS and 15% operating expenses will maintain those percentages even if sales triple. Use percentage-based margins.

Exception: Capital-intensive businesses. A telecom or utility might have fixed costs (network maintenance) that don't scale perfectly with revenue. In such cases, break down costs into fixed and variable components:

COGS = (variable_cogs_rate × Revenue) + fixed_cogs
Operating Expense = (variable_opex_rate × Revenue) + fixed_opex

Taxes and After-Tax Income

Project income taxes as a rate applied to earnings before taxes (EBT). Account for tax loss carry-forwards if the company is unprofitable.

Earnings Before Tax = Operating Income - Interest Expense
Taxes = EBT × inp_tax_rate (but min 0 if EBT is negative)
Net Income = EBT - Taxes

Important: Tax rate varies with profitability and jurisdiction. Use the company's statutory rate as the starting point (federal + state + local), then adjust if the company has permanent differences (R&D credits, foreign income, etc.). Check the 10-K for the effective tax rate and use that as your baseline.

Working Capital and Cash Conversion

This stage converts net income into free cash flow by accounting for changes in accounts receivable, inventory, accounts payable, and other current liabilities.

Working Capital Formula:

Working Capital = Accounts Receivable + Inventory - Accounts Payable
Change in WC = Current Year WC - Prior Year WC
Operating Cash Flow = Net Income + Depreciation & Amortization - Change in WC

To project working capital, use Days metrics:

• Days Sales Outstanding (DSO) = (Accounts Receivable / Revenue) × 365
• Days Inventory Outstanding (DIO) = (Inventory / COGS) × 365
• Days Payable Outstanding (DPO) = (Accounts Payable / COGS) × 365

Project each as a percentage of relevant revenue or cost items. For example, if historical DSO is 45 days, assume DSO stays at 45 days in the projection:

Projected AR = (DSO_assumption / 365) × Projected Revenue
Change in AR = Current Year AR - Prior Year AR

If working capital changes are negative (more payables than receivables), they add to cash flow. If positive, they consume cash.

Capital Expenditures

CapEx is the money spent on property, plant, and equipment. Project it as a percentage of revenue:

CapEx = Revenue × inp_capex_as_pct_of_revenue

Alternatively, project it as depreciation plus a maintenance buffer:

CapEx = Depreciation & Amortization + Growth CapEx

Where growth CapEx funds the expansion needed to support revenue growth.

For mature companies with stable revenue, CapEx ≈ Depreciation (maintenance mode). For high-growth companies, CapEx > Depreciation (reinvestment in growth).

Free Cash Flow Formula

With all pieces in place, calculate free cash flow:

Free Cash Flow = Operating Cash Flow - CapEx
               = (Net Income + D&A - Change in WC) - CapEx

Or:

Free Cash Flow = EBIT × (1 - Tax Rate) + D&A - Change in WC - CapEx

Both formulas are equivalent; use whichever structure aligns with your inputs. The key is that FCF represents cash available to debt holders and equity holders after the company reinvests in operations.

Building the Projection Table

Organize projections in a clear matrix:

Item	Year 1	Year 2	Year 3	Year 4	Year 5
Revenue	1,000	1,150	1,322	1,521	1,750
Growth %	15%	15%	15%	15%	15%
Gross Margin %	40%	40%	40%	40%	40%
Gross Profit	400	460	529	608	700
Operating Margin %	15%	15%	15%	15%	15%
Operating Income	150	173	198	228	263
Taxes (30%)	45	52	59	68	79
NOPAT	105	121	139	160	184
D&A	50	58	66	76	87
CapEx	40	46	53	60	69
Change in WC	5	10	12	14	16
Free Cash Flow	110	123	140	162	186

Each cell is a formula referencing inputs (growth rate, margins) and prior-year values. Change a growth rate, and the entire table recalculates.

Handling Declining or Distressed Companies

For companies in decline (shrinking revenue) or turnarounds (improving margins over time), projections are trickier but follow the same structure.

Declining company:

Year 1 Revenue = Historical Revenue × (1 - inp_decline_rate)
Year 2 Revenue = Year 1 Revenue × (1 - inp_decline_rate)
(etc., with negative growth rates)

Manage decline explicitly. Don't project it to drop 60% in Year 1 and then stabilize at Year 2—that's unrealistic. Use a gradual decline, or model specific drivers (market share loss, competitive pressure) that you can defend.

Turnaround company:

Year 1 Operating Margin = Historical Margin
Year 2 Operating Margin = Year 1 Margin + inp_margin_improvement
Year 3 Operating Margin = Year 2 Margin + inp_margin_improvement
(until reaching target margin)

Explicitly model when improvements occur and at what pace. If you assume a company improves from 5% margin to 15% margin, be clear: is it a 2-year turnaround (5% improvement per year) or 5-year (2% per year)?

Sensitivity and Scenario Projections

Once the base case projection is built, copy it to separate sheets for scenarios:

• Base Case: Central assumptions (10% growth, 40% gross margin, 5% terminal growth).
• Bull Case: Optimistic assumptions (15% growth, 42% margin, 5% terminal growth).
• Bear Case: Pessimistic assumptions (5% growth, 38% margin, 5% terminal growth).

Keep the structure identical across all three. Use the same named ranges in each sheet so a summary table can pull outputs from all scenarios at once.

Flowchart

Real-world examples

Example 1: SaaS company valuation. Historical revenue is $50M growing at 25% annually. Assumptions: 25% growth Year 1–2, then 20% Year 3, 15% Year 4, 10% Year 5, 5% terminal. Gross margin 85% (high, as most costs are COGS/cloud hosting). Operating margin improves from 5% to 25% by Year 5 (scaling overhead). Projected Year 5 revenue is $146M; Year 5 free cash flow is $32M. Valuation hinges on whether the company reaches 25% operating margin by Year 5 (high leverage to that assumption).

Example 2: Retailer valuation. Historical revenue $500M with 5% comparable-store sales growth. Assumptions: 4% growth Year 1–5 (maturing). Gross margin 35%, operating margin 8% (rent, labor). Working capital is low (most sales are cash). CapEx is $20M annually for store refreshes. Projected Year 5 revenue is $608M; free cash flow is $32M (lower than SaaS due to higher CapEx). Valuation is less sensitive to margin expansion and more to comparable-store sales assumptions.

Example 3: Biotech company valuation. Company pre-revenue; only CapEx and burn rate matter. Assumptions: R&D spend $100M annually, working capital negative (customer advances). Projection is mostly cash burn until drug launch in Year 4, then ramp to $200M revenue by Year 5. Valuation is entirely driven by launch timing, peak sales, and gross margins post-launch—small changes in these assumptions swing valuation by 50%+.

Common mistakes

Projecting margins to stay constant forever. Margins typically either improve (as company scales) or compress (as competition increases). A realistic projection shows trajectory. If margins are flat for 10+ years, reconsider.

Forgetting working capital increases. Growing companies need more receivables, inventory, and payables. If you project $1B revenue growth but ignore working capital, you'll overstate free cash flow by $50–100M.

Using inconsistent growth assumptions. If you project revenue to grow 20% but use a terminal value growth of 15%, reconsider. Is the transition explicit (declining growth each year), or are you jumping inconsistently?

Projecting too few years. Projecting only 2–3 years and jumping to terminal value is risky. Project at least 5 years so you see the trajectory and can verify reasonableness. For slower-growing businesses, 7–10 years is better.

Not testing sensitivity. Build your projections, then change one assumption (growth down 2%, margins down 100 bps) and verify the impact. If valuation doesn't budge, there's a formula error.

FAQ

Q: How many years should I project? A: Minimum 5 years; 7–10 is more realistic for mature companies. The longer the explicit projection, the less weight the terminal value carries, and the more grounded the model is in reality.

Q: Should I use a constant growth rate or declining growth? A: Declining is more realistic. Most companies can't maintain 25% growth indefinitely. Use declining growth unless you have a specific reason (e.g., a start-up with documented market growth and early adoption phase).

Q: What if historical margins are noisy due to one-time items? A: Adjust historical data to remove the one-time items, then use the adjusted margins as your baseline. This gives you "normal" margins to project from.

Q: Do I project revenue for each business segment separately or roll up? A: Separate segments if they have different growth rates or margins. Roll up if they're similar. Separate detail provides auditability and lets you change assumptions by segment (e.g., "Core business grows 5%, New product line grows 30%").

Q: How do I project for a company with multiple product lines with different growth rates? A: Segment the revenue. Project each segment's revenue growth separately, then sum to get total revenue. Apply segment-specific margins to each segment, then roll up to company-level operating income. This is more complex but reveals where value comes from.

Q: What if the company is currently unprofitable? A: Project a path to profitability. When does the company reach breakeven? Is it in Year 1, Year 3, or Year 5? Once profitable, apply standard margin assumptions. Your valuation hinges on whether breakeven is achievable and when.

Related concepts

• Chapter 11: Free Cash Flow — The output of your projections; understand what drives it.
• Chapter 7: Profitability and Margins — The margin rates you're projecting.
• Chapter 12: Terminal Value — Where your projections converge.
• Chapter 13: Discount Rate and WACC — Used to discount your projected cash flows.

Summary

Dynamic projections are the engine of your valuation model. Build them in two stages: revenue and operating metrics, then balance sheet and cash conversion. Use formulas and named ranges so that changing an assumption ripples through the entire forecast. Project declining growth to realism, and always project more years than you need for the valuation. Test your projections by changing assumptions and verifying the model responds correctly. A dynamic model lets you ask "what if" questions efficiently and gives you confidence that your valuation reflects your assumptions.

Next

Proceed to Error-Checking Your Model.