Problem
Canton Supplies, Inc., is a service firm that employs approximately 100 people. Because of the necessity of meeting monthly cash obligations, the chief financial officer wants to develop a forecast of monthly cash requirements. Because of a recent change in equipment and operating policy, only the past seven months of data are considered relevant. The change in operations has had a great impact on cash flow. What forecasting model do you recommend? Use the Moving Average and Exponential Smoothing Excel templates or other Excel tools to help you answer this question.
|
Cash Required
|
Cash Required
|
|
Month
|
($1,000)
|
Month
|
($1,000)
|
|
1
|
193
|
5
|
229
|
|
2
|
208
|
6
|
238
|
|
3
|
216
|
7
|
201
|
|
4
|
270
|
|
|
Find the best number of months to use in a moving average forecast based on MSE. Do not round intermediate calculations.
|
Number of months
|
MSE
|
|
1
|
|
|
2
|
|
|
3
|
|
|
4
|
|
The -Select-2-month moving average3-month moving average4-month moving averageItem 4 model is the best.
Find the best single exponential smoothing model by evaluating the MSE from 0.1 to 0.9, in increments of 0.1. Do not round intermediate calculations.
|
Alpha
|
MSE
|
|
0.1
|
|
|
0.2
|
|
|
0.3
|
|
|
0.4
|
|
|
0.5
|
|
|
0.6
|
|
|
0.7
|
|
|
0.8
|
|
|
0.9
|
|
The model based on a smoothing constant of -Select-0.10.20.30.40.50.60.70.80.9Item 14 is the best.
Build the linear regression model and calculate the MSE. Do not round intermediate calculations.
The regression model is Cash Required = + (Month). The MSE is.
How does the best single exponential smoothing model compare with the best moving average model and the linear regression model?
The -Select-single exponential smoothing moving average linear regression Item 18 model has the lowest MSE.