The weekly demand (in cases) for a particular brand of automatic dishwasher detergent for a chain of grocery stores located in Columbus, Ohio, follows.
- a. Construct a time series plot. What type of pattern exists in the data?
- b. Use a three-week moving average to develop a forecast for week 11.
- c. Use exponential smoothing with a smoothing constant of α = .2 to develop a forecast for week 11.
- d. Which of the two methods do you prefer? Why?
a.
Construct the time series plot.
Explain the type of pattern.
Answer to Problem 41SE
The time series plot is given below:
The pattern that appears in the graph is a horizontal pattern.
Explanation of Solution
Calculation:
The given data represent the weekly demand for automatic dishwasher detergent.
Software procedure:
Step-by-step software procedure to draw the time series plot using EXCEL:
- Open an EXCEL file.
- In column A, enter the data of Week, and in column B, enter the corresponding values of Demand.
- Select the data that are to be displayed.
- Click on the Insert Tab > select Scatter icon.
- Choose a Scatter with Straight Lines and Markers.
- Click on the chart > select Layout from the Chart Tools.
- Select Chart Title > Above Chart and enter Time Series Plot.
- Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
- Enter Week in the dialog box.
- Select Axis Title > Primary Vertical Axis Title > Rotated Title.
- Enter Demand in the dialog box.
From the output, the pattern that appears in the graph is a horizontal pattern.
b.
Calculate the forecast for week 11 using three-week moving averages.
Answer to Problem 41SE
The forecast for week 11 using three-week moving averages is 19.33.
Explanation of Solution
Calculation:
The forecast for week 11 using three-week moving averages is to be obtained.
Software procedure:
Step-by-step procedure to obtain the forecasts using EXCEL:
- In column A, enter the data of Month, and in column B, enter the corresponding values of Demand.
- In Data, select Data Analysis and choose Moving Average.
- In Input Range, select Demand.
- Select Label in First Row.
- In Interval, enter 3.
- In Output Range, select C3.
- Click OK.
Output using the EXCEL software is given below:
From the output, the forecast value for week 11 is 19.33.
c.
Calculate the forecast for week 11 using the exponential smoothing with constant 0.2.
Answer to Problem 41SE
The forecast for week 11 using the exponential smoothing with constant 0.2 is 20.14.
Explanation of Solution
Calculation:
It is given that
Software procedure:
Step-by-step procedure to obtain the forecasts using EXCEL:
- In column A, enter the data of Week, and in column B, enter the corresponding values of Demand.
- Select Data Analysis and choose Exponential Smoothing.
- In Input Range, select Demand.
- In Damping factor, enter 0.8.
- Select Label in First Row.
- In Output Range, select C2.
- Click OK.
Output using the EXCEL software is given below:
The forecast value for week 11 using exponential smoothing method is obtained as follows:
Here,
Thus, the forecast value for week 11 is 20.26.
d.
Identify the most preferable method between three-week moving averages and exponential smoothing. Explain the reason.
Answer to Problem 41SE
The three-week moving average gives the most accurate forecast because MSE for three-week moving averages is lesser when compared to the MSE for exponential smoothing.
Explanation of Solution
The formula for finding the forecast error2 is as follows:
For Week 3:
The forecast error2 for week 4 for 3-week moving average is obtained as follows:
The remaining forecasts errors2 for exponential smoothing averages are obtained as follows:
Week | Demand | Forecast (Ft) for 3-Week Moving Average | (Forecast Error)2 | Forecast (Ft) for | (Forecast Error)2 |
1 | 7.35 | - | - | - | - |
2 | 7.4 | - | - | 22.00 | 16.00 |
3 | 7.55 | - | - | 21.20 | 3.24 |
4 | 7.56 | 21.00 | 0.00 | 21.56 | 0.31 |
5 | 7.6 | 20.67 | 13.44 | 21.45 | 19.78 |
6 | 7.52 | 20.33 | 13.44 | 20.56 | 11.84 |
7 | 7.52 | 20.67 | 0.44 | 21.25 | 1.55 |
8 | 7.7 | 20.33 | 1.78 | 21.00 | 3.99 |
9 | 7.62 | 21.00 | 9.00 | 20.60 | 6.75 |
10 | 7.55 | 19.00 | 4.00 | 20.08 | 0.85 |
Total | 42.11 | 64.33 |
The MSE for 3-week moving average is obtained as follows:
Thus, the value of MSE for 3-week moving average is 6.02.
The MSE for exponential smoothing averages for
Thus, the value of MSE for exponential smoothing averages for
Here, it is observed that the MSE for three-week moving averages is lesser when compared to the MSE for exponential smoothing. Thus, the three-week moving average gives the most accurate forecast.
Want to see more full solutions like this?
Chapter 17 Solutions
Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List)
- Find the equation of the regression line for the following data set. x 1 2 3 y 0 3 4arrow_forwardTable 6 shows the year and the number ofpeople unemployed in a particular city for several years. Determine whether the trend appears linear. If so, and assuming the trend continues, in what year will the number of unemployed reach 5 people?arrow_forwardOlympic Pole Vault The graph in Figure 7 indicates that in recent years the winning Olympic men’s pole vault height has fallen below the value predicted by the regression line in Example 2. This might have occurred because when the pole vault was a new event there was much room for improvement in vaulters’ performances, whereas now even the best training can produce only incremental advances. Let’s see whether concentrating on more recent results gives a better predictor of future records. (a) Use the data in Table 2 (page 176) to complete the table of winning pole vault heights shown in the margin. (Note that we are using x=0 to correspond to the year 1972, where this restricted data set begins.) (b) Find the regression line for the data in part ‚(a). (c) Plot the data and the regression line on the same axes. Does the regression line seem to provide a good model for the data? (d) What does the regression line predict as the winning pole vault height for the 2012 Olympics? Compare this predicted value to the actual 2012 winning height of 5.97 m, as described on page 177. Has this new regression line provided a better prediction than the line in Example 2?arrow_forward
- What does the y -intercept on the graph of a logistic equation correspond to for a population modeled by that equation?arrow_forwardLife Expectancy The following table shows the average life expectancy, in years, of a child born in the given year42 Life expectancy 2005 77.6 2007 78.1 2009 78.5 2011 78.7 2013 78.8 a. Find the equation of the regression line, and explain the meaning of its slope. b. Plot the data points and the regression line. c. Explain in practical terms the meaning of the slope of the regression line. d. Based on the trend of the regression line, what do you predict as the life expectancy of a child born in 2019? e. Based on the trend of the regression line, what do you predict as the life expectancy of a child born in 1580?2300arrow_forwardplease answer within 30 minutes...arrow_forward
- The weekly sales (in dollars) for a particular brand of ice cream for a chain of grocery stores located in Melbourne, Australia, follows. Week Sale ($) 1 200 2 325 3 121 4 630 500 Use a four-week moving average to develop a forecast for week 6. Use exponential smoothing with a smoothing constant of a=0.7 to develop a forecast for week 6 Which of the two methods do you prefer and why? (arrow_forwardB4. A company has collected and smoothened its historcal yearly sales data of a product from 2015 up to 2021. The following table shows the sales and moving average figures. Year Sales Three-year moving average Five-ycar moving average (S thousands) 2015 2016 326.0 324.0 E 367.4 2017 344.0 2018 383.0 371.0 E 2019 389.0 2020 398.0 D. 2021 428.0 Calculate the missing values of 4, B, C, D, E and F(Correct to I decimal place).arrow_forwardGiven the number of calls per week received by a dental clinic, what is the forecasted number of calls for week 12 considering that the forecast for week 1 was 47 and a smoothing constant a -0.2? CALLS 52 35 25 36 45 35 44 WEEK 1 2 3 4 5 6 7 869 10 34 calls 35 calls 33 calls 38 calls 30 35 25arrow_forward
- Use the data given in the three scenarios below to produce scatter plots, determine the type of model demonstrated in each example and then answer the questions based on the graph you created. Scenario 1 Rescue 911 In a rural community, fire and paramedic crews must service a large region. They often cover 2 or 3 towns several kilometres apart. These crews travel, on average, 70 km/h to their destination. The Chief of Station 42 wants to determine the time required to reach the patient's home (response time) for his region. The station is located next to the local hospital. The Chief knows the distances to each community that he services, including Deerborn, the furthest community, at 55 km away. Using the data below, create a scatter plot showing the relationship between distance to patient and response time. Distance to Patient (km) Response Time (minutes) 10 8.6 15 12.9 40 34.3 50 42.9 55 47.1arrow_forward1. Make a scatter plot of the table provided in the image. B. Write a linear/exponential equation that models this table provided in the image. C. Explain what the slope/multiplier means in the context of the problem. E. Use your model to predict when there will be 100 new casesarrow_forwardAn electronic appliance manufacturer wants to know if there is a relationship between percentage change in deposable personal income which is reported quarterly by the government, and the percentage change in appliances sold by the manufacturer following same years of quarterly data. Brenda Chee and Clarence Paulus lead an analyst team has obtained data for the past 10 quarters. (Hint: Provides your answers in two decimal points) Quarter Percent change in income Percent Change in appliance sold Quarter Percent change in income Percent change in appliance sold 1 -2.3 -2.5 6 -1.0 1.0 2 -1.5 -1.0 7 0.7 1.4 3 2.8 7.4 8 5.2 3.4 4 0.5 2.6 9 -2.5 -0.5 5 4.6 8.5 10 1.7 1.8 (a) What forecasting model should be used for this data. Why?(5(b) Develop the forecasting model that you have proposed in (a).(c) Compute the relationship for these data. In your opinion, is the relationship between independentvariable…arrow_forward
- Glencoe Algebra 1, Student Edition, 9780079039897...AlgebraISBN:9780079039897Author:CarterPublisher:McGraw HillBig Ideas Math A Bridge To Success Algebra 1: Stu...AlgebraISBN:9781680331141Author:HOUGHTON MIFFLIN HARCOURTPublisher:Houghton Mifflin Harcourt
- Functions and Change: A Modeling Approach to Coll...AlgebraISBN:9781337111348Author:Bruce Crauder, Benny Evans, Alan NoellPublisher:Cengage LearningTrigonometry (MindTap Course List)TrigonometryISBN:9781337278461Author:Ron LarsonPublisher:Cengage Learning