# Financial risk analysis using financial risk simulation program.

Investment decisions, using discounted cash flow decision
techniques such as net present worth and rate of return, are based on
underlying projections of revenues, costs, interest rates, product mix
and other variables. The measure most commonly used in evaluating
potential investments is the single-estimate expected value that
represents the most probable estimate of future conditions.

In almost any financial evaluation environment, some of the parameters are the best estimate of experienced personnel or are based on a very cursory analysis of minimal data. Others are based on projections of future market, financial and manufacturing conditions which fluctuate over time. Consequently, risk associated with parameter estimates must be incorporated in financial analysis.

The solution technique most commonly used for risk analysis is simulation. When a large number of variables have uncertainty associated with them, or there are complex dependence relationships between the variables, use of simulation is the preferred alternative. This analysis consists of estimating the probability distribution of each variable affecting the investment decision and simulating the system to determine the range of possible outcomes and probabilities associated with them.

Benefits resulting from simulation analysis include:

* If forces the analyst to think systematically about the individual economic determinants of investment risk;

* It allows the decision maker to quantify the reliability of the estimates; and

* It answers questions concerning investment, such as the probability that the investment will have a net present worth greater than zero.

Program description

One Financial Risk Simulation Program (FRSP) is designed to aid in decision making regarding potential capital investments. The program uses Monte Carlo simulation techniques to estimate the net present worth of an investment. The program is written using the Auattro Pro 3.0 spreadsheet system. The emphasis in the design of the system has been on user interaction so that a user with little or no background in spreadsheet operation can successfully use the system.

Program input consists of two types of variables: stochastic and deterministic. The stochastic variables specified by the user include initial investment costs, annual revenues, annual expenses and salvage value. Annual expenses and revenues are the product of number of units produced (or sold) per year and unit costs and sales price. The user may directly input revenues and expenses or express them in terms of the components (unit costs/revenues and quantities).

The system provides four commonly used distributions to model the stochastic components. These include:

* Constant or deterministic value;

* Uniform distribution;

* Normal distribution; and

* Triangular distribution.

Once a distribution is selected, the associated parameters of that distribution need to be specified. The uniform distribution requires two parameters: minimum and maximum. The normal distribution also requires two parameters: mean and standard deviation. The triangular distribution requires three paramters: minimum, mode and maximum.

Interest rate and study period are specified as deterministic parameters. This is because these parameters are kept constant in analyzing specific scenarios. However, multiple runs may be made with different values for these parameters (such as different interest rates) to evaluate the impact of these parameters on the alternative being analyzed.

The other input parameter required from the user is the number of simulation runs. The user may enter any desired value. However, large number of trials (for example, 200 to 500 trials) are recommended for increased confidence in results obtained. Furthermore, the execution times is quite fast, particularly on a 286- or a 386-based microcomputer.

Based on the user input, the system automatically computes random variables from specified distributions; estimates net present worth (NPW) of the alternative; and presents the results to the user. System output includes a table summarizing results for each cost component and net present worth, frequency histogram for NPW and the probability of NPW being less than some specified value.

Using the program

To illustrate the use of the system, consider the following investment alternative. Assume that a company is considering the purchase of automated production equipment that costs $10,000. The sales forecast calls for a production rate of 5,000 units per year, although fluctuations in output may occur as a result of changes in market demand and the equipmnents' availability over time (due to breakdowns, etc.). Sales fluctuations may be modeled as being normally distributed with a standard deviation of 500 units. Equipment availability, variations in raw material and labor rates may also cause variability in unit production costs. In absence of prior information, the triangular distribution can be used as an approximate fit. The use of triangular distribution requires specification of three parameters, minimum, mode (or most likely) and maximum values. For unit production costs, these values are estimated to be $.50, $.70 and $1.10, respectively. The company would like to maintain a relatively constant sales price of $1.50 per unit. The salvage value at the end of the equipments' useful life of 10 years is estimated to be uniformly distributed between $2,000 and $5,000. Assume the average interest rate to be 12 percent.

