The Economics add-in provides convenient
features for modeling cash flow components as random variables.
Click the QuickTime icon to see a movie that outlines
the process of creating and analyzing a model with uncertainty. There is no sound with the movie.
For convenience we repeat some of figures from
the movie on the remainder of this page. To create a model
with uncertainty, choose the Add Project item from the
Economics menu and click the Uncertainty checkbox
on the Project
Dialog as shown below. Select a distribution for the cash
flow components. Clicking a named distribution assigns
all components with the same distribution type. The General button
allows the components to have different distribution types.
A model that includes uncertainty is shown below.
All the cash flow values are assigned triangular distributions.
The example is shown in two parts, but the form on the worksheet
stretches across the worksheet with the cash flow section to the
right of the distribution section.
The distribution types are in column C and
the distribution parameters are in columns D through F.
The Random Variables add-in computes the Mean, Variance and Quantile values
in columns G, H and I, respectively. The Quantiles values
depend on the percentages in column I.
Cash Flow Description
The cash flow information is to the
right of the distribution information. First we explain
the content of rows 10 through 15. Column K holds point
estimates of the component values. Three different point
estimates are available, the Mean
Point Estimate, the Quantile Point Estimate and
the Simulated Point Estimate. The example uses
the mean values that are computed in column G. Characteristics
of the cash flow are described in columns L through O.
The equivalency factors are computed by the Economics
add-in in column P. The factors are used to compute
the mean and variance values in columns Q and R. Column S holds
the NPW values computed using the point estimates.
The results of the analysis are shown in
rows 2 through 6. Row 2 holds the statistical parameters
of the NPW. Cell P2 is the sum of the Mean
NPW values for the components. Cell Q2 is the sum
of Variance NPW values for the components, and
cell R2 is the square root of the variance. Cell S2 is
the NPW computed with the point estimates. Cells
P2 and S2 are the same because the means of the component
distributions are used as the point estimate in this
case. For quantile point estimates and simulated point
estimates, cells P2 and S2 are usually different.
Row 3 holds
the statistical parameters of the NAW computed
from the NPW using the (A/P, i, N)
factor. Row 4 holds the NPW for the study period.
Since the life is the same as the study period, rows 2
and 4 are identical. The IRR value shown in cell S5 is
the internal rate of return of the point estimate
If we assume that the NPW is a normally
distributed random variable, we can use the normal distribution
to make probability statements regarding the NPW or NAW.
The figure below shows a form creating by the Random Variables add-in that
describes the distribution of the NPW. Note we have
set the mean and standard deviation of the random variable, Ex_NPW,
equal to the mean and standard deviation computed for the NPW.
Cell P23 holds a function that computes the probability
that the value falls in the range specified by cells P21 and
P22. The case above shows the probability that the NPW is
less than 0. Cell P25 holds a function that computes the kth
percentile of the random variable. The value of k is
in cell P24. For the case shown, there is a 10% chance that the
NPW will fall below -1,018.4. These are both measures of risk
that may be useful to decision makers when uncertainty is explicitly
When the cash flow component values are not normally
distributed or when other features of the model are uncertain,
simple combinations of means and variances are not sufficient
to obtain valid statistical results. For these situations, we
use Monte-Carlo simulation.
The first step toward simulation is to change the point estimates
to simulated values. This is done by selecting Change Project from
the Economics menu and selecting Simulate from
the point estimate options. Column K in the worksheet below holds
simulated values of the cash flow components. Cell S2 contains
the NPW for the simulated values for a single replication.
Choose Simulate_RV from the Random
Variables menu to obtain the dialog below. Enter S2 as
the cell to simulate because it holds the simulated NPW.
The dialog specifies that 1000 observations are to be simulated
and the check boxes indicate the statistics to be gathered.
Part of the results of a simulation run are shown
below along with the results computed by the Economics
add-in. The mean and standard deviation obtained by the
simulation are close to those obtained by the previous section
where normality was assumed.
The frequency chart created by the simulation defines
a random variable called SimS2. The name comes from
the address of the simulated cell. Functions available from the Random
Variables add-in compute probabilities and percentile levels
based on the simulated distribution.
and percentiles obtained through simulation do not require
the assumption of normality.
The figure below shows the statistical portion of the model
when the Beta distribution is chosen as the default
type. Four columns are provided for the parameters of this distribution.
The form for the general distribution type provides four
columns for parameters, but does not identify the parameters. Different
distributions may be entered for the cash flow components as shown below.
The various distributions have different numbers of parameters
ranging from the Fixed distribution
with a single parameter to the Beta distribution with
four parameters. Unused parameters are neglected by the add-in,
but they must be numeric. The example uses 0 for the unused parameters.
Return to Top of Page
by Paul A. Jensen
Copyright 2005 - All rights reserved