Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org Sale Forecasting of Merck Pharma Company using ARMA Model Nawaz Ahmad Visiting Professor at PAF KIET, Karachi Fouzia Nasir PhD Scholor at Indus University, Karachi Usman Aleem Assistant Professor at PAK KIET, Karachi Abstract This study aims to develop a stochastic framework of model to forecast future sales for pharmaceutical industry. In this regard, the study focuses on Merck Pharmaceutical monthly sales data. This study examines the Sale forecasting models. The study includes monthly data published in the annual reports of the company from Jan. 2008 to Dec. 2012.The time series diagram shows unequal means over the time period that suggests the data is stationary. Having transformed the data, ARMA (1, 1) model is applied which shows that there will be increase in sales by $6.784m given that in the last month sales were $1bn. On the contrary, last month’s residual has an adverse effect on current month sales up to the extent of $432.942m. In this study AR (1) and MA (1) both the processes are significant at 1% Keywords: Sales forecast, ARMA (1, 1), Pharma Industry 1. Introduction Forecasts are needed throughout an organization -- and they should certainly not be produced by an isolated group of forecasters. Neither is forecasting ever "finished". Forecasts are needed continually, and as time moves on, the impact of the forecasts on actual performance is measured; original forecasts are updated; and decisions are modified, and so on. Forecasting is the process of making statements about events whose actual outcomes (typically) have not yet been observed. A commonplace example might be estimation of some variable of interest at some specified future date. Usage can differ between areas of application: for example in hydrology, the terms "forecast" and "forecasting" are sometimes reserved for estimates of values at certain specific future times, while the term "prediction" is used for more general estimates, such as the number of times floods will occur over a long period. Risk and uncertainty are central to forecasting and prediction; it is generally considered good practice to indicate the degree of uncertainty attaching to forecasts.Although quantitative analysis can be very precise, it is not always appropriate. Some experts in the field of forecasting have advised against the use of mean square error to compare forecasting methods. Sales forecasting is an essential tool in the master budget. The sales budget is one of the most important tools because the accuracy of other budgets depends on the accuracy of the sales budget. The sales budget is dependent upon sales forecasts, therefore analyzing past patterns of sales, general economic conditions and other factors aid in creating the sales budget. Sales forecasting is done by predicting sales under a set of conditions based on past sales and future outlooks. In creating a sales forecast, past performance and analysis of expected market conditions are the two major factors that are taken into consideration. The sales budget is dependent upon sales forecast therefore creating an accurate sales forecast will be beneficial to Guillermo. Sales forecasts are also supported by documentation on how much the client plans on spending and the profit that will be received for the sales. It is essential that Guillermo use every effort when creating the sales forecast to assure that his budgeting is done correctly. (Jock, 2008). The sales forecast is essential to assure inventory levels are accurate, staffing plans meet the requirement and suppliers, customers and investors are pleased with the outcome of the product and the amount being distributed. There are many risks that can be associated with sales forecasting due to the financial risk that can be associated because of producing too much inventory and having to many or too few resources to meet the sales that are going out. (Jock, 2008). It is essential that Guillermo take past information from his Flex Budget and have an understanding of the demand for his product based on future estimation if he plans to be successful with his business venture. Forecasting is not based on intuition but on calculations and are an essential tool in the master budget in order to prepare for future business ventures. In planning, one must make forecasts. In making forecasts, you consider a number of possibilities (called “states” of the world). One possibility is that business conditions will be “normal”, another that the 30 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org economy will go into a mild recession and business suffers a little, and so on. To each possibility one associates a likelihood (or “probability”). If we have taken into account all possible outcomes, then these probabilities must sum to unity. Each possible “outcome” implies certain things about your business – e.g., if the economy experiences a mild recession your sales may drop. When all these outcomes are considered some average (“expected value”) of your future sales is obtained. Forecasts assist the finance group as they develop revenue plans, determine appropriate expense levels, and forecast the profitability of the company. The operations group uses forecasts to develop a production schedule, to make component buying decisions, and to plan for any required capacity changes needed to meet demand. Developing a sales forecast for existing products can easily be arrived at by conducting a statistical analysis of historical sales data and then combining this information with anticipated changes in market dynamics, sales organization structure and pricing. Forecasting sales revenue and product utilization for novel medical technologies becomes much more difficult due in part to the lack of historical sales data and the unknowns associated with a new product in the marketplace. Using input from market based assumptions and company related parameters, a spreadsheet-based model can be built which allows the user to more accurately forecast sales revenue and product demand. With these models, users can determine the effect that changes to baseline assumptions can have on the forecast. As a standalone company, Merck & Co. faces a tough future of declining sales. Data monitor forecasts that the proposed merger with Schering-Plough will succeed in returning Merck to a positive sales growth outlook. If management delivers on its promise of an additional $3.5bn of annual cost savings beyond 2011, Data monitor calculates that the combined company will see operating profit growth rate accelerate to a strong 6.9% compound annual growth rate (CAGR) 2008-13. As it is evident from Figure 5, in terms of generated revenues for 2008, Merck’s main three areas of focus are cardiovascular, respiratory and infectious diseases. Coupling that with the knowledge that out of the $12bn portfolio that will be exposed to generic competition by 2015, 29% comes from cardiovascular, 36% from respiratory and seven percent from infectious diseases, the need for a drastic deal that will reshape Merck’s future becomes apparent. Within the cardiovascular arena, Merck’s hypertension portfolio (Cozaar and Hyzaar) also holds great importance with sales approaching $3.6bn in 2008. However, patent expiries will all but eliminate this revenue stream from 2010 onwards. These imminent patent expiries, in combination with the challenges faced by the cholesterol franchise have left Merck in a highly exposed position with regards to its cardiovascular products.In addition, the resulting company is expected to change its focus in the cardiovascular arena from primary care indications to indications mainly covered by specialists. Merck has performed an impressive entry in the diabetes therapy area leapfrogging Novartis to bring the first, and still only, DPP-4 (dipeptidyl peptidase-4) inhibitor in the market. With sales of $1.4bn in 2008, two years after launch, Januvia (sitagliptin) has had the second most successful launch in the cardiovascular and metabolic diseases arena, behind only Pfizer’s mega-blockbuster Lipitor. Merck has already launched a combination with metformin (Janumet) and is developing a combination with pioglitazone (MK-0431c). The FDA changed the requirements for approval of antidiabetic medications late in 2008, raising the bar in terms of proving cardiovascular safety and at the same time ensuring Januvia enjoys an even longer period in the market without any true competitors, Dr. Karachalias says. “Schering-Plough has virtually nothing to offer in the diabetes arena both in terms of marketed products and in terms of developmental pipeline. Inevitably, Merck’s focus in the therapy will be somewhat diluted post-merger. 2. Literature review Although quantitative techniques are arguably very useful and often should be part of a company’s forecasting process, they have certain weaknesses that can be counterbalanced by the use of qualitative forecasting. (Fulcher, 1998; Moon and Mentzer, 1999; Helms et al., 2000). Quantitative time-series techniques are designed to identify and forecast trends and seasonal patterns in data and to adjust quickly to changes in these trends or patterns. Their limitation is that Assortment forecasting method 141 they do not consider contextual information, such as price changes (Mentzer and Schroeter, 1994). Regression analysis makes it possible to take such factors into consideration, but the complexity of the method and its significant data requirements limit its use (Lapide, 1999). Neither of the methods does well in dealing with changes that have never happened before, or that have happened before but for which no data exist in the system. This is where expert judgment can add significant value to the forecasting process. (Mentzer and Bienstock, 1998). Situations in which expert judgment is needed include, in addition to the price changes mentioned 31 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org above, assortment changes, promotions, competitor activities, and product introductions. The best information concerning these situations oftentimes resides with the company’s marketing and sales personnel (Fulcher, 1998; Fosnaught, 1999; Moon and Mentzer, 1999; Helms et al., 2000; Jain, 2000; Reese, 2000). Although both researchers and practitioners seem to agree that sales force involvement in forecasting is important, benefiting from it in practice can be difficult. Several motivational, organizational, and tool-related obstacles have been identified. In their in-depth study of the sales forecasting management practices at 33 companies, Moon and Mentzer (1999) found there to be some resistance from salespeople concerning their forecasting responsibilities in almost all of thecompanies studied. Many salespersons felt that it was not their job to forecast and that time spent on forecasting was time taken away from their real job of managing customer relationships and selling products and services. Similar observations have been reported by Helms et al. (2000) and Reese (2000). According to Reese (2000), these motivational problems are often aggravated by the lack of forecasting incentives; salespersons are seldom rewarded for producing accurate forecasts. Moreover, Moon and Mentzer (1999) claim that even when companies get salespeople to forecast, they tend to do a relatively poor job. As they put it: even when the salespeople are provided with a history of their customers’ demand patterns, they frequently will either see patterns that do not exist, or will fail to see patterns that do exist (Moon and Mentzer, 1999). Based on their research, Moon and Mentzer (1999) have compiled a set of guidelines for overcoming the barriers that hinder companies from fully benefiting from sales force involvement in forecasting. They suggest that companies should: Make forecasting part of the salespeople’s job by including forecasting as a part of their job descriptions, creating incentives for high performance in forecasting, and providing feedback and training. Minimize game playing by making forecasting accuracy an important outcome for salespeople and clearly separating sales quotas from forecasts. Keep it simple by asking salespeople only to adjust statistically generated forecasts rather than producing forecasts from scratch and by providing them with adequate tools that enable them to complete their forecasting work as efficiently as possible. . Keep it focused by having the salespeople deal only with the products and customers that are truly important and where their input can significantly affect the company’s supply chain. The first two recommendations concern organizational and motivational factors, such as rewards, job descriptions, and training. The other two are about creating forecasting processes and tools that support sales force involvement by making forecasting simpler, more efficient, and more focused on the products and customers that really matter. These latter ones are the focus of this paper. 3. Methodology 3.1 Data and Variable The data extracted from the annual report of Merck pharmaceutical company. The selected data is based on monthly sales from Jan 2008 to Dec 2012. 3.2 Data analysis The selection and application of correct prediction methodology has always been an important issue of planning and control for most companies and organizations. Often, the financial well-being of the entire operation rely on the accuracy of the forecast since such information will likely be used to make interrelated budgetary and operative decisions in areas of personnel management, purchasing, marketing and advertising, capital financing, etc. If, on the other hand, has historically experienced signature pattern upward and downward sales, then the complexity of the task is compounded prediction. Data is analyzed via E – views 6, an econometric package. 3.3 Model We use Correlogram followed by Unit root test for Stationarity. Then Auto Regressive Moving Average – ARMA (1, 1)model to find whether trailing sales and first lag of error term has significant impact while sales is forecasted. 3.4 Hypothesis H1: Trailing sales has no impact on current sales. H2: First lag of error has no impact on current sales. 32 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 4. www.iiste.org Results and Discussion Date: 06/04/15 Time: 12:59 Sample: 2008M01 2012M04 Included observations: 52 Autocorrelation . |******| . |******| . |***** | . |***** | . |***** | . |**** | . |**** | . |**** | . |**** | . |*** | . |*** | . |** | . |** | . |*. | . |*. | . |*. | .|. | .|. | .|. | .|. | .*| . | .*| . | .*| . | .*| . | Partial Correlation . |******| . |*. | . |*. | .|. | . |*. | .|. | .|. | .|. | . |*. | .*| . | .|. | .|. | .*| . | .*| . | . |*. | .*| . | .|. | .|. | .|. | .*| . | .|. | .|. | .|. | .|. | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 AC PAC 0.874 0.789 0.735 0.674 0.646 0.605 0.564 0.516 0.494 0.437 0.381 0.345 0.269 0.193 0.169 0.108 0.073 0.054 0.003 -0.057 -0.095 -0.113 -0.154 -0.170 0.874 0.106 0.114 -0.020 0.129 -0.032 0.006 -0.061 0.097 -0.157 -0.033 -0.007 -0.155 -0.135 0.140 -0.159 0.071 0.004 -0.055 -0.144 0.049 0.051 -0.058 -0.018 Q-Stat 42.035 76.953 107.92 134.47 159.44 181.81 201.67 218.68 234.63 247.39 257.33 265.67 270.87 273.63 275.80 276.71 277.14 277.38 277.38 277.67 278.48 279.67 281.98 284.90 Prob 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 As we can observe a decreasing pattern in auto correlation function (ACF) which further gets into negative and makes a wave shape, it depicts sales data is stationary. ACF is associated with moving averages. Furthermore, partial autocorrelation function (PACF) is significant at first lag which suggests that only first lag of sales ie, last month’s sales has a significant effect on current sales as it is associated with auto regression. The time series diagram shows monthly sales for the period from 2008- 2011 which shows unequal means over the time period that suggest the data is stationary. 33 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org Date: 06/04/15 Time: 13:39 Sample: 2008M01 2012M04 Included observations: 51 Autocorrelation ***| . | .|. | . |*. | **| . | . |*. | .|. | . |*. | .*| . | .|. | .|. | .*| . | . |** | .*| . | .|. | . |*. | .*| . | .|. | . |*. | .|. | **| . | . |*. | .*| . | .|. | . |*. | Partial Correlation ***| . | .*| . | . |*. | .*| . | .|. | .|. | . |*. | .*| . | .|. | .|. | . |*. | . |** | .|. | .|. | . |*. | .|. | .*| . | .|. | . |*. | **| . | .|. | .*| . | .|. | .|. | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 AC PAC -0.377 -0.031 0.203 -0.226 0.097 -0.029 0.123 -0.172 0.070 0.066 -0.073 0.233 -0.110 -0.051 0.169 -0.141 -0.022 0.092 0.005 -0.223 0.162 -0.070 -0.052 0.208 -0.377 -0.202 0.137 -0.115 -0.005 -0.060 0.188 -0.131 0.004 0.017 0.087 0.214 0.073 -0.064 0.139 0.001 -0.090 -0.018 0.089 -0.217 -0.034 -0.171 0.007 0.065 Q-Stat 7.6867 7.7410 10.062 13.011 13.568 13.620 14.549 16.413 16.727 17.010 17.369 21.146 22.012 22.206 24.349 25.890 25.929 26.615 26.617 30.962 33.339 33.798 34.054 38.376 Prob 0.006 0.021 0.018 0.011 0.019 0.034 0.042 0.037 0.053 0.074 0.097 0.048 0.055 0.074 0.059 0.056 0.076 0.087 0.114 0.056 0.043 0.052 0.064 0.032 Since the sales data was stationary at level, the first difference was taken and again the data was run and now it has been observed that sales data become non stationary as there is no loop observed in ACF. Furthermore, PACF is also significant for lag 1. Since ACF and PACF both are significant at first lag, and ACF follows MA process and PACF follows AR process therefore Auto Regressive Moving Average i.e. ARMA (1, 1) process is the most suitable model to forecast monthly sales which follows first lag of sales and first lag of error term. 4.1 ARMA Model Dependent Variable: SALES Method: Least Squares Date: 06/04/15 Time: 13:58 Sample (adjusted): 2008M03 2012M04 Included observations: 50 after adjustments Variable Coefficient Std. Error t-Statistic Prob. C SALES(-1) E(-1) 511.0677 1.006784 -0.432942 3199.698 0.054696 0.143863 0.159724 18.40686 -3.009405 0.8738 0.0000 0.0042 R-squared Adjusted R-squared S.E. of regression Sum squared resid Log likelihood F-statistic Prob(F-statistic) 0.884200 0.879272 4210.816 8.33E+08 -486.6706 179.4354 0.000000 Mean dependent var S.D. dependent var Akaike info criterion Schwarz criterion Hannan-Quinn criter. Durbin-Watson stat 34 58387.60 12118.87 19.58682 19.70155 19.63051 2.203031 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org In ARMA output, both first lag of sales and error term is significant at 1%. There will be increase in sales by $6.784m given that in the last month sales were $1bn. On the contrary, last month’s residual has an adverse effect on current month sales up to the extent of $432.942m. Moreover, there is no sample error as the difference between r-square and adjusted r-square is as low as 0.5%. Overall model is also significant as F-statistics is quite high (179.43). There is no evidence of strong auto correlation as Durbin-Watson statistics is closer to 2. 5. Conclusion This study concludes that the sales data has unit root which has overcome via taking first difference. Furthermore, the pattern of ACF indorses the presence of unit roots whereas PACF suggests that only first lag of error term is significance. There will be increase in sales by $6.784m given that in the last month sales were $1bn. On the contrary, last month’s residual has an adverse effect on current month sales up to the extent of $432.942m. Both the variables are significant at 1%. 6. References Armstrong, J.S. (2001), “Selecting forecasting methods”, in Armstrong, J.S. (Ed.), Principles ofForecasting, Kluwer Academic Publishers, Norwell, MA. Basu, S. and Schroeder, R.G. (1977), “Incorporating judgments in sales forecasts: application ofthe Delphi method at American Hoist and Derrick”, Interfaces, Vol. 7 No. 3, pp. 18-27. Bunn, D. and Wright, G. (1991), “Interaction of judgemental and statistical forecasting methods:issues& analysis”, Management Science, Vol. 37 No. 5, pp. 501-18. Cadeaux, J.M. (1997), “A closer look at the interface between the product lines of manufacturersand the assortments of retailers”, International Journal of Retail & DistributionManagement, Vol. 25 No. 6, pp. 197-203. Dalrymple, D.J. (1988), “Sales forecasting practices”, International Journal of Forecasting, Vol. 3No. 3, pp. 37991. Edmundson, R.H., Lawrence, M.J. and O’Connor, M.J. (1988), “The use of non-time series data insales forecasting: a case study”, Journal of Forecasting, Vol. 7 No. 3, pp. 201-11. Fildes, R. (1991), “Efficient use of information in the formation of subjective industry forecasts”,Journal of Forecasting, Vol. 10 No. 5, pp. 597-617. Fosnaught, K. (1999), “The strategic power of consensus forecasting: setting your organizationup to win”, Journal of Business Forecasting Methods & Systems, Vol. 18 No. 3, pp. 3-7.Freeland, K. (2003), Personal communication, 14 August. Fulcher, J. (1998), “A common vision”, Manufacturing Systems, Vol. 16 No. 2, pp. 88-94. Helms, M.M., Attain, L.P. and Chapman, S. (2000), “Supply chain forecasting – collaborativeforecasting supports supply chain management”, Business Process Management Journal,Vol. 6 No. 5, pp. 392-407. Herbig, P., Milewicz, J. and Golden, J.E. (1993), “Forecasting: who, what, when and how”, Journalof Business Forecasting Methods & Systems, Vol. 12 No. 2, pp. 16-21. Holmstro¨m, J. (1998), “Handling product range complexity: a case study on re-engineeringdemand forecasting”, Business Process Management Journal, Vol. 4 No. 3, pp. 241-58. Jain, C.L. (2000), “Editorial: which forecasting model should we use?”, Journal of BusinessForecasting Methods & Systems, Vol. 19 No. 3, pp. 2-4.IJPDLM34,2156 Kahn, K.B. (1998), “Benchmarking sales forecasting performance measure”, Journal of BusinessForecasting Methods & Systems, Vol. 17 No. 4, pp. 19-23. Lapide, L. (1999), “New developments in business forecasting”, Journal of Business ForecastingMethods & Systems, Vol. 18 No. 2, pp. 13-14. Lim, J.S. and O’Connor, M. (1996), “Judgmental forecasting with time series and causal information”, International Journal of Forecasting, Vol. 12 No. 1, pp. 139-53. McClelland, A.S., Raman, A. and Fisher, M. (2000), “Supply chain management at World Co Ltd.”,Harvard Business School Case N9-601-072. Makridakis, S. (1988), “Metaforecasting: ways of improving forecasting accuracy and usefulness”, International Journal of Forecasting, Vol. 4 No. 3, pp. 467-91. Mentzer, J.T. and Bienstock, C.C. (1998), “The seven principles of sales-forecasting systems”,Supply Chain Management Review, Fall, pp. 76-83. Mentzer, J.T. and Kahn, K.B. (1995), “A framework of logistics research”, Journal of BusinessLogistics, Vol. 16 No. 1, pp. 231-50. Mentzer, J.T. and Schroeter, J. (1994), “Integrating logistics forecasting techniques, systems, andadministration: the multiple forecasting system”, Journal of Business Logistics, Vol. 15No. 2, pp. 205-26. Moon, M.A. and Mentzer, J.T. (1999), “Improving salesforce forecasting”, Journal of BusinessForecasting, Vol. 35 Research Journal of Finance and Accounting ISSN 2222-1697 (Paper) ISSN 2222-2847 (Online) Vol.6, No.21, 2015 www.iiste.org 18 No. 2, pp. 7-12. Muir, J.W. (1979), “The pyramid principle”, Proceedings of 22nd Annual Conference, AmericanProduction and Inventory Control Society, pp. 105-7. Peterson, R.T. and Jun, M. (1999), “Forecasting sales in wholesale industry”, Journal of BusinessForecasting Methods & Systems, Vol. 18 No. 2, pp. 15-17. Plossl, G.W. (1973), “Getting the most from forecasts”, Production and Inventory Management,Vol. 14 No. 1, pp. 1-16. Reese, S. (2000), “The human aspects of collaborative forecasting”, Journal of BusinessForecasting Methods & Systems, Vol. 19 No. 4, pp. 3-9. Safavi, A. (2000), “Choosing the right forecasting software and system”, Journal of BusinessForecasting Methods & Systems, Vol. 19 No. 3, pp. 6-10. Sanders, N. and Manrodt, K. (1994), “Forecasting practices in US corporations: survey results”,Interfaces, Vol. 24 No. 2, pp. 92-100. Sparkes, J.R. and McHugh, A.K. (1984), “Awareness and use of forecasting techniques in Britishindustry”, Journal of Forecasting, Vol. 3 No. 2, pp. 37-42. Webby, R. and O’Connor, M. (1996), “Judgemental and statistical time series forecasting: a reviewof the literature”, International Journal of Forecasting, Vol. 12 No. 1, pp. 91-118.Assortment forecasting method157. 36