Sales Forecasting: The 4 Methods to Master

Welcome to this chapter on calculating sales forecasts!

Here are the 4 methods that I will cover in this Operational Management course for the BTS MCO:

• Method No. 1: The least squares method
• Method #2: The extreme points method
• Method No. 3: Mayer's method or Double Average Method
• Method #4: Seasonality of sales
• Correlation coefficient and sales forecasting
• Conclusion

Regardless of the forecasting method, the general principle is the same.

Using a straight line with an equation of the form "y = ax+b", the aim is to find future sales figures.

Throughout this article I will take the following example:

The following table is given concerning the financial information of a fast food store in the suburbs of Paris. You must study the forecast of this data for the year N+1.

years	Sales figures
N-4	42
N-3	58
N-2	64
N-1	77

When writing an exercise, you must pay careful attention to the chronological order.

 

Method No. 1: The least squares method

In this linear adjustment sales forecasting method, the following elements are assumed:

• the period considered is “xi”
• turnover corresponds to "yi"
• the number of rows in the table of passed elements corresponds to "n"

With the previous elements, you must calculate two averages: the average of the periods and the average of the turnovers.

The average term is identified using a small bar over the letter as in the following formulas.

To do this, you need to apply the following formulas:

The formula for the average of the periods which we call "average of x":

 

The formula for the average turnover, which is called the "average of y":

 

How to read formulas?

The reading of the formula for the period averages is as follows:

average of "x" equals sum of "xi" divided by "n"

The reading of the formula for average turnover figures is as follows:

average of "y" equals sum of "yi" divided by "n"

 

At first, you cannot apply formulas to find these two averages. You have to go through the development of a preparatory table.

Here is an example of a preparatory table that will help you find the useful elements:

 

The most important row in the table is the “Total” row.

The "Rank" corresponds to a number that we attribute to a period in order to be able to use it to apply a formula.

You should always start with the number "1" for the most distant period and increment (increase) by "1" for each new period.

The total of the "xi" column is useful for calculating the average of the periods. The total of the "yi" column is useful for calculating the averages of the turnovers.

I will apply the formulas with the numerical elements of our initial example.

In our example "n" = 4 because there are four rows in the statement table.

The formula for the mean of x is:

 

And the average of y is:

 

This same table is also used to calculate other formulas.

 

How to calculate the parameters "a" and "b" of the equation?

To determine the equation of the line of the form "y=ax+b", it is necessary to first find the parameters of the equation, that is, the element "a" and the element "b".

This is where the totals in the "xi.yi" and "xi squared" columns come in.

"xi.yi" means xi multiplied by yi. This calculation is done for each row of the table.

"xi" squared means that for each row of "xi" in the table the value is squared.

To find the parameters "a" and "b" we must apply the following formulas:

In the formula for parameter "a" we have the following:

• the sum of the product "xi.yi"
• the number of rows in the table "n"
• the sum of the column "xi squared"
• the means of x and y

In the formula for parameter "b" we have the means of x and y on the one hand, and on the other hand the parameter "a".

So here is what this gives in our example:

Now we can apply the formulas for the parameters "a" and "b":

 

How to interpret the parameters “a” and “b”?

To interpret the values ​​found, we must replace "a" and "b" in the equation "y = ax + b" with these same values:

y = 11,10x + 32,5

We can therefore write that this equation allows us to find any future (i.e. forecast) turnover figures.

 

How to carry out sales forecasting?

Once the equation is determined, we must replace "x" with the rank of the period sought. In our example, the rank of the period sought is 6.

Why “6”? In our initial table, the last rank is 4 corresponding to year “N-1”.

So year N corresponds to rank 5 and year N+1 corresponds to rank 6.

Hence the following equation:

y = (11,10 x 6) + 32,5 = 99,10

We can therefore write that the forecast turnover for year N+1 is €99,10K.

 

Method No. 2: Sales forecasting using the extreme points method

What is the principle of the method?

