How to Forecast Revenue in Excel (5 Main Methods)

Finance-focused summary (Revenue forecasting in Excel)

The video demonstrates 5 Excel-based methods to forecast revenue, with emphasis on:

• What to do when you have no historical data
• How to handle growth, growth fade, run-rate assumptions, seasonality, and statistical forecasting

It uses a coffee shop / new store example (no tickers/assets referenced).

---

Instruments / tickers / markets mentioned

• None.

---

Key quantitative inputs (from examples)

Top-down (no history) coffee store example

• Town population: 40,000
• Coffee buyers (out-of-home): 25%
• Market capture / store market share: 4% (illustrated sensitivity to 8%)
• Visits frequency: 4 times/month
• Average order value (AOV): $7
• Market growth rate (population growth): 0.2% (monthly)
• Monthly market share increase: 0.1
• Initial month revenue: approximately $11,000 (as shown after sheet calculations)

Bottom-up (no history) coffee store example

• Weekday customers per day: 65
• Weekend customer ratio/discount: 80% of weekday traffic
• Open days: 22 weekdays + 8 weekend days
• Monthly customer growth assumption: 4%
• AOV: $7
• Example sensitivity: changing weekday customers (to 80) causes large forecast changes

Historical growth forecast (with history)

• Uses 2025–2026 actuals to forecast 2027

• Monthly YoY growth formula: [ \text{YoY growth} = \frac{\text{latest year}}{\text{previous year}} - 1 ]

• Example growth rate cited: ~20% in January (~19% referenced in the fade example)

• Growth fade vs latest YoY: subtract 3%
  • Example: 19% − 3% = 16%
• Minimum threshold: 6%
• When applying a hypothetical 4% growth, the model floors it at 6%

Run-rate method (recent months emphasized)

• Normalized run rate uses the last 3 months average
• Month-over-month growth overlay: 35% monthly growth (stated)
• Seasonal weighting based on last year:
  • “current month / annual total,” with weights summing to 100%
• Seasonality adjustment includes multiplying by 12 (to annualize)

Statistical forecast (Excel)

• Uses forecast ETS
• Seasonal period: 12 (annual cycle)
• Model weights recent data more heavily (ETS behavior described)

---

Methodology / step-by-step frameworks (all 5 methods)

1) Top-down method (market → customers → transactions → revenue)

1. Start with general market size (e.g., town population).
2. Apply:
   • % who buy coffee out (e.g., 25%)
   • estimated store market share/capture (e.g., 4%, sensitive)
3. Estimate frequency:
   • visits per month (e.g., 4)
4. Compute:
   • active customers = coffee buyers × market share
   • transactions = active customers × visits/month
   • revenue = transactions × AOV ($7)
5. Apply growth forward using assumptions such as:
   • population growth (e.g., monthly 0.2%)
   • market share increases (e.g., +0.1 monthly)

Key caution: revenue is highly sensitive to market share (example changes from 4% to 8% produce large output changes).

---

2) Bottom-up method (foot traffic → orders)

1. Start from customers per day.
2. Split into:
   • weekday customers
   • weekend customers = weekday × 80%
3. Multiply by open days:
   • total monthly customers = weekday × 22 + weekend × 8
4. Apply AOV ($7) to convert to revenue:
   • revenue = monthly customers × AOV
5. Apply growth:
   • monthly customer growth rate (e.g., 4%) via (1 + growth)

Key caution: forecast is highly sensitive to the daily customer assumption; ideally derive it from local foot traffic.

---

3) Historical growth forecast (YoY + growth fade + minimum)

1. Use actuals for multiple years (example: 2025–2026 actuals, forecast 2027).
2. Compute monthly YoY growth:
   • latest year / previous year − 1
3. Apply growth fade to avoid assuming the latest high growth continues forever:
   • reduce by 3% (e.g., 19% → 16%)
4. Apply a minimum threshold:
   • floor forecast growth at 6%
5. Compute forecast revenue:
   • [ \text{revenue}(t+1) = \text{revenue(latest year)} \times (1 + \text{applied growth}) ]

Seasonality: forecast is month-by-month using growth trajectories, so seasonality is treated as capturable.

Key limitation: the model doesn’t explain drivers of growth (price vs volume vs customer mix).

---

4) Run rate method (normalized last N months + MoM overlay + seasonal weights)

1. Choose normalization window:
   • last 3 months average (“normalized run rate”)
2. Compute normalized monthly baseline.
3. Apply a growth overlay:
   • monthly growth rate (example: 35% monthly growth), applied as (1 + growth)
4. Apply seasonal weighting:
   • seasonal weight = (month value / annual total) from last year
   • weights sum to 100%
5. Annualize seasonal contribution:
   • multiply by 12
6. Compute monthly forecasts and then sum/average for annual totals as needed.

Key cautions: highly sensitive to what happened recently (e.g., a promotion in Nov/Dec can distort the whole year forecast).

---

5) Statistical forecast (Excel Forecast.ETS)

• Use Excel’s time-series function:
  • forecast ETS

Inputs:

• target date
• historical values
• historical timeline (dates)
• optional seasonal parameter:
  • seasonality period = 12

Excel automatically accounts for:

• trend
• seasonality
• heavier weighting to recent data

Key limitation: not transparent—output is produced “without assumptions,” which can make it harder to explain why revenue rises at certain times.

---

Key recommendations / decision points highlighted

• When you have no historical data:
  • Prefer top-down or bottom-up, but ensure assumptions are defensible:
    • Top-down risk driver: market share/capture
    • Bottom-up risk driver: daily foot traffic/customer count
• When you have historical data:
  • Include growth fade (latest growth not fully sustainable)
  • Use a minimum floor to avoid unrealistic downside
• For fast-growing startups:
  • Run rate can be more relevant than long-run historical averages
• For faster/automated forecasting:
  • ETS works, but it’s less common in traditional financial modeling because it’s less explainable

---

Disclosures / disclaimers

• The video includes course promotion text (no explicit “not financial advice” disclaimer is shown in the provided subtitles).

---

Presenters / sources

• No presenter name provided in the subtitles.
• Source referenced: Excel built-in function forecast ETS.

---