EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:FINANCIAL RISK ANALYSIS

FINANCIAL RISK ANALYSIS

One of the earliest areas of application of simulation, dating back to the 1960s, was financial risk analysis. This continues today to be one of the most important areas of application.

When assessing any financial investment (or a portfolio of investments), the key trade- off is between the return from the investment and the risk associated with the investment. Of these two quantities, the less difficult one to determine is the return that would be obtained if everything evolves as currently projected. However, assessing the risk is relatively difficult. Fortunately, simulation is ideally suited to perform this risk analysis by obtaining a risk profile, namely, a frequency distribution of the return from the investment. The portion of the frequency distribution that reflects an unfavorable return clearly describes the risk associated with the investment.

The following example illustrates this approach in the context of real estate investments. Like the Everglade example in the preceding section, you will see simulation being used to refine prior analysis done by linear programming because this prior analysis was unable to take the uncertainty in future cash flows into account.

The Think-Big Financial Risk Analysis Problem

The Think-Big Development Co. is a major investor in commercial real estate development projects. It has been considering taking a share in three large construction projects— a high-rise office building, a hotel, and a shopping center. In each case, the partners in the project would spend three years with the construction, then retain ownership for three years while establishing the property, and then sell the property in the seventh year. By using estimates of expected cash flows, as well as constraints on the amounts of investment capital available both now and over the next three years, linear programming has been applied to obtain the following proposal for how big a share Think-Big should take in each of these projects:

Proposal

Do not take any share of the high-rise building project. Take a 16.50 percent share of the hotel project.

Take a 13.11 percent share of the shopping center project.

This proposal is estimated to return a net present value (NPV) of $18.11 million to Think- Big.

However, Think-Big management understands very well that such decisions should not be made without taking risk into account. These are very risky projects since it is unclear how well these properties will compete in the marketplace when they go into operation in a few years. Although the construction costs during the first three years can be estimated fairly roughly, the net incomes during the following three years of operation are very uncertain. Consequently, there is an extremely wide range of possible values for each sale price in year 7. Therefore, management wants risk analysis to be performed in the usual way (with simulation) to obtain a risk profile of what the total NPV might actually turn out to be with this proposal.

To perform this risk analysis, Think-Big staff now has devoted considerable time to estimating the amount of uncertainty in the cash flows for each project over the next seven years. These data are summarized in Table 28.2 (in units of millions of dollars) for a 100 percent share of each project. Thus, when taking a smaller percentage share of a project, the numbers in the table should be reduced proportionally to obtain the relevant numbers

for Think-Big. In years 1 through 6 for each project, the probability distribution of cash flow is assumed to be a normal distribution, where the first number shown is the estimated mean and the second number is the estimated standard deviation of the distribution. In year 7, the income from the sale of the property is assumed to have a uniform distribution over the range from the first number shown to the second number shown.

To compute NPV, a cost of capital of 10 percent per annum is being used. Thus, the cash flow in year n is divided by (1.1)n before adding these discounted cash flows to obtain NPV.

A Spreadsheet Model for Applying Simulation

A spreadsheet model has been formulated for this problem in Fig. 28.13. There is no uncertainty about the immediate (year 0) cash flows appearing in cells D6 and D16, so these are data cells. However, because of the uncertainty for years 1–7, cells D7:D13 and D17:D23 containing the simulated cash flows for these years need to be uncertain variable cells. (The numbers in these cells in Fig. 28.13 represent one possible random outcome— the last trial of the simulation run.) Table 28.2 specifies the probability distributions and their parameters that have been estimated for these cash flows, so the form of the distributions has been recorded in cells E7:E13 and E17:E23 while entering the corresponding parameters in cells F7:G13 and F17:G23. Figure 28.14 shows the Normal Distribution dialog box that is used to enter the parameters (mean and standard deviation) for the normal distribution into the first uncertain variable cell D7 by referencing cells F7 and G7. The formula in D7 is then copied and pasted into cells D8:D12 and D17:D22 to define these uncertain variable cells. The Uniform Distribution dialog box (like the similar one dis- played earlier in Fig. 20.9 for an integer uniform distribution) is used in a similar way to enter the parameters (minimum and maximum) for this kind of distribution into the uncer- tain variable cells D13 and D23.

The simulated cash flows in cells D6:D13 and D16:D23 are for 100 percent of the hotel project and the shopping center project, respectively, so Think-Big’s share of these cash flows needs to be reduced proportionally based on its shares in these projects. The proposal being analyzed is to take the shares shown in cells H28:H29. The equations entered into cells D28:D35 (see the bottom of Fig. 28.13) then gives Think-Big’s total cash flow in the respective years for its share of the two projects.

Think-Big’s management wants to obtain a risk profile of what the total net present value (NPV) might be with this proposal. Therefore, the results cell is NetPresent Value (D37). To show the mean NPV over the simulation run, MeanNPV (D39) is defined as a statistic cell.

The Simulation Results

Using the Simulation Options dialog box to specify 1,000 trials, Fig. 28.15 shows the results of applying simulation to the spreadsheet model in Fig. 28.13. The frequency chart in Fig. 28.15 provides the risk profile for the proposal since it shows the relative likelihood of the various values of NPV, including those where NPV is negative. The mean is $18.120 million, which is very attractive. However, the 1,000 trials generated an extremely wide range of NPV values, all the way from about –$28 million to over $62 million. Thus, there is a significant chance of incurring a huge loss. By entering 0 into the box in the Upper Cutoff box of the statistics table, the Likelihood box indicates that 81 percent of the trials resulted in a profit (a positive value of NPV). This also gives the bad news that there is roughly a 19 percent chance of incurring a loss of some size. The lightly shaded portion of the chart to the left of 0 shows that most of the trials with losses involved losses up to about $10 million, but that quite a few trials had losses that ranged from $10 million to nearly $30 million.

Armed with all this information, a managerial decision now can be made about whether the likelihood of a sizable profit justifies the significant risk of incurring a loss and perhaps even a very substantial loss. Thus, the role of simulation is to provide the information needed for making a sound decision, but it is management that uses its best judgment to make the decision.