Excel Models
R&D Valuation
From the perspective of evaluating innovative projects, the model for releasing a new drug is very interesting. The process is fully regulated by the government, as neither the government nor businesses have an interest in thoughtlessly wasting such expenses or releasing inadequate drugs.
The process consists of four stages, each with clearly defined goals and a system for evaluating results.
Since progression to each subsequent stage happens only upon success, the entire process can be visualized as a “tree of events.” At each stage, there is a probability of success, additional necessary investment in the case of success, and the expected outcome value (Terminal Value) in case of complete success, which needs to be discounted to derive the NPV.
For example:
- At the start of the project, $10 million is required. The first phase lasts one year with a 50% chance of success.
- At the beginning of the second phase, an additional $30 million is needed, it also lasts one year, and the probability of success is 40%.
- At the start of the third phase, another $60 million is required, lasting three years, with a 50% success probability.
- In the case of success, the FDA grants a license, and the expected project return is $1 billion.
This would result in a diagram yielding an outcome of $63 million, which should be discounted at a risk-free rate, as we are dealing with specific, non-systematic risks. Assuming a rate of 5%, the final NPV would be $43.18 million*.
The same result can be obtained using Monte Carlo simulation (see Excel)*:
Note: The result may vary slightly, as only 10,000 iterations are programmed in Excel to avoid overloading the file.
It’s important to consider that the simulation allows for more interesting and accurate results, as event probabilities may not be uniformly distributed (as the rand function does), allowing for additional variables in the calculations.
For example, if I simulate revenues in a construction-development project and think the price ranges from $1,000 to $1,200, instead of giving each figure in this range an equal chance, I could use a triangular distribution where probabilities decrease as values approach $1,000 on the left and $1,200 on the right (i.e., frequency of figures in the model decreases).
In practice, other than triangular, normal (bell-shaped), log-normal, exponential, and other types of distributions are used. Thanks to ChatGPT, I created an Excel file where random data generated by the rand function is transformed into various distribution types (see Excel**).
Finally, as mentioned, the drug release model can be further complicated by introducing additional variables.
In Excel***, only the third phase is modeled, and variables such as market size, drug effectiveness (FDA approval probability), and competitor drug effectiveness (=> market share) are added. Calculations are presented in the following sequence:
- On the first sheet, NPV is calculated without simulation, using average figures—the result indicates that the project is unprofitable (-$410 million).
- The second sheet is prepared for intermediate calculations where probability distributions of variables are calculated based on normal and triangular distributions.
- The third sheet contains the simulation, which changes our decision by showing that the expected NPV is +$285 million (and not -$410 million); this outcome results from more accurate modeling of probability distributions.
- The fourth sheet is the most interesting. Here, an option to sell the license if FDA approval is granted and the project is abandoned is added, increasing the project NPV from $285 million to $475 million.
Excel Models:
3 Phase Monte Carlo Simulation
III Phase Simulation with Decision Node
Source:
Venture Capital & the Finance of Innovation – by A. Metrick & A. Yasuda