Question

FOR4. The Excel file Unemployment Rates provides data on monthly rates for 4 years. (8 pts)...

FOR4. The Excel file Unemployment Rates provides data on monthly rates for 4 years. (8 pts)

a.         Develop 3- and 6-months moving average forecasts, and exponential smoothing forecasts (use alpha of your choice)

b.         Using MAD as a criterion, explain which model yields better forecast?

DATA:

Unemployment Rates
Year Month Rate (%)
2009 Jan 7.8
2009 Feb 8.3
2009 Mar 8.7
2009 Apr 9.0
2009 May 9.4
2009 Jun 9.5
2009 Jul 9.5
2009 Aug 9.6
2009 Sep 9.8
2009 Oct 10.0
2009 Nov 9.9
2009 Dec 9.9
2010 Jan 9.8
2010 Feb 9.8
2010 Mar 9.9
2010 Apr 9.9
2010 May 9.6
2010 Jun 9.4
2010 Jul 9.5
2010 Aug 9.5
2010 Sep 9.5
2010 Oct 9.5
2010 Nov 9.8
2010 Dec 9.3
2011 Jan 9.1
2011 Feb 9.0
2011 Mar 8.9
2011 Apr 9.0
2011 May 9.0
2011 Jun 9.1
2011 Jul 9.0
2011 Aug 9.0
2011 Sep 9.0
2011 Oct 8.9
2011 Nov 8.6
2011 Dec 8.5
2012 Jan 8.3
2012 Feb 8.3
2012 Mar 8.2
2012 Apr 8.1
2012 May 8.2
2012 Jun 8.2
2012 Jul 8.2
2012 Aug 8.1
2012 Sep 7.8
2012 Oct 7.9
2012 Nov 7.8
2012 Dec 7.8
Business   Finance   
0 0
Add a commentTranscribed image text
Next >

Homework Answers

Answer #1

A.   Forecast Using 3 Month moving average, 6 Month moving average and exponential smoothing method

3 Month Moving Average - The forecast for the 4th month is the average of last 3 months.

6 Month Moving Average - The forecast for the 7th month is the average of Last 6 months.

Exponential Smoothing - Used Exponential smoothing method from data analysis tab in excel. (used damping factor as 0.5, which is nothing but 1- Alpha.) Alpha - 0.5

Absolute deviation (Mod value of deviation) has been calculated using ABS function in excel

Deviation - Actual rate - Forecast rate

