# Excel Modelling Drill Book: [Your name and start date]

> A graded set of spreadsheet drills for admitted and prospective MBA students who want to arrive on day one able to
> build, check and explain a small model without fighting the software. It covers the skills a first-year core course
> assumes: relative, absolute and mixed references, lookups with XLOOKUP and INDEX-MATCH, conditional sums with SUMIFS,
> pivot tables, one- and two-way data tables for sensitivity, Goal Seek and Solver, charts, a small three-statement model
> and the modelling conventions (inputs, calculations, outputs, colour coding, checks) that make a model readable by
> someone else. Every drill gives the exact formula and the expected result, so you can grade yourself.
> Source basis: Microsoft Excel function documentation, widely used financial-modelling conventions and the
> linear-programming formulation behind Solver (Dantzig 1947 onward), all explained in original words. The data are
> fictional (Northfield Outdoor Co.). Replace every [bracketed] value and every row marked *Example* when you reuse the
> sheets for your own practice.

<!--
Guidance: How to use this drill book
- Build every drill yourself in a blank workbook. Do not copy the expected result into the cell: type the formula,
  then compare your result with the "Expected" column. A match to the cent is the pass mark.
- Work in level order: Level 1 (foundation) drills D1 to D3, Level 2 (core) drills D4 to D7, Level 3 (stretch)
  drills D8 and D9. Time each drill and record the time in the progress log; repeat the slowest drills after two days
  and again after a week (spaced practice).
- Use Microsoft 365 or Excel 2021 or later for XLOOKUP. If you only have an older version, use the INDEX-MATCH
  alternative given in every lookup drill. Google Sheets and LibreOffice Calc support most formulas here; Data Tables
  and Solver differ by product, so check your software's help.
- Related templates: mba/pre-mba-accounting-primer, mba/pre-mba-finance-primer, mba/pre-mba-quant-refresher,
  mba/pre-mba-roadmap-12-weeks, mba/mba-three-statements-card, mba/mba-npv-irr-card, mba/mba-cvp-breakeven-card,
  mba/mba-formula-sheet, finance/financial-model-specification, finance/unit-economics-analysis,
  academic/stats-probability-basics.
-->

## Document control

<!-- Guidance: Record your software version and the date you started, so that you can tell later whether a formula failed because of the version or because of you. -->

| Field | Value |
|---|---|
| Learner | [Your name] |
| Programme and start date | [School A, full-time MBA, YYYY-MM-DD] |
| Software and version | [Microsoft 365 Excel, version 2609 / Excel 2021 / Google Sheets] |
| Operating system and locale | [Windows 11, English (US): comma as argument separator] |
| Workbook file | [pre-mba-drills-v1.xlsx] |
| Started | YYYY-MM-DD |
| Reviewed by | [Mentor, second-year student or study partner] |
| Version | [0.1] |

## How the drills are graded

<!-- Guidance: Read the grading rule once and then hold yourself to it. The point is speed with accuracy, not speed alone. A fast wrong model is worse than a slow right one. -->

Each drill has a level, a target time and a pass rule. The target times are what a confident first-year student
manages after practice; they are guides, not tests.

| Level | Drills | Target time per drill | Pass rule |
|---|---|---|---|
| 1 Foundation | D1 references, D2 lookups, D3 conditional sums | 8 to 12 minutes | Every expected value matched, formulas copied down or across without manual edits |
| 2 Core | D4 pivot tables, D5 data tables, D6 Goal Seek and Solver, D7 charts | 12 to 20 minutes | Values matched, sensitivity or optimum explained in one sentence |
| 3 Stretch | D8 three-statement model, D9 conventions and checks | 45 to 75 minutes | Balance check equals zero in every year, all checks pass, a reviewer can follow the model without asking you |

Two rules apply to every drill. First, **no hard-coded numbers inside formulas** except true constants (12 months, 100
per cent); every assumption sits in its own labelled input cell. Second, **one formula per row or column**: a row of a
forecast uses the same formula in every year, so you can copy it across and audit it with one glance.

A note on locale: some European locales use a semicolon as the argument separator and a comma as the decimal sign.
If `=SUMIFS(G2:G13,C2:C13,"North")` returns an error, try `=SUMIFS(G2:G13;C2:C13;"North")`.

## The practice data set

<!-- Guidance: Type the two tables into a new workbook exactly as shown, on sheets named Prices and Sales, plus an Inputs sheet with the discount rate. Every drill uses these cell addresses. -->

Northfield Outdoor Co. (fictional) sells tents, packs and apparel through three regions. You have a price list and a
first-quarter sales extract.

**Sheet `Prices`, range A1:C7**

| Row | A: SKU | B: Category | C: Price (USD) |
|---|---|---|---|
| 2 | TN-2P | Tents | 249 |
| 3 | TN-4P | Tents | 389 |
| 4 | PK-30 | Packs | 119 |
| 5 | PK-55 | Packs | 179 |
| 6 | JK-RN | Apparel | 139 |
| 7 | JK-DN | Apparel | 219 |

**Sheet `Sales`, range A1:E13** (columns F to H are added in the drills)

