Chapter 4, Problem 6P

The monthly sales for Yazici Batteries, Inc., were as follows:

a) Plot the monthly sales data.

b) Forecast January sales using each of the following:

i) Naive method.

ii) A 3-month moving average.

iii) A 6-month weighted average using .1, .1, .1, .2, .2, and .3, with the heaviest weights applied to the most recent months.

iv) Exponential smoothing using an α = .3 and a September forecast of 18.

v) A trend projection.

c) With the data given, which method would allow you to forecast next March’s sales?

Summary Introduction

To determine: Plot and represent the monthly sales data in graphical form.

Introduction: Forecasting is used to predict future changes or demand patterns. It involves different approaches and varies with different time periods. A sequence of data points in successive order is known as a time series. Time series forecasting is the prediction based on past events which are at a uniform time interval.

Answer to Problem 6P

The monthly sales data is plotted and represented.

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

Table 1

Graph:

The data to plot the sales is obtained from Table 1. Graph is plotted with the sales for January to December.

Thus, the sales data points are plotted and the graphical representation of sales data is presented.

Summary Introduction

To determine: Forecast January sales using Naïve method.

Answer to Problem 6P

The forecast for January using Naïve method is 23

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

Naïve Approach: This method assumes that the demand for a particular period will be the same as the demand in the most recent period.

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23
January	23

According to the naïve approach, the demand for January will be the same as the demand in the most recent past month. That is, the demand will be the same as that of December. Therefore, the demand for January will be same as the demand of December; 23.

Hence, the forecast for January using naïve approach is 23

Summary Introduction

To determine: Forecast January sales using 3-month moving average.

Answer to Problem 6P

The forecast for January using 3-month moving average is 50.67

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

Formula to calculate the demand forecast:

$\text{Moving}\text{average}=\frac{{\displaystyle \sum \text{demand}\text{in}\text{previous}n\text{periods}}}{n}$

Month	Sales	Moving Average
January	20
February	21
March	15
April	14	42.67
May	13	36.00
June	16	32.00
July	17	33.67
August	18	37.33
September	20	40.33
October	20	43.67
November	21	46.00
December	23	47.67
January		50.67

Excel worksheet:

Calculation of the demand forecast for January sales:

Substitute the summation of the values 20, 21, and 23and divide it by the n^th period; n=3

$\begin{array}{c}\text{Moving}\text{average}=\frac{20+21+23}{3}\\ =50.67\end{array}$ (1)

The January forecast is 50.67

Hence, the forecast of January sales using 3-month moving average is 50.67

Summary Introduction

To determine: Forecast January sales using 6-month weighted moving average.

Answer to Problem 6P

The forecast for January using 6-month moving average is 20.60

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

Formula to calculate the demand forecast:

$\text{Weighted}\text{moving}\text{average}=\frac{{\displaystyle \sum \left(\left(\text{Weight}\text{for}\text{period}n\right)\left(\text{Demand}\text{in}\text{period}n\right)\right)}}{{\displaystyle \sum \text{Weights}}}$

Month	Sales	Weighted moving average
January	20
February	21
March	15
April	14
May	13
June	16
July	17	15.80
August	18	15.90
September	20	16.20
October	20	17.30
November	21	18.20
December	23	19.40
January		20.60

Excel worksheet:

Calculation for the demand forecast of January sales:

$\begin{array}{c}\text{Weighted}\text{moving}\text{average}=\frac{\left(0.3\times 23\right)+\left(0.2\times 21\right)+\left(0.2\times 20\right)+\left(0.1\times 20\right)+\left(0.1\times 18\right)+\left(0.1\times 17\right)}{0.3+0.2+0.2+0.1+0.1+0.1}\\ =20.60\end{array}$ (2)

To calculate the forecast for January, multiply the weights with the sales of recent year, i.e. multiply weight 0.3 with 23, 0.2 with 21, 0.2 with 20, 0.1 with 20, 0.1 with 18 and 0.1 with 17.

Divide the summation of the multiplied values with the summation of the weights i.e. (0.3+0.2+0.2+0.1+0.1+0.1). The corresponding result is 20.60which is the forecasted value for January. Therefore January forecast is 20.60.

