Introduction

A financial model that breaks every time you update a revenue assumption is not a model - it is a spreadsheet with numbers in it. A dynamic financial model recalculates from end to end with a single input change. No manual fixing, no broken links, no hunting for the cell that needs updating.

This guide walks you through building exactly that: a three-statement integrated model (P&L, Balance Sheet, Cash Flow) with dynamic formulas and built-in scenario switching. Open Excel and follow along.

If you are new to model structure, start with our financial modeling best practices guide first.

Model Architecture: Three Sheets, One Purpose

Every robust financial model uses the same structure: separate sheets for inputs, calculations, and outputs. This is the difference between a model you can audit in five minutes and one you have to unpick cell by cell.

Sheet 1 - Inputs. One cell per assumption. No formulas, no cross-sheet references. Every input is a hard-coded value: revenue growth rate, COGS percentage, SG&A headcount, interest rate, tax rate. Colour these cells blue (standard convention) so anyone knows these are the only cells that should ever change.

Sheet 2 - Calculations. The logic lives here. Revenue, COGS, gross margin, operating expenses, depreciation, interest, and tax are all computed from the inputs. Nothing here is typed manually.

Sheet 3 - Outputs. A clean presentation layer. The P&L, balance sheet, and cash flow statement each pull from the calculations sheet with simple reference formulas.

For a deeper look, see our complete guide to financial modeling in Excel.

Building the P&L Statement

Start with the income statement. It is the simplest and forms the bridge to the balance sheet and cash flow.

Revenue

Assume your Inputs sheet has a cell named RevGrowth and a prior-year revenue figure. In Excel:

Year 1 Revenue = Prior_Revenue * (1 + RevGrowth)

Name ranges via the Name Manager so formulas read as plain English.

Cost of Goods Sold and Gross Margin

COGS = Revenue * COGS_Pct
Gross Profit = Revenue - COGS

Operating Expenses

Model fixed costs (rent, salaries) as dollar values with an escalation rate. Model variable costs (commissions, delivery) as a percentage of revenue:

Total OPEX = Fixed_OPEX * (1 + Escalation)^(Year - 1) + Revenue * Variable_OPEX_Pct

EBITDA, Interest, Depreciation, and Tax

EBITDA = Gross Profit - Total OPEX
Depreciation = Depreciation_Rate * Fixed_Assets
EBIT = EBITDA - Depreciation
Interest = Interest_Rate * Avg_Debt
EBT = EBIT - Interest
Tax = EBT * Tax_Rate
Net Income = EBT - Tax

Net Income drives the rest of the model. Everything flows from it.

Building the Balance Sheet

The balance sheet must satisfy Assets = Liabilities + Equity. If it does not balance, something is wrong.

Cash and Cash Equivalents

Do not model cash directly. Cash is the plug: opening cash plus net change in cash (from the cash flow statement) equals closing cash. Reference the closing cash balance from the cash flow statement.

Accounts Receivable and Payable

Driven by turnover days. If receivables take 45 days to collect:

AR = Revenue / 365 * AR_Days
AP = COGS / 365 * AP_Days

Fixed Assets, Debt, and Equity

Closing_FA = Opening_FA + Capex - Depreciation
Closing_Equity = Opening_Equity + Net_Income - Dividends

For debt, track opening balance less repayments plus new drawdowns. Keep it simple with a single tranche and fixed repayment schedule.

The Balancing Check

Add a cell with =Assets - Liabilities_And_Equity. It should always return zero. The most common sources of imbalance: a missing link between net income and retained earnings, or a cash flow / cash balance mismatch.

Building the Cash Flow Statement

The cash flow statement reconciles net income to the change in cash across three sections.

Operating Cash Flow

Start with Net Income, add back depreciation, and adjust for working capital changes:

Change in AR = -(Closing_AR - Opening_AR)
Change in AP = Closing_AP - Opening_AP
Operating CF = Net_Income + Depreciation + Change_AR + Change_AP

Investing and Financing Cash Flow

Investing CF = -Capex
Financing CF = New_Debt - Debt_Repayment - Dividends

