Monte Carlo Analysis

These pages are designed for students in Intro. to Finance and other elementary Finance courses.

These pages are not meant to replace your textbook. They are provided as an adjunct to help you with practical problems and assignments.

Monte Carlo Analysis

Introduction

Monte Carlo Analysis is a general technique to aid in decision making in complex situations. Business decisions must be made in an environment that is filled with uncertainty about many factors simultaneously. Because of this fact many managers distrust recommendations based on computer models that assume that all inputs are known with certainty. The apparent precision of computer or spreadsheet analysis can be dangerous when an underlying uncertainty about the input numbers exists. When a spreadsheet model gives results based on number inputs that are assumed to be fixed it is known as a DETERMINISTIC MODEL. Deterministic models are useful to work out the relationships among variables. They are not good working tools for business situations. Many of the important factors involved in real business decisions are not known with certainty. Experienced managers are not completely in the dark, however. They are able to call on their experience to specify a range of possible values a factor may take. They will usually also be able to assess the probabilities of occurrence. Real business is an uncertain situation. Simulating this uncertain situation requires a STOCHASTIC MODEL. Stochastic models account for the uncertainty involved and give answers not as single numbers but as distributions of possible answers. The parameters of these answer distributions measure the risk inherent in a decision.

Spreadsheets and Uncertainty

There are built-in methods in spreadsheets to do some rudimentary accounting for risks in decision models. The most frequently used approach is also a good place to start a more sophisticated analysis.

After a deterministic model is completed, the numbers that were assumed to know with certainty are varied using Range Analyze What If to generate data tables showing how the bottom line number is affected by assuming varying input values. This process assumes all other factors are fixed while one factor is varied using one-way what is tables. This process is also called SENSITIVITY ANALYSIS, meaning how sensitive is the answer to variations in one factor. Up to three factors can be analyzed simultaneously using What-if tables.

The trouble with using What if tables in complex or realistic situations is that they assume that each input value is equally likely, and that is not likely to be the case in practice. It is possible to attach a probability set to a set of variable inputs and then calculate expected values. It is also possible to calculate variances, but those are meaningful only if the probability set is sort of unimodal and symmetrical. If there are more than two variables in a situation the calculation of the joint probabilities can be quite daunting!

The Monte Carlo Engine

The Monte Carlo Engine is based on the premise that many managers when facing an uncertain situation are able to predict the future as a small set of "states of nature". Each of these "states of nature" are understood as a "payoff table".

A typical Payoff Table:

TABLE 1

The manager represents the possible situation as one in which one of the five possible sales amounts can occur. Amounts beyond the scope of the table are not considered possible and the manager is not concerned about amounts occurring between the values listed. The amounts are not equally likely, but the manager is able to estimate the relative odds of occurrence by virtue of her expertise.

The next step is add a column to the table that converts the frequency distribution to a cumulative distribution:

TABLE 2

The next step is to adjust the cumulative probability column so that it matches the way @rand and @vlookup functions work in Lotus123.

TABLE 3

This adjustment is necessary since the @rand function generates random numbers between .00000 and .99999.

Now this is where the name Monte Carlo comes from. Monte Carlo is a principality that has been famous for casino gambling for hundreds of years. Imagine a roulette wheel with exactly 100 slots on it numbered from .00 to .99. The ball will fall into one of the slots, say .49. The @vlookup function will move down the adjusted value column until it finds a value that is beyond the value [.49] to be looked up. It will back up one row and move over one column [the offset] to find the value of occurrence. The value returned is 30000.

Although each slot on the roulette wheel [@rand] is equally probable the event looked up is not because of the probabilities in the lookup table.

Where this analysis is really a time saver is in complex realistic situations where lots of input numbers are variables. You can devise a process that looks up a number for each variable; enters that number in a spreadsheet model that looks like profit estimate sheet or an income statement. Of course you will get a different answer each time you do it but they are not random answers. If you repeat the process 100 times [easy for the computer] and make a record of the bottom line answer you will be able to see a distribution of answers that will give you an estimate of the most likely result and a measure of the variability or volatility [risk].

FIGURE 1

Converting the Deterministic Model to a Stochastic Model

When a spreadsheet model gives an exact result based on calculations done with known inputs it is called a deterministic model. This means that once the input values are known the answer is also known with certainty. Deterministic models are the way most spreadsheet decision models are started. We guess at the Sales, Process Cost, and Shipping cost as used in Figure 1 above. These guesses are placed in separate cells and not used directly in the calculation cells. After we have worked out all the relationships in the model to give the "bottom line" result, we go back to the cells containing the guesses and put in a formula like +SALES or +PROCESS where these names are the named ranges that are the cells that contain the @VLOOKUP function the gets the simulated result from the adjusted cumulative probability distribution column as shown in Table 3 above.

Every time the spreadsheet recalculates, you can use the F9 key to force recalculation, a different number will show up for the guesses. When you get all the probability distribution connected through the use of named ranges your deterministic model becomes a stochastic model. Notice that it will give a different answer every time you recalculate the spreadsheet. Observing the changeability of these answers gives an insight into the riskiness inherent in the decision.

Collecting the Results in an Organized Manner

All that remains is to set up a way to make the stochastic model generate a large number of answers, write the answers in a column and analyze that column with statistics and charts. All you need to do is to write a simple looping macro that will recalculate the spreadsheet and keep a record of the results by placing the result in a column of numbers before the previous answer. A macrois just a column of cells in the spreadsheet that is designed to do what you would do with your fingers or mouse. Macros are what make the OVERLOAD and WEALTH games work. Learning to write macros will move you into the realm of "power users" of spreadsheets.

Writing the Macros

FIGURE 2

All of the macro commands needed to generate 100 results from the model, place them in a column called OUTPUT are shown in the first five lines of FIGURE 2 above. The last line creates a chart of the results and the RESET lines erase everything to do it again.

Detailed instructions and guidance on setting up the macros to drive your model will be given in the class.

Comments and Suggestions should may be sent togramborw@tiger.uofs.edu