← Financial Modeling in Excel from Scratch
Lesson
The second location: assumptions and the profit forecast
Build the second coffee shop's revenue, costs, EBIT, and NOPAT by year from drivers, with anchored references to the assumptions.
The second location: the model sheet
The second location: the model sheet
The course's final task is to decide whether the Zerno coffee shop should open a second location. You'll build the model yourself: the profit forecast in this lesson, cash and valuation in the next, and scenarios and the decision in the last.
The sheet is laid out as in lesson 1.3. At the top are the assumptions, one number per cell: 100 orders a day, 80% of that level in the first year, an average order value of $6, 360 working days, variable costs of 30% of revenue, fixed costs of $84 thousand a year, depreciation of 24 (a 120 investment over 5 years), and a 25% tax rate. Below, the years 2027–2031 run across the columns, one formula per row.
Worked example: revenue for 2027. Revenue = orders per day × average order value × days / 1,000: the drivers are in dollars, but the model is in $ thousands. In 2027 there are 80 orders: 80 × 6 × 360 / 1,000 = 172.8. The formula is filled across the years to the right, so the references to the order value and the days are anchored with “$”, while the reference to the year's orders stays relative: each year takes its own orders. In 2028, with 100 orders, you get 216.
The rest follows the same pattern: variable costs = revenue × share, EBIT = revenue − variable − fixed − depreciation, tax = EBIT × 25%, NOPAT = EBIT − tax (lesson 5.1).
What's where on the sheet
| Cells | What's there |
|---|---|
| B2:B9 | Assumptions: orders, first-year share, order value, days, cost shares and amounts, tax |
| B11:F11 | Years 2027–2031, G — the 5-year total |
| Row 12 | Orders per day: 2027 — 80% of the level, then 100 |
| Rows 13–16 | Revenue, variable costs, fixed costs, depreciation |
| Rows 17–19 | EBIT, tax, NOPAT |
Lesson notes
The second location: the model sheet
The course's final task is to decide whether the Zerno coffee shop should open a second location. You'll build the model yourself: the profit forecast in this lesson, cash and valuation in the next, and scenarios and the decision in the last.
The sheet is laid out as in lesson 1.3. At the top are the assumptions, one number per cell: 100 orders a day, 80% of that level in the first year, an average order value of $6, 360 working days, variable costs of 30% of revenue, fixed costs of $84 thousand a year, depreciation of 24 (a 120 investment over 5 years), and a 25% tax rate. Below, the years 2027–2031 run across the columns, one formula per row.
Worked example: revenue for 2027. Revenue = orders per day × average order value × days / 1,000: the drivers are in dollars, but the model is in $ thousands. In 2027 there are 80 orders: 80 × 6 × 360 / 1,000 = 172.8. The formula is filled across the years to the right, so the references to the order value and the days are anchored with “$”, while the reference to the year's orders stays relative: each year takes its own orders. In 2028, with 100 orders, you get 216.
The rest follows the same pattern: variable costs = revenue × share, EBIT = revenue − variable − fixed − depreciation, tax = EBIT × 25%, NOPAT = EBIT − tax (lesson 5.1).