Aspect Ratio harness analytics to help clients businesses make smarter decisions. In this era of unprecedented growth in companies’ abilities to collect, store and access massive amounts of data, the data by itself doesn’t guarantee any results. This is where aspect Ratio comes in. As an analytics firm, it relies on continuous innovation to transform data into insights that provide the business with definitive competitive advantage.
Basic principles that uphold the culture and guide decision making are:
‘ Passion For Excellence
‘ People First
‘ Deliver With Integrity
Aspect Ratio specializes in data analytics. It specializes in timely and efficient analysis of data to enable quality business decisions. It is a one-stop solution for data collection, conversion to digital format, analysis and presentation to provide client business with a competitive advantage and powerful value for its customers. It has been a committed and efficient outsourcing partner to many organizations, large and small.
One of the organization being MSD Pharma (Merck).
MSD-GCAF is the arm of Aspect Ratio dedicated for MSD Pharma. Merck & Co, Inc (MSD outside US and Canada) is one of world’s largest pharmaceutical company spread across 120 countries. It can be described as a global research driven pharmaceutical company that discovers, develops, manufactures and markets a broad range of innovative products to improve human and animal health, directly and through its joint ventures.
MSD-GCAF is the Global Centre for Analytics and Forecasting for MSD. We have around 100 people working here to develop various models in excel to help MSD have a better forecasting approach and provide them higher forecasting accuracies. There are separate teams working for developments of models for MSD’s core, non-core drugs and vaccines. And a dedicated team to populate these models for different therapeutic areas for different countries and provide the countries with a forecast.
OBJECTIVE
Forecasting in a pharmaceutical industry is of utmost importance when it comes to prediction of demands which thereby predict the sales. In a pharmaceutical industry, there is huge quantity of wastes incurred which lead to losses. This is due to over-estimation of the demands of the drug and over a period of time, the drugs expire and needs to be disposed. Not only the input cost of the product leads to loss, but the cost involved in disposal of drugs is also of major concern. If the demands are under-estimated, the patient might shift to other drugs leading to loss of customer. Ultimately, both under-estimation and over-estimation are illogical prediction methods for demands. Hence, an accurate estimation of demands and sales needs to be focussed upon to earn maximum revenue and mark a best name in the industry.
Forecasting of demands needs a lot of analysis of secondary data and processing it to predict the demands. This analysis when done manually might take a lot of time and tough calculations involved in it. To make these calculations automated, there is a need of Forecast Models using which a demand planner needs only to focus on market research and based on the research can interpret certain inputs which are desired by the models and get a result automatically. Behind every model, there is a concept based on which the entire model is designed. Therefore, my objective is to design those Concept Models using which the final models can be coded using programming and used for forecast.
The Concept models are designed based on the following approaches which a demand planner can use for forecast data modification based on his market research.
1. Business rules.
2. Errors and outliers based concept model.
3. Overrides based concept model.
4. Market event concept model.
Before designing of the concept models, there is a lot of study involved. The method of forecast in the pharmaceutical industry, the terminologies and basic concepts of statistics were learnt deeply with the help of literatures available. Also, knowledge of advance MS Excel was gained before designing of concept model.
ABSTRACT
Objective: The objective is to design Concept Models which can be used for developing a model for forecasting. They are required for processing of the secondary data. These concept models are typically based on 4 approaches:
1. Business rules.
2. Error and outliers.
3. Overrides by demand planner.
4. Market Events.
Materials and Methods: Knowledge of the pharmaceutical industry and its market, the basic method of forecast, basic concepts of forecast, awareness of various statistics and tools involved and facts of the advance MS Excel are pre-requisite for this project. Hence, studying the literatures of the same constitute the first step of the methodology which has been adopted for the project. The next step is to analyse and design the concept models based on the above approaches using that knowledge.
Result: The concept models were designed using the above approaches
Discussion: The concept models developed can be used for a smaller amount of data for predictive analysis, but usually in a pharmaceutical industry the secondary data is huge. So, a proper model is required which is based on these concept models which can be developed using VBA programming. Also, these approaches is limited, the demand planner may require many more option for modification.
INTRODUCTION
OVERVIEW
Pharmaceutical industry includes development, production and marketing of drugs and other dietary supplement pills which are licensed for use as medications. It also includes other medically important products such as vaccines, follow-on biologics (which contains active biological species), medical diagnostic devices and daily care products. The drugs can be of two types depending on the ownership of the formula of drug; generic drug and branded drug. Branded drug are those drugs over which an organization has patent filed and has got approved. Also, the patent is given on the active molecule of the drug in case of drugs prescribed for chronic disorders. Patent is given on the process of production for vaccines and follow-on biologics. Generic drugs are those drugs over which the ownership has been lost and the production can be done by any other pharmaceutical company by varying its excipient molecules.
Analysis of the product and thereafter forecasting depends on the type of product the pharmaceutical industry is about to launch. The launch of any new drug into the market starts with drug discovery which is followed by drug development. In these two steps, the drugs are completely formulated using scientific research and development where the dosage, strength, active molecular part of drug and excipient molecules, which are inert and generally employed for absorption and preservation of the drug, are finalized. After this there are various levels of clinical trials and thereafter launch into the market. During these phases, there is a high cost involvement which has to bear by the organization. Hence, it becomes very important for the manufacturer to understand the demands of the product, effect of competitor’s launch on the product and vice-versa and effect of their product on the consumer to intelligently utilize the finances and increase profit margins. To understand these effects, control the cost involved and be the best in the market, there is need for predictive analysis and forecasting in the pharmaceutical industry.
Predictive analysis is data mining which includes relevant information from data and consuming it for predicting the trends and behaviour patterns. It incorporates various statistical techniques such as modelling, machine learning and data mining and analyses the current and historical facts to make future forecasts, or otherwise unknown events. In business, these predictive models are employed for exploiting historical patterns and transactional data to identify the risk and opportunities. Based on the risks involved and opportunities available, the stakeholders take decision about launching of the product in the market. After overall data processing of historical and analysis of the market and its effect, forecasting occurs by which the stakeholders can take decisions for units of manufacturing and inventory stock.
Predictive analysis and forecasting is important due to the following reasons:
‘ Distribution of resources:
In business, capital requirement and human resource planning needs to be done prior to investment. Predictive analysis helps in in taking this decision accurately.
‘ Reduces wastage of resources and inventory management:
Wastage gets reduced when we have an accurate prediction of the demands by which inventory can be reduced and the cost of disposal of wastage also gets reduced.
‘ Pricing:
The pricing of the drugs and their fill size can be set according to the requirement of the drug depending on the disorder, disease, days of therapy, number of doses of therapy, competitors in the market etc. Also, discounts and various schemes can be made available to the consumer for increasing the sales of the product.
‘ Decrease of business risk:
Business starts with the analysis of risk. By predictive analysis and forecasting, the risk involved in the business can be reduced to a large extent and opportunities available can be seen with a broader outlook.
‘ Business strategies:
If the risks and chances of failures can be analysed, then accordingly the strategies of launch of product in the market can be enhanced or changed.
Hence, predictive analysis and forecasting in the pharmaceutical industry becomes essential for better growth of the company and mark an impression to the customer.
There can be various procedures and strategies which can be employed for predictive analysis and processing of data. The historical data available is in the form of secondary data using which the future trends can be generated. But, it is not necessary that the future demands will follow the historical pattern completely. Hence, data processing needs to be done by applying various views of analysis and various factors that can affect these trends should be taken care of. Therefore, excel based models are designed which involves the use of various functions and tools available in MS Excel and they are called as Concept Models. Using these models, the historical data available to us can be trended to generate a basic future trend which can be further processed depending on the type of modifications required which further depends on the product study by analyst. There are various factors which will affect the future trended data with different intensities during predictive analysis. The factors which are considered here are as follows:
‘ Business Rules:
There are three basic business rules which every forecast in business should follow. They are coefficient of variation of history, deviation in the slope of trended and forecasted data and number of zeroes present in forecasted data. Each of them has its own significance and attached range with it. A perfectly trended data should follow all these rules. If there is violation of any rule, then analyst needs to reconsider the prediction again so as to generate the perfect outcomes. If there are no possibilities visible of reaching to the acceptable range for future demands and sales, then demand planner must focus on the various reasons that can lead to failure in future and should arise with possible solutions and opportunities that can be chosen. The calculation of these rule are done using combination of various built-in functions in MS Excel. And these functions are incorporated for use of predictive analysis of smaller quantity of data in the form of Concept Model.
‘ Outliers and Error rectification:
Historical data need not always be such that a specific future trend can be generated. Sometimes, there may be a sudden rise in sales followed by a drastic drop which can be seen repeatedly. In such cases, it is difficult to trend for the future data as extending the increasing graph will predict the increase in demands for future whereas the sale’s drop line will extend the future data in a pessimistic fashion. In real case scenario this is not happen. Therefore, a concept of Outlier has to be used using which the historical data can be modified in such a way that the standard deviation from the mean of the historical data is minimum whereas the overall effect generated with those sales remains same and a trend is also followed. Special formulas are created in MS Excel to solve this purpose and merged with Concept models.
There are various approaches which can be used for predicting over all future market demands. One such approach is ‘Top ‘ down’ approach where the trending starts from the top level, that is, monthly overall sales of a product, and then dividing it on the basis of analysis of demand planner at PSF (product, strength and fill size) level. Another approach is opposite of it; it starts from fill size and sums up to product level. Another approach is a mid-way approach where the trending starts from strength level and adds to the product level and breaks into fill size. After, we get future data according to these approaches, errors for variation between the actual and the forecasted data are calculated and the approach with the lowest error is chosen.
The errors are of two types: L2 (Lag-2-Months) Error and R3M (Rolling-3-months) Error. In case of L2M error, last 3 months before the forecast month are hold-out and for the third month the error is calculated. In case of R3M error, five months are hold-out and error is calculated cumulatively for last three months. These concepts are incorporated into Concept models where errors will be calculated and the best forecast approach can be selected.
‘ Overrides:
Any future trend does not necessarily follow the pattern of historical data. The curve type which it can take up might change depending on the launch type and its seasonality. To alter the values depending on the seasonality and various other factors, overrides can be done by incorporating constraints such as minimum and maximum demands, smoothing level and curve type. This strategy is also incorporated in the form of concept model to alter the trended data according to the demand planner analysis.
‘ Market Events:
Market event can take various forms such as launch of a new competitor’s product, launch of generic product, pricing change etc. All such type of events will make an impact on the sales and demand of the self-product as well as on the revenue earned. The revenue can either increase or decrease depending on the type of launch into the market. For example, if there a launch of generic product, then it will steal some share from the self-product. Now, to understand the level of share it will take depends on the analysis by the demand planner based on market study.
Concepts models with five factors as its basis will then be used for predictive analysis and forecast for a specific product.
REVIEW OF LITERATURE
Forecasting in a pharmaceutical industry includes predictive analysis about sales revenue, unit volume, return on investment, health care policies, pricing policies, corporate direction, research and development portfolio, marketing programs, promotional programs, consumer programs, sales force size, sales force structure, sales force allocation and all these occurs at regional country and global level. There exist multiple links between these units of analysis. Formerly, it is important for us to know about the forecasting process which is as follows:
1. Define the forecast:
Defining forecast implies that the behaviour of forecast. Is the forecast being done for a new product launch or is it for an old product whose competitor is being launched in the market? The time period of the forecast also needs to be known for an accurate and effective forecast. When the forecast and its type is understood and mentioned, then the forecast algorithm needs to be evaluated for its role in potential development or may need to explicitly evaluate the financial terms. It is also necessary to realize weather the forecast is for a single country or a cumulative forecast for multiple countries (global level). The answer to such question helps in providing a framework to initiate with the predictive analysis of the existing secondary data and the solutions which needs to be produced. [1]
2. Selecting a forecast method [2] :
Selecting a forecast method is based on our knowledge and expertise in the field of analysis, knowledge of statistical tools and pharmaceutical industry itself. There are mainly 4 types of forecast methods that can be opted for:
(i) Dartboard Method: It is based on the gut feel of the analyst which is dependent on his experience in the pharmaceutical industry and analysis.
(ii) Simple spreadsheet Method: It is purely based on MS Excel and various tools, functions and formulae present in it. This is the method which I will be opting for making my concept Models for forecast.
(iii) Analytical Spreadsheet Method: It is a combination of MS Excel and VBA (Visual Basic for Application). This combination is primarily used to reduce the file size of the MS Excel. An Excel sheet will have a larger file size when there are too many formulae applied in each cell; hence, incorporating such a combination helps us reduce the file size incredibly.
(iv) System Dynamics Method: This method is highly advance method which includes a number of software packages, complex interactions and Venism.
There are also few quantitative approaches which can be employed for predicting the future trends. They are as follows [3]:
(i) Na??ve Approach: The last period actual value is considered as the forecasted value. There is no deviation from the previous year sales and demands.
(ii) Simple Mean (Average): The average of previous sales is considered as the forecast for future years.
(iii) Simple Moving Averages: It takes an average of a specified number of most recent observations where each observation receives the same weightage.
(iv) Weighted Moving Averages: It takes an average of a specified number of recent observations where each observation is given different weightage.
(v) Exponential Smoothing: a weighted average procedure is applied to predict the future trend where a decline is observed after a certain period of time.
(vi) Trend Projection: It is a technique which uses least square methods to fit the straight line to a data and predict the future values.
(vii) Seasonal Indexes: Sometimes the demands expand seasonally. Hence, to incorporate such a condition in the forecast involves the use of seasonal indices.
3. Enable Analytical Insights [4]:
This step includes the study of risks and uncertainties associated with the business and modifying the forecast accordingly. It is the study of simulations, different scenarios, decision analysis, sensitivity analysis and tornado diagrams which further leads to analysis present value, return on investments, break-even analysis and productivity analysis.
Risk and uncertainty are often interchangeably used, but from a forecaster’s perspective it has a different view. Risks are resolved throughout the planning and development process of the product, whereas uncertainties remain even after the product is launched in the market. During the developmental phase of the product, risk is calculated whereas after its launch the uncertainty is calculated of its failure and success and its extent into the market. There are more insights to be gained by considering the continuous distribution of forecasted values of forecast uncertainties, instead of considering the discrete end points presented in the decision tree example. An example is shown in the figure 1. Traditional views of forecasting posit uncertainty as a negative outcome of forecasts, arguing that the greater the uncertainty in a forecast the less accurate the forecast. This can be considered as myopic view of the forecasting because there is a tremendous amount of valuable information in the drivers of forecast uncertainty. Generating ranges of forecast outcomes is accomplished using simulation methodology. Simulation methods have the potential to combine the uncertainty of the input variables to create a distribution of outputs, as shown in figure 2. The core of this method is to run the forecast algorithm multiple times, each time drawing a point value randomly from the distribution of each input variable. As the number of independent runs increases, the distribution of outputs occurs (one output associated with each iteration of the forecast algorithm), as shown in figure 3. The skewness of the input ranges is preserved and transferred into the output range. These methods effectively capture the uncertainties inherent in the input assumptions and translate these uncertainties into the forecast outputs. These are software tools that enable the user to perform these simulations. Programs such as crystal Ball, At Risk and Risk Detective all are Excel add-in programs which create this capability. It enables all Excel based calculations too without the use of any VBA with Excel. Hundreds of iterations are required to produce a well distributed outcome.
In scenario analytical approach, we take three assumption into consideration; pessimistic, optimistic and neutral approach. In case of optimistic approach, there is an aggressive uptake curve in which the demands are supposed to increase with a high exponential rate. In case of pessimistic approach, the uptake in demands is slow and uptake curve is low. In case of neutral approach, an uptake curve is 5 which mean the uptake is gradual. Using the 3 approaches, analysis of data can be done and forecast can be generated. This is depicted in the figure 4.
In case of sensitivity analyses [6,7], the goal is to link the input assumptions to the outputs, determining the degree to which the forecast outputs depend on each input variable. Primary approximation is that the forecast said is to be most sensitive to the input variable that most affects the output.
4. Present the results.
There are few basic concepts which need to be known before starting with the building of concept model.
‘ Patient and Prescription model:
In case of patient based model, total population who have the tendency to get the disease or disorder are considered as the sample size and forecasting is done on this basis as the total patients. Whereas in case of prescription based model, the total sample size is the number of actual patients who are diagnosed and prescribed for a particular treatment.
In patient based algorithm, the filter is as follows:
Figure 4: Patient based Model
‘ Epidemiology:
Epidemiology is the study of the distribution and patterns of health-events, health-characteristics and their causes or influences in well-defined populations. It is the cornerstone method of public health research, and helps inform policy decisions and evidence-based medicine by identifying risk factors for disease and targets for preventive medicine. [11]
‘ Prevalence:
Prevalence of a health-related state (typically disease, but also other things like smoking or seatbelt use) in a statistical population is defined as the total number of cases of the risk factor in the population at a given time, or the total number of cases in the population, divided by the number of individuals in the population. It is used as an estimate of how common a disease is within a population over a certain period of time. [12]
‘ Incidence:
Incidence is a measure of the risk of developing some new condition within a specified period of time.
‘ Symptomatic Patients:
The patients who show symptoms for a particular disease.
‘ Compliance:
Compliance is the degree of constancy and accuracy with which a patient follows a prescribed regime.
‘ Adherence:
Adherence is the extent to which the patient continues the agreed-upon mode of treatment under limited supervision, when faced with conflicting demands. Adherence is how well a patient follows the complete treatment regime, which may include lifestyle changes and services from other entities, such as physiotherapy etc.
‘ Persistence:
Persistence is the act of continuing the treatment regime for the prescribed duration.
‘ New Product Forecasting:
Forecasting for a new product in the pharmaceutical industry follows a general pattern, which may vary for specific lines of therapy depending upon the weightage of each parameter in the event. [15,14]
Along with this knowledge of analytics and pharmaceutical industry, there is a need for MS Excel knowledge. The various leaning in Excel are as follows [16],[17]:
‘ MS Excel functions
‘ Data analyzer
‘ Solver
‘ Regression and Correlation
METHODOLOGY
The methodology adopted for this Major Project starts with the literature study of the pharmaceutical industry and its market. Ultimately, the forecast is of the market demands in the pharmaceutical industry. Hence, knowledge of the pharmaceutical industry and the way it switches and moves with respect to demands, consumption, prevalence rate and sales has to be studied. This will help in providing a deep insight about the inputs which a demand planner might want to give so as to generate an automated result. Secondly, it was the basic requirement to have a basic knowledge of the statistical tools to be used in designing of the concept model. Any forecast and data analysis involves statistics which has to be manipulated using the statistical tools. The various concepts of statistics are mean, median, mode, standard deviation, coefficient of standard deviation, variance, root mean square deviation, slope calculation, regression analysis, correlation, hypothesis testing and normalisation. Without the knowledge of these concepts, it is impossible to analyse any data. Next, advance MS Excel was learnt so as to incorporate these statistical tools in a spread-sheet to make it an automated concept model. The knowledge which was needed to be grasped relating to MS Excel was about various functions ( index, match, offset, vlookup, hlookup, trend, slope, if etc.), data analyser, regression and correlation and solver technique. These all were the basic requirements before starting with the project.
The designing of concept model was based on the various approaches and modifications which a demand planner may want to make after his initial forecast to official forecast. The approaches which a demand planner may need are based on the following and concept models are designed on the same basis:
1. Business rules.
2. Overrides.
3. Error and outliers.
4. Market Events.
ANALYSIS AND RESULTS
CONCEPT MODEL: 1
Aim: To design a concept Model for Business Rules.
Theory:
There are basically 3 business rules which a forecasted data should always follow. The business rules are as follows:
‘ Coefficient of Variation:
It is the ratio of standard deviation to the mean of the set of data. It signifies the extent of variability of the data set. Standard deviation is the dispersion of that particular value form the mean of the set of data. Data can be forecasted data or historical data. But the coefficient of variation is calculated only for forecasted data.
Coefficient of variation = ?? / ??
Where ?? is the standard deviation and ?? is the mean of the data set.
The threshold value for the coefficient of variation is 70%, that is, the coefficient of variation should not be more than 70% of the historical data.
If it is higher than the threshold, then the forecasting done on the basis of historical value is not significant implying that it is incorrect.
‘ Absolute deviation between the historical and forecast slope:
The slope of the line obtained by plotting the trended values on graph should not deviate than the specific level from the slope obtained by plotting the historical values. The threshold for this difference is 25%, that is, the difference between the forecasted and the trended data slope should not be more than 25%. If it is more than this specific value than the significance of the forecasted based on the historical data is lost which is not possible.
‘ Zeroes in the forecasted data:
There should be no zero in the forecasted data. If there are zeroes in the forecasted data, then it means that we are assuming the demands of that particular product as nil and thereby the production has to be stopped. Is this is so, and then there is no significance of forecasting for that particular product. The threshold for it is no zero in the forecasted data.
Based on the business rules, the demand planner comes to the conclusion of the intensity of the business problem (if it is found). Below table shows the action which a demand planner will make based on violation of the business rules.
Trend Category Actions Rules violated
Statistical Trend Good trend, demand planner need not focus on this. No rule violated.
Alert Trend Demand planner need to focus specifically on this, and should make manual overrides in it. Either rule 1 or rule 2 or both are violated.
Caution Trend Demand planner may use an in-built override to manipulate these. Rule 3 is violated.
Formulae Used in MS Excel:
‘ CV of History = STDEV.P (array of past sales)/AVERAGE (array of past sales)*100
‘ Slope = SLOPE (y-axis values, x-axis values);
Where, y-axis is the values of sales historic/predicted, x-axis are the dates.
‘ Difference in the slope = (Slope of historical data) ‘ (slope of forecasted data)
‘ Number of zeroes= COUNTIF (the array of data of forecast)
‘ Trend
Procedure:
1. In a MS Excel sheet, enter the historical data for a particular drug monthly till last month.
2. Using Trend function in MS Excel, enter the forecast data till the time period needed.
3. Then apply the above formulas to calculate coefficient of variation, slope of the trended and the historical data, difference between the slope of the trended and the forecasted data and number of zeroes in the forecasted data.
4. Now, analyse the trend which a forecast is following and take the decisions accordingly.
Results:
Figure 5: Historical Data
Figure 7: Forecast Data (contd.)
Figure 8: Business rules calculations.
The coefficient of variation for drug A and Drug B were calculated for the historical data which were found to be 30.26% and 12.6% respectively. Also, the difference in the slope of historical data and forecast data was 15.19 for drug A and zero difference for drug B. In case of drug A, there was a zero value for a month in forecast data whereas in drug B there was no zero value. Hence, Drug A trend was a statistical trend where the demand planner need not focus more whereas Drug B is a caution trend.
CONCEPT MODEL: 2
Aim: To design a concept model for Outliers and Errors in the forecast.
Theory:
Outliers’ means an extreme deviation from mean of the set of data. The deviation of a value in the set of data should not be more than the standard deviation (SD) of the set of data. If it is more then it will lie outside of the curve obtained by plotting the set of values in the graph which should ideally not happen. When the historical data is of such type that deviates from a high value, then the user will not be able to trend the forecast data as curve is highly random. So, to make historical data lie along the same curve, it is important to apply the concept of outliers.
The outlier concept is applied in the following explained fashion. First, the deviation from the previous value is checked. This deviation should not be more than twice of the standard deviation. This is because a value in the historical data will disperse from a maximum of standard deviation and same applies to the next value in the historical data. If we sum up the extremities of the two values, they should not be more than twice of the standard deviation. If it is higher than this dispersion, they will lie outside of the curve to be obtained. Hence, double value of standard deviation will be added to the smaller value of the two. For doing this, double IF conditions are applied one inside another. One condition is applied by subtracting first value from the next value and other by subtracting next value from the first value. The smaller value is added with the 2(SD). And if the condition is satisfied, the value remains the same.
There are 3 strategies using which forecast can be done. And with each strategy used, there is an error associated with it. There are 2 types of error: error metric 1 which is calculated specifically for a single months and error metric 2 which is calculated for 3 months cumulatively. After forecasting using 3 strategies, we go back to the previous months and calculate the error. In case of error metric 1, we go back to one month whose actual data exist and forecast for that particular month using that same strategy. Now for that month we have both forecasted and actual data, hence, error can be calculated. In case of error metric 2, three month cumulative error is calculated using the same approach.
Error metric 1 = | actual value ‘ forecast value| / (actual value)
Error metric 2 = | (sum of 3 actual values for 3 cumulative months) ‘ (sum of 3 forecasted values for 3 cumulative months) | / (sum of 3 actual values for 3 cumulative months)
Formulae Used in MS Excel:
‘ Modified historical values= IF(( Next value ‘previous value)>(2*SD), previous value+(2*SD), IF((Previous value-next value)>(2*SD), previous value-(2*SD), Next value))
‘ Error metric 1 = ABS ( actual value ‘ forecast value) / (actual value)
‘ Error metric 2 = ABS ((sum of 3 months actual value) ‘ (sum of 3 months forecasted value)) / (sum of 3 months actual values)
Procedure:
1. In an MS Excel sheet, enter the historical data and create another row where the formula for modified value calculation is entered.
2. The values are then modified according to the outlier rule.
3. In an MS Excel sheet, enter the historical data and forecast the demands according to various strategies.
4. The formula for one month Error metric 1 and error metric 2 is entered in a cell in the spreadsheet and calculate the error and choose the appropriate strategy.
Results:
Outliers:
After outlier correction, the historical values were confined within a certain range whereas the range was quite high earlier. This case is for both drug A and drug B.
Errors:
CONCEPT MODEL: 3
Aim: To design a concept model for overrides by a demand planner.
Theory:
A demand planner according to his analysis of the market which is based on his intense market research, he would like to make various types of overrides over the forecast data. These overrides can be due to various reasons such as seasonality of the product or market expansion or decline etc. The overrides which a demand planner would like to make may be the curve type of fall of demand, minimum value below which the demands will not seize, maximum value over which the sales cannot increase and smooth factor. The curve type of fall of demand may also be referring to as dampening factor.
The demand planner may realize that after a certain period of time the sales/demands of the product may fall. The fall in the demand will be an aggressive fall or gradual fall depends on the market analysis of the demand planner. Thus, the dampening factor will vary on the scale on 1 to 10; where 1 is a gentle fall in the demands and 10 being the most aggressive and speedy fall in the demand. To incorporate such an option in the model for the demand planner, we have made a concept model using the idea which is explained further. From the initial forecast data there has to be some value deducted which will depend on the initial forecast value and the dampening factor. This factor will be calculated by multiplying the number of month from which the forecast has started with the initial forecast value and dampening factor. This value will be deducted from the previous forecast value to get the new value after applying dampening factor. If the value reaches on negative side, it will be considered as zero.
A demand planner may also realize from the market research that above a certain value, the demands will not increase. To inculcate this option in the model, the final adjusted values are calculated considering the maximum value. Also, minimum value needs to be considered which a demand planner may realize which can be due to previous orders and below which the demands will not seize. There is an option for that too in the concept model.
There is a concept called as smoothing, which is done to smooth the curve obtained after adding dampening factor. This smoothing factor varies from 0 to 1; smooth factor of 1 means the values obtained will follow a straight line and zero implies that the values will not be changed. A smooth factor of 0.5 signifies that the values of the curve will be in some minimum range. To incorporate this in the model, the ratio of previous and value obtained after applying dampening factor is multiplied with the smooth factor to give it a value which is confined alone a smooth curve.
Dampening = Damped Forecast value = Original Forecast Value ‘ Original Forecast Value * Dampening Factor * (Serial count of the data point in data series including history – Total number of months in History period)/Total number of months in Forecast period
Seasonality (smoothing) = Original forecast value/ Forecast value using native trend function
New Seasonality = Seasonality + (1-Seasonality)*smoothening factor
Smoothened forecast value = Forecast value using native trend function / New Seasonality
Formulae used in MS Excel:
‘ Applying dampening factor = MAX(IF(month number > last history month, previous forecast value ‘ ((month number ‘ last history month number) * dampening factor * previous value / number of months in the forecast), previous value), 0)
‘ Trended value = IF( TREND( historical data for new forecast month) < 0, 0, TREND( historical data for new forecast month))
‘ Ratio of Initial forecast value to the forecast value after applying dampening factor = IFERROR (previous value/ value after dampening, 0)
‘ Applying smoothing factor = previous ratio + (1- previous ratio) * IF(ABS(smooth factor))
‘ Final trended value = IF (month number > forecast month number, MIN(MAX(trended value*seasonality), min value), max value), initial value)
Procedure:
1. Enter historical and forecast data for drug A.
2. Apply the formula for the calculation of overrides in the same order as given above and the same formula in a spreadsheet.
3. Enter the desired value for the Smooth factor, max value, min value and dampening factor.
4. See the graph for changes.
Results:
Figure 2: Historical Values
Figure 3:Historical Value (contd.)
Figure 11: Forecast data without overrides
Figure 12: Forecast data without overrides (contd.)
Figure 13: Overrides entered and graph obtained.
Figure 14: Forecast values after overrides.
Figure 15: Forecast Values after overrides (contd.)
Figure 16: Deviation in graphs in visible after each modification in data.
In this concept model, we had initial forecast data and historical data and using the concept model, the values after overrides (smooth factor of 0.2, max value of 200000, min value of 10000 and dampening factor of 2) were obtained. The variation in the forecast can be visualized using the graph available above.
Using this model, we can understand that how will the forecast vary according to the overrides and demand planner can use it very effectively to forecast according to his own market research and analysis.
Concept Model: 4
Aim: To design a concept model for Market Events.
Theory:
Events in the pharmaceutical industry are referred to as any activity happening in the market which will affect the demands and sales of our product and will steal patients (consumers) from the owner. These activities happening at market level are called market events. Market events may be of two types: one in which there is launch of a competitors drug in the same class developed for same indication, and other where the production of the existing product increases due to more demand of that drug. The launch of new competitor’s drug can be a launch of generic drug (when there is loss of patent of the drug, the competitors may launch the same product with a slight difference in the composition of the excipient molecules in the drug) or a pure new drug (in case of such drug the chemical molecule is different evolved via new research and development, but is designed for the same indication). Increase in volume of the production is only for self-made drugs not competitors because the production level of the competitor’s drug cannot be estimated accurately; can only be predicted.
Whenever a new drug is launched in the market, it steals some share of patients from the existing drugs in the market. Also, this stealing increases gradually from month to month and reaches a point of stagnation. Simultaneously, the market also expands by some percentage due to incorporation of new patients in the sample space. Sometimes, a patient might take more than one drug of the same class for treatment. In such cases, the sample size deviates from the actuals. To estimate the actual sample size and prevalence in the market, we incorporate the concept of concomitancy rate. Concomitancy rate is the ratio of total number of drugs taken to the actual number of patients in the prevalence area. For example, there are three patients and two of them takes 2 drugs and one of them only one for the same disease. Hence, total there is incorporation of all the concepts in this concept model.
First, the actual total patients are calculated using the concomitancy factor. Then in case of launch of new drug, all the desired inputs such as market expansion rate, the patient share that the new drug will steal from the existing product and the monthly uptake rate will be entered. Then using MS Excel functions and formulae, the switched patients, add-on patients and patients due to market expansion will be calculated for each drug monthly. The total patients and market share of the new drug are then calculated. The last step is calculated automatically because of the designed concept model.
In case of volume increase of the existing product, the desired inputs are same as the previous event scenario, other than the switched patients for the drug whose production units are increasing remains zero which has to be entered manually. This is the only drawback of the existing concept model.
Formulae used in MS Excel:
‘ Market Treated Patients = Total patients / Concomitancy rate.
‘ Switched patients = monthly uptake rate * share steal from existing drugs * forecasted patients for that existing drug (monthly).
‘ Add-on Patients = monthly uptake rate * add-on value * forecasted patients for that existing drug (monthly).
‘ Patients due to market expansion = market expansion rate * market treated patients * monthly uptake rate.
‘ Total patients of new drug = switched patients + patients due to market expansion.
Procedure:
Market Event 1: Launch of new drug in the market.
1. Enter the forecast data of patients for all the existing drugs.
2. Calculate total patients by adding the patients of each drug monthly.
3. Enter the concomitancy rate and using the formulae for total market treated patients, calculate that.
4. Enter the desired inputs: Market expansion rate, steal share of each drug and add-on rate of each drug to the new drug.
5. Define the uptake each month which will lead till 100%.
6. Now calculate patients who switched from existing drug to new drug monthly using the formulae mentioned above of Switched patients.
7. Calculate total patients switched to new drug by adding them monthly.
8. Similarly, the monthly add-on patients are calculated.
9. Patients due to market expansion are then calculated using the formulae mentioned above.
10. Total patients of the new drug are then calculated by adding total switched patients, total add-on patients and total patients due to market expansion.
11. Market share of the new drug are then deliberate.
12. A summarized table of new patient number of existing drug and new drug is made.
Market Event 2: Increase in volume of existing drug.
1. The new forecast data after event 1 are first entered.
2. The desired inputs: market expansion rate, monthly uptake rate and patient share steal are entered. The share-steal for the drug whose volume is being increased remains zero.
3. Same as for market event 1.
Results:
Figure 17: Market before launch of event 1.
Figure 18: Desired inputs: Market expansion rate (1%), steal share and monthly uptake rate entered.
Figure 19: Total Switched patients, Add-on patients, patients due to market expansion calculated.
Figure 20: Market after launch of event.
Figure 21: Product A condition.
Figure22: Product B condition.
Figure 23: Product C condition.
Event 2: Increase in volume of production.
Figure 24: Market scenario before event 2 and after event 1.
Figure 25: Desired inputs entered (market expansion of 2%, steal share and monthly uptake rate).
Figure 26: Calculated switched patients, add-on patients and patients due to market expansion.
Figure 27: Market scenario after event 2.
Figure 28: Product A condition
Figure 29: Product B condition.
Figure 30: Product C condition.
DISCUSSION AND CONCLUSION
There are various benefits as well as disadvantages of these concept models. Concept model 1, based on three basic business rules, allows the analyst or demand planner to judge the trend of the forecast. Based on the trend followed the demand planner can conclude the future of the industry for which the forecast has been done. Furthermore, on this basis various solutions can be generated and opted for to increase the market and the demands. But, the standard value range of the three business rules which can be accepted has to be first ensured. This is usually decided after extensive market research. Concept model 2, based on the outlier correction and trending of data based on these outlier corrected historical values, is useful when the data using a specific strategic approach has to be trended to generate the forecast. Using this concept model, the values lying outside the curve can be brought in line with other values. Hence, the trending the history becomes easier for the analyst. But, for cases where there is launch of the new product, historical data does not exist. The trending and outlier correction is not required. The second part of concept model 2, based error estimation in the forecast, helps us to identify the error when forecast has been done using various approaches. But the same drawback exists for this concept too. This concept model cannot be used for new product launch as historical data does not exist. Concept model 3, based on forecast overrides, can be used when the demand planner needs to override the trended forecast based on his assumption and market research. The various overrides options available are; dampening factor (1 to 10), this factor enables the demand planner to generate a forecast where after a certain period of time he feels that sales going to be dropped. But, the pattern in the drop of sales is decided by the demand planner by market scenario and then input is given. Using this factor, the forecast is changed according to the level of drop in the sales; smoothing factor, this factor enables the demand planner to generate the curve which can be smooth or not smooth; and, maximum value and minimum value of demands in future, using this option the analyst can generate a curve which does not fall below a certain limit and does not increases above a certain point. But the disadvantage of these factor is that that it does not include the factor that can be opposite of the dampening factor. For example, if it is identified that the growth will be more than the trended data, the value cannot be calculated. Concept model 4, based on the market events makes the analyst go ahead with the modification in the forecast if there is a launch of a new product in the market or the volume of units manufactured is been increased by a certain limit. But, it does not incorporate certain other market events such as increase in volume produced by competitors, launch of a new product of different type but is can be used for same purpose, launch of a new class of product etc. It also does not account for all type of sharing and stealing the market from the existing product. The end user might want the explicit sharing method as the input, that is, the peak share point to be entered are the maximum sharing point of the market. It might even decrease. This type of requirement is not fulfilled in this concept model.
The overall benefit of the concept model is that it helps generate forecast based on these concepts very easily for a smaller data. For large database which are usually present in SQL (structured query language) server or MS Access database, requires much more formulation in MS Excel. That is possible, but this will increase the file size of the MS Excel to such an extent that the file will crash anytime. So, to avoid this VBA (visual basic application) is required which are based on these concept models to run the models efficiently. This was a shortcoming of the project as grasping knowledge of VBA is an extremely tedious job along with other learning too. The concept models made work best to my knowledge for those factors.
Hence, concept models were designed based on the following methods:
1. Business Rules
2. Outliers and error metrics
3. Overrides
4. Market Events
REFERENCES
1. Cook A G. (2006). ‘The Forecasting Process’. Forecasting for the Pharmaceutical Industry. Vol.1.1. Pg. 11-33.
2. Armstrong J M. (2001). ‘Selecting Forecasting Method’. Principles of Forecasting: A Handbook for Researchers and Practitioners.
3. Archer B H (1980). ‘Forecasting demands: quantitative and intuitive techniques’. International Journal of Tourism Management. Vol.2.1.
4. Armstrong J S, Green K. C. (2012). ‘Demand forecasting: Evidence based methods’. Journal of Forecasting.
5. Cook A G. (2006). ‘The Forecasting Process’. Forecasting for the Pharmaceutical Industry. Vol.1.1. Pg. 39-46.
6. Braae R, McNee W, David M. (1999). ‘Managing Pharmaceuticals Expenditure while increasing access’. Pharmacoeconomics. Vol. 16. Pg. 649-660.
7. Shafaei N, Ford J L, Morecoft C W, Taylor M J, Lisboa J B. (2009). ‘Transformation in pharmaceutical Industry ‘A systemic review of the literature’. PDA Journal of Pharmaceutical Science and technology. Vol. 67. Pg. 105-122.
8. Dan K. (2004). ‘The phase of Pharmaceutical Industry supply planning and demand forecasting’. The Journal of Business forecasting Methods and systems. Vol.23.3. Pg. 145-167.
9. Luois C. (2000). ‘Forecasting practices in pharmaceutical industry in Singapore’. The Journal of Business forecasting methods and systems. Vol. 19.2.
10. Ramnath S, Rock S, Shane P. (2007). ‘The financial analyst forecasting literature- A taxanomy with suggestions’. International Journal of forecasting. Vol.24.1. Pg. 34-75.
11. (2012). ‘Industry Overview: Pharmaceuticals and Biotech’. Wetfeet.
12. Song H, Li G.(2001). ‘Demand Modelling and forecasting- A review of recent research’. International Journal of Management. Vol.22.1
13. Fisher R A. (1925). ‘Theory of Statistical Estimation’. Mathematical Proceedings of the Cambridge philosophical Society. Vol. 22.5. pg. 700-725.
14. Moyal J E. (1949). ‘Quantum mehcanics as a Statistical theory’. Mathematical Proceedings of the Cambridge philosophical Society. Vol.45.1. Pg. 99-124.
15. Steel R G D, Torrie J H. (1980). ‘Principles and procedures of statistics, a biometrical approach’. Priciples ans procedures of statistics. Vol. 2. Pg.01-96.
16. McCullough B D, Heiser D A. (2008). ‘On the accuracy of statistical procedures in Microsoft Excel 2007’. Computational Statistics and Data Analysis. Vol.52. Pg. 4570-4578.
17. Remenyi D, Onofrei G, English J. (2010). ‘An introduction to statistics using Microsoft Excel’. Vol. 3.