Sales Forecasting Methods Explained
Translated by
ScribdTranslationsSales Forecasting Methods Explained
Translated by
ScribdTranslationsManagerial decisions
Members
Andres Felipe Ramirez Londoño code 201610946
Maria Alejandra Mejia Arias code 201710403
08/05/2021 Medellín Colombia
1. Example of trend projection
Illustrative example:
With the following data about sales in millions of dollarsthe CompanyM & M:
Year (X) Ventas (Y)
1995 3.4
1996 3.1
1997 3.9
1998 3.3
1999 3.2
2000 4.3
2001 3.9
2002 3.5
2003 3.6
2004 3.7
2005 4
2006 3.6
2007 4.1
2008 4.7
2009 4.2
2010 4.5
1) Find the trend equation using the least squares method.
2) Forecast the trend ofexportfor 2011.
3) Create the graph for the data and the trend line.
Solution:
1) To find the trend equation using the least squares method, the
next table, coding the numbering of the years 1995 as 1, 1996 as 2, and so on
consecutively to facilitate calculations.
Year (X) X Y XY X2 Y2
1995 1 3,4 3.40 1 11.56
1996 2 3.1 6.20 4 9,61
1997 3 3.9 11.70 9 15,21
1998 4 3.3 13.20 16 10.89
1999 5 3.2 16.00 25 10.24
2000 6 4.3 25.80 36 18.49
2001 7 3.9 27,30 49 15,21
2002 8 3.5 28.00 64 12.25
2003 9 3.6 32,40 81 12.96
2004 10 3.7 37.00 100 13.69
2005 11 4 44.00 121 16.00
2006 12 3.6 43.20 144 12.96
2007 13 4.1 53.30 169 16.81
2008 14 4.7 65.80 196 22.09
2009 15 4.2 63.00 225 17.64
2010 16 4.5 72.00 256 20.25
Total 136 61 542.3 1496 235,86
By replacing values in the following formulas, the values of a0 and a1 are obtained:
Interpretation:
Thevalue being positive indicates that there is an upward trend of
theexportsincreasing to achangeor an average rate of 0.07 million dollars per
every year.
The value of indicates the point where the line intersects the Y-axis when X = 0, that is
indica las exportaciones estimadas para el año 1996 igual a 3,22.
Replacing the previous values in the trend line gives:
Y = 3.22 + 0.07X
2) To forecast the export trend for 2011, X = 17 is replaced in the line of
trend, obtaining the following result:
Y = 3,22 + 0,07X
Y = 3.22 + 0.07·17 = 4.41
The calculations inExcelare shown in the following figure:
3) The graph of the data and the trend line created in Excel is shown in the following
figure:
2. What does the Delphi method talk about
The Delphi method is encompassed within the prospective methods of which
they study the future evolution of techno-socio-economic factors and their
interactions. It requires the participation of a group of experts who respond,
anonymously and without interacting with each other, to a series of questionnaires
successive ones that contain questions related to the future
The Delphi forecasting methodology uses expert judgments to visualize the
probable orientations of the development of specific or different technologies
health and social change processes with the following general objectives:
1. Gain a greater intersubjective and prospective understanding of the
chosen theme.
2. Analyze the desires and preferences of characteristic segments or groups in the
thematic areas of research.
3. Promote a structured debate process by visualizing the points of
disagreement among the members of the expert panel.
4. Promote opinion trends among decision-makers.
Thus, the Delphi method is a prospective research technique whose purpose is
raise the level of consensus or visualize the points of disagreement between people
experts in the subject of the research. The forecast from the panel of experts
it is obtained through successive queries, also called rounds, by means of
structured questionnaires with multiple choice proposals and/or questions
open. The provisional results of each round are analyzed and returned to
each expert for their reconsideration and/or further development in rounds
of successive consultations.
In the Delphi method, there are no fixed rules to determine the optimal number of
neither the rounds nor the ideal dimensions of the expert panel. Thus, both issues are
subject to the degree of stability that is desired to be achieved, to the positioning of the panel
consulted and the number of experts deemed necessary to include in the
consultation. Thus, both issues are subject to the degree of stability desired
obtener, al posicionamiento del panel consultado y al número de expertos que se
consider it necessary to include in the consultation.
To correctly interpret your results, it must be taken into account that, given the
systematic process of selecting participants based on their condition of
expert, the surveyed population sample is in no case representative of
the general population. Consequently, the results obtained are of a character
exclusively qualitative in terms of the level of consensus or discrepancy that
previously defined.
Delphi Method
Created in the 1950s, this method is inspired by Greek mythology, more
specifically, in the temple of Delphi. The story goes that people used to visit the
place in search of advice from the priests, who answered the questions of
doubtful manner.
In the sales forecasting universe, this technique is relatively accurate. The
specialists answer questionnaires and, based on their answers, proposals are made
new questions. The information is shared with the group, which must arrive at a
consensus on the topic.
When to use this method
When you have few data;
When your product or service does not yet exist in the market.
2. Método de Suavizamiento Exponencial
This type of sales forecasting focuses on analyzing historical data in search of
of patterns and their changes. It is able to identify the rhythm at which an event occurs and,
based on the combination of mathematical techniques, make projections. By
For example, you can find out how quickly your customer base is growing or what is the
purchase trend of a particular product.
When to use this method
When you have a lot of data about a product or service;
When relationships and trends in your sector are relatively stable.
3. Regression Method
Regression analysis is basically a mathematical equation. Its objective is
to make a correlation between variables and to know which affects the analyzed element in a
determined period of time — in our case, sales.
For example, you can relate the number of calls made by
the sales representatives and the number of products sold in the last five
years.
When to use this method
When you need a deep and detailed understanding of the impact of variables
specific in your sales;
When you have access to a sales software to perform calculations
automatically.
3. Delphi method at Coca-Cola
Coca-Cola is very interesting and the panel of experts worked in a way
jointly sharing their ideas, the Delphi technique allowed them to rise
actions 2.8% with the creation of Coca-Cola Zero being a method
profitable for the company to visualize its forecasts
4. Delphi method in Excel
The first is to use the exponential smoothing tool.
incorporated and available in Excel.
You will have to add your sales data to a standard graph.
before being able to analyze them with this module.
In this example, I will use sales data from the last 12 months to
predict the income for next January or month 13, in this case. Add
the data in 2 columns:
Time period (month, quarter, year)
Ventas(unidades, valor monetario, ingresos recurrentes
monthly...)
Then you will have to click on the Data window at the top.
from your Excel sheet (between Formulas and Review) which will make it appear
a dropdown menu.
Go to the upper right corner and select the Analysis tab
data.
If you do not yet have the Data Analysis feature enabled in Excel, do not you
don't worry because it's very easy to install:
Go to File > Options > Add-ins > Add-ins of
Excel > Analysis tools
Now you just have to complete the form and click accept.
Anyway, when you click on the Data Analysis tab, you will be
will ask you to complete a table with different analysis formulas.
Go ahead and click on exponential smoothing.
You will then be asked to complete the following table:
It's time to fill in the box:
The input range will be your sales figures, so go ahead and
select the cells from B2 to B13 in the allocated space.
The next step is to add the 'smoothing factor'. In case you are not
familiar with the term, it is a reference to the value assigned to the
most recent sales results. It has a value between 0 and
1.
So, if you work in an industry that experiences regular spikes in
The sales, you will need a constant exponential smoothing. You
I suggest you choose a number between 0-0.5.
On the other hand, if your industry is experiencing an increase without
precedents in sales, then you need to put more emphasis on,
example, the last 2-3 time periods. In this case, you see for a
number between 0.6-1.
The last step in the process is to select where you want them to go.
results of exponential smoothing, so let's go for it, click
In cell C2. You will see a line chart and a set of results.
As you can see, the forecast results with smoothing
exponential appear in column C, in addition to being represented
also in a line graph.
To calculate the forecast for month 13, simply click on the part of
down to the right of the forecast value for month 12, and drag towards
down.
The cell will be automatically filled with the expected forecast (in this
case: 199.97