We will restrict our discussion to so-called Monte Carlo simulations. Such simulations start with extracting random numbers which are employed to calculate values from probability distributions that constitute the simulation model. These values can then be used to perform specific calculations and obtain the results we want to study.
Assume you want to simulate values for a variable x given by x = a + b. Variable a follows the uniform probability distribution at the top of figure 14.1, which means that all values between 100 and 500 have the same probability. The area under a probability distribution is always 1. Thus numbers between 0 and 1 can represent different probabilities and areas under the probability distribution with corresponding values on the horizontal axis. At the top of figure 14.1 we see that 0 corresponds to a = 100, 0.5 to a = 300, and 1 to a = 500. A random number, such as 0.823, corresponds to a = 100 + 0.823(500 – 100) = 429.2.
Variable b follows a normal distribution with μ = 300 and σ = 50 as shown at the bottom of figure 14.1. This means for instance that the probability for obtaining a value less than 300 for b is 0.5, since the area under the distribution to the left of b = 300 is 0.5. For a given probability the value b on the horizontal axis can be calculated with the Excel function NORMINV. For the random number (and probability) 0.298, the value of b is calculated to NORMINV(0.298;300;50) = 273.5.
From the two simulated values of a and b in figure 14.1 the simulated value of x can be calculated to x = 429.2 + 273.5 = 702.7. If we can draw random numbers for a and b, we can repeatedly simulate as many values for x as we want. The distribution of the simulated values gives us valuable information. Simulations must always be carried out on computers with appropriate software. In this book we will restrict ourselves to Excel, which is suitable for smaller simulation models.
Figure 14.1 Simulation.
The function RAND has no arguments and generates a new random number between 0 and 1 each time the spreadsheet carries out its calculations. This function will be demonstrated in a later example. Another alternative is the function RANDBETWEEN which generates random numbers between two specified values.
Normally all calculations are repeated each time we change something in the spreadsheet. Since simulations normally include lots of calculations, this might be a problem. To avoid this problem, Excel can be set to manual calculation. Choose the tab Formulas and then Calculation Options and Manual. When a spreadsheet is set to manual calculation, new calculations are performed each time you push F9.
Let us illustrate simulation by looking at an example where operating income is simulated for a product. The product is sold for the price p per unit, variable unit cost is vuc, fixed costs per year are FC, and number of units sold per year is n. Operating income per year can be calculated as:
Operating income = (p – vuc)n – FC
Let us assume that n can have values between 5,500 and 6,000 units per year, and that all values between these limits have the same probability. This means that n follows the uniform probability distribution in figure 14.2.
Figure 14.2 Uniform probability distribution for units sold per year.
To pick values for n arbitrarily from this distribution, we can apply the formula:
= B8 + (B9 – B8) * B5
In figure 14.3 cell B6 contains this formula. We also have 5,500 in cell B8, 6,000 in cell B9, and the formula RAND in cell B5. When multiplying the random number with the difference 50,000 – 40,000 = 10,000 (B9 – B8), and adding 40,000 (B8), we get a random number between 40,000 and 50,000 appearing in cell B6.
The same results can also be obtained with the following formula:
RANDBETWEEN (B8;B9)
Figure 14.3 Simulation of operating income per year.
Price per unit is assumed to follow a normal distribution with expectation value £17 and standard deviation £1 as illustrated in figure 14.4. The function NORMINV calculates a value on the horizontal axis for a specified normal distribution based on a probability between 0 and 1. In figure 14.4 the area under the curve to the left of the dotted line is 0.3507, which corresponds to the value 16.62 on the horizontal axis (cf. figure 14.3).
Figure 14.4 Normal distribution for price per unit.
The dialogue box for the NORMINV function in figure 14.5 uses the probability 0.3507 in cell C5, the median £17 in cell C9 and the standard deviation £1 in cell C11 as arguments. This results in the price p = 16.62.
For some strange reasons we assume that variable unit cost (vuc) follows a binomial distribution with n = 5 and p = 0.7. This means that
and so on. The actual probability distribution is illustrated in figure 14.6.
Figure 14.5 Dialogue box for the NORMINV function.
Figure 14.6 Binomial probability distribution.
The cumulative probabilities are:
which means that the probability of getting vuc = 0 is 0.00243, the probability of getting vuc = 0 or 1 is 0.03078, etc. The BINOM.INV function returns the value on the horizontal axis corresponding to a given value in the cumulative probability distribution. If we in this case enter the random number 0.00112 to the BINOM.INV function, it returns 0. If we enter 0.80012 to the function, it returns 4, and so on. In the spreadsheet in figure 14.3 cell D6 contains the formula “=BINOM.INV(D9;D11;D5)”. With 0.3187 in cell D5 the formula returns 3 in cell D6.
We assume that fixed costs per year (FC) follow a discrete distribution with probabilities as shown in figure 14.7. Fixed costs per year can have only one of five distinct values.
Figure 14.7 Discrete probability distribution for fixed cost per year.
We use a random number between 0 and 1 to choose one of the five possible values for FC. This must be done so that the probability for choosing £10,000 is 0.1, for choosing £20,000 is 0.25, etc.
To accomplish this, we start with the cumulative probabilities in figure 14.8. Here the probability increases from one row to the next by the probabilities in figure 14.7.
Figure 14.8 Intervals for probabilities.
The first row in figure 14.8 with FC = £10,000 represents cumulative probabilities from 0 to 0.1, the second row cumulative probabilities from 0.1 to 0.35, and so on. The width of the first interval is then 0.1, the second interval 0.25, and so on. When we generate a random number between 0 and 1 and find the correct row with the interval where this random number belongs, the probability for choosing the first row is 0.1, the probability for choosing the second row is 0.25, etc. The number 0.9365 belongs for instance in the fourth row with the interval 0.85–0.95 and FC = £40,000. In this way we can choose an FC corresponding to a random number and obtain the correct probabilities for choosing the different values for FC.
The procedure we suggest here can be accomplished by the VLOOKUP function. (See figure 14.9.) In this case we find the table from figure 14.8 in the cells F9:G13. In cell E5 a random number between 0 and 1 is generated with the RAND function. The VLOOKUP function is placed in cell E6. In the dialogue box for VLOOKUP we choose the random number in E5 as Lookup_value and the table in F9:G13 as Table_array. VLOOKUP uses the Lookup_value to search in the first column of Table_array for the correct row. Col_index_num specifies which column the solution of the function should be returned from. In our example, that would be column 2. In figures 14.3 and 14.9 Lookup_value is 0.9365. Then the function will look up the row (vertical lookup) starting with 0.85 (closest number rounded down), and return 40,000.
Figure 14.9 The VLOOKUP function in Excel.
If probabilities and corresponding values are presented in rows instead of columns, we must use the HLOOKUP function instead of VLOOKUP.
Our simulation results in an operating income of (with all the decimals from the spreadsheet):
(16.62 – 3.00)5,714 – 40,000 = 37,802
A simulation needs to be repeated several times (maybe 1,000 or more) to obtain useful results. For simple cases, this can be done by repeating the simulation in new rows in the spreadsheet. Anyway, simulations are often so complicated that more efficient methods are required. One method is to collect the results from the repeated simulations in a data table.
In our example each simulation results in an operating income in cell I4 (cf. figure 14.3). In the spreadsheet we have carried out 1,000 simulations and collected the results in the one-dimensional data table in figure 14.10 where the rows 21–1017 are hidden. A data table must include a formula for calculating the values that should be put into the table. In our example the formula “=I4” is written in cell B18, since we want to put the operating incomes from repeated simulations into the table. The numbers 1–1,000 in the cells A19:A1018 are written to keep track of the number of simulations and are strictly not necessary in this case. The column on the left side of a data table often contains values used for calculating the contents of the table. Anyway, in our example the cells A19:A1018 are not used for any calculations. The formula in cell B18 is only used for transferring values from cell I4 to the data table in the cells B19:B1018.
Figure 14.10 Data table.
To create the data table we first define the area for the table by marking the cells A18:B1018, and then we choose the tab Data and What-If Analysis and Data Table. Then the dialogue box in figure 14.11 appears. In this case with a one-dimensional table with only one column, we leave Row input cell open. As Column input cell we usually state a cell address that represents the values from the column to the left of the data table. Anyway, in our example the contents of the table are not calculated from the values in the column to the left of the data table. We just want to put repeated values from cell I4 into the table. Therefore we state an address to an empty cell, for instance A17, as Column input cell so that this cell works as a dummy variable. The data table is generated when we push OK.
Figure 14.11 Dialogue box for data table.
The simulated operating incomes need to be systemized to give a useful presentation. To do this we can generate a frequency table, i.e. to count the number of results that fall into different intervals. To find appropriate intervals for our data, we can first use the MIN and MAX functions to find the smallest and largest values in the data table. In our example the operating incomes vary between £0 and £100,000, and we choose intervals that have a width of £10,000. The cell E19 in figure 14.13 represents the interval up to £10,000, cell E20 the interval £10,000–£20,000, and so on.
To make the frequency table in the cells F19:F28 in figure 14.13 we click cell F19 and choose the FREQUENCY function so that the dialogue box in figure 14.12 appears. As Data_array we choose the simulated operating incomes in the cells B19:B1018, and as Bins_array the intervals in the cells E19:E28. When pushing OK a single value will appear in cell F19. Anyway, the FREQUENCY function must be entered as an array formula. Therefore we mark the cells F19:F28, push F2 and after that Ctrl+Shift+Enter. Then the frequency table appears as shown in cells F19:F28 in figure 14.13.
Figure 14.12 The FREQUENCY function dialogue box.
In figure 14.13 we have also made two bar charts for the results. In cells G19:G28 we have typed the labels 0–10, 10–20, etc. (in £000) for the horizontal axis. The values on the vertical axis in the upper bar chart are the contents of the frequency table. Probabilities for the different intervals are also estimated in the cells H19:H28 as 0/1,000 = 0.00, and so on. The lower bar chart in figure 14.13 illustrates these probabilities. The bar charts tell us that the most probable interval for the operating income is £50,000–£60,000. We can also estimate the probability for the operating income to be between £40,000 and £60,000 to 0.288 + 0.328 = 0.616. Remember that all calculations are repeated so that new bar charts appear every time we push F9.
Figure 14.13 Frequency table and bar charts.
The simulation in example 14.1 can be carried out with the data analysis tool in Excel, which is able to generate values arbitrarily from specified probability distributions. Such simulations cannot be repeated by pushing F9. With this method we need to repeat the entire procedure (programming of dialogue boxes etc.) to perform for instance 1,000 new simulations. Anyway, we will now perform a simulation similar to the one in example 14.1 using the data analysis tool. The results are shown in figure 14.14.
Figure 14.14 Simulation with the data analysis tool in Excel.
We repeat that the operating income per year was calculated as (p – vuc)n – FC. The number of units sold per year (n) follows a uniform distribution, the price (p) follows a normal distribution, the variable unit cost (vuc) follows a binomial distribution, and the fixed costs per year (FC) follow a discrete distribution. This information is given in the cells B3:F10 in the spreadsheet in figure 14.14. The 1,000 simulated values for n, p, vuc and FC are written in the columns B–E starting in row 14. From these values operating incomes are calculated in column F.
To generate values arbitrarily from the given probability distributions we choose the tab Data and Data Analysis. A dialogue box appears where we choose Random Number Generation. Then the dialogue box in figure 14.15 appears.
For each new and different variable n, p, vuc and FC, we set Number of Variables to 1 and Number of Random Numbers to 1,000 to simulate 1,000 values for the variable in question.
To simulate values for n we choose Uniform as Distribution and write in the parameters 5,500 and 6,000. When we set Output Range to $B$14, we get 1,000 arbitrary values generated from the specified uniform distribution in the cells B14:B1013. (See figure 14.14.)
To simulate values for p, vuc and FC, we repeat the procedure and choose the appropriate distribution for each variable. The values for the operating income are calculated in the cells F14:F1013.
Figure 14.15 Dialogue box for Random Number Generation.
To systemize the simulated operating incomes we may use the FREQUENCY function as we did in example 14.1. Alternatively, we can choose Histogram in the Data Analysis dialogue box so that the dialogue box in figure 14.16 appears. As Input Range we choose F14:F1013, and as Bin Range we choose some cells where we have written 10,000, 20,000, etc. for the intervals 0–10,000, 10,000–20,000, etc. If we set Output Range to H3 and press OK, we get the frequency table in the cells H3:I14 in figure 14.14. Be aware that this frequency table will not be updated if new data are written to the cells F14:F1013.
Figure 14.16 The histogram dialogue box.
Based on the frequency table in the cells I4:I13, a bar chart has been drawn in figure 14.14. We see that the bar charts from the two simulations in figures 14.13 and 14.14 look pretty much the same (as expected).
In example 13.3 we calculated the value of a given call option by a binomial model with four periods and got the result $18.01.
In the spreadsheet in figure 14.17 the value of the same option has been calculated by a simulation model using the same binomial model with four periods.
Figure 14.17 Simulation of a call option’s value.
For the first period we simulate in cell B36 whether the value of the stock will increase (1) or decrease (0). The formula in cell B36 generates a random number between 0 and 1. If this number is below the risk-neutral probability q in cell E7, cell B36 is set to 1. If not, cell B36 is set to 0. Thus the probability to get 1 in cell B36 is q and to get 0 is 1 – q. Similar simulations are performed for the periods 2–4 in cells B37:B39. The number of increases is summed in cell G36, and the stock value on the settlement date:
is calculated in cell G37 by the formula 
. In figure 14.17 this stock value is calculated to:
On the settlement date the value of the call option is S – X if S > X; otherwise the value is 0. This value is calculated in cell G38 with the formula MAX(G37–$B$4;0).
The cells C41:D1041 contain a data table that registers 1,000 simulations for the call’s value at the settlement date (from cell G38). The cell D1043 contains the average of the 1,000 simulated vales, and in cell C1045 this average is discounted over four periods so that we get the present value of the call, $18.08.
It is straightforward to expand this model to 365 periods, which is done in the spreadsheet in figure 14.18. (The interest, u and d have been adjusted.) This is a more realistic model since stock prices change every day.
Figure 14.18 Simulation of a call option’s value.
Problems
14-1. |
A variable x depends on the three variables a, b and c: x = a · b + c. The variable a can take any value between 10 and 15 with the same probability. The variable b follows a normal distribution with μ = 1.2 and σ = 0.15. The variable c can take one of the four values 1, 2, 3 or 4 with the probabilities: P (b = 1) = 0.05 P (b = 2) = 0.3 P (b = 3) = 0.45 P (b = 4) = 0.20 Run 1,000 simulations for the variable x in Excel, and present the results with a bar chart. |
14-2. |
Simulate the sum when you throw 12 dice. Run 3,000 simulations and present the results as a discrete probability distribution. Can you say anything about the shape of your distribution? |
The company OLA Inc. wants to start production and sale of a new product from January next year. It makes the following assumptions about the project. • The product’s price is constant and equal to $750 per unit. • The number of units sold per month follows a normal distribution with μ = 1,500 and σ = 700. • 20–50 per cent of the payments will come in the same month as the sale. The rest will come the next month. • Material cost per unit in a given month will be $320 if sales are less than 800 units that month, $290 if the sales are between 800 and 1,200 units, $270 if the sales are between 1,200 and 1,600 units, $250 if the sales are between 1,600 and 2,000 units, and $235 if the sales are more than 2,000 units. • OLA Inc. has a supplier credit time of 10 days. • Variable cost that comes in addition to material costs is 12 per cent of the monthly sales. • Fixed cost per month is $200,000 if the sales are less than 1,700 units. Otherwise fixed cost per month is $250,000. • Wage cost is $300,000 per month. From May the wage cost will increase by 1, 2, 3, 4, 5, 6 or 7 per cent. The probabilities for these outcomes are defined by a binomial distribution with p = 0.55. • Taxes are paid with $75,000 in June and $85,000 in December. • OLA Inc. has a cash credit loan whereby it pays 1.2 per cent interest of the month’s average negative balance at the end of each month. For the part of the average negative balance that is below –$500,000, it must pay 2.5 per cent interest. If the average balance is positive, it receives 0.3 per cent interest. Simulate a yearly cash budget for OLA Inc. 1,000 times and calculate the probability of a liquidity surplus after one year. |
|
14-4. |
A manufacturer plans to develop a new motorbike under the following assumptions. • Development costs will be $800,000,000. These costs are depreciated linearly over the project lifetime, which is five years. The company pays 35 per cent tax and has a required rate of return of 12 per cent. • The contribution margin per motorbike is $3,000 the first year, and then decreases by 3 per cent each year. • The first year the sales are assumed to follow a binomial distribution with p = 0.55 and the eight different sale figures 80,000, 90,000, …, 150,000. For consecutive years, the sales are assumed to drop every year by 5, 7 or 10 per cent. The corresponding probabilities are:
Simulate net present value for the project and draw a probability distribution for the net present value based on your simulations. |
14-5. |
Prices for a stock have been observed, and the following probability estimates are made for the change in stock price from one day to the next. (The probabilities follow a binomial distribution with n = 7 and p = 0.55.)
This means for instance that the probability of an increase in stock price of $0.5 is 0.29185. Today’s stock price is $122. Simulate the stock price in 30 days. Make 500 repetitions and illustrate the results graphically. |
14-6. |
Customers in a shop arrive with intervals for which the following probabilities are estimated:
The shop has one sales assistant with varying service time. The following probabilities have been estimated for different service times:
Simulate total waiting time for 30 consecutive customers. Make 500 repetitions and illustrate the results graphically. |
