Chapter 29: Financial Modeling with Python

"A financial model is not a prediction. It is a structured way of thinking about what could happen — and why." — Common wisdom among financial analysts

Why This Chapter Matters

Sandra Chen, Acme Corp's VP of Finance, had a problem that would be familiar to anyone who has worked in a mid-sized company. Every year, the annual planning process consumed three weeks of her team's time. Seventeen spreadsheet files. Forty-two tabs. Links that broke when anyone moved a cell. Manual copy-paste from the accounting system. A "sensitivity analysis" that meant changing one number and re-running everything by hand.

When Priya — Acme's data analyst — suggested building the planning model in Python, Sandra's first reaction was skepticism. "Excel is the language of finance," she said. "Everyone can open it. The auditors can follow it."

Sandra was right about Excel. But she was thinking about reporting, not modeling. The distinction matters enormously.

Financial reporting is showing what happened: last quarter's income statement, the balance sheet at year end, the cash position today. Excel is excellent for this. Tables, formats, colors, formulas — it is built for humans to read static snapshots.

Financial modeling is exploring what could happen: if revenue grows 12% but we hire three people, can we still hit our EBITDA margin? If a customer pays 60 days late instead of 30, how much working capital do we need? If we buy this equipment, does the NPV justify the risk?

This is where Python earns its place. When a model has 500 scenarios to run, when assumptions need to flow through fifteen interdependent calculations, when you want to visualize an entire sensitivity surface rather than a single number — Python does this in seconds, cleanly, reproducibly, with no broken links.

This chapter teaches you how to build financial models in Python. By the end, you will be able to construct multi-year income statement projections, calculate free cash flow, run break-even analysis, evaluate capital investments with NPV and IRR, and build scenario and sensitivity models that would take days to build manually in Excel.

29.1 The Decimal Module for Financial Precision

Before writing a single financial calculation, we need to revisit a concept from Chapter 3: floating-point arithmetic errors.

Run this in Python:

>>> 0.1 + 0.2
0.30000000000000004

That extra 0.00000000000004 is not a Python bug. It is a fundamental property of how computers store decimal numbers in binary. For most calculations, this is harmless. For financial calculations — especially when you are aggregating thousands of transactions, calculating tax to the cent, or presenting results to a CFO — it is not acceptable.

The decimal module solves this:

from decimal import Decimal, getcontext

# Set precision to 10 significant figures
getcontext().prec = 10

price = Decimal("149.99")
quantity = Decimal("47")
tax_rate = Decimal("0.0875")

subtotal = price * quantity
tax = subtotal * tax_rate
total = subtotal + tax

print(f"Subtotal: ${subtotal:,.2f}")   # $7,049.53
print(f"Tax:      ${tax:,.2f}")        # $616.83
print(f"Total:    ${total:,.2f}")      # $7,666.36

When to Use Decimal vs Float

For financial modeling — as opposed to financial transaction processing — you will often use float anyway. This is not reckless. When you are projecting revenue for 2026 with a growth assumption of 8.5%, the projection itself is inherently imprecise. A floating-point error at the 15th decimal place is irrelevant when your assumption has a margin of error of hundreds of thousands of dollars.

The rule of thumb:

For the models in this chapter, we will use float for projections and Decimal where exact cent-level precision matters.

29.2 Building an Income Statement Model

The income statement (also called the profit and loss statement, or P&L) is the foundational document of business financial performance. It shows revenue, costs, and the progression to profit over a period of time.

A well-structured income statement model flows from top to bottom:

Revenue
  - Cost of Goods Sold (COGS)
  = Gross Profit
  - Operating Expenses
  = EBITDA (Earnings Before Interest, Taxes, Depreciation, Amortization)
  - Depreciation & Amortization
  = EBIT (Earnings Before Interest and Taxes)
  - Interest Expense
  = EBT (Earnings Before Taxes)
  - Income Tax
  = Net Income

Each line item in a financial model has an assumption behind it. The power of a Python model is that those assumptions are explicit, named variables — not buried inside Excel formula strings.

