INFO 564 South Shore Construction Time Series Forecasting Paper

Anonymous
timer Asked: Mar 29th, 2019
account_balance_wallet $40

Question Description

This work must be done completely in EXCEL.Answer each question on a separate tab.Label each tab appropriately. You can copy and paste the data given into an Excel worksheet.

South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.

Quarter

Year 1

Year 2

Year 3

Year 4

Year 5

1

20

37

75

92

176

2

100

136

155

202

282

3

175

245

326

384

445

4

13

26

48

82

181

Question 1

Plot this data with quarters from years 1-5 on the horizontal axis. What components do you see in this time series?

Question 2

Ignore any trend or seasonality in the data.

  • Suppose the company uses moving averages to make forecasts.Make forecasts all the way through Q4 Year 5.Assume the company uses (i) 3-quarterly moving averages and (ii) 4-quarterly moving averages.
  • Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation.Which is more accurate – 3 quarterly moving average or 4 quarterly moving average?
  • On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 3

Ignore any trend or seasonality in the data.

  • Suppose the company uses weighted moving averages to make forecasts.What are the forecasts starting with Q4 Year 1 all the way through Q4 Year 5?Assume the company uses (i) 3-quarterly moving averages with weights 0.6, 0.3, and 0.1 and (ii) 4-quarterly moving averages with weights 0.4, 0.3, 0.2, and 0.1.In both cases the most weight is given to the most recent quarter and the least to the oldest quarter in the moving average.
  • Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation.Which is more accurate – 3 quarterly weighted moving average or 4 quarterly weighted moving average?
  • On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 4

Again ignore any trend or seasonality in the data.

  • Suppose the company uses exponential smoothing to make forecasts.What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming (i) alpha = 0.3 and (ii) alpha = 0.7?In both cases assume that the forecast for Q1 Year 1 was 25 units.
  • Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation.Which is more accurate – alpha of 0.3 or alpha of 0.7?
  • On a line chart plot the time series along with the forecasts from the method you select in (b)

Question 5

Now make adjustments for trend and seasonality.

  • Quantify the trend in the time series.What does the trend equation tell you?
  • Quantify the seasonality in the time series by calculating seasonality indexes.What do these indexes tell you?
  • Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5.
  • Calculate the Mean Absolute Percent Deviation for the forecasts in (c).
  • On a line chart plot the time series along with the forecasts from (c).

Question 6

Using the most accurate method of all of the above,

  • Make forecasts for the four quarters of Year 6.
  • Plot these forecasts on the same line chart as the time series.
  • Summarize in a few lines your findings from your answers to Q1 through Q6b.

Unformatted Attachment Preview

