On average, our customers have one breast and one testicle – the problem of forecasting

July 17, 2010

The ‘Flaw of Averages‘ is alive and well in sales forecasting in CRM software. I have fallen in love with salesforce.com (big time) and have been playing about with the forecasting module; great, but is doesn’t capture the binary nature of a sales opportunity. If I have an opportunity for $100k and assign a subjective probability of 60% that we will get it, what should we put in a forecast? Expected value = $100k x 60% = $60k – not really cos it either ends up $0 or $100k. Same principle that “The average human has one breast and one testicle” (Des McHale).

When these normalised expected values are used you don’t get a sense of the pipeline variance. For example, if we have 100 opportunities of $1k, each with a likelihood of 50% or, 1 opportunity of $100k with a likelihood of 50%, the expected value is the same, $50K, but I know which of these I would rather include in a forecast.

It’s Monte Carlo time: salesforce.com has a nice integration with Excel to export reports. Here is an example spreadsheet with a bunch of random values for the sale value and subjective likelihood to close

DOWNLOAD THE MONTECARLO FORECAST SPREADSHEET

What does it do? It extracts the pipeline data from salesforce and creates a list of the opportunities, their sales value and probability to close. A macro is then used to simulate 300 ‘possible outcomes’ i.e. in one outcome we may close on the first two opportunities and not the third, in a different outcome we may only close on one opportunity etc. From these simulated outcomes we then can pull out some stats; the expected sales figure, min, max and the variance i.e. possible spread of results.

Lots of eggs in different baskets: if we have 100 opportunities of $1k, each with a likelihood of 50% then it turns out that it is very likely we’ll close on at least $40k worth of business. The corollary is that it is also very unlikely that we’ll close on more than $60k of business. Like death and taxes, you have the inevitability of making a trade between risk and reward. Having a good estimate of the variance is very useful for planning especially if you need to watch cash flow. Also handy to keep in check the ever present cognitive bias of over confidence.


The graph above is a value-at-risk (VAR) chart for the case of 100 opportunities of $1k, each with a likelihood of 50%. The distribution shows that the most likely outcome is around the $50k mark, which is what the expected value should be. Most of the action happens in the $40-$60k region so you can use these as your upper and lower estimates when rolling up into a financial model.

In the example above it is pretty easy to calculate the exact expected return and variance. The Monte Carlo approach becomes useful in the real world case where the individual sales values and likelihood to close are all over the map. I’ve also found it useful because the integration sucks the real data out of salesforce seamlessly. In the example below there is a larger $40k opportunity with a likelihood of close of 70% grouped in with the smaller $1k opportunities; what we now see is a bimodal distribution, where depending on whether we close the big deal we are sitting on the lower or upper distribution – for the electronic engineers reading, there’s a sales ‘band gap’ J


Using this type of forecasting approach integrated with your real world CRM data helps figure out what the pipeline risk is more realistically. The big health warning is that you need good historically data on the likelihood to close, at different parts of your sales cycle and to apply consistently across a sales team, or you’ll end up with GIGO.


Interactive Net Present Value (NPV)

June 28, 2008

Net present value (NPV) is defined as the present value of net cash flows. It is a standard method for using the time value of money to appraise long-term projects. See full description at Wikipedia.

Each cash inflow/outflow is discounted back to its present value (PV). Then they are summed. Therefore


Where

t – the time of the cash flow

N – the total time of the project

r – the discount rate (the rate of return that could be earned on an investment in the financial markets with similar risk)

Ct – the net cash flow (the amount of cash) at time t

Interactive NPV Calculation – Click here to launch NPV chart


The following dashboard allows you to change the discount rate and see the time value of a dollar over a period of 5 years.


At a 10% discount rate a dollar in year 5 is worth 62 cents in today’s money


At a 50% discount rate a dollar in year 5 is worth 13 cents in today’s money


We can also demonstrate an arbitrary project with an initial cash outlay and an increasing yearly income stream


At a higher discount rate of 50% the future income is heavily discounted and the overall project NPV is significantly reduced.


SBIR Monte Carlo Analysis

June 10, 2008

Introduction

SBIR grants are a key source of early stage financing for many small companies – awards are frequent, transparent, 100% funded (allowing significant non-dilutive growth), and may result in product revenue as the agencies act as informed customers. Here is a useful link explaining SBIRs and a report by David Connell, which argues that the UK should urgently introduce the US SBIR scheme that has successfully converted billions of dollars of taxpayer-funded research into highly valuable products and helped build hundreds of successful companies.