| Row | A: Order | B: Month | C: Region | D: SKU | E: Units |
|---|---|---|---|---|---|
| 2 | SO-001 | Jan | North | TN-2P | 14 |
| 3 | SO-002 | Jan | South | PK-30 | 22 |
| 4 | SO-003 | Jan | West | JK-RN | 30 |
| 5 | SO-004 | Jan | North | TN-4P | 6 |
| 6 | SO-005 | Feb | South | PK-55 | 12 |
| 7 | SO-006 | Feb | West | TN-2P | 18 |
| 8 | SO-007 | Feb | North | JK-DN | 9 |
| 9 | SO-008 | Feb | South | TN-4P | 8 |
| 10 | SO-009 | Mar | West | PK-30 | 25 |
| 11 | SO-010 | Mar | North | JK-RN | 16 |
| 12 | SO-011 | Mar | South | TN-2P | 20 |
| 13 | SO-012 | Mar | West | PK-55 | 10 |

**Sheet `Inputs`:** cell A2 holds the label "Promotional discount", cell B2 holds 5%.

## D1 (Level 1): relative, absolute and mixed references

<!-- Guidance: The single most common spreadsheet error is a reference that moves when it should not. Learn to press F4 (Windows) or Cmd+T (Mac) to cycle through A1, $A$1, A$1 and $A1, and say aloud which part must stay fixed before you copy. -->

**Idea.** A reference such as `F2` is relative: copied one row down it becomes `F3`. A dollar sign fixes the part that
follows it. `$B$2` never moves; `B$4` keeps the row and lets the column move; `$A5` keeps the column and lets the row
move. Absolute references point at inputs; mixed references build grids from one formula.

**Task 1.1.** On `Sales`, add column F "Price" (for now type the prices by hand, D2 replaces this), column G "Revenue"
and column H "Net revenue" after the promotional discount.

| Cell | Formula | Copied to | Expected |
|---|---|---|---|
| `G2` | `=E2*F2` | G3:G13 | 3,486 |
| `H2` | `=G2*(1-Inputs!$B$2)` | H3:H13 | 3,311.70 |
| `G14` | `=SUM(G2:G13)` | | 36,290 |
| `H14` | `=SUM(H2:H13)` | | 34,475.50 |
| `E14` | `=SUM(E2:E13)` | | 190 |

Check: change `Inputs!B2` to 0%. H14 must become 36,290. If only H2 changes, you forgot the dollar signs.

**Task 1.2.** Build a price-escalation grid with one formula. Put base prices 249, 389 and 119 in A5:A7 and growth
rates 3%, 5% and 8% in B4:D4. In B5 type `=$A5*(1+B$4)` and copy it to B5:D7.

| Base price | 3% | 5% | 8% |
|---|---|---|---|
| 249 | 256.47 | 261.45 | 268.92 |
| 389 | 400.67 | 408.45 | 420.12 |
| 119 | 122.57 | 124.95 | 128.52 |

Why it works: `$A5` always reads column A (the price) while the row moves, and `B$4` always reads row 4 (the growth
rate) while the column moves. If you get 256.47 everywhere in column B but nonsense elsewhere, one of the two dollar
signs is missing.

**Self-check question.** You copy `=G2/$G$14` from I2 down to I13 to get each order's share of revenue. What should
I14 (the sum of I2:I13) show, and what would it show if you had typed `=G2/G14`?

## D2 (Level 1): lookups with XLOOKUP and INDEX-MATCH

<!-- Guidance: Never type a value that already exists elsewhere in the workbook. Look it up. Exact match is the default you want for codes; approximate match is for tiers and bands, and needs sorted data. -->

**Idea.** A lookup finds a key in one column and returns the value from the same position in another column. XLOOKUP
(Microsoft 365, Excel 2021 and later) does this in one function and defaults to exact match. INDEX-MATCH does the same
in every version: MATCH finds the position, INDEX returns the value at that position. Avoid VLOOKUP with a hard-coded
column number in models: inserting a column silently breaks it.

The XLOOKUP syntax is `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`.
Match mode 0 is exact (default), -1 is exact or next smaller, 1 is exact or next larger, 2 is wildcard.

**Task 2.1.** Replace the typed prices in `Sales!F2:F13` with a lookup.

| Cell | Formula | Expected |
|---|---|---|
| `F2` (XLOOKUP) | `=XLOOKUP(D2,Prices!$A$2:$A$7,Prices!$C$2:$C$7,"Not found")` | 249 |
| `F6` after copying | (same formula, row 6) | 179 |
| `F2` (INDEX-MATCH alternative) | `=INDEX(Prices!$C$2:$C$7,MATCH(D2,Prices!$A$2:$A$7,0))` | 249 |
| Test cell, key "TN-6P" | `=XLOOKUP("TN-6P",Prices!$A$2:$A$7,Prices!$C$2:$C$7,"Not found")` | Not found |
| Test cell, key "JK-DN" | `=INDEX(Prices!$C$2:$C$7,MATCH("JK-DN",Prices!$A$2:$A$7,0))` | 219 |

After the lookup, G14 must still be 36,290. Add column I "Category" with
`=XLOOKUP(D2,Prices!$A$2:$A$7,Prices!$B$2:$B$7,"Not found")`; D3 and D4 need it.

