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. 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)#

TabContentsAssumptionsAll input variables with labels, units, and sourcesRevenueRevenue build-up by segment/product/channelOpExCOGS, gross margin, operating expensesP&LIncome statement (3-5 years monthly or annual)Cash FlowOperating, investing, financing cash flowsBalance SheetAssets, liabilities, equity (if full 3-statement)DCFWACC, FCF projections, terminal value, EV bridgeLBOSources & uses, debt schedule, returns summaryScenariosScenario toggle and outputsSensitivityData tables for key variable combinationsCohortMRR cohort waterfall, NRR, GRRDashboardCharts, KPI summary, executive view

Key Formula Patterns#

excel

# Compound growth
=B5*(1+$B$2)^(COLUMN()-COLUMN($B5))

# LTV/CAC
=B_ARPU / B_ChurnRate / B_CAC

# NPV
=NPV(discount_rate, cash_flows_range)

# IRR
=IRR(cash_flows_range)

# Scenario toggle (named range "scenario")
=IF(scenario="bull", bull_value, IF(scenario="bear", bear_value, base_value))

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)

VariableMeaningSourceEMarket value of equityMarket cap or comparableDMarket value of debtBalance sheet + adjustmentsVE + D (total capital)CalculatedReCost of equity (CAPM)Rf + β × ERPRdCost of debt (pre-tax)Weighted avg interest rateTMarginal tax rateCompany / jurisdiction rate

CAPM for cost of equity:

text

Re  = Rf + β × ERP
Rf  = risk-free rate (10-yr Treasury yield)
β   = levered beta (from comparable companies; unlever/relever for target structure)
ERP = equity risk premium (typically 4.5–6.5%)

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

excel

# Cost of equity (CAPM)
=Risk_Free_Rate + Beta * Equity_Risk_Premium

# Unlevered beta (strip out current capital structure)
=Levered_Beta / (1 + (1 - Tax_Rate) * (Debt_Value / Equity_Value))

# Relever beta at target capital structure
=Unlevered_Beta * (1 + (1 - Tax_Rate) * Target_D_E_Ratio)

# WACC
=(Equity_Value / (Equity_Value + Debt_Value) * Cost_Of_Equity)
+ (Debt_Value / (Equity_Value + Debt_Value) * Cost_Of_Debt * (1 - Tax_Rate))

Free Cash Flow (FCF) Build#

text

EBIT
  × (1 − Tax Rate)          → NOPAT (Net Operating Profit After Tax)
  + D&A                     → add back non-cash charge
  − CapEx                   → capital expenditures
  − Δ Net Working Capital   → increase in NWC = cash outflow
= Unlevered Free Cash Flow

Excel FCF row structure (columns = projection years):

excel

EBIT:            =Revenue - COGS - OpEx - DA
NOPAT:           =EBIT * (1 - Tax_Rate)
Add D&A:         =DA_row
Less CapEx:      =-CapEx_row
Less ΔNWC:       =-(NWC_row - OFFSET(NWC_row, 0, -1))
FCF:             =NOPAT + DA - CapEx - ΔNWC
Discount factor: =1 / (1 + WACC) ^ period_number
PV of FCF:       =FCF_row * Discount_factor_row

Terminal Value#

Always show both methods and reconcile:

Gordon Growth Model (GGM):

excel

# Terminal FCF (year after last projection year)
=FCF_final_year * (1 + Terminal_Growth_Rate)

# Terminal Value
=Terminal_FCF / (WACC - Terminal_Growth_Rate)

# PV of Terminal Value
=Terminal_Value / (1 + WACC) ^ projection_years

Exit Multiple Method:

excel

# Terminal EBITDA × multiple
=EBITDA_final_year * Exit_EBITDA_Multiple

# PV of Terminal Value
=Exit_Multiple_TV / (1 + WACC) ^ projection_years

Enterprise Value → Equity Value Bridge#

excel

# Enterprise Value
=SUM(PV_FCF_range) + PV_Terminal_Value

# Net Debt
=Total_Debt + Preferred_Stock + Minority_Interest - Cash - Marketable_Securities

# Equity Value (intrinsic)
=Enterprise_Value - Net_Debt

# Implied share price
=Equity_Value / Diluted_Shares_Outstanding

# Implied EV/EBITDA sanity check
=Enterprise_Value / LTM_EBITDA

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

Python DCF Model#

python

import numpy as np