Revenue Projections

Revenue can be modeled several ways depending on your business:

Simple growth rate model: Apply a percentage growth to prior year revenue.

base_revenue = 4_200_000  # Year 0 actual
growth_rates = [0.08, 0.10, 0.12, 0.10, 0.09]  # Years 1-5

revenues = [base_revenue]
for rate in growth_rates:
    revenues.append(revenues[-1] * (1 + rate))

# revenues = [4200000, 4536000, 4989600, 5588352, 6147187, 6700434]

Volume x Price model: More realistic for product businesses.

# Year 0 actuals
base_units = 28_000
base_price = 150.00

# Annual assumptions
unit_growth = [0.06, 0.08, 0.08, 0.07, 0.06]
price_growth = [0.02, 0.02, 0.03, 0.02, 0.02]

units = [base_units]
prices = [base_price]

for i in range(5):
    units.append(units[-1] * (1 + unit_growth[i]))
    prices.append(prices[-1] * (1 + price_growth[i]))

revenues = [u * p for u, p in zip(units, prices)]

The volume x price model is more powerful because it lets you run separate sensitivities on volume assumptions vs pricing assumptions — which is exactly the kind of analysis executives want.

COGS as a Percentage of Revenue

Cost of Goods Sold (COGS) includes the direct costs of producing your product or service: raw materials, direct labor, manufacturing overhead. The simplest way to model it is as a percentage of revenue (the gross margin assumption):

gross_margin = 0.62  # 62% gross margin assumption

cogs = [rev * (1 - gross_margin) for rev in revenues]
gross_profits = [rev - c for rev, c in zip(revenues, cogs)]

A more sophisticated model might have COGS as a mix of fixed and variable components, or model each cost line separately.

Operating Expenses: Fixed and Variable Components

Operating expenses (OpEx) are costs not directly tied to production. They fall into two categories:

Fixed costs do not change with revenue volume: rent, executive salaries, insurance, software subscriptions. They step up occasionally (when you hire a VP or move to a larger office), but they do not scale linearly.

Variable costs scale with activity: sales commissions, marketing spend tied to revenue, customer support headcount growing with customer count.

# Fixed operating expenses (dollar amounts, growing at inflation)
opex_fixed_base = {
    "rent_utilities": 180_000,
    "executive_salaries": 450_000,
    "insurance": 42_000,
    "software_subscriptions": 95_000,
}
inflation_rate = 0.03

# Variable operating expenses (as % of revenue)
opex_variable_pct = {
    "sales_commissions": 0.05,
    "marketing": 0.04,
    "customer_support": 0.02,
}

total_opex = []
for year_idx, rev in enumerate(revenues[1:], 1):  # Years 1-5
    inflation_factor = (1 + inflation_rate) ** year_idx

    fixed = sum(v * inflation_factor for v in opex_fixed_base.values())
    variable = sum(pct * rev for pct in opex_variable_pct.values())

    total_opex.append(fixed + variable)

EBITDA, EBIT, EBT, Net Income

With revenue, COGS, and OpEx modeled, the rest of the income statement flows as subtractions:

# Depreciation & Amortization (D&A)
# Typically modeled as a fixed amount or % of fixed assets
da = [85_000 * (1.05 ** i) for i in range(1, 6)]  # Grows 5%/yr with capex

# Interest expense (on long-term debt)
debt_balance = 500_000
interest_rate = 0.065
interest_expense = [debt_balance * interest_rate] * 5  # Simplified

# Tax rate
effective_tax_rate = 0.27

# Assemble the income statement
ebitda = [gp - opex for gp, opex in zip(gross_profits[1:], total_opex)]
ebit = [e - d for e, d in zip(ebitda, da)]
ebt = [e - i for e, i in zip(ebit, interest_expense)]
net_income = [max(0, e * (1 - effective_tax_rate)) for e in ebt]

The max(0, ...) handles years where EBT is negative — you do not pay taxes on a loss (in a simplified model).

