CHAPTER 12 FORECASTING
Period #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Day
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sales
$6,050
$7,200
$4,800
$3,200
$4,700
$7,300
$6,750
$6,200
$7,560
$5,140
$3,340
$5,000
$7,610
$7,150
$6,500
$7,850
$5,200
$3,590
$5,260
$8,010
$7,600
CHAPTER 12 FORECASTING
Period #
Day
From Regression
Table
Sales/Trend
Sales
Trend Value
Ratio
$5,523
$5,571
$5,619
$5,666
$5,714
$5,762
$5,810
$5,857
$5,905
$5,953
$6,000
$6,048
$6,096
$6,144
$6,191
$6,239
$6,287
$6,335
$6,382
$6,430
$6,478
$6,526
$6,573
$6,621
$6,669
$6,717
$6,764
$6,812
1.095
1.292
0.854
0.565
0.823
1.267
1.162
1.059
1.280
0.863
0.557
0.827
1.248
1.164
1.050
1.258
0.827
0.567
0.824
1.246
1.173
1
Sunday
$6,050
2
3
4
5
6
7
8
9
10
11
12
13
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
$7,200
$4,800
$3,200
$4,700
$7,300
$6,750
$6,200
$7,560
$5,140
$3,340
$5,000
$7,610
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
$7,150
$6,500
$7,850
$5,200
$3,590
$5,260
$8,010
$7,600
28
Seasonal Indices
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
1.068
1.277
0.848
0.563
0.824
1.254
1.166
Trend * Seasonal Index
(Sales - Reverse
Forecast)^2
Seasonal lndex
* Trend Value
Seasonal Index
Reverse Forecast
Squared Error
Forecast
1.068
1.277
0.848
0.563
0.824
1.254
1.166
1.068
1.277
0.848
0.563
0.824
1.254
1.166
1.068
1.277
0.848
0.563
0.824
1.254
1.166
1.068
1.277
0.848
0.563
0.824
1.254
1.166
$5,898
$7,114
$4,766
$3,188
$4,711
$7,223
$6,776
$6,255
$7,540
$5,050
$3,376
$4,986
$7,642
$7,165
$6,612
$7,967
$5,333
$3,564
$5,262
$8,061
$7,555
$23,044
$7,440
$1,142
$134
$120
$5,866
$658
$3,033
$381
$8,155
$1,328
$183
$1,048
$237
$12,534
$13,741
$17,738
$651
$4
$2,634
$2,011
$6,969
$8,394
$5,617
$3,753
$5,538
$8,480
$7,945
TREND LINE
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.192952573
R Square
0.037230696
Adjusted R Square
-0.013441373
Standard Error
1545.481756
Observations
21
ANOVA
df
Regression
Residual
Total
Intercept
Period #
1
19
20
Coefficients
5475.333333
47.74025974
SS
MS
F
Significance F
1754931.948 1754931.948 0.734738022
0.40203447
45381763.29 2388513.857
47136695.24
Standard Error
t Stat
699.3401511 7.829284969
55.69529647 0.857168608
P-value
2.30542E-07
0.40203447
Lower 95%
Upper 95% Lower 95.0% Upper 95.0%
4011.597575 6939.069092 4011.597575 6939.069092
-68.83133548 164.311855 -68.83133548
164.311855
$9,000
$8,000
$7,000
$6,000
$5,000
$4,000
$3,000
$2,000
$1,000
$0
0
5
10
Actual Sales History
15
Reverse Forecast
20
Forecast
25
Linear (Actual Sales History)
30
Period #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Day
Sales
Sunday
$6,050
Monday
$7,200
Tuesday
$4,800
Wednesday $3,200
Thursday
$4,700
Friday
$7,300
Saturday
$6,750
Sunday
$6,200
Monday
$7,560
Tuesday
$5,140
Wednesday $3,340
Thursday
$5,000
Friday
$7,610
Saturday
$7,150
Sunday
$6,500
Monday
$7,850
Tuesday
$5,200
Wednesday $3,590
Thursday
$5,260
Friday
$8,010
Saturday
$7,600
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Seasonal Indices
Sunday
1.068
Monday
1.277
Tuesday
0.848
Wednesday
0.563
Thursday
0.824
Friday
1.254
Saturday
1.166
CHAPTER 12 BUSINESS FORECASTING AND GRADING RUBRIC
A brewery runs three 8-hour shifts. The most experienced workers are on the day shift,
moderately experienced workers are on the evening shift, and the least experienced workers are
on the midnight shift. New equipment was installed in June, and the Production Manager hopes
that each shift will improve its productivity levels. The brewery computes a proprietary
production level metric, and below are the data for the average productivity levels since May
using that metric.
Day
Evening
Midnight
May
10
9.8
9.7
June
12
11
10.2
July
12.2
11
10.2
August
13
12.5
11.1
Compute the forecast for the average productivity levels for each shift in September. Compute
using the full time series method based only on the above information.
Solve the following using Excel.
1. What is the equation of the trend line for the model. Write the equation here.
2 pts.
2. If there are seasonal indices, write them here (season and corresponding index). If not, then
briefly explain below why there are no seasonal indices in this problem. 3 pts.
3. What is the forecast for the “evening shift” time frame for the September. Write your answer
below. 1 pt.
4. What is the error factor in the model. Write that value here. 1 pt.
5. Develop a graph showing plots of the original data, the regression line through that data, the
forecast of the actual data, and the forecast of the next month. 2 pts.
6. Why is it advisable to perform a “reverse forecast.” 1 pt.
7. Suppose you are briefing this forecast model to a senior manager in your company. Suppose the
senior manager asks you for a forecast for December, based on your model. Describe your response
to this request. Do not actually calculate an answer; just describe either what you would do to
respond or how you would respond. 2 pts.
Purchase answer to see full
attachment