100 Excel Simulations E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 247

Veröffentlichungsjahr: 2024

Beschreibung

This book dives deep into the art of creating realistic simulations using Excel. Starting with simple concepts like dice rolls and roulette, it gradually introduces advanced scenarios in genetics, finance, and statistics. Each example is designed to help readers understand the principles of modeling and problem-solving with step-by-step guidance.
Readers will explore Monte Carlo simulations, hypothesis testing, and iterative techniques to tackle real-world challenges. The book covers diverse applications, such as risk analysis, DNA sequencing, and stock market predictions. It emphasizes practical approaches that make complex techniques accessible, even for users with basic Excel skills.
By the end, readers will have hands-on experience designing and testing custom simulations for various fields. Whether analyzing traffic patterns, forecasting market trends, or studying population dynamics, this book equips users with the tools and confidence to address multifaceted problems effectively.

Leseprobe

100 Excel Simulations

Dr. Gerard M. Verschuuren

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

100 Excel Simulations

(c) 2017 Gerard M. Verschuuren

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Author: Dr. Gerard M. Verschuuren

Cover Design: Shannon Travise

Layout: Bill Jelen

Published by: Holy Macro! Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953USA

Printed in USA

ISBN 978-1-61547-048-8 (Print)

978-1-61547-134-8 (Mobi)

978-1-61547-234-5 (PDF)

978-1-61547-357-1 (ePub)

LCCN 2016938256

Preface

This book is about simulations and how to create them in Excel. Simulations are basically a form of what-if-analysis. With simulations we can see what the impact is of changing certain input cells. The model behind simulations either works with a series of fixed values or with “uncertain” variables that show a random, normal variation. The latter kinds of simulations are not as deterministic as the former kind; they are sometimes called Monte Carlo simulations, which use a sample probability distribution for each variable to produce hundreds or thousands of possible outcomes.

No matter whether you are dealing with scientific issues, financial issues, statistical issues, logistic issues, or just entertaining puzzles, simulations may give you the answers you so badly need, but could not solve without Excel’s calculating power. Excel can help us to put a meaningful numerical value on things we do not know with certainty.