Displaying the Model as a DataFrame

Once you have all the components, assembling them into a pandas DataFrame gives you a clean, professional output:

import pandas as pd

years = [f"Year {i}" for i in range(1, 6)]

income_statement = pd.DataFrame({
    "Revenue": revenues[1:],
    "COGS": cogs[1:],
    "Gross Profit": gross_profits[1:],
    "Operating Expenses": total_opex,
    "EBITDA": ebitda,
    "D&A": da,
    "EBIT": ebit,
    "Interest Expense": interest_expense,
    "EBT": ebt,
    "Net Income": net_income,
}, index=years).T

# Format as dollars
formatted = income_statement.applymap(lambda x: f"${x:>12,.0f}")
print(formatted.to_string())

29.3 Cash Flow Modeling

Profitability and cash are not the same thing. A company can show positive net income and still run out of cash. This is why cash flow modeling is as important as income statement modeling.

Operating Cash Flow (Simplified)

The simplest approximation of operating cash flow:

Operating Cash Flow = Net Income + D&A - Changes in Working Capital

Why add back D&A? Because depreciation is a non-cash expense. It reduces net income but does not represent actual cash leaving the business.

# Simplified operating cash flow
ocf = [ni + d for ni, d in zip(net_income, da)]

Working Capital Dynamics

Working capital is the cash tied up in the day-to-day operations of a business:

Working Capital = Accounts Receivable + Inventory - Accounts Payable

When working capital increases, it uses cash (a customer owes you money, but you have not received it yet). When working capital decreases, it generates cash.

The key drivers: - Days Sales Outstanding (DSO): How many days until customers pay. Higher DSO = more cash tied up in A/R. - Days Inventory Outstanding (DIO): How long inventory sits before sale. Higher DIO = more cash in inventory. - Days Payable Outstanding (DPO): How long before you pay suppliers. Higher DPO = less cash you need (suppliers are financing you).

# Working capital assumptions
dso = 42    # Days to collect from customers
dio = 28    # Days of inventory on hand
dpo = 35    # Days to pay suppliers

def working_capital(revenue, cogs, dso, dio, dpo):
    """Calculate working capital from operational metrics."""
    days_in_year = 365
    ar = revenue * (dso / days_in_year)
    inventory = cogs * (dio / days_in_year)
    ap = cogs * (dpo / days_in_year)
    return ar + inventory - ap

# Calculate working capital for each year
wc = [working_capital(rev, c, dso, dio, dpo)
      for rev, c in zip(revenues[1:], cogs[1:])]

# Change in working capital (Year-over-Year)
prev_wc = working_capital(revenues[0], cogs[0] if cogs else revenues[0]*0.38,
                          dso, dio, dpo)
delta_wc = [wc[0] - prev_wc] + [wc[i] - wc[i-1] for i in range(1, len(wc))]

# Adjusted operating cash flow
ocf_adjusted = [ocf_i - dwc for ocf_i, dwc in zip(ocf, delta_wc)]

Free Cash Flow

Free Cash Flow (FCF) is the cash available after maintaining and growing the business's asset base:

Free Cash Flow = Operating Cash Flow - Capital Expenditures (CapEx)

# Capital expenditure assumptions
capex = [120_000, 95_000, 150_000, 110_000, 130_000]  # Planned investments

fcf = [ocf_i - c for ocf_i, c in zip(ocf_adjusted, capex)]

Free cash flow is the metric most closely watched by investors and acquirers. It represents the cash the business actually generates that can be used for debt repayment, dividends, acquisitions, or reinvestment.

29.4 Break-Even Analysis

Break-even analysis answers one of the most fundamental business questions: How much do we need to sell to cover our costs?

The Key Concepts

Fixed Costs (FC): Costs that do not change with volume. Rent, base salaries, insurance. You pay these whether you sell one unit or ten thousand.

Variable Costs (VC): Costs that scale with each unit sold. Materials, direct labor, shipping, sales commissions.

