Stage 1 — The Ratios Template

Weight: 20% Deliverable: unmodified template committed to models/templates/

Your instructor provides the Excel model everyone builds on. Stage 1 isn't about modifying it — it's about understanding its architecture, because Stages 3, 4, and 5 all speak this template's language.

What you'll learn

How a professional financial model separates inputs, assumptions, formulas, and outputs
What named ranges are and why they make a model portable and specifiable
Why EMBA sections get the template while undergrads build from scratch (hint: it protects time for the higher-leverage AI work)

The architecture

Tab	What it holds
Cover & Instructions	Project overview, how to use the template, the named-range key — and where you'll document source URL, reporting standard, currency, and fiscal year end at Stage 3
Legend	Color code: yellow = inputs, blue = assumptions, green = formulas, gray = outputs
Income Statement	Skeleton with `INC_*` named-range placeholders
Balance Sheet	Current + prior year columns — `BAL_` and `startYear_`
Cash Flow	Operating / investing / financing — `CASH_*`
Ratios	All six categories, fully formulaic in named-range notation (`RATIO_*`) — zero hardcoded numbers. It auto-populates the moment Stage 3 data lands

Named ranges — the big idea

A named range assigns a label to a cell, so formulas read like finance instead of like coordinates:

❌ cell-address style:   = B12 / D14
✓ named-range style:    = INC_net_income_2025 / BAL_assets_total_2025

Three consequences, and they carry the whole project:

Readable — anyone (including an AI) can audit the formula without opening the workbook.
Portable — formulas survive when the workbook is copied, rows shift, or a new company's data is loaded.
Specifiable — at Stage 4 you'll write a technical spec that defines every ratio in named-range notation. An LLM that has never seen your Excel file can then compute or verify everything. That's only possible because names, not addresses, are the interface.

The startYear_* convention

Ratios with a balance-sheet denominator (ROA, asset turnover, inventory turnover) use the average of the start-of-year and end-of-year values. The prior-year column populates the startYear_* named ranges. Miss it at Stage 3 and these ratios fail silently — it's the single most common mistake in the project.

Want to feel how the formulas behave? The Ratio Lab mirrors this template's named ranges and averaging conventions — try the sample company.

What to do

Download the template from the course repo: docs/templates/spreadsheets/performance-ratios-template.xlsx
Open it and click through every tab. Read the Cover tab. Skim the Ratios tab and notice every formula is a sentence in named ranges.
Commit an unmodified copy to models/templates/ in your repo.
Complete the directory skeleton (if Stage 0 left gaps) — README in every folder.

Do not modify the template yet

If you spot something you'd change — write it down. That observation becomes raw material for your Stage 4 spec, where improvements belong.

Set up this project's folders

Stage 0 stood up your portfolio repo. This project adds five folders to that skeleton, and every stage from here commits into one of them. Build them now, each with a one-line README.md inside — Git does not track an empty folder, so the README is what makes the folder exist — and no later stage stalls on housekeeping.

firstname-lastname/
  docs/
    decisions/         the company-selection memo
    specs/             add this — the model specification
  models/              add this whole branch
    templates/         the instructor's template, unmodified
    builds/            your populated workbook
  data/                the filings you worked from, with provenance
  analysis/
    validation/        add this — the self-audit and verification reports
  deliverables/        add this — the final outputs and the prompt log

Stage	Deliverable	Where it goes
1	Unmodified template	`models/templates/performance-ratios-template.xlsx`
2	Company-selection memo	`docs/decisions/YYYY-MM-DD-{lastname}-{company-slug}-selection.md`
3	Populated workbook	`models/builds/YYYY-MM-DD-{lastname}-{company-slug}-financials.xlsx`
4	Model spec + revision evidence	`docs/specs/YYYY-MM-DD-{lastname}-{company-slug}-spec.md` · `analysis/validation/`
5	Verification + final analysis	`analysis/validation/…-stage5-verification.md` · `deliverables/…-final-analysis.md`

Every graded file carries the same prefix — YYYY-MM-DD-{lastname}-{company-slug}, a real date, your last name, your company slug, all lowercase. The repo is named for you; individual files carry the date and company, so a folder full of them stays legible. Your running AI record lives at deliverables/prompt-log.md and is appended to, not replaced. Each stage page restates its own path, so you never have to come back here — this is the whole map in one place.

Submission checklist

models/templates/performance-ratios-template.xlsx — unmodified, right directory, right filename
Opened every tab and read the Cover & Instructions tab
Directory skeleton complete with a meaningful README.md in each directory
At least 2 new commits since Stage 0 with descriptive messages
Project folders created with a stub README.md in each

Rubric

Criterion	%	Strong work looks like
Template uploaded correctly	30%	Unmodified, correct directory and filename
Directory structure	40%	All required directories; README in each; logical organization
README quality	20%	Explain purpose and conventions — not placeholders
Commit hygiene	10%	Meaningful messages; clean history