The Brode Report - March 2011

The Brode Report | March 2011

Hi, this is David Brode. Sometimes I see problems that just beg to be solved in Excel. I’ve taken stabs at Sudoku and Ken Ken that were immensely satisfying. I do just love watching the computer solve puzzles; it reminds me of that scene from War Games. This month's article is about beating the odds behind "Let's Make a Deal" - yes, there is an Excel analysis for that.

Thanks so much for the referrals that have been coming in--they are very much appreciated.


	Struggling with spreadsheet data overload? Crazy-hard analysis? Multiple entity forecasts? Complex cash flow structures? I'm always happy to discuss situations--call me at (303) 444-3300.

Beating “Let’s Make a Deal”

"It is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail."
--Abraham Maslow

"All work and no play makes Jack a dull boy."
--The Shining

Sometimes I see problems that just beg to be solved in Excel. I’ve taken stabs at Sudoku and Ken Ken that were immensely satisfying. I do just love watching the computer solve puzzles; it reminds me of that scene from War Games.

The Monty Hall problem is a classic. Wikipedia’s article summarizes it like this:

Suppose you're on a game show, and you're given the choice of three doors: Behind one door is a car; behind the others, goats. You pick a door, say No. 1, and the host, who knows what's behind the doors, opens another door, say No. 3, which has a goat. He then says to you, "Do you want to pick door No. 2?" Is it to your advantage to switch your choice?

The answer is counter-intuitive: switching doubles your chances of winning. This was surprisingly easy to demonstrate in Excel using a Monte Carlo simulation approach. First, envision the problem like this:

Door	Position	Initial Guess
1
2
3

There are three doors. The position of the car will be designated with a 1 in the “Position” column; the other two doors get a 0. Your initial door choice is designated by a 1 in the “Initial Guess” column with the other two doors getting a 0.

So if we pick two random numbers we can fill in these columns. In the example spreadsheet here I simply used Excel’s RAND() function, but I prefer ZRandom ($29; free trial download) because of how it accepts seed values so you can replicate simulations.

1) Randomness

Seed	1
Random #1	0.64	2	Determines position of car
Random #2	0.51	2	Determines initial guess

2) Summary of Initial Position

Door	Position	Initial Guess
1	0	0
2	1	1
3	0	0

Here the Random #1 value results in the car being behind Door #2 (0-0.3333= Door #1, etc.). Similarly, Random #2 is used to set the Initial Guess as Door #2.

Then we get to Monty Hall’s choice. Since your Initial Guess=Position, he chooses randomly between the two doors. I have a Random #3 variable for that but didn’t show it. Had you not selected the car in your initial guess, he reveals the *other* door that doesn’t have the car.

Finally, we can see whether switching results in a win (1) or a loss (0). In this case, obviously, switching is a loss.

3) Monty Hall reveals a door
a) If person guessed the car, he picks randomly
b) If person picked a goat, he takes the other goat

Door Revealed:			3

Switch
Yes			0
No			1
		Yes	No
	Output:	0	1

Then I wrote a short macro to simulate the game 10,000 times. The VBA code looks like this:

Sub runSim()
   Application.ScreenUpdating = False
   For i = 1 To 10000
      [Seed].Value = i
      Application.Calculate

      [output].Copy
      [output].Offset(3 + i, 0).PasteSpecial Paste:=xlValues
   Next i
End Sub

The results of my simulation showed that switching results in a win 66.4% of the time—quite close to the predicted 2/3 level. Q.E.D.

If you’re intrigued by this, I encourage you to read Wikipedia’s article. My spreadsheet on this analysis is also available, and you can watch the video of me going over it.

This type of analysis isn't just for fun and games. Breaking down a problem into simple steps is an essential part to understanding business dynamics for strategic decision making and investor communications. The simulation analysis has fantastic applications in financial markets, where there is a large body of evidence and the ability to "play the game" repeatedly.

Go back

The Brode Group

Strategic Financial Consulting - Real-World Results

(303) 444-3300