This method of linear adjustment of sales forecasting consists of taking into consideration the oldest period and the most recent period, setting up a system of equations, finding the parameters of this system, and finally carrying out the forecast.

Reminder of the initial table of the exercise:

years	Sales figures
N-4	42
N-3	58
N-2	64
N-1	77

How to pose a system of equations?

Here is the method to follow to correctly pose the system of equations:

• identify the two lines concerned: the oldest and the most recent
• The Revenue column represents the “yi”
• the “Rank” column represents the “xi”
• the equations sought are of the form: y = ax + b
• Replace "x" and "y" with "xi" and "yi" for the two lines concerned

In our example we have the following system of equations:

To solve this system of equations there are several methods. We will use the subtraction method. We will subtract member by member the first equation from the second equation.

77 = 4a + b
42 = a + b
_________
35 = 3a

Indeed: 77 – 42 = 35 and 4a – a = 3a and “b” – “b” = 0b

35 = 3a becomes:

a = 35/3
a = 11,66

We have just found the value of "a". To find the value of "b", we must replace "a" in one of the two initial equations.

Let's take for example the first equation: 42 = a + b

So we have :

42 = 11,66 + b
b = 42 – 11,66
b = 31,66

Now that we have the two parameters of the equation y = ax + b, we can write the equation that allows us to find any forecasted sales figures:

y = 11,66x + 31,66

 

Calculating the sales forecast

We replace “x” with the rank of the period sought, in our example rank 6 which corresponds to year N+1:

y = (11,66 x 6) + 31,66
y = 69,96 + 31,66
y = 101,62 K€

We can therefore conclude that the forecast turnover for N+1 amounts to €101,62K.

 

Here is a video on the extreme point method.

 

Method No. 3: Mayer's method or Double Average Method

What is the principle of the method?

This linear adjustment sales forecasting method consists of dividing the statistical series into two equal subcategories (if possible).

For each of them, we determine the means and an equation of the form y = ax+b.

Finally we will solve a system of equations, we will find the parameters “a” and “b” then we will carry out the N+1 forecast.

First, I divide the statistical series into two subcategories:

 

Calculating averages and determining the equation

First subcategory:

 

To write the equation of the form y = ax + b, I replace "x" and "y" with the averages.

Hence the equation which is written: 50 = 1,5a + b

 

Second subcategory:

 

Hence the equation which is written: 70,5 = 3,5a + b

 

Solving the system of equations

the equation system is as follows:

50 = 1,5a + b
70,5 = 3,5a + b

To solve this system of equations there are several methods. We will use the subtraction method. We will subtract member by member the first equation from the second equation.

70,5 = 3,5a + b
50 = 1,5a + b
____________
20,5 = 2a

Indeed, 70,5 – 50 = 20,5; 3,5a – 1,5a = 2a and b – b = 0b

So we have :

20,5 = 2a
a = 20,5/2
a = 10,25

To find the value of "b", we replace "a" in one of the two original equations.

I will take the first equation:

50 = 1,5a + b
50 = (1,5 x 10,25) + b
50 = 15,375 + b
b = 50 – 15,375
b = 34,625

We can therefore write:

The equation of the adjustment line which allows us to find any forecast sales figures is written:

y = 10,25x + 34,625

 

Calculating the sales forecast

We replace “x” with the rank of the period sought, in our example rank 6 which corresponds to year N+1:

y = (10,25 x 6) + 34,625
y = 61,5 + 34,625
y = 96,125

We can therefore conclude that the forecast turnover for N+1 amounts to €96,125K.

 

Seasonality of sales

The principle of seasonality of sales

The principle is to determine future turnover by taking into account seasonal variations which are themselves a function of the company's activity.

There are many methods for calculating seasonal coefficients (We will assume that the turnover figures given are quarterly and that we have the forecast turnover).

 

How to calculate the seasonal coefficient?

Sales forecast: The percentage method

Here are the different calculation steps that you must insert into the columns of the final table:

1. calculation of total turnover
2. calculation of the proportion of turnover for each period
3. calculation of forecast turnover for each period

 

