 |
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 |
|
|
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
distribution.
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.
$129.95Special! 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
SPECIALS!
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