Stage 2 — Model Specification

What you'll learn

• The design discipline professionals apply before building any model that others must audit
• The actual logic of the forward, money-market, and option hedges — specified in named-range notation
• How to write for an AI reader: ambiguity here becomes a wrong workbook in Stage 3

Why the spec comes before the build

The CFO said "yes, build it." Before you (or your AI) open Excel, Treasury wants a design document it can review — because a model built without a spec is a model nobody else can audit. Your Stage 3 build will follow this document to the letter: every weakness in the spec becomes a defect in the build, which you will then have to find and fix in the audit. The better the spec, the shorter the audit.

And here's the AI-era reason this matters even more than the audit story: in Stage 3 you don't describe the workbook to an AI and hope — the spec is the prompt. A precise spec tells the model exactly what to build and exactly what each result must equal, so the first build comes back close and the audit becomes verification, not archaeology. A vague spec produces the opposite: the AI confidently builds the wrong thing, and you discover your real requirements one frustrating tweak at a time. Every minute you spend making this document unambiguous is a round of corrective back-and-forth you don't pay for in Stage 3.

The eight required sections

The named-range contract

These ten names are the shared vocabulary of your spec, the workbook, the grading script, and every LLM prompt in this project. Use them exactly:

The calculation flow you're specifying

1 · Forward hedge — one line

USD proceeds = FC_AMT × F0_in

Locked. Done. The rest of the model exists to tell you whether locking was the right call.

2 · Money-market hedge — three explicit steps

Step 1  Borrow FC today:      Borrow = FC_AMT / (1 + R_FC × T_DAYS/360)
        (sized so the receivable exactly repays the loan at settlement)
Step 2  Convert at spot:      USD_now = Borrow × S0_in
Step 3  Invest in USD:        Proceeds = USD_now × (1 + R_USD × T_DAYS/360)

Specify each step as its own cell — the audit (and any auditor) wants the pipeline visible, not a single nested formula.

3 · The parity check — your free error detector

Covered interest rate parity says a forward contract and the borrow-convert-invest pipeline are the same trade built two ways, so:

F_implied = S0_in × (1 + R_USD × T/360) / (1 + R_FC × T/360)   ≈   F0_in

Write this into §7 as a validation rule: if MM proceeds and forward proceeds differ by more than rounding, the build has a formula error. This single check will catch half the defects an AI build can produce.

4 · Option hedge (put floor)

Net proceeds at settlement spot S_T:
  = FC_AMT × max(S_T, K_PUT)  −  FC_AMT × PREM_PUT

Below the strike, the put pays the difference (floor holds). Above it, the option expires worthless and you sell at the better market rate — minus the premium you already paid either way. Specify the call's participation payoff the same way.

Conceptual formulas, not cell equations

✗ "C7 = C4/(1+C5*C6/360)"                        ← dies with the workbook
✓ "Compute the FC borrowing amount by dividing
   FC_AMT by (1 + R_FC × T_DAYS/360); this sizes
   the loan so the receivable repays it exactly
   at settlement."                                ← buildable in any tool

The named-range layer is what makes your logic portable between a document, Excel, and an AI prompt. Cell addresses are an implementation detail; the names are the model.

LLM as drafter, you as editor — with evidence

Have an AI draft the spec from your memo + scenario, then correct it. Log prompts in prompt-log.md and show at least one specific iteration: a gap you identified in the draft and how you fixed it (a before/after note or annotated diff). "The AI's first draft omitted the rate basis; I added ACT/360 to §4 and to every rate row in §2" is exactly the evidence the rubric wants.

Submission checklist

• Started from the template-spec.md
• All 8 sections present; 2–3 pages, no filler
• Named-range contract complete: all ten names, placeholder values, units, Stage-4 sources
• Placeholders explicitly flagged "indicative — replaced with live data at Stage 4"
• Every tab named with its purpose (Cover, Legend/Key, Inputs, per-hedge calcs, Sensitivity, Notes)
• Calculation flow in named-range notation — zero cell addresses; MM hedge in three steps
• Sensitivity plan fully specified (±5%, 1% steps, chart purpose stated)
• Validation rules concrete: parity check, no error cells, every output a formula
• prompt-log.md shows at least one specific draft→fix iteration
• Committed to docs/specs/ with the convention-compliant filename

Rubric

Check yourself