A PROJECT REPORT ON DEMAND FORECASTING OF RETAIL SUPPLY CHAIN MANAGEMENT USING STATISTICAL ANALYSIS By AVINASH KUMAR SONEE2005B3A8582G KRISHNA MOHAN YEGAREDDY2006B3PS704P AT HETERO MED SOLUTIONS LIMITED Madhuranagar, Hyderabad A Practice School–II station of [pic] BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI DECEMBER, 2009 A PROJECT REPORT On DEMAND FORECASTING OF RETAIL SUPPLY CHAIN MANAGEMENT USING STATISTICAL ANALYSIS by AVINASH KUMAR SONEE – (M. Sc. (Hons.

) Economics) – 2005B3A8582G KRISHNA MOHAN YEGAREDDY – (M. Sc. (Hons. ) Economics) – 2006B3PS704PPrepared in partial fulfillment of the Course PRACTICE SCHOOL-2 Submitted to Mr. J. T. RAO [pic] BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE, PILANI DECEMBER, 2009 ACKNOWLEDGEMENTS We would like to thank the founder and chairman of our PS Hetero Drugs Ltd.

, Mr. B. Parthasaradhi Reddy for giving us the opportunity to work in the prestigious company.

Mr. B. Vamsi Krishna, MD, Hetero Med Solutions Ltd. has inspired and guided us since the beginning of the project and we owe a great deal of knowledge earned during the project to him. We also thank Mr. Suresh, Manager, I. T.

Department and the in charge of our project for his invaluable assistance, encouragement and suggestions for improvement and taking his time to guide us for the entire project. We are indebted to Mr. G. Sundar, Dean, Practice School Division BITS-Pilani for the opportunity to work at Hetero Drugs . Ltd. Hyderabad as part of Practice school- II programme. We owe our sincere thanks to Prof.

J. T. Rao, our PS Instructor for extending his constant support and guidance. We also are grateful to few other people who have directly or indirectly helped us in our successful completion of the project.BIRLA INSTITUTE OF TECHNOLOGY AND SCIENCE PILANI (RAJASTHAN) Practice School Division Station: Hetero Med Solutions Ltd. Centre: Hyderabad Duration: Six months (From: 2 July, 2009 to: 12 December, 2009) Date of Submission: 10th Dec, 2009 Title of the Project: DEMAND FORECASTING OF RETAIL SUPPLY CHAIN MANAGEMENT USING STATISTICAL ANALYSIS ID No. : 2005B3A8582G Name: AVINASH KUMAR SONEE 2006B3PS704P KRISHNA MOHAN YEGAREDDY Discipline: M.

Sc. (Hons. ) Economics Name(s) of Experts: Mr. B. Vamsi Krishna, Managing Director, Hetero Med Solutions Ltd.

Mr. Suresh, Manager, I.T. Dept. , Hetero Med Solutions Ltd. Name of the PS Faculty: Prof. J. T.

Rao Key Words: ARIMA, Auto ARIMA, Lead Time, Moving Average, Re-Order Level (ROL), Seasonality, Safety Stock, Time Series. Project Areas: Demand Forecasting, R Programming, Inventory Management. Abstract Demand Forecasting is an integral part of any retail supply chain management system and we have presented a case of Retail Pharmacy (Hetero Pharmacy) for our project using the historical Sales data of different drugs.

We analyzed the data and then applied different statistical models using R Programming and Spreadsheet.We have also studied cases of seasonality and trends and forecasted the values for those series by de-seasonalizing. After forecasting, we have also calculated the Re-Order Level (ROL) which is used to control the inventory levels. Signature of Student Signature of PS Faculty Date: Date: CONTENTS 1. NATURE AND SOURCES OF ECONOMIC DATA- 8 – 1.

1. Economic data- 8 – 1. 2. Time series data- 8 – 2. DEMAND FORECASTING- 10 – 2. 1. Qualitative forecasting methods- 11 – 2. 2.

Quantitative forecasting methods(Time Series Forecasting Methods)- 11 – 2. 3.Qualitative Forecasting Method – Example- 12 – 2. 4. Quantitative forecasting methods- 14 – 2. 4. 1.

Simple moving average method- 14 – 2. 4. 2. Weighted moving average method- 15 – 2. 4. 4. Double exponential smoothing (Trend-adjusted exponential smoothing)- 17 – 2.

4. 5. Multiplicative Seasonal Method- 18 – 2.

