theLAW: So what is next Doc?
Doc: Well, last time we had showed how we could use some basic functions in Excel to simulate throwing a dice. If you recall, we were using an example of a 'game' where each of us rolled a dice - me winning £4 if the total on the dice is less than 7, and you winning £5 if it is greater than 8.
theLAW: Yep, I remember.
Doc: So we enter "=A1+B1" in cell C1, "=IF(C1<7,-4,"")" in cell D1 and "=IF(C1>8,5,"")" in cell E1.
theLAW: I've done that - but what does it mean?
Doc: Well cell C1 will add together the values of A1 and B1, which represent the individual dice - so C1 represents the total on the two dice. Cell D1 contains an IF statement. What that does is evaluates the 'condition' - i.e. what is before the first comma, in this case "C1<7" and returns the first value after the comma if it is true, and the second otherwise.
In this example it means that if the contents of cell C1 are less than 7, the it returns a value of -4, otherwise it returns "", i.e. a blank cell.
theLAW: Ah ok - so this is related to the game - I win £5 or lose £4?
Doc: Correct. Now I want you to repeat this for 20 rows. Can you select the 5 cells in the first row, by clicking and dragging your mouse?
theLAW: Ok. They're selected.
Doc: Now if you move the mouse over to the bottom right hand corner, it (the cursor) should change from an arrow to a cross. Click and drag this to extend the shaded region to 20 rows and let go.
theLAW: Ok - nifty trick that! So I've now got the result of 20 games, right?
Doc: Yes, that is exactly what you have. Now type the following into cell F1: "=SUM(D1:E21)/20"
theLAW: I get 0 - is that right?
Doc: It could be - press F9 for me
theLAW: It changed to 2! Why is that?
Doc: Well what cell F1 is doing is representing the average profit/loss from a run of 20 games. What the F9 button does is to 'recalculate', which means that new random numbers are generated in columns A and B, so that it effectively constitutes a new series of 20 games each time we press it.
theLAW: Ok, I understand that and it is sort of clever, but does it help me know whether this game is fair?
Doc: Well, press F9 20 times and see how many times your answer is positive.
theLAW: OK. I did that - it was positive 5 times - what does that mean?
Doc: Well it was positive 5 times and therefore negative 15 times, indicating that the game is probably unfair. We used 20 rows for the test, but if you used more (e.g. 100 or 1000) you'd get an answer which changed less and would be closer to the 'true' expectation for the game - i.e. the theoretically correct answer.
theLAW: Right, so more data tested, the more accurate the prediction?
Doc: Exactly. Now try filling cells A1 to E1 all the way down to A1000 and E1000
theLAW: Ready
- cells copied to 1000
Doc: You'll notice the results change less now; you'll also notice, that even over 1000 games, you can have a significant difference in results - even with a fairly simple game like this.
theLAW: Yep - so more data is needed?
Doc: Well the more data you use, the closer you will get to the 'true' answer. In this case we can calculate what that true answer is.
theLAW: How?
Doc: Well, think of it like this. There are 6 outcomes on our dice and 6 outcomes on our opponents. That means 36 combinations - of those 36, 15 result in a total of less than 7 (i.e. 6 or lower) and 10 result in a total of 9 or higher. So - 15/36 of the time we will lose £4 and 10/36 we will make £5. If we add those: (15/36)*(-4) + (10/36)*(5) We get -10/36, or £-0.28 That is the amount that, on average, we would expect to lose in any game.
theLAW: So I will lose!
Doc: Yep, you will - and you can pat yourself on the back for realising that from the off ;)
theLAW: LOL
Doc: You now have 1000 rows filled, right?
theLAW: Yep - A1000 - E1000
Doc: Ok - click on cell D1 for me. Edit the formula from "-4" to "-3.5" and hit enter. Now click on D1 again and move the mouse pointer to the bottom right corner till it changes into a cross sign - double click that.
theLAW: Whoa! Everything changed - what happened there?
Doc: Well we used the 'smart-fill' facility in Excel - it noticed that we were using a certain amount of the columns and rows, so when we double clicked on the cross - it assumed we wanted to fill that formula through every cell in that column of the region we are working on.
theLAW: Right - nice trick - I must remember that!
Doc: What we've done is change the rules of the game slightly - now you lose £3.50 (when you lose) rather than £4 as before. This new version of the game has a yield (i.e. expected profit) of -0.69. i.e. you lose 6.9% of your stake each time you play (the last one was -28%!). Now what I want you to do is to click F9 20 times, and count the number of times that the 'average profit' (i.e. cell F1) is positive
theLAW: I got 4 positives?
Doc: If I'd asked you beforehand, I bet you'd have thought 1000 goes of the game would tell you whether or not it was fair?
theLAW: You'd think so
Doc: Right - you've just learned the first (and one of the most important) lesson of monte carlo simulations: the power of randomness - just how deceptive certain sequences can be. For example, when I did that 20 click test, I found expected profits (from the 1000 run test sequence) between -18% and +13%
theLAW: Certainly is deceptive!
Doc: So to really have confidence in our tests (or in whatever it is we are testing) we need to make sure we have enough test points.
theLAW: A good lesson there
- I would never have imagined we would get that much variation! What's next?
Doc: You'll have to wait till next time to find that out
BBC
