Question

1. Consider the applications for home mortgages data in the file of P12_04.xlsx.

1. Create a time series chart of the dat
2. Use simple exponential smoothing to forecast these data, using the default smoothing constant of 0.1
3. Calculate the three types of forecast errors, RMSE, MAE, and MAPE
4. Use the solver function in excel to optimize the smoothing constant in order to generate a minimum MAPE value.
5. Use multiple regression to develop an equation that can be used to predict future applications for home mortgages (hint: use dummy variables for the quarters and create a time variable for the quarter numbers)

Quarter	Year	Applications
1	1	96
2	1	114
3	1	112
4	1	81
1	2	97
2	2	103
3	2	120
4	2	99
1	3	105
2	3	110
3	3	117
4	3	96
1	4	74
2	4	94
3	4	100
4	4	96
1	5	95
2	5	122
3	5	113
4	5	100
1	6	102
2	6	96
3	6	116
4	6	98

Answer 1

A C F G H I J K (a) (b) (c) (d) (e) Create a time series chart of the dat 1 Use simple exponential smoothing to forecast these data, using the default smoothing constant of 0.1 Calculate the three types of forecast errors, RMSE, MAE, and MAPE Use the solver function in excel to optimize the smoothing constant in order to generate a minimum MAPE value. Use multiple regression to develop an equation that can be used to predict future applications for home mortgages Chint. use dummy variables for the quarters and create a time variable for the quarter numbers) 2 3 4 5 6 7 Chart Title 130 10 120 11 110 12 13 100 14 90 15 16 80 17 70 18 19 60 0 1 2 3 4 5 6 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 20 21 .. .. eccE LO LO

22 Absolute 0.10), Ft Deviation 0.00 18.00 14.20 18.22 0.40 5.64 22.08 Squared Exponential Absolute 23 24 Applications, At Smoothing(a 96 Error Error 0.00 0.16 0.00 324.00 201.64 331.97 0.16 96.00 25 96.00 114 26 97.80 112 0.13 27 0.22 81 99.22 28 97.40 97.36 97.92 0.00 97 29 0.05 103 120 99 105 31.83 487.42 30 0.18 31 100.13 1.13 1.28 0.01 32 100.02 100.52 101.46 103.02 4.98 9.48 15.54 7.02 28.32 5.48 0.05 24.83 33 89.96 241.37 49.25 801.78 30.08 110 0.09 34 0.13 117 35 96 0.07 36 74 102.32 0.38 37 99.48 0.06 94 38 100 98.94 1.06 1.13 0.01 39 99.04 98.74 98.36 100.73 3.04 3.74 23.64 12.27 9.25 13.97 558.65 96 95 122 0.03 40 0.04 41 0.19 42 113 150.61 0.11 43

3.82 0.06 33.45 218.89 21.95 0.02 101.95 101.76 101.78 100 1.95 44 0.24 5.78 14.79 4.68 102 0.00 45 0.06 96 46 101.21 116 0.13 47 102.68 98 0.05 48 0.09 9.11% 9.24 MSE MAD 151.14 MAE 49 0.1 RMSE 12.29 MAPE 9.11% 50 51 52 othing: E- aA(-a)F Mean Absolute Deviati on 53 ΣΙ4-| 54 t-1 t-l 55 MAD a: Smoothing constant E: Foracast for pericdi 56 Root Mean Square Error 57 58 (4-F) 59 FLast period forecast t-l RMSE 60 n ALast period actual value 61 Mean Absolute Percentage Error 62 4-F Σ 63 64 t-l x100 MAPE 65 8