BUS 370
FORECASTING CASE
1. Cases for BUS 370 are take-home, open-book, open-notes, open-library, etc. You may consult any written materials other than another (past or present) team’s case or work papers. Please do not discuss any aspect of the case with anyone except your partners, my staff, or me. To collaborate or seek other assistance is a breach of academic integrity and is viewed as a serious offense by the University.
2. Please submit one solution package per team with its answers neatly worked out in detail. To earn partial credit, you must show all work (including Excel input).
3. For each question requiring a new Excel run, please submit the following:
a. A printout of the input data.
b. A printout of all reports that support your answers.
Please label each accompanying printouts with the corresponding question number(s) and insert comments on them to indicate the sources of your answers. There is no need to submit printouts that are not supportive of your final answers (e.g., runs that bombed, runs with incorrect input, etc.).
4. Please be neat.Sloppiness, disorganization, spelling, etc. may, in the aggregate, result in apoint reduction of up to the equivalent of one letter grade.
5. Please submit your solution package for this case at the beginning of class on the scheduled date. In fairness to the teams that submit their solution package on time, unexcused lateness will result in a loss of one letter grade per late-day or fraction thereof.
6. Some very important advice regarding teamwork:
a. Past experience shows that team effectiveness and efficiency are greatly enhanced if partners work independently first and then convene to share individual results. Please proceed in this fashion.
b. It is critically important to maintain an atmosphere that fosters open, honest communication among team members. So, please establish clear expectations for individual performance on each case assignment and always discuss deviations in individual performance as work progresses.
7. GOOD LUCK AND HAPPY LEARNING!
Bobs Bank & Trust (BB&T) was established in Year 1 to provide residents of Saverville, ME with an alternative to the traditional banking institutions that are located in a larger neighboring town. The president, Amanda, is proud of its ability to supply their small, but ever-growing number of depositors with a broad assortment of banking services. Amanda feels that careful planning and a sound growth strategy have been the key to BB&Ts success. Deposits have increased slowly over the last four and one half years. Recognizing that overestimation and underestimation do not compensate for each other, Amanda insists on having a detailed plan that involves creating short-term forecasts of deposits that are accurate on a month-by-month basis. Every month, she reexamines the past deposit data at BB&T and prepares a new forecast of deposits for each of the next six months. Amanda re-forecasts every month when the new actuals become available because she believes that they are apt to influence the projections for the next six months.
The historical data that she needs to prepare the upcoming six-month forecast are the monthly savings, checking, and total deposits for the 54 months from January, Year 1 to June, Year 5. These are provided in Table 1.
Table 1: Deposits ($000) from January, Year 1 to June, Year 5
Deposits
Deposits
Month
Savings
Checking
Total
Month
Savings
Checking
Total
1
63.06
26.49
89.55
31
87.64
40.34
127.98
2
76.38
32.29
108.67
32
89.36
38.53
127.89
3
64.11
27.90
92.01
33
86.83
37.83
124.66
4
86.58
40.36
126.94
34
88.26
37.27
125.53
5
82.28
31.74
114.02
35
79.48
37.12
116.60
6
85.99
39.15
125.14
36
107.43
43.14
150.57
7
82.05
37.28
119.33
37
71.92
31.21
103.13
8
82.86
32.43
115.29
38
85.99
37.45
123.44
9
79.76
37.14
116.90
39
73.16
30.24
103.40
10
79.38
31.14
110.52
40
95.16
43.57
138.73
11
72.65
33.69
106.34
41
89.76
38.55
128.31
12
100.12
40.55
140.67
42
95.89
42.75
138.64
13
63.71
24.33
88.04
43
90.54
35.89
126.43
14
78.23
34.57
112.80
44
92.01
36.29
128.30
15
65.55
29.14
94.69
45
89.37
37.11
126.48
16
89.06
37.37
126.43
46
92.17
35.21
127.38
17
84.28
38.77
123.05
47
81.80
33.05
114.85
18
88.74
36.37
125.11
48
110.89
50.64
161.53
19
85.08
35.48
120.56
49
76.61
32.27
108.88
20
84.79
33.28
118.07
50
89.63
39.03
128.66
21
82.21
38.95
121.16
51
75.45
33.68
109.13
22
84.50
37.02
121.52
52
98.21
37.25
135.46
23
77.76
29.40
107.16
53
88.47
41.35
129.82
24
103.45
44.50
147.95
54
94.67
40.77
135.44
25
70.50
26.33
96.83
26
83.46
36.14
119.60
27
71.22
29.25
100.47
28
92.44
38.41
130.85
29
88.40
36.67
125.07
30
92.29
38.83
131.12
Note: For purposes of data entry checking, your three means and standard deviations should match these:
Savings
Checking
Total
Parameter
Deposits
Deposits
Deposits
Mean
84.6591
36.0280
120.6870
Std. Dev.
10.3761
4.9909
14.9855
Use your understanding of forecasting to help Amanda deal with the forecasting issues that follow.
1. For each of the three 54-point time series (savings, checking, and total deposits for January, Year 1 through June, Year 5), make two projections for the rest of Year 5 (July through December), one using simple linear regression and the other using the exponential smoothing model with= 0.2 . (NOTE: In order to make projections beyond month 55 using the exponential smoothing model, assume the previous months actual was what you forecasted for that particular time period. So, for month 56, you should assume the actual deposit was exactly what you predicted for the 55th month.)
a) Please use the format of Table 2 below to show your forecasts for the next six months for each of the forecasts you made (to four digits to the right of the decimal). Comment on any particular patterns you notice.
Table 2: Forecasts for Months 55 60
Month
Savings Deposits
Checking Deposits
Total Deposits
LR
ES
LR
ES
LR
ES
55
56
57
58
59
60
b) For purposes of comparison, using the format of Table 3 below, please tabulate the six sets of values for the four goodness-of-fit measures of CFE, MAD, MSE, and MAPE (to two digits to right of the decimal). For each of the three time series, which model (linear regression or exponential smoothing) do you prefer? Please defend your preferences.
Table 3: Goodness-of-Fit Comparisons
Goodness-of-Fit
Savings Deposits
Checking Deposits
Total Deposits
Measure
LR
ES
LR
ES
LR
ES
CFE
MAD
MSE
MAPE
2. Please conduct three ex-post forecast analyses using simple linear regression. That is, repeat the runs using only 48 months of data and make projections for months 49-54. Then, compare in tabular form (using the format of Table 4) these ex-post forecasts to the actual deposits for months 49-54. Look at the differences in terms of their absolute ($) and relative (%) magnitudes, and interpret your results.
Table 4: Ex-Post Analysis
Savings Deposits ($000)
Checking Deposits ($000)
Total Deposits ($000)
$
%
$
%
$
%
Month
ACTUAL
FORECAST
DIFF.
DIFF.
ACTUAL
FORECAST
DIFF.
DIFF.
ACTUAL
FORECAST
DIFF.
DIFF.
49
50
51
52
53
54
Total
3. Now, for total deposits only, please transform your 54-point monthly time series into an 18-point quarterly time series. Now use the multivariate linear regression with (a) QTRNUM (having values 1-18), (b) three dummy variables (Q1, Q2, and Q3) to capture quarterly seasonality, and (c) ADS as five independent variables to build a model for forecasting total deposits for quarter 19. ADS refers to the number of times Amanda has placed an advertisement in the local daily paper during each quarter. The complete data set is given in Table 5
Table 5: Quarterly Total Deposits, Quarter Numbers, and Number of Advertisements
Total Deposits ($000)
per Quarter
QTRNUM
ADS
290.23
1
60
366.10
2
49
351.52
3
61
357.53
4
59
295.53
5
60
374.59
6
45
359.79
7
61
376.63
8
59
316.90
9
60
387.04
10
45
380.53
11
60
392.70
12
61
329.97
13
59
405.68
14
52
381.21
15
61
403.76
16
59
346.67
17
60
400.72
18
34
In making the forecast for quarter 19, assume that Amanda plans to advertise 60 times in that period.
a. Please provide the algebraic expression for the full regression equation using the variable names QTRNUM, Q1, Q2, Q3, and ADS. Use this equation to find the forecasted deposits for quarter 19.
b. What is your best estimate of the additional total deposits that result from each additional advertisement? Please explain.
c. What is your best estimate of the slope of the annual trend effect in total deposits? Please explain.
d. Among the first three quarters of each year, which one appears to have the most pronounced effect on total deposits? Please explain.
e. Please interpret the meaning of the R-squared for this multiple regression model.
f. In examining the p-values, do all of the independent variables belong in the regression model?
BUS 370
CONFIDENTIAL TEAM BALLOT FORECASTING CASE
YOUR NAME: ______________________, _______________________
(Last) (First)
TEAM MEMBERS NAMES
POINTS
Last
First
1
2
3
(4)
(5)
(6)
Total Points
100
NOTE: Please include yourself when distributing your 100 points.
