Bullet-Proof your Business Case in less than 8 hours

In a previous post, I presented a method that product managers can use to integrate the stakeholders’ inputs into their business case, rapidly assess their impact on specific goals and, as a result, increase everyone’s confidence in the quality of the decision regarding the business case.

In the same post I then made the claim that this method, called a Monte-Carlo analysis, could be implemented in just one uninterrupted day by anyone with a minimum of spreadsheet skills.

This post walks you through the process via a step-by-step approach to help you build a bullet-proof business case in less than a day.

For the sake of the discussion, we will reuse the same example as in my previous post, and build an analysis to observe how Stakeholder’s input regarding price, quantities and fixed costs figures can impact the financial goal of a $1M NPV.

I must first disclose that I am not an Excel expert and I fully expect that someone reading this will suggest a faster and easier way to do this. This is an outcome that I am prepared for. Also, I have deconstructed the steps as much as possible so if you eyeball this post in horror, please do not get overwhelmed by the number of graphs and formulas. I am just trying to make it easy for you.

At any rate, you can access the actual spreadsheet here on the condition that you do not hold me responsible for any issues in your business case! Hang on to your boots; this is worth your time.

There are three steps I use to obtain the results presented in my previous post:

  1. Integration of stakeholder’s input to the spreadsheet
  2. Setup of the Monte-Carlo analysis
  3. Plotting of the results

Let’s look at each step individually:

Step 1: Integration of stakeholder’s input to the spreadsheet (estimate – 3 hours)

Step 1 is the most complex step and where we will devote most of our attention. Let’s look at the figure below:

The first worksheet is composed of 4 sections. The first section is the original business case which will be used to provide the baseline for the Monte-Carlo analysis.

The second section, named “input from stakeholders” captures in spreadsheet format the input obtained during the feedback phase, as described in the previous post. For example, your CTO estimated that there is a 50% chance that the fixed cost may vary uniformly +/- 40%. This is captured on line 23. It is important to spell each input separately for two important reasons: first this will facilitate the work when running a sensitivity analysis, second, if one of your stakeholders wants to refine their figures (for example the CEO now thinks that the chances of a competitor entering the market is 40% instead of 30%), you can just input the new numbers in a flash and see the impact right away.

The third section consists of 3 random numbers generated for each of the 3 figures that will change during the Monte-Carlo analysis: fixed costs, quantity and price. Since it is assumed that changes in price, quantities and fixed cost are uncorrelated, we need these 3 random numbers. Please note if at some point you feel there is a correlation, you can always model it, but we will stick to the initial assumptions for this discussion. These random numbers are generated using the RAND () Excel function.

The fourth section “New Business Case Integrating Input from Stakeholders” is where the meat of the analysis is. It has the exact same format of the original business case, but several lines show different numbers than the original business case. For example, look at the fixed cost line (line 6 on the original business case and line 33 on the new business case), quantities (lines 4 and 31 respectively), as well as the average price per item (lines 13 and 40).  As a result of these calculations, a new NPV is calculated in cell C46.

A close look at the price per item indicates that it is $10 on year 2 (cell D13), but it is now $8 in the new business case (cell D40). Why? Because the corresponding random number in C26 (0.205647 which is 20.5647%) is below the 30% specified in cell C22. As a result, the 20% reduction is applied for Y2 and all subsequent years.

Look at the figure below to see how it is done in the spreadsheet. Please note that I left column A empty, so the first column you see on the left is B.

We can incorporate stakeholder input into the average price / item through use of a logic function. The probability of a competitor entering the same market is stated as 30% (cell C22 above). The formula in cell D40 tests the probability factor for competition generated in cell C26. If the value of C26 is greater than the threshold (cell C22) then we keep the price of the original business case. If not, then the price is reduced to the amount specified in cell E22 (20% in this example).

Let’s look at how the quantities are calculated now in the figure below:

To address variability into the sales quantity forecast we will incorporate stakeholder input through use of a linear equation. In cell C31 above, that part of the formula in parentheses calculates a factor that enables a range from plus or minus the percentage entered in cell E24, in this example +/- 20%.

The factor in turn will be used to vary the projected sales quantities originally forecast in cells C4:G4. This example illustrates a straightforward linear distribution. Other means of distribution such as a bell curve can be incorporated into the business case through modification of the factor equation.

Finally for this section, let’s look at how we include the engineering costs issue:

