Looking for help with a textbook case study. I tried to create the model in Excel and I just can't figure it out - I really want to know the steps to creating the forecast model so please include formulas and result screen shots.
An important part of planning manufacturing capacity is having a good forecast of sales. Elizabeth Burke is interested in forecasting sales of mowers in each marketing region to assess future changes in market share.
Develop a historical analogy, time series and regression (seasonality and trend) forecasting models for mower unit sales data.
|
Mower Unit Sales
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Month
|
NA
|
SA
|
Europe
|
Pacific
|
China
|
World
|
|
Jan-10
|
6000
|
200
|
720
|
100
|
0
|
7020
|
|
Feb-10
|
7950
|
220
|
990
|
120
|
0
|
9280
|
|
Mar-10
|
8100
|
250
|
1320
|
110
|
0
|
9780
|
|
Apr-10
|
9050
|
280
|
1650
|
120
|
0
|
11100
|
|
May-10
|
9900
|
310
|
1590
|
130
|
0
|
11930
|
|
Jun-10
|
10200
|
300
|
1620
|
120
|
0
|
12240
|
|
Jul-10
|
8730
|
280
|
1590
|
140
|
0
|
10740
|
|
Aug-10
|
8140
|
250
|
1560
|
130
|
0
|
10080
|
|
Sep-10
|
6480
|
230
|
1590
|
130
|
0
|
8430
|
|
Oct-10
|
5990
|
220
|
1320
|
120
|
0
|
7650
|
|
Nov-10
|
5320
|
210
|
990
|
130
|
0
|
6650
|
|
Dec-10
|
4640
|
180
|
660
|
140
|
0
|
5620
|
|
Jan-11
|
5980
|
210
|
690
|
140
|
0
|
7020
|
|
Feb-11
|
7620
|
240
|
1020
|
150
|
0
|
9030
|
|
Mar-11
|
8370
|
250
|
1290
|
140
|
0
|
10050
|
|
Apr-11
|
8830
|
290
|
1620
|
150
|
0
|
10890
|
|
May-11
|
9310
|
330
|
1650
|
130
|
0
|
11420
|
|
Jun-11
|
10230
|
310
|
1590
|
140
|
0
|
12270
|
|
Jul-11
|
8720
|
290
|
1560
|
150
|
0
|
10720
|
|
Aug-11
|
7710
|
270
|
1530
|
140
|
0
|
9650
|
|
Sep-11
|
6320
|
250
|
1590
|
150
|
0
|
8310
|
|
Oct-11
|
5840
|
250
|
1260
|
160
|
0
|
7510
|
|
Nov-11
|
4960
|
240
|
900
|
150
|
0
|
6250
|
|
Dec-11
|
4350
|
210
|
660
|
150
|
0
|
5370
|
|
Jan-12
|
6020
|
220
|
570
|
160
|
0
|
6970
|
|
Feb-12
|
7920
|
250
|
840
|
150
|
0
|
9160
|
|
Mar-12
|
8430
|
270
|
1110
|
160
|
0
|
9970
|
|
Apr-12
|
9040
|
310
|
1500
|
170
|
0
|
11020
|
|
May-12
|
9820
|
360
|
1440
|
160
|
0
|
11780
|
|
Jun-12
|
10370
|
330
|
1410
|
170
|
0
|
12280
|
|
Jul-12
|
9050
|
310
|
1440
|
160
|
0
|
10960
|
|
Aug-12
|
7620
|
300
|
1410
|
170
|
0
|
9500
|
|
Sep-12
|
6420
|
280
|
1350
|
180
|
0
|
8230
|
|
Oct-12
|
5890
|
270
|
1080
|
180
|
0
|
7420
|
|
Nov-12
|
5340
|
260
|
840
|
190
|
0
|
6630
|
|
Dec-12
|
4430
|
230
|
510
|
180
|
0
|
5350
|
|
Jan-13
|
6100
|
250
|
480
|
200
|
0
|
7030
|
|
Feb-13
|
8010
|
270
|
750
|
190
|
0
|
9220
|
|
Mar-13
|
8430
|
280
|
1140
|
200
|
0
|
10050
|
|
Apr-13
|
9110
|
320
|
1410
|
210
|
0
|
11050
|
|
May-13
|
9730
|
380
|
1340
|
190
|
0
|
11640
|
|
Jun-13
|
10120
|
360
|
1360
|
200
|
0
|
12040
|
|
Jul-13
|
9080
|
320
|
1410
|
200
|
0
|
11010
|
|
Aug-13
|
7820
|
310
|
1490
|
210
|
0
|
9830
|
|
Sep-13
|
6540
|
300
|
1310
|
220
|
0
|
8370
|
|
Oct-13
|
6010
|
290
|
980
|
210
|
0
|
7490
|
|
Nov-13
|
5270
|
270
|
770
|
220
|
0
|
6530
|
|
Dec-13
|
5380
|
260
|
430
|
230
|
0
|
6300
|
|
Jan-14
|
6210
|
270
|
400
|
200
|
0
|
7080
|
|
Feb-14
|
8030
|
280
|
750
|
190
|
0
|
9250
|
|
Mar-14
|
8540
|
300
|
970
|
210
|
0
|
10020
|
|
Apr-14
|
9120
|
340
|
1310
|
220
|
5
|
10995
|
|
May-14
|
9570
|
390
|
1260
|
200
|
16
|
11436
|
|
Jun-14
|
10230
|
380
|
1240
|
210
|
22
|
12082
|
|
Jul-14
|
9580
|
350
|
1300
|
230
|
26
|
11486
|
|
Aug-14
|
7680
|
340
|
1250
|
220
|
14
|
9504
|
|
Sep-14
|
6870
|
320
|
1210
|
220
|
15
|
8635
|
|
Oct-14
|
5930
|
310
|
970
|
230
|
11
|
7451
|
|
Nov-14
|
5260
|
300
|
650
|
240
|
3
|
6453
|
|
Dec-14
|
4830
|
290
|
300
|
230
|
1
|
5651
|