Hence, the forecast of January sales using 6-month weighted moving average is 20.60

Summary Introduction

To determine: Forecast January sales using exponential smoothing method.

Answer to Problem 6P

The forecast for January using exponential smoothing method is 20.6298

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

$\begin{array}{c}\text{Smoothing}\text{constant}\left(\alpha \right)=0.3\\ \text{Forecast}\text{of}\text{September}=18\end{array}$

Formula to calculate the demand forecast

${\text{F}}_{\text{t}}={\text{F}}_{\text{t-1}}+\text{α}\left({\text{A}}_{\text{t-1}}-{\text{F}}_{\text{t-1}}\right)$

Where

$\begin{array}{c}{\text{F}}_{\text{t}}=\text{new}\text{forecast}\\ {\text{F}}_{\text{t-1}}=\text{Previous}\text{period's}\text{forecast}\\ \text{α}=\text{smoothing}\text{constant}\\ {\text{A}}_{\text{t-1}}=\text{Previous}\text{period}\text{actual}\text{Demand}\end{array}$

Sl. No.	Month	Sales	Forecast
1	January	20
2	February	21
3	March	15
4	April	14
5	May	13
6	June	16
7	July	17
8	August	18
9	September	20	18
10	October	20	18.6
11	November	21	19.02
12	December	23	19.614
13	January		20.6298

Excel worksheet:

Calculation of the forecast for October:

$\begin{array}{c}{\text{F}}_{\text{ocotber}}={\text{F}}_{\text{september}}+\text{α}\left({\text{A}}_{\text{september}}-{\text{F}}_{\text{september}}\right)\\ =18+0.3\left(28-18\right)\\ =18.6\end{array}$

To calculate forecast for October, substitute the value of forecast of September, smoothing constant and difference of actual and forecasted demand of September. The result of forecast for October is 18.6.

Calculation of the forecast for November:

$\begin{array}{c}{\text{F}}_{\text{november}}={\text{F}}_{\text{ocotber}}+\text{α}\left({\text{A}}_{\text{ocotber}}-{\text{F}}_{\text{ocotber}}\right)\\ =18.6+0.3\left(20-18.6\right)\\ =19.02\end{array}$

To calculate forecast for November, substitute the value of forecast of October, smoothing constant and difference of actual and forecasted demand of October. The result of forecast for November is 19.02.

Calculation of the forecast for December:

$\begin{array}{c}{\text{F}}_{\text{december}}={\text{F}}_{\text{november}}+\text{α}\left({\text{A}}_{\text{november}}-{\text{F}}_{\text{november}}\right)\\ =19.02+0.3\left(21-19.02\right)\\ =19.614\end{array}$

To calculate forecast for December, substitute the value of forecast of November, smoothing constant and difference of actual and forecasted demand of November. Therefore, the forecast for December is 19.614.

Calculation of the forecast for January:

$\begin{array}{c}{\text{F}}_{\text{january}}={\text{F}}_{\text{december}}+\text{α}\left({\text{A}}_{\text{december}}-{\text{F}}_{\text{december}}\right)\\ =19.614+0.3\left(23-19.614\right)\\ =20.6298\end{array}$ (3)

To calculate forecast for January, substitute the value of forecast of December, smoothing constant and difference of actual and forecasted demand of December. Therefore, the forecast for January is 20.6298.

Hence, the forecast of January sales using exponential smoothing method is 20.6298

Summary Introduction

To determine: Forecast January sales using trend projection.

Answer to Problem 6P

The forecast for January using trend projection is 20.754

Explanation of Solution

Given information:

Month	Sales
January	20
February	21
March	15
April	14
May	13
June	16
July	17
August	18
September	20
October	20
November	21
December	23

Formula to calculate the demand forecast

$\widehat{\text{y}}=\text{a}+\text{bx}$

Where,

$\begin{array}{c}\widehat{\text{y}}=\text{computed value of the variable}\\ \text{a}=\text{y-axis intercept}\\ \text{b}=\text{slope of the regression line}\\ \text{x}=\text{the independent variable}\end{array}$

$\text{b}=\frac{\sum \text{xy}-n\overline{\text{x}}\overline{\text{y}}}{\sum {\text{x}}^{\text{2}}-\text{n}{\overline{\text{x}}}^{\text{2}}}$

Where

$\begin{array}{l}\text{b}=\text{slope of the regression line}\\ \sum =\text{ summation sign}\\ \text{x}=\text{known values of the independent variables}\\ \text{y}=\text{known values of the dependent variables}\end{array}$

$\begin{array}{l}\overline{\text{x}}=\text{average of the x - values}\\ \overline{\text{y}}=\text{average of the y - values}\\ \text{n }=\text{ number of data points}\end{array}$

Month (x)	Sales (y)	xy	x2
1	20	20	1
2	21	42	4
3	15	45	9
4	14	56	16
5	13	65	25
6	16	96	36
7	17	119	49
8	18	144	64
9	20	180	81
10	20	200	100
11	21	231	121
12	23	276	144
∑=78	∑=218	∑=1474	∑=650

Substituting the values in the above formula

Calculation of average of x values $\overline{x}$:

$\begin{array}{c}\overline{\text{x}}=\frac{{\displaystyle \sum _{i=1}^{12}\text{x}}}{n}\\ =\frac{\text{78}}{\text{12}}\\ =\text{6}\text{.5}\end{array}$

Average of x values is obtained by dividing the summation of x values i.e. (1+2+…+12) with the number of period n i.e.12. The value of $\overline{x}$= 6.5.

Calculation of average of y values $\overline{y}$:

$\begin{array}{c}\overline{\text{y}}=\frac{{\displaystyle \sum _{i=1}^{12}y}}{n}\\ =\frac{\text{218}}{\text{12}}\\ =\text{18}\text{.166}\end{array}$

Average of y values is obtained by dividing the summation of sales with the number of period n i.e.12. The value of $\overline{y}$= 18.166

Calculation of slope of regression line ‘b’:

$\begin{array}{c}\text{b}=\frac{\sum \text{xy}-n\overline{\text{x}}\overline{\text{y}}}{\sum {\text{x}}^{\text{2}}-n{\overline{\text{x}}}^{\text{2}}}\\ =\frac{1,474-\left(12\times 6.5\times 18.166\right)}{650-\left(12\times {6.5}^2\right)}\\ =\frac{57}{143}\\ =0.398\end{array}$

Summation of product of sales (y) with x values is ∑xy = 1474, product of number of months (n), average of x values and average of y values is obtained i.e. $n\overline{\text{x}}\overline{\text{y}}$=1416.94. Difference between 1474 and 1416.94 is 57.

Summation of square of x values i.e. 650 is subtracted from the product of number of months i.e. 12 with average of x values i.e. 6.5. The resultant value is 143. The slope of regression line is obtained by dividing 57 with 143. The value of ‘b’ is 0.398.

Calculation of y axis intercept ‘a’:

$\begin{array}{c}\text{a}=\overline{\text{y}}-\text{b}\overline{\text{x}}\\ =\text{18}\text{.166}-\left(\text{0}\text{.398}\times \text{6}\text{.5}\right)\\ =\text{15}\text{.579}\end{array}$

The y axis intercept is obtained by the difference between average of y values and values obtained by the product of slope of regression line with average of x values. The resultant value of ‘a’ is 15.579.

Calculation of forecast of January:

$\begin{array}{c}{\widehat{\text{y}}}_{\text{january}}=\text{a}+\text{bx}\\ =\text{15}\text{.58}+\left(\text{0}\text{.398}\times 1\text{3}\right)\\ =\text{20}\text{.754}\end{array}$ (4)

The January forecast is obtained by summation of the product of slope of regression line and forecasted month, January i.e. 13 with the y-axis intercept. The forecasted value obtained is 20.754.

Hence, the forecast for January sales using trend projection is 20.754

Summary Introduction

To determine: The best technique among time series methods to forecast March sales.

Explanation of Solution

The calculated results from the data revels that the trend projection (refer to equation (4)) is the best suitable technique to forecast March sales as it is useful in evaluating trends in the data.