Data Analytics and Forecasting

Document ID: CA_SOP_009

Version Number: 4.0

Effective Date: 2nd Jul 2020

Internal Use Only

Approval Information in Annexure Z
 Data Analytics and Forecasting

Contents

1. Purpose and Scope ......................................................................................................................... 3

2. Roles and Responsibilities ............................................................................................................... 5

3. Entry Criteria.................................................................................................................................... 5

4. Exit Criteria ...................................................................................................................................... 5

5. Process/ Policy/ Procedure Description (with details and Process Flow) ........................................... 6

6. Output Document........................................................................................................................... 13

7. Contact list ..................................................................................................................................... 14

8. Internal Documents ........................................................................................................................ 14

9. Annexure Z: Revision History ......................................................................................................... 14

Version: 4.0 Effective date: 2nd Jul 2020 Page 2 of 15

 Data Analytics and Forecasting

1. Purpose and Scope

Purpose

This document helps in understanding and executing data analytics and forecasting related projects.

Scope of the Deliverable – ‘Excel Based Forecasting Model’

Listed below are few questions that are usually focused on while creating a forecasting model

General Key Intelligence Questions (KIQs)

What is the time horizon and frequency (Monthly, Quarterly or Yearly) for which the forecasting
has to be done?

What is geography of forecast, is it US, EU5 or any other country?

Is it a single product forecast or multiproduct forecast?

The product to be forecasted is inline or pipeline, in case of inline whether it’s branded or
generics?

Which forecasting method should be used, Epi based, market based or both?

What are the events that will take place during the forecast horizon? What will be the impact of
these events on the forecast?

How to estimate the peak shares of product?

Which is the most suitable analogue for calculating the market share?

What should be the share theft mechanism in multiproduct forecast?

What pricing assumptions should be used?

How to capture the evolution in price change?

What should be the final output of the forecast-Patient numbers, units, MGs, gross sales NRA,
Net sales NRA or Net sales RA?

Why is forecasting needed?

Why forecasting in pharmaceutical industry is needed:
• Forecasting is prerequisite for strategic decisions
o Minimize uncertainty
o Investment decision
▪ Budget allocation
▪ Product development decision
• For decisions beyond gut feeling- through innate unbiased logical reasoning
• Identifying in-licensing opportunity: Forecasting helps in finding the potential of a product in
respective market which helps in making decisions for business development
• Investors make their investments based on the forecasted revenues which would be generated
from the pipeline drugs

Version: 4.0 Effective date: 2nd Jul 2020 Page 3 of 15

 Data Analytics and Forecasting

• Market based forecast estimated the accurate demand in the market which helps in
manufacturing adequate amount of units
Types of Forecasting

2 types of forecasting are most commonly used in pharmaceutical industry

• Epidemiology based forecasting: In this approach we first define the theoretical maximum
number of patients with a given disease state and then contract, or filter, the market to arrive at
the number of patients who currently are receiving therapy. This is done through a series of filters
such as incidence/prevalence, diagnosis and treatment rate. These treated patient pool who are
currently receiving therapy is further segmented into various treatment buckets (by products, drug
class etc.) to estimate the target patient pool for the product to be forecasted. This method is also
referred as bottom up approach

• Market based forecasting: In this approach we first gather the historical data, the historical data
can be sales, standard units (SU), total prescriptions (TRx), new prescriptions (NRx), Patient
days of therapy (PDOT) or patient numbers itself. Based on the historical data the base line
projection can be done by using different methods. The critical step in market-based forecast is
selecting the underlining methods to project it for future. There are different kind of projections
methods are available such as Linear regression, Exponential, Moving average, ARIMA,
Exponential smoothening, growth, power, quadratic etc. Selection of method is based on the
nature of the historical data, e.g. the historical data which has seasonality, it does not make
sense in selecting linear method for projecting data which has seasonality. Conversion
parameters such as average daily dosage, days of therapy (DOT), price per SU or units and
compliance rate are used to convert sales into patient numbers and then out of the total treated
patient pool the relevant is calculated to estimate the patient pool the product to be forecasted.
Market based forecasting can be done directly using the sales data without converting them into
patient numbers. The approach to be used is dependent on the data availability and the
methodology being used.

Version: 4.0 Effective date: 2nd Jul 2020 Page 4 of 15

 Data Analytics and Forecasting