On purpose, I used only functions and tools that Excel provides. In other words, you do not have to learn how to program in Excel by using Visual Basic for Applications (VBA). I intentionally left VBA out. If you like to learn how to do your work in Excel with the extra power of VBA, I refer you to other learning tools that I developed in the past few years for Mr. Excel (http://www.mrexcel.com/microsoft-office-visual-learning.html).

Each chapter covers one specific simulation and has three small segments: 1. What the simulation does. 2. What you need to know. 3. What you need to do. In the larger, main sections, I focus on specific areas of interest, such as statistics or genetics. Even if these are not your area of interest, you will most likely still gain much benefit from studying those sections. At least give them a chance. And besides, using simulations is also just fun.

This book is an expanded version of the book Simulations published in 2014. It has 20 more powerful simulations added to the existing 80 ones published before. It is a rich and diverse selection that will give you many ideas to create your own simulations. Simulations are often a necessity, and yet they are fun at the same time. Enjoy!

Instructions

All these simulations are supported by files that you can download from the following website:

http://www.genesispc.com/download/simulations100.zip.

This download contains a set of files that you can use to do your own work by following the instructions in this book—plus a second set of files that contain the final solutions. The finished files have their formulas protected; if you wish, you can turn protection OFF with Review | Unprotect Sheet.

I assume that you are already familiar with many Excel features, including graphs or charts. If not, I would refer you to some learning tools that you can find on www.mrexcel.com/microsoft-office-visual-learning.html. In this book, however, I will only explain in more detail those features that you may not be familiar with yet and that are very helpful when you do what-if-analysis with simulations.

All these simulations are supported by files that you can download from the following website:

http://www.genesispc.com/download/simulations100.zip.

This download contains a set of files that you can use to do your own work by following the instructions in this book—plus a second set of files that contain the final solutions. The finished files have their formulas protected; if you wish, you can turn protection OFF with Review | Unprotect Sheet.

I assume that you are already familiar with many Excel features, including graphs or charts. If not, I would refer you to some learning tools that you can find on www.mrexcel.com/microsoft-office-visual-learning.html. In this book, however, I will only explain in more detail those features that you may not be familiar with yet and that are very helpful when you do what-if-analysis with simulations.

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

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.Type in B3: =IF(A1>1,0,””). The two double quotes in the last argument return an empty string, showing up as nothing.Type in D3: =IF(A1>3,0,””).Type in B5: =IF(A1=6,0,””).Type in D5: =IF(A1=6,0,””).Type in B7: =IF(A1>3,0,””).Type in D7: =IF(A1>1,0,””).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.”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 six die displays. Each die has the same structure as the one used in Simulation 1.

In addition, we use conditional formatting to change colors of the dice when they show six eyes, or contain at least two dice with six eyes.

What you need to do

Make sure all six dice are set up as was done in Simulation 1, but each die is connected to the random cell just above it.Select range A1:C7 first, then Home | Conditional Format | Formula: =$A$1=6.Do something similar for the other five dice.Finally select A3:W7 (that is all six dice) and format them conditionally: =COUNTIF($A$1:$U$1,6)>=2.By using (Sh) F9, you may hit a situation like below where at least two dice have six eyes (F9 recalculates all the sheets of the entire file, whereas ShF9 only does so for the current sheet and may take less time).

Chapter 3 Frequencies

What the simulation does

Open file 1-Gambling.xlsx on sheet “Frequencies.” This time we cast two dice at once and sum the number of eyes in column C; we repeat this process 9 more times. In column F, we calculate how often we had a hit of 2 eyes in total, 3 eyes, and so on, up to 12 eyes.

The frequencies are plotted in a graph. Cell F14 calculates the average of column C. It turns color for extreme values. The average is also plotted in the graph as a vertical line—based on the two sets of coordinates shown in E16:F17.

The curve keeps changing each time we hit Shift F9. Very rarely does it come close to a normal distribution with a mean somewhere in the center. The chance for this to happen would increase if we would have used more dice and more repeats.

What you need to know

On order to calculate frequencies, we need the function FREQUENCY. This is a so-called array function (more on this in Simulations 62 and 63). Such functions return an array or require an array for intermediate calculations. All array functions have to be implemented with three keys at the same time: Ctr Sh Enter.

The function FREQUENCY returns an array of multiple answers based on a set of “bins.” In this case, the bins are in column E. The function “reads” the bins as follows: 2 covers all cases up to and including 2, 3 covers all cases >2 and <=3, etc. To make this function work, you need to select all the cells that are going to hold the frequency values all at once, before you use the array function. Once the array function has been implemented with Ctr Sh Enter, you will see the formula in the formula bar surrounded by braces—like this: {=FREQUENCY(…,…)}. Do not type the braces; they come automatically with Ctr Sh Enter.

Changing colors of cells under certain conditions is done with so-called conditional formatting (located under the Home tab). When the specified conditions kick in, the cell will be formatted according to certain settings. In our case, we want to flag averages under 5.5 and over 8.5, which requires an OR function.

What you need to do

Column C sums the eyes of both dice.Select the entire range F2:F12 first, before you implement =FREQUENCY(C2:C11,E2:E12), and then hit Ctr Sh Enter. Notice the braces in the formula bar.Type in cell F14: =AVERAGE(C2:C11).Select cell F14, Home | Conditional Formatting | New Rule | Use a Formula | =OR($F$14<5.5,$F$14>8.5) | Format color.The average line in the graph is based on a new series of values with two sets of coordinates: E16:E17 for the X-values, and F16:F17 for the Y-values.Hit Sh F9 for new simulations. As they say, “Results may vary” (see below).

Chapter 4 Roulette Machine

What the simulation does

Open file 1-Gambling.xlsx on sheet “Roulette.” Column A simulates a roulette with 1,000 random numbers between 1 and 36. In column B, you type 1 if you expect the next number to be odd—otherwise 2 for even.

Column E keeps the score: it adds 1, when your prediction was correct—otherwise it subtracts 1. Once you are finished, you can just empty your predictions in B2:B1001—and start all over (you may need Sh F9, though).

What you need to know

Most people believe that if they keep consistently betting “odd,” the ball will most certainly land on an odd number sometime soon. This is called “the law of averages” which says, the longer you wait for a certain random event, the more likely it becomes.

Do not believe it! Try it out in this “real life” simulation and find out how the casino makes money on people who think that way. You may initially gain but eventually lose.

What you need to do

Column A has 1,000 random numbers. They were once generated and then changed into values. You will not see all of them because 999 of them are hidden through conditional formatting.Cell E2 has this formula: =IF(MOD(A3,2)=B2,E1+1,E1-1). Copy the formula down to E1001. The MOD function divides a number by 2 and returns the remainder. The remainder is either 0 or 1 here. If what the user had predicted in column B is correct, the score goes up by 1—otherwise down by 1.Select range A3:A1001 and apply the following conditional formatting formula: =$B2=””. Notice that B is locked but 2 is not (see Appendix 1 on this issue). Format the entire range to a white font—which means you will not see this number if the cell in the next column of the previous row is still empty, but it will show its value once that cell has been filled with either 1 (odd) or 2 (even).Validate range B2:B1001 with this custom formula: =OR($B2=1,$B2=2). So the cell accepts only 1s or 2s.Now you can make your predictions for every next roulette outcome—either 1 or 2. You may have to hit Sh F9 each time if the file has been set to manual calculation (or change that).To start all over, just clear the colored cells in column B (and again you may have to manually recalculate the sheet).Notice how easily you can lose by going for the “law of averages” by repeating constantly 1 for “odd” or 2 for “even.”

Chapter 5 Gambler’s Ruin

What the simulation does

Open file 1-Gambling.xlsx on sheet “Addiction.” This sheet simulates what may happen to people who are addicted to gambling.

The player has 100 chances (in column A) to go for odd or even. We simulate a 50% probability for either choice. If the choice was correct, the count in column A goes up by 1, otherwise it goes down by 1.

Next we simulate that this addicted player repeats the game for some twenty more times. For each game, we calculate average, minimum, maximum, standard deviation, and the final score (in column H). At the end, we calculate how often the player had a positive final score, and how often a negative one. This looks like much more work than it actually is…

What you need to know

In column A, we use the RAND function for each individual choice the player makes. Then we simulate doing this 20 more times in the right table. To do so, we use Excel’s Data Table tool (see more details in Appendix 3). I consider this an ideal tool for what-if analysis.

How does it work? Usually Data Tables have a formula in the first cell—which would be cell C1 in our case. Based on that formula, a Data Table typically uses a row input of variables and a column input of variables to recalculate the formula placed at its origin. It does so by filling the table cells with a formula that has the following syntax: =TABLE(row-input, col-input).

In this case we use a what-if table merely to trick Excel into simulating 20 (or many more) iterations of column A. We do so by not placing a formula at the origin, by leaving the row-input argument empty, and having the col-input argument refer to an empty cell somewhere outside the table. Yes, that does the trick!

What you need to do

Place in cell A2: =IF(RAND()>0.5,A1+1,A1-1).Copy this formula down for 100 rows.Place in cell D2: =AVERAGE(A:A). Do something similar with MAX (in E2), MIN (in F2), and STDEV (in G2).Place in cell H2 a reference to the last cell in column A.Now select C2:H22 (yes, the empty cell C2, not D2).Start the table: Data | What-If Analysis | Data Table.Set the row input to nothing and the column input to an empty cell outside the table (say, J2).This automatically places the following formula in the range D2:H22 (yes, D2 this time): {=TABLE(,J2)}. Do not type this formula or the braces—both kick in automatically.# of negative scores in H24: =COUNTIF($H$2:$H$22,”<0”).# of positive scores in H25: =COUNTIF($H$2:$H$22,”>0”).Select H2:H22: Home | Conditional Formatting | Data Bars (the last option is not available in pre-2007). Notice how the Data Table runs 20 x 100 choices each time you hit the keys ShF9. That’s what addiction does!

Chapter 6 Random Walk

What the simulation does

Open file 1-Gambling.xlsx on sheet “RandomWalk.” Another way of looking at the situation discussed in Simulation 5 is a random-walk approach. As we leave home (position 0), if we flip a coin and get heads, we go one block north (position +1); if we flip tails, we go one block south (position -1). We keep doing this many times and then check how far we end up being from home. (We may also ask what the probability is that we return to where we started—believe it or not, 100% for long, long walks.)

We will simulate first 50 steps for one dimension (north-south, in column B, plotted in the top graph as up and down). Then we will do this for two dimensions (north-east-south-west, in columns B and C, plotted in the bottom graph). As it turns out, we could make big “gains” and drift far away from where we started.

But not always! In the table to the right, we repeated all 50 steps 14 times. If this random-walk were interpreted as a case of gambling, we could encounter many negative, perhaps even huge outcomes—“losses” in gambling terms.

What you need to know

We are going to use a few new functions. First we want to find out in which row position in column B, we did reach position 0 again. To do so, we need the function MATCH to locate in which row the first 0 was found. MATCH has 3 arguments: what to match, in which range, and with which match type (0 for an exact match, 1 for an ascending list, -1 for a descending list).

If MATCH can’t find “its match,” we get an error. To avoid this, we can use the function IFERROR which allows us to specify in its second argument what to display when there is an error.

In addition to COUNTIF, there is also a function COUNTIFS (missing in pre-2007), which allows for multiple count criteria.

What you need to do

Place in cell B3: =IF(RAND()<0.5,B2-1,B2+1). Copy this formula down to cell B1007. RAND determines whether we go one down or one up. This populates the top graph. In cell N1, we display how often the random walk got us back to 0: =COUNTIF(B3:B52,0) & “ times back to 0”. The ampersand (&) is an operator that “hooks” things together.In cell N3, we display in which row we got back to 0 for the first time: =”1st time back to 0 is in trial “ & IFERROR(MATCH(0,B3:B52,0),”-”). Be aware that this is not Excel’s row number, but the run number (in column A).Now we make the random walk two-dimensional by placing in cell C3: =IF(RAND()<0.5,C2-1,C2+1). Copy this down.Now the bottom graph populates with 2-D random walks. (The start and end positions are marked differently).Select range N5:P18 for a Data Table with no row input and an empty cell (e.g. cell M5) for the column input. This creates the following array formula: =TABLE(,M5).In cell P20: =COUNTIFS(O5:O18,0,P5:P18,0). This counts how often the coordinates 0, 0 popped up in the Data Table.In cell P21: =COUNTIFS(O5:O18,”<0”,P5:P18,”<0”). This counts how often we ended up losing in both columns.In P22, we count how often we lost in one column: =SUM(COUNTIFS(O5:O18,”<0”,P5:P18,”>0”),COUNTIFS(O5:O18,”>0”,P5:P18,”<0”)).Apply Conditional Formatting to B2:B52: =$B$52<0. A loss.And to B2:C52: =AND($B$52=0,$C$52=0). Back to 0,0.

Chapter 7 Gambling Strategy

What the simulation does

Open file 1-Gambling.xlsx on sheet “Strategy.” Let’s pretend you are a persistent, but very systematic, gambler. You decide ahead of time how to spend your different kinds of banknotes, which is specified in range D1:E5. The left columns in the chart display these settings as well.

Then we let the machine determine one hundred times, in column A, when and which kind of banknotes to use and in which order. This is a random process, but within the margins set in D1:E5. The results are shown in the right columns of the chart.

Although the process is random, it follows a discrete distribution which comes always very close to what you would expect.

What you need to know

In order to let column A determine which banknote to choose, we call the function RAND to create a random percentage between 0 and 1 (0% and 100%). Then the function VLOOKUP uses this percentage to find the corresponding type of banknote.

However, VLOOKUP always searches vertically, from top to bottom, in the first column of a table and then finds a corresponding value in a column to the right, specified by a number. So we need a lookup column before D1:D5 in order to determine the type of banknote to use. Besides, VLOOKUP looks for the previous value in an ascending order, so it would find $1 for all percentages between 0% and 60%, $5 between 60% and 80%, and $100 for percentages greater than or equal to 98%.

So, we need cumulative totals in the first column shown below, starting at 0%. The third column is now redundant, but is still needed for the chart to the right in order to show the expected frequencies—versus the randomly generated frequencies.

What you need to do

Place in cell C1 the number 0 (or 0%).Place in cell C2: =SUM($E$1:E1). This is a combination of an absolute (“locked”) and a relative (“unlocked”) cell reference. (See Appendix 1 for more information.)Copy this formula down to E5. This creates cumulative totals.Place in cell A1: =VLOOKUP(RAND(),$C$1:$D$5,2). Copy this formula down to cell A100. This formula does the random work in accordance with a frequency table (C1:D5), where it finds the corresponding type of banknote in column 2 (specified by the last argument in the function).In order to calculate the actual frequencies for the various banknotes, we use the array function FREQUENCY in cells E8:E12: =FREQUENCY(A1:A100,D8:D12)/100. Make sure you select E8:E12 ahead of time, place the formula, and then accept this formula with Ctr Sh Enter. (For more info on array formulas see Simulation 65).Each time you hit (Sh) F9, column A updates, as do the right columns in the chart. Notice how close you stay to your preset expectations—but “results may vary,” as the saying goes.

Chapter 8 Cracking a Password

What the simulation does

Open file 1-Gambling.xlsx on sheet “Password.” This is not a real password cracker, of course—that would at least require VBA code. But we can still mimic part of the process. Let us assume that the password is “pass.” This is a 4-letter word, so if we only use the characters a-z (no capitals), then we would still have 26^4 possible combinations—which amounts to 456,976.

Since Excel 2003 has only 65536 rows, we will limit ourselves to that number of rows. This means of course that we may not hit the right combination in one run. Increasing the number of rows would increase our chances but also our calculation time.

In the example below, we were “lucky enough” to find one matching combination in row 33596.

What you need to know

There is a function called CHAR which returns the character that comes with a certain asci number. The numbers 97 through 122 represent the characters a through z. (To find out what the asci number of a certain key is, you could use the function CODE; for instance, CODE(“a”) would give you the number 97.)

Now we should be able to generate random numbers between 97 and 122 by using either RANDBETWEEN(97,122) or ROUND(RAND()*(122-97)+97,0).

In addition, we need the ampersand operator (&) which hooks characters together—four in a row, this time. Instead you could use the function CONCATENATE.

We also need the function MATCH again to locate in which row the match was found. MATCH has 3 arguments: what to match, in which range, and with which match type (0 for an exact match, 1 for an ascending list, -1 for a descending list).

What you need to do

The cells A1:A65536 hold the following formula: =CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122)).Cell D11 counts the number of matches: =COUNTIF(A:A,D4).Cell D12 finds the row position of the match: =MATCH(D4,A:A,0).Place in cell E12: =”Type A” & D12 & “ in the Name Box and hit Enter”.Very often no match will be found (see below); very seldom you may get two, or even more, matches.