INFO 564 Homework Assignment 5 This work must be done completely in EXCEL. Answer each question on a separate tab. Label each tab appropriately. You can copy and paste the data given into an Excel worksheet. South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years. Quarter 1 2 3 4 Year 1 20 100 175 13 Year 2 37 136 245 26 Year 3 75 155 326 48 Year 4 92 202 384 82 Year 5 176 282 445 181 Question 1 Plot this data with quarters from years 1-5 on the horizontal axis. What components do you see in this time series? Question 2 Ignore any trend or seasonality in the data. a. Suppose the company uses moving averages to make forecasts. Make forecasts all the way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages and (ii) 4-quarterly moving averages. b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly moving average or 4 quarterly moving average? c. On a line chart plot the time series along with the forecasts from the method you select in (b). Question 3 Ignore any trend or seasonality in the data. a. Suppose the company uses weighted moving averages to make forecasts. What are the forecasts starting with Q4 Year 1 all the way through Q4 Year 5? Assume the company uses (i) 3-quarterly moving averages with weights 0.6, 0.3, and 0.1 and (ii) 4-quarterly moving averages with weights 0.4, 0.3, 0.2, and 0.1. In both cases the most weight is given to the most recent quarter and the least to the oldest quarter in the moving average. b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly weighted moving average or 4 quarterly weighted moving average? c. On a line chart plot the time series along with the forecasts from the method you select in (b). Question 4 Again ignore any trend or seasonality in the data. a. Suppose the company uses exponential smoothing to make forecasts. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming (i) alpha = 0.3 and (ii) alpha = 0.7? In both cases assume that the forecast for Q1 Year 1 was 25 units. b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – alpha of 0.3 or alpha of 0.7? c. On a line chart plot the time series along with the forecasts from the method you select in (b) Question 5 Now make adjustments for trend and seasonality. a. Quantify the trend in the time series. What does the trend equation tell you? b. Quantify the seasonality in the time series by calculating seasonality indexes. What do these indexes tell you? c. Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5. d. Calculate the Mean Absolute Percent Deviation for the forecasts in (c). e. On a line chart plot the time series along with the forecasts from (c). Question 6 Using the most accurate method of all of the above, a. Make forecasts for the four quarters of Year 6. b. Plot these forecasts on the same line chart as the time series. c. Summarize in a few lines your findings from your answers to Q1 through Q6b. Quarterly Sales Revenues for South Shore Construction Year Quarter Sales (000's) Year 1 1 20 2 100 3 175 4 13 Year 2 1 37 2 136 3 245 4 26 Year 3 1 75 2 155 3 326 4 48 Year 4 1 92 2 202 3 384 4 82 Year 5 1 176 2 282 3 445 4 181 Question 1: The time series displays an upward trend component. Theres a seasonality because theres a cycle that occurs within each year. For the first two quarters sales are highly increasing and once it reaches the third quarter sales start declining to the forth quarter, this cycle repeats itself year after year. Time Series plot of Sales Re 450 400 350 300 250 200 150 100 50 0 1 2 3 Year 1 4 1 2 3 Year 2 4 eries plot of Sales Revenue for Shore Construction Sales (000's) 4 Year 2 1 2 3 Year 3 4 1 2 3 Year 4 4 1 2 3 Year 5 4 Quarterly Sales Revenues for South Shore Construction 3 Quarterly Moving Average Errors Year Quarter Sales (000's) MA 3 MA 4 Mean error MAE Year 1 1 20 2 100 3 175 4 13 98.33333 -85.3333333 85.3333333 Year 2 1 37 96 77 -59 59 2 136 75 81.25 61 61 3 245 62 90.25 183 183 4 26 139.3333 107.75 -113.333333 113.333333 Year 3 1 75 135.6667 111 -60.6666667 60.6666667 2 155 115.3333 120.5 39.6666667 39.6666667 3 326 85.33333 125.25 240.666667 240.666667 4 48 185.3333 145.5 -137.333333 137.333333 Year 4 1 92 176.3333 151 -84.3333333 84.3333333 2 202 155.3333 155.25 46.6666667 46.6666667 3 384 114 167 270 270 4 82 226 181.5 -144 144 Year 5 1 176 222.6667 190 -46.6666667 46.6666667 2 282 214 211 68 68 3 445 180 231 265 265 4 181 301 246.25 -120 120 19.0196078 119.098039 Moving Average Errors MAPE 4 Quarterly Moving Average Errors Mean error MAE MAPE Time Series plot of Sales Revenue 15% 63% 223% 134% 23% 124% 391% 135% 35% 109% 433% 142% 57% 377% 415% 168% 151% 1607% 450 -40 40 54.75 54.75 154.75 154.75 -81.75 81.75 -36 36 34.5 34.5 200.75 200.75 -97.5 97.5 -59 59 46.75 46.75 217 217 -99.5 99.5 -14 14 71 71 214 214 -65.25 65.25 31.28125 92.90625 62.71% 222.95% 133.88% 22.94% 123.63% 390.76% 135.46% 34.95% 109.09% 432.86% 142.22% 56.94% 377.14% 414.71% 167.92% 150.83% 186.19% 400 350 300 250 200 150 100 50 0 1 2 3 Year 1 4 1 2 3 Year 2 4 1 s plot of Sales Revenue for Shore Construction Sales (000's) 1 2 3 Year 3 4 1 2 3 Year 4 4 1 2 3 Year 5 4 INFO 564 Operations & Supply Chain Management Module 5a: Measuring Forecast Accuracy Copyright 2017 Montclair State University Forecast Accuracy • Measured retrospectively based on past forecasts and their errors • Error = Actual – Forecast • Also referred to as deviation • Common measures are functions of past errors • Mean Error (also called bias) • Mean Absolute Error (MAE) • Mean Absolute Percent Error (MAPE) Mean Error • Suppose we made forecasts for 5 past periods and wish to measure their accuracy. • Error = Actual - Forecast • Mean error is the average of the errors in the 5 periods. • Tells us that on average we are underforecasting by 1.2 units. • Caveat: Small mean error does not necessarily mean accurate forecasts • Large negative errors in some periods could cancel out large positive errors in others Period 1 2 3 4 5 Actual 22 29 29 26 26 Forecast 25 26 26 28 21 Mean Error = Error -3 3 3 -2 5 1.2 Doesn’t seem to be of same magnitude as the errors. Mean Absolute Error • Very popular measure • Absolute Error ignores the sign associated with error. • Mean Absolute Error averages the absolute errors. • More reliable measure of forecast errors. • Forecasts are typically off by 3.2 units • But is 3.2 big or small? MAE does not tell us Period 1 2 3 4 5 Actual 22 29 29 26 26 Forecast 25 26 26 28 21 Error -3 3 3 -2 5 Absolute Error 3 3 3 2 5 Mean Absolute Error = Is of same magnitude as the errors. 3.2 Mean Absolute Percent Error • Absolute Percent Error = Absolute Error ÷ Actual • Mean Absolute Percent Error = average of all the Absolute Percent Errors. • On average, forecasts are off by about 12.2% of actual. • Provides estimate of the relative size of forecast error • Another popular measure of forecast accuracy Period 1 2 3 4 5 Actual 22 29 29 26 26 Forecast 25 26 26 28 21 Error -3 3 3 -2 5 Absolute Absolute Error Percent Error 3 13.6% 3 10.3% 3 10.3% 2 7.7% 5 19.2% Mean Absolute Percent Error = 12.2% Forecasts off typically by about 12.2% of actual values. In Conclusion… • Three very common measures of forecast accuracy • Mean Error (also called bias) • Mean Absolute Error (MAE) • Mean Absolute Percent Error (MAPE) • Found in all forecasting software • Other measures available for specialized situations • Can be used to compare different forecasting methods • All based on past performance • No guarantee of future performance of forecasts INFO 564 Operations & Supply Chain Management Module 5b: Patterns in Time-Series Data Closing Price of Stock Closing Price $ What are Time Series? 80 60 40 20 0 • Data collected over time 2 3 4 5 6 Week 7 8 9 10 Daily High Temperature Temperature (F) Monthly energy bills Yearly college enrollment Daily closing value of the DJIA Hourly temperatures in a given zip-code • Quarterly earnings of a company 90 88 86 84 82 80 78 76 74 1 2 3 4 5 6 7 8 Laptop Sales 3500 Sales (Units) • • • • 1 3000 2500 2000 1500 9 10 11 12 Patterns in Time Series: Randomness • No pattern • Seemingly random small ups and downs in the time series • Too many small causes that contribute • Difficult to forecast • Impact reduced by averaging 1200 1100 # of Calls • Movement not too big compared with general level of the series Number of Calls to Help Center 1000 900 800 700 600 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Day Patterns in Time Series: Trend • The spikes are random ups and downs • Useful for forecasting • If we can assume trend will continue Temperature (F) • Upward: cloud services, Alexa, electric cars, battery life • Downward: compact discs, stickshifts, cash Daily High Temperature 90 88 86 84 82 80 78 76 74 1 2 3 4 5 6 7 8 9 10 11 12 10 11 12 Day Bank Balance ($) 6000 5000 Dollars • Trend: sustained upward or downward movement 4000 3000 2000 1000 0 1 2 3 4 5 6 7 End of Week 8 9 Patterns in Time Series: Seasonality Laptop Sales • Often products and services exhibit seasonal demand • Randomness is present. 2500 2000 1500 1000 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec • Patterns can repeat annually, monthly, weekly, even daily. • If pattern can be expected to continue, can use in forecasting. • Pattern is not perfectly repeated. 3000 # of Laptops • Some periods are consistently high, some consistently low • Christmas trees, school supplies, vacation travel, business travel, construction, etc. 3500 Patterns in Time Series: Cycles •Cycles are like seasonality, but they repeat over much longer periods •Correspond with business cycles, economic cycles •Relevant in medium-term (3-5 years) and long-term (5 or more years) forecasting •Require lots of past data to recognize these patterns Year-1 Year-2 Year-3 Year-4 Year-5 Year-6 Year-7 Year-8 Year-9 Using the Patterns for Forecasting • Time-series can exhibit one or more of these patterns. • Recognizing patterns – trends, seasonality, cyclicality – allows us to use them for forecasting • We have to be able to quantify them. • Assumption: these patterns will hold in the future. • Cyclicality is hard to recognize and quantity • Only occasionally used in time-series forecasting INFO 564 Operations & Supply Chain Management Module 5c: Forecasting with Moving Averages Example Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr Demand 988 978 1059 1013 1092 948 1002 952 958 1029 978 917 944 955 998 1017 • Past demand for a product is given in the time-series on the left. • A graph of the series is shown below: Demand 1200 1100 1000 900 800 700 600 500 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr • The series shows no trend or seasonality • Only random ups and downs. • What is our forecast for the next period, May? Approach – Moving Averages • Forecast for May based on a moving average of recent data • Example: • 3-month moving average: Average of demand in February, March, and April • = (955+998+1017)/3 = 990 units • 6-month moving average: Average of demand from November through April • = (978+917+…+1017)/6 = 968 units • Moving averages assume only recent periods are relevant. • Older periods may be ignored safely • Reasonable assumption in real life Moving Averages: Impact of Period • Graph shows demand, 3-month, and 6-month moving average forecasts • 3-month moving average forecasts more responsive to actual demand • 6-month moving average forecasts less responsive to demand • Which is better? • Longer periods dampen random fluctuations (good) but also dampen trends (bad) • Shorter periods respond to random fluctuations (bad) and to trends (good) Demand & Forecasts 1050 1000 950 900 850 800 750 700 Jan Feb Mar Apr Demand • We want forecasts to ignore random fluctuations but highlight trends May Jun 3-Mth MA Jul Aug 6-Mth MA Sep Oct How to pick a period for moving averages? • Experience, knowledge, instinct OR… • Use past data to experiment • 3-month moving average forecasts have a MAD of 37 • 6-month moving average forecasts have a MAD of 36 • 6-month moving averages seem slightly superior • May be the choice going forward Month Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr Demand 1002 952 958 1029 978 917 944 955 998 1017 3-Mth MA Abs Dev 1017 15 1014 62 967 9 971 58 980 1 988 72 975 31 947 9 939 59 966 52 37 MAD 6-Mth MA Abs Dev 1013 11 1015 64 1011 53 994 35 997 18 978 61 973 28 963 8 964 34 970 47 36 MAD Weighted Moving Averages • Simple moving averages assume equal importance (weight) of each period used to compute the moving average. • We could give different weights to different periods • In a 3-month weighted moving average, 60% to the most recent period, 30% to the one before, and 10% to the oldest period. May forecast = 0.6*1017+0.3*998+0.1*955 = 1005 units • Weights are subjective • Most recent period is considered the most important and gets most weight • Oldest period is least important and gets the least weight • Add up to 1 Summary • Moving averages are appropriate when the time-series shows no trend or seasonality • Subjective considerations • Averaging period • Weighting if any • Moving averages are reactive • When there is trend moving averages will always lag behind • Easy to understand • Easy to implement on a spreadsheet INFO 564 Operations & Supply Chain Management Module 5d: Forecasting with Exponential Smoothing Exponential Smoothing • A weighted-average forecasting method • Forecasts are a series of adjustments to previous forecasts • New Forecast = Old Forecast + Adjustment • Adjustment depends on forecast error • All past periods are used in calculating the new forecast • Unlike moving averages • Given declining weights; most recent period the most. • A subjective parameter, denoted α, is used to perform the weighting • α is between 0 and 1 Basic Idea • Ft+1 = Forecast for period t+1 (upcoming period) • Ft= Forecast for period t (period that just ended) • At= Actual demand for period t Now Ft Period t+1 Period t Ft+1 At Adjustment Ft+1= Ft + α(At - Ft) Thus Ft+1 is Ft plus a portion of the forecast error. May also be written as: Ft+1 = αAt + (1-α)Ft Easier for calculation Example Suppose α = 0.4 Forecast for April=30 • FMay = FApr + α(AApr-FApr) = 30+0.4*(25-30) = 28 Actual for April=25 • FJun = FMay + α(AMay-FMay) Forecast for May=28 = 28+0.4*(29-28) = 28.4 Actual for May=29 = 28.4+0.4*(32-28.4) = 29.84 Forecast for June=28.4 Actual for June=32 Forecast for July=29.84 • FJul = FJun + α(AJun-FJun) • And so on… Effect of α • Alternate form Ft+1 = αAt + (1-α)Ft • Can interpret Ft+1 as a weighted average of At and Ft • α is the weight given to At, 1-α the weight given to Ft • Large values of α give more weight to actual demand At • Forecasts become more responsive to actual demand • Small values of α give less weight to At • Forecasts less responsive to actual demand Effect of α Forecasts Month Exponential Smoothing Forecasts Demand Alpha=0.2 Alpha=0.8 988 1000 1000 1150 Feb 978 998 990 1100 Mar 1059 994 980 1050 Apr 1013 1007 1043 May 1092 1008 1019 Jun 948 1025 1077 Jul 1002 1009 974 Aug 952 1008 996 Sep 958 997 960 Oct 1029 989 959 Nov 978 997 1015 Dec 917 993 986 Jan 944 978 931 Feb 955 971 942 Mar 998 968 953 Apr 1017 974 989 * Forecasts for this January are assumed numbers Units Demand Jan* 1000 950 900 850 800 750 700 Jan Feb Mar Apr May Jun Demand Jul Aug Sep Alpha = 0.2 Oct Nov Dec Jan Feb Mar Alpha=0.8 Forecasts with the smaller value of α are much steadier than with the larger value of α Apr Picking a value of α • Judgment, experience, intuition • Using value of α that works well on past data • Table on right, forecasts for past periods using α=0.2 and α=0.8. • α=0.2 provides more accurate forecasts (smaller MAD) • Going forward, α=0.2 may be a better value than α=0.8. • Can also experiment with other values to obtain best value. • No guarantee that this value will work well in the future Month Demand Alpha=0.2 Abs.Dev Alpha=0.8 Abs.Dev Jan 988 1000 12.4 1000 12.4 Feb 978 998 20.0 990 12.5 Mar 1059 994 65.6 980 79.1 Apr 1013 1007 6.1 1043 30.6 May 1092 1008 83.8 1019 72.8 Jun Jul 948 1002 1025 1009 77.0 7.3 1077 974 129.4 28.4 Aug 952 1008 56.3 996 44.8 Sep 958 997 38.4 960 2.3 Oct 1029 989 40.0 959 70.2 Nov 978 997 18.4 1015 36.3 Dec 917 993 76.3 986 68.8 Jan 944 978 33.7 931 13.5 Feb 955 971 15.8 942 13.8 Mar 998 968 30.1 953 45.6 Apr 1017 974 43.5 989 28.5 39.0 43.1 MAD MAD Advantages of Exponential Smoothing • More accurate than more sophisticated methods • Easy to use and understand • Easy to adjust importance given to actual demand through α • Nested mechanism means that all past periods are used in making a forecast • FNov depends on AOct and FOct. FOct depends on ASep and FSep. FSep depends on AAug and FAug, and so on. • Thus FNov depends on AOct, ASep, AAug, and so on. • No period is ignored • More importance is given to more recent data • Included in all popular forecasting packages INFO 564 Operations & Supply Chain Management Module 5e: Trend in Time Series Example – # of Passengers • Weekly number of passengers carried by a bus service reveals an upward trend. • How to quantify this trend? • Trend in this instance seems linear • A straight line with random departures from it # Week Pass 1 305 2 302 3 380 4 372 5 452 6 404 7 424 8 408 9 533 10 522 11 510 12 588 13 604 14 581 15 585 16 617 # of Passengers 700 600 500 400 300 200 100 0 1 2 3 4 5 6 7 8 9 Week 10 11 12 13 14 15 16 Example – # of Passengers • Weekly number of passengers carried by a bus service reveals an upward trend. • How to quantify this trend? • Trend in this instance seems linear • A straight line with random departures from it # Week Pass 1 305 2 302 3 380 4 372 5 452 6 404 7 424 8 408 9 533 10 522 11 510 12 588 13 604 14 581 15 585 16 617 # of Passengers 700 600 500 400 300 200 100 0 1 2 3 4 5 6 7 8 9 Week 10 11 12 13 14 15 16 Quantifying Trend • A line is described by a slope and an intercept • Y = bX + a • b: slope, a: intercept • The slope b measures the rate at which the line climbs or falls – trend • How to find slope and intercept? • Line of best fit • Formula for b and a • Software • Spreadsheet # of Passengers 700 600 500 400 300 200 100 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Week • The line of best fit above is # of Passengers = b(Week) + a • b captures the trend: the rate at which the number of passengers is increasing each week. 16 Trendline with EXCEL Put mouse cursor on plot and Left Click. Trendline with EXCEL Right Click and select Add Trendline… Trendline with EXCEL Check these. Trend Line • y ...
Purchase answer to see full attachment

Tutor Answer

GeniousCoach
School: New York University

Hi,I have uploaded the p...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors