Question

National Scan Inc. sells radio frequency inventory tags. Monthly sales for a seven-month period were as follows: Month Sales (000) Units

Feb 19

Mar 15

Apr 12

May 28

Jun 17

Jul 24

Aug 28

b. Forecast September sales volume using each of the following:

SHOW WORKS!!!!

(1) The naive approach.

(2) A five month moving average.

(3) A weighted average using .60 for August, .10 for July, and .30 for June.

(4) Exponential smoothing with a smoothing constant equal to .30, assuming a a March

forecast of 15(000).

(5) A linear trend equation.

Answer 1

1. Forecast of September using Naïve approach = Actual monthly sales of August = 28000
2. Forecast using five month moving average

=( Sum of actual sales :April to August )/5

= ( 12 + 28 + 17 + 24 + 28)/5

= 21.8

1. Forecast basis three months weighted average method

= 0.60 x actual sales of August + 0.10 x actual sales of July + 0.30 x actual sales of June

= 0.60 x 28 + 0.10 x 24 + 0.30 x 17

= 16.8 + 2.4 + 5.1

= 24.3

1. Formula for forecasts basis exponential smoothing method :

Ft = alpha x At-1 + ( 1 – alpha) x Ft-1

     = 0.3 x At-1 + 0.7 x Ft-1

Where,

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

At-1 = actual demand for period t-1

Alpha = Exponential smoothing constant

Based on forecast of March as 15000 and using above formula , forecast values of various months are presented in below format :

Month	Actual	Forecast
March	15	15.00
April	12	15.00
May	28	14.10
June	17	18.27
July	24	17.89
August	28	19.72
September		22.21

FORECAST FOR SEPTEMBER SALES VOLUME = 22.21( 000)

1. Let the linear trend equation be :

Y = a + b.t

Y ( dependent variable ) = Forecast value of sales

T ( independent variable ) = Serial number for months ( e.g February = 1, March= 2 , April = 3 , May = 4, June = 5, July= 6 , August = 7, September = 8)

A, b = constants

We place all the values of Actual sales and serial number for months( as provide din the problem ) in 2 different columns and apply the formula LINEST ( )

Accordingly , we obtain following values of a and b :

A = 13.285

B = 1.785

Therefore ,

Y = 13.285 + 1.785.t

To derive forecast for September, we need to put t= 8

Accordingly , forecast for September = 13.285 + 1.785 x 8 = 13.285 + 14.28 = 27.565

FORECAST FOR SEPTEMBER = 27.565 ( 000)