Chapter 9 Excel Tutorial Script

(Ch.9) 1

Chapter 9 Excel Tutorial Script

Welcome to Chapter 9, Monte Carlo Simulation
After completing this tutorial you should be able to create a Monte Carlo Simulation in Excel and begin building your own Monte Carlo simulations applied to a wide range of business problems
Now that you have an introduction to the basics of Monte Carlo simulation from part one of this tutorial, we will simulate the tossing of a coin
Four different approaches will be discussed and used to simulate the tossing of a coin
After we run the simulation, we can draw some conclusions regarding the outcome of the process
For example, we might be interested in determining, for a single toss of the coin, what is the expected probability of getting “heads”. To determine this probability we have Excel repeat the simple coin toss many times, then calculate the percentage of those tosses that yield heads
For this tutorial refer to the Excel spreadsheet titled Monte Carlo Simulation or follow along in a blank Excel worksheet
As a preliminary setup, we recommend that you “freeze” the model you are about to create so that it doesn’t continue to recalculate every time we make a change. To do this go the formulas ribbon tab, then calculation options, and make sure the boxed label manual is checked. However, by doing this we will have to hit the F9 key to recalculate the formulas and run the simulation. Also, by doing this, we will need to reset the calculation option back to Automatic when we are done or it could create confusion in working with the other spreadsheets.
Note, if you are using the Monte Carlo Simulation spreadsheet document, refer to the worksheet tab labeled Coin Toss for the simulation approaches.
In, Approach 1 to simulating the tossing of coin, we will use the RANDBETWEEN() function
Under this approach, enter the formula =RANDBEWTEEN(0,1) in cell A6
Next, copy cell A6 one thousand times, which is filling cells A6 through A1005. To do this make sure cell A6 is highlighted, then press CTRL+C to copy its contents to the clipboard.
Next, hold down the SHIFT key while pressing page down repeatedly until the selected region reaches cell A1005. You may have to use the down arrow instead of the page down button when you get closer to cell A1005.
Once cells A7 through A1005 are highlighted press ENTER, and the contents of the clipboard will be pasted into the entire range.
Next, hit the F9 key to run the simulation.
After hitting the F9 key our formulas are updated and the simulation has been run.
In Approach 2, we will simple increase the number of iteration to increase accuracy
To do this we will use the same RANDBETWEEN() function as in Approach 1 and copy the formula from D6 through cell D2505
To do this, once again press CTRL+C to copy the formula contents to the clipboard.
Then hold the SHIFT key down and press the down page. You may need to use the arrows as you get closer to cell D2505. Then hit enter.
Again we will have to hit the F9 key to run the simulation.
After hitting the F9 key our formulas are again updated and the simulation is run
In Approach 3, we will use a Lookup table to simulate a coin toss
The previous two approaches are entirely appropriate for the simple coin-tossing experiment that we wanted to model. However, there are alternative approaches that can be used in Excel to simulate the running of models. These alternative approaches are particularly useful when working with more complex models.
To use the Lookup Table approach, we must first create a Lookup Table
We can do this by having a column labeled cumulative probability and a column labeled amount as seen on the screen. Enter 0 and .5 for the cumulative probability column and 0 and 1 for the amount column as seen on the screen
Now that we have our lookup table, we can use the Lookup function to simulate a coin toss
In cell G6 enter the following equation =VLOOKUP(RAND(),$H$3:$I$4, 2) (the Lookup number is random, Lookup table in the one we created, and the number two refers to the column index number we want to reference)
Now copy the equation to as many rows in the spreadsheet as the number of simulations you want to run.
If, as in Approach 1, we want to simulate 1,000 tosses of a coin, then the formula should be copied down to G1005. Thus our simulation range is G6 through G1005
To do this, once again press CTRL+C to copy the formula contents to the clipboard.
Then hold the SHIFT key down and press the down page. You may need to use the arrows as you get closer to cell G1005. Then hit enter.
Again we will have to hit the F9 key to run the simulation.
After hitting the F9 key our formulas are again updated and the simulation is run
The VLOOKUP() function will be discussed more in its own tutorial, tutorial 10
In Approach 4, our last approach in this example of tossing a coin, we will use the CHOOSE() function that is built into Excel.
To do this, enter the following formula in cell K6: =CHOOSE(INT(RAND()*10)+1,0,0,0,0,0,1,1,1,1,1)
This formula is saying that the integer function applied to the product of the random number generator and 10 will produce an integer value of 0 to 9. However, to us the CHOOSE function we need values corresponding to 1, 2, ….n. To show the generality of this approach, we use ten categories here, which allows us to capture probabilities for ten different values. We can have simplified the formula by using two categories.
Thus, for a converted random number that is equal to 1 through 5 (inclusive) the entered formula will assign a zero to the cell and for converted random integers 6 through 10, a value of 1 will be assigned
As in Approach 1 and 3 make 1,000 copies of the formula in cell K6. Are simulation range is K6 through K1005
To do this, once again press CTRL+C to copy the formula contents to the clipboard.
Then hold the SHIFT key down and press the down page. You may need to use the arrows as you get closer to cell K1005. Then hit enter.
Again we will have to hit the F9 key to run the simulation.
After hitting the F9 key our formulas are again updated and the simulation is run
We have now successfully created a Monte Carlo Simulation for the tossing of a coin using four approaches
We can calculate various statistics associated with simulation results we generated
If you are using the Monte Carlo Simulation template document, please refer to the worksheet labeled Analytics-1
To conduct such analysis it is best to have a clean “hard copy” of the data with no embedded formulas and no RAND() functions to bother with
To do this create a new worksheet label “Analytics-1” where you save the “hard copy” of the simulation results without any formulas embedded
After we have our hard copy of the data, we can use Excel’s build-in statistical functions to analyze the output of the simulations we ran
In this present case, we can apply the average, count, and countif functions
In addition to the statistical formulas, the percentile function is typically useful for analyzing simulation results
Your final analytics-1 page should look like the one that appears on the screen
Do not forget to rest you calculation option so you do not get confused in working with other spreadsheets
As a review to do this go to the formulas ribbon , then calculation options and make sure the automatic button is clicked
You have successfully created a Monte Carlo simulation in Excel
Having completed this tutorial you should now be able to create a Monte Carlo Simulation in Excel and begin building your own Monte Carlo simulations applied to a wide range of business problems
You have successfully completed Chapter 9, Monte Carlo Simulation, of the Excel Tutorial Series
For additional practice refer to the Self-Study Problem, Profit-Planning (CVP) Model at the end of Chapter 9