Question

2. The historical demand for a product is below. Please forecast the demand in April. Month Sales (a) Use a 3-month simple moving average. 1 (January) 33 (b) Use a 3-month weighted moving average with weights of 0.6, 0.3, and 0.1. 2 (February) 36 (c) Use exponential smoothing with a = 0.8 and a March forecast = 37. 3 (March) 39 (d) Find trend line y = a+bx, and then forecast demand. (3 points)

Answer 1

1. Demand using 3 Month Simple Moving average
Month	Sales
a	Jan	33.00
b	Feb	36.00
c	Mar	39.00
d	Total	108.00	a+b+c
e	No. of months	3.00
f	3 Months moving average	36.00	d/e
g	So demand forecasted for April	36.00

2. Demand using 3 Month Simple Moving average with weights
Month	Sales
Demand	Weights	Weighted Demand
a	Jan	33.00	0.6	19.8
b	Feb	36.00	0.3	10.8
c	Mar	39.00	0.1	3.9
d	Total	108.00	a+b+c	34.5
g	So demand forecasted for April	34.50

3. Demand using Exponential Smoothing
Month	Sales
Demand	alpha	Calculation	Forecast
a	Jan	33.00	0.8		33.00
b	Feb	36.00	0.8	[(33x .8)+(33 x .2)	33.00
c	Mar	39.00	0.8	[(36x .8)+(33 x .2)	35.40
d	April		0.8	[(39x .8)+(35.40 x .2)	38.28
	Foemula = (Actual demand in previous month x alpha ) + Forecasted demand in previous month (1 - alpha)
If the March Forecast is 37
Month	Sales
Demand	alpha		Forecast Amount
c	Mar	39.00	0.8		37.00
d	April		0.8	(39 x .8) + (37 x .2)	38.60

4. Demand Forecast using the Trend line

y = a+bx

For the purpose of solving the equation first find the value of a

Assumptions

y is the dependent variable .ie., mean value of sales

x is the independent variable ie., mean value of the month( assigned values to each months as 1,2,3)

a is the intercept of y

b is the slope the calculation for b is as follows

The new equation will be

a = mean of y- b(mean of X)

n	X	Y	XY	X^2
1	1	33.00	33.00	1
2	2	36.00	72.00	4
3	3	39.00	117.00	9
	6	108.00	222.00	14.00

Number of items	3
Mean of X = 6/3 =	2
Mean of y= 108/3=	36

So b = [total of XY - n (mean of X)(mean of y)]/[total of x^2-n*(mean of x^2)]

(	222.00	-	3 * 2 * 36)	/	14.00	-	3* 2^2
	222.00	-	216	/	14.00	-	12
			6.00	/	2.00		=	3.00

Solving the equation we get 3 as b

So applying the value of b to the equation we get a

a = mean of y- b(mean of X)

So a = 36 - 3 * 2 =

Applying the value of a in the equation when x is 4 is

y = a+ bx

y= 30+(3*4)

y = 42

So the demand for the 4th month ie., April is 42

Month	Sales
1	33.00
2	36.00
3	39.00
4	42.00

Trendline 45.00 40.00 35.00 30.00 25.00 0 20.00 15.00 10.00 5.00 1 2 3 4 5 5 Months

forecast.linear function in excel can be used to find out the trend line demand