Year Month Actual Rate (%) 3 Month Moving Average( Forecast ) Deviation (Actual - Forecast) Absolute Deviation (Mod value of Deviation) 6 Month Moving Average (Forecast) Deviation (Actual - Forecast) Absolute Deviation (Mod value of Deviation) Exponential Smoothing (Alpha - 0.5) - Forecast Deviation (Actual - Forecast) Absolute Deviation (Mod value of Deviation)
2009 Jan 7.8 #NA #NA #NA #NA #NA #NA #N/A #NA #NA
2009 Feb 8.3 #NA #NA #NA #NA #NA #NA 7.80 0.50 0.50
2009 Mar 8.7 #NA #NA #NA #NA #NA #NA 8.05 0.65 0.65
2009 Apr 9 8.27 0.73 0.73 #NA #NA #NA 8.38 0.63 0.63
2009 May 9.4 8.67 0.73 0.73 #NA #NA #NA 8.69 0.71 0.71
2009 Jun 9.5 9.03 0.47 0.47 #NA #NA #NA 9.04 0.46 0.46
2009 Jul 9.5 9.30 0.20 0.20 8.78 0.72 0.72 9.27 0.23 0.23
2009 Aug 9.6 9.47 0.13 0.13 9.07 0.53 0.53 9.39 0.21 0.21
2009 Sep 9.8 9.53 0.27 0.27 9.28 0.52 0.52 9.49 0.31 0.31
2009 Oct 10 9.63 0.37 0.37 9.47 0.53 0.53 9.65 0.35 0.35
2009 Nov 9.9 9.80 0.10 0.10 9.63 0.27 0.27 9.82 0.08 0.08
2009 Dec 9.9 9.90 0.00 0.00 9.72 0.18 0.18 9.86 0.04 0.04
2010 Jan 9.8 9.93 -0.13 0.13 9.78 0.02 0.02 9.88 -0.08 0.08
2010 Feb 9.8 9.87 -0.07 0.07 9.83 -0.03 0.03 9.84 -0.04 0.04
2010 Mar 9.9 9.83 0.07 0.07 9.87 0.03 0.03 9.82 0.08 0.08
2010 Apr 9.9 9.83 0.07 0.07 9.88 0.02 0.02 9.86 0.04 0.04
2010 May 9.6 9.87 -0.27 0.27 9.87 -0.27 0.27 9.88 -0.28 0.28
2010 Jun 9.4 9.80 -0.40 0.40 9.82 -0.42 0.42 9.74 -0.34 0.34
2010 Jul 9.5 9.63 -0.13 0.13 9.73 -0.23 0.23 9.57 -0.07 0.07
2010 Aug 9.5 9.50 0.00 0.00 9.68 -0.18 0.18 9.54 -0.04 0.04
2010 Sep 9.5 9.47 0.03 0.03 9.63 -0.13 0.13 9.52 -0.02 0.02
2010 Oct 9.5 9.50 0.00 0.00 9.57 -0.07 0.07 9.51 -0.01 0.01
2010 Nov 9.8 9.50 0.30 0.30 9.50 0.30 0.30 9.50 0.30 0.30
2010 Dec 9.3 9.60 -0.30 0.30 9.53 -0.23 0.23 9.65 -0.35 0.35
2011 Jan 9.1 9.53 -0.43 0.43 9.52 -0.42 0.42 9.48 -0.38 0.38
2011 Feb 9 9.40 -0.40 0.40 9.45 -0.45 0.45 9.29 -0.29 0.29
2011 Mar 8.9 9.13 -0.23 0.23 9.37 -0.47 0.47 9.14 -0.24 0.24
2011 Apr 9 9.00 0.00 0.00 9.27 -0.27 0.27 9.02 -0.02 0.02
2011 May 9 8.97 0.03 0.03 9.18 -0.18 0.18 9.01 -0.01 0.01
2011 Jun 9.1 8.97 0.13 0.13 9.05 0.05 0.05 9.01 0.09 0.09
2011 Jul 9 9.03 -0.03 0.03 9.02 -0.02 0.02 9.05 -0.05 0.05
2011 Aug 9 9.03 -0.03 0.03 9.00 0.00 0.00 9.03 -0.03 0.03
2011 Sep 9 9.03 -0.03 0.03 9.00 0.00 0.00 9.01 -0.01 0.01
2011 Oct 8.9 9.00 -0.10 0.10 9.02 -0.12 0.12 9.01 -0.11 0.11
2011 Nov 8.6 8.97 -0.37 0.37 9.00 -0.40 0.40 8.95 -0.35 0.35
2011 Dec 8.5 8.83 -0.33 0.33 8.93 -0.43 0.43 8.78 -0.28 0.28
2012 Jan 8.3 8.67 -0.37 0.37 8.83 -0.53 0.53 8.64 -0.34 0.34
2012 Feb 8.3 8.47 -0.17 0.17 8.72 -0.42 0.42 8.47 -0.17 0.17
2012 Mar 8.2 8.37 -0.17 0.17 8.60 -0.40 0.40 8.38 -0.18 0.18
2012 Apr 8.1 8.27 -0.17 0.17 8.47 -0.37 0.37 8.29 -0.19 0.19
2012 May 8.2 8.20 0.00 0.00 8.33 -0.13 0.13 8.20 0.00 0.00
2012 Jun 8.2 8.17 0.03 0.03 8.27 -0.07 0.07 8.20 0.00 0.00
2012 Jul 8.2 8.17 0.03 0.03 8.22 -0.02 0.02 8.20 0.00 0.00
2012 Aug 8.1 8.20 -0.10 0.10 8.20 -0.10 0.10 8.20 -0.10 0.10
2012 Sep 7.8 8.17 -0.37 0.37 8.17 -0.37 0.37 8.15 -0.35 0.35
2012 Oct 7.9 8.03 -0.13 0.13 8.10 -0.20 0.20 7.97 -0.07 0.07
2012 Nov 7.8 7.93 -0.13 0.13 8.07 -0.27 0.27 7.94 -0.14 0.14
2012 Dec 7.8 7.83 -0.03 0.03 8.00 -0.20 0.20 7.87 -0.07 0.07

