Financial Model Builder

Builds structured financial models including revenue forecasts, pricing tools, DCF valuations, LBO models, scenario analyses, and SaaS cohort metrics.

When to Use This Skill

• - Building 3-statement models (P&L, Balance Sheet, Cash Flow)
• Creating SaaS revenue models (MRR, ARR, churn, LTV/CAC)
• DCF (Discounted Cash Flow) valuation analysis with WACC
• Unit economics and pricing sensitivity analysis
• LBO (Leveraged Buyout) models for investment banking
• Budget vs. actuals tracking spreadsheets
• Break-even analysis and contribution margin models
• Cohort analysis and revenue forecasting

Core Workflow

1. 1. Define scope — Identify model type, time horizon (monthly/quarterly/annual), and key drivers
2. Build assumptions tab — All inputs in one clearly labeled sheet; color-code inputs (blue) vs. formulas (black)
3. Build calculation layers — Revenue model → cost model → P&L → cash flow → balance sheet
4. Add scenarios — Base / bull / bear cases with a scenario toggle (data validation dropdown)
5. Sensitivity tables — Two-variable data tables for key metrics (e.g., price × volume → revenue)
6. Dashboard — Summary sheet with KPIs, charts, and scenario outputs
7. Validate — Balance sheet must balance; cash flow must reconcile; check for circular refs

Standard Model Structure (Excel Tabs)

Tab	Contents
INLINECODE0	All input variables with labels, units, and sources
INLINECODE1

Revenue build-up by segment/product/channel | | OpEx | COGS, gross margin, operating expenses | | P&L | Income statement (3-5 years monthly or annual) | | Cash Flow | Operating, investing, financing cash flows | | Balance Sheet | Assets, liabilities, equity (if full 3-statement) | | DCF | WACC, FCF projections, terminal value, EV bridge | | LBO | Sources & uses, debt schedule, returns summary | | Scenarios | Scenario toggle and outputs | | Sensitivity | Data tables for key variable combinations | | Cohort | MRR cohort waterfall, NRR, GRR | | Dashboard | Charts, KPI summary, executive view |

Key Formula Patterns

CODEBLOCK0

SaaS Metrics Checklist

• - MRR / ARR growth rate
• Net Revenue Retention (NRR)
• Gross Revenue Retention (GRR)
• CAC (by channel)
• LTV and LTV/CAC ratio
• Churn rate (logo and revenue)
• Payback period
• Rule of 40 (growth rate + FCF margin)

Output

Deliver as .xlsx file with all tabs labeled. Include a README tab explaining assumptions and how to use the scenario toggle. Flag any assumptions that need client input with [INPUT NEEDED] in yellow highlight.

---

DCF Model (Discounted Cash Flow)

WACC Calculation

Formula: WACC = (E/V) × Re + (D/V) × Rd × (1 − T)

Variable	Meaning	Source
E	Market value of equity	Market cap or comparable
D

Market value of debt | Balance sheet + adjustments |
| V | E + D (total capital) | Calculated |
| Re | Cost of equity (CAPM) | Rf + β × ERP |
| Rd | Cost of debt (pre-tax) | Weighted avg interest rate |
| T | Marginal tax rate | Company / jurisdiction rate |

CAPM for cost of equity:
CODEBLOCK1

Excel formulas (Assumptions tab → referenced in DCF tab):
CODEBLOCK2

Free Cash Flow (FCF) Build

CODEBLOCK3

Excel FCF row structure (columns = projection years):
CODEBLOCK4

Terminal Value

Always show both methods and reconcile:

Gordon Growth Model (GGM):
CODEBLOCK5

Exit Multiple Method:
CODEBLOCK6

Enterprise Value → Equity Value Bridge

CODEBLOCK7

Flag: if TV > 80% of EV, projection period is too short — extend to 5–10 years.

Python DCF Model

CODEBLOCK8

---

Sensitivity Analysis

Two-Variable Data Table (Excel)

Test how an output changes when two inputs vary simultaneously.

Setup:
CODEBLOCK9

Standard sensitivity ranges:
CODEBLOCK10

Conditional formatting for tables:
CODEBLOCK11

One-Variable Data Table

CODEBLOCK12

Tornado Chart (Python)

Rank variables by their impact range (high output − low output) to show what moves the needle most.

CODEBLOCK13

---

Scenario Modeling

Named Range Scenario Toggle

CODEBLOCK14

OFFSET-based Switching

CODEBLOCK15

Python Scenario Runner

CODEBLOCK16

---

LBO Model (Leveraged Buyout)

Sources & Uses of Funds

CODEBLOCK17

Excel formulas:
CODEBLOCK18

Debt Schedule

CODEBLOCK19

Returns Analysis

CODEBLOCK20

Benchmark targets: MOIC ≥ 2.5×, IRR ≥ 20%, hold period 3–7 years, leverage ≤ 6–7× EBITDA.

Python LBO Model

CODEBLOCK21

---

Revenue Forecasting & Cohort Analysis

MRR Waterfall (SaaS)

CODEBLOCK22

Excel MRR waterfall formulas:
CODEBLOCK23

Cohort Analysis Pattern

Cohort = all customers acquired in the same period.

CODEBLOCK24

Excel cohort grid (diagonal offset pattern):
CODEBLOCK25

Python Cohort Builder

CODEBLOCK26

SaaS Unit Economics

CODEBLOCK27

---

Excel / Python Interoperability

Build Excel Model with openpyxl

CODEBLOCK28

Read Excel into Pandas

CODEBLOCK29

---

Validation & Error Checking

Balance Sheet Balance Check

CODEBLOCK30

Cash Flow Reconciliation

CODEBLOCK31

Python Validation Suite

CODEBLOCK32

Common Model Errors

Error	Detection	Fix
Circular reference	Excel warning on open	Reference prior-period NWC, not current
Hard-coded values in formulas

Ctrl+~ formula audit | Move all inputs to Assumptions tab | | TV > 80% of EV | tv_pct_ev flag | Extend projection to 5–10 years | | NWC includes non-cash items | Manual review | Exclude D&A, impairments from ΔNWC | | IRR with multiple sign changes | MIRR check | =MIRR(flows, reinvest_rate, finance_rate) | | LBO leverage > 7× | Leverage check cell | Reduce debt quantum or increase equity | | BS doesn't balance | Balance check cell | Trace retained earnings / equity roll |