How to Use This Resource: This reference covers the Excel functions and techniques you need for the Sunrise Cafe Expansion workbook. Refer back to it whenever you get stuck on a formula.

Excel Techniques: Projections & Scenario Modeling

This resource covers the Excel functions and techniques you need for the Unit 8 workbook. Refer back to it whenever you get stuck on a formula.

1. AVERAGE Function

You used SUM in earlier units. AVERAGE works the same way but divides by the count automatically.

Syntax: =AVERAGE(range)

Example: =AVERAGE(B5:B16)
→ Returns the average of cells B5 through B16

AVERAGE ignores empty cells but treats cells containing 0 as values. If your data has blank months, AVERAGE will still calculate correctly. If some months show $0, those zeros will pull the average down.

2. Exponents (The ^ Operator)

CAGR and compound growth formulas require raising a number to a power. In Excel, the caret symbol (^) does this.

Syntax: =base ^ exponent

Examples: =2^3 → 8 (2 × 2 × 2)
=(1.025)^12 → 1.3449 (2.5% monthly growth compounded over 12 months)
=(B16/B5)^(1/11)-1 → monthly CAGR from January (B5) to December (B16)

Why 11 and not 12? If you have data for January through December, there are 12 data points but only 11 periods of growth between them (Jan→Feb, Feb→Mar, ... Nov→Dec). CAGR measures the growth rate per period, so the exponent uses the number of periods, not the number of data points.

3. Compound Growth Projection

Once you have a growth rate, you can project future values by compounding forward from a known starting point.

Formula: =Starting_Value * (1 + Growth_Rate) ^ Periods_Forward

Example: If December revenue is $31,000 and monthly growth is 2.5%:

January (Year 2): =31000 * (1.025)^1 → $31,775
February (Year 2): =31000 * (1.025)^2 → $32,569
June (Year 2): =31000 * (1.025)^6 → $35,986

Alternatively, you can chain projections: each month's value equals the previous month times (1 + growth rate). Both approaches produce the same result.

Chain method: January: =December_Revenue * (1 + Growth_Rate)
February: =January_Projected * (1 + Growth_Rate)
March: =February_Projected * (1 + Growth_Rate)

The chain method is easier to build in a spreadsheet because you can write one formula and copy it down. The direct method (using ^Periods_Forward) is useful when you need to jump to a specific future month without calculating every month in between.

4. PMT Function (Loan Payments)

PMT calculates the fixed monthly payment on a loan. This is the most important new function in this unit.

Syntax: =PMT(rate, nper, pv)

Where: rate = interest rate per period (monthly rate, not annual)
nper = total number of payments
pv = present value (loan amount, entered as a negative number)

Example: $80,000 loan at 8% annual interest, repaid over 5 years:

=PMT(0.08/12, 60, -80000)
→ $1,622 per month

Two common mistakes with PMT:

Using the annual rate instead of monthly. An 8% annual rate means 0.08/12 per month, not 0.08. If you enter 0.08, Excel will calculate payments as if the rate is 8% per month (96% per year), producing an absurdly high number.
Forgetting the negative sign on the loan amount. PMT expects the loan amount to be negative (money flowing out to you). If you enter 80000 instead of -80000, the result will be negative, which still works mathematically but can cause sign errors in your model.

5. Cell References in Formulas (Review)

Your expansion model reuses the same values across multiple formulas: the growth rate, the ingredient cost ratio, the loan payment. Instead of typing the number into each formula, reference the cell where it is calculated.

Bad (hard-coded): =B7 * 0.30 ← If the ratio changes, you must update every formula

Good (cell reference): =B7 * $B$3 ← Change the ratio in B3 once, all formulas update

The dollar signs ($B$3) create an absolute reference, meaning the reference stays on cell B3 even when you copy the formula to other cells. Without dollar signs, Excel shifts the reference relative to where you paste it, which is usually not what you want when referencing a single parameter like a cost ratio.

Keyboard shortcut: After typing a cell reference, press F4 (Windows) or Fn+F4 (Mac) to cycle through reference types: B3 → $B$3 → B$3 → $B3 → B3.

6. Cumulative Totals (Running Sum)

In the expansion model, you track cumulative profit/loss to see when the business earns back its early losses. A running sum adds the current month's value to everything before it.

Month 1: =H5   (just the first month's profit/loss)

Month 2: =I5 + H6   (previous cumulative + this month's profit/loss)

Month 3: =I6 + H7   (previous cumulative + this month's profit/loss)

After entering Month 2's formula, you can copy it down for Months 3-12. The pattern stays the same: previous running total plus the current month's value.

7. Creating a Line Chart

Line charts show trends over time. For the scenario comparison:

Select the data you want to chart (month labels + the values for each scenario).
Go to Insert > Chart > Line.
Add a chart title by clicking on the chart title area and typing.
Add axis labels: click the chart, then Chart Design > Add Chart Element > Axis Titles.

Selecting non-adjacent columns: To chart Month (column A) and three scenario columns that are not next to each other, hold Ctrl (Windows) or Cmd (Mac) while selecting each column's data. Excel will plot each selection as a separate line.

If your chart does not look right, check that the month labels are being used as the X-axis (horizontal) and the dollar values as the Y-axis (vertical). You can switch them by right-clicking the chart and selecting Select Data > Switch Row/Column.

Quick Reference Table

Excel formulas quick reference for projections and scenario modeling
What You Need	Formula	Used In
Average of a range	`=AVERAGE(B5:B16)`	Exercise 1
Monthly CAGR	`=(End/Start)^(1/Periods)-1`	Exercise 2
Growth projection	`=Start*(1+Rate)^N`	Exercise 4
Cost ratio	`=Total_Cost/Total_Revenue`	Exercise 6
Variable cost from ratio	`=Revenue*$B$3`	Exercise 7
Loan payment	`=PMT(Rate/12, Months, -Principal)`	Exercise 7
Running total	`=Previous_Cumulative+Current`	Exercise 7