Contribution Margin: Revenue minus variable costs. Each unit sold "contributes" this amount toward covering fixed costs — and then to profit.

Contribution Margin per Unit = Selling Price - Variable Cost per Unit
Contribution Margin Ratio = Contribution Margin / Selling Price

Break-Even Point:

Break-Even Units = Fixed Costs / Contribution Margin per Unit
Break-Even Revenue = Fixed Costs / Contribution Margin Ratio

Python Implementation

# Acme Corp: New product line analysis
selling_price = 85.00
variable_cost_per_unit = 31.50
fixed_costs_monthly = 42_000

contribution_margin = selling_price - variable_cost_per_unit
cm_ratio = contribution_margin / selling_price

breakeven_units = fixed_costs_monthly / contribution_margin
breakeven_revenue = fixed_costs_monthly / cm_ratio

print(f"Contribution Margin:       ${contribution_margin:.2f}/unit")
print(f"Contribution Margin Ratio:  {cm_ratio:.1%}")
print(f"Break-Even Units:           {breakeven_units:,.0f} units/month")
print(f"Break-Even Revenue:        ${breakeven_revenue:>10,.0f}/month")

Output:

Contribution Margin:       $53.50/unit
Contribution Margin Ratio:  62.9%
Break-Even Units:           785 units/month
Break-Even Revenue:        $66,729/month

Break-Even Chart

A break-even chart makes the concept intuitively clear:

import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker

units = np.arange(0, 1600, 10)
total_revenue = units * selling_price
total_costs = fixed_costs_monthly + (units * variable_cost_per_unit)

fig, ax = plt.subplots(figsize=(10, 6))

ax.plot(units, total_revenue, 'b-', linewidth=2.5, label='Total Revenue')
ax.plot(units, total_costs, 'r-', linewidth=2.5, label='Total Costs')
ax.axhline(y=fixed_costs_monthly, color='gray', linestyle='--',
           linewidth=1.5, label=f'Fixed Costs (${fixed_costs_monthly:,.0f})')

# Shade profit and loss regions
ax.fill_between(units, total_revenue, total_costs,
                where=(total_revenue >= total_costs),
                alpha=0.15, color='green', label='Profit Zone')
ax.fill_between(units, total_revenue, total_costs,
                where=(total_revenue < total_costs),
                alpha=0.15, color='red', label='Loss Zone')

# Mark break-even point
ax.axvline(x=breakeven_units, color='darkgreen', linestyle=':', linewidth=1.5)
ax.annotate(f'Break-Even: {breakeven_units:.0f} units\n${breakeven_revenue:,.0f}/mo',
            xy=(breakeven_units, breakeven_revenue),
            xytext=(breakeven_units + 80, breakeven_revenue - 8000),
            fontsize=10,
            arrowprops=dict(arrowstyle='->', color='darkgreen'),
            color='darkgreen')

ax.set_xlabel('Units Sold per Month', fontsize=12)
ax.set_ylabel('Dollars ($)', fontsize=12)
ax.set_title('Break-Even Analysis: New Product Line', fontsize=14, fontweight='bold')
ax.legend(loc='upper left', fontsize=10)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}'))
ax.grid(True, alpha=0.3)

plt.tight_layout()
plt.savefig('break_even_chart.png', dpi=150)
plt.show()

Margin of Safety

Once you know the break-even point, you can calculate the margin of safety — how far current sales are above break-even:

current_units = 1_100  # Current monthly volume

margin_of_safety_units = current_units - breakeven_units
margin_of_safety_pct = margin_of_safety_units / current_units

current_profit = (contribution_margin * current_units) - fixed_costs_monthly

print(f"Current Volume:          {current_units:,} units/month")
print(f"Margin of Safety:        {margin_of_safety_units:.0f} units ({margin_of_safety_pct:.1%})")
print(f"Current Monthly Profit: ${current_profit:,.0f}")

The margin of safety tells you how much sales can decline before you start losing money. A margin of safety of 40% means sales would need to fall 40% before you hit break-even — a reassuring cushion.

