Times Series - Completed.xlsx

Times Series - Completed.xlsx - A 1 2 3 4 5 6 B G H Ft(Yt...

This preview shows page 1 out of 8 pages.

Unformatted text preview: 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A 1 2 3 4 5 6 B G H Ft (Yt - Ft)2 Ft (Yt - Ft)2 Ft (Yt - Ft)2 Week Actual Sales 2-wk Mov. Ave Sq Error 3-wk Mov. Ave. Sq Error Expo. Smoothing Sq Error 17 2 9 10 3 4 21 19 23 11 5 18 12 6 16 13 7 20 14 8 18 15 9 22 16 10 20 17 11 15 18 12 22 ? 25 26 F Yt 8 21 22 23 24 E t 1 20 D Smoothing Methods Example: Gasoline Sales 7 19 C How How to to create create the the data data table: table: -- Enter Enter column column headings headings :: Alpha, Alpha, Forecast, Forecast, MSE MSE in cells J6:L6. in cells J6:L6. The data show the amount of gasoline (in 1000's of gallons) sold by a gasoline distributor over the past 12 weeks. We want to make a forecast for the sales The data show the amount of gasoline (in 1000's of gallons) sold by a gasoline distributor over the past 12 weeks. --We want make a forecast for Enter the trial alpha values: 0.2, ...,0.9 Enter theto trial alpha values: 0.1, 0.1,the 0.2,sales ...,0.9 in in for the 13th week. for the 13th week. J8:J16. J8:J16. MSE -- Enter Enter formulas formulas =G19 =G19 and and =H21 =H21 just just below below Forecast Forecast and and MSE MSE headings. headings. -- Select Select the the data data table table range range J7:L16. J7:L16. -- Data Data tab tab >> What-If What-If Analysis Analysis >> Data Data Table Table [Mac: [Mac: Data Data menu menu >> Data Data Table] Table] -- Leave Leave Row Row input input cell cell blank blank and and enter enter G5 G5 for for Column Column input input cell, cell, then then hit hit OK. OK. -- IfIf the the values values given given in in different different rows rows are are the the same, hit F9 (recalculate). [Mac: Cmd same, hit F9 (recalculate). [Mac: Cmd =] =] 13 Smoothing 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A 1 B C D E F G H I J K L M Smoothing Methods Example: Gasoline Sales 2 3 Yt t Ft (Yt - Ft)2 Ft (Yt - Ft)2 Ft 4 5 6 Expo. Smoothing Data Table Sq Error Alpha 0.2 Actual Sales Week 2-wk Mov. Ave Sq Error 3-wk Mov. Ave. Sq Error 7 1 17 8 2 21 9 3 4 5 6 19 23 18 16 19.000 20.000 21.000 20.500 0.000 9.000 9.000 20.250 19.000 21.000 20.000 7 8 9 20 18 22 17.000 18.000 19.000 9.000 0.000 9.000 17 10 11 20 15 20.000 21.000 18 12 22 17.500 19 13 ? 10 11 12 13 14 15 16 20 21 MSE 22 23 24 (Yt - Ft)2 Expo. Smoothing Forecast MSE 19.185 17.000 16.000 0.1 16.000 9.000 16.000 17.800 18.040 19.032 18.826 1.440 24.602 1.065 7.984 0.2 0.3 0.4 0.5 19.000 18.000 18.000 1.000 0.000 16.000 18.260 18.608 18.487 3.026 0.370 12.343 0.6 0.7 0.8 0.000 36.000 20.000 20.000 0.000 25.000 19.189 19.351 0.657 18.935 0.9 20.250 19.000 9.000 18.481 12.382 18.500 19.000 12.500 19.185 10.222 8.982 10.222 The The data data show show the the amount amount of of gasoline gasoline (in (in 1000's 1000'sof of gallons) gallons) sold sold by by aa gasoline gasolinedistributor distributor over over the the past past 12 12 weeks. weeks. We Wewant want to to make makeaa forecast forecast for for the the sales sales for for the the 13th 13th week. week. 25 26 27 28 29 30 31 32 33 34 35 36 37 Gasoline Sales 24 22 20 Smoothing - Completed 8.982 How How to to create create the the data data table: table: -- Enter Enter column column headings headings::Alpha, Alpha, Forecast, Forecast, MSE MSE in in cells cellsJ6:L6. J6:L6. -- Enter Enter the thetrial trial alpha alpha values: values: 0.1, 0.1, 0.2, 0.2, ...,0.9 ...,0.9 in in J8:J16. J8:J16. -- Enter Enter formulas formulas=G19 =G19 and and =H21 =H21just just below below Forecast Forecast and and MSE MSEheadings. headings. -- Select Select the thedata data table tablerange rangeJ7:L16. J7:L16. -- Data Data tab tab >>What-If What-If Analysis Analysis >>Data DataTable Table [Mac: [Mac: Data Datamenu menu >>Data Data Table] Table] -- Leave Leave Row Row input input cell cellblank blankand and enter enterG5 G5for for Column Column input input cell, cell, then then hit hit OK. OK. -- IfIf the thevalues values given given in in different different rows rows are are the the same, same, hit hit F9 F9(recalculate). (recalculate). [Mac: [Mac:Cmd Cmd =] =] 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx N 1 2 3 4 5 6 7 8 O P Q R S T U Smoothing Methods - Used to smooth out irregular, random fluctuations. - For fairly steady time series data with no significant trends, seasonality, or cycles. - Appropriate for short-term forecasts. We use the following notations in the forecasting formulas: Ft = forecast in period t Yt = actual data value in period t 9 10 11 12 13 14 15 16 17 18 1. Moving Averages k-period moving average, Ft = (most recent k observations) k 2. Exponential Smoothing Ft Yt 1 (1 ) Ft 1 (To start, we let F2 = Y1.) 19 20 21 22 23 24 25 26 27 28 Mean Squared Error (MSE) MSE is one way to measure the accuracy of a forecasting method. The formula is: (Y F ) MSE t 2 t n k where smoothing constant (0 1) where n-k = number of terms in the numerator. (n = # of observations, k = number of observations not counted) When comparing different methods, the best one is the one with the smallest MSE. 29 30 31 32 33 34 35 36 37 Smoothing - Completed V 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A B C D 1 Trend Projection Example: Bicycle Sales 2 3 Data 4 5 6 Year 1 7 2 8 3 9 4 10 5 11 6 12 7 13 8 14 9 15 10 16 17 18 Results 19 Year 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 E F G H I J K L M The The data data show show bicycle bicycle sales sales of of aa particular particular manufacturer manufacturerover over the the past past 10 10 years. years. Forecast Forecast the the sales sales for foryears years 11, 11, 12, 12, and and 13. 13. Sales (1000's) 21.6 Trend Projection Used when time series data shows a consistent, The method of least squares (as in regression) d 22.9 25.5 21.9 23.9 27.5 31.5 29.7 28.6 31.4 Ft b0 b1t where b0 intercept b1 slope The independent variable = time period, t The dependent variable = observed value in the Does not follow every up and down movement It is only for estimating trends. Sales 11 12 13 How How to to make make aascatter scatter chart chart with with trend trend equation: equation: 1. 1. Create Create aa scatter scatter chart chart -- Highlight Highlight the the two two columns columns A6:B15. A6:B15. -- Insert Insert >> Scatter Scatter (in (in Charts Charts Group). Group). [Mac: [Mac: Charts Charts ribbon] ribbon] Choose Choose the the first first one one with with only only markers. markers. Remember Remember the the data data range range should should have have XX column column followed followed by by YY column. column. Optional Optional --When When there there isis aa lot lot of of empty empty space space on on your your chart chart above above or or below below the the data data points, points,you you may may want want to to change change the the range range of of vertical vertical axes. axes. For Forexample, example,to to have have the the vertical vertical axis axis start start at at 15 15 and and end endat at 35, 35,do do the thefollowing: following: -- Click Click on on Format Format tab tab under under Chart Chart Tools. Tools. -- In top Inthe the Current Current Selection Selection group, group,click click on on the the down down arrow arrow on on the the top to to choose choose Vertical Vertical (Value) (Value)Axis. Axis. -- Click Click on on Format Format Selection Selection just just below. below. -- Under Under Axis Axis Options: Options: Minimum: Minimum: enter enter 15 15 Maximum: Maximum: enter enter35 35 2. 2. Add Add trend trend line line with withequation equation -- Right Right click click on onone oneof of the the data data points points to to select select the the data data >> Add Add Trendline.... Trendline.... -- Check Check "Display "Display equation equation on on chart" chart" >> Close. Close. (With (With some some Excel Excel versions, versions, you you need need to togo go to to Options Options tab tab to to check check "Display "Display equation equation on on chart.") chart.") Trend 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A 1 B C D E F Trend Projection Example: Bicycle Sales Data 6 Year 1 Sales (1000's) 21.6 7 2 22.9 8 9 3 4 25.5 21.9 10 11 5 6 23.9 27.5 12 7 31.5 Adjusted R Square Standard Error 13 8 29.7 Observations 14 9 28.6 15 10 31.4 21 22 J K L M Trend Projection Used when time series data shows a consistent, The method of least squares (as in regression) d Regression Statistics Multiple R 0.8745261666 R Square 0.7647960161 Ft b0 b1t 0.7353955181 1.9589538024 where b0 intercept 10 b1 slope ANOVA df 20 I SUMMARY OUTPUT 16 17 18 Results 19 Year H The The data datashow show bicycle bicycle sales salesof of aaparticular particular manufacturer manufacturer over over the the past past 10 10 years. years. Forecast Forecast the the sales sales for for years years 11, 11, 12, 12, and and 13. 13. 2 3 4 5 G Sales 11 12 32.5 33.6 13 34.7 SS Regression Residual 1 8 99.825 30.700 Total 9 130.525 Coefficients Intercept 23 MS Year Standard Error F 99.825 3.838 t Stat 26.013 P-value The independent variable = time period, t The dependent variable = observed value in the Does not follow every up and down movement It is only for estimating trends. Significance F 0.001 Lower 95% Upper 95% Lower 95.0% Upper 95.0% 20.4 1.338 15.244 0.000 17.314 23.486 17.314 23.486 1.1 0.216 5.100 0.001 0.603 1.597 0.603 1.597 24 25 26 27 Bicycle Sales 28 29 35 30 31 30 32 33 34 How How to to make make aa scatter scatterchart chart with with trend trendequation: equation: 1. 1. Create Create aa scatter scatter chart chart -- Highlight Highlightthe the two two columns columns A6:B15. A6:B15. -- Insert Insert >>Scatter Scatter (in (in Charts Charts Group). Group). [Mac: [Mac: Charts Charts ribbon] ribbon] Choose Choose the the first first one one with with only only markers. markers. Remember Remember the the data data range range should should have have XX column column followed followed by by YY column. column. Sales Optional Optional -- When When there there isis aalot lot of of empty empty space space on on your your chart chart above above or or below below the the data data points, points, you you may may want want to to change change the the range range of of vertical vertical axes. axes. For For example, example, to to have havethe the vertical vertical axis axis start start at at 15 15 and and end end at at 35, 35, do do the the following: following: -- Click Click on on Format Format tab tab under under Chart Chart Tools. Tools. -- In top In the the Current Current Selection Selection group, group,click clickon onthe thedown downarrow arrowon onthe the topto tochoose choose Vertical Vertical (Value) (Value) Axis. Axis. -- Click Click on on Format Format Selection Selection just just below. below. -- Under Under Axis Axis Options: Options: Minimum: Minimum: enter enter 15 15 Maximum: Maximum: enter enter 35 35 f(x) = 1.1x + 20.4 R² = 0.7647960161 25 20 15 0 1 2 3 4 5 6 7 8 9 10 11 12 Year Trend - Completed 13 2. 2. Add Add trend trend line line with with equation equation -- Right Right click click on on one one of of the the data datapoints points to to select select the the data data >> Add Add Trendline.... Trendline.... -- Check Check "Display "Display equation equation on on chart" chart" >>Close. Close. (With (With some some Excel Excelversions, versions, you you need need to to go go to to Options Options tab tab to to check check "Display "Display equation equation on onchart.") chart.") 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A 1 2 3 4 5 6 7 8 9 B C D E F G H I J K L M N Seasonality with Trend: Smartphone Sales Data Data consists consists of of smartphone smartphone sales sales (in (in thousands thousands of of units) units) for for aa particula particul the the past past four four years. years. (a) (a) Obtain Obtain quarterly quarterly forecasts forecasts for for year year 5. 5. (b) (b) Out Out of of the the four four quarters, quarters, which which quarter quarter experiences experiences the the highest highest dem dem quarter quarter experiences experiences the the lowest lowest demand? demand? (c) (c) IfIf you you compare compare the the sales sales in in Quarter Quarter 33 this this year year and and quarter quarter 33 next next ye ye smartphone smartphone would would you you expect expect to to sell sell Quarter Quarter 33 of of next next year? year? (d) (d) Compared Compared to to Quarter Quarter 2, 2, how how many many more more smartphones smartphones would would you you ee Quarter Quarter 3? 3? Data & Computation Sales (1000s) (Y) 4.8 4.1 6 6.5 5.8 5.2 6.8 7.4 6 5.6 7.5 7.8 6.3 5.9 8 8.4 10 Year Quarter Q1 11 1 1 12 1 2 13 1 3 14 1 4 15 2 1 16 2 2 17 2 3 18 2 4 19 3 1 20 3 2 21 3 3 22 3 4 23 4 1 24 4 2 25 4 3 26 4 4 27 28 29 30 31 32 b0 b1 33 Coeff's 34 35 Manual Computation of Forecast 36 37 38 Qtr t Ft 39 Year 5 1 17 40 2 18 41 3 19 42 43 4 20 Q2 Q3 Time (t) Forecast Regression for Seasonality and Trend For data with seasonality and trend, we need to take into account both the time period t and the season of the year (e.g., quarter) in our regression. Quarter is a categorical variable, not a quantitative variable (quarter numbers, 1, 2, 3, 4, do not represent meaningful quantities). So in place of Quarter number, we need to use dummy variables. For each quarter, we designate a dummy variable that is 1 if we are in that quarter, and 0 if not. We need only 3 dummy variables, say, for quarters 1, 2, 3. Quarter 4 could be a “baseline” category (all the dummy variables equal to Specifically, the dummy variables are: Qtr1t 1 if time period t corresponds to Qtr 1; 0 otherwise Qtr 2t 1 if time period t corresponds to Qtr 2; 0 otherwise b2 b3 b4 Qtr 3t 1 if time period t corresponds to Qtr 3; 0 otherwise With t = time period, below is the regression equation. Ft b0 b1 * Qtr1t b2 * Qtr 2t b3 * Qtr 3t b4 * t Trend and Seasonality 3050dd7a193747ebd33d528b0583a30138b60f5c.xlsx A 1 2 3 4 5 6 7 8 B C E F G H Sales (1000s) (Y) Year Quarter 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 Q1 4.8 4.1 6 6.5 5.8 5.2 6.8 7.4 6 5.6 7.5 7.8 6.3 5.9 8 8.4 b0 Coeff's Q2 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 b1 6.069 Q3 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 b2 -1.363 Time (t) 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 b3 -2.034 I 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Forecast 4.851 4.326 6.201 6.651 5.434 4.909 6.784 7.234 6.016 5.491 7.366 7.816 6.599 6.074 7.949 8.399 7.181 6.656 8.531 8.981 0.146 Ft 6.069 1.363* Qtr1t 2.034 * Qtr 2t 0.304 * Qtr3t 0.146* t Qtr 5 t 1 2 3 4 c d L M N Regression Statistics Multiple R 0.988 R Square 0.976 Adjusted R S 0.968 Standard Erro 0.217 Observations 16 ANOVA df Regression Residual Total Intercept Q1 Q2 Q3 Time (t) Ft 17 6.069 - 1.363(1) - 2.034(0) - 0.304(0) + 0.146(17) = 6.069 - 1.363 - 0 - 0 + 2.482 = 7.188 18 6.069 - 1.363(0) - 2.034(1) - 0.304(0) + 0.146(18) = 6.069 - 0 - 2.034 - 0 + 2.628 = 6.663 19 6.069 - 1.363(0) - 2.034(0) - 0.304(1) + 0.146(19) = 6.069 - 0 - 0 - 0.304 + 2.774 = 8.539 20 6.069 - 1.363(0) - 2.034(0) - 0.304(0) + 0.146(20) = 6.069 - 0 - 0 - 0 + 2.92 = 8.989 0.583 1.875 K Regression with Data Analysis SUMMARY OUTPUT b4 -0.304 Manual Computation of Forecast Year J Data Data consists consists of of smartphone smartphone sales sales (in (in thousands thousands of of units) units) for for aa particular particular manufacturer manufacturer for for the the past past four four years. years. (a) (a) Obtain Obtain quarterly quarterly forecasts forecasts for for year year5. 5. (b) (b) Out Out of of the the four fourquarters, quarters, which which quarter quarterexperiences experiences the the highest highest demand? demand? Which Which quarter quarter experiences experiences the the lowest lowest demand? demand? (c) (c) IfIf you you compare compare the the sales sales in in Quarter Quarter 33 this this year year and and quarter quarter 33 next next year, year, how how many many more more smartphone smartphone would would expect expect to to sell sellQuarter Quarter 33 of of next next year? year? (d) Compared to Quarter 2, how many more smartphones would you expect to sell in Quarter 3? (d) Compared to Quarter 2, how many more smartphones would you expect to sell in Quarter 3? Data & Computation 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 D Seasonality with Trend: Smartphone Sales 0.5825 1.875 Trend and Seasonality-Complete SS 4 11 15 21.248 0.516 21.764 Coefficients Standard Error 6.069 0.162 -1.363 0.157 -2.034 0.155 -0.304 0.154 0.146 0.012 MS 5.312 0.047 F 113.158 t Stat 37.347 -8.657 -13.112 -1.981 12.023 P-value 0.000 0.000 0.000 0.073 0.000 The result from a manual calculation is slightly different from that from Excel formulas because we used here the coefficients that were rounded to the 3rd decimal digit. Smartphone Sales 10 9 8 7 6 Sales (thousands) (Y) Forecast 5 4 3 2 1 0 1 2 3 4 5 6 7 8 9 10 11 12 Time period (t) 13 14 15 16 ...
View Full Document

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern