Following up to yesterday’s post about randomness, Excel provides a very easy tool for modeling probability. Just insert =RAND() into a cell. This will generate a new (pseudo) random number between 0 and 1 each time the spreadsheet is recalculated.
For most business modeling one wants to have a discrete distribution of values with a subjective probability distribution. For instance, in projecting out the cap table of a company one might have three possible valuations for the next round – low, expected and high with probabilities of 30%, 60%, 10%. To model this, one needs to form the cumulative distribution, which is 30%, 90%, 100%, and then use vlookup (or hlookup) with the random number generate by RAND() as the lookup value to find the outcome.
A good model will have a fair number of instances of RAND(). If the model is large it’s usually a good idea to set Calculation to “Manual” in the options while working on the model (just hit F9 to get a new set of random numbers). Finally, to generate an actual distribution of outputs of the model one wants to run the model hundreds or even thousands of times. This can easily be done with a VBA macro that uses CALCULATE to get a new set of random numbers and then copies the outputs to a results table (in fact for speed it’s best to gather the results in an array and then dump the array into an output spreadsheet in one go).
