100 Excel Simulations

Using Excel to Model Risk, Investments, Genetics, Growth, Gambling and Monte Carlo Analysis

Gerard M. Verschuuren

• Editorial: Holy Macro! Books
• Año: 2016
• ISBN:
  9781615473571
• Edición: 1
• Categoría: Informatique
• Categoría: Applications de bureau

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook. The simulations covered offer a fun alternative to the usual Excel topics and include situations such as roulette, password cracking, sex determination, population growth, and traffic patterns, among many others.

0. Gambling
   Chapter 1 The Die Is Cast
   All these simulations are supported by files that you can download from the following website:
   http://www.genesispc.com/download/simulations100.zip.
   What the simulation does
   We start with a very simple case of simulation—casting a die (on sheet “Dice” in 1-Gambling.xlsx). In cell A1 is a formula that generates a random number between 1 and 6. According to that outcome, the colored die shows the appropriate number of eyes at their proper locations. Each time the random number changes, the die adjusts accordingly.
   
   What you need to know
   Cell A1 has a formula that uses a volatile function called RAND. On each recalculation, this function generates a new random number between 0 and 1. Because we want numbers between 1 and 6, we need to multiply by 6, round the number down by using the INT function, and then add 1 to the end result. More in general: =INT((high-low+1)*RAND()+low).
   Users of Excel 2007 and later can also use the “easier” function RANDBETWEEN which has two arguments for the lower limit (in this case 1) and the upper limit (in this case 6). I decided not to use that function, because in pre-2007 Excel versions this function was only available through the Analysis Toolpak.
   To generate a new random number, you either hit the key F9 or the combination of the Shift key and the F9 key. In this file, I would recommend the latter option (Shift F9), since that would only recalculate the current sheet—otherwise you would recalculate all sheets in this file, which may take lots of calculating time.
   Finally, we need to regulate which eyes should pop up for each new random number. This is done inside some of the die cells by using the IF function. This function is a “decision maker,” which determines whether a specific eye should be on or off.
   What you need to do

1. Type in cell A1: =INT(RAND()*6) + 1. In this case, the function RAND is “nested” inside the function INT (INT eliminates decimals). Nested functions are very common in Excel; for more information, see Appendix 2.
2. Type in B3: =IF(A1>1,0,””). The two double quotes in the last argument return an empty string, showing up as nothing.
3. Type in D3: =IF(A1>3,0,””).
4. Type in B5: =IF(A1=6,0,””).
5. Type in D5: =IF(A1=6,0,””).
6. Type in B7: =IF(A1>3,0,””).
7. Type in D7: =IF(A1>1,0,””).
8. Type in C5: =IF(OR(A1=1,A1=3,A1=5),0,””). In this case, the function OR is nested inside IF. The function OR returns “true” if any of the enclosed arguments is “true.”
9. If you want to see all formulas at once, hit Ctr ~ (the tilde can be found below the Esc key). This shortcut toggles the sheet, back and forth, between value-view and formula-view.

Chapter 2 Casting Six Dice

What the simulation does

Open file 1-Gambling.xlsx on sheet “6-Dices.” This time we have six different dice. Each die “listens” to a random number above it, to its left.

The settings for each die are similar to what we did in Simulation 1. The number of eyes for each die is plotted in a column chart below the dice.

A die that shows six eyes gets marked with a color. When there are at least 2 dice in a row with six eyes, all dice get marked at the same time.

What you need to know

There is not much new on this sheet. The main difference is that we need 6 different cells with a RAND function in order to control the...