Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List)
Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List)
6th Edition
ISBN: 9781337115186
Author: David R. Anderson, Dennis J. Sweeney, Thomas A. Williams, Jeffrey D. Camm, James J. Cochran
Publisher: Cengage Learning
bartleby

Videos

Textbook Question
Book Icon
Chapter 17, Problem 41SE

The weekly demand (in cases) for a particular brand of automatic dishwasher detergent for a chain of grocery stores located in Columbus, Ohio, follows.

Chapter 17, Problem 41SE, The weekly demand (in cases) for a particular brand of automatic dishwasher detergent for a chain of

  1. a. Construct a time series plot. What type of pattern exists in the data?
  2. b. Use a three-week moving average to develop a forecast for week 11.
  3. c. Use exponential smoothing with a smoothing constant of α = .2 to develop a forecast for week 11.
  4. d. Which of the two methods do you prefer? Why?

a.

Expert Solution
Check Mark
To determine

Construct the time series plot.

Explain the type of pattern.

Answer to Problem 41SE

The time series plot is given below:

Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List), Chapter 17, Problem 41SE , additional homework tip  1

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.

Expert Solution
Check Mark
To determine

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:

Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List), Chapter 17, Problem 41SE , additional homework tip  2

From the output, the forecast value for week 11 is 19.33.

c.

Expert Solution
Check Mark
To determine

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 α=0.2. Therefore, the dropping factor is 0.8 (=10.2).

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:

Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List), Chapter 17, Problem 41SE , additional homework tip  3

The forecast value for week 11 using exponential smoothing method is obtained as follows:

F11=αY10+(1α)F10

Here, F11 represents forecast for week 11; Y10 represents the actual value for week 10; and F10 represents forecast for week 10.

F11=0.2(21)+(10.2)(20.08)=4.2+16.06420.26

Thus, the forecast value for week 11 is 20.26.

d.

Expert Solution
Check Mark
To determine

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:

(Forecast error)2=(Actual valueForecast)2

For Week 3:

The forecast error2 for week 4 for 3-week moving average is obtained as follows:

(Forecast error)2=(Actual valueForecast)2=(260273.33)2=177.69

The remaining forecasts errors2 for exponential smoothing averages are obtained as follows:

WeekDemandForecast (Ft) for 3-Week Moving Average(Forecast Error)2Forecast (Ft) for α=0.2(Forecast Error)2
17.35----
27.4--22.0016.00
37.55--21.203.24
47.5621.000.0021.560.31
57.620.6713.4421.4519.78
67.5220.3313.4420.5611.84
77.5220.670.4421.251.55
87.720.331.7821.003.99
97.6221.009.0020.606.75
107.5519.004.0020.080.85
Total  42.11 64.33

The MSE for 3-week moving average is obtained as follows:

MSE=Forecast error2Number of forecast errors=42.117=6.02

Thus, the value of MSE for 3-week moving average is 6.02.

The MSE for exponential smoothing averages for α=0.2 is obtained as follows:

MSE=Forecast error2Number of forecast errors=64.339=7.15

Thus, the value of MSE for exponential smoothing averages for α=0.2 is 7.15.

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?

Subscribe now to access step-by-step solutions to millions of textbook problems written by subject matter experts!
Students have asked these similar questions
Observed weekly sales of ball peen hammers at the town hardware store over an eight-week period have been 14, 9, 30, 22, 34, 12, 19, 23.a. Suppose that three-week moving averages are used to forecast sales. Determine the one-step-ahead forecasts for weeks 4 through 8.b. Suppose that exponential smoothing is used with a smoothing constantof a = .15. Find the exponential smoothing forecasts for weeks 4 through 8.[To get the method started, use the same forecast for week 4 as you used in part (a).]c. Based on the MAD, which method did better?d. What is the exponential smoothing forecast made at the end of week 6 for the sales in week 12?
4. The sales (in millions of dollars) for an 18-month period are as follows. Month Sales Month Sales 1 840 10 1085 1225 11 840 3 805 12 805 4 910 13 875 980 14 910 1120 15 840 7 770 16 973 8 1085 17 1050 840 18 1085 a. Compare a three-month moving average forecast with an exponential smoothing forecast. Use a = 0.3. Which provides the better forecasts based on MSE? b. What is the forecast for the next month?
No need to answer everything! Just plot the actual visitors and the forecasts. Compare the accuracy of the forecasts in the 1985 to 1990 period with those subsequent to that period.

Chapter 17 Solutions

Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card) (MindTap Course List)

Ch. 17.3 - For the Hawkins Company, the monthly percentages...Ch. 17.3 - Corporate triple-A bond interest rates for 12...Ch. 17.3 - The values of Alabama building contracts (in $...Ch. 17.3 - The following time series shows the sales of a...Ch. 17.3 - Ten weeks of data on the Commodity Futures Index...Ch. 17.3 - Prob. 16ECh. 17.4 - Consider the following time series...Ch. 17.4 - Prob. 18ECh. 17.4 - Prob. 19ECh. 17.4 - Prob. 20ECh. 17.4 - Prob. 21ECh. 17.4 - Prob. 22ECh. 17.4 - The president of a small manufacturing firm is...Ch. 17.4 - The following data shows the average interest rate...Ch. 17.4 - Quarterly revenue ($ millions) for Twitter for the...Ch. 17.4 - Giovanni Food Products produces and sells frozen...Ch. 17.4 - The number of users of Facebook from 2004 through...Ch. 17.5 - Consider the following time series. Construct a...Ch. 17.5 - Consider the following time series...Ch. 17.5 - The quarterly sales data (number of copies sold)...Ch. 17.5 - Air pollution control specialists in southern...Ch. 17.5 - South Shore Construction builds permanent docks...Ch. 17.5 - Prob. 33ECh. 17.5 - Prob. 34ECh. 17.6 - Consider the following time series...Ch. 17.6 - Refer to exercise 35. Deseasonalize the time...Ch. 17.6 - The quarterly sales data (number of copies sold)...Ch. 17.6 - Three years of monthly lawn-maintenance expenses...Ch. 17.6 - Air pollution control specialists in southern...Ch. 17.6 - Electric power consumption is measured in...Ch. 17 - The weekly demand (in cases) for a particular...Ch. 17 - The following table reports the percentage of...Ch. 17 - United Dairies, Inc., supplies milk to several...Ch. 17 - Annual retail store revenue for Apple from 2007 to...Ch. 17 - The Mayfair Department Store in Davenport, Iowa,...Ch. 17 - Prob. 47SECh. 17 - The Costello Music Company has been in business...Ch. 17 - Consider the Costello Music Company problem in...Ch. 17 - Prob. 50SECh. 17 - Refer to the Costello Music Company time series in...Ch. 17 - Prob. 52SECh. 17 - Refer to the Hudson Marine problem in exercise 52....Ch. 17 - Refer to the Hudson Marine problem in exercise...Ch. 17 - Refer to the Hudson Marine data in exercise...Ch. 17 - Forecasting Food and Beverage Sales The Vintage...Ch. 17 - The Carlson Department Store suffered heavy damage...
Knowledge Booster
Background pattern image
Statistics
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, statistics and related others by exploring similar questions and additional content below.
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Glencoe Algebra 1, Student Edition, 9780079039897...
Algebra
ISBN:9780079039897
Author:Carter
Publisher:McGraw Hill
Text book image
College Algebra
Algebra
ISBN:9781938168383
Author:Jay Abramson
Publisher:OpenStax
Text book image
Big Ideas Math A Bridge To Success Algebra 1: Stu...
Algebra
ISBN:9781680331141
Author:HOUGHTON MIFFLIN HARCOURT
Publisher:Houghton Mifflin Harcourt
Text book image
Functions and Change: A Modeling Approach to Coll...
Algebra
ISBN:9781337111348
Author:Bruce Crauder, Benny Evans, Alan Noell
Publisher:Cengage Learning
Text book image
College Algebra
Algebra
ISBN:9781337282291
Author:Ron Larson
Publisher:Cengage Learning
Text book image
Trigonometry (MindTap Course List)
Trigonometry
ISBN:9781337278461
Author:Ron Larson
Publisher:Cengage Learning
Time Series Analysis Theory & Uni-variate Forecasting Techniques; Author: Analytics University;https://www.youtube.com/watch?v=_X5q9FYLGxM;License: Standard YouTube License, CC-BY
Operations management 101: Time-series, forecasting introduction; Author: Brandoz Foltz;https://www.youtube.com/watch?v=EaqZP36ool8;License: Standard YouTube License, CC-BY