Case Study 29-1: Priya Builds the Annual Revenue Model
The Situation
It was the second week of October, and Priya had just received the calendar invite she dreaded every year: "Annual Planning Kickoff — Revenue Modeling Session." The meeting was in twelve days.
At Acme Corp, the annual planning process was historically a three-week exercise in collective anxiety. Seventeen Excel files would be emailed around. People would overwrite each other's changes. Someone would use the wrong growth rate in the wrong cell. Sandra Chen, VP of Finance, would spend three days reconciling conflicting numbers before the board presentation.
This year, Priya had proposed something different. "Let me build the revenue model in Python," she told Sandra. "We run all three scenarios from one script. You change the assumptions, you re-run it, clean output every time. No broken links."
Sandra had looked skeptical. "Can you print it to something the board can read?"
"I'll write it to a formatted Excel file."
Sandra had agreed to a two-week trial.
What Priya Needed to Build
Sandra's requirements were clear: 1. Three scenarios: Bear Case, Base Case, Bull Case 2. Revenue broken out by product line (not just a total) 3. Sensitivity to two key assumptions: unit volume and average selling price 4. Year-over-year growth percentages visible 5. Exportable to Excel for the board deck
Acme Corp had three product lines: - Widget Pro — premium product, $85/unit, higher margin - Widget Lite — volume product, $52/unit, broad distribution - Widget Max — enterprise product, $145/unit, low volume, high touch
The Code Priya Wrote
# annual_revenue_model.py
"""
Acme Corp Annual Revenue Model — FY2025 Planning
Three-scenario revenue projection by product line.
"""
from dataclasses import dataclass
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
# ============================================================
# BASE YEAR ACTUALS (FY2024)
# ============================================================
BASE_YEAR = 2024
ACTUALS = {
"Widget Pro": {"units": 14_200, "avg_price": 85.00},
"Widget Lite": {"units": 31_800, "avg_price": 52.00},
"Widget Max": {"units": 1_420, "avg_price": 145.00},
}
# ============================================================
# SCENARIO DEFINITIONS
# Each scenario provides unit growth and price growth by
# product line for each of the 3 projection years.
# ============================================================
@dataclass
class ProductLineAssumptions:
unit_growth: list[float] # 3 years
price_growth: list[float] # 3 years
@dataclass
class RevenueScenario:
name: str
description: str
assumptions: dict[str, ProductLineAssumptions]
color: str
SCENARIOS = {
"bear": RevenueScenario(
name="Bear Case",
description=(
"Macro softening reduces consumer discretionary spend. "
"Widget Lite volumes decline. Pricing pressure limits increases."
),
assumptions={
"Widget Pro": ProductLineAssumptions(
unit_growth=[0.01, 0.02, 0.02],
price_growth=[0.01, 0.01, 0.01],
),
"Widget Lite": ProductLineAssumptions(
unit_growth=[-0.03, -0.02, 0.01],
price_growth=[0.00, 0.01, 0.01],
),
"Widget Max": ProductLineAssumptions(
unit_growth=[0.03, 0.05, 0.05],
price_growth=[0.02, 0.02, 0.02],
),
},
color="#e74c3c",
),
"base": RevenueScenario(
name="Base Case",
description=(
"Stable market conditions. Widget Pro benefits from new "
"distribution channel. Widget Lite holds share. Widget Max "
"pipeline converts at historical rates."
),
assumptions={
"Widget Pro": ProductLineAssumptions(
unit_growth=[0.07, 0.09, 0.08],
price_growth=[0.02, 0.02, 0.02],
),
"Widget Lite": ProductLineAssumptions(
unit_growth=[0.03, 0.04, 0.04],
price_growth=[0.01, 0.02, 0.02],
),
"Widget Max": ProductLineAssumptions(
unit_growth=[0.08, 0.10, 0.09],
price_growth=[0.03, 0.03, 0.02],
),
},
color="#2ecc71",
),
"bull": RevenueScenario(
name="Bull Case",
description=(
"New enterprise contract materializes in Q1. Widget Pro "
"captures share from a competitor's product recall. "
"International distribution deal for Widget Lite closes."
),
assumptions={
"Widget Pro": ProductLineAssumptions(
unit_growth=[0.12, 0.14, 0.12],
price_growth=[0.03, 0.03, 0.02],
),
"Widget Lite": ProductLineAssumptions(
unit_growth=[0.10, 0.12, 0.10],
price_growth=[0.02, 0.02, 0.02],
),
"Widget Max": ProductLineAssumptions(
unit_growth=[0.15, 0.18, 0.15],
price_growth=[0.04, 0.03, 0.03],
),
},
color="#3498db",
),
}
# ============================================================
# PROJECTION ENGINE
# ============================================================
def project_product_line(
product: str,
scenario: RevenueScenario,
n_years: int = 3,
) -> pd.DataFrame:
"""Project units, price, and revenue for one product line."""
assumptions = scenario.assumptions[product]
actuals = ACTUALS[product]
rows = []
units = actuals["units"]
price = actuals["avg_price"]
for i in range(n_years):
year = BASE_YEAR + i + 1
units = units * (1 + assumptions.unit_growth[i])
price = price * (1 + assumptions.price_growth[i])
revenue = units * price
rows.append({
"Year": year,
"Product": product,
"Units": units,
"Avg Price": price,
"Revenue": revenue,
"Unit Growth": assumptions.unit_growth[i],
"Price Growth": assumptions.price_growth[i],
})
return pd.DataFrame(rows)
def build_revenue_model(scenario: RevenueScenario) -> pd.DataFrame:
"""Build full revenue model for all product lines."""
dfs = [
project_product_line(product, scenario)
for product in ACTUALS.keys()
]
return pd.concat(dfs, ignore_index=True)
# ============================================================
# DISPLAY FUNCTIONS
# ============================================================
def display_revenue_summary(scenario: RevenueScenario) -> None:
"""Print formatted revenue summary for a scenario."""
df = build_revenue_model(scenario)
years = sorted(df["Year"].unique())
products = list(ACTUALS.keys())
print(f"\n{'='*70}")
print(f" {scenario.name.upper()}")
print(f" {scenario.description}")
print(f"{'='*70}")
# Base year actuals header
header = f" {'Product Line':<20}"
header += f" {'FY2024 Act':>12}"
for y in years:
header += f" {str(y):>12}"
print(header)
print(" " + "-" * 60)
total_base = sum(
a["units"] * a["avg_price"] for a in ACTUALS.values()
)
year_totals = {y: 0.0 for y in years}
for product in products:
row = f" {product:<20} ${ACTUALS[product]['units'] * ACTUALS[product]['avg_price']:>10,.0f}"
product_data = df[df["Product"] == product].set_index("Year")
for y in years:
rev = product_data.loc[y, "Revenue"]
year_totals[y] += rev
row += f" ${rev:>10,.0f}"
print(row)
print(" " + "-" * 60)
total_row = f" {'TOTAL':<20} ${total_base:>10,.0f}"
for y in years:
total_row += f" ${year_totals[y]:>10,.0f}"
print(total_row)
# YoY growth row
prev = total_base
growth_row = f" {'YoY Growth':.<20} {'':>12}"
for y in years:
growth = (year_totals[y] - prev) / prev
growth_row += f" {growth:>11.1%} "
prev = year_totals[y]
print(growth_row)
def build_scenario_comparison_table() -> None:
"""Display all three scenarios side by side."""
all_totals = {}
for key, scenario in SCENARIOS.items():
df = build_revenue_model(scenario)
years = sorted(df["Year"].unique())
totals = df.groupby("Year")["Revenue"].sum()
all_totals[scenario.name] = totals
print(f"\n\n{'='*70}")
print(f" SCENARIO COMPARISON — TOTAL REVENUE")
print(f"{'='*70}")
base_actual = sum(a["units"] * a["avg_price"] for a in ACTUALS.values())
header = f" {'':>10}"
for name in all_totals.keys():
header += f" {name:>18}"
print(header)
print(" " + "-" * 65)
print(f" {'FY2024 Actual':>10} "
+ " ".join(f"${base_actual:>17,.0f}" for _ in all_totals))
print()
for year in years:
row = f" {str(year):>10}"
for name, totals in all_totals.items():
row += f" ${totals[year]:>17,.0f}"
print(row)
# 3-year cumulative
print(" " + "-" * 65)
cum_row = f" {'3-Yr Total':>10}"
for name, totals in all_totals.items():
cum_row += f" ${totals.sum():>17,.0f}"
print(cum_row)
# 3-year CAGR
cagr_row = f" {'3-Yr CAGR':>10}"
for name, totals in all_totals.items():
cagr = (totals.iloc[-1] / base_actual) ** (1/3) - 1
cagr_row += f" {cagr:>18.1%}"
print(cagr_row)
# ============================================================
# SENSITIVITY ANALYSIS
# ============================================================
def sensitivity_volume_vs_price() -> None:
"""
Two-way sensitivity: Total Year 3 Revenue by
volume growth modifier and price growth modifier.
"""
print(f"\n\n{'='*70}")
print(f" SENSITIVITY ANALYSIS: Year 3 Total Revenue ($M)")
print(f" Base Case | Volume Growth Multiplier vs Price Growth Multiplier")
print(f"{'='*70}")
vol_multipliers = [0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.3, 1.4]
price_multipliers = [0.6, 0.7, 0.8, 0.9, 1.0, 1.1, 1.2, 1.3]
base_scenario = SCENARIOS["base"]
results = {}
for price_mult in price_multipliers:
row = {}
for vol_mult in vol_multipliers:
total_rev_yr3 = 0
for product, actuals in ACTUALS.items():
assumptions = base_scenario.assumptions[product]
units = actuals["units"]
price = actuals["avg_price"]
for i in range(3):
units *= (1 + assumptions.unit_growth[i] * vol_mult)
price *= (1 + assumptions.price_growth[i] * price_mult)
total_rev_yr3 += units * price
row[f"{vol_mult:.0%} Vol"] = total_rev_yr3 / 1e6
results[f"{price_mult:.0%} Price"] = row
sens_df = pd.DataFrame(results).T
sens_df.index.name = "Price Growth Mult \\ Vol Growth Mult"
# Highlight base case
formatted = sens_df.applymap(lambda x: f"${x:.2f}M")
print(formatted.to_string())
print()
print(" * Base case (1.0x / 1.0x) marked with **")
print(" * Assumptions: All growth rates scaled by multiplier")
# ============================================================
# MAIN
# ============================================================
def main():
print("\n" + "=" * 70)
print(" ACME CORP — FY2025–2027 REVENUE PLANNING MODEL")
print(" Prepared by: Priya | October Planning Cycle")
print("=" * 70)
# Display each scenario
for scenario in SCENARIOS.values():
display_revenue_summary(scenario)
# Side-by-side comparison
build_scenario_comparison_table()
# Sensitivity analysis
sensitivity_volume_vs_price()
print(f"\n\nModel assumptions documented in scenario definitions above.")
print(f"Next step: Connect revenue projections to expense model.")
print(f"Output ready for export to Excel for board presentation.\n")
if __name__ == "__main__":
main()
What the Output Told Sandra
When Priya ran the model and printed the output in Sandra's office, Sandra studied the scenario comparison for a full minute.
"The spread between bear and bull in Year 3 is twenty-three million dollars," she said.
"Twenty-two point eight," Priya confirmed. "That's the range of outcomes we're planning around."
Sandra pointed at the sensitivity table. "If we hit base case volumes but pricing pressure holds us to 70% of our price growth assumptions — what does that do?"
Priya scrolled to the sensitivity output. "Base case volume, 70% price growth: we land at $8.41M in Year 3 revenue versus $8.94M at full base case. About a $530K shortfall."
"Is that enough to flip our EBITDA target?"
"That's the next model," Priya said. "I'll connect this into the expense model this afternoon."
Key Observations
The model took Priya one and a half days to build. The equivalent Excel model had historically taken a team of three people a week — and still had errors found during board review.
Assumptions were explicit and auditable. Every growth rate had a name and a location. When Sandra asked "why does Widget Lite decline in the bear case?", Priya could point directly to the line in the code. No hunting through formula cells.
Scenarios were consistent. In Excel, it was common for scenario cells to get accidentally overwritten. In the Python model, each scenario was a self-contained data structure — the bear case was always the bear case.
The sensitivity table was automatically generated. In the old Excel model, creating this table would have required manually building a data table with 72 cells. In Python, it was a nested loop that ran in under a second.
Sandra's Verdict
After the planning session, Sandra asked Priya to build the expense model the same way. "I still want the Excel output for the board," she said. "But I want to run the sensitivities from your script."
It was not a conversion away from Excel. It was Python doing what it does best — computation, automation, scenario exploration — and Excel doing what it does best: presentation.
The planning process that year took eleven days instead of twenty-one.
What You Can Learn From This
-
Separate assumptions from calculations. Priya's model put every assumption at the top, clearly labeled. This is the single most important practice in financial modeling.
-
Build a data structure for each scenario. Using a
dataclassor dictionary for scenario assumptions prevents the accidental cross-contamination that plagues Excel scenario modeling. -
Automate the sensitivity table. The nested loop approach means you can add rows or columns to a sensitivity analysis with a one-line change.
-
Know your audience. Sandra needed Excel output. Priya delivered it. Python models that produce output nobody can read are not useful business tools.