4. 6. Simple Regression Method (Trend Line Model)- 20 – 2. 4. 7. Arima (Box Jenkins Methodology)- 20 – 2.

4. 8. Automated Arima Model Estimation And Selection – (Auto Arima)- 26 – 3.

APPLICATION OF DIFFERENT MODELS FOR OUR PRODUCTS- 27 – 3. 1. Moving Average (Example)- 27 – 3. 2. Exponential Moving Average (Example)- 29 – . 3.

AUTO ARIMA (Example)- 31 – 4. SEASONALITY- 40 – 4. 1. Seasonal Variation- 46 – 5. RE-ORDER LEVEL (ROL)- 47 – 6.

CONCLUSION- 50 – REFERENCES- 51 – INTRODUCTION For any Retail Supply Chain Management system, forecasting forms the foremost and also becomes an integral part of the system. For a retail pharmacy, forecasting is more important than any other because they sell life saving drugs and so they should always have enough stock to satisfy the patients. And also they cannot hold huge inventories which cost them over time and hence should forecast accurately the demand for the drugs.There are many methods and strategies used by experts for forecasting which includes both Qualitative and Quantitative Methods. Qualitative methods are used for new products or for products which cannot be analyzed quantitatively because of lack of data. But for a retail pharmacy chain, we have the historical sales data which is a major source used to predict the future sales.

The drug sales do not follow any trend in particular (increasing and decreasing) but depend on the needs of the patients. So, in forecasting the future demand, we have to carefully analyze any trends or seasonality and predict accordingly.Also, after prediction, we have to maintain the Inventory level in any outlet so as to optimize the cost and demand simultaneously. NATURE AND SOURCES OF ECONOMIC DATA 1 Economic data Three types of data may be available for empirical analysis: time series, cross-section, and pooled (i. e. , combination of time series and cross-section) data. Time series data is data collected over a period of time where as Cross-section data are data on one or more variables collected at the same point in time.

For our project, we deal mostly with Time Series Data. So, first we do some basic study on Time Series Data. 2 Time series data |First |Second |Third |Fourth |Month | | |Week |Week |Week |Week | | |Nov ’08 |22 |0 |23 |4 |49 | |Dec ’08 |10 |0 |7 |10 |27 | |Jan ’09 |0 |0 |10 |7 |17 | |Feb ’09 |10 |10 |0 |10 |30 | |Mar ’09 |5 |4 |0 |5 |14 | |Apr ’09 |10 |2 |15 |0 |27 | |May ’09 |10 |10 |6 |0 |26 | |Jun ’09 |10 |30 |16 |0 |56 | |Jul ’09 |14 |20 |15 |0 |49 | |Aug ’09 |5 |35 |10 |31 |81 | |Sep ’09 |22 |7 |20 |28 |77 | |Oct ’09 |0 |19 |18 |32 |69 | |Total |118 |137 |140 |127 |522 | (Source: Weekly/Monthly Sales Data of a drug product “Allegra~180mg~TAB” sold in an outlet of Hetero Pharmacy)The data shown in above Table are an example of time series data.

A time series is a set of observations on the values that a variable takes at different times. Such data may be collected at regular time intervals, such as daily (e. g.

, stock prices, weather reports), weekly (e. g. , money supply ? gures), monthly [e. g. , the unemployment rate, the Consumer Price Index (CPI)], quarterly (e.

g. , GDP), annually (e. g. , government budgets), quinquennially, that is, every 5 years (e. g. , the census of manufactures), or decennially (e. g.

, the census of population). Sometime data are available both quarterly as well as annually, as in the case of the data on GDP and consumer expenditure.With the advent of high-speed computers, data can now be collected over an extremely short interval of time, such as the data on stock prices, which can be obtained literally continuously (the so-called real-time quote). Although we use time series data, they present special problems for us. We assume that the data of the underlying time series is stationary. Although we will look at the precise technical meaning of stationarity later, loosely speaking a time series is stationary if its mean and variance do not vary systematically over time.

DEMAND FORECASTING Demand Forecasting is the activity of estimating the quantity of a product or service that consumers will purchase.Demand forecasting involves techniques including both informal methods, such as educated guesses, and quantitative methods, such as the use of historical sales data or current data from test markets. Demand forecasting may be used in making pricing decisions, in assessing future capacity requirements, or in making decisions on whether to enter a new market. No demand forecasting method is 100% accurate. Combined forecasts improve accuracy and reduce the likelihood of large errors. 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.At present there is no scientific system for demand forecasting in the organization, it is done by manual estimation. Though there are many different methods used for Forecasting Economic Data, since our project deals with Uni-variate time series, we use ARIMA model.