2. Roles and Responsibilities

Roles and Responsibilities

Roles Responsibility
Project planning and management, communication with the clients, and second
Project Manager
level review by manager for content, summarization and presentation.
Data extraction, data analysis, dashboard creation, 1st level review by data fact
Senior Analyst check, consistency check, references check and addition of insights to the report,
sharing file with DTP/copy editing team.
Define forecasting parameters including market definition (indication, patient
segments, and geographic areas), product forecast (inline or pipeline products),
and time horizon for forecast. Build an appropriate query in IMS to fetch the
Analyst historical IMS data in USD. Calculate the baseline sales data for future years
either based by using a suitable statistical trending method. Converting the sales
data into patient numbers. Estimating patient share. Calculate revenues. Split the
revenues by lines of therapy if needed.

RACI Chart (Mandatory)

Project Manager Senior Analyst Analyst

Developing forecast methodology I R A

Building forecast tool/dashboard

R A C

File Review I R A

Client communication I I R

R-Responsibility, A-Accountability, C-Consulted, I-Informed

3. Entry Criteria
PO received from Client

RD: To understand the scope

TOQAP: For understanding the execution methodology of data analytics and forecasting based projects

Template: Check for existing template, else prepare a new template

4. Exit Criteria

1. Report quality check done by project manager.

2. Sign off taken from PM.

3. Deliverable sent to the client through email.

Version: 4.0 Effective date: 2nd Jul 2020 Page 5 of 15

 Data Analytics and Forecasting

5. Process/ Policy/ Procedure Description (with details and Process Flow)

Flow Chart

1. Epidemiology Based Forecasting (for Innovative Products)

Epidemiology
Analysis
Epidemiology
Events Population

Growth Rate Incidence Number/Rate

Diagnosis Number/Rate
Access to
Treatment Treatment Number/Rate

Attribute
Analysis
Existing Market Target Patient Pool New Product Primary
Patient Shares Patient Shares Market
Research
Historical Data Product Peak Share
Analog
Statistical Trending Uptake Curves Analysis

Patient Shares
Diffusion
Product
Curves
Parameters
Generic
Compliance Erosion

Days of Therapy Events

Price

Daily Dose
Forecasted Units &
Revenues

Version: 4.0 Effective date: 2nd Jul 2020 Page 6 of 15

 Data Analytics and Forecasting

2. Market Data-Based Forecasting (for Innovative Products)

Quarterly/annual historical sales Ex-trend events that affect the

($ & SU) data for the market Market forecast market

Regression Exponential
Smoothing/Curve fitting

Product profile comparison –

Historical SU share of competitor Estimated peak share [Attribute analysis, PMR insights]
molecules in analogous markets
Product penetration (historic &
projected)

Analogous share uptake Uptake curve

Price & adjustment factors (IMS

vs. company reported sales)

Ex-trend events that affect

the products

Forecasted sales

3. Epidemiology/Market based forecasting for IGx/BGx/Biosimilar

Innovator historical sales

Epidemiology
(standard units)

• Scale up/ Correction factor for sales

• Conversion Parameters
Reconciliation
Historical total Patients on
Innovator
Using statistically best-fit trend
and epidemiology trends

Evaluating Base-line trended patients

Interchangeability dynamics on Innovator

Future events Share theft Usage Split based

Erosion impact (Size
on penetration rates
of Innovator Loss)
(Uptake estimation)
Function of:
Based on:
Number of Patients on Patients on Patients on
Generics/Biosimilars Generics/ Generics/ Generics/ Attribute/coinjoint
Biosimilars 1 Biosimilars 2 Biosimilars n analysis

Pricing
Conversion Order of entry
Parameters
Re-imbursement/
Access status PMR Inputs

Market Access Sales by volume Sales by value

Version: 4.0 Effective date: 2nd Jul 2020 Page 7 of 15

 Data Analytics and Forecasting

Step by Step Instruction

Common steps for all the types of forecasting approach:

1. Finalize Key Intelligence Topics (KITs) and scope of the forecasting model.
2. Based on KITs and scope, decide on the type of forecast approach i.e. epidemiology based or
market based.
3. Create the data requirement (like IMS data query, syndicate report etc.) and check with the client
regarding the data availability.
4. Create the forecast methodology depending on the user requirement and data availability
5. Finalize the methodology with the client.

