Today’s article is a guest post by my good friend Adam Buckingham. You can find him on Twitter (@AdamBuckingham). Adam has written some excellent sci-fi novels, posted about them on his blog (aegisys.blogspot.com), and is planning on bringing a game design to Protospiel-Milwaukee at the end of the month. Today he is sharing some amazing stuff about using Monte Carlo simulations within Microsoft Excel to help balance your game design. Thanks, Adam, for writing this article for all of us! Throughout the article editor’s notes are in italics.

You’ve just designed the coolest game ever conceptualized. You’ve done a few playtests, and as a result, you’ve started to see some possible signs of imbalance in the deck of cards. But you’re not sure if the problem is just an anomaly of the first couple of playtests, or a core issue with the game. If only you had the time to do 1,000 playtests to test the balance. Enter the Monte Carlo Method.

What is the Monte Carlo Method?

The famous Monte Carlo Casino

The Monte Carlo method is a method of statistical modeling that uses random factors to create a set of results that can then be analyzed similarly to how a statistical sample would be. The name is in honor of the creator’s uncle, who used to borrow lots of money to gamble at the Monte Carlo Casino in Monaco. The method came into common use on the Manhattan Project (the bomb thing… not the awesome worker placement game by Minion Games) and has become a standard in fields from physics to finance. The idea is that rather than try to calculate the probability of a series of random events blindly, just create a dataset you can analyze. If you can define the random elements, determine their constraints and associated probabilities, then you can run a large number (Typically 1000 or more) of simulations to determine the most likely result.

Editor’s Nerd Note: We use Monte Carlo analysis for determining the effect that neutrons have in nuclear fusion reactors. Neat, huh?

Games are a perfect (if quite simple) application of the method. Imagine a game that has a deck of 20 goal cards (e.g., Ticket to Ride). Each player will be dealt 3 of those cards. On these cards, there are a number of resource requirements and a point value. If we know the number of players and have a list of the cards, we can simulate the starting hands for the players by generating a random shuffle of the deck. Repeat a few hundred times, and you’ve got a picture of what the game will look like if dealt repeatedly. Taking it a step further, we can calculate the likely end score, average score per player, resource requirements, average winning margin if all players complete their goals, and a whole host of other statistics.

Sounds Magical, tell me more!

It is Magical! More on that later. Running a Monte Carlo Simulation can be resource intensive (Editor’s Note: this means it can swamp your computer). Luckily, most of us have a tool on our home computers that can run simple simulations really easily. That’s right, I’m talking about Microsoft Excel. Using Excel, it’s actually quite simple to run a simulation with thousands of iterations, and takes less than 10 minutes to set up if you know what you’re doing. Hopefully, this article will get you on your way.

I’ve created a sample file that you can follow along with. It’s located here:

https://docs.google.com/uc?export=download&id=0B7jhoEOs7yvtRkNVM2NCYWxvR0k

Depending on your machine, It may take a minute to calculate, but just hang in there. If it’s unbearable, you can change to Manual calculation: Formulas Tab > Calculation Options > Manual. Just remember that you need to press F9 when you want to calculate.

To start, I’ll give an overview of a few of the key functions you will use in creating the simulation. Note that some of these functions might not be available in versions of Excel before 2007.

Setting up the Simulation

Basic Card Design: Assigning “resources” to your cards.

Let’s go back to the earlier example of the 20 card deck with resources and points for each card. In order to create our Monte Carlo Model, we need to be able to create a single, randomly generated simulation of our game. Once we do that, we can repeat that setup 1,000 times to calculate our statistics.

The first step is to input all the card parameters into a table in excel. For each card, we need the number of points and the requirements for each resource. We’ll put this on the first tab of our workbook. You could add other elements to this table, but for now, we’ll keep it simple.

Next, we’ll create our simulation on a second tab in the same file. We need to create a deck shuffling engine. To do this, we’re going to generate a random value for each card, and then sort the cards based on that value. This will put them in a random order each time we calculate our spreadsheet.

Put the numbers “1” through “20” in column B to represent the 20 cards in your deck. In column A, we’re going to use our Rand function to generate random numbers. So type =Rand() next to the first card, and fill that down to card number 20. The result will look something like the picture to the left.

Why did we use Rand() instead of Randbetween(1,20)? You could use randbetween, but since each function result is truly random, it would be possible to get two results that are the same number. This will cause issues when sorting later. With rand, you get 15 digits, which means it’s very unlikely that there will be two values that are exactly the same.

Using Vlookup and Small will allow for correct sorting every time!

Speaking of Sorting, it’s time to sort these numbers by the randomly generated numbers. We can’t just sort the list, because the order will change every time we “shuffle” the deck. So we’re going to do it programmatically. To do this, we’re going to use a combination of vlookup and small. Remember that small returns the k^th smallest value in a list. So we are going to start by making a list from 1 to 20, representing the order of the cards in the deck. Put that in Column D. Then in E2, we’re going to use Small to return the smallest random value: =small(A:A,D2). That returns the smallest random number from column A. We’ll go a step further and return the card number from Column B using a Vlookup: =vlookup(small(A:A,D2),A:B,2,0). Fill that formula down and we’ve got a shuffled deck. Press F9 a few times to verify that it shuffles each time you calculate.

Now that the deck is shuffled, we’re almost there. We just need to deal 3 to each player. So we’ll create yet another table, with the 4 players listed across the top and the 3 cards dealt to each. P1 is dealt the 1^st, 5^th, and 9^th cards, etc… Make sure you use a formula to fill in these values (=E2, etc.). Below that, we’re going to calculate the resources each player needs and the points they earn. We’ll use a bunch of Vlookups to look these values up in the first table we built. We end up with a table that looks something like this:

So now we just need to repeat that 1000 times. Just keep pressing F9 over and over, and write down the results…

Actually, Let’s do it the Fancy Way!

To make this easier, we’re going to translate all those values above so that they show up in a single row of data. So P1 R1 | P1 R2 | P1 R3 | P1 Points | P2 R1| etc… and create formulas for each column that points to the appropriate cell in our table above. We’ll also add columns to the right that sum the total needs for each resource and total points. It should look like this (click to embiggen):

You can see I’ve labeled this row “Key” because this will serve as the key from which we will run our thousand simulations. Below the word Key, we’re going to number 1 to 1,000. (Quick tip, you can do this via the “Fill” button on the Home tab in Excel. Type 1 in the first cell, then select it and go to Fill>Series, then select columns, and a Stop value of 1000).

Now we’ll use the Magic of Excel to run 1,000 iterations of your simulation. We’ll use the Table tool for this. Start by selecting your key row (including the word Key), and dragging down to your last numbered row. So you should have a big selection of 1,000 rows and a bunch of columns. Now go to the Data Tab in Excel, and click What-If Analysis > Data Table:

Awesomeness approaches!

In the pop-up window, leave “Row Input Cell” blank, and for Column Input Cell, just select some empty cell. Maybe the cell directly above the word “Key,” it doesn’t matter, as long as the cell is completely empty, and is outside your table range.

Click OK, sit back, and watch the magic. Excel will run your simulation 1000 times. Now you can calculate statistics like the average number of resource 1 per player or the average total points per game. Press F9 a few times. The statistics should change slightly, but not significantly. If they are changing a lot, you can try making a bigger table with 2,000 iterations or more until you get a more stable result.

Results! Yay! What do they mean???

Interpreting the Results

Editor’s Note: This is the tricky part. If you can’t understand what the results are actually saying, then don’t bother doing the analysis. When you start the simulation, make sure you ask yourself what you expect or want the results to look like. Then you can compare the results with what you expected. After that you can tweak your deck of cards, in this example, to get the expected result.

Based on our result, we can see that Resources 2 (R2) and 3 (R3) are fairly well balanced with each other, while Resource 1 is in much greater demand. This might be ok, but you will want to make sure that there is enough Resource 1 available. We see the average points per player is about 25 per game, with a minimum of 15 and a max of 36.  The average total score between 4 players is 99. The average highest possible score for any given game is about 30, while the average spread is about 10 points from highest to lowest.

See! It is Magical!

So there you have it. You’ve just tested balance in your game without having to touch a single card. Now you can re-balance and get your friends together to play. Of course, you shouldn’t consider this to be a replacement for playtesting. Excel can’t tell you if your game is fun, and it can’t simulate strategy. It can’t make complicated choices or stab you in the back. It’s difficult to simulate social game or smack-talk. But it might be able to help you see areas of imbalance or help you make tweaks.

https://docs.google.com/uc?export=download&id=0B7jhoEOs7yvtRkNVM2NCYWxvR0k

I’ve uploaded a copy of the simulation above that you can feel free to download and use to test your own designs. I’ve also included a simple Blackjack dealing simulation, and a few die roll simulations. Feel free to contact me with any questions. @AdamBuckingham or AdamLBuckingham at gmail.

Editor’s Note: Thanks, Adam, for writing this article! I know that this method has already helped me with the Scoville design. I’m looking forward to hearing success stories from other people!

Also, I am definitely NOT the person to contact about this. If you have questions, please send them Adam’s way rather than posting as comments on this article.