In: Operations Management
Historical demand for a product is:
DEMAND
January 20
February 19
March 23
April 20
May 24
June 23
a. Using a weighted moving average with weights of 0.40 (June), 0.40 (May), and 0.20 (April), find the July forecast. (Round your answer to 1 decimal place.) July forecast
b. Using a simple three-month moving average, find the July forecast. (Round your answer to 1 decimal place.) July forecast
c. Using single exponential smoothing with ? = 0.30 and a June forecast = 14, find the July forecast. (Round your answer to 1 decimal place.) July forecast
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.) Y = + t
e. Using the regression equation in d, calculate the forecast for July. (Do not round intermediate calculations. Round your answer to 1 decimal place.) July forecast
= 0.40 x Demand for June + 0.4 x demand for May + 0.2 x demand for April
= 0.40 x 23 + 0.40 x 24 + 0.2 x 20
= 9.2 + 9.6 + 4
= 22.8
= ( Demand April + Demand May + Demand June) /3
= ( 20 + 24 + 23 ) / 3
= 22.33
= 0.30 x Demand June + ( 1 – 0.30) x Forecast June
= 0.3 x 23 + 0.7 x 14
= 6.9 + 9.8
= 16.7
Where,
Y ( Dependent variable ) = Forecasted demand
T = serial number of month ( e.g January = 1 , February = 2 , March = 3 , April = 4 , May = 5 , June = 6 etc )
We place all the data of month and demand in 2 adjacent columns excel and apply the formula LINEST ( ) to arrive at values of A and B.
Accordingly values of A and B are :
A = 18.8
B = 0.77
Therefore , Regression equation :
Y = 18.8 + 0.77.t
= 18.8 + 0.77x7
= 18.8 + 5.39
= 24.19