Steps for Epidemiology based Forecasting:

1. Epidemiology Analysis:
a. Estimating total number of cases with the disease
b. Identify diagnosis rate and factors affecting it
c. Estimating percentage of people diagnosed
d. Estimate number of people who have access & can afford treatment
e. Analyze number of people availing the treatment
2. Identifying target patient pool

Population

Incidence

Patient Pool

Diagnosis

Treatment

Events Impacting
Epidemiology
a. Identify the filters/segments to arrive at the target patient pool for the respective drug. i.e.
population>prevalence>diagnosed>treated pool. Build the above indicated funnel chart to
arrive at final target patient pool.
b. Identify appropriate population split specific to the therapeutic area (TA) i.e. adult or
pediatric population, gender specific population (like for breast cancer as TA, look for
female population) etc. Apply specific population and growth/decline rates to forecast the
population or use the data available on government censuses (e.g. US Census)
c. Look for the incidence/prevalence rate of the TA or the disease condition and apply the
below mentioned rates to specific population to calculate the total treated patient pool.
o Diagnosis rate and factors affecting it positively/negatively
o Number of people or % of people diagnosed
o Number of people who have access and can afford treatment
Version: 4.0 Effective date: 2nd Jul 2020 Page 8 of 15
 Data Analytics and Forecasting

o Number of people availing treatment

d. On the treated patient pool apply events which may have positive or negative effect on
the disease growth, eg., increased screening leading to increased diagnosis
3. Estimating patient share: Patient share is estimated using an analogue analysis or attribute
analysis, PMR inputs, or audit data like IMS, Synovate etc.
a. There different ways to arrive at new product patient share
i. Direct user entry: Directly user can enter the shares based on gut feeling
ii. Peak share estimation and time to peak: With the help of different uptake
curves along with its time to peak
iii. Attribute analysis: Identification of attributes, assigning weights to the
attributes, scoring products on each and every attribute, Clinical attributes and
commercial attributes are the critical parameter to look after
iv. Analogue analysis: Analogue analysis is ideal when the data availability is
limited, when quick and realistic visibility of the market is required, Nature of
market, product attributes and commercial environment are critical parameters to
consider.
v. Attribute analysis gives the peak share whereas the analogue analysis gives the
velocity with which it reaches its peak
vi. Order of entry: Shares are inversely proportional to order of entry, thus the first
product will receive the highest peak share, second product the highest, and so
forth

4. Applying conversion factors: Conversion parameters are used to convert patient numbers into
volume (sales units) and then to revenue by using the price. Daily dose, compliance and days of
treatment are commonly used conversion parameters.

Version: 4.0 Effective date: 2nd Jul 2020 Page 9 of 15

 Data Analytics and Forecasting

Number of patients

Compliance x

Prescribed dose per day Actual dose per day

Events Impacting Compliance

x
Persistence
Prescribed days of therapy Actual days of therapy

Volume

5. Data reconciliation: If the epidemiology flow with market-based data like IMS, reconcile the
patients based on the two approaches to see into the deviations and adjust the parameters in
either of the two approaches. If in the therapeutic area under the study has the usage of two or
more products in the same treatment condition, calculate the co-perception/poypill ratio and
validate this with the data available in the public domain or market research study.
6. Apply events which are likely to impact the treated patient pool (e.g. Generic erosion,
reimbursement, new product launch)
7. Defining pricing assumptions: An analogue can be used to calculate the price based on the
historical data. Price can also be a direct user input too.
8. Apply price on the volume to calculate the revenues.
9. Split the revenues by lines of therapy if needed (usually done while forecasting for oncology
products).
10. Use of analytical tools in epidemiology forecast (if required)

Risk and Uncertainty Sensitivity Feasibility

Importance of the
Estimate upside Connection map financial value of
potential and between input and products and
downside risk output variables projects to the
organization

 Simulations  Sensitivity  Net present value

 Scenarios analysis  ROI
 Tornado  Break-even
diagrams analysis
 Risk versus
return

Sample Epidemiology Based Forecast Flow:

Version: 4.0 Effective date: 2nd Jul 2020 Page 10 of 15

 Data Analytics and Forecasting

• Risk and Uncertainty:

o Risk and uncertainty are concepts frequently used interchangeably when discussing product
forecasts. In fact, the concepts of risk and uncertainty represent two very different dynamics
in the marketplace and should be treated differently
o From the forecaster’s perspective the difference between risk and uncertainty is related to if
(and when) the uncertainty is resolved. Risk is resolved throughout the planning process as a
product progresses in its development; uncertainty remains even after the product launches
to the market. This uncertainty is inherent in the forecast itself, because of uncertainty in input
assumptions. For example, in a forecast for congestive heart failure the sources of
prevalence for the disease may range between 1 and 3 per cent of the population. Assuming
a critical review of the data cannot resolve the differences in prevalence rates, what data
should the forecaster use? If the forecaster uses either pure data source there will be a
threefold difference in the size of the potential patient pool. If the forecaster combines the two
data points to average out 2 per cent and uses this as the forecast, they will be either
overestimating (if 1 per cent is the accurate number) or underestimating (if 3 per cent is the
accurate number) the forecast potential. In this simple example, exclusively using any of the
three potential measures – 1, 2, or 3 per cent – leads to an inaccurate portrayal of the
potential patients.
• Simulations:
o Generating ranges of forecast outcomes is accomplished using simulation methodology.
Simulation methods combine the uncertainty of the input variables to create a distribution of
outputs. 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. These methods effectively
capture the uncertainties inherent in the input assumptions and translate these uncertainties
into the forecast outputs. There 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 that create this capability. It also is possible to enable Excel to perform these
calculations without using additional software by using Visual Basic for Applications within

Version: 4.0 Effective date: 2nd Jul 2020 Page 11 of 15

 Data Analytics and Forecasting

Excel itself. Typically, several hundred iterations are required to produce a well-distributed
outcome.
• Scenarios:
o Simulation methods are appropriate for most input variables that range in the forecast. There
are, however, some input variables that create strong dependencies on the other variables in
the forecast. When strong dependency exists simulation, methods are not appropriate; in this
case the forecaster must use discrete scenarios. Examples of variables for which scenario –
instead of simulation – methods are employed are product profiles, launch date and price.
Each of these example variables is strongly linked to other variables in the forecast. The
product profile assumption may drive share, drug treatment rate, compliance, price and so
forth. Likewise, launch date may drive adoption, peak share, price and reimbursement. For
these variables a forecaster employs scenario analysis.
• Sensitivity Analysis:
o The goal of sensitivity analysis is to link the input assumptions to the outputs, determining the
degree to which the forecast outputs depend on each input variable. To a first approximation,
the forecast is said to be most sensitive to the input variable that most affects the outputs.
• Tornado Diagrams:
o The classic method for sensitivity analysis is tornado diagram, it is named like that because
they are shaped like a funnel- capture the effect of each input variable on the forecast. This
diagram is very powerful in communicating forecast sensitives.
• Water fall chart:
o Waterfall diagram provide a second set of sensitivity measures in forecast. Waterfall chart
gives the user insights into which filters are responsible for drop or surge in patient numbers
or revenue.

Steps for Market based Forecasting:

1. Defining forecasting parameters

