Pre-MBA Excel Modelling Drills Template

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.

Document control

How the drills are graded

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.

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

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

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

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

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

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.

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.

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

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.

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)).

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

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.

Task 3.2. Answer each with one formula.

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

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.

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

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).

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.

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

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.

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.

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

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".

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

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)

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)

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

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)

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

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):

Checks sheet (Example rows for the D8 model)

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.

Progress log

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

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

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

Sources and further reading

• 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

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

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.