A couple of weeks ago, I had this question from a potential client: - "How can I explain to stakeholders what simulation adds to a portfolio forecast?". At the time, I answered that you could look at all possible futures and draw conclusions from that. I realized that this answer was not satisfactory because it is kind of difficult to visualize what that means.
In this article, I will create a portfolio revenue forecast based on simulation, and I will then use it to explain how simulations can elevate portfolio analysis.
Portfolio Background
I will use a sample of 17 projects that I like to call the spice portfolio. Each one of the spices represents an R&D asset in development, and in the table below you can see the assumptions that I have used. There are durations, costs, and the probability of success for each phase of development for each asset.
There are also sales assumptions that will be needed to generate the revenue curve for each project and for the total portfolio.
As the table shows, we are using ranges for almost all assumptions. This is to reflect the uncertain nature of drug development, not just in terms of technical success, but also commercial and operational uncertainty.
A Basic Revenue Forecast The first step in creating a revenue forecast is to look at the individual asset level. In many cases, a commercial organization will create sales forecasts and provide them as static time series. In our case, we describe the sales potential with only three variables. Here is how it works:
Sales start when the product launches, and the date for that is given by the R&D model and the timelines for the phases. The time to ramp up to the peak is an assumption, and so is the peak sales. When patents expire, the sales drop back to zero again. This is a very simple and straightforward sales model that we will use for individual assets in this example. In a real case, this would be more detailed just as an Excel-based sales model would. Since we are using ranges for most variables, sales will vary even for the individual assets. In the left picture below we can see the expected sales for the Garlic asset given launch, and in the right, we can see the revenue spread across all 10 000 simulation iterations that we did! Same project and simulation, but two perspectives on the sales forecast.
What can we do with this data? Well, the curve on the left is exactly the type of curve we would get from an excel sales model. These are basically static time series that assume a launch date. For a simulation, I consider this to be one of the outputs rather than input. The curve on the right shows us the uncertainty in what we can expect from the Garlic asset in terms of sales. We can use this to start to plan what we will do if we end up at the lower end of the spectrum. Portfolio Revenue The revenue of a portfolio is - simply put - the sum of the revenue of all the assets within. We can create a portfolio forecast in two ways: 1. Calculate a mean (expected) revenue for each project using the 10.000 outcomes from the simulations. Then for the portfolio, add together the expected revenue for each project per year.
2. For each simulation iteration, sum the generated revenue for the assets that launch in that particular simulation iteration. When we have a portfolio forecast for all 10.000 iterations, we can use that data in all sorts of analyses including the estimated revenue as in the picture above.
One benefit of using the second method is that we can investigate the sales forecast case by case. From an analytic point of view, this gives us a wealth of possibilities to analyze data, including cross-referencing with other data to better understand cause and effect.
The picture above shows the portfolio revenue from 10.000 simulation iterations, and here we can see that the peak sales for the portfolio can reach 15B, although the expected peak is 5B (see the previous picture). If we cross-reference this data with project data and with portfolio NPV data, we can create an analytic that tells us more about the factors behind these sales curves.
Portfolio Level Analysis Using Tableau, I created a dashboard that consists of four interlinked parts (see picture below). the upper left is the revenue chart. The upper right is a table with project-level information - Launch information and peak sales. The chart in the lower right is again a portfolio level chart. It cross-references the number of new launches with Portfolio NPV and shows the result for each simulation iteration.
I have added a box-plot here to show where the majority of outcomes lie. And last but not least, in the lower-left, we have a chart that shows Peak sales variation across the projects in the portfolio. So, two project and two portfolio level charts, and they are all connected in the dashboard. The connectivity means that we can select a single simulation iteration, and see exactly what lies behind that particular outcome in the other charts. The following picture shows an example: I have selected the iteration that yielded the highest NPV and, coincidentally, the highest peak sales for the portfolio.
In the revenue chart, this is the outcome with the highest peak sales, and relative to most other simulations also has a fairly steep ramp-up of sales. It looks like it has a fairly steep ramp-down after peak sales as well. Let's see if we can figure out why that is.
The project data table has now changed to reflect the selection that we have made. We can see that 7 projects were launched in this outcome, and it looks like Caper is a major contributor with 4.7B in peak sales. If we look at the Project PYS chart see that Caper peak sales are in the top percent of the range. This means that quite a lot in this iteration is dependent on Caper. In the lower-right chart, we can see that this is the iteration with the top Portfolio NPV in the entire simulation. This tells us that if Caper performs well, it drives portfolio value as well. I am going to show one more example, and now I will select the 20 iterations that have the highest sales in 2031 - this goes towards creating sustainable revenue for the portfolio.
When we look at the dashboard now, we can see that it is Garlic and Basil that are the main contributors. They are launched in 90% of the iterations that have high revenue in 2031. Sales for these two assets are in the upper half of their range so they must perform well. We can also see that we need 7-8 launches at least to be in this group of outcomes. Summary Run simulations to generate a vast number of potential outcomes for the portfolio. This will allow analytics that will help stakeholders and everyone else understand the dynamic in the portfolio. It also allows for what-if analyses to be done directly, on the spot, in the investment meeting!