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
- Define scope — Identify model type, time horizon (monthly/quarterly/annual), and key drivers
- Build assumptions tab — All inputs in one clearly labeled sheet; color-code inputs (blue) vs. formulas (black)
- Build calculation layers — Revenue model → cost model → P&L → cash flow → balance sheet
- Add scenarios — Base / bull / bear cases with a scenario toggle (data validation dropdown)
- Sensitivity tables — Two-variable data tables for key metrics (e.g., price × volume → revenue)
- Dashboard — Summary sheet with KPIs, charts, and scenario outputs
- Validate — Balance sheet must balance; cash flow must reconcile; check for circular refs
Standard Model Structure (Excel Tabs)
| Tab | Contents |
|---|
Assumptions | All input variables with labels, units, and sources |
Revenue | 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
# 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)
| 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:
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):
# 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
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):
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):
# 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:
# 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
# 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
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:
# 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:
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:
# Green: result above target price/IRR
=$E11 > $B$3
# Red: result below target
=$E11 < $B$3
One-Variable Data Table
# 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.
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
# 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
# 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
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
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:
# 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
# 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
# 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
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)
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:
# 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.
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):
# 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
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
# 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
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
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
# 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
# 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
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
| 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 |
