Introduction

Monte Carlo simulation is a process using probabilistic technique to generate random values for uncertain inputs then compute the outputs, replicating this process for multiple times to understand the distribution of outputs and their statistical properties (min, max, mean and so on).

In this project, I will apply Monte Carlo method to understand the future of a business decision. The method is commonly used in financial planning & budgeting to forecast the future and support decision making. A model is built to predict financial performance metrics (NPV, profitability, cashflow) in multiple periods. Thought most of finance professionals prefer Excel spreadsheet to run simulation, I prefer Python. I want to practice and hone my skill with Python. It's faster and more convenient.

Define problem

Suppose that ABC Company has discovered a potential drug breakthrough in the laboratory and needs to decide whether to conduct clinical trials and seek FDA approval to market the drug. Total R&D costs are expected to reach USD 700 million, and the cost of clinical trials will be about USD 150 million. The current market size is estimated to be two million people and is expected to grow at approximately 3% each year. In the first year, ABC estimates gaining an 8% market share, which is anticipated to grow around 20% each year. It is difficult to estimate beyond five years because new competitors are expected to be entering the market. A monthly prescription is anticipated to generate revenue of USD130 while incurring variable costs of USD40.

With a lot of uncertainty, their FP&A team is trying to illustrate different scenarios to predict the profitability of the new product. They are interested in evaluating the risk associated with the project and need to answer 3 questions as follows:

1. What is the risk that the net present value (NPV) over the five years will not be positive?
2. What are the chances that the product will show a positive cumulative net profit in the third year?
3. In the fifth year, what minimum amount of cumulative profit are we likely to achieve with a probability of at least 90%?

The distributions and parameters of uncertain variables are as follows:

a. Market size is normally estimated with average of 2,000,000 units (mean) and could fluctuate of 400,000 units (standard deviation).
b. R&D costs are expected to reach any level between USD 600 million and USD 800 million (uniformly distribution).
c. Clinical trial costs: normal with average of USD 150 million and standard deviation USD 30 million.
d. Annual market growth factor: triangular with minimum = 2%, maximum 6%, and most likely = 3%.
e. Annual market share growth rate: triangular with minimum = 15%, maximum = 25%, and most likely = 20%.

Reference: Evans, J. R. (2021). Spreadsheet Modeling and Analysis. In Business analytics: Methods, models, and decisions (pp. 405–440). Pearson.

Import library

import numpy as np
import pandas as pd
import statistics
import random
import matplotlib.pyplot as plt
%matplotlib inline

Inputs

discount_rate = 0.09
unit_sales = 130
unit_cost = 40
unit_margin = unit_sales - unit_cost
market_size_mean = 2000000
market_size_sd = 40000
clinical_trial_cost_mean = 150000000
clinical_trial_cost_sd = 30000000
rd_cost_lower = 600000000
rd_cost_upper = 800000000
annual_mk_growth_min = .02
annual_mk_growth_mode = .03
annual_mk_growth_max = .06  
share_growth_min = .15
share_growth_mode = .2
share_growth_max = .25
iteration = 1000 
project_year = 5

Set up code

In this problem, some inputs are uncertain and distributed in different ways. Inputs are normal, uniform and triangular random variates.

Normal distribution: Clinical trial cost, market size.
Uniform distribution: R&D cost.
Triangular distribution: annual market growth factor and annual market share growth rate.

For R&D and Clinical trial cost, the random inputs are used in first year only, these expenses presumably to spend all once the project begins. The other inputs like growth rate or market size, the team must use different input values for each year of the model as these values are independent of each other. For example, the code below generate 5 different radom value of share growth rate for 5 years.

share_growth_rate = np.random.triangular(share_growth_min,share_growth_mode,share_growth_max,project_year)
share_growth_rate

array([0.2196643 , 0.21885991, 0.22675191, 0.20275684, 0.1965074 ])

To build the model, we need to understand what is NPV.

"Net present value (NPV) is a financial metric that seeks to capture the total value of a potential investment opportunity. The idea is to project all of the future cash inflows and outflows associated with an investment, discount all those future cash flows to the present day, and then add them together. The resulting number after adding all the positive and negative cash flows together is the investment’s NPV. A positive NPV means that, after accounting for the time value of money, you will make money if you proceed with the investment."

For more information, please check https://www.investopedia.com/terms/n/npv.asp

def project ():
    outputs = []
    for i in range(iteration):
        rd_cost = np.random.uniform(rd_cost_lower,rd_cost_upper) # generate a random value for R&D cost at the beginning
        clinical_trial_cost = np.random.normal(clinical_trial_cost_mean,clinical_trial_cost_sd) # generate a random value, similar to R&D
        project_cost = round((rd_cost + clinical_trial_cost)/1000000) # sum up the investment costs
        share = .08
        mksize = np.random.normal(market_size_mean,market_size_sd) # generate a random value
        share_growth = list(np.random.triangular(share_growth_min,share_growth_mode,share_growth_max,project_year)) # generate random value for each year
        annual_mk_growth = list(np.random.triangular(annual_mk_growth_min,annual_mk_growth_mode,annual_mk_growth_max,project_year)) # generate random value for each year
        yearly_cum_profit = []
        cumulated_profit = 0 - project_cost
        npv_profit = 0
        year = 0
        while year < project_year:
            profit = round((share * mksize *  unit_margin * 12)/1000000)
            cumulated_profit = cumulated_profit + profit
            yearly_cum_profit.append(cumulated_profit)
            npv_profit = npv_profit + round(profit/(1+discount_rate)**(year+1)) #discount all those future cash inflows to the present day, and then add them together
            share = share * (1+share_growth[year])
            mksize = mksize * (1+ annual_mk_growth[year])
            year += 1
        npv_project = round((npv_profit - project_cost)) #project all of the future cash inflows and outflows associated with an investment
        yearly_cum_profit.append(npv_project)
        outputs.append(yearly_cum_profit)
    return outputs

Result table

The first 5 columns describe cumulated profit each year. The last column shows total project NPV after 5 years.

df = pd.DataFrame(project(), columns = ['year1','year2','year3','year4','year5','Total_NPV'])
df.head() #showing first 5 rows

	year1	year2	year3	year4	year5	Total_NPV
0	-662	-448	-176	163	576	222
1	-624	-413	-157	170	581	237
2	-673	-464	-200	132	546	197
3	-623	-399	-112	253	704	326
4	-560	-342	-66	271	691	333

df.describe(percentiles=[.95, .75, .5, .25, .05])

	year1	year2	year3	year4	year5	Total_NPV
count	1000.000000	1000.00000	1000.000000	1000.000000	1000.000000	1000.000000
mean	-677.639000	-462.55800	-195.231000	137.227000	550.481000	199.672000
std	64.942515	65.26253	66.755854	70.797215	78.506655	72.220622
min	-843.000000	-629.00000	-366.000000	-55.000000	316.000000	-9.000000
5%	-785.000000	-568.00000	-306.000000	22.950000	420.950000	80.950000
25%	-730.000000	-514.25000	-246.000000	86.000000	495.750000	149.750000
50%	-677.000000	-463.00000	-195.000000	140.000000	553.000000	202.000000
75%	-626.000000	-412.00000	-146.000000	189.000000	604.000000	250.000000
95%	-572.950000	-358.95000	-88.000000	253.000000	678.050000	321.000000
max	-523.000000	-304.00000	-17.000000	349.000000	803.000000	420.000000

df['Total_NPV'].plot.hist(bins=100)
plt.xlabel("Total NPV")
plt.ylabel("Occurence")
plt.title('Net present value over 5 years')

Text(0.5, 1.0, 'Net present value over 5 years')

Analyse the result

What is the probability that the net present value over the 5 years will not be positive?
To tackle this, we need to calculate P(x < 0) of Total NPV.
The result shows it is rarely a chance that Total project NPV is negative. It is a profitable project after 5 years.

threshold_x1 = 0
p_x1 = sum(map(lambda x: x < threshold_x1, df['Total_NPV']))/len(df)
p_x1_display = "{:.0%}".format(p_x1)
print('Chance of npv < threshold ' + str(threshold_x1) + 'M is: ' + str(p_x1_display))

Chance of npv < threshold 0M is: 0%

What are the chances that the product will show a cumulative net profit in the third year?
To tackle this, we need to calculate P(x > 0) in Year 3.
The result shows it is unlikely that the cumulated net profit is positive in year 3.

threshold_x2 = 0
p_x2 = sum(map(lambda x: x > threshold_x2, df['year3']))/len(df)
p_x2_display = "{:.0%}".format(p_x2)
print('Chance of year 3 cumulated net profit > threshold ' + str(threshold_x2) + 'M is: ' + str(p_x2_display))

Chance of year 3 cumulated net profit > threshold 0M is: 0%

What cumulative profit in the fifth year are we likely to realize with a probability of at least 0.90? Or in the fifth year, what minimum amount of cumulative profit are we likely to achieve with a probability of at least 0.90? ~ we are looking for the 10th percentile

print('90 percent of chance that the cumulated profit in year 5 will larger than ' + str(np.percentile(df['year5'],10)))

90 percent of chance that the cumulated profit in year 5 will larger than 446.0

With the assumption that all investment costs is spent in at the beginning, it starts with a negative cash flow and becomes positive when cumulated profit exceed the initial investment costs. As can be seen from the summary:

• Average cumulated profit (or cash flow) of the project is negative from year 1 to 3, positive from year 4 on ward.
• Average total project NPV is approximately 200M

It is a profitable project over 5 years.