Using Data Tables for Simulations

Last year, or maybe it was two years ago, I tried to go through this simulation book, Excel Simulations by Dr. Gerard M. Verschuuren, and blog at the same time about it, but it ended up being too much. I was trying to come up with good examples to use with the simulation exercises and then write about it every week and also come up with a decent picture to go with the post. I just couldn’t keep up because I had to find examples, and coming up with a decent picture turned out to be extremely taxing on my creativity.

So, this time, I’m going to try to go through this book but not add the stress of trying to write a post about the examples (and also eliminate the need to create a compelling picture (ha!)). I decided to start at the beginning because there were some interesting things I wanted to refresh my memory. One of them was using data tables to do Monte Carlo simulation, or I think that is what that is. The data tables was being used to create multiple simulations and I think that’s what Monte Carlo basically is.

The last time I went through the simulation examples in the book, I couldn’t understand how the data table was working when doing the Monte Carlo method. But now, going through it the second 

time, I’m starting to get it. So in the interest of understanding data tables better, especially the multiple simulations part, I’m going to try to explain data tables, from the simple use to the multiple simulations.

At the end of this post, there is a sample file that you can use to follow along in the rest of the post.

First there is a single variable data table. You may have multiple variables but only one variable will be changing, the rest will remain the same for each change in variable. You don’t want to use data table for something simple because I believe data tables uses a lot of calculation memory. Reserve data tables for complicated calculations. In the example in the attached file, tab “Simple example”, I am using a very simple example which would really be better served by formulas, such as that shown in cells E14:E33, in the attached Excel file.

Data tables are found under the Data tab, What-If Analysis. For a single variable, you would choose either a column or row input, not both. Before getting into the Data tab, you should highlight the area of where your data table will be. In my file, I used a thick border to show where I set the data table. The yellow shaded areas are the values for the changing variable x that I want to calculate, the green shaded area is the formula and the blue area is where the input variables are. For the column input for the single data table, I used cell F8 but I could have used cell B14. For 2 variable data tables, I could not use cells J13 and I14, I had to denote cells outside of the data table. So that is a distinction between single and 2 variable data table.

I won’t go further into the mechanics of setting up either single or 2 variable data tables because there are a lot of sites that explains very well how to do the set up. Just realize how and where I set up the formulas (green shaded cells), what I used as changing variables (yellow shaded cells), which cells were used for the input column and row (blue shaded cells) and where I set the data table (the thick border).

The other tab, “Multiple simulations”, provides another use of data table for simulations. This is just a simple example of using a RAND function to generate a number between 0 and 1 and then either adding 1 or subtracting 1. Cells B9 through B28 shows the formulas. In this example I chose to have 20 runs. This single trial of 20 runs has a maximum, minimum and end score, calculated in cells B31 to B33. Now what if you want to do multiple trials of 20 runs and see what kind of results you get. This is like running multiple simulations of runs. This is a form of testing to see what kind of results and the averages you get. Again, I marked in green where the formulas go, in blue what will be the column input (note, it is an empty cell and it could be any empty cell outside of the data table), and what are the changing variables (shaded in yellow).

This example is like a single variable data table except it had 3 formulas instead of 1, but basically, only one variable is changing. That variable though is the number created by the RAND formula. It’s not like the x or y variable in the “Simple example” tab. This use of the data table works as long as you are using either RAND or RANDBETWEEN because those formulas calculate each time you hit F9. In the “Simple example” tab, hit F9 and the numbers don’t change.

Below is the example file where I have an example of a simulation using data tables: