Explore BrainMass
Share

Forecasting - Management Science

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Hi this is a forecasting problem that can be solved using Excel with the solver-add in on it. Please answer all parts to the question . If you need any information or have a question Please let me know thanks.

3. A company has been experiencing growth in demand for its principal product over the past several years and has collected the following data (demand in millions of units):

1999 2000 2001 2002 2003 2004
Quarter 1 3.47 4.06 4.27 5.88 9.44 14.25
Quarter 2 3.12 6.90 5.24 8.99 7.75 14.89
Quarter 3 3.97 3.60 6.39 4.12 9.91 14.22
Quarter 4 4.50 6.47 5.45 6.68 9.14 15.56

a) Plot the demand over time (number the consecutive quarters 1 to 24). Fit a linear trend line to the data. What do you observe (in general terms)?
b) Project the value for the first quarter of 2005 using a naà¯ve forecast and a 4 period moving average.
c) Build the formulas for fitting exponential smoothing to all of the historical data, using the naà¯ve forecast as the beginning forecast for the 2nd quarter of 1999. Use Solver to find the value of alpha that minimizes the resulting Minimum Absolute Deviation (MAD) for the 12 quarters of 2000 -2002. What is the resulting MAD for the 8 quarters of 2003-2004?
d) Build the formulas for fitting double exponential smoothing (Holt) to all of the historical data, using the naà¯ve forecast as the beginning forecast for the 2nd quarter of 1999 and an initial trend estimate of zero. Use Solver to find the values of alpha and beta that minimize the resulting Minimum Absolute Deviation (MAD) for the 12 quarters of 2000 -2002. What is the resulting MAD for the 8 quarters of 2003-2004?
e) Plot the Holt historical forecast series and projection for the first quarter of 2005 on the chart you created at step a). What do you observe (in general terms)?
f) You now have 4 forecasts for the first quarter of 2005 (not counting the trend line). Which did best for the 8 quarters of 2003-2004 (based on MAD)? Which one do you think would do best for first quarter of 2005? Why?

Please see attached.

© BrainMass Inc. brainmass.com October 24, 2018, 6:33 pm ad1c9bdddf
https://brainmass.com/economics/estimation-and-forecasting/forecasting-management-science-43468

Attachments

Solution Preview

a) A linear trendline doesn't seem to be appropiate to approximate this data set. Demand experienced a very slow growth the first years and accelerated during 2003/2004; hence the fact that almost all of the data points before 2003 are below the trendline, while after 2003 they are above it.

b) The naive forecast consists simply of repeating the value of the previous period. Therefore, a naive demand forecast for Q1 of 2005 is 15.56, which is equal to the value in Q4 of 2004.

The moving average forecast is the last value in the moving average series; so the forecast for Q1 of 2005 would be 14.73

c) The formula for exponential smoothing is F(t) = alpha*d(t-1) + (1-alpha)*F(t-1), where F(t) is the forecasted value at period t, and d(t-1) ...

Solution Summary

Quantitative Decison Making for business is examined.

$2.19
See Also This Related BrainMass Solution

Management Science: Forecasting

6. The manager of the Petroco Service station wants to forecast the demand for unleaded gasoline next month so that the proper number of gallons can be ordered from the distributor. The owner has accumulated the following data on demand for unleaded gasoline from sales during the past 10 months:

Month Gasoline Demand (gal)
October 800
November 725
December 630
January 500
February 645
March 690
April 730
May 810
June 1,200
July 980

a. Compute an exponentially smoothed forecast, using on a value of .30.
b. Compute an adjusted exponentially smoothed forecast (with a =.30 and B =.20).
c. Compare the two forecast by using MAPD and indicate which seems to be more accurate.

5. The chairperson of the department of Management of State University wants to forecast the number of students who will enroll in production and operations management (POM) next semester, in order to determine how many sections to schedule. The chair has accumulated the following enrollment data for the past eight semesters:

Semester No. of students enrolled in POM
1 400
2 450
3 350
4 420
5 500
6 575
7 490
8 650

a. Compute the three-semester moving average forecast for semesters 4 through 9.
b. Compute the exponentially smoothed forecast (alpha = .20) for the enrollment data.
c. Compare the two forecasts by using MAD and indicate the more accurate of the two.

7. The victory plus mutual fund of growth stocks has had the following average monthly price for the past 10 months:

Month Fund Price
1 62.7
2 63.9
3 68.0
4 66.4
5 67.2
6 65.8
7 68.2
8 69.3
9 67.2
10 70.1

Compute the exponentially smoothed forecast with alpha=.40, the adjusted exponential smoothing forecast with alpha=.40 and beta=.30, and the linear trend line forecast. Compare the accuracy of the three forecasts, using cumulative error and MAD, and indicate which forecast appears to be most accurate.

1. The saki motorcycle dealer in Minneapolis wants to make an accurate forecast of demand for the Saki Super TXII motorcycle during the next month. Because the manufacturer is in Japan, it is difficult to send motorcycles back or reorder if the proper number is not ordered a month ahead. From sales records, the dealer has accumulated the following data for the past year:

Month Motorcyle Sales
January 9
February 7
March 10
April 8
May 7
June 12
July 10
August 11
September 12
October 10
November 14
December 16

a) Compute a 3-month moving average forecast of demand for April through January of the next year
b) Compute a 5-month moving average forecast for June through January
c) Compare the two forecasts computed in (a) and (b) using MAD. Which one should the dealer use for January of the next year?

View Full Posting Details