def build_dcf(
    revenue: list[float],        # projected revenues per year
    ebit_margins: list[float],   # EBIT margin per year
    da_pct: float,               # D&A as % of revenue
    capex_pct: float,            # CapEx as % of revenue
    nwc_pct: float,              # NWC as % of revenue
    tax_rate: float,
    wacc: float,
    terminal_growth: float,
    exit_multiple: float,        # EBITDA exit multiple
    net_debt: float,
    shares_outstanding: float,
) -> dict:
    years = len(revenue)
    fcf = []
    for i, rev in enumerate(revenue):
        ebit = rev * ebit_margins[i]
        nopat = ebit * (1 - tax_rate)
        da = rev * da_pct
        capex = rev * capex_pct
        prev_rev = revenue[i - 1] if i > 0 else rev
        delta_nwc = (rev - prev_rev) * nwc_pct
        fcf.append(nopat + da - capex - delta_nwc)

    discount_factors = [1 / (1 + wacc) ** (i + 1) for i in range(years)]
    pv_fcfs = [f * d for f, d in zip(fcf, discount_factors)]

    # Terminal value — average of both methods
    tv_ggm = fcf[-1] * (1 + terminal_growth) / (wacc - terminal_growth)
    ebitda_final = revenue[-1] * (ebit_margins[-1] + da_pct)
    tv_exit = ebitda_final * exit_multiple
    tv_avg = (tv_ggm + tv_exit) / 2

    pv_tv = tv_avg / (1 + wacc) ** years
    ev = sum(pv_fcfs) + pv_tv
    equity_value = ev - net_debt
    price_per_share = equity_value / shares_outstanding

    return {
        "fcf": fcf,
        "pv_fcfs": pv_fcfs,
        "tv_ggm": tv_ggm,
        "tv_exit": tv_exit,
        "enterprise_value": round(ev, 2),
        "equity_value": round(equity_value, 2),
        "price_per_share": round(price_per_share, 2),
        "tv_pct_ev": round(pv_tv / ev, 4),  # flag if > 0.80
    }

---

Sensitivity Analysis#

Two-Variable Data Table (Excel)#

Test how an output changes when two inputs vary simultaneously.

Setup:

excel

# 1. Place output formula in top-left cell of the table range (e.g., D10 = IRR formula)
# 2. Row inputs across the top (e.g., E10:J10 = WACC values)
# 3. Column inputs down the left (e.g., D11:D16 = revenue growth values)
# 4. Select entire table range D10:J16
# 5. Data → What-If Analysis → Data Table
#    Row input cell:    → WACC assumption cell
#    Column input cell: → revenue growth assumption cell

Standard sensitivity ranges:

text

WACC:            [WACC-2%, WACC-1%, WACC, WACC+1%, WACC+2%]
Revenue growth:  [g-5%, g-2.5%, g, g+2.5%, g+5%]
Exit multiple:   [6.0x, 7.0x, 8.0x, 9.0x, 10.0x]
Terminal growth: [1.0%, 1.5%, 2.0%, 2.5%, 3.0%]

Conditional formatting for tables:

excel

# Green: result above target price/IRR
=$E11 > $B$3

# Red: result below target
=$E11 < $B$3

One-Variable Data Table#

excel

# Column A: input values (e.g., revenue growth rates)
# Cell B1: formula referencing the growth rate assumption (=IRR_formula)
# Select A1:B6 → Data → What-If Analysis → Data Table
# Column input cell: → growth rate assumption cell (leave row input blank)

Tornado Chart (Python)#

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

python

import matplotlib.pyplot as plt
import numpy as np

def tornado_chart(
    base_value: float,
    variables: list[str],
    low_outputs: list[float],   # output when variable is at downside
    high_outputs: list[float],  # output when variable is at upside
    title: str = "Sensitivity Tornado",
    output_label: str = "IRR (%)",
) -> plt.Figure:
    # Sort by total swing, ascending (largest impact at top)
    impacts = sorted(
        zip(variables, low_outputs, high_outputs),
        key=lambda x: x[2] - x[1],
    )
    vars_sorted, lows, highs = zip(*impacts)
    y_pos = np.arange(len(vars_sorted))

    fig, ax = plt.subplots(figsize=(10, max(4, len(variables) * 0.65)))
    ax.barh(y_pos, [h - base_value for h in highs], left=base_value,
            color="#2ecc71", alpha=0.85, label="Upside")
    ax.barh(y_pos, [l - base_value for l in lows], left=base_value,
            color="#e74c3c", alpha=0.85, label="Downside")
    ax.axvline(x=base_value, color="black", linewidth=1.5, linestyle="--",
               label=f"Base: {base_value}")
    ax.set_yticks(y_pos)
    ax.set_yticklabels(vars_sorted)
    ax.set_xlabel(output_label)
    ax.set_title(title)
    ax.legend()
    plt.tight_layout()
    return fig