**Task 2.2 (approximate match).** Northfield gives a volume discount by order size. Put thresholds 0, 10, 20, 30 in
`Inputs!A6:A9` (sorted ascending) and rates 0%, 2%, 4%, 6% in `Inputs!B6:B9`. Return the rate for each order with
`=XLOOKUP(E2,Inputs!$A$6:$A$9,Inputs!$B$6:$B$9,0,-1)` or `=INDEX(Inputs!$B$6:$B$9,MATCH(E2,Inputs!$A$6:$A$9,1))`.

| Units | Expected rate | Why |
|---|---|---|
| 6 | 0% | 6 is below the first break at 10 |
| 14 | 2% | Largest threshold not above 14 is 10 |
| 22 | 4% | Largest threshold not above 22 is 20 |
| 30 | 6% | Exact match on 30 |

MATCH with match type 1 requires the thresholds sorted ascending; unsorted data give wrong answers with no error.

## D3 (Level 1): conditional sums, counts and averages

<!-- Guidance: SUMIFS takes the sum range first and then pairs of criteria range and criterion. Put the criteria in cells, not inside the formula, so that one formula serves a whole summary table. -->

**Idea.** `SUMIFS(sum_range, criteria_range1, criterion1, ...)` adds the values that meet all criteria. COUNTIFS
counts rows and AVERAGEIFS averages them. Criteria can be text ("North"), a cell (`$K2`) or a comparison in quotes
(">=15", or `">="&L1` to read the threshold from a cell).

**Task 3.1.** Build a summary on a new sheet `Summary`, regions in A2:A4.

| Measure | Formula (in B2, copied down) | Expected |
|---|---|---|
| Revenue, North | `=SUMIFS(Sales!$G$2:$G$13,Sales!$C$2:$C$13,$A2)` | 10,015 |
| Revenue, South | (same, row 3) | 12,858 |
| Revenue, West | (same, row 4) | 13,417 |
| Total check | `=SUM(B2:B4)-Sales!G14` | 0 |

**Task 3.2.** Answer each with one formula.

| Question | Formula | Expected |
|---|---|---|
| Revenue, South in February | `=SUMIFS(Sales!$G$2:$G$13,Sales!$C$2:$C$13,"South",Sales!$B$2:$B$13,"Feb")` | 5,260 |
| Revenue, Tents | `=SUMIFS(Sales!$G$2:$G$13,Sales!$I$2:$I$13,"Tents")` | 18,394 |
| Orders of 15 units or more | `=COUNTIFS(Sales!$E$2:$E$13,">=15")` | 6 |
| Revenue from those orders | `=SUMIFS(Sales!$G$2:$G$13,Sales!$E$2:$E$13,">=15")` | 21,449 |
| Average units per Packs order | `=AVERAGEIFS(Sales!$E$2:$E$13,Sales!$I$2:$I$13,"Packs")` | 17.25 |

**Self-check question.** Why is a "Total check" row (summary total minus source total) worth adding to every summary
table you build?

## D4 (Level 2): pivot tables

<!-- Guidance: A pivot table is the fastest way to explore; SUMIFS is the better way to feed a model, because it recalculates automatically and does not change shape. Use both, and reconcile them. -->

**Idea.** A pivot table summarises a list by the fields you drag into Rows, Columns and Values. It needs a clean list:
one header row, no blank rows or merged cells, one record per row. Convert the list to an Excel Table first
(Ctrl+T) so new rows are picked up when you refresh.

**Task 4.1.** Select `Sales!A1:I13`, Insert, PivotTable. Rows: Region. Columns: Category. Values: Sum of Revenue.

| Region | Apparel | Packs | Tents | Grand total |
|---|---|---|---|---|
| North | 4,195 | | 5,820 | 10,015 |
| South | | 4,766 | 8,092 | 12,858 |
| West | 4,170 | 4,765 | 4,482 | 13,417 |
| Grand total | 8,365 | 9,531 | 18,394 | 36,290 |