29.5 Net Present Value (NPV) and Internal Rate of Return (IRR)

The Time Value of Money

A dollar today is worth more than a dollar tomorrow. Not because of inflation (though that is part of it), but because a dollar today can be invested and earn a return. If you can earn 8% annually, then $1 today becomes $1.08 in one year. Conversely, $1.08 promised one year from now is only worth $1 today.

This concept — the time value of money — is the foundation of capital investment analysis.

Net Present Value (NPV) is the sum of all future cash flows from an investment, each discounted back to today's value, minus the initial investment. It answers: "After accounting for the time value of money, does this investment make financial sense?"

• NPV > 0: The investment creates value. Do it.
• NPV < 0: The investment destroys value. Do not do it.
• NPV = 0: The investment exactly earns your required return.

Manual NPV Calculation

The formula for NPV:

NPV = -Initial Investment + CF₁/(1+r)¹ + CF₂/(1+r)² + ... + CFₙ/(1+r)ⁿ

Where r is the discount rate — your minimum required rate of return, often the company's cost of capital or a hurdle rate set by management.

def calculate_npv(discount_rate: float, cash_flows: list[float]) -> float:
    """
    Calculate Net Present Value.

    Args:
        discount_rate: Required rate of return (e.g., 0.10 for 10%)
        cash_flows: List of cash flows. First element is Year 0 (typically negative).

    Returns:
        NPV as a float
    """
    npv = 0.0
    for t, cf in enumerate(cash_flows):
        npv += cf / (1 + discount_rate) ** t
    return npv

Calculating IRR

The Internal Rate of Return (IRR) is the discount rate at which NPV equals zero. It is the investment's effective annual return. If the IRR exceeds your required rate of return (hurdle rate), the investment is worth pursuing.

IRR cannot be solved algebraically — it requires numerical methods (iterative solving):

def calculate_irr(cash_flows: list[float],
                  guess: float = 0.1,
                  tolerance: float = 1e-6,
                  max_iterations: int = 1000) -> float:
    """
    Calculate IRR using Newton-Raphson iteration.

    Args:
        cash_flows: List starting with Year 0 cash flow (negative = outflow)
        guess: Initial guess for IRR
        tolerance: Convergence threshold
        max_iterations: Maximum iterations before giving up

    Returns:
        IRR as a decimal (e.g., 0.15 for 15%)
    """
    rate = guess

    for _ in range(max_iterations):
        npv = sum(cf / (1 + rate) ** t for t, cf in enumerate(cash_flows))

        # Derivative of NPV with respect to rate
        dnpv = sum(-t * cf / (1 + rate) ** (t + 1)
                   for t, cf in enumerate(cash_flows))

        if abs(dnpv) < 1e-12:
            break

        new_rate = rate - npv / dnpv

        if abs(new_rate - rate) < tolerance:
            return new_rate

        rate = new_rate

    return rate

Practical Application: Should We Buy This Equipment?

Acme Corp is evaluating a packaging automation machine:

# Investment analysis: Packaging automation machine
initial_investment = -285_000   # Year 0 outflow
annual_savings = [
    68_000,   # Year 1: Partial-year benefits, training period
    82_000,   # Year 2: Full implementation
    87_000,   # Year 3
    89_000,   # Year 4
    91_000,   # Year 5
    45_000,   # Year 6: Salvage value + remaining savings
]

cash_flows = [initial_investment] + annual_savings
discount_rate = 0.12  # Acme's hurdle rate (12%)

npv = calculate_npv(discount_rate, cash_flows)
irr = calculate_irr(cash_flows)

# Payback period
cumulative = 0
payback_year = None
for i, cf in enumerate(cash_flows[1:], 1):
    cumulative += cf
    if cumulative >= abs(initial_investment):
        payback_year = i
        break

