Reference no: EM132998380
The Midwest Computer Company serves a large number of businesses in the Great Lakes region. The company sells supplies and replacements and performs service on all computers sold through seven sales offices. Many items are stocked, so close inventory control is necessary to assure customers of efficient service. Recently, business has been increasing, and management is concerned about stockouts. A forecasting method is needed to estimate requirements several months in advance to estimate requirements several months in advance so that adequate replenishment quantities can be purchased. An example of the sales growth experienced during the last 50 months is the growth in demand for item EP-37, a laser printer cartridge, shown in Table 3.
Month / EP-37 Sales / Leases / Month / EP-37 Sales / Leases
1 / 80 / 32 / 26 / 1296 / 281
2 / 132 / 29 / 27 / 1199 / 298
3 / 143 / 32 / 28 / 1267 / 314
4 / 180 / 54 / 29 / 1300 / 323
5 / 200 / 53 / 30 / 1370 / 309
6 / 168 / 89 / 31 / 1489 / 343
7 / 212 / 74 / 32 / 1499 / 357
8 / 254 / 93 / 33 / 1669 / 353
9 / 397 / 120 / 34 / 1716 / 360
10 / 385 / 113 / 35 / 1603 / 370
11 / 472 / 147 / 36 / 1812 / 386
12 / 397 / 126 / 37 / 1817 / 389
13 / 476 / 138 / 38 / 1798 / 399
14 / 699 / 145 / 39 / 1873 / 409
15 / 545 / 160 / 40 / 1923 / 410
16 / 837 / 196 / 41 / 2028 / 413
17 / 743 / 180 / 42 / 2049 / 439
18 / 722 / 197 / 43 / 2084 / 454
19 / 735 / 203 / 44 / 2083 / 441
20 / 838 / 223 / 45 / 2121 / 470
21 / 1057 / 247 / 46 / 2072 / 469
22 / 930 / 242 / 47 / 2262 / 490
23 / 1085 / 234 / 48 / 2371 / 496
24 / 1090 / 254 / 49 / 2309 / 509
25 / 1218 / 271 / 50 / 2422 / 522
For part a), it is believed that sales is a function of time; please use Excel for both parts a) and b).
a) Develop a trend projection with regression solution using Excel. Forecast demand for month 51.
b) A consultant to Midwest's management suggested that new office building leases would be a good leading indicator for company sales. The consultant quoted a recent university study finding that new office building leases preced office equipment and supply sales by 3 months. According to the study findings, leases in month 1 would affect sales in month 4, leases in month 2 would affect sales in month 5, and so on. Use Excel's linear regression module to develop a forecasting model for sales, with leases as the independent variable. Forecast sales for month 51.
c) Which of the two models provides better forecasts? Explain.