FRSP is an easy-to-use program. All that needs to be done is to follow the prompts on the screens.

Figure 1 shows the main menu that first appears when the program is loaded. The first four options are for data input and printing. The next three options are specific to simulation analysis. The last three options are for viewing and plotting distribution of net present value and for computing the probability of net present value being less than some specified value. The "Exit" option takes the user to the previous menu, in this case to the DOS operating system.

Upon selecting the "Input Data" option from the main menu, Figure 2 is displayed on the screen. As mentioned previously, initial cost, revenues, expenses and salvage value are treated as stochastic variables; interest rate and study period are treated as deterministic. When a stochastic parameter is selected for input, distribution options are displayed on the screen. After a selection is made the user is prompted to enter the parameters associated with that distribution.

All input values (Figure 2) need to be specified before running simulation. The user is prompted if an input error occurs. Also, the user has the option of changing input values using the "Edit Data" option. This option may also be used for making changes in sensitivity analysis. The input data may be viewed using the "View Data" option.

The "Run Simulation" option in Figure 1 executes simulation. Once simulation is complete, the results may be viewed and printed. Simulation output consists of individual results from each of the simulation trials and the expected value and variance resulting from these trials. Figure 3 shows the first three and last three iterations, and summary of results. The summary also shows total simulation time. In this case, it took three minutes for 200 trials on a 386-based computer.

The distribution of NPW resulting from 200 trials is shown in Figure 4. If the "Probability Calculations" option in Figure 1 is selected, the user is prompted for a target NPW value to be used in computations.

For further reading

References Law, A.M., "How to Choose Input Probabilitty Distributions for Simulation," Simon Sez, 8, Summer 1988.

Randhawa, S.U. and T.M. West, "Uncertainty Modeling in CIM Investment Analysis," CIM Review, 6, 1, Fall 1989.

In almost any financial evaluation environment, some of the parameters are the best estimate of experienced personnel or are based on a very cursory analysis of minimal data. Others are based on projections of future market, financial and manufacturing conditions which fluctuate over time. Consequently, risk associated with parameter estimates must be incorporated in financial analysis.

The solution technique most commonly used for risk analysis is simulation. When a large number of variables have uncertainty associated with them, or there are complex dependence relationships between the variables, use of simulation is the preferred alternative. This analysis consists of estimating the probability distribution of each variable affecting the investment decision and simulating the system to determine the range of possible outcomes and probabilities associated with them.

Benefits resulting from simulation analysis include:

* If forces the analyst to think systematically about the individual economic determinants of investment risk;

* It allows the decision maker to quantify the reliability of the estimates; and

* It answers questions concerning investment, such as the probability that the investment will have a net present worth greater than zero.

Program description

One Financial Risk Simulation Program (FRSP) is designed to aid in decision making regarding potential capital investments. The program uses Monte Carlo simulation techniques to estimate the net present worth of an investment. The program is written using the Auattro Pro 3.0 spreadsheet system. The emphasis in the design of the system has been on user interaction so that a user with little or no background in spreadsheet operation can successfully use the system.

Program input consists of two types of variables: stochastic and deterministic. The stochastic variables specified by the user include initial investment costs, annual revenues, annual expenses and salvage value. Annual expenses and revenues are the product of number of units produced (or sold) per year and unit costs and sales price. The user may directly input revenues and expenses or express them in terms of the components (unit costs/revenues and quantities).

The system provides four commonly used distributions to model the stochastic components. These include:

* Constant or deterministic value;

* Uniform distribution;

* Normal distribution; and

* Triangular distribution.

Once a distribution is selected, the associated parameters of that distribution need to be specified. The uniform distribution requires two parameters: minimum and maximum. The normal distribution also requires two parameters: mean and standard deviation. The triangular distribution requires three paramters: minimum, mode and maximum.

