PK ! W9O O [Content_Types].xmlPK ! ' ' _rels/.relsPK ! g;( ( xl/workbook.xmlPK ! h h xl/_rels/workbook.xml.relsPK !
xl/styles.xmlPK ! sB? ? xl/worksheets/sheet1.xmlExcel forecasting practice workbook
Companion file for 'How to Forecast in Excel Based on Historical Data' on Game of Excel.
Blue cells are sample inputs you can change. Black cells are formulas. Yellow cells are for you to fill in.
Sample data is made up for practice. It is not real sales data.
Sheets:
1. Sales - the 6-month sales table and the FORECAST.LINEAR and moving average examples
2. Accuracy - forecast vs actual for months 7 to 9, with MAPE, MAE and RMSE
3. Seasonal - 24 months of sample sales with FORECAST.ETS and its range
4. Population - a GROWTH example
5. Challenge - practice task, with the answers further down
FORECAST.LINEAR and FORECAST.ETS need Excel 2016 or later (Microsoft 365, 2019, 2021, 2024).
PK ! o; xl/worksheets/sheet2.xmlMonthSalesMethodFormula result
1120FORECAST.LINEAR, month 7_xlfn.FORECAST.LINEAR(A8,B2:B7,A2:A7)179.466667
2135FORECAST (older name), month 7FORECAST(A8,B2:B7,A2:A7)179.466667
31283-month moving averageAVERAGE(B5:B7)160.666667
4150TREND, month 7TREND(B2:B7,A2:A7,A8)179.466667
5162
6170Matches the article: 179.5 for the straight-line methods and 160.7 for the moving average.
7
PK ! I xl/worksheets/sheet3.xmlMonthActualForecastError %MetricResult
1120MAPE (average percent error)SUMPRODUCT(ABS((B8:B10-C8:C10)/B8:B10))/COUNT(B8:B10)0.018945
2135MAE (average error in units)SUMPRODUCT(ABS(B8:B10-C8:C10))/COUNT(B8:B10)3.51746
3128RMSE (big misses count more)SQRT(SUMXMY2(B8:B10,C8:C10)/COUNT(B8:B10))3.613756
4150
5162Months 1 to 6 build the forecast. Months 7 to 9 are held back to test it.
6170Expected: MAPE 1.89%, MAE 3.52, RMSE 3.61.
7175_xlfn.FORECAST.LINEAR(A8,$B$2:$B$7,$A$2:$A$7)179.466667ABS((B8-C8)/B8)0.025524
8192_xlfn.FORECAST.LINEAR(A9,$B$2:$B$7,$A$2:$A$7)189.552381ABS((B9-C9)/B9)0.012748
9196_xlfn.FORECAST.LINEAR(A10,$B$2:$B$7,$A$2:$A$7)199.638095ABS((B10-C10)/B10)0.018562
PK ! zNB B xl/worksheets/sheet4.xmlMonth startSales (sample)ForecastLower boundUpper boundCycle length Excel finds
45292100_xlfn.FORECAST.ETS.SEASONALITY(B2:B25,A2:A25)12
4532392
4535298Sample data: two years of made-up monthly sales with a December peak.
45383105Forecast column uses seasonality 12. Lower and upper bound show the 95% range.
45413110
45444118
45474122
45505120
45536115
45566125
45597150
45627190
45658110
45689101
45717108
45748115
45778121
45809129
45839134
45870132
45901126
45931137
45962164
45992207
46023_xlfn.FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,12)128.328181C26-_xlfn.FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95,12)126.841845C26+_xlfn.FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95,12)129.927825
46054_xlfn.FORECAST.ETS(A27,$B$2:$B$25,$A$2:$A$25,12)123.239694C27-_xlfn.FORECAST.ETS.CONFINT(A27,$B$2:$B$25,$A$2:$A$25,0.95,12)120.978156C27+_xlfn.FORECAST.ETS.CONFINT(A27,$B$2:$B$25,$A$2:$A$25,0.95,12)125.421165
46082_xlfn.FORECAST.ETS(A28,$B$2:$B$25,$A$2:$A$25,12)130.567875C28-_xlfn.FORECAST.ETS.CONFINT(A28,$B$2:$B$25,$A$2:$A$25,0.95,12)127.608042C28+_xlfn.FORECAST.ETS.CONFINT(A28,$B$2:$B$25,$A$2:$A$25,0.95,12)133.726058
46113_xlfn.FORECAST.ETS(A29,$B$2:$B$25,$A$2:$A$25,12)138.896055C29-_xlfn.FORECAST.ETS.CONFINT(A29,$B$2:$B$25,$A$2:$A$25,0.95,12)135.017974C29+_xlfn.FORECAST.ETS.CONFINT(A29,$B$2:$B$25,$A$2:$A$25,0.95,12)142.870127
46143_xlfn.FORECAST.ETS(A30,$B$2:$B$25,$A$2:$A$25,12)145.224236C30-_xlfn.FORECAST.ETS.CONFINT(A30,$B$2:$B$25,$A$2:$A$25,0.95,12)140.112401C30+_xlfn.FORECAST.ETS.CONFINT(A30,$B$2:$B$25,$A$2:$A$25,0.95,12)149.922037
46174_xlfn.FORECAST.ETS(A31,$B$2:$B$25,$A$2:$A$25,12)154.552416C31-_xlfn.FORECAST.ETS.CONFINT(A31,$B$2:$B$25,$A$2:$A$25,0.95,12)149.118375C31+_xlfn.FORECAST.ETS.CONFINT(A31,$B$2:$B$25,$A$2:$A$25,0.95,12)159.916913
PK ! x xl/worksheets/sheet5.xmlYearPopulationYear 6 with GROWTH
150000GROWTH(B2:B6,A2:A6,A7)57963.155086
251500Year 6 with FORECAST.LINEAR
353045_xlfn.FORECAST.LINEAR(A7,B2:B6,A2:A6)57797
454636
556275A town of 50,000 growing 3% a year. GROWTH expects about 57,963 for year 6.
6
PK ! o) ) xl/worksheets/sheet6.xmlQuarterUnitsTasks
1401. Forecast quarter 9 with FORECAST.LINEAR. Type your formula in E2.
2442. Forecast quarter 9 with a 3-quarter moving average. Type your formula in E3.
3473. Which answer fits a shop that grows every quarter? Write it in E4.
452
555
658Answers (scroll down after you try)
763
866
9
Answer 1_xlfn.FORECAST.LINEAR(A10,B2:B9,A2:A9)69.892857
Answer 2AVERAGE(B7:B9)62.333333
Answer 3FORECAST.LINEAR, because a moving average trails a rising trend.
PK ! W9O O [Content_Types].xmlPK ! ' ' _rels/.relsPK ! g;( ( xl/workbook.xmlPK ! h h % xl/_rels/workbook.xml.relsPK !
xl/styles.xmlPK ! sB? ? xl/worksheets/sheet1.xmlPK ! o; n xl/worksheets/sheet2.xmlPK ! I xl/worksheets/sheet3.xmlPK ! zNB B t) xl/worksheets/sheet4.xmlPK ! x = xl/worksheets/sheet5.xmlPK ! o) ) B xl/worksheets/sheet6.xmlPK 2K