Engineering Finance
Lessons


Project Evaluation
Evaluation with Risk

Economics add-in

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.

.
Economic Analysis with Uncertainty
.

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.

Distribution Description

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 cash flow.

Measures of Risk

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 modeled.

Simulation

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.

Probabilities and percentiles obtained through simulation do not require the assumption of normality.

Other Distribution Types

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.

Summary

 

. Browser Page
Risk Summary
.

Navigation Front Page Lessons Resources

Return to Top of Page

Engineering Finance
by Paul A. Jensen
Copyright 2005 - All rights reserved

Front Page Lessons Resources