print(f"Investment Analysis: Packaging Automation Machine")
print(f"{'='*50}")
print(f"Initial Investment:  ${abs(initial_investment):>10,.0f}")
print(f"NPV @ {discount_rate:.0%} discount: ${npv:>10,.0f}")
print(f"IRR:                  {irr:>10.1%}")
print(f"Payback Period:       {payback_year} years")
print()
if npv > 0:
    print(f"RECOMMENDATION: Invest. NPV is positive (${npv:,.0f}).")
    print(f"IRR of {irr:.1%} exceeds hurdle rate of {discount_rate:.0%}.")
else:
    print(f"RECOMMENDATION: Do not invest. NPV is negative.")

Output:

Investment Analysis: Packaging Automation Machine
==================================================
Initial Investment:    $285,000
NPV @ 12% discount:    $59,847
IRR:                      17.3%
Payback Period:          4 years

RECOMMENDATION: Invest. NPV is positive ($59,847).
IRR of 17.3% exceeds hurdle rate of 12%.

A Note on numpy-financial

The numpy-financial library (a separate package from numpy, installed with pip install numpy-financial) provides pre-built NPV and IRR functions:

import numpy_financial as npf

# numpy_financial.npv takes rate and array of cash flows
# Note: npf.npv does not include Year 0 in the discounting,
# so you add it separately
npv_npf = npf.npv(discount_rate, cash_flows[1:]) + cash_flows[0]
irr_npf = npf.irr(cash_flows)

print(f"NPV (numpy-financial): ${npv_npf:,.0f}")
print(f"IRR (numpy-financial): {irr_npf:.1%}")

Understanding the manual implementation helps you verify results and handle edge cases. The library functions are convenient for production use.

29.6 Sensitivity Analysis with Pandas

A single NPV or revenue projection is a point estimate. But every assumption in your model has uncertainty. Sensitivity analysis asks: How much does the output change when an assumption changes?

One-Variable Sensitivity

import pandas as pd
import numpy as np

# How sensitive is NPV to the discount rate?
discount_rates = np.arange(0.06, 0.22, 0.02)

sensitivity_results = []
for rate in discount_rates:
    npv = calculate_npv(rate, cash_flows)
    sensitivity_results.append({
        "Discount Rate": f"{rate:.0%}",
        "NPV": npv,
        "Decision": "Invest" if npv > 0 else "Pass"
    })

sensitivity_df = pd.DataFrame(sensitivity_results)
sensitivity_df["NPV Formatted"] = sensitivity_df["NPV"].map(lambda x: f"${x:>10,.0f}")
print(sensitivity_df[["Discount Rate", "NPV Formatted", "Decision"]].to_string(index=False))

Two-Variable Sensitivity Table

The real power of sensitivity analysis is a two-dimensional table — varying two assumptions simultaneously:

# Revenue model sensitivity: Growth Rate vs Gross Margin
growth_rates_range = [0.04, 0.06, 0.08, 0.10, 0.12, 0.14]
gross_margin_range = [0.54, 0.57, 0.60, 0.63, 0.66]

base_revenue = 4_200_000
fixed_opex = 1_800_000
years = 5

results = {}
for margin in gross_margin_range:
    row = {}
    for growth in growth_rates_range:
        # 5-year cumulative net income (simplified)
        total_ni = 0
        rev = base_revenue
        for _ in range(years):
            rev *= (1 + growth)
            gross_profit = rev * margin
            ni = max(0, (gross_profit - fixed_opex) * 0.73)  # After 27% tax
            total_ni += ni
        row[f"{growth:.0%}"] = total_ni
    results[f"{margin:.0%}"] = row

sensitivity_table = pd.DataFrame(results).T
sensitivity_table.index.name = "Gross Margin"
sensitivity_table.columns.name = "Revenue Growth Rate"

# Format as millions
formatted_table = sensitivity_table.applymap(lambda x: f"${x/1e6:.1f}M")

print("\n5-Year Cumulative Net Income Sensitivity")
print("Gross Margin \\ Revenue Growth Rate")
print(formatted_table.to_string())

This produces a table where you can immediately see which combinations of growth and margin produce acceptable outcomes.