Net Change in Cash

Net_Change_in_Cash = Operating_CF + Investing_CF + Financing_CF
Closing_Cash = Opening_Cash + Net_Change_in_Cash

Reference Closing_Cash back to the balance sheet. This completes the integration. If the balance sheet balances and the cash flow ties back to the closing cash balance, the model is correct.

For a dedicated walkthrough of this integration, see the three-way financial model guide.

Adding Scenario Switching with CHOOSE

A dynamic model is not truly dynamic if you have to manually edit assumptions to test different cases. Add a scenario switch so you can toggle between Base, Upside, and Downside cases from a single dropdown.

Step 1: Set Up Scenario Input Tables

On your Inputs sheet, lay out assumptions side by side:

Step 2: Add a Dropdown

In a cell named Scenario_Select, add data validation (Data > Data Validation > List) with the values Base, Upside, Downside.

Step 3: Reference with CHOOSE

Instead of hard-coding which column to use, use:

=CHOOSE(MATCH(Scenario_Select, {"Base","Upside","Downside"}, 0), Base_Growth, Upside_Growth, Downside_Growth)

MATCH returns 1, 2, or 3 based on the dropdown. CHOOSE returns the corresponding value. Change the dropdown and every formula updates instantly. No VBA, no manual copying of scenarios.

You can extend this pattern to as many scenarios as needed. Add a fourth column for a "Worst Case" or a "Managed" scenario - just update the MATCH array and add the new column data.

Building a Simple Tornado Chart

A tornado chart visualises which assumptions have the biggest impact on your output (typically Net Income).

1. Identify the 5 to 7 assumptions you want to test (e.g., Revenue Growth, COGS %, OPEX, Tax Rate).
2. For each, run the model at a low and high value, recording the output each time.
3. Build a two-column table showing downside and upside variance from the base case.
4. Insert a stacked bar chart with the base case as the invisible middle series, and format bars so one side is orange and the other blue.

The result shows stakeholders instantly which assumptions matter. If a 1% change in revenue growth swings net income by $50,000 but a 1% change in tax rate only moves it by $5,000, you know where to focus due diligence.

Best Practices for Maintaining a Dynamic Model

Use named ranges. A formula like =Revenue * COGS_Pct is self-documenting. =Inputs!$C$5 * Inputs!$C$12 is not.

Add a checks and flags section. Have a cell that turns red if the balance sheet does not balance. Add warnings for negative cash balances or debt covenants approaching their limit.

Keep time columns consistent. Every column should represent the same period (month, quarter, or year). Mixing periods causes reference errors.

Use the model structure checklist from our financial modeling best practices post. It covers naming conventions, color-coding, and audit trails.

Test every link. Change an input by a large amount and verify outputs move as expected. Revenue going up should increase net income. COGS going up should decrease it.

Frequently Asked Questions

What is a dynamic financial model in Excel?

A dynamic financial model automatically recalculates outputs when input assumptions change. It uses Excel formulas and structured references to link assumptions through calculations into outputs.

Best-practice models separate inputs (assumptions and drivers), calculations (operating and financial logic), and outputs (P&L, balance sheet, cash flow statement). This separation makes the model auditable and less error-prone.

Use a scenario input cell with data validation (dropdown list), then reference it with the CHOOSE function or INDEX and MATCH to pull the correct assumptions. No VBA required.

Integrated statements ensure consistency: net profit flows from the P&L to the balance sheet (retained earnings) and cash flow statement (starting profit), which then reconciles back to the balance sheet cash balance.

A circular reference occurs when a formula refers back to its own cell. The most common source is interest on average debt. Enable iterative calculation in Excel options or build a clean loop with a manual toggle.

Next Steps

The model you have built - three sheets, integrated statements, and scenario switching - forms the core of almost every professional financial model. Once it is working, extend it with debt schedules, tax loss carryforwards, or a separate capex module.

For broader coverage of model design and error-checking, see the complete guide to financial modeling in Excel. Before putting the model into production, review the 10 most common financial modeling mistakes to catch issues early.