1 Qualitative forecasting methods Qualitative forecasting methods are based on educated opinions of appropriate persons 1. Delphi method: forecast is developed by a panel of experts who anonymously answer a series of questions; responses are fed back to panel members who then may change their original responses – very time consuming and expensive – new groupware makes this process much more feasible 2. Market Research: Panels, Questionnaires, Test markets, Surveys, etc.

3.Sales force composite: This involves having product managers or sales reps developing individual forecasts, and then adding them up 4. Product life-cycle analogy: Forecasts based on life-cycles of similar products, services, or processes 5. Expert Judgment by management, sales force, or other knowledgeable persons 2 Quantitative forecasting methods(Time Series Forecasting Methods) Time series forecasting methods are based on analysis of historical data (time series: a set of observations measured at successive times or over successive periods).

They make the assumption that historical patterns in data can be used to forecast future data points. 1.Naive Approach: Assumes that demand in the next period is the same as demand in most recent period; demand pattern may not always be that stable For example: If July sales were 50, then August sales will also be 50 2. Moving averages (simple moving average, weighted moving average): forecast is based on arithmetic average of a given number of historical data points 3. Exponential smoothing (single exponential smoothing, double exponential smoothing): a type of weighted moving average that allows inclusion of trends, etc.

4. Mathematical models (trend lines, log-linear models, Fourier series, etc. ): linear or non-linear models fitted to time-series data, usually by regression methods 5.Box-Jenkins methods: autocorrelation methods used to identify underlying time series and to fit the “best” model 3 Qualitative Forecasting Method – Example Life cycle analogy Used when the product is new.

The technique is based on the fact that many products have well-defined life cycle stages (Growth, Maturity, and Decline) Example Let us assume a Car Manufacturing Company is coming out with a new model of SUV. Sales history for some SUV model currently in the market is shown below. [pic] (Source: http://scm. ncsu.

edu/images/fcast33. gif) [pic] (Source: http://scm. ncsu.

edu/images/fcast34. gif) Due to competitive pressures and innovations, the product has a definite life cycle. The time of the life cycle should be known. Can we find this using surveys, opinions, etc.? ? What is the average demand level. Can we find this using surveys, opinions, etc.? It is difficult to find these out using opinions and surveys before launching the product. Key point: Using life cycle data from a similar product provides a starting point and helps us focus on the right questions Qualitative Forecasting method is generally used when there is no data available to carry out statistical analyses (Quantitative methods) which are discussed below.

