Modeling discipline
Structuring a model
Separate inputs, calculations and outputs, and format a model so anyone can audit it.
Learning objectives
- Separate a model into inputs, calculations and outputs
- Apply the standard formatting conventions: blue inputs, black formulas, green links
- Build a forecast in which every number traces back to a single input
A financial model is a decision tool that other people will read, change and rely on. A credit committee will flex the downside case. A reviewer will trace a number back to its source. You will reopen it in three months. A model that mixes typed numbers into formulas, or scatters assumptions across tabs, gives wrong answers after the first change, and nobody notices.
Modeling tests in banking and FP&A interviews grade structure as heavily as the answer. Throughout this track you will build a forecast of Rawdah Home Retail Co., ending with a fully linked three-statement model. Rawdah Home Retail Co. is a fictional retailer; all figures are SAR millions and illustrative.
Three layers, kept apart:
- Inputs. Every hard-coded number: historical figures and forecast assumptions. Each appears once.
- Calculations. Formulas only. They read inputs and earlier calculations, never a typed number.
- Outputs. The handful of results a reader needs (growth, margins, value), linked from the calculations.
Calculations flow one way: inputs → calculations → outputs, top to bottom and left to right. Time runs across the columns, and the same column means the same year on every tab. That way, one formula can be copied across the row, and a cross-tab link such as =Inputs!C3 reads the right year.
Revenueₜ = Revenueₜ₋₁ × (1 + gₜ)
- Revenueₜ₋₁
- the previous column's value on the same row (a calculation)
- gₜ
- this year's growth assumption, read from the Inputs tab, same column
Check your understanding
On the Calculations tab, Rawdah's FY2026E gross profit (column C) is revenue × the FY2026 gross margin, which sits in Inputs!C4. You will copy the formula right to FY2027E and FY2028E. Which formula is best practice?
Check your understanding
Reviewing a colleague's model, you click a black-font cell on the Calculations tab showing 1,729.9 (FY2027E revenue). The formula bar shows the typed number 1,729.9. What is the problem, and the fix?
Pull the hard-codes out of a colleague's forecast
A colleague forecast Rawdah Home Retail Co.'s revenue and gross profit with the assumptions typed inside the formulas:
| Cell | FY2026E (C) | FY2027E (D) | FY2028E (E) |
|---|---|---|---|
| Revenue (row 7) | =B7*(1+0.083) | =C7*(1+0.067) | =D7*(1+0.06) |
| Gross profit (row 8) | =C7*0.405 | =D7*0.408 | =E7*0.41 |
Restructure it:
- Inputs block: type each year's growth rate (row 3) and gross margin (row 4) into the blue cells, once each.
- Calculations: rebuild rows 7 and 8 for FY2026E–FY2028E as formulas that read the inputs block, with no numbers typed inside them. Write each formula once and fill right.
After you submit, FY2025A revenue (B2) is shifted to check that the calculations are live.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Inputs (SAR millions) | FY2025A | FY2026E | FY2027E | FY2028E |
| 2 | Revenue | 1,497.0 | |||
| 3 | Revenue growth | ||||
| 4 | Gross margin | ||||
| 5 | |||||
| 6 | Calculations (SAR millions) | FY2025A | FY2026E | FY2027E | FY2028E |
| 7 | Revenue | 1,497.0 | |||
| 8 | Gross profit |
Challenge: Structure Rawdah's operating forecast
Build a three-tab model of Rawdah Home Retail Co.'s operating forecast (SAR millions). All assumptions are on Inputs.
- Calculations: link FY2025A revenue (B2) from Inputs, then forecast revenue, gross profit, operating expenses and EBITDA for FY2026E–FY2028E (columns C–E). Each year reads its own column on Inputs.
- Outputs: revenue CAGR from FY2025A to FY2028E (use the forecast-years cell on Inputs), the FY2028E EBITDA margin and cumulative EBITDA for the three forecast years.
No typed numbers outside Inputs: after you submit, every assumption is shifted to check the model is live.
Inputs only: hard-coded numbers in blue, one place each.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Inputs (SAR millions) | FY2025A | FY2026E | FY2027E | FY2028E |
| 2 | Revenue | 1,497.0 | |||
| 3 | Revenue growth | 8.3% | 6.7% | 6.0% | |
| 4 | Gross margin | 40.5% | 40.8% | 41.0% | |
| 5 | Operating expenses (% of revenue) | 27.9% | 27.9% | 28.0% | |
| 6 | |||||
| 7 | Forecast years (for the CAGR) | 3 |
You're learning as a guest — exercises are checked, but progress isn't saved. Create a free account to keep it.