Blank cells mean no orders, not zero sales; in a report, decide whether to show them as 0 (PivotTable Options, "For
empty cells show").

**Task 4.2.** Change Values to "Show Values As, % of Grand Total". Expected category shares of the grand total:
Apparel 23.05%, Packs 26.26%, Tents 50.69%.

**Task 4.3.** Move Category out and Month into Rows. Expected: Jan 12,608, Feb 11,713, Mar 11,969. Excel normally
lists Jan, Feb, Mar in calendar order because month abbreviations are a built-in custom list and "Use Custom Lists when
sorting" is on by default (PivotTable Options, Totals and Filters). If that option is off, or in other software, text
months sort alphabetically (Feb, Jan, Mar); the order a report needs is chronological, so check it.

**Task 4.4.** Reconcile: the pivot grand total, `Summary` total and `Sales!G14` must all equal 36,290. Retrieve one
pivot value into a model cell with `=GETPIVOTDATA("Revenue",$A$3,"Region","West")`, expected 13,417 (the anchor
cell `$A$3` depends on where your pivot starts).

## D5 (Level 2): data tables for sensitivity

<!-- Guidance: Sensitivity analysis answers "what drives the result?". A data table recalculates the model for each input value. Keep the model on the same sheet as the table (a data table's input cells must sit on the table's sheet). -->

**Idea.** A one-way data table varies one input; a two-way data table varies two and shows one output. Excel builds it
under Data, What-If Analysis, Data Table, and fills the body with the array formula `{=TABLE(row_input, column_input)}`
that you cannot edit cell by cell. Set Calculation Options to "Automatic except for data tables" in large models, and
press F9 to refresh.

**Set-up: a tent launch model** on sheet `Launch` (*Example*).

| Cell | Label | Value or formula |
|---|---|---|
| `B2` | Price per tent (USD) | 299 |
| `B3` | Units sold | 2,400 |
| `B4` | Variable cost per tent | 165 |
| `B5` | Fixed launch costs | 240,000 |
| `B7` | Profit | `=(B2-B4)*B3-B5` gives 81,600 |

**Task 5.1.** Build a two-way table: in D10 type `=B7`; prices 269, 289, 299, 309, 329 in D11:D15; units 1,800 to
3,000 in E10:I10. Select D10:I15, Data Table, Row input cell `B3`, Column input cell `B2`.

| Price \ Units | 1,800 | 2,100 | 2,400 | 2,700 | 3,000 |
|---|---|---|---|---|---|
| 269 | -52,800 | -21,600 | 9,600 | 40,800 | 72,000 |
| 289 | -16,800 | 20,400 | 57,600 | 94,800 | 132,000 |
| 299 | 1,200 | 41,400 | 81,600 | 121,800 | 162,000 |
| 309 | 19,200 | 62,400 | 105,600 | 148,800 | 192,000 |
| 329 | 55,200 | 104,400 | 153,600 | 202,800 | 252,000 |

Read it: at the base case (299, 2,400) profit is 81,600. A 10-dollar price cut to 289 costs 24,000 (2,400 units times
10), the same as losing about 179 units at the base margin of 134. The table makes that trade-off visible without
algebra. Apply conditional formatting (a three-colour scale) so the loss region stands out.

**Common error.** Swapping the row and column input cells produces a table that looks plausible and is wrong. Test one
corner by hand: at 269 and 1,800, $(269-165)\times 1{,}800-240{,}000=-52{,}800$.

## D6 (Level 2): Goal Seek and Solver

<!-- Guidance: Goal Seek solves one equation for one unknown. Solver optimises an objective subject to constraints. Write the problem in words (objective, decisions, constraints) before you open either dialog. -->

**Goal Seek idea.** Data, What-If Analysis, Goal Seek: set cell, to value, by changing cell. It iterates; it does not
give a formula, so record the answer as a value and, where possible, confirm it with algebra.

**Task 6.1.** Break-even units: Set cell `B7` to 0 by changing `B3`. Expected 1,791.04 units. Algebra:
$Q^{*}=F/(p-v)=240{,}000/(299-165)=1{,}791.04$; in a cell, `=B5/(B2-B4)`. Round up to 1,792 tents for a real plan.

**Task 6.2.** Target price: with units at 2,400, set `B7` to 120,000 by changing `B2`. Expected 315.00. Check:
`=(120000+B5)/B3+B4` gives 315.

**Solver idea.** Solver (an add-in: File, Options, Add-ins, Excel Add-ins, Solver) chooses decision cells to maximise or
minimise an objective cell subject to constraints. For a linear problem pick "Simplex LP"; it is exact and fast and
gives a sensitivity report. Linear programming goes back to Dantzig's simplex method (1947), and Solver's linear
engine applies the same family of methods.

**Task 6.3 (*Example* product mix).** Northfield's sewing workshop makes tents (contribution 134 each) and packs (62
each) for the season.

| Resource | Per tent | Per pack | Available |
|---|---|---|---|
| Sewing hours | 1.5 | 0.8 | 3,660 |
| Fabric (square metres) | 6 | 2 | 13,200 |
| Pack demand cap | | 1 | 2,500 |

Set-up: decision cells `C3` (tents) and `C4` (packs); objective `=134*C3+62*C4` in `C6`; constraint cells
`=1.5*C3+0.8*C4` and `=6*C3+2*C4`; Solver: Max `C6`, by changing `C3:C4`, subject to sewing used `<=` 3,660, fabric
used `<=` 13,200, `C4 <= 2500`, non-negative variables, Simplex LP.

| Output | Expected |
|---|---|
| Tents | 1,800 |
| Packs | 1,200 |
| Contribution | 315,600 |
| Sewing slack | 0 (binding) |
| Fabric slack | 0 (binding) |
| Pack demand slack | 1,300 |
| Shadow price, one more sewing hour | 57.78 |
| Shadow price, one more square metre of fabric | 7.89 |