Taking our example again and taking into account the details indicated above, we have the following calculations:

Total sales for year N: €241

 

Calculation of the the proportion sales for each quarter :

Quarter 1: 42 ÷ 241 = 0,17 or 17%

Quarter 2: 58 ÷ 241 = 0,24 or 24%

Quarter 3: 64 ÷ 241 = 0,26 or 26%

Quarter 4: 77 ÷ 241 = 0,33 or 33%

The sum of the coefficients gives 1.

 

Calculation of the forecast :

To make the prediction you must multiply the annual forecast turnover  by seasonal coefficient of the quarter in question.

The forecast is made by period and in our example it is the quarter.

So here are the calculations:

The statement also gives the following information: the turnover N+1 is €300K.

Quarter 1: 300 x 0,17 = 51

Quarter 2: 300 x 0,24 = 72

Quarter 3: 300 x 0,26 = 78

Quarter 4: 300 x 0,33 = 99

The sum of the forecasts is equal to the forecast turnover N +1, i.e. €300.

 

Results interpretation

For example, for the second quarter, the interpretation is: The forecast turnover for the second quarter N + 1 is €72K.

 

Sales Forecasting: The Averages Method

Principle of the average method

This method involves calculating the average of the periods, then calculating the seasonal coefficient and finally determining the sales forecast.

Here is an example of a calculation using the average method.

The statement specifies that the forecast annual turnover is €6 and gives the following table:

 

How to calculate the seasonal coefficient?

Here are the different calculation steps that you must insert into the columns of the final table:

1. average turnover per period (online)
2. calculate the average of averages
3. calculate the seasonal coefficient by reporting each average on the average of the averages

Calculation of average turnover per period online:

(1): (2 + 000 + 3) / 500 = 2 and so on for each period (row)

(2): (2 + 800 + 516,66 + 350) / 1 = 833,34

 

The coefficient is calculated by dividing the average of a quarter by the average of the averages. Which gives the following column:

(1): 2 / 800 = 1 and so on for each quarter.

(2): the total of the seasonal coefficients column gives 4 because these are quarterly turnover figures.

 

La sales forecast is done by taking the forecast turnover N+1 divided by 4 and then multiplying it for each quarter by the coefficient of the quarter considered.

(1): (6 / 500) x 4 = 2,036

(2): according to statement

 

Correlation coefficient and sales forecasting

Principle

In the 3 sales forecasting methods we have just seen, turnover is related to time.

This is not always the case. It is possible to relate turnover to another variable.

For example, we can link turnover and advertising budget.

 

Calculation of the correlation coefficient

The calculation of the correlation coefficient is used to show whether the relationship, that is to say the dependence, is strong between the two variables.

The calculation is done from a preparatory table then from a formula.

Example of a preparatory table:

x corresponds to the advertising budget

y corresponds to the turnover figures

"x bar" which reads "average of x" corresponds to the average

“x – x bar” corresponds to the difference between the advertising budget and the average of the periods

"y bar" corresponds to the average turnover

“y – y bar” corresponds to the difference between the turnover and the average turnover

 

Example of a preparatory table (part 2):

 

the first column corresponds to the multiplication of the variations between the average turnover and the average of the years

the second column corresponds to the difference of the years and their average, all squared

the last column is the difference between the turnover and its average, all squared

Here is the formula for the correlation coefficient “r”:

 

Interpretation of the result

The correlation coefficient is always between 1 and -1.

The correlation is strong when the result is close to one of the two extremes: 1 or -1.

 

Conclusion on sales forecasting

The sales forecasting methods are all three different. Each is used in very specific situations.

If the correlation is strong between two parameters then it is appropriate to make a sales forecast.

If you want to apply what you have just read, I strongly invite you to consult my article on corrected management exercises entitled Sales forecast: 6 corrected financial years.

So, now you know how to make a sales forecast using different methods. You no longer have any excuses for not reaching your goal: Get an excellent grade in the Operational Management test!