# Usage
fig = tornado_chart(
    base_value=18.5,
    variables=["Revenue Growth", "EBITDA Margin", "Exit Multiple", "WACC", "Leverage"],
    low_outputs=[12.1, 14.3, 15.8, 16.2, 17.0],
    high_outputs=[27.3, 23.1, 22.4, 21.0, 20.1],
    title="LBO IRR Sensitivity",
    output_label="IRR (%)",
)
fig.savefig("tornado.png", dpi=150, bbox_inches="tight")

---

Scenario Modeling#

Named Range Scenario Toggle#

excel

# Step 1: Dropdown in Assumptions tab
#   Cell C2: data validation → list → "Base,Bull,Bear"
#   Define named range: scenario = Assumptions!$C$2

# Step 2: Scenario input table (Assumptions rows 30–40)
#   Variable         | Base | Bull | Bear
#   Revenue Growth   | 15%  | 25%  |  5%
#   Gross Margin     | 65%  | 70%  | 55%
#   Monthly Churn    |  8%  |  5%  | 15%
#   Exit Multiple    | 8.0x | 10.0x| 6.0x

# Step 3: Reference in model using CHOOSE + MATCH
=CHOOSE(MATCH(scenario, {"Base","Bull","Bear"}, 0), base_val, bull_val, bear_val)

# Alternative — INDEX/MATCH for table-driven lookup
=INDEX(scenario_table_range,
       MATCH(variable_row_label, variable_labels, 0),
       MATCH(scenario, {"Base","Bull","Bear"}, 0))

OFFSET-based Switching#

excel

# 0=Base, 1=Bull, 2=Bear offset from base column
=OFFSET(base_input_cell, 0, MATCH(scenario, {"Base","Bull","Bear"}, 0) - 1)

Python Scenario Runner#

python

from dataclasses import dataclass
from typing import Literal
import pandas as pd

@dataclass
class Scenario:
    name: Literal["base", "bull", "bear"]
    revenue_growth: float
    gross_margin: float
    churn_rate: float
    exit_multiple: float
    wacc: float

SCENARIOS: dict[str, Scenario] = {
    "base": Scenario("base", revenue_growth=0.15, gross_margin=0.65,
                     churn_rate=0.08, exit_multiple=8.0, wacc=0.12),
    "bull": Scenario("bull", revenue_growth=0.25, gross_margin=0.70,
                     churn_rate=0.05, exit_multiple=10.0, wacc=0.11),
    "bear": Scenario("bear", revenue_growth=0.05, gross_margin=0.55,
                     churn_rate=0.15, exit_multiple=6.0, wacc=0.14),
}

def run_all_scenarios(model_fn, base_inputs: dict) -> pd.DataFrame:
    results = []
    for name, s in SCENARIOS.items():
        inputs = {
            **base_inputs,
            "revenue_growth": s.revenue_growth,
            "gross_margin": s.gross_margin,
            "churn_rate": s.churn_rate,
            "exit_multiple": s.exit_multiple,
            "wacc": s.wacc,
        }
        output = model_fn(**inputs)
        results.append({"scenario": name, **output})
    return pd.DataFrame(results).set_index("scenario")

# Usage
df = run_all_scenarios(build_dcf, base_inputs={...})
print(df[["enterprise_value", "equity_value", "price_per_share"]])

---

LBO Model (Leveraged Buyout)#

Sources & Uses of Funds#

text

SOURCES                            USES
──────────────────────────────     ──────────────────────────────
Senior Secured Debt                Purchase Price (EV)
  Term Loan A          $Xm           = Entry EBITDA × Entry Multiple
  Term Loan B          $Xm         Transaction Fees  (~2% of EV)
Subordinated Debt      $Xm         Financing Fees    (~2% of debt)
Sponsor Equity         $Xm         Cash to Balance Sheet (minimum)
──────────────────────────────     ──────────────────────────────
Total Sources          $Xm         Total Uses                  $Xm

Excel formulas:

excel

# Entry EV
=Entry_EBITDA * Entry_Multiple

# Equity check (target: sponsor equity 30–50% of EV)
=Sponsor_Equity / Entry_EV