The shadow prices (Solver's Sensitivity Report, "Shadow Price") say an extra sewing hour is worth up to 57.78 of
contribution, and an extra square metre of fabric 7.89, within the allowable range. That is the managerial output:
an overtime hour costing 30 is worth buying; fabric at 12 per square metre above plan is not.

## D7 (Level 2): charts that make one point

<!-- Guidance: Choose the chart from the message, not from the menu. One chart, one message, written as the chart title. Remove gridlines and legends you do not need, label the series directly and start a bar axis at zero. -->

**Idea.** A column chart compares categories, a line chart shows change over time, a stacked column shows composition
and totals together, a scatter shows a relationship, and a waterfall bridges one total to another. Use the
recommended-chart menu only as a starting point.

**Task 7.1.** From the region by month table (SUMIFS with two criteria, or a pivot with Month in Rows and Region in
Columns) build a stacked column chart. Title it with the finding, for example "South overtook North after January".

| Month | North | South | West | Total |
|---|---|---|---|---|
| Jan | 5,820 | 2,618 | 4,170 | 12,608 |
| Feb | 1,971 | 5,260 | 4,482 | 11,713 |
| Mar | 2,224 | 4,980 | 4,765 | 11,969 |

```vega-lite
{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "Northfield Outdoor Co. first-quarter revenue by month and region, USD (fictional data)",
  "width": 360,
  "height": 220,
  "data": {"values": [
    {"month": "Jan", "order": 1, "region": "North", "revenue": 5820},
    {"month": "Jan", "order": 1, "region": "South", "revenue": 2618},
    {"month": "Jan", "order": 1, "region": "West", "revenue": 4170},
    {"month": "Feb", "order": 2, "region": "North", "revenue": 1971},
    {"month": "Feb", "order": 2, "region": "South", "revenue": 5260},
    {"month": "Feb", "order": 2, "region": "West", "revenue": 4482},
    {"month": "Mar", "order": 3, "region": "North", "revenue": 2224},
    {"month": "Mar", "order": 3, "region": "South", "revenue": 4980},
    {"month": "Mar", "order": 3, "region": "West", "revenue": 4765}
  ]},
  "mark": "bar",
  "encoding": {
    "x": {"field": "month", "type": "ordinal", "sort": ["Jan", "Feb", "Mar"], "title": "Month"},
    "y": {"field": "revenue", "type": "quantitative", "aggregate": "sum", "title": "Revenue (USD)"},
    "color": {"field": "region", "type": "nominal", "title": "Region"}
  }
}
```

**Task 7.2.** Plot the D5 profit at 2,400 units against price as a line chart (prices on the x-axis). Expected: a
straight line from 9,600 at 269 to 153,600 at 329, slope 2,400 per dollar. Ask yourself why it is straight (profit is
linear in price when volume is held fixed) and what would bend it (volume that responds to price, see
mba/mba-demand-elasticity-card).

## D8 (Level 3): a small three-statement model

<!-- Guidance: Build in this order: inputs, income statement, balance sheet items other than cash, cash flow statement, then cash on the balance sheet from the cash flow statement, then the balance check. If the check is not zero, find the first year where it breaks and the first line that differs. -->

**Idea.** The income statement, balance sheet and cash flow statement are linked: net income flows to retained
earnings and to the top of the cash flow statement; changes in working capital and fixed assets explain the gap
between profit and cash; closing cash from the cash flow statement is the cash line of the balance sheet. If every
link is right, assets equal liabilities plus equity in every year without a plug. The accounting behind each link is in
mba/pre-mba-accounting-primer and mba/mba-three-statements-card.

**Inputs** (sheet `Model`, column C, *Example* values for a Northfield business unit, USD thousands)

| Input | Value | Input | Value |
|---|---|---|---|
| Year 0 revenue | 2,000 | Receivables, % of revenue | 8% |
| Revenue growth | 8% | Inventory, % of cost of sales | 15% |
| Gross margin | 42% | Payables, % of cost of sales | 10% |
| Operating expenses, % of revenue | 28% | Interest rate on opening debt | 6% |
| Depreciation, % of opening PP&E | 10% | Debt repayment per year | 40 |
| Capital expenditure per year | 90 | Dividend payout, % of net income | 30% |
| Tax rate | 25% | | |

**Opening balance sheet (Year 0):** cash 150, receivables 160, inventory 174, PP&E 600 (total assets 1,084);
payables 116, debt 400, share capital 300, retained earnings 268 (total 1,084).

**Row map** (inputs in C4:C16 in the order of the inputs table read row by row: C4 Year 0 revenue, C5 growth, C6 gross
margin, C7 operating expenses, C8 depreciation, C9 capital expenditure, C10 tax rate, C11 receivables, C12 inventory,
C13 payables, C14 payout, C15 interest rate, C16 repayment)

| Block | Rows |
|---|---|
| Income statement | 10 revenue, 11 cost of sales, 12 gross profit, 13 operating expenses, 14 depreciation, 15 operating profit, 16 interest, 17 profit before tax, 18 tax, 19 net income, 20 dividends |
| Balance sheet | 27 cash, 28 receivables, 29 inventory, 30 PP&E, 31 total assets, 32 payables, 33 debt, 34 share capital, 35 retained earnings, 36 total liabilities and equity |
| Cash flow statement | 40 net income, 41 depreciation, 42 change in receivables, 43 change in inventory, 44 change in payables, 45 cash flow from operations, 46 capital expenditure, 47 debt repaid and dividends paid, 48 net change in cash |

**Key formulas** (Year 1 in column E, Year 0 in column D; copy E across to G)

| Line | Formula pattern |
|---|---|
| Revenue | `=D10*(1+$C$5)` |
| Cost of sales | `=E10*(1-$C$6)` |
| Depreciation | `=$C$8*D30` (opening PP&E) |
| Interest | `=$C$15*D33` (opening debt, which avoids a circular reference) |
| Tax | `=$C$10*E17` (profit before tax) |
| Receivables | `=$C$11*E10` |
| Change in receivables (row 42) | `=-(E28-D28)` |
| PP&E | `=D30+$C$9-E14` |
| Debt | `=D33-$C$16` |
| Retained earnings | `=D35+E19-E20` (plus net income, less dividends) |
| Closing cash | `=D27+E48` (opening cash plus net change in cash) |
| Balance check | `=ROUND(E31-E36,6)` must be 0 |

**Expected results** (rounded to one decimal; each figure is computed on unrounded values, so a rounded column may
differ by 0.1 from the sum of its rounded lines)

| Line | Year 1 | Year 2 | Year 3 |
|---|---|---|---|
| Revenue | 2,160.0 | 2,332.8 | 2,519.4 |
| Cost of sales | 1,252.8 | 1,353.0 | 1,461.3 |
| Gross profit | 907.2 | 979.8 | 1,058.2 |
| Operating expenses | 604.8 | 653.2 | 705.4 |
| Depreciation | 60.0 | 63.0 | 65.7 |
| Operating profit (EBIT) | 242.4 | 263.6 | 287.0 |
| Interest | 24.0 | 21.6 | 19.2 |
| Profit before tax | 218.4 | 242.0 | 267.8 |
| Tax | 54.6 | 60.5 | 67.0 |
| Net income | 163.8 | 181.5 | 200.9 |
| Dividends | 49.1 | 54.4 | 60.3 |
| Cash flow from operations | 206.4 | 225.7 | 246.2 |
| Cash flow from investing | -90.0 | -90.0 | -90.0 |
| Cash flow from financing | -89.1 | -94.4 | -100.3 |
| Net change in cash | 27.2 | 41.2 | 56.0 |
| Cash | 177.2 | 218.4 | 274.4 |
| Receivables | 172.8 | 186.6 | 201.6 |
| Inventory | 187.9 | 203.0 | 219.2 |
| PP&E | 630.0 | 657.0 | 681.3 |
| Total assets | 1,167.9 | 1,265.0 | 1,376.4 |
| Payables | 125.3 | 135.3 | 146.1 |
| Debt | 360.0 | 320.0 | 280.0 |
| Share capital | 300.0 | 300.0 | 300.0 |
| Retained earnings | 382.7 | 509.7 | 650.3 |
| Total liabilities and equity | 1,167.9 | 1,265.0 | 1,376.4 |
| Balance check | 0.0 | 0.0 | 0.0 |

Year 1 cash flow from operations, line by line: net income 163.8, plus depreciation 60.0, less the increase in
receivables 12.8, less the increase in inventory 13.9, plus the increase in payables 9.3, gives 206.4. The model is
profitable and generates cash, and by Year 3 net debt (debt 280.0 less cash 274.4) is almost zero.

**Stretch extension.** Compute interest on average debt instead of opening debt. That makes interest depend on a
balance that depends on cash, which depends on interest: a circular reference. Either keep opening balances (the
simple, transparent choice used here), or add a circularity switch cell and enable iterative calculation (File,
Options, Formulas) knowingly, never by accident.

## D9 (Level 3): modelling conventions and checks

<!-- Guidance: Conventions are what let a reviewer trust your model in five minutes. Apply all of them to the D8 model, then ask a study partner to review it with the checklist below without your help. -->

**Layout.** Separate inputs, calculations and outputs. In a small model they can be blocks on one sheet; in a larger
one, separate sheets (`Inputs`, `Calc`, `Outputs`, `Checks`). Time runs left to right, one column per period, the same
column for the same period on every sheet. Fix one column layout and keep it: in D8, labels sit in column B, inputs in
column C, Year 0 in column D and Years 1 to 3 in columns E to G.

**Colour coding** (a widespread convention; follow your firm's or course's house style when it differs):

| Content | Font colour | Example |
|---|---|---|
| Hard-coded input | Blue | Growth rate 8% |
| Formula on the same sheet | Black | `=D10*(1+$C$5)` |
| Link to another sheet | Green | `=Inputs!C5` |
| Check or flag | Red, or a red fill when it fails | `=IF(ABS(E31-E36)>0.001,"ERROR","OK")` |

**Checks sheet** (*Example* rows for the D8 model)

| ID | Check | Formula pattern | Expected |
|---|---|---|---|
| CHK-001 | Balance sheet balances, every year | `=ROUND(E31-E36,6)` | 0 |
| CHK-002 | Cash on balance sheet equals closing cash on cash flow | `=ROUND(E27-(D27+E48),6)` | 0 |
| CHK-003 | Retained earnings roll forward | `=ROUND(E35-(D35+E19-E20),6)` | 0 |
| CHK-004 | PP&E roll forward | `=ROUND(E30-(D30+$C$9-E14),6)` | 0 |
| CHK-005 | No negative cash | `=MIN(E27:G27)>=0` | TRUE |
| CHK-006 | Summary totals equal source totals (D3, D4) | `=ROUND(SUM(Summary!B2:B4)-Sales!G14,6)` | 0 |
| CHK-007 | Master check | `=IF(AND(...all checks...),"ALL OK","CHECK FAILED")` | ALL OK |

**Other conventions.** One formula per row; no numbers typed into formulas; no merged cells inside calculation blocks;
named ranges only for a few key inputs (or none, if your course discourages them); a cover sheet with purpose, author,
version and a change log; and Formulas, Trace Precedents and Trace Dependents to audit a cell. Press Ctrl+` (show
formulas) to scan a row for an odd formula out.

```mermaid
flowchart LR
  A["Inputs sheet (blue, one value per cell)"] --> B["Calculations (black, one formula per row)"]
  B --> C["Outputs (statements, charts, summary)"]
  B --> D["Checks sheet (balance, roll-forwards, totals)"]
  C --> D
  D --> E{"All checks pass?"}
  E -- "No" --> F["Find the first year and first line that fails"]
  F --> B
  E -- "Yes" --> G["Review by a peer, then share"]
```

## Progress log

<!-- Guidance: Record each attempt. The second and third attempts, days apart, are where speed comes from. Note the error, not only the time. -->

| Drill | Attempt 1 (date, minutes) | Errors found | Attempt 2 | Attempt 3 | Passed |
|---|---|---|---|---|---|
| D1 references *(Example)* | 2026-11-02, 14 min | Forgot `$` on the discount cell | 2026-11-04, 7 min | 2026-11-11, 5 min | Yes |
| D2 lookups | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D3 conditional sums | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D4 pivot tables | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D5 data tables | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D6 Goal Seek and Solver | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D7 charts | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D8 three-statement model | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |
| D9 conventions and checks | [YYYY-MM-DD, min] | [ ] | [ ] | [ ] | [ ] |

**Keyboard shortcuts worth learning first** (Windows; Mac equivalents in brackets): F2 edit a cell, F4 toggle
absolute references (Cmd+T), Ctrl+D fill down (Cmd+D), Ctrl+R fill right (Cmd+R), Ctrl+arrow jump to the edge of data,
Ctrl+Shift+arrow select to the edge, Alt+= AutoSum (Cmd+Shift+T), Ctrl+1 format cells (Cmd+1), Ctrl+` show formulas,
Ctrl+[ go to precedents.

## Common mistakes

<!-- Guidance: Read this list before D8 and again after it. Most model errors are one of these. -->

1. **Missing dollar signs.** The formula is right in the first cell and wrong in every copy. Test by copying and
   checking one far cell by hand.
2. **Hard-coded numbers inside formulas.** `=D10*1.08` hides the growth assumption; when the input changes, this cell
   does not. Point to the input cell.
3. **VLOOKUP with a fixed column number** breaks silently when someone inserts a column. Use XLOOKUP or INDEX-MATCH.
4. **Approximate match on unsorted data.** MATCH type 1 (and VLOOKUP with TRUE) returns a wrong value without an
   error when the thresholds are not ascending. XLOOKUP match mode -1 does not need sorted data, but keep thresholds
   ascending anyway so a reader can check the bands.
5. **Numbers stored as text** (imported data, a leading apostrophe). SUMIFS ignores them and the total is too low. Use
   `=ISNUMBER()` to test, and Data, Text to Columns or `=VALUE()` to convert.
6. **Pivot tables not refreshed.** The pivot shows yesterday's data. Refresh (Alt+F5) and reconcile with a SUMIFS total.
7. **Data table input cells swapped**, or the input cells on a different sheet from the table. Check a corner by hand.
8. **Goal Seek answers left as unexplained values.** Record what was solved for and confirm it with algebra where you
   can.
9. **Solver run with the wrong engine.** GRG Nonlinear treats a linear problem as a general smooth one: it can stop
   on tolerance short of the exact vertex and its sensitivity report gives Lagrange multipliers without allowable
   ranges. Choose Simplex LP for linear problems, and Solver will also tell you if the model is not linear.
10. **A plug to make the balance sheet balance.** A balancing figure hides the error instead of finding it. Find the
    first year and line where the check breaks.
11. **Accidental circular references** from interest on average debt or cash. Use opening balances, or a deliberate
    switch with iteration turned on and documented.
12. **Charts that need a legend to be understood.** Title the chart with the message and label the series directly.

## Self-test

<!-- Guidance: Answer from memory before you look at the answer key. Retrieval, not rereading, is what makes the skill stick (Dunlosky et al. 2013). -->

1. You type `=B$3*$A4` in B4 and copy it to D6. What formula is in D6?
2. Which XLOOKUP argument returns "Not found" instead of #N/A, and which match mode returns the next smaller value?
3. Write one formula for the revenue of Tents in the West region in March, using the D3 ranges.
4. Your pivot table shows a grand total of 35,000 but `Sales!G14` shows 36,290. Name two likely causes.
5. In D5, what profit does the data table show for a price of 309 and 2,100 units, and how would you check it by hand?
6. Break-even units at a price of 289 (other inputs as in D5)? Give the Excel formula and the value.
7. In D6, the shadow price of sewing time is 57.78. Overtime costs 45 per hour. Should Northfield buy overtime, and
   what limits the answer?
8. In D8, why is interest calculated on opening debt rather than closing or average debt?
9. In D8, the balance check is 0 in Year 1 but 12.8 in Year 2. What do you do first?
10. Name the four colour conventions in D9 and what each signals.

## My notes and next steps

<!-- Guidance: Write down the two drills that cost you most time and when you will repeat them. Link the next primer you will work on. -->

| Item | Note |
|---|---|
| Slowest drills | [D5 and D8] |
| Repeat dates | [YYYY-MM-DD, YYYY-MM-DD] |
| Questions for a second-year student or TA | [ ] |
| Next primer | [mba/pre-mba-accounting-primer, then mba/pre-mba-finance-primer] |

## Sources and further reading

<!-- Guidance: Use the official function documentation when a formula behaves unexpectedly; it lists version availability and edge cases. -->

- Microsoft Support. XLOOKUP function, INDEX function, MATCH function, SUMIFS function, COUNTIFS function, AVERAGEIFS
  function, "Calculate multiple results by using a data table", "Use Goal Seek to find the result you want by adjusting
  an input value" and "Define and solve a problem by using Solver", support.microsoft.com (accessed 2026-10-28). The
  XLOOKUP page lists Microsoft 365, Excel 2021 and Excel 2024 among the versions that support it; check your version.
- Dantzig, G. B. (1951). Maximization of a linear function of variables subject to linear inequalities. In T. C.
  Koopmans (ed.), *Activity Analysis of Production and Allocation*. Wiley. (The simplex method, developed from 1947.)
- Dunlosky, J., Rawson, K. A., Marsh, E. J., Nathan, M. J., and Willingham, D. T. (2013). Improving students' learning
  with effective learning techniques. *Psychological Science in the Public Interest*, 14(1), 4-58.
- Further reading (by name only): Winston, *Microsoft Excel Data Analysis and Business Modeling*; Benninga,
  *Financial Modeling*; Powell and Baker, *Business Analytics: The Art of Modeling with Spreadsheets*. The FAST
  Standard (fast-standard.org) is one published set of modelling conventions you may compare with D9.
- Related templates: mba/mba-three-statements-card, mba/mba-cvp-breakeven-card, mba/mba-formula-sheet,
  finance/financial-model-specification.

## Answer key

<!-- Guidance: Check after you have written your own answers. For each wrong answer, note why your first answer was wrong. -->

**D1 self-check.** I14 shows 100% (1.0). With `=G2/G14` the denominator moves down with the copy (G15, G16 and so on,
which are empty), so I3:I13 return #DIV/0! errors.

**Self-test.**

1. `=D$3*$A6`. The column of the first reference moves (B to D) but its row stays 3; the second keeps column A and its
   row moves (4 to 6).
2. The fourth argument, `if_not_found`. Match mode -1 (exact or next smaller); the thresholds should be sorted for a
   readable model, although XLOOKUP itself does not require sorting in this mode.
3. `=SUMIFS(Sales!$G$2:$G$13,Sales!$I$2:$I$13,"Tents",Sales!$C$2:$C$13,"West",Sales!$B$2:$B$13,"Mar")`. The result is 0:
   the West orders in March are PK-30 and PK-55, both Packs.
4. The pivot source range does not include all rows (a static range set before rows were added, or an Excel Table not
   used), or the pivot was not refreshed; a filter applied in the pivot; or numbers stored as text in the source.
   36,290 less 35,000 is 1,290, so look for a row or filter worth that amount: none of the twelve orders is exactly
   1,290, so suspect a text value or an edited cell.
5. 62,400. By hand: $(309-165)\times 2{,}100-240{,}000=302{,}400-240{,}000=62{,}400$.
6. `=B5/(289-B4)` with the price in its own cell, or `=240000/(289-165)`: 1,935.48 units, so plan for 1,936 tents.
7. Yes within the allowable range of the sensitivity report: each overtime hour adds up to 57.78 of contribution for
   45 of cost, a gain of about 12.78 per hour. Beyond the allowable increase, another constraint (fabric, or pack
   demand) starts to bind and the shadow price changes, so re-run Solver for a large change.
8. Interest on opening debt avoids a circular reference (closing debt and cash depend on interest). It is a slight
   approximation when debt changes during the year; state it as an assumption.
9. Find the first line that differs in Year 2: compare total assets and total liabilities and equity line by line with
   Year 1 logic. The difference of 12.8 equals the Year 1 increase in receivables, which suggests the Year 2 working
   capital change uses the wrong year (for example `=-(F28-E28)` overwritten with a typed value or an absolute reference on the
   opening balance).
10. Blue: hard-coded input. Black: formula on the same sheet. Green: link to another sheet. Red: check or flag.

## Revision history

<!-- Guidance: Add a row when you change the data, the drills or the expected results. -->

| Version | Date | Author | Change summary |
|---|---|---|---|
| 0.1 | YYYY-MM-DD | [Your name] | First attempt at all drills |
| 0.2 | YYYY-MM-DD | [Your name] | *Example:* Repeated D5 and D8, added CHK-008 for net debt |

## Standard and licence note

Based on the public Microsoft Excel function documentation and on spreadsheet modelling conventions that are common
practice in business-school and finance teaching (inputs, calculations and outputs separated, colour coding, check
sheets), explained in original wording. The drills, data, expected results and model are original; every expected
value was computed independently in Python and with a spreadsheet-formula engine. Northfield Outdoor Co. and all
figures are fictional. Microsoft and Excel are trademarks of Microsoft Corporation, named only to identify the
software; this template is not affiliated with or endorsed by Microsoft or by any business school. Check the current
documentation of your spreadsheet software before relying on version-specific behaviour. Template licence: MIT.