How to Choose the Best Forecasting Method | Marketing & Sales Example

How to Choose the Best Forecasting Method | Marketing & Sales Example:

Hi Readers! Here, today we will learn on How to Choose the Best Forecasting Method with sales and marketing examples and also simultaneously study the different types of forecast methods in excel and their interpretation. As we know sales volume may not be constant in every month it depends on several factors like market demand, supply quantity, buyer interest, season, festive time, price, product features, etc. but as a market sales person how can you easily predict the future sales volume w.r.t your past sales data. If you can do it easily then similarly you can prepare the manufacturing/service plan accordingly. It could give an approximate idea for business planning. So forecasting is one of the common methodologies used in several types of industries for predicting the future based on the results of previous data.

So, especially we will talk about a sales-related example here, and different types of forecasting methods or processes or types. After reading this article I think you can be understood and select or choose or decide the best forecast methods.

Example:

Suppose a company PQR Ltd has the following below sales quantity for the last 8 months and based on the past 8 months data the sales manager is going to forecast the sales volume of 9th month but after calculating of forecast quantity of 9th month he is confused to select method which will be best. But here we will help you to choose and select the best forecast method, just go through the below concept.   

MonthSales Quantity
Jan150000
Feb249000
Mar351000
Apr448000
May552000
Jun656000
Jul749500
Aug851300
Sep9??

[Table 1-Sales Quantity of past 8 months]

As you know that there are so many forecasting methods available but which one will give you the best forecast value for the 9th month, it is difficult to select at an initial time so we are going to calculate the forecast value by 3 to 4 methods and will calculate the error of each method to select the best one.

Method-1: 3-Months Moving Average Forecasting

MonthSales Quantity3-Months Moving Average Forecast
Jan150000NA
Feb249000NA
Mar351000NA
Apr44800050000
May55200049333
Jun65600050333
Jul74950052000
Aug85130052500
Sep9??52267

9th Month Value= (56000+49500+51300)/3 = 52266.66

Method-2: Exponential Smoothening Forecast (let’s say, factor, alpha=0.8)

MonthSales QuantityExponential Smoothening Forecast (factor, alpha=0.8)
Jan150000NA
Feb24900050000
Mar35100049200
Apr44800050640
May55200048528
Jun65600051306
Jul74950055061
Aug85130050612
Sep9??51162

9th Month Value= 0.8*51300+0.2*50612 = 41040+10122.4 = 51162.4

Method-3: Linear Regression.

MonthSales Quantity
Jan150000
Feb249000
Mar351000
Apr448000
May552000
Jun656000
Jul749500
Aug851300
Sep9??
How to Choose the Best Forecasting Method

After plotting the graph, we found the Linear regressing function is 364.2*X+49211 and by using the function we have to calculate the forecast value as given below;

MonthSales QuantityLinear Regression
Jan15000049575
Feb24900049939
Mar35100050304
Apr44800050668
May55200051032
Jun65600051396
Jul74950051760
Aug85130052125
Sep9??52489

9th Month Value= 364.2*9 + 49211 =3277.8+49211 = 52488.8

Method-4: Polynomial Regression.
MonthSales QuantityPolynomial Regression
Jan15000049016
Feb24900049859
Mar35100050542
Apr44800051066
May55200051430
Jun65600051635
Jul74950051680
Aug85130051565
Sep9??51291
How to Choose the Best Forecasting Method

9th Month Value= -79.76*9*9 + 1082*9 +48014 =-6460.56+9738+48014 =51291.44

Methods 1 to 4 in single Table:
MonthSales Quantity3-Months Moving Average ForecastExponential Smoothening Forecast (factor, alpha=0.8)Linear RegressionPolynomial Regression
Jan150000NANA4957549016
Feb249000NA500004993949859
Mar351000NA492005030450542
Apr44800050000506405066851066
May55200049333485285103251430
Jun65600050333513065139651635
Jul74950052000550615176051680
Aug85130052500506125212551565
Sep9??52267511625248951291
How to Choose the Best Forecasting Method?

Now, the most important thing is to select or choose or decide the best method from the above 4 types of forecasting methods. As you can see in the above table we have mentioned the forecast value of the possible period of all 4 types of methods but even after getting the forecast value it is difficult to select the best method, so to choose the best one, we are supposed to calculate the error of each method.

MAF-Error ESF-Error LR-Error PR-Error
MonthSales Quantity3-Months Moving Average ForecastErrorSquare ErrorErrorSquare ErrorErrorSquare ErrorErrorSquare Error
Jan150000NANA NA 425180455984967783.7
Feb249000NANA -1000.001000000-939882472.4-859737812.3
Mar351000NANA 1800.003240000696484973458209617.5
Apr44800050000-2000.004000000-2640.006969600-26687117157-30669399375
May552000493332666.6771111113472.0012054784968937024570324900
Jun656000503335666.67321111114694.4022037391460421194974436519056368
Jul74950052000-2500.006250000-5561.1230926056-22605109408-21804751354
Aug85130052500-1200.001440000687.78473035.8-825679965.2-26570415.93
Sep9??52267MSE10182444 1095726745733044439703
RMSE3190.993310.182138.5282107.06

According to the above RMSE of all four forecasting methods, we found that the Polynomial Regression method is the best method in this case. So we can consider the forecast sale quantity of 9th month is 51291.

Free Templates / Formats of QM: we have published some free templates or formats related to Quality Management with manufacturing / industrial practical examples for better understanding and learning. if you have not yet read these free template articles/posts then, you could visit our “Template/Format” section. Thanks for reading…keep visiting techiequality.com

Popular Post

Add a Comment

Your email address will not be published. Required fields are marked *

error: Content is protected !!