|Software Search, Categories and Specials||Similar Products
Monte Carlo Financial Modeling
Multiple Target Goal Seek
What-If Analysis Manager
Excel Model Builder
Finance & Statistics Model Set
|Buy Today (below) and send us your order ID and claim over $70.00 worth of FREE software|
With some analysis and a few
updates, you can add statistical risk and uncertainty analysis into any flat
spreadsheet model. This article will outline how to add probability
scenarios to your spreadsheet, create a Monte Carlo simulation, and
understand your exposure to risk and uncertainty.
A probability simulation can be critical when a spreadsheet model contains multiple uncertain values which are unrelated - such as project time or cost estimates from multiple vendors, or financial models with multiple independent, uncorrelated expected returns. This article will address probability simulation in terms of a simple financial or project planning model, but the concepts described below can be applied to complex spreadsheet models as well.
Step 1: Identify Uncertainty in the Model
The first step is to identify
the uncertainty in your model. In most spreadsheet models, there are one or
more (and often many) random variables, which are included in the model
using an estimate.
For example, a financial planning spreadsheet might include an estimate of future annual returns for a period of years. Based on past performance and historical data, you can use an educated guess for the return. But this value is a guess, and this is exactly the type of uncertainty that a probability simulation will attempt to model.
In a project planning model, a common uncertain variable is time to complete a given task. This value can often be estimated using contractor or management projections, past experience, or some other system, but this is again an uncertain field which should be modeled using a probability simulation.
Other common uncertain values are project costs (usually based on a contractor's estimate), event probability risk (as with insurance), and sales/revenue growth (generally based on optimistic projections).
Identify the variables in your spreadsheet model that are based on estimates of uncertain values. You can replace these one at a time to develop the model, and to get a better understanding of how the process works. Select at least one value to replace with a random value, and then continue.
Step 2: Define Parameters for Uncertain Values
The next step is to define
parameters for the uncertain value or values you're replacing in your
spreadsheet. If you know what the distribution should be, the add-in
has more than 20 random distribution functions for modeling uncertainty. If
you don't know, we'll Start by discussing two of the most commonly-used
probability distributions: the normal distribution and the beta-PERT
Financial returns, insurance and actuarial projections, and population estimates can use the normal distribution. This is often appropriate when the value is based on a large number of possible outcomes or has a large number of inputs. Normal curves model many real-world scenarios, from IQ distribution in population to the likelihood of inclement weather. If you know the expected mean and standard deviation of your uncertain variable, then the normal curve may be appropriate.
Cost and time estimates often use the beta-PERT distribution. This distribution is designed to model scenarios without well-defined parameters or with very few inputs, but with estimates for the minimum, maximum, and most likely values. It is often possible to get these estimates from contractors or managers. The beta-PERT distribution favors the most likely estimate but balances the probability with the minimum and maximum values.
If you know the mean and standard deviation, try using the normal curve. If you have estimates for the minimum, maximum, and most likely values, try the beta-PERT distribution. Replace your spreadsheet estimates with functions representing the distribution:
For the normal distribution,
=NormalValue( Mean, StandardDeviation )
For the beta PERT distribution,
=PERTValue( Minimum, MostLikely, Maximum )
Note that the beta PERT function takes an optional fourth parameter, lambda, which defines the shape of the curve (via the weight attached to the most likely value). You can ignore this parameter for now.
Once the random values are inserted, your model should calculate as before, although the output will be different depending on the generated random value. You might notice that the random values do not update when the spreadsheet is recalculated; the functions are designed to update only during a probability simulation, in order to reduce unnecessary calculation.
Step 3: Define Your Output Values and Run a Simulation
Your spreadsheet model likely
has one or more output cells, representing (for example) total aggregate
financial returns, total project time, total project cost, or the like. Any
number of output values can be modeled using a probability simulation.
Select one to Start ; more outputs can be added at any time.
For a given output cell, we will Start by defining the mean (average) result. Select another cell to use for this value, and enter the formula
=SimulationMean( OutputCell )
where the OutputCell is a reference to the output value you want to model.
You will initially see an #N/A error in the output value cell; this error indicates that there is no simulation data available for that cell.
Now run a Monte Carlo simulation. Select the menu item Monte Carlo -> Run Monte Carlo Simulation to open the simulation dialog. Click "Start " to Start the simulation. You should see your random variable update for each iteration of the simulation, and you should see the mean value update over time.
The mean represents the average value of the output. In a probability simulation, however, it is possible to represent a variety of possible outcomes based on different probabilities. Next to the mean output cell, create a new cell and add the formula
=SimulationPercentile( OutputCell, 10% )
You'll see a value that's lower than your mean value. What does this represent? The simulation percentile function provides the expected value of the output cell in some percentage of simulation iterations. When used with the value "10%" as above, the value represents the value of the output cell in 90% of the cases - sometimes known as the P90 value.
This value is important because it means that there is a 90% likelihood of achieving at least this result; in only 10% of cases does a lower result occur. That means that there is a 90% likelihood of achieving a particular return, or completing a project within a specified time or at a specified cost.
Any likelihood can be expressed in this fashion, and you can generate estimated minimum returns for any given probability using this simulation. You can also create charts expressing these probability results, using the histogram and charts wizard.
Analyzing the Results and Next Steps
It's important to recognize
that the P90 value is by no means guaranteed. In fact, there is a real
danger of over-relying on this value as a floor, when in fact it is nothing
more than an expression of probability.
Nevertheless, the P90 value and other simulation results values can be extremely useful in financial or project planning, because they can identify risks or uncertainties that were otherwise invisible in a spreadsheet model.
If you discover that the P90 value is unacceptable - representing cost or time overruns, or an insufficient aggregate financial return - it may be time to reassess other model inputs. This is the goal of a probability simulation - to understand the effects of risk and uncertainty, in order to have more visibility and control over the ultimate project outcome.
Special! Free Choice of Complete Excel Training Course or Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates