Chapter 29 Exercises: Financial Modeling with Python

These exercises progress from foundational to advanced across five tiers. Complete them in order within each tier, as later exercises build on earlier ones.

Tier 1: Foundations (Concepts and Basic Calculations)

Exercise 1.1 — The Time Value Sanity Check

Without running any code, answer these questions: - If your discount rate is 10%, what is the present value of $1,000 received in 3 years? - If your discount rate is 10%, what is the present value of $1,000 received in 10 years? - Why does the present value decrease as the time horizon increases?

Then verify your answers in Python:

def present_value(future_value, rate, years):
    # Your implementation here
    pass

Exercise 1.2 — Manual Income Statement

Given the following annual data for a small business, build a complete income statement in Python: - Revenue: $520,000 - Gross Margin: 58% - Fixed Operating Expenses: $185,000 - Variable Operating Expenses: 12% of revenue - Depreciation & Amortization: $22,000 - Interest Expense: $8,500 - Tax Rate: 26%

Print each line item clearly formatted. What is the net income? What is the EBITDA margin?

Exercise 1.3 — Break-Even Basics

A food truck sells gourmet sandwiches for $14.00 each. The ingredients cost $5.20 per sandwich. The truck's monthly fixed costs (lease, insurance, permits) are $3,800.

Calculate: 1. Contribution margin per sandwich 2. Contribution margin ratio 3. Monthly break-even in units 4. Monthly break-even in revenue 5. How many sandwiches must be sold to earn $2,000 monthly profit?

Exercise 1.4 — NPV Decision

A small manufacturer is considering a $50,000 CNC machine that will save $14,000/year in labor costs for 5 years, then has a salvage value of $5,000. Their hurdle rate is 10%.

• Build the cash flow list
• Calculate NPV manually (use your present_value function)
• Should they buy the machine?

Tier 2: Core Skills (Building the Models)

Exercise 2.1 — 3-Year Revenue Projection

A software company has $2.4M in annual recurring revenue. They expect: - New customer growth: 18%, 15%, 12% in Years 1-3 - Average Revenue Per User (ARPU) growth: 3%, 3%, 4% per year - Annual churn rate: 8% of beginning-of-year revenue

Build a Python model that projects revenue for 3 years, accounting for: - New revenue (growth on retained base) - Churn (revenue lost) - ARPU expansion on retained customers

Display the results in a formatted DataFrame.

Exercise 2.2 — Operating Cash Flow Model

Extend the income statement from Exercise 1.2 to calculate operating cash flow. Assume: - DSO (Days Sales Outstanding): 35 days - DIO (Days Inventory Outstanding): 20 days (assume COGS = 42% of revenue) - DPO (Days Payable Outstanding): 28 days

Calculate: 1. Working capital required 2. Operating cash flow = Net Income + D&A - Change in Working Capital 3. Free Cash Flow (assume CapEx of $35,000)

Exercise 2.3 — Break-Even Chart

Using the food truck from Exercise 1.3, build a break-even chart using matplotlib that shows: - Total Revenue line - Total Cost line - Fixed Cost horizontal line - Shaded profit zone (green) and loss zone (red) - Annotated break-even point - Current volume marker (assume 425 sandwiches/month)

Label all axes, include a title, and save the figure.

Exercise 2.4 — Multi-Year Break-Even

The food truck owner is considering expanding to a second location. Additional fixed costs: $2,800/month. All variable costs and prices stay the same.

Build a model that shows: - Single location break-even - Two-location break-even (combined) - At what total volume does the second location start to be profitable on its own?

Exercise 2.5 — IRR Calculation

A rental property costs $180,000. It generates the following net cash flows: - Year 1-5: $18,000/year - Year 5: Sale of property for $220,000 (add to Year 5 cash flow)

Calculate: 1. NPV at discount rates of 8%, 10%, 12%, 14% 2. IRR using your Newton-Raphson implementation 3. Payback period 4. Is this a good investment if the investor's required return is 10%?

Tier 3: Applied Skills (Business Scenarios)

Exercise 3.1 — Scenario Model: Subscription Business

A SaaS startup has 850 paid subscribers at $49/month. Their monthly fixed costs are $28,000. Variable costs are $8/subscriber/month.

Build a 2-year monthly projection model with three scenarios:

For each scenario, show: - Monthly subscriber count - Monthly revenue - Monthly profit/loss - Month in which each scenario reaches profitability (if applicable)

Exercise 3.2 — Equipment vs. Lease NPV

A bakery needs a commercial mixer. They can: - Buy: $12,500 upfront, saves $5,200/year in contract baking fees, 7-year life, $800 salvage value - Lease: $280/month ($3,360/year), no upfront cost, same 7-year term, no salvage

Using a discount rate of 9%, calculate the NPV of each option and recommend one. Show your complete cash flow table for both options.

Exercise 3.3 — Sensitivity Table: Margin vs Growth

Build a two-variable sensitivity table showing 5-year cumulative net income for a company with: - Base revenue: $1,800,000 - Fixed costs: $620,000/year (growing at inflation) - Tax rate: 25%

Vary: - Gross margin from 45% to 65% (5% increments) - Annual revenue growth from 3% to 15% (3% increments)

Format the table clearly. Mark the base case (55% margin, 8% growth) with an asterisk.

Exercise 3.4 — Working Capital Impact Analysis

A product company ($3M annual revenue, 40% COGS) is considering two changes: 1. Offering net-60 payment terms instead of net-30 to win a large customer 2. Building inventory ahead of their busy season (adding 45 days of inventory)

Model the working capital impact of each change separately, then both together: - How much additional cash is required for each change? - What is the annualized cost of this cash at a 12% cost of capital? - Is the large customer deal still worth it if their annual revenue is $180,000?

Tier 4: Integration (Building Production-Ready Models)

Exercise 4.1 — Full 5-Year Financial Model

Build a complete, production-ready 5-year financial projection model for a fictional company of your choosing. Requirements:

1. Revenue model: Use the volume x price approach, at least two product lines
2. Cost model: Separate fixed and variable operating expenses
3. Full income statement: All lines from Revenue to Net Income
4. Cash flow: Operating cash flow and free cash flow
5. Three scenarios: Bear, Base, Bull (each with documented rationale)
6. Sensitivity table: Two key assumptions
7. Clean output: Formatted for executive review (all values in dollars with commas)
8. Summary statistics: 5-year CAGR, cumulative net income, average EBITDA margin

All assumptions must be named constants at the top of the file. No magic numbers in calculations.

Exercise 4.2 — Capital Allocation Ranking

A company has $500,000 to invest. It has five capital projects to choose from:

Discount rate: 11%. Budget constraint: $500,000.

1. Calculate NPV, IRR, Payback, and Profitability Index for each project
2. Rank projects by Profitability Index
3. Which combination of projects maximizes total NPV without exceeding budget?
4. Should the company fund Project B even if it means skipping two smaller positive-NPV projects?

Exercise 4.3 — Monte Carlo Revenue Simulation

Instead of three discrete scenarios, build a Monte Carlo simulation for annual revenue: - Base revenue: $2,000,000 - Annual growth rate: normally distributed, mean=8%, std=5% - Gross margin: normally distributed, mean=60%, std=3% - Fixed costs: $700,000/year (deterministic)

Run 10,000 simulations for Year 5 net income. Plot the distribution and report: - Mean Year 5 net income - 10th percentile (pessimistic) - 90th percentile (optimistic) - Probability that Year 5 net income exceeds $500,000

Tier 5: Mastery (Open-Ended Projects)

Exercise 5.1 — Real Company DCF

Choose a publicly traded company. Download their last 3 years of free cash flow from their SEC filings or a financial data source.

Build a 5-year DCF (Discounted Cash Flow) valuation model: 1. Project free cash flow for 5 years using a reasonable growth assumption 2. Calculate terminal value using the Gordon Growth Model 3. Discount all cash flows at the company's estimated WACC (research or estimate) 4. Divide by shares outstanding to get intrinsic value per share 5. Compare to the current stock price 6. Document your assumptions and their sensitivity

Exercise 5.2 — LBO Model (Simplified)

A private equity firm is considering buying a company for 6x EBITDA. The company has: - Revenue: $8M, growing 7%/year - EBITDA margin: 22% - The acquisition is financed 60% debt at 7.5% interest, 40% equity

Build a simplified 5-year LBO model showing: 1. Annual income statement (accounting for interest on acquisition debt) 2. Debt paydown schedule (assume 30% of FCF goes to debt service) 3. Exit at Year 5 at 6.5x EBITDA 4. Return to equity investors (cash-on-cash multiple and IRR) 5. What EBITDA margin is needed for the equity investors to earn a 20% IRR?

Exercise 5.3 — Build a Financial Modeling Library

Build a reusable Python module financial_toolkit.py with the following functions, fully tested with docstrings: - npv(rate, cash_flows) — with error handling - irr(cash_flows) — with Newton-Raphson and bisection fallback - payback_period(cash_flows) — simple and discounted - profitability_index(rate, cash_flows) - wacc(equity_value, debt_value, cost_equity, cost_debt, tax_rate) - gordon_growth_model(fcf, growth_rate, discount_rate) - contribution_margin(price, variable_cost, units) - breakeven_units(fixed_costs, price, variable_cost) - project_revenue(base_revenue, growth_rates) — handles list of rates

Write unit tests for each function using Python's unittest module.