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)
- solve the question based on hw 1, 1.41arrow_forwardT1.4: Let ẞ(G) be the minimum size of a vertex cover, a(G) be the maximum size of an independent set and m(G) = |E(G)|. (i) Prove that if G is triangle free (no induced K3) then m(G) ≤ a(G)B(G). Hints - The neighborhood of a vertex in a triangle free graph must be independent; all edges have at least one end in a vertex cover. (ii) Show that all graphs of order n ≥ 3 and size m> [n2/4] contain a triangle. Hints - you may need to use either elementary calculus or the arithmetic-geometric mean inequality.arrow_forwardWe consider the one-period model studied in class as an example. Namely, we assumethat the current stock price is S0 = 10. At time T, the stock has either moved up toSt = 12 (with probability p = 0.6) or down towards St = 8 (with probability 1−p = 0.4).We consider a call option on this stock with maturity T and strike price K = 10. Theinterest rate on the money market is zero.As in class, we assume that you, as a customer, are willing to buy the call option on100 shares of stock for $120. The investor, who sold you the option, can adopt one of thefollowing strategies: Strategy 1: (seen in class) Buy 50 shares of stock and borrow $380. Strategy 2: Buy 55 shares of stock and borrow $430. Strategy 3: Buy 60 shares of stock and borrow $480. Strategy 4: Buy 40 shares of stock and borrow $280.(a) For each of strategies 2-4, describe the value of the investor’s portfolio at time 0,and at time T for each possible movement of the stock.(b) For each of strategies 2-4, does the investor have…arrow_forward
- Negate the following compound statement using De Morgans's laws.arrow_forwardNegate the following compound statement using De Morgans's laws.arrow_forwardQuestion 6: Negate the following compound statements, using De Morgan's laws. A) If Alberta was under water entirely then there should be no fossil of mammals.arrow_forward
- Negate the following compound statement using De Morgans's laws.arrow_forwardCharacterize (with proof) all connected graphs that contain no even cycles in terms oftheir blocks.arrow_forwardLet G be a connected graph that does not have P4 or C3 as an induced subgraph (i.e.,G is P4, C3 free). Prove that G is a complete bipartite grapharrow_forward
- Functions and Change: A Modeling Approach to Coll...AlgebraISBN:9781337111348Author:Bruce Crauder, Benny Evans, Alan NoellPublisher:Cengage LearningGlencoe 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
- Algebra and Trigonometry (MindTap Course List)AlgebraISBN:9781305071742Author:James Stewart, Lothar Redlin, Saleem WatsonPublisher:Cengage Learning