Interest rate and study period are specified as deterministic parameters. This is because these parameters are kept constant in analyzing specific scenarios. However, multiple runs may be made with different values for these parameters (such as different interest rates) to evaluate the impact of these parameters on the alternative being analyzed.

The other input parameter required from the user is the number of simulation runs. The user may enter any desired value. However, large number of trials (for example, 200 to 500 trials) are recommended for increased confidence in results obtained. Furthermore, the execution times is quite fast, particularly on a 286- or a 386-based microcomputer.

Based on the user input, the system automatically computes random variables from specified distributions; estimates net present worth (NPW) of the alternative; and presents the results to the user. System output includes a table summarizing results for each cost component and net present worth, frequency histogram for NPW and the probability of NPW being less than some specified value.

Using the program

To illustrate the use of the system, consider the following investment alternative. Assume that a company is considering the purchase of automated production equipment that costs $10,000. The sales forecast calls for a production rate of 5,000 units per year, although fluctuations in output may occur as a result of changes in market demand and the equipmnents' availability over time (due to breakdowns, etc.). Sales fluctuations may be modeled as being normally distributed with a standard deviation of 500 units. Equipment availability, variations in raw material and labor rates may also cause variability in unit production costs. In absence of prior information, the triangular distribution can be used as an approximate fit. The use of triangular distribution requires specification of three parameters, minimum, mode (or most likely) and maximum values. For unit production costs, these values are estimated to be $.50, $.70 and $1.10, respectively. The company would like to maintain a relatively constant sales price of $1.50 per unit. The salvage value at the end of the equipments' useful life of 10 years is estimated to be uniformly distributed between $2,000 and $5,000. Assume the average interest rate to be 12 percent.

FRSP is an easy-to-use program. All that needs to be done is to follow the prompts on the screens.

Figure 1 shows the main menu that first appears when the program is loaded. The first four options are for data input and printing. The next three options are specific to simulation analysis. The last three options are for viewing and plotting distribution of net present value and for computing the probability of net present value being less than some specified value. The "Exit" option takes the user to the previous menu, in this case to the DOS operating system.

Upon selecting the "Input Data" option from the main menu, Figure 2 is displayed on the screen. As mentioned previously, initial cost, revenues, expenses and salvage value are treated as stochastic variables; interest rate and study period are treated as deterministic. When a stochastic parameter is selected for input, distribution options are displayed on the screen. After a selection is made the user is prompted to enter the parameters associated with that distribution.

All input values (Figure 2) need to be specified before running simulation. The user is prompted if an input error occurs. Also, the user has the option of changing input values using the "Edit Data" option. This option may also be used for making changes in sensitivity analysis. The input data may be viewed using the "View Data" option.

The "Run Simulation" option in Figure 1 executes simulation. Once simulation is complete, the results may be viewed and printed. Simulation output consists of individual results from each of the simulation trials and the expected value and variance resulting from these trials. Figure 3 shows the first three and last three iterations, and summary of results. The summary also shows total simulation time. In this case, it took three minutes for 200 trials on a 386-based computer.

The distribution of NPW resulting from 200 trials is shown in Figure 4. If the "Probability Calculations" option in Figure 1 is selected, the user is prompted for a target NPW value to be used in computations.

For further reading

References Law, A.M., "How to Choose Input Probabilitty Distributions for Simulation," Simon Sez, 8, Summer 1988.

Randhawa, S.U. and T.M. West, "Uncertainty Modeling in CIM Investment Analysis," CIM Review, 6, 1, Fall 1989.

Printer friendly Cite/link Email Feedback | |

Author: | Randhawa, Sabah U.; Douglas, James A. |
---|---|

Publication: | Industrial Management |

Date: | Sep 1, 1993 |

Words: | 1205 |

Previous Article: | Total flexibility management: a managerial approach for developing flexible resources. |

Next Article: | Operational planning: going beyond PERT with TQM tools. |

Topics: |