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.

**We have the best researchers, writers and editors for all your assignments****We deliver what we promise and we deliver it fast!**

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.