# Sponsor equity (residual plug)
=Total_Uses - Senior_Debt - Sub_Debt

# Total uses
=Entry_EV + Transaction_Fees + Financing_Fees + Min_Cash_on_BS

Debt Schedule#

excel

# Columns per year: Beginning_Bal | New_Borrow | Mandatory_Amort | Optional_Paydown | Ending_Bal | Interest

# Beginning balance (from prior year ending)
=OFFSET(Ending_Bal_cell, 0, -1)

# Mandatory amortization — Term Loan A (5%/yr example)
=-TLA_Original_Principal * TLA_Amort_Rate

# Optional paydown — excess cash sweep
=-MAX(0, Beginning_Cash + Operating_CF - Min_Cash_Balance - Mandatory_Debt_Service)

# Ending balance
=Beginning_Bal + New_Borrow + Mandatory_Amort + Optional_Paydown

# Interest (average balance method)
=(Beginning_Bal + Ending_Bal) / 2 * Interest_Rate

Returns Analysis#

excel

# Exit Enterprise Value
=Exit_Year_EBITDA * Exit_Multiple

# Equity proceeds to sponsor
=Exit_EV - Remaining_Total_Debt + Exit_Cash - Exit_Transaction_Fees

# MOIC (Multiple on Invested Capital)
=Total_Equity_Proceeds / Sponsor_Equity_Invested

# IRR (XIRR for exact dates)
=XIRR(
  {-Sponsor_Equity, 0, 0, 0, 0, Equity_Proceeds},
  {Entry_Date, Y1_Date, Y2_Date, Y3_Date, Y4_Date, Exit_Date}
)

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

Python LBO Model#

python

def build_lbo(
    entry_ebitda: float,
    entry_multiple: float,
    exit_multiple: float,
    ebitda_growth: list[float],     # annual growth rates for each hold year
    debt_tranches: list[dict],      # [{"name":"TLA","amount":100,"rate":0.06,"amort":0.05}]
    tax_rate: float = 0.25,
    min_cash: float = 5.0,
    transaction_fee_pct: float = 0.02,
    financing_fee_pct: float = 0.02,
) -> dict:
    entry_ev = entry_ebitda * entry_multiple
    total_debt = sum(t["amount"] for t in debt_tranches)
    transaction_fees = entry_ev * transaction_fee_pct
    financing_fees = total_debt * financing_fee_pct
    sponsor_equity = entry_ev + transaction_fees + financing_fees + min_cash - total_debt

    ebitda_series = [entry_ebitda]
    for g in ebitda_growth:
        ebitda_series.append(ebitda_series[-1] * (1 + g))

    # Debt paydown (mandatory amortization only for simplicity)
    remaining_debt = total_debt
    for _ in ebitda_growth:
        mandatory = sum(t["amount"] * t.get("amort", 0.01) for t in debt_tranches)
        remaining_debt -= mandatory

    exit_ev = ebitda_series[-1] * exit_multiple
    equity_proceeds = max(0.0, exit_ev - remaining_debt)
    moic = equity_proceeds / sponsor_equity if sponsor_equity > 0 else 0.0
    years = len(ebitda_growth)
    irr_approx = moic ** (1 / years) - 1  # geometric approx; use numpy_financial.irr for exact

    return {
        "entry_ev": round(entry_ev, 2),
        "sponsor_equity": round(sponsor_equity, 2),
        "total_debt": round(total_debt, 2),
        "leverage_ratio": round(total_debt / entry_ebitda, 2),
        "exit_ev": round(exit_ev, 2),
        "equity_proceeds": round(equity_proceeds, 2),
        "moic": round(moic, 2),
        "irr_approx": round(irr_approx, 4),
    }

---

Revenue Forecasting & Cohort Analysis#

MRR Waterfall (SaaS)#

text

Beginning MRR
  + New MRR           (new logos × ARPU)
  + Expansion MRR     (upsell/cross-sell to existing customers)
  − Contraction MRR   (downgrades)
  − Churned MRR       (cancellations)
= Ending MRR

ARR = Ending MRR × 12

Excel MRR waterfall formulas:

excel

# New MRR
=New_Customers_This_Month * ARPU

# Expansion MRR (from existing base)
=Beginning_MRR * Monthly_Expansion_Rate

# Contraction MRR (downgrade rate)
=-Beginning_MRR * Monthly_Contraction_Rate

# Churned MRR (cancellation rate)
=-Beginning_MRR * Monthly_Churn_Rate