Also for medicinal drugs, this life cycle analogy doesn’t apply in general. 4 Quantitative forecasting methods Simple moving average method Moving Average techniques forecast demand by calculating an average of actual demands from a specified number of prior periods. Each new forecast drops the demand in the oldest period and replaces it with the demand in the most recent period; thus, the data in the calculation “moves” over time simple moving average: At = (Dt + Dt-1 + Dt-2 + … + Dt-N+1)/N where N = total number of periods in the average forecast for period t+1: Ft+1 = At Key Decision: N – How many periods should be considered in the forecast Tradeoff: Higher value of N – greater smoothing, lower responsiveness Lower value of N – less smoothing, more responsiveness the more periods (N) over which the moving average is calculated, the less susceptible the forecast is to random variations, but the less responsive it is to changes – a large value of N is appropriate if the underlying pattern of demand is stable – a smaller value of N is appropriate if the underlying pattern is changing or if it is important to identify short-term fluctuations [pic] (Stock Chart of CISCO which has the 50 periods and 200 periods Moving Average – Chart made at http://stockcharts. com/h-sc/ui on 03-December-2009) The above chart supports the statements made above. We can see that 50 periods MA (more curved line) is more responsive than the 200 periods MA (less curved line).

So, we should see that the number of periods should be optimum and match the trend. 2 Weighted moving average method A weighted moving average is a moving average where each historical demand may be weighted differently Average: At = W1 Dt + W2 Dt-1 + W3 Dt-2 + … + WN Dt-N+1 where: N = total number of periods in the average Wt = weight applied to period t’s demand Sum of all the weights = 1 Forecast: Ft+1 = At = forecast for period t+1 1. Exponential Moving Average Method Or Exponential Smoothing Exponential smoothing gives greater weight to demand in more recent periods, and less weight to demand in earlier periods Average: At = a Dt + (1 – a) At-1 = a Dt + (1 – a) Ft Forecast for period t+1: Ft+1 = At where:At-1 = “series average” calculated by the exponential smoothing model to period t-1 a = smoothing parameter between 0 and 1 the larger the smoothing parameter , the greater the weight given to the most recent demand. [pic] (Stock Chart of CISCO which has the 50 periods MA (the less curved), 200 periods MA (the least curved) and also Exponential Moving Average (the most curved) – Chart made at http://stockcharts. com/h-sc/ui on 03-December-2009) As we can see from the above chart, it is clear that the Exponential Smoothing or Exponential Moving Average is the most responsive when compared to the Simple Moving Average method.

3 Double exponential smoothing (Trend-adjusted exponential smoothing)When a trend exists, the forecasting technique must consider the trend as well as the series average ignoring the trend will cause the forecast to always be below (with an increasing trend) or above (with a decreasing trend) actual demand double exponential smoothing smooths (averages) both the series average and the trend. Forecast for period t+1: Ft+1 = At + Tt Average: At = aDt + (1 – a) (At-1 + Tt-1) = aDt + (1 – a) Ft Average Trend: Tt = B CTt + (1 – B) Tt-1 Current Trend: CTt = At – At-1 Forecast for p periods into the future: Ft+p = At + p Tt where: At = exponentially smoothed average of the series in period t Tt = exponentially smoothed average of the trend in period t CTt = current estimate of the trend in period t = smoothing parameter between 0 and 1 for smoothing the averages B = smoothing parameter between 0 and 1 for smoothing the trend 4 Multiplicative Seasonal Method Seasonality – It can range from true variation between seasons, to variation between months, weeks, days in the week and even variation during a single day or hour. Some Time series data display seasonal patterns. To deal with seasonal effects in forecasting two tasks must be completed: 1. A forecast for the entire period (i. e.

year) must be made using whatever forecasting technique is appropriate. This forecast will be developed using whatever 2. The forecast must be adjust to reflect the seasonal effects in each period (i. e.

onth or quarter) The multiplicative seasonal method adjusts a given forecast by multiplying the forecast by a seasonal factor Step 1: calculate the average demand Dy,t per period for each year (y) of historical data by dividing total demand for the year by the number of periods in the year Step 2: Divide the actual demand Dy,t for each period (t) by the average demand y per period (calculated in Step 1) to get a seasonal factor fy,t for each period; repeat for each year of data Step 3: calculate the average seasonal factor t for each period by summing all the seasonal factors fy,t for that period and dividing by the number of seasonal factors Step 4: determine the forecast for a given period in a future year by multiplying the average seasonal factor t by the forecasted demand in that future year Seasonal Forecasting (multiplicative method) – Example (Data taken randomly) Actual Demand Year |Q1 |Q2 |Q3 |Q4 |Total |Avg | |1 |100 |70 |60 |90 |320 |80 | |2 |120 |80 |70 |110 |380 |95 | |3 |134 |80 |70 |100 |381 |96 | Seasonal Factor |Year |Q1 |Q2 |Q3 |Q4 | |1 |1. 25 |0. 875 |0. 75 |1. 125 | |2 |1. 26 |0.

84 |0. 74 |1. 16 | |3 |1. |0. 83 |0.

73 |1. 04 | |Avg. Seasonal Factor |1. 3 |0. 85 |0.

74 |1. 083 | Seasonal Factor – the percentage of average quarterly demand that occurs in each quarter. Annual Forecast for year 4 is predicted to be 400 units.

Average forecast per quarter is 400/4 = 100 units. Quarterly Forecast = avg. forecast ? seasonal factor. • Q1: 1. 303(100) = 130 • Q2: . 85(100) = 85 • Q3: . 74(100) = 74 • Q4: 1.

083(100) = 108 5 Simple Regression Method (Trend Line Model) Regression analysis is a statistical technique that attempts to explore and model the relationship between two or more variables.For example, an analyst may want to know if there is a relationship between road accidents and the age of the driver. Regression analysis forms an important part of the statistical analysis of the data. A linear regression model attempts to explain the relationship between two or more variables using a straight line. Y = mx + b This is the equation of the trend line equation which best fits the data minimizing the sum of least squares and after finding out the slope (m) and intercept (b) of the equation, we forecast or predict the future values for the given data using the equation. 6 Arima (Box Jenkins Methodology) Given a time series of data Xt where t is an integer index and the Xt are real numbers, then an ARMA(p,q) model is given by [pic] here L is the lag operator, the ? i are the parameters of the autoregressive part of the model, the ? i are the parameters of the moving average part and the [pic] are error terms. The error terms [pic] are generally assumed to be independent, identically distributed variables sampled from a normal distribution with zero mean.

An ARIMA(p,d,q) process is obtained by integrating an ARMA(p,q) process. That is, [pic] The Box Jenkins Method consists of four steps: Step 1. Identi? cation.

That is, ? nd out the appropriate values of p, d, and q. We will show shortly how the correlogramand partial correlogram aid in this task. Step 2. Estimation. Having identi? d the appropriate p and q values, the next stage is to estimate the parameters of the autoregressive and moving average terms included in the model. Sometimes this calculation can be done by simple least squares but sometimes we will have to resort to nonlinear (in parameter) estimation methods. Since this task is now routinely handled by several statistical packages, we do not have to worry about the actual mathematics of estimation; the enterprising student may consult the references on that. Step 3.

Diagnostic checking. Having chosen a particular ARIMA model, and having estimated its parameters, we next see whether the chosen model ? ts the data reasonably well, for it is possible that another ARIMA model might do the job as well.This is why Box–Jenkins ARIMA modeling is more an art than a science; considerable skill is required to choose the right ARIMA model. One simple test of the chosen model is to see if the residuals estimated from this model are white noise; if they are, we can accept the particular ? t; if not, we must start over.

Thus, the BJ methodology is an iterative process. Step 4. Forecasting.

One of the reasons for the popularity of the ARIMA modeling is its success in forecasting. In many cases, the forecasts obtained by this method are more reliable than those obtained from the traditional econometric modeling, particularly for short-term forecasts. Of course, each case must be checked. Application Of Arima Model To The Data Using ‘R’ Programming SoftwareIntroduction to ‘R’ programming Software R is an integrated suite of software facilities for data manipulation, calculation and graphical display. Among other things it has • an effective data handling and storage facility, • a suite of operators for calculations on arrays, in particular matrices, • a large, coherent, integrated collection of intermediate tools for data analysis, • graphical facilities for data analysis and display either directly at the computer or on hardcopy, and • a well developed, simple and effective programming language (called `S’) which includes conditionals, loops, user defined recursive functions and input and output facilities.The term “environment” is intended to characterize it as a fully planned and coherent system, rather than an incremental accretion of very specific and inflexible tools, as is frequently the case with other data analysis software. R is very much a vehicle for newly developing methods of interactive data analysis.

It has developed rapidly, and has been extended by a large collection of packages. However, most programs written in R are essentially ephemeral, written for a single piece of data analysis. (The R Programming package can be downloaded at http://www.

r-project. org/) Importing DATA into R We were given the data in the form text documents.Each text document consists of 7 columns and as many rows as the number of products. The data which we have to use and then forecast for future is in the 6th column of the text document. So, we have to import this data for each product for each day in to R Programming Software. For this we use the commands either scan or read.

table. read. table(file pointer, header = TRUE, sep = “|”); In the above command read. table, file pointer is the location of the file, like “C:/ARIMA Data/Data1.

txt” and sep is ‘|’ because the data which we are given is separated into columns by ‘|’. We can also use scan command scan(file = “”, what = character(), nmax = -1, sep = “|”); Here also since the separator is ‘|’, we use it in the format.After we import the data, we apply ARIMA models to them and then analyze. Applying ARIMA Function on the Data R has in built functions for ARIMA which generates the coefficients and also residuals. If the imported data is stored in the variable ‘x’, then we have to apply ARIMA function on x.

Now applying ARIMA function on x, > y=arima(x, order=c(1,0,0)) The ARIMA Data is stored in the variable y. We can look at the coefficients by typing y. > y Call: arima(x = x, order = c(1, 0, 0)) Coefficients: ar1 intercept 0. 9990 3868.

4421 s. e. 0. 0013 954. 4463 sigma^2 estimated as 1802: log likelihood = -457. 86, aic = 921. 72 Since, the ARIMA is of the order (1,0,0), we get only one coefficient as seen.Now, let us see at the residuals of y to analyze the error values.

> acf((residuals(y))) [pic] Here we can see that the error terms are crossing the limit of +/- 0. 2. So, we have to change the order of the ARIMA function. > y=arima(x, order=c(12,1,0)) > acf((residuals(y))) [pic] Now, we can see that no error terms cross the limit and almost tend to zero. So, we use the order (12,1,0). Forecasting the future values Once we are sure of the order of the ARIMA model, we can then forecast/predict the future values using in built function in R.

> predict(arima(x, order=c(12,1,0)), n. ahead=5) $pred [1] 4869. 9924863.

615 4861. 026 4877. 53 4885. 208 $se [1] 31. 07092 53. 28781 76. 34697 99.

35588 121. 06159 Here we predicted the next 5 values (i. e. 89th to 93rd).

Likewise, we can predict future values for any Time Series after confirming the order of the ARIMA Model. 7 Automated Arima Model Estimation And Selection – (Auto Arima) Our Forecast using ARIMA is as effective as the model selection is. So, for each of every product, we have to identify the best model by either using the ACF and PACF plots or by using the AIC, BIC information. But since we have large number of products and also since the data keeps changing, it’s better if the model selection process is a automated.This can be done by creating a function which compare the AIC, BIC information for different models and select the best model by itself. There is a package for R developed by Rob J Hyndman called forecast which has a module named AUTO ARIMA which when applied to the time series data, selects the best ARIMA model using iteration. The package can be downloaded at http://robjhyndman.

com/software/forecast We have to load this package into R and then apply the module to our Time Series Data. APPLICATION OF DIFFERENT MODELS FOR OUR PRODUCTS We selected three products randomly. • Name – ‘ALLEGRA~180 mg~Tab’ whose UOM: 10 • Name – ‘MEDLER~. ~Tab’ whose UOM: 10 Name – ‘VICKS VAPORUB~10GM~GENERAL’ whose UOM: 1 1 Moving Average (Example) |Product : ALLEGRA~180 mg~Tab |UOM : 10 | | | | | | |Predicted Value for Oct ’09 | | |X |Y |MA (3) | |Feb ’09 |4 |30 |69 | | | | |Predicted Value for Oct ’09 | | |X |Y |MA (3) |MA (5) | | |Nov ’08 |1 |479 |124 |202. | | |Dec ’08 |2 |399 | | | | |Jan ’09 |3 |562 |Actual Value for Oct ’09 | |Feb ’09 |4 |388 |121 | | | |Mar ’09 |5 |339 | | | | |Apr ’09 |6 |259 | | | | |May ’09 |7 |300 | | | | |Jun ’09 |8 |340 | | | | |Jul ’09 |9 |239 | | | | |Aug ’09 |10 |44 | | | | |Sep ’09 |11 |89 | | | | |Oct ’09 |12 |121 | | | | For October ’09, The MA(3) and MA(5) predictions for the product “MEDLER~. ~Tab” are 124 and 202. 4 and the actual value is 121.

|Product : VICKS VAPORUB~10GM~GENERAL |UOM : 1 | | | | | | |Predicted Value for Oct ’09 | | |X |Y | |MA (3) |MA (5) | | |Nov ’08 |1 |7 | |12. 6667 |9. | | |Dec ’08 |2 |8 | | | | | |Jan ’09 |3 |12 | |Actual Value for Oct ’09 | |Feb ’09 |4 |5 | |16 | | | |Mar ’09 |5 |9 | | | | | |Apr ’09 |6 |6 | | | | | |May ’09 |7 |4 | | | | | |Jun ’09 |8 |4 | | | | | |Jul ’09 |9 |8 | | | | | |Aug ’09 |10 |13 | | | | | |Sep ’09 |11 |17 | | | | | |Oct ’09 |12 |16 | | | | | For October ’09, The MA(3) and MA(5) predictions for the product “VICKS VAPORUB~10GM~GENERAL” are 12. 67 and 9. 2 and the actual value is 16 2 Exponential Moving Average (Example)For Exponential Moving average we take the Damping factor as 0.

8 which means we give 80% weight age to the most recent value. |Product : ALLEGRA~180 mg~Tab |UOM : 10 | | | | | | | | | |X |Y |EMA (k = 0. 8) | |Mar ’09 |5 |14 |69 | | | | |Predicted Value for Oct ’09 | | |X |Y |EMA (k = 0.

) | | | |Nov ’08 |1 |479 |80 | | | |Dec ’08 |2 |399 | | | | |Jan ’09 |3 |562 | | | | |Feb ’09 |4 |388 |Actual Value for Oct’ 09 | |Mar ’09 |5 |339 |121 | | | |Apr ’09 |6 |259 | | | | |May ’09 |7 |300 | | | | |Jun ’09 |8 |340 | | | | |Jul ’09 |9 |239 | | | | |Aug ’09 |10 |44 | | | | |Sep ’09 |11 |89 | | | | |Oct ’09 |12 |121 | | | | For October ’09, the EMA(k=0. 8) prediction for the product “MEDLER~. ~Tab” is 80 and the actual value is 121 |Product : VICKS VAPORUB~10GM~GENERAL |UOM : 1 | | | | | |Predicted Value for Oct ’09 | | |X |Y |EMA (k = 0. 8) | | | |Nov ’08 |1 |7 |16. | | | |Dec ’08 |2 |8 | | | | |Jan ’09 |3 |12 | | | | |Feb ’09 |4 |5 |Actual Value for Oct ‘09 | |Mar ’09 |5 |9 |16 | | | |Apr ’09 |6 |6 | | | | |May ’09 |7 |4 | | | | |Jun ’09 |8 |4 | | | | |Jul ’09 |9 |8 | | | | |Aug ’09 |10 |13 | | | | |Sep ’09 |11 |17 | | | | |Oct ’09 |12 |16 | | | |For October ’09, the EMA(k=0.

8) prediction for the product “VICKS VAPORUB~10GM~GENERAL” is 16. 2 and the actual value is 16. 3 AUTO ARIMA (Example) (Implemented in R using the “auto. arima” module in “Forecast” Package) Our primary objective has been that of demand forecasting for this process we have experimented with various models one of the model is arima, we implemented automatic arima identification and forecast function (auto. arima) from forecast package one of the contributed packages in R.

This function returns the best ARIMA model according to either AIC, AICc or BIC value. Example of implementation Product Name -‘ALLEGRA~180 mg~Tab’Sales data of 44 weeks (Nov 2008 –Sep 2009) represented in R vector format (implementation in R) drugsales g auto. arima(x = g, d = NA, D = NA, max. p = 20, max. q = 20, max. P = 5, max.

Q = 5, max. order = 5, start. p = 0, start. q = 0, start. P = 1, start.

Q = 1, stationary = FALSE, ic = c(“aic”), stepwise = TRUE, trace = FALSE, approximation = TRUE | frequency(g) ; 12, xreg = NULL, test = c(“kpss”, “adf”)) ARIMA(0,0,1) with non-zero mean Coefficients: ma1 intercept 0. 2179 2. 1076 s. e.

0. 1425 0. 3139 sigma^2 estimated as 2.

945: log likelihood = -86. 22 AIC = 178. 43 AICc = 179.

03 BIC = 183. 79 Forecast for the next four weeks 4. 36639 4. 3582314. 3582314. 358231 SUM = 17. 511332 = 17. 5 Actual sales for the month of October 2009 = 16 For Product “MEDLER~.

~Tab” ; adrugsales = scan(file = “C:/Users/sonee/Desktop/medler. csv”, what = character(), nmax = -1, sep = “”); ; medlersales = as. numeric(adrugsales) ; g 12, xreg = NULL, test = c(“kpss”, “adf”)) ARIMA(0,1,1) Coefficients: ma1 -0. 7432 s. e. 0. 1016 sigma^2 estimated as 1741: log likelihood = -221.

85 AIC = 447. 7 AICc = 448 BIC = 451. 22 Forecast for the next four weeks U 24. 7605224. 7605224. 7605224. 76052 SUM = 99 Predicted Values for “ALLEGRA~180 mg~Tab” for Oct 2009 using other methods METHOD |PREDICTED VALUE |ACTUAL VALUE | |Croston |59 |69 | |Thetaf |75 |69 | |Splinef |95 |69 | |Random walk model |113 |69 | |Meanf |41 |69 | |Auto Arima |74 |69 | 1.

Efficiency The best possible results are from auto arima so this method has been implemented for the remaining 5831 products and the data analyzed it is as follows. The following table displays the relation between difference in strip size of predicted value and actual value (DiffStrip) and number of zero sales in the whole time period (count) in terms of number of products which fall in their category when auto. arima function is used. Auto Arima (C=Count) |DiffStrip |C=0 |C