Start-ups trying to embed SBIR into planning can use Monte Carlo methods to analyse

  1. Total SBIR contract revenue over a time period and likelihood
  2. Cash flow over time and the associated variances
  3. The effect of increasing the number of applications on total and revenue and cash profile
  4. Increasing future resources required to execute and support grants – Including increasing headcount and capital expenditures

Formulating a static income model – Download the excel model here

We begin with a functionally valid model of cash flow generation from SBIRs over time. The following is a static, logical model of cash flow generation through SBIR awards. An SBIR grant is typically divided into Phase 1 and Phase 2 (there is possibility for follow-on funding, however the intricacies will not be covered here), worth $100k and $750k respectively. Historical data is in the public domain enables us to estimate the likelihood of attaining a Phase 1 and subsequent Phase 2. The likelihood of a successful Phase 1 award is approximately 25% and Phase 2 is 50%.





Monte Carlo income model

Whilst traditional static NPV analyses take projections of the uncertainty drivers as input and produce a numerical estimate of the value of the project, a Monte Carlo simulation takes distributions of the uncertainty drivers as inputs and produces a distribution of the value. For example instead of assuming a fixed number of successful Phase 1’s per year we can create a distribution to reflect the fact that in one year we may get none while in another we may get 4.

The underlying technology for Monte Carlo simulation is that of random number generation. The total SBIR contract revenue spreadsheet can be “randomised” by replacing the number in each uncertain cell by a random number generator which samples a number from an appropriate distribution. Each time we press the re-calculate button, the computer fills all uncertain cells simultaneously with sampled values. In other words, the computer creates a scenario from appropriate distributions and calculates the corresponding SBIR contract revenue. We can ask the computer to do this many, say 5,000 times and to gather the corresponding revenues. The risk profile of the project is now obtained by setting up a value-at-risk chart from this list of revenues. This shape reflects the spread of revenue obtained from 5,000 simulated outcomes.

Modelling the number of successful SBIR awards

The starting point of the Monte Carlo simulation is finding a way to capture the uncertainty in how many grants we are likely to be awarded, for example, if we apply for 4 SBIR’s in one financial quarter what is the likelihood of getting one award? Two awards? No awards? The problem is identical to coin tossing. If there is a 50% chance of getting heads what is the chance of getting 3 heads if I flip the coin 4 times? This is described mathematically using a binomial distribution.


Where P(n) is the probability of exactly n successful SBIR awards

N is the number of SBIR applications

p is the probability of a successful application for one SBIR application.


The following is the probability density function for the binomial distribution of grant applications. If we make 4 applications and there is a 25% chance that a single application is successful then we see that there is a 31.6% chance that we will not receive any awards and 0.4% chance that we would receive all 4.

A similar model applies for Phase 2 awards, except they are also dependent on the number of successful Phase 1 applications.

Incorporating an uncertain number of successful SBIR awards into cash flow

Estimates of the number of awards have now been replaced with a binomial distribution to capture the uncertainty. Every time the computer recalculates it creates a scenario from the appropriate distributions and calculates the corresponding SBIR contract revenue. The yellow cells contain a binomial distribution. Here are two simulated outcomes



The two example scenarios are just two possible instances of what might actually happen and it is very clear that there can be a significant difference in the amount of contract revenue obtained. In the latter scenario there have been far fewer successful Phase 1 and 2 applications and a difference in over $4million in received contract revenues.

We can use the spreadsheet to re-sample from the distributions by pressing the re-calculate button. Each time we do this, we obtain new realizations of the uncertain quantities and a corresponding new contract revenue sample. Using the spreadsheet add-in software @Risk from Palisade tools we can automatically do this many times and collect these revenue’s in a list, and obtain a good idea of the distribution of the possible outcomes. The possible revenue outcomes can be graphically represented either as a histogram or as a value-at-risk chart.

Monte Carlo Income Statement results (4 applications per quarter)

Value at Risk – Total SBIR contract revenues (4 application per quarter). The curve on the value at risk chart gives the likelihood that the contract revenue lies below or above the target values given on the horizontal axis. Risk profiles are ideally suited to convey the variance associated with the contract revenue. For example, on the chart below, the likelihood of total SBIR revenues less than $6M is approximately 30% and the likelihood of more than $6M is approximately 70%.


Value at Risk – Yearly contract revenues (4 application per quarter)


Summary Income Chart – Quarterly contract revenues (4 application per quarter)


Monte Carlo Income Statement results (6 applications per quarter)

Phase 1 distribution function

It is clear an increased number of applications per quarter will result in higher total SBIR contract revenue

Value at Risk – Total SBIR contract revenues (6 applications per quarter)

SBIR Income Summary Table

The following table summarises the key information on the timing, likelihood and amount of contract revenues