# Ending MRR
=Beginning_MRR + New_MRR + Expansion_MRR + Contraction_MRR + Churned_MRR

# NRR (Net Revenue Retention) — trailing 12 months
=(Ending_MRR_cohort - New_MRR_cohort) / Beginning_MRR_cohort

# GRR (Gross Revenue Retention — excludes expansion, floor at 0%)
=MAX(0, (Beginning_MRR + Contraction_MRR + Churned_MRR) / Beginning_MRR)

Cohort Analysis Pattern#

Cohort = all customers acquired in the same period.

text

             Month 0   Month 1   Month 2   Month 3  ...
Cohort Jan     100%      85%       74%       67%
Cohort Feb               100%      82%       71%
Cohort Mar                          100%      84%

Excel cohort grid (diagonal offset pattern):

excel

# Retention at month M for cohort starting at column C
=IF(COLUMN() - cohort_start_col > 0,
   Retention_Rate ^ (COLUMN() - cohort_start_col),
   IF(COLUMN() = cohort_start_col, 1, ""))

# Revenue from cohort at month M (with expansion)
=Cohort_starting_MRR
  * Retention_Rate ^ M
  * (1 + Monthly_Expansion_Rate) ^ M

Python Cohort Builder#

python

import numpy as np
import pandas as pd

def build_cohort_model(
    cohort_sizes: list[int],          # customers entering each period
    arpu: float,
    monthly_retention: float,         # e.g., 0.92 = 92% retained per month
    monthly_expansion: float = 0.02,  # expansion revenue per retained customer
    periods: int = 24,
) -> pd.DataFrame:
    """Returns cohort × period revenue matrix."""
    n_cohorts = len(cohort_sizes)
    revenue = np.zeros((n_cohorts, periods))

    for i, size in enumerate(cohort_sizes):
        for m in range(periods - i):
            customers = size * (monthly_retention ** m)
            expansion_mult = (1 + monthly_expansion) ** m
            revenue[i, i + m] = customers * arpu * expansion_mult

    df = pd.DataFrame(
        revenue,
        index=[f"Cohort_{i + 1:02d}" for i in range(n_cohorts)],
        columns=[f"Month_{m + 1:02d}" for m in range(periods)],
    )
    df["Total_Revenue"] = df.sum(axis=1)
    return df

def compute_nrr(cohort_df: pd.DataFrame, window: int = 12) -> float:
    """NRR from cohort matrix — trailing window vs next window."""
    start = cohort_df.iloc[:, :window].sum().sum()
    end = cohort_df.iloc[:, window:window * 2].sum().sum()
    return round(end / start, 4) if start > 0 else 0.0

SaaS Unit Economics#

excel

# LTV (gross margin adjusted)
=(ARPU * Gross_Margin_Pct) / Monthly_Churn_Rate

# LTV/CAC ratio (target: > 3×)
=LTV / CAC

# CAC Payback Period in months (target: < 12 months)
=CAC / (ARPU * Gross_Margin_Pct)

# Magic Number — sales efficiency (target: > 0.75)
=(Current_Qtr_ARR - Prior_Qtr_ARR) / Prior_Qtr_Sales_And_Marketing_Spend

# Rule of 40 (target: ≥ 40%)
=YoY_Revenue_Growth_Pct + FCF_Margin_Pct

---

Excel / Python Interoperability#

Build Excel Model with openpyxl#

python

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.utils import quote_sheetname, absolute_coordinate

# Palette
BLUE_FILL   = PatternFill(start_color="DBEAFE", end_color="DBEAFE", fill_type="solid")  # inputs
YELLOW_FILL = PatternFill(start_color="FEF9C3", end_color="FEF9C3", fill_type="solid")  # flagged
HEADER_FILL = PatternFill(start_color="1E3A5F", end_color="1E3A5F", fill_type="solid")  # headers

def style_input(ws, row: int, col: int) -> None:
    cell = ws.cell(row=row, column=col)
    cell.fill = BLUE_FILL
    cell.font = Font(color="1F497D")

def style_header_row(ws, row: int, cols: int, start_col: int = 1) -> None:
    for c in range(start_col, start_col + cols):
        cell = ws.cell(row=row, column=c)
        cell.fill = HEADER_FILL
        cell.font = Font(bold=True, color="FFFFFF")
        cell.alignment = Alignment(horizontal="center")

def add_named_range(wb: Workbook, name: str, sheet: str, cell: str) -> None:
    ref = f"{quote_sheetname(sheet)}!{absolute_coordinate(cell)}"
    wb.defined_names[name] = DefinedName(name, attr_text=ref)

