Unit 8 Discussion Example - Initial Post A time series model is a forecasting technique that attempts to predict the future values of a variable by using only historical data on that one variable. Here are some examples of variables you can use to forecast. You may use a different source other than the ones listed (be sure to reference the website). There are many other variables you can use, as long as you have values that are recorded at successive intervals of time.       Currency price: XE (http://www.xe.com/currencyconverter/ ) GNP: Trading Economics (http://www.tradingeconomics.com/united-states/gross-national-product ) Average home sales: National Association of Realtors (http://www.realtor.org/topics/existing-homesales ) College tuition: National Center for Education Statistics (https://nces.ed.gov/fastfacts/display.asp?id=76 ) Weather temperature or precipitation: (http://www.weather.gov/help-past-weather ) Stock price: Yahoo Finance (https://finance.yahoo.com ) Once you have historical data, address the following: 1. State the variable you are forecasting. 2. Collect data for any time horizon (daily, monthly, yearly). Select at least 8 data values. 3. Use Excel QM to forecast using moving average, weighted moving average and exponential smoothing (see video in Live Binder). 4. Copy/paste the results of each method. Be sure to state the number of periods used in the moving average method, the weights used in the weighted moving average, and the value of alpha used in exponential smoothing. 5. Clearly state the “next period” prediction for each method. ****************************************************************************************** I will use the National Association of Realtors Website (http://www.realtor.org/topics/existing-home-sales ) and I downloaded the “Single-Family Existing Home Sales and Prices” spreadsheet for Database work. 1. I will look at the (not-seasonally adjusted) median sale price for the West column over the past year by month (May 2015 – May 2016). Here is the data: Year 2015 2015 2015 2015 2015 2015 2015 2015 2016 2016 2016 2016 2016 May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May West 325,800 331,300 329,300 322,000 322,200 324,200 321,700 324,900 313,400 312,300 322,500 337,800 348,100 2&3) 3-Month Moving Average – forecast is $336,133 Forecasting Moving averages - 3 period moving average Num pds 3 D a ta Period Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Month 13 Demand $325,800 $331,300 $329,300 $322,000 $322,200 $324,200 $321,700 $324,900 $313,400 $312,300 $322,500 $337,800 $348,100 Fore ca sts a nd E rror Ana lysis Fore ca st E rror Absolute S qua re d Abs P ct E rr $328,800 $327,533 $324,500 $322,800 $322,700 $323,600 $320,000 $316,867 $316,067 $324,200 Total Ave ra ge 331300 329300 -6800 -5333.33 -300 -1100 2200 -10200 -7700 5633.333 21733.33 23900 682633.3 56886.1 Bia s 331300 329300 6800 5333.333 300 1100 2200 10200 7700 5633.333 21733.33 23900 745500 62125 MAD SE 1.1E+11 100.00% 1.08E+11 100.00% 46240000 02.11% 28444444 01.66% 90000 00.09% 1210000 00.34% 4840000 00.68% 1.04E+08 03.25% 59290000 02.47% 31734444 01.75% 4.72E+08 06.43% 5.71E+08 06.87% 2.2E+11 225.65% 1.8E +10 18.80% MS E MAP E 148161 N e xt pe riod $336,133 3-Month Weighted Moving Average – forecast is $340,400 Weights are 3 = most recent month, 2 = 1-month prior, 1 = 2-months prior Forecasting D a ta Period Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Month 13 Weighted moving averages - 3 period moving average Demand Weights $325,800 1 $331,300 2 $329,300 3 $322,000 $322,200 $324,200 $321,700 $324,900 $313,400 $312,300 $322,500 $337,800 $348,100 N e xt pe riod $340,400 Fore ca sts a nd Error Ana lysis Fore ca st Error Absolute Squa re d Abs Pct Err 329383.3 325983.3 323316.7 323166.7 322616.7 323716.7 318616.7 314766.7 317583.3 328450 Total Ave ra ge -7383.33 -3783.33 883.3333 -1466.67 2283.333 -10316.7 -6316.67 7733.333 20216.67 19650 21500 2150 Bia s 7383.333 3783.333 883.3333 1466.667 2283.333 10316.67 6316.667 7733.333 20216.67 19650 80033.33 8003.33 MAD SE 54513611 02.29% 14313611 01.17% 780277.8 00.27% 2151111 00.46% 5213611 00.70% 1.06E+08 03.29% 39900278 02.02% 59804444 02.40% 4.09E+08 05.98% 3.86E+08 05.64% 1.08E+09 24.24% 1.1E+08 02.42% MSE MAPE 11607.9 Exponential Smoothing, alpha = 0.25 – forecast is $330,413 Forecasting Alpha D a ta Period Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Period 7 Period 8 Period 9 Period 10 Period 11 Period 12 Period 13 Exponential smoothing 0.25 Demand $325,800 $331,300 $329,300 $322,000 $322,200 $324,200 $321,700 $324,900 $313,400 $312,300 $322,500 $337,800 $348,100 N e xt pe riod $330,413 Fore ca sts a nd E rror Ana lysis Fore ca st E rror Absolute S qua re d Abs P ct E rr 325800 0 0 0 00.00% 325800 5500 5500 30250000 01.66% 327175 2125 2125 4515625 00.65% 327706.3 -5706.25 5706.25 32561289 01.77% 326279.7 -4079.69 4079.688 16643850 01.27% 325259.8 -1059.77 1059.766 1123103 00.33% 324994.8 -3294.82 3294.824 10855867 01.02% 324171.1 728.8818 728.8818 531268.7 00.22% 324353.3 -10953.3 10953.34 1.2E+08 03.50% 321615 -9315 9315.004 86769299 02.98% 319286.3 3213.747 3213.747 10328170 01.00% 320089.7 17710.31 17710.31 3.14E+08 05.24% 324517.3 23582.73 23582.73 5.56E+08 0.067747006 Total 18451.8 87269.54 1.18E+09 26.41% Ave ra ge 1419.37 6713.04 9.1E +07 02.03% Bia s MAD MS E MAP E SE 10372 Unit 8 Discussion Example - First Response to a Classmate’s Post First response: Choose a classmate’s post. Use the same data, and forecast a trend projection using Excel QM. Share the graph and “next period” prediction. Based on the graph, do you think this is a good model for this variable? ****************************************************************************************** I will use the National Association of Realtors Website (http://www.realtor.org/topics/existing-home-sales ) and I downloaded the “Single-Family Existing Home Sales and Prices” spreadsheet for Database work. I looked at the (not-seasonally adjusted) median sale price for the West column over the past year by month (May 2015 – May 2016). Forecast for the next period is $329,388. Based on the graph, there looks like there could be some seasonality trends that I might want to explore by looking at more than one years worth of data. Is there always a drop in prices around months 9-10 (Jan-Feb)? Forecasting D a ta Period Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Month 13 Simple linear regression Demand (y) Period(x) $325,800 1 $331,300 2 $329,300 3 $322,000 4 $322,200 5 $324,200 6 $321,700 7 $324,900 8 $313,400 9 $312,300 10 $322,500 11 $337,800 12 $348,100 13 Inte rce pt Slope 322226.9 511.5385 Fore ca st $329,388 Fore ca sts a nd Error Ana lysis Forecast Error Absolute Squared Abs Pct Err 322738.5 3061.538 3061.538 9373018 00.94% 323250 8050 8050 64802500 02.43% 323761.5 5538.462 5538.462 30674556 01.68% 324273.1 -2273.08 2273.077 5166879 00.71% 324784.6 -2584.62 2584.615 6680237 00.80% 325296.2 -1096.15 1096.154 1201553 00.34% 325807.7 -4107.69 4107.692 16873136 01.28% 326319.2 -1419.23 1419.231 2014216 00.44% 326830.8 -13430.8 13430.77 1.8E+08 04.29% 327342.3 -15042.3 15042.31 2.26E+08 04.82% 327853.8 -5353.85 5353.846 28663669 01.66% 328365.4 9434.615 9434.615 89011967 02.79% 328876.9 19223.08 19223.08 3.7E+08 05.52% Total 1.75E-10 90615.38 1.03E+09 27.69% Ave ra ge 1.3E-11 6970.41 7.9E+07 02.13% Bia s MAD MSE MAPE SE 9679.62 14 Corre la tion0.21016 Coe fficie nt of de te rmina tion 0.04417 Unit 8 Discussion Example - Second Response to a Classmate’s Post Second response: Choose another classmate’s post. Compare the MAD (mean absolute deviation) of all three forecasts (moving average, weighted moving average and exponential smoothing) and state which forecasting method gives the most accurate forecast. ****************************************************************************************** I will use the National Association of Realtors Website (http://www.realtor.org/topics/existing-home-sales ) and I downloaded the “Single-Family Existing Home Sales and Prices” spreadsheet for Database work. 3-Month Moving Average – forecast is $336,133 MAD score = 62,125 3-Month Weighted Moving Average – forecast is $340,400 MAD score = 8003.33 Exponential Smoothing, alpha = 0.25 – forecast is $330,413 MAD score = 6713.04 Based on the MAD scores, the most accurate forecast would be the exponential smoothing with alpha = 0.25.
Purchase answer to see full attachment