Tuesday, January 23, 2018
Home > Pharmaceuticals > Challenging Art: Forecasting

Challenging Art: Forecasting

Forecasting is divided into two parts—Demand forecasting and Sales forecasting. In the most complex and competitive Indian Pharma market it is tough to forecast both—demand and sales.

For demand forecasting Jesse Werner gives us good advice saying “ For most accurate forecasting forget your present volumes; start with the future trends of the economy , then mix in projections of market demands, and customer needs and then finally evaluate your own firm’s prospects for expansion and growth”.

Forecasting demand for a product is tougher than forecasting sales for a product as demand forecasting requires a lot of data which may not be easily available and after making it available it is complex to use and forecast. Demand for a new product could be forecast by ‘test marketing’ and / or conducting a ‘prescription intension survey’ among doctors.

Sales forecast affects every department of the company. And, hence it has to be done meticulously and accurately.

 

 

 

 

 

 

 

 

 

Once the sales forecast is made, Production department gears up and makes all the necessary arrangement to produce hundreds of products as per the forecast and as required by marketing department on weekly or monthly basis. It has to ensure that sales does not suffer because of unavailability of products. Purchase department also roles up the sleeves and makes procurement programmes to supply every raw material to production department so that its production does not halt up. Purchase department works in tandem with inventory control and ensures that money is not locked up in unnecessary items and smooth supply of raw material is maintained. Logistics and Supply Chain management department arranges to and fro movements of finished products to every nook and corner of the country. Finance department makes arrangements for adequate money so that supply of raw materials and movements of finished products do not suffer. Human Resource Management also gets ready to recruit and train more employees, if required, to achieve sales target. Thus, sales forecast puts every department of the company on its toes.

If the forecast sale is achieved, then every department is happy that it lent support to achieve the target. However, if it’s not achieved, then marketing / sales gets flak from all the departments besides top management. Knowing the importance of sales forecast, once a SCM head of a MNC approached us and asked us if we could train its sales force in sales forecasting as he was one of the affected persons in the company since he was making all the arrangements to carry finished products to every nook and corner of the country and only 70 to 80 percent forecast-sales was being achieved by the company.

Let’s deal more in detail in sales forecasting (for next year), which every Pharma company does it every year and calls it ‘budgeting exercise’. Average time taken by a company is three months to complete product wise budget for the next year. Most of the companies use their experience and expertise to forecast the sales and a few use certain techniques and software to project sales of every product. Sometimes a forecast is made by telling marketing & sales teams that ‘management wants 15 % growth next year.’ On what basis 15 % is decided is not known. In Indian market one MNC was growing at the rate of 10 % to 12 %  per annum and wanted its team to achieve 30 % growth next year. Of course, the MNC did not achieve the 30 % growth. However, marketing and sales team was under tremendous pressure.

 For sales forecasting let’s look at practical methods as follows:

  1. Delphi method
  2. Bottom up method
  3. Regression method
  4. CAGR method 

Delphi Method:

 In Delphi method experts are consulted about the market growth, therapeutic class growth, impact of new products and government policies etc. to get good idea about the market and get a base for forecast. In this method if an experienced person talks to experts he can add better value to forecast, as he would get better perceptions of the market and can use the discussions fruitfully for forecasting sales based on his experience. 

Bottom up Method:

In this method marketing department asks sales to prepare forecast from the lowest rung of its ladder viz. medical representative or professional sales officer. The MR/ PSO looks at his achievement product wise in his territories and quotes some figures product wise keeping something under his sleeves. He will not give the real potential that he can achieve, naturally. Similarly forecast is collected from frontline manager, regional manager, zonal manager and finally from all India sales manager.

Marketing people work out forecast for each product in each area based on some statistical techniques and performance of economy. Then, both the forecasts are dovetailed in an annual meeting where marketing and sales people make presentations about their forecasts and bases for forecast. Such meeting lasts for three days wherein the dovetailing of forecast is done. Once the forecast (targets) are accepted by sales people, then they are frozen and marketing inputs are given to achieve the forecast (sales).

When marketing people make the forecasts they can use very simple statistical methods as regression and CAGR of various methods available to them.

Before we discuss the above methods in detail, let’s keep the following points in mind:

  1. Total Pharma market (size)
  2. Its growth rate
  3. Performance of various TCs (Therapeutic Classes) & their size.
  4. Share and rate of growth of each TC
  5. of brands in TC
  6. Top 5 brands expanding the TC market, their share and their growth rate
  7. Brands losing market share, their share and growth rate
  8. Your brand’s sales, market share and rate of growth
  9. Share of new brands in each TC

Regression:

Regression, simple & multiple, are statistical methods useful to forecast the sale based on the past data. Before simple regression is used keep in mind the following points:

  1. Plot the given data. If you get a straight line indicating linear relationship between the two variables( year & sale) use the regression.
  2. Change the years to 1, 2, 3, 4, 5 etc from year 2011, 2012, 2013, 2014, 2015.
  3. If you have 10 years of data, divide it into two parts viz. first five yers and second five years.
  4. Use current five years data as it is reflects the market reality.
  5. Use SPSS or Excel to do regression analysis.

Let’s take a real case for regression analysis. Let’s look at sales of Cipla for the past 10 years.

Year 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Sale (Rs. Crore) 2891.36 3438.24 3997.90 4960.60 5359.52 6331.09 6977.50 8202.42 9456.90 10131.78

Source: Ace Analyser

The first five years data is from 2006 to 2010 and current five years data is from 2011 to 2015. We would work on the data from 2010 to 2014 and project the sake for 2015 and check the accuracy of our estimate as the 2015 sale is given as Rs. 10131.78

The graph of the data from 2010 to 2014 is as follows:

The graph indicates almost a straight line except a slight dip in the year 2012. There is no erratic pattern of sales and hence we can go ahead with regression model.

The model is Y  = a + bx

Where Y is dependent variable (Sales) and x is independent variable ie. year. b is a regression coefficient.

When we put the data into SPSS, we get, the following tables:

Model Summaryb
Model R R Square Adjusted R Square Std. Error of the Estimate Change Statistics
R Square Change F Change df1 df2 Sig. F Change
1 .993a .987 .982 213.12130 .987 223.084 1 3 .001
a. Predictors: (Constant), Year
b. Dependent Variable: Sale
 ANOVAb
Model Sum of Squares df Mean Square F Sig.
1 Regression 1.013E7 1 1.013E7 223.084 .001a
Residual 136262.065 3 45420.688
Total 1.027E7 4
a. Predictors: (Constant), Year
b. Dependent Variable: Sale
  Coefficientsa
Model Unstandardized Coefficients Standardized Coefficients t Sig. Correlations
B Std. Error Beta Zero-order Partial Part
1 (Constant) 4245.659 223.524 18.994 .000
Year 1006.609 67.395 .993 14.936 .001 .993 .993 .993
a. Dependent Variable: Sale

The Model Summary table above indicates that R = 0.993 and Adjusted R square = 0.982. This means that 98.2 % variation in independent variable (Year) is well explained by the model and hence the model is good. The ANOVA table gives us significant value =0.001, which less than 0.05 indicating that the model built is significant at 5 % level of significance.  Since the model is good and significant, we can use it to predict the sale in 2015.

From coefficient table we get, a =4245.659 and b = 1006.609. We put these values of a and b in the following equation.  Value of x will be 6 as we have 5 years data and we estimating sales for the 6th year (2015).

Y = a + bx

Y= 4245.659 + 1006.659 X 6

   = 4245.659 + 6039.954

 = 10,285.613

The estimated sale is Rs. 10,285.613 and actual sale is 10131.78.

Thus, we get efficiency of our estimate as

Efficiency = Estimated sale / Actual sale = 10,285.613 / 10,131.78 =1.0158. It means that we, by regression method, have over estimates sale of Cipla for the year 2015 by 1.58 %. It’s a very good accuracy.

 Now we will estimate sale of Cipla for 2016. For this estimation, we will use data from Cipla’s table from 2011 to 2015. Applying regression for the same data we get estimated sale for 2016 as Rs.11, 243.468. The regression model, as we know, is over estimating sale of Cipla by 1.58 %. Applying this correction to the projected sale of Rs. 11,243.468 we get actual estimated sale for 2016 as Rs.11, 068.5844.

Similarly, let’s take sale of Colgate Palmolive (India) Ltd. which is as follows:

Year 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016
Sale (Rs.Cr) 1295.14 1473.38 1694.81 1962.46 2286.12 2693.23 3163.81 3578.81 3981.94 4162.29

 Source: Ace analyser

Putting data from 2011 to 2015 in SPSS and working out regression, we get a = 1857.616 and b = 427.722. Substituting these values in the equation Y = a = + bx, we get estimated sale of Colgate for 2016 is Rs. 4418.332. Here, the Efficiency of the estimate = 4418.332 / 4162.29 = 1.0615 %. This indicates that Colgate’s sale for 2016 is over estimated by 6.15 %.

If we can estimate sales 90 % accurately, it can be of great help to us. In both the cases—Cipla and Colgate we do not know the marketing inputs provided by the companies and still we could estimate the sales accurately. This method of regression will be of great help in practice. If we know the marketing inputs we can use the multiple regression. 

CAGR Method:

This method is compound annual growth rate method. This gives us rate of growth of product or company sales. The formula for CAGR is as follows:

A = P (1+r/100) n-1

Where, A = Latest year sale

              P = base (first) year sale

              R = rate of growth

              N = no. of years of data

The formula could be solved in the following easy four steps.

Referring to Cipla’s table, we get Sales for 2011 = Rs.6331.09 and sale for 2015 = 10131.78

For the data from 2011 to 2015, sale of 2011 is base year sale and that of 2015 is the latest year sale. Substituting these values in the following formula we get,

Step 1 : latest year sale/ base year sale = 10131.78/6331.09 = 1.6003

Step 2: take 4th root (since we have five years data) of 1.6003 = 1.1247

Step 3 : 1.1247- 1 = 0.1247

Step 4 : 0.1247 X 100 = 12.47 %

This indicates that Cipla’s growth rate between the years 2011 to 2015 is 12. 47 %

Applying this growth rate to its 2015 sale of Rs. 10131.78, we get estimated sale for the year 2016 as Rs. 11,395.21

Similarly, the rate of growth of Colgate is worked out as follows:

Step 1 : latest year sale / base year sale = Rs.4162.29/2693.23 = 1.5455

Step 2: 4th root of 1.5455 = 1.11497 = 1.1150

Step 3 : 1.1150 – 1 = 0.1150 (0.1150 X 100 = 11.50 % rate of growth of Colgate between period 2012 to 2016)

Step 4 : 1.1150 X 4162. 29 = 4640. 95 (Estimated sale of Colgate in 2017)

The two methods discussed above—Regression and CAGR- are simple and practical. Companies should follow them and work out a base forecast. The forecast, thus worked out, should become the point of discussions and the experienced managers should now add their expertise, wisdom and market insight to fine tune the forecast. Such forecasts will help all the concerned departments to work in sync and achieve it.

Leave a Reply

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