a. Market definition (indication, patient segments, and geographic areas)
b. Product forecast (Marketed or pipeline products)
c. Time horizon for forecast
2. Build an appropriate query in IMS to fetch the historical IMS data in USD and SU. If the client
provided with IMS access, then run an appropriate query to fetch the data.
3. Calculate the baseline sales data for future years either based on CAGR or by using a suitable
statistical trending method (log, linear, polynomial or power curve, moving average, Exponential
smoothening or ARIMA)
4. Covert the sales data into patient numbers using the formula: Treated patient pool estimated
=Standard units/ (Daily dose * Compliance rate * Average days of treatment in a year
5. Daily dose, compliance and days of treatment are called the conversion parameters.
6. Apply events which are likely to impact the treated patient pool (e.g. Generic erosion,
reimbursement, new product launch)
7. Calculate the treated patient pool after applying the events.
8. Estimating patient share: Patient share is estimated using an analogue analysis or attribute
analysis, PMR inputs, or percentage available from secondary domain
9. Defining pricing assumptions: An analogue can be used to calculate the price based on the
historical data.
10. Apply price on the volume to calculate the revenues.

Version: 4.0 Effective date: 2nd Jul 2020 Page 12 of 15

 Data Analytics and Forecasting

11. Split the revenues by lines of therapy if needed (usually done while forecasting for oncology
products)

Steps for Epidemiology/Market Based Forecasting for Generic Products:

1. Define the time period of forecast and the methodology to be used.

2. Calculate the target patient pool using either market or epidemiology-based forecast. (Refer to
the steps mentioned above in epidemiology and market based forecast approach)
3. Identify probable future events which will impact the generic market/product. (e.g.
reimbursement, government initiatives)
4. Calculate the effective patient number after applying the events (Forecast can be done directly
on the sales data. Conversion of sales data into patient number is not mandatory)
5. Peak share assumptions:
a. Use a suitable analogue to calculate the peak share of the generic and time to reach this
peak share.
b. Identify other probable generics which are going to be launched.
c. Identify the order of entry of various generics based on the respective launch dates.
(Order of entry refers the launching sequence of the drugs. The drug which is launched
first has the order of entry 1)
d. Distribute the share amongst various generics based on the order of entry (analogues
can be used to estimate the share distribution based on order of entry)
6. Pricing assumptions:
a. An analogue can be used to calculate the generic price
b. Value based pricing, based on attributes of the product, in case of non-availability of any
suitable analogue
c. Based on the patent expiry, generic price slash can be applied on the current price (user
can also define the current price)
d. In case if the generic product is from a branded pharmaceutical group, then a premium
can be charged for the generic product too. In such cases a factor is applied on the
generic price.
7. Treated patient numbers based on the peak are converted into volumes using the conversion
parameters.
8. Volume is multiplied with the unit price to arrive at the revenues.

Review Process:

Review process needs to be carried out at every stage.

1. First level review should be done by subject matter experts (SMEs)/ Sr. Analyst/Associate
Manager.

2. Changes to be incorporated.

Client review and feedback received and accordingly the report is updated/ changed

6. Output Document

Excel Workbook and PowerPoint deck.

Version: 4.0 Effective date: 2nd Jul 2020 Page 13 of 15
 Data Analytics and Forecasting

7. Contact list

Commercial Analytics Data Analytics Team

8. Internal Documents

Sl. Item Comments Link

No.

1 Training Material Forecasting training material [Link]

om/sites/CA/SitePages/Data%20
and%20Analytics%[Link]

2 Checklists No separate checklist. Basic checklist

exists

3 Templates and NA
Tools

9. Annexure Z: Revision History

Document Approval Information

Document Owner Data & Analytics

Author(s) Name & Santosh Ragho Reviewed &

Designation(s) 4th Jun 2020 Approved and via
Lead, Data & Analytics Email

Reviewer(s) Name 4th Jun 2020 Tahira Shafiulla

& Designation(s)
Senior Manager, Data &
Analytics

Approver Name & 4th Jun 2020 Tahira Shafiulla

Designation(s)
Senior Manager, Data &
Analytics

Final Approver 02nd Jul 2020 Vijayvitthal.B

Name &
Designation Associate Manager
Business Process
Excellence

Version: 4.0 Effective date: 2nd Jul 2020 Page 14 of 15

 Data Analytics and Forecasting

Revision Summary
Ver. No. Ver. Date Revised By Description Filename
4.0 02nd Jul Santosh Ragho Edits & addition in patient Data_Analytics_Foreca
2020 estimation share section sting_v4.0

3.0 30th Jan Santosh Ragho Edits & addition of some basic Data_Analytics_Foreca
2019 questions in general KIQ’s table, sting_v3.0
patient share estimation methods,
Decision analytics concepts like
risk & uncertainty, Simulation,
Scenario, Sensitivity and sample
epi based forecast flow
2.0 23-OCT- Kushal P Singh Edits & Additions in the Pricing Data Analytics and
2017 Assumptions Forecasting _Ver 2.0

1.2 25-AUG- Kshitiz Shekhawat DMI Template changed, added Data Analytics and
2017 roles and responsibilities Forecasting _Ver 1.2

1.1 18-MAY- Kshitiz Shekhawat DMI Template changed Data Analytics and
2015 Forecasting _Ver 1.1

1.0 11-FEB- Kshitiz Shekhawat Development of TOQAP Data Analytics and

2014 Forecasting _Ver 1.0

Version: 4.0 Effective date: 2nd Jul 2020 Page 15 of 15