def write_assumptions(wb: Workbook, assumptions: dict) -> None:
    ws = wb.create_sheet("Assumptions")
    ws.column_dimensions["A"].width = 30
    ws.column_dimensions["B"].width = 18
    ws.column_dimensions["C"].width = 12
    style_header_row(ws, 1, 3)
    for col, h in enumerate(["Parameter", "Value", "Unit"], 1):
        ws.cell(row=1, column=col).value = h
    for row, (key, val) in enumerate(assumptions.items(), 2):
        ws.cell(row=row, column=1).value = key
        ws.cell(row=row, column=2).value = val
        style_input(ws, row, 2)

def build_model_xlsx(data: dict, filepath: str) -> None:
    wb = Workbook()
    wb.remove(wb.active)
    write_assumptions(wb, data["assumptions"])
    add_named_range(wb, "WACC", "Assumptions", "B5")
    add_named_range(wb, "scenario", "Assumptions", "B2")
    add_named_range(wb, "terminal_growth", "Assumptions", "B8")
    wb.save(filepath)

Read Excel into Pandas#

python

import pandas as pd

def load_model_tabs(filepath: str) -> dict[str, pd.DataFrame]:
    xl = pd.ExcelFile(filepath)
    return {sheet: xl.parse(sheet, index_col=0) for sheet in xl.sheet_names}

def extract_assumptions(filepath: str) -> dict:
    df = pd.read_excel(filepath, sheet_name="Assumptions", index_col=0)
    return df.iloc[:, 0].to_dict()

---

Validation & Error Checking#

Balance Sheet Balance Check#

excel

# Total Assets
=SUM(Current_Assets_range) + SUM(Non_Current_Assets_range)

# Total Liabilities + Equity
=SUM(Liabilities_range) + Total_Equity

# Balance check cell (must be ~0)
=Total_Assets - Total_Liabilities_And_Equity

# Conditional format: flag if |BS_Check| > 0.01
=ABS(BS_Check_cell) > 0.01

Cash Flow Reconciliation#

excel

# Calculated ending cash (indirect method)
=Beginning_Cash + Operating_CF + Investing_CF + Financing_CF

# Reconciliation check (must be ~0)
=Calculated_Ending_Cash - Balance_Sheet_Cash_Ending

# Flag
=ABS(CF_Recon_cell) > 0.01

Python Validation Suite#

python

def validate_model(results: dict) -> list[str]:
    """Returns list of error messages. Empty list = model passes."""
    errors = []

    bs = results.get("balance_sheet", {})
    if abs(bs.get("total_assets", 0) - bs.get("total_liabilities_equity", 0)) > 0.01:
        errors.append("Balance sheet does not balance")

    cf = results.get("cash_flow", {})
    if abs(cf.get("ending_cash", 0) - cf.get("calc_ending_cash", 0)) > 0.01:
        errors.append("Cash flow does not reconcile with balance sheet")

    dcf = results.get("dcf", {})
    if dcf.get("tv_pct_ev", 0) > 0.80:
        errors.append(
            f"Terminal value is {dcf['tv_pct_ev']:.0%} of EV — extend projection period"
        )

    lbo = results.get("lbo", {})
    if lbo.get("leverage_ratio", 0) > 7.0:
        errors.append(f"Leverage {lbo['leverage_ratio']:.1f}× exceeds 7.0× — review debt structure")
    if 0 < lbo.get("moic", 0) < 2.0:
        errors.append(f"MOIC {lbo['moic']:.2f}× below 2.0× — deal unlikely to pencil")

    saas = results.get("saas_metrics", {})
    if saas.get("ltv_cac", 0) < 3.0:
        errors.append(f"LTV/CAC {saas['ltv_cac']:.1f}× below 3.0× minimum")

    return errors

Common Model Errors#

ErrorDetectionFixCircular referenceExcel warning on openReference prior-period NWC, not currentHard-coded values in formulasCtrl+~ formula auditMove all inputs to Assumptions tabTV > 80% of EVtv_pct_ev flagExtend projection to 5–10 yearsNWC includes non-cash itemsManual reviewExclude D&A, impairments from ΔNWCIRR with multiple sign changesMIRR check=MIRR(flows, reinvest_rate, finance_rate)LBO leverage > 7×Leverage check cellReduce debt quantum or increase equityBS doesn't balanceBalance check cellTrace retained earnings / equity roll