An example of a Monte Carlo simulation risk analysis model for cost modeling

Technical difficulty: 4

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VoseSumProduct,VoseAggregateMC

Model description

A common situation is that we have a large random number of revenue items that follow the same probability distribution but which are independent of each other, and we have independent profit margins that follow another distribution that must be applied to each revenue item.

This type of analysis quickly becomes extremely cumbersome to model because for each revenue item we need two distributions: one for revenue and another for the profit margin and we may have many types of large numbers of revenue items. It is such a common problem that we designed a function in ModelRisk to handle this, allowing you to keep the model to a manageable size, speeding up simulation time, and making the model far simpler to review.

Consider the following problem: a capital venture company is considering investing in a company that makes TV shows. They expect to make PERT(28,32,39) pilots next year which will generate revenues of $PERT(120,150,250)k each independently and from which the profit margin is PERT(1%,5%,12%). There is a 30% chance that each pilot is made into a TV series run in that country running for Discrete({1,2,3,4,5},{0.4,0.25,0.2,0.1,0.05}) series, where each season of each series generates $PERT(120,150,250)k with margins of PERT(15%,25%,45%).

There is a 20% chance that these local series would be sold to the US generating $PERT(240,255,1350) per season sold of which the profit margin is PERT(65%,70%,85%). What is the total profit generated from next year's pilots?

The solution is provided in this model:



The problem is not technically difficult but the scale of the modelling explodes very quickly. The figure shows how using ModelRisk objects for modeling allows us a surprisingly succinct model: rows 5 to 14 are the input data, rows 17 to 19 are the actual calculations – just nine cells in total.

There are a few things to point out. In Cell F5, ½ is subtracted and added to the minimum and maximum estimates respectively of the number of pilots to give a more realistic chance of their occurrence after rounding. Distributions are input as ModelRisk objects in cells F6, etc. because we want to use these distributions many times.

Cell C19 and elsewhere uses the VoseSumProduct function to add together revenue * margin for each pilot where the revenue and margin distributions are defined by the distribution Objects in Cells F6 and F7 respectively.

Cell F17 simulates the number of pilots that made it to become series, from which the model determines how many of those become series also sold into the US in Cell E17, the difference being the number of pilots that only became local series in Cell D17. Setting up the logic this way ensures that we have a consistent model: the local only and US & local series always add up to the total series produced. Cells D18 and E18 use the VoseAggregateMC(x, y) function to simulate the sum of x random variables all taking the same distribution y defined as an Object.