29.7 Scenario Modeling: Base, Bull, and Bear Cases

Sensitivity analysis varies one or two inputs at a time. Scenario modeling is different: you define complete, internally consistent stories about the future.

• Bear case: The pessimistic scenario. Key risks materialize. Lower growth, compressed margins, higher costs.
• Base case: Your best estimate. Reasonably optimistic assumptions supported by data.
• Bull case: The optimistic scenario. Everything breaks your way. Higher growth, expanded margins, operating leverage.

from dataclasses import dataclass

@dataclass
class ScenarioAssumptions:
    name: str
    revenue_growth: list[float]        # Year 1-5 growth rates
    gross_margin: float
    opex_as_pct_revenue: float
    da_annual: float
    interest_expense: float
    tax_rate: float

scenarios = [
    ScenarioAssumptions(
        name="Bear Case",
        revenue_growth=[0.02, 0.03, 0.04, 0.04, 0.03],
        gross_margin=0.56,
        opex_as_pct_revenue=0.42,
        da_annual=85_000,
        interest_expense=35_000,
        tax_rate=0.28,
    ),
    ScenarioAssumptions(
        name="Base Case",
        revenue_growth=[0.06, 0.08, 0.09, 0.08, 0.07],
        gross_margin=0.61,
        opex_as_pct_revenue=0.38,
        da_annual=85_000,
        interest_expense=32_000,
        tax_rate=0.27,
    ),
    ScenarioAssumptions(
        name="Bull Case",
        revenue_growth=[0.10, 0.13, 0.14, 0.12, 0.10],
        gross_margin=0.65,
        opex_as_pct_revenue=0.34,
        da_annual=85_000,
        interest_expense=28_000,
        tax_rate=0.26,
    ),
]

def run_scenario(scenario: ScenarioAssumptions,
                 base_revenue: float) -> dict:
    """Run a 5-year projection for a given scenario."""
    revenues = [base_revenue]
    for rate in scenario.revenue_growth:
        revenues.append(revenues[-1] * (1 + rate))
    revenues = revenues[1:]  # Year 1-5 only

    results = {
        "Scenario": scenario.name,
    }

    year5 = revenues[-1]
    gross_profit_y5 = year5 * scenario.gross_margin
    opex_y5 = year5 * scenario.opex_as_pct_revenue
    ebitda_y5 = gross_profit_y5 - opex_y5
    ebit_y5 = ebitda_y5 - scenario.da_annual
    ebt_y5 = ebit_y5 - scenario.interest_expense
    ni_y5 = max(0, ebt_y5 * (1 - scenario.tax_rate))

    results["Year 5 Revenue"] = year5
    results["Year 5 EBITDA"] = ebitda_y5
    results["Year 5 EBITDA Margin"] = ebitda_y5 / year5
    results["Year 5 Net Income"] = ni_y5
    results["Year 5 Net Margin"] = ni_y5 / year5

    return results

base_revenue = 4_200_000
scenario_results = [run_scenario(s, base_revenue) for s in scenarios]
scenario_df = pd.DataFrame(scenario_results).set_index("Scenario")

print("\nScenario Analysis: 5-Year Outlook")
print(f"{'='*65}")
for col in scenario_df.columns:
    if "Margin" in col:
        row_str = "  ".join(f"{scenario_df.loc[s, col]:>12.1%}"
                            for s in scenario_df.index)
    else:
        row_str = "  ".join(f"${scenario_df.loc[s, col]:>11,.0f}"
                            for s in scenario_df.index)
    print(f"{col:<25} {row_str}")

29.8 Python vs Excel for Financial Modeling: An Honest Assessment

Every financial analyst who learns Python eventually faces the question: should I rebuild my Excel models in Python? The honest answer is nuanced.

Where Python Wins

Scenario automation: Running 500 Monte Carlo simulations or a full two-dimensional sensitivity surface takes seconds in Python. In Excel, this is a weekend project.

Data integration: Python can pull live data from databases, APIs, and accounting systems. Your model can be automatically refreshed every morning.