Cell C33 is calculated in the same way as  as was the price. If the random value for fixed costs (cell B27)  is above a certain threshold (50%  in this example as delineated by cell B23), then the costs are the same as in the original business case. If not, then they are increased by 40% (cell E23).

Completion of this step provides for a single business case on the basis of the generated random numbers. This becomes the basis upon which a series of similar business cases are run to generate a distribution of results.

Step 2: Setup of the Monte-Carlo analysis (estimate – 30 minutes)

So now what we have is a baseline business case with the NPV changing each time new random numbers are generated. Since these numbers can be generated by just pressing a key on your keyboard, one could be tempted to do just that, then write down the NPV and repeat the process a couple thousand times…. Wait! There is a better way, which is what this step 2 is about. We are going to cut and paste the new business case 2048 times in the worksheet. This will allow us to access the result of 2048 Monte-Carlo runs from one worksheet so we can plot the results.

Why 2048? I recommend 2048 runs as it will provide for consistent results of about +/- 3% – results that are practical for a business case. The variation can be further reduced with additional runs. Since there is a limit on the number of lines per worksheet, you can use several worksheets if you want to increase the number of runs.

You may argue that this cut-and-paste operation is as much of a hassle than the previous method, but my response is that you can cut one instance of the business case and paste it. That gives you 2 instances (or runs). You can copy these 2 runs and paste them to obtain 4, then cut and paste the 4 runs to obtain 16, and so on… I did just that until I got 2048 runs of the business case as seen below (2 runs out of 2048):

You can see that since each random number is different, then the NPV results are different. See lines 68 and 90. The whole process of cut and paste should not take more than 20 minutes.

Step 3: Plotting of the results (estimate – 2 hours)

So now you have 2048 NPV values calculated across a spreadsheet. You cannot really understand what this all means until you plot the result as I did in my previous post. The first order of business is to bucket the results so they can be analyzed.  By bucketing, I mean counting the number of NPV values within specific bounds. For example, I want to know, out of the 2048 runs, how many NPV values fall between $100k and $200k. I do this in several sub-steps:

a)      Copy columns A, B and C of the worksheet developed in steps 1 and 2 above in a second worksheet named “bucketing”.

b)      Clean-up the “bucketing” worksheet by filtering for only NPV values. You do this with a simple IF/Then statement:

c)      Filter column A so that only the appropriate NPV information appears:

d)      Bucket the NPV values. I do this hard way by setting up a logic formula for each of the columns in which I am interested. This is done through nested “IF” statements which generates a count of the number of NPV values that fall within the specified range.

If a cell gets a “1” it means that the NPV falls into the bucket in that column. For example cell G107 displays a 1 because the NPV in line 107 is $308,622 and column G represents values from $300k to $400k. There’s got to be a better way with Pivot Tables, so if someone knows how to do this more elegantly please let me know.

Now we are done with the exhausting part! All we have to do is create a new worksheet called “plotting” and plot the results of the consolidated data below. Add all the 1s you got for each bucket and divide that number by 2048, to get the appropriate percentage.

You can use the table above to plot the data in any way you want. I opted for a Pareto-type, 2 Y-axis graph for the initial post. Click here if you want to know how to create a Pareto chart.

That’s it! Now you are done as far as the details of the workbook implementation is concerned. You can now focus your attention on interpreting the results. That’s the most important part. There is a chance that you will not like the answer that the Monte-Carlo analysis gives you. This is normal, because the human mind is usually optimistic and there is always a need to inject a dose of reality in business cases. Here are the scenarios likely to occur:

  • Your business case is “all over the place”. If half or more of the runs are within 50% of your financial goals, then anything could happen to your business case.  We will deal with this potential outcome in a future post.
  • Most of the runs are way below projected goals. You obviously have an overly optimistic business case and must readjust your expectations.
  • You see a bipolar situation, where 30% or the runs are well below your goals and 30% well above. You seem to have a business case that may depend on one or two extraneous events, such as a responsive/unresponsive market or competitor entrance/absence. Do not try to place your business case in the middle because obviously this “average” situation will not happen. Instead make two separate business cases for comparison and lay out the respective business assumptions to your management.

The next post in this series will discuss how to use the workbook developed above for a sensitivity analysis.

[Since I posted this, a few people suggested that I mention the existence of “cheap” packages which can do all of this above and more. This is definitely an option to consider]