B.

Mean Absolute Deviation - Average or Mean of Absolute deviation of the forecast rate from the actual rate

Lower the MAD better the Model.

Mean Absolute Deviation (in %)
3 Monthly Moving Average 0.1911
6 Monthly Moving Average 0.2512
Exponential Smoothing 0.1976

Since the Mean Absolute deviation is lowest in case of 3-month moving average(0.1911%), so 3 Month moving average yields better forecast.

0 0
Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Year Month Return Year Month Return 2006     Jan 3.95 2008     Jul 3.29 2006     Feb 3.77 2008...
Year Month Return Year Month Return 2006     Jan 3.95 2008     Jul 3.29 2006     Feb 3.77 2008     Aug 4.62 2006     Mar 5.29 2008     Sep 4.81 2006     Apr 3.77 2008     Oct 5.16 2006     May 4.47 2008     Nov 3.69 2006     Jun 5.2 2008     Dec 5.15 2006     Jul 3.9 2009     Jan 5.29 2006     Aug 4.33 2009     Feb 3.19 2006     Sep 4.41 2009     Mar 3.89 2006     Oct 5.14 2009     Apr 4.48 2006     Nov 3.24 2009     May 5.27 2006     Dec 4.13 2009     Jun 3.93 2007     Jan...
Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010. Date...
Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010. Date Return Jan-06 5.39 Feb-06 4.83 Mar-06 5.41 Apr-06 4.64 May-06 4.05 Jun-06 3.41 Jul-06 3.92 Aug-06 3.46 Sep-06 5.06 Oct-06 5.44 Nov-06 4.96 Dec-06 4.17 Jan-07 3.48 Feb-07 4.7 Mar-07 4.38 Apr-07 3.82 May-07 4.19 Jun-07 4.35 Jul-07 3.83 Aug-07 5.42 Sep-07 3.29 Oct-07 4 Nov-07 3.42 Dec-07 3.24 Jan-08 5.21 Feb-08 4.84 Mar-08 4.59 Apr-08 3.82 May-08 3.61 Jun-08 4.34 Jul-08 4.94 Aug-08...
Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010. Date...
Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010. Date Return Jan-06 3.13 Feb-06 4.15 Mar-06 3.18 Apr-06 4.94 May-06 4.34 Jun-06 4.19 Jul-06 5.12 Aug-06 5.26 Sep-06 3.81 Oct-06 3.1 Nov-06 3.87 Dec-06 4.89 Jan-07 3.94 Feb-07 3.42 Mar-07 4.13 Apr-07 3.54 May-07 4.58 Jun-07 4.19 Jul-07 4.62 Aug-07 3.89 Sep-07 3.62 Oct-07 3.92 Nov-07 4.46 Dec-07 3.23 Jan-08 4.78 Feb-08 4.71 Mar-08 5.05 Apr-08 3.46 May-08 3.15 Jun-08 4.82 Jul-08 3.87 Aug-08...
Use the data below to answer this questions. a.) Generate a scatter of the data b....
Use the data below to answer this questions. a.) Generate a scatter of the data b. ) Report the monthly averages (January for all years, February for all years etc.) c.) Is there seasonality? Is there a trend? d.) How can you forecast the value for March 2020? Generate that forecast. e.) (Not technical) This forecast will for sure be wrong. Why? Reference period Employment 3 Persons Jan-01 1,879.50 Feb-01 1,901.00 Mar-01 1,925.30 Apr-01 1,914.60 May-01 1,961.50 Jun-01 1,960.60 Jul-01...
Use the data below to answer this questions. Period Employment Jan-01 1,879.50 Feb-01 1,901.00 Mar-01 1,925.30...
Use the data below to answer this questions. Period Employment Jan-01 1,879.50 Feb-01 1,901.00 Mar-01 1,925.30 Apr-01 1,914.60 May-01 1,961.50 Jun-01 1,960.60 Jul-01 1,953.40 Aug-01 1,940.20 Sep-01 1,928.00 Oct-01 1,909.20 Nov-01 1,896.40 Dec-01 1,881.40 Jan-02 1,880.20 Feb-02 1,884.00 Mar-02 1,902.60 Apr-02 1,913.40 May-02 1,937.40 Jun-02 1,990.90 Jul-02 1,994.80 Aug-02 2,013.10 Sep-02 2,002.30 Oct-02 1,982.50 Nov-02 1,969.00 Dec-02 1,959.20 Jan-03 1,928.20 Feb-03 1,952.40 Mar-03 1,980.40 Apr-03 1,972.00 May-03 1,987.80 Jun-03 2,018.70 Jul-03 2,027.80 Aug-03 2,030.20 Sep-03 2,012.20 Oct-03 2,032.30 Nov-03 2,008.30...
Given the following history, use a three-quarter moving average to forecast the demand for the third...
Given the following history, use a three-quarter moving average to forecast the demand for the third quarter of this year. Note, the 1st quarter is Jan, Feb, and Mar; 2nd quarter Apr, May, Jun; 3rd quarter Jul, Aug, Sep; and 4th quarter Oct, Nov, Dec. JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC     Last year 165 185 200 230 240 265 210 200 195 265 290 315   This year 175 200 165 260 260 200   Forecast...
1) Calculate a three period moving average forecast for the months of July, August, and September...
1) Calculate a three period moving average forecast for the months of July, August, and September 2011. Using the observed data and your forecasted values, calculate the MAPE for this forecasting method over those three months. Select the correct answer from the list given. 0.8% 2.6% 5.7% 7.9% 9.3% 27.9% 2) Given this data, what forecasting method would be best to use to forecast the unemployment rate in January 2012? Weighted moving average Three-period moving average Five-period moving average Simple...
The following table shows a portion of the monthly returns data (in percent) for 2010–2016 for...
The following table shows a portion of the monthly returns data (in percent) for 2010–2016 for two of Vanguard’s mutual funds: the Vanguard Energy Fund and the Vanguard Healthcare Fund. a. Calculate the sample correlation coefficient rxy. b. Specify the competing hypotheses in order to determine whether the population correlation coefficient is different from zero. H0: ρxy ≥ 0; HA: ρxy < 0 H0: ρxy ≤ 0; HA: ρxy > 0 H0: ρxy = 0; HA: ρxy ≠ 0 c-1....
Rounded to the nearest whole number, what is the standard deviation for Google weekly closing prices...
Rounded to the nearest whole number, what is the standard deviation for Google weekly closing prices from December 12, 2008 to December 4, 2009?   4-Dec-09 585.01 27-Nov-09 579.76 20-Nov-09 569.96 13-Nov-09 572.05 20-Nov-09 551.1 30-Oct-09 536.12 23-Oct-09 553.69 16-Oct-09 549.85 9-Oct-09 516.25 2-Oct-09 484.58 25-Sep-09 492.48 18-Sep-09 491.46 11-Sep-09 472.14 4-Sep-09 461.3 28-Aug-09 464.75 21-Aug-09 465.24 14-Aug-09 460 7-Aug-09 457.1 31-Jul-09 443.05 24-Jul-09 446.72 17-Jul-09 430.25 10-Jul-09 414.4 2-Jul-09 408.49 26-Jun-09 425.32 19-Jun-09 420.09 12-Jun-09 424.84 5-Jun-09 444.32 29-May-09 417.23...
A local bookstore recorded their revenue (in thousands) for the last 36 months starting in September,...
A local bookstore recorded their revenue (in thousands) for the last 36 months starting in September, as provided below. a. find the deseasonalized line of best fit b. use the additive model of seasonal forecasting to predict the revenue for each month of the next academic year c. use the multiplicative model of seasonal forecasting to predict the revenue for each month of the next academic year d. what is the predicted total profit for the academic year for each...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT