Question

Historical demand for a product is

	DEMAND
January	15
February	12
March	16
April	15
May	17
June	16
a. Using a weighted moving average with weights of 0.60 (June), 0.20 (May), and 0.20 (April), find the July forecast. (Round your answer to 1 decimal place.) b. Using a simple three-month moving average, find the July forecast. (Round your answer to 1 decimal place.) c. Using single exponential smoothing with ? = 0.30 and a June forecast = 11, find the July forecast. (Round your answer to 1 decimal place.) d. Using simple linear regression analysis, calculate the regression equation for the preceding demand data. (Do not round intermediate calculations. Round your intercept value to 1 decimal place and slope value to 2 decimal places.) e. Using the regression equation in d, calculate the forecast for July. (Do not round intermediate calculations. Round your answer to 1 decimal place.)

Answer 1

Answer to Question a :

Forecast for July

= 0.60 x Demand for June + 0.2 x demand for May + 0.2 x demand for April

= 0.60 x 16 + 0.2 x 17 + 0.2 x 15

= 9.6 + 3.4 +3

= 16

Answer to question b :

Forecast for July using a 3 month simple moving average

= Demand for April + Demand for May + demand for June ) / 3

= ( 15 + 17 + 16 ) / 3

= 16

Answer to problem c:

The equation for exponential smoothing forecast :

Ft = alpha x D t-1 + ( 1 – alpha) x Ft-1

Ft, Ft-1 = Forecast for period t and t-1 respectively

Dt-1 = Demand for period t-1

Alpha = Exponential smoothing forecast = 0.3

Therefore ,

Ft = 0.3xDt-1 + 0.7.Ft-1

Given are following data :

Dt-1 = Demand for June =16

Ft-1 = Forecast for June = 11

Ft = 0.3 x 16 + 0.7 x 11 = 4.8 + 7.7 = 12.5

FORECAST FOR JULY = 12.5

Answer to problem d:

We denote months in terms of numbers such as :

January = 1 , February = 2 , March = 3 , April = 4 , May = 5 , June = 6 , July = 7

Let the linear regression equation is :

Y = a + b.t

Y ( dependent variable ) = Demand

T =Serial number of month

A, b = Constants

We place all the values of months ( in terms of numbers ) and Demands as given in 2 parallel columns in excel . and apply theformula LINEST( ) to find out the values of a and b

Accordingly relevant values are :

A =13.266 ( 13.27 rounded to 2 decimal places )

B =0.542

Therefore , the regression equation will be :

Y = 13.27 + 0.542.t

Answer to question e :

To calculate forecast for July, we need to putt= 7

Accordingly, forecast for July :

Y = 13.266 + 0.542 x 7 = 13.266 + 3.794 = 17.06

FORECAST FOR JULY = 17.06 ( 17.1 rounded to 1 decimal place)