Using Excel. Please provide detailed steps. 1. Create a 2 month moving average a
ID: 2931653 • Letter: U
Question
Using Excel. Please provide detailed steps.
1. Create a 2 month moving average and an exponential smoothing forecast (use =.5) to predict the 30 year fixed rate for 7/2/2010.
2. Compute the MAD, MSE and MAPE? Which forecast is most accurate? How could you improve that forecast?
6/27/2008 6.45 6.04 5.27
7/4/2008 6.35 5.92 5.17
7/11/2008 6.37 5.91 5.17
7/18/2008 6.26 5.78 5.1
7/25/2008 6.63 6.18 5.49
8/1/2008 6.52 6.07 5.27
8/8/2008 6.52 6.1 5.22
8/15/2008 6.52 6.07 5.18
8/22/2008 6.47 6 5.29
8/29/2008 6.4 5.93 5.33
9/5/2008 6.35 5.9 5.15
9/12/2008 5.93 5.54 5.21
9/19/2008 5.78 5.35 5.03
9/26/2008 6.09 5.77 5.16
10/3/2008 6.1 5.78 5.12
10/10/2008 5.94 5.63 5.15
10/17/2008 6.46 6.14 5.16
10/24/2008 6.04 5.72 5.23
10/31/2008 6.46 6.19 5.38
11/7/2008 6.2 5.88 5.25
11/14/2008 6.14 5.81 5.33
11/21/2008 6.04 5.73 5.29
11/28/2008 5.97 5.74 5.18
12/5/2008 5.53 5.33 5.02
12/12/2008 5.47 5.2 5.09
12/19/2008 5.19 4.92 4.94
12/26/2008 5.14 4.91 4.95
1/9/2009 5.01 4.62 4.95
1/16/2009 4.96 4.65 4.89
1/23/2009 5.12 4.8 4.92
1/30/2009 5.1 4.8 4
2/6/2009 5.25 4.92 4.92
2/13/2009 5.16 4.81 4.94
2/20/2009 5.04 4.68 4.8
2/27/2009 5.07 4.68 4.81
3/6/2009 5.15 4.72 4.86
3/13/2009 5.03 4.64 4.8
3/20/2009 4.98 4.61 4.91
3/27/2009 4.85 4.58 4.85
4/3/2009 4.78 4.52 4.75
4/10/2009 4.87 4.54 4.83
4/17/2009 4.82 4.48 4.91
4/24/2009 4.8 4.48 4.82
5/1/2009 4.78 4.48 4.77
5/8/2009 4.84 4.51 4.78
5/15/2009 4.86 4.52 4.71
5/22/2009 4.82 4.5 4.82
5/29/2009 4.91 4.53 4.69
6/5/2009 5.29 4.79 4.81
6/12/2009 5.59 5.06 5.04
6/19/2009 5.38 4.89 4.95
6/26/2009 5.42 4.87 4.93
7/3/2009 5.32 4.77 4.94
7/10/2009 5.2 4.69 4.82
7/17/2009 5.14 4.63 4.76
7/24/2009 5.2 4.68 4.77
7/31/2009 5.25 4.69 4.8
8/7/2009 5.22 4.63 4.78
8/14/2009 5.29 4.68 4.72
8/21/2009 5.12 4.56 4.69
8/28/2009 5.14 4.58 4.69
9/4/2009 5.08 5.54 4.62
9/11/2009 5.07 4.5 4.64
9/18/2009 5.04 4.47 4.58
9/25/2009 5.04 4.46 4.52
10/2/2009 4.94 4.36 4.49
10/9/2009 4.87 4.33 4.53
10/16/2009 4.92 4.37 4.6
10/23/2009 5 4.43 4.54
10/30/2009 5.03 4.46 4.57
11/6/2009 4.98 4.4 4.47
11/13/2009 4.91 4.36 4.46
11/20/2009 4.83 4.32 4.35
11/27/2009 4.78 4.29 4.35
12/4/2009 4.71 4.27 4.25
12/11/2009 4.81 4.32 4.24
12/18/2009 4.94 4.38 4.34
12/25/2009 5.05 4.45 4.38
1/1/2010 5.14 4.54 4.33
1/8/2010 5.09 4.5 4.31
1/15/2010 5.06 4.45 4.39
1/22/2010 4.99 4.4 4.32
1/29/2010 4.98 4.39 4.29
2/5/2010 5.01 4.4 4.22
2/12/2010 4.97 4.34 4.33
2/19/2010 4.93 4.33 4.23
2/26/2010 5.05 4.4 4.15
3/5/2010 4.97 4.33 4.27
3/12/2010 4.95 4.32 4.22
3/19/2010 4.96 4.33 4.12
3/26/2010 4.99 4.34 4.2
4/2/2010 5.08 4.39 4.05
4/9/2010 5.21 4.52 4.14
4/16/2010 5.07 4.4 4.13
4/23/2010 5.07 4.39 4.22
4/30/2010 5.06 4.39 4.25
5/7/2010 5 4.36 4.07
5/14/2010 4.93 4.3 4.02
5/21/2010 4.84 4.24 4
5/28/2010 4.78 4.21 3.95
6/4/2010 4.79 4.2 3.95
6/11/2010 4.72 4.17 3.91
6/18/2010 4.75 4.2 3.82
6/25/2010 4.69 4.13 3.77
Mortgage Rates Fixed Rate Fixed Rate Adjustable Rate Week Ending 30 year 15 year 1 yearExplanation / Answer
1)
2)
2 mo Moving average seems more accurate based on the MAD.
Week Ending 30 year fixed rate 2-mo MA Exp Smoothing Error_2-mo MA Absolute error_2-mo MA Squared error_2-mo MA Absolute % error_2-mo MA Error_Exp Smoothing Absolute error_Exp Smoothing Squared error_Exp Smoothing Absolute % error_Exp Smoothing 6/27/2008 6.45 6.45 0 0 0 0 07/04/08 6.35 6.45 -0.1 0.1 0.01 0.001574803 07/11/08 6.37 6.4 -0.03 0.03 0.0009 0.000141287 7/18/2008 6.26 6.385 -0.125 0.125 0.015625 0.002496006 7/25/2008 6.63 6.45 6.3225 0.18 0.1775 0.03150625 0.026772247 0.3075 0.3075 0.09455625 0.014261878 08/01/08 6.52 6.46 6.47625 0.07 0.065 0.004225 0.009969325 0.04375 0.04375 0.001914062 0.000293568 08/08/08 6.52 6.46 6.498125 0.06 0.05875 0.003451563 0.009010736 0.021875 0.021875 0.000478516 7.3392E-05 8/15/2008 6.52 6.46 6.5090625 0.06 0.06125 0.003751562 0.009394172 0.0109375 0.0109375 0.000119629 1.8348E-05 8/22/2008 6.47 6.42 6.51453125 0.05 0.0525 0.00275625 0.008114374 -0.04453125 0.04453125 0.001983032 0.000306496 8/29/2008 6.4 6.31 6.492265625 0.09 0.08875 0.007876563 0.013867188 -0.092265625 0.092265625 0.008512946 0.001330148 09/05/08 6.35 6.26 6.446132813 0.09 0.0925 0.00855625 0.014566929 -0.096132813 0.096132813 0.009241518 0.001455357 09/12/08 5.93 6.21 6.398066406 -0.28 0.275 0.075625 0.046374368 -0.468066406 0.468066406 0.219086161 0.03694539 9/19/2008 5.78 6.13 6.164033203 -0.35 0.3525 0.12425625 0.060986159 -0.384033203 0.384033203 0.147481501 0.025515831 9/26/2008 6.09 6.13 5.972016602 -0.04 0.04125 0.001701562 0.006773399 0.117983398 0.117983398 0.013920082 0.002285728 10/03/08 6.1 6.09 6.031008301 0.01 0.01375 0.000189062 0.002254098 0.068991699 0.068991699 0.004759855 0.000780304 10/10/08 5.94 6.10 6.06550415 -0.16 0.16 0.0256 0.026936027 -0.12550415 0.12550415 0.015751292 0.002651733 10/17/2008 6.46 6.13 6.002752075 0.33 0.32625 0.106439062 0.050503096 0.457247925 0.457247925 0.209075665 0.032364654 10/24/2008 6.04 6.18 6.231376038 -0.14 0.13875 0.019251563 0.022971854 -0.191376038 0.191376038 0.036624788 0.006063707 10/31/2008 6.46 6.17 6.135688019 0.29 0.2875 0.08265625 0.044504644 0.324311981 0.324311981 0.105178261 0.016281465 11/07/08 6.2 6.16 6.297844009 0.04 0.04375 0.001914063 0.007056452 -0.097844009 0.097844009 0.00957345 0.001544105 11/14/2008 6.14 6.11 6.248922005 0.03 0.035 0.001225 0.005700326 -0.108922005 0.108922005 0.011864003 0.001932248 11/21/2008 6.04 5.98 6.194461002 0.06 0.05875 0.003451562 0.009726821 -0.154461002 0.154461002 0.023858201 0.003950033 11/28/2008 5.97 5.88 6.117230501 0.10 0.095 0.009025 0.015912898 -0.147230501 0.147230501 0.02167682 0.003630958 12/05/08 5.53 5.71 6.043615251 -0.18 0.18 0.0324 0.032549729 -0.513615251 0.513615251 0.263800626 0.047703549 12/12/08 5.47 5.56 5.786807625 -0.09 0.09125 0.008326562 0.016681901 -0.316807625 0.316807625 0.100367071 0.018348642 12/19/2008 5.19 5.41 5.628403813 -0.22 0.22375 0.050064062 0.043111753 -0.438403813 0.438403813 0.192197903 0.037032351 12/26/2008 5.14 5.30 5.409201906 -0.16 0.15875 0.025201563 0.030885214 -0.269201906 0.269201906 0.072469666 0.014099157 01/09/09 5.01 5.19 5.274600953 -0.18 0.18 0.0324 0.035928144 -0.264600953 0.264600953 0.070013664 0.013974783 1/16/2009 4.96 5.16 5.142300477 -0.20 0.195 0.038025 0.039314516 -0.182300477 0.182300477 0.033233464 0.006700295 1/23/2009 5.12 5.12 5.051150238 0.00 0.00375 1.40625E-05 0.000732422 0.068849762 0.068849762 0.00474029 0.000925838 1/30/2009 5.1 5.10 5.085575119 0.00 0.0025 6.25E-06 0.000490196 0.014424881 0.014424881 0.000208077 4.07994E-05 02/06/09 5.25 5.09 5.09278756 0.16 0.16125 0.026001563 0.030714286 0.15721244 0.15721244 0.024715751 0.004707762 2/13/2009 5.16 5.11 5.17139378 0.05 0.05375 0.002889063 0.010416667 -0.01139378 0.01139378 0.000129818 2.51586E-05 2/20/2009 5.04 5.12 5.16569689 -0.08 0.075 0.005625 0.014880952 -0.12569689 0.12569689 0.015799708 0.003134863 2/27/2009 5.07 5.10 5.102848445 -0.03 0.0275 0.00075625 0.005424063 -0.032848445 0.032848445 0.00107902 0.000212825 03/06/09 5.15 5.07 5.086424222 0.08 0.08375 0.007014062 0.016262136 0.063575778 0.063575778 0.004041879 0.000784831 3/13/2009 5.03 5.01 5.118212111 0.02 0.0225 0.00050625 0.004473161 -0.088212111 0.088212111 0.007781377 0.001546993 3/20/2009 4.98 4.97 5.074106056 0.01 0.00875 7.65625E-05 0.001757028 -0.094106056 0.094106056 0.00885595 0.001778303 3/27/2009 4.85 4.94 5.027053028 -0.09 0.09375 0.008789063 0.019329897 -0.177053028 0.177053028 0.031347775 0.006463459 04/03/09 4.78 4.91 4.938526514 -0.13 0.13 0.0169 0.027196653 -0.158526514 0.158526514 0.025130656 0.005257459 04/10/09 4.87 4.86 4.859263257 0.01 0.00625 3.90625E-05 0.001283368 0.010736743 0.010736743 0.000115278 2.3671E-05 4/17/2009 4.82 4.84 4.864631628 -0.02 0.02 0.0004 0.004149378 -0.044631628 0.044631628 0.001991982 0.000413274 4/24/2009 4.8 4.83 4.842315814 -0.03 0.025 0.000625 0.005208333 -0.042315814 0.042315814 0.001790628 0.000373048 05/01/09 4.78 4.82 4.821157907 -0.04 0.04125 0.001701562 0.008629707 -0.041157907 0.041157907 0.001693973 0.000354388 05/08/09 4.84 4.84 4.800578954 0.00 0.0025 6.25E-06 0.000516529 0.039421046 0.039421046 0.001554019 0.000321078 5/15/2009 4.86 4.89 4.820289477 -0.03 0.03 0.0009 0.00617284 0.039710523 0.039710523 0.001576926 0.00032447 5/22/2009 4.82 4.99 4.840144738 -0.17 0.16625 0.027639062 0.034491701 -0.020144738 0.020144738 0.00040581 8.4193E-05 5/29/2009 4.91 5.06 4.830072369 -0.15 0.14875 0.022126563 0.030295316 0.079927631 0.079927631 0.006388426 0.001301105 06/05/09 5.29 5.14 4.870036185 0.15 0.15125 0.022876563 0.028591682 0.419963815 0.419963815 0.176369606 0.03334019 06/12/09 5.59 5.20 5.080018092 0.39 0.39125 0.153076563 0.069991055 0.509981908 0.509981908 0.260081546 0.046526216 6/19/2009 5.38 5.24 5.335009046 0.14 0.13875 0.019251563 0.025789963 0.044990954 0.044990954 0.002024186 0.000376243 6/26/2009 5.42 5.28 5.357504523 0.14 0.13875 0.019251563 0.025599631 0.062495477 0.062495477 0.003905685 0.000720606 07/03/09 5.32 5.32 5.388752262 0.00 0.0025 6.25E-06 0.000469925 -0.068752262 0.068752262 0.004726873 0.00088851 07/10/09 5.2 5.31 5.354376131 -0.11 0.1125 0.01265625 0.021634615 -0.154376131 0.154376131 0.02383199 0.004583075 7/17/2009 5.14 5.27 5.277188065 -0.13 0.12625 0.015939062 0.024562257 -0.137188065 0.137188065 0.018820565 0.003661589 7/24/2009 5.2 5.26 5.208594033 -0.05 0.055 0.003025 0.010576923 -0.008594033 0.008594033 7.38574E-05 1.42033E-05 7/31/2009 5.25 5.22 5.204297016 0.03 0.0325 0.00105625 0.006190476 0.045702984 0.045702984 0.002088763 0.00039786 08/07/09 5.22 5.20 5.227148508 0.03 0.025 0.000625 0.004789272 -0.007148508 0.007148508 5.11012E-05 9.7895E-06 8/14/2009 5.29 5.18 5.223574254 0.11 0.11 0.0121 0.020793951 0.066425746 0.066425746 0.00441238 0.000834098 8/21/2009 5.12 5.17 5.256787127 -0.05 0.05125 0.002626562 0.010009766 -0.136787127 0.136787127 0.018710718 0.003654437 8/28/2009 5.14 5.15 5.188393564 -0.01 0.01125 0.000126563 0.002188716 -0.048393564 0.048393564 0.002341937 0.00045563 09/04/09 5.08 5.13 5.164196782 -0.04 0.045 0.002025 0.008858268 -0.084196782 0.084196782 0.007089098 0.001395492 09/11/09 5.07 5.09 5.122098391 -0.02 0.02 0.0004 0.003944773 -0.052098391 0.052098391 0.002714242 0.000535354 9/18/2009 5.04 5.04 5.096049195 0.00 0.0025 6.25E-06 0.000496032 -0.056049195 0.056049195 0.003141512 0.000623316 9/25/2009 5.04 5.01 5.068024598 0.03 0.0275 0.00075625 0.005456349 -0.028024598 0.028024598 0.000785378 0.000155829 10/02/09 4.94 5.00 5.054012299 -0.05 0.055 0.003025 0.011133603 -0.114012299 0.114012299 0.012998804 0.002631337 10/09/09 4.87 4.99 4.997006149 -0.12 0.11875 0.014101563 0.024383984 -0.127006149 0.127006149 0.016130562 0.00331223 10/16/2009 4.92 4.98 4.933503075 -0.06 0.0575 0.00330625 0.011686992 -0.013503075 0.013503075 0.000182333 3.70596E-05 10/23/2009 5 4.96 4.926751537 0.04 0.03875 0.001501563 0.00775 0.073248463 0.073248463 0.005365337 0.001073067 10/30/2009 5.03 4.94 4.963375769 0.09 0.095 0.009025 0.01888668 0.066624231 0.066624231 0.004438788 0.000882463 11/06/09 4.98 4.92 4.996687884 0.07 0.065 0.004225 0.013052209 -0.016687884 0.016687884 0.000278485 5.59208E-05 11/13/2009 4.91 4.90 4.988343942 0.01 0.015 0.000225 0.00305499 -0.078343942 0.078343942 0.006137773 0.001250056 11/20/2009 4.83 4.88 4.949171971 -0.05 0.05125 0.002626563 0.010610766 -0.119171971 0.119171971 0.014201959 0.002940364 11/27/2009 4.78 4.87 4.889585986 -0.09 0.09375 0.008789063 0.019612971 -0.109585986 0.109585986 0.012009088 0.002512362 12/04/09 4.71 4.88 4.834792993 -0.17 0.16625 0.027639062 0.03529724 -0.124792993 0.124792993 0.015573291 0.003306431 12/11/09 4.81 4.90 4.772396496 -0.09 0.08625 0.007439063 0.017931393 0.037603504 0.037603504 0.001414023 0.000293976 12/18/2009 4.94 4.92 4.791198248 0.02 0.02125 0.000451563 0.004301619 0.148801752 0.148801752 0.022141961 0.004482178 12/25/2009 5.05 4.95 4.865599124 0.10 0.1025 0.01050625 0.02029703 0.184400876 0.184400876 0.034003683 0.006733403 01/01/10 5.14 4.97 4.957799562 0.17 0.16625 0.027639062 0.032344358 0.182200438 0.182200438 0.033197 0.00645856 01/08/10 5.09 5.01 5.048899781 0.08 0.0825 0.00680625 0.016208251 0.041100219 0.041100219 0.001689228 0.000331872 1/15/2010 5.06 5.03 5.069449891 0.03 0.0275 0.00075625 0.005434783 -0.009449891 0.009449891 8.93004E-05 1.76483E-05 1/22/2010 4.99 5.04 5.064724945 -0.05 0.04625 0.002139062 0.009268537 -0.074724945 0.074724945 0.005583817 0.001119001 1/29/2010 4.98 5.02 5.027362473 -0.04 0.04125 0.001701562 0.008283133 -0.047362473 0.047362473 0.002243204 0.000450443 02/05/10 5.01 5.01 5.003681236 0.00 0 0 0 0.006318764 0.006318764 3.99268E-05 7.96942E-06 02/12/10 4.97 5.00 5.006840618 -0.02 0.025 0.000625 0.005030181 -0.036840618 0.036840618 0.001357231 0.000273085 2/19/2010 4.93 4.98 4.988420309 -0.05 0.05125 0.002626563 0.010395538 -0.058420309 0.058420309 0.003412933 0.000692278 2/26/2010 5.05 4.98 4.959210155 0.07 0.0725 0.00525625 0.014356436 0.090789845 0.090789845 0.008242796 0.001632237 03/05/10 4.97 4.98 5.004605077 -0.01 0.00875 7.65625E-05 0.001760563 -0.034605077 0.034605077 0.001197511 0.000240948 03/12/10 4.95 4.99 4.987302539 -0.04 0.0375 0.00140625 0.007575758 -0.037302539 0.037302539 0.001391479 0.000281107 3/19/2010 4.96 5.02 4.968651269 -0.06 0.0575 0.00330625 0.011592742 -0.008651269 0.008651269 7.48445E-05 1.50896E-05 3/26/2010 4.99 5.04 4.964325635 -0.04 0.045 0.002025 0.009018036 0.025674365 0.025674365 0.000659173 0.000132099 04/02/10 5.08 5.04 4.977162817 0.04 0.0425 0.00180625 0.008366142 0.102837183 0.102837183 0.010575486 0.002081789 04/09/10 5.21 5.05 5.028581409 0.16 0.16125 0.026001563 0.030950096 0.181418591 0.181418591 0.032912705 0.006317218 4/16/2010 5.07 5.06 5.119290704 0.02 0.015 0.000225 0.00295858 -0.049290704 0.049290704 0.002429574 0.000479206 4/23/2010 5.07 5.05 5.094645352 0.02 0.01875 0.000351562 0.003698225 -0.024645352 0.024645352 0.000607393 0.000119801 4/30/2010 5.06 5.03 5.082322676 0.03 0.0275 0.00075625 0.005434783 -0.022322676 0.022322676 0.000498302 9.84786E-05 05/07/10 5 5.00 5.071161338 0.00 0.005 2.5E-05 0.001 -0.071161338 0.071161338 0.005063936 0.001012787 5/14/2010 4.93 4.94 5.035580669 -0.01 0.0125 0.00015625 0.002535497 -0.105580669 0.105580669 0.011147278 0.002261111 5/21/2010 4.84 4.90 4.982790335 -0.06 0.05875 0.003451562 0.01213843 -0.142790335 0.142790335 0.02038908 0.00421262 5/28/2010 4.78 4.86 4.911395167 -0.08 0.07875 0.006201562 0.016474895 -0.131395167 0.131395167 0.01726469 0.00361186 06/04/10 4.79 4.81 4.845697584 -0.02 0.0225 0.00050625 0.004697286 -0.055697584 0.055697584 0.003102221 0.000647645 06/11/10 4.72 4.817848792 -0.097848792 0.097848792 0.009574386 0.002028472 6/18/2010 4.75 4.768924396 -0.018924396 0.018924396 0.000358133 7.53964E-05 6/25/2010 4.69 4.759462198 -0.069462198 0.069462198 0.004824997 0.001028784 07/02/10 4.724731099 0.083157216 0.01313816 0.015461406 0.112352006 0.025842143 0.004664813 Prediction for 07/02/2010 using exp smoothing 4.724 2-mo MA MAD 8.32% MSE 1.31% MAPE 1.55% Exp Smoothing MAD 11.24% MSE 2.58% MAPE 0.47%Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.