What you will learn
- Absolute and relative references
- Unit-aware formulas
- Lookup tables
- Error handling
Absolute and relative references
Relative references change as formulas are copied; absolute references keep a specified cell fixed. A risk-budget formula that accidentally shifts to another row can produce plausible but incorrect quantities. Inspect copied formulas at the beginning and end of a range.
Unit-aware formulas
Write units beside inputs and intermediate results. A multiplier table should distinguish price units, contract quantity and account currency. Consistent units make it easier to spot an inverse conversion or missing contract multiplier.
Lookup tables
Lookup tables centralize instrument specifications and fee assumptions. Use exact keys and explicit missing-key handling. An approximate match can silently assign a neighboring instrument's multiplier, which is unacceptable for exposure calculations.
Error handling
Error handling should reveal invalid inputs rather than hide them. Replacing every spreadsheet error with zero can turn missing data into an apparently safe trade. Show the reason and suppress dependent conclusions until the input is corrected.
Worked example
A copied formula references B2 for the budget, then B3, which contains a different value. If the budget was intended to be fixed, an absolute reference or named input is required; both outputs could otherwise look reasonable.
Try it yourself
Explain how to test a copied sizing formula and an instrument lookup using one normal row, one unknown symbol and one missing stop distance.
Show the worked solution
The normal row should match an independent calculation. The unknown symbol should produce a specific missing-specification error; the missing distance should block sizing. Neither invalid case should become a zero-risk result.
Apply this to your course project
Import a sample CSV, reconcile totals and publish a documented performance worksheet.
Keep the calculation inputs, assumptions and decisions with your work. Practical exercises are self-reviewed; the scored knowledge checks assess the questions shown, not an independent certification of practical competence.