Reproducibility: A Python model is version-controlled, auditable, and deterministic. When you ask "why did the output change?", you look at what changed in the code.

Scale: A model with 10,000 customers, 50 product lines, or 15 years of monthly projections is manageable in pandas. Excel begins to creak.

Complexity: Multi-entity consolidations, complex waterfall calculations, and iterative models with circular references are far easier to reason about in Python.

Where Excel Wins

Accessibility: Your CFO, your auditors, and your banker can open and read an Excel file. They cannot run a Python script.

Ad hoc exploration: For a quick what-if on a single number, changing a cell in Excel is faster than editing code and re-running.

Presentation: Excel has decades of refinement for formatted financial statement output. Python requires more work to produce board-ready tables.

Existing infrastructure: If the rest of your organization runs on Excel, fighting that is a political battle you may not win.

The Practical Recommendation

Build your models in Python. Present results in Excel. Use openpyxl or xlsxwriter to generate formatted Excel output programmatically. You get the analytical power of Python and the accessibility of Excel.

import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, numbers

# Export income statement to formatted Excel
# (See full implementation in income_statement_model.py)

The best financial modelers in the industry use both tools — Python for the engine, Excel for the dashboard.

29.9 Putting It All Together: A Complete Modeling Workflow

Here is the full workflow for building a financial model in Python:

Step 1: Define assumptions explicitly. Put all assumptions in a single, clearly named section at the top of your code (or in a separate assumptions.py file). Never bury a magic number inside a calculation.

Step 2: Build from top-line to bottom. Revenue → Gross Profit → EBITDA → EBIT → EBT → Net Income → Operating Cash Flow → Free Cash Flow.

Step 3: Validate against known data. Your Year 0 should match actual financials. If your model cannot reproduce history, it cannot project the future.

Step 4: Run scenarios. Always present at least three scenarios. A model with one scenario is a point estimate, not a model.

Step 5: Build sensitivity tables. Identify the two or three assumptions that most drive outcomes. Build a sensitivity table for each.

Step 6: Communicate results clearly. A financial model that only its builder can understand has limited value. Use clear output, formatted tables, and annotated charts.

Chapter Summary

Financial modeling with Python gives you capabilities that are genuinely difficult or impossible in spreadsheets: automated scenario analysis, clean assumption management, sensitivity surfaces, integration with live data, and reproducible results.

The core toolkit you have built in this chapter:

• Income statement modeling: Revenue projections, COGS assumptions, fixed/variable OpEx, EBITDA through net income
• Cash flow modeling: Operating cash flow, working capital dynamics, free cash flow
• Break-even analysis: Contribution margin, break-even units and revenue, break-even chart
• NPV and IRR: Time value of money, capital investment decisions, the hurdle rate concept
• Sensitivity and scenario analysis: One-variable sensitivity, two-variable tables, base/bull/bear scenarios

In the next chapter, we turn to a different kind of data — people data — and explore HR analytics.

Key Terms

COGS (Cost of Goods Sold): Direct costs of producing goods or services sold.

Contribution Margin: Revenue minus variable costs; the amount each unit "contributes" to covering fixed costs and then profit.

Discount Rate: The required rate of return used to calculate present value. In corporate finance, often the weighted average cost of capital (WACC).

EBITDA: Earnings Before Interest, Taxes, Depreciation, and Amortization. A common proxy for operating cash generation.

Free Cash Flow (FCF): Operating cash flow minus capital expenditures. Cash available after sustaining the business.

Hurdle Rate: The minimum acceptable rate of return on an investment, set by management.

IRR (Internal Rate of Return): The discount rate at which an investment's NPV equals zero. Represents the effective annual return.

NPV (Net Present Value): The sum of discounted future cash flows minus initial investment. Positive NPV = value creation.

Sensitivity Analysis: Systematic analysis of how model outputs change when input assumptions change.

Working Capital: Current assets minus current liabilities; specifically A/R + Inventory - A/P in operational modeling.