Ravindra BagaleCourses & study guides मराठी Track your progress

31. Forecasting in Excel

Practice and Answer Key: Forecast Weekly Orders for a Kolhapur Store and Test the Forecast

By Last updated:

Goal of this practice: Forecast with a straight line, test two methods on held-back weeks with MAE and MAPE, and choose a method honestly.

Suppose Ruhi Bagale has 8 weeks of weekly orders for a Kolhapur dark store in A1:B9 (Week 1 to 8, Orders). There is no weekly seasonality in weekly totals, so a straight line is a fair first model. All data is fictional practice data.

Week Orders
1 2410
2 2465
3 2530
4 2490
5 2575
6 2620
7 2600
8 2680

Exercise (one under the other):

  1. Forecast weeks 9 and 10 with FORECAST.LINEAR (or TREND).
  2. What is the weekly growth (SLOPE) and how well does the line fit (RSQ)?
  3. Holdout: using weeks 1 to 6 only, forecast weeks 7 and 8 with FORECAST.LINEAR.
  4. Second method: the "naive" forecast (weeks 7 and 8 = the week 6 value).
  5. Calculate MAE and MAPE of both methods on weeks 7 and 8.
  6. Which method would you use for weeks 9 and 10, and what is your forecast?

Expected result (check your numbers):

Check Expected
Forecast week 9 / week 10 2702.68 / 2737.44
SLOPE / RSQ 34.76 / 0.9122
Holdout linear forecasts (weeks 7, 8) 2649.00, 2687.29
Holdout naive forecasts 2620, 2620
Linear MAE / MAPE 28.14 / 1.08%
Naive MAE / MAPE 40.00 / 1.50%
Answer key (open after you try)
  1. =FORECAST.LINEAR(9,B2:B9,A2:A9) = 2702.68 and =FORECAST.LINEAR(10,B2:B9,A2:A9) = 2737.44 (or =TREND(B2:B9,A2:A9,{9;10})).
  2. =SLOPE(B2:B9,A2:A9) = 34.76 orders per week; =RSQ(B2:B9,A2:A9) = 0.9122, a good straight-line fit.
  3. =FORECAST.LINEAR(7,B2:B7,A2:A7) = 2649.00 and =FORECAST.LINEAR(8,B2:B7,A2:A7) = 2687.29.
  4. Both weeks = 2620 (=B7).
  5. Linear: MAE 28.14, MAPE 1.08%. Naive: MAE 40.00, MAPE 1.50%. Formulas: =AVERAGE(ABS(B8:B9-D8:D9)) and =AVERAGE(ABS((B8:B9-D8:D9)/B8:B9)) with the forecasts in D8:D9.
  6. The straight line had the lower error, so use it, re-fitted on all 8 weeks: about 2,703 orders in week 9 and 2,737 in week 10. With only two test weeks, re-check after the next weeks arrive.

Common mistakes:

What you see Why Fix
#N/A from FORECAST.LINEAR y and x ranges of different sizes Use B2:B9 with A2:A9 (same rows)
Holdout looks perfect Weeks 7 and 8 were included in the training range Fit on B2:B7 / A2:A7 only
MAPE shows 0.0092 Not formatted as a percentage Format the cell as % (0.92%)
FORECAST.ETS gives #NAME? Excel for the web, iPad, iPhone or Android Use FORECAST.LINEAR or open in desktop Excel

Ravindra Bagale's rule: test every forecast on the past before you trust it with the future.

Quick recap

  • A forecast needs a clean time series: one row per equal step, no duplicates, few gaps, at least two seasonal cycles.
  • FORECAST.LINEAR and TREND fit a straight line: good for steady growth, blind to weekly or yearly patterns.
  • FORECAST.ETS (AAA exponential smoothing) follows trend and seasonality; set seasonality yourself when you know it; not in Excel for the web, iOS or Android.
  • FORECAST.ETS.CONFINT gives the ± range; FORECAST.ETS.SEASONALITY and .STAT show what Excel detected.
  • Forecast Sheet (Data › Forecast, Excel for Windows) writes the same ETS formulas and a chart; trend + weekday offsets explains the pattern in every Excel.
  • Choose methods with a holdout test: MAE, MAPE, RMSE and bias against simple baselines.

Loading practice tasks...

  1. Train slope (Easy): Slope of B2:B9 on A2:A9, rounded to 3 decimals.
  2. Train intercept (Easy): Intercept of B2:B9 on A2:A9, rounded to 2 decimals.
  3. Week 9 forecast (Easy): Forecast for A10 using training rows, rounded to 1 decimal.
  4. Week 12 forecast (Medium): Forecast for A13 using training rows, rounded to 1 decimal.
  5. Week 9 error (Medium): B10 − week 9 forecast, rounded to 1 decimal.
  6. Test MAE (Medium): Average absolute error for rows 10 to 13 with intercept + slope × week, rounded to 2 decimals.
  7. Test MAPE (Hard): Average of |error| ÷ actual for rows 10 to 13, rounded to 4 decimals.
  8. Total test forecast (Hard): Sum of intercept + slope × weeks 9 to 12, rounded to 0 decimals.
  9. Week 16 forecast (all data) (Hard): Forecast for week 16 using B2:B13, rounded to 0 decimals.
  10. Good enough? (Hard): Is the test MAPE below 0.02? (TRUE or FALSE)

See all practice tasks in the Task Hub →