Alternative title: that-one-time-my-colleagues-convinced-me-to-throw-my-money-in-the bin.xlsx
Bit of backstory, not like you need it. My colleagues somehow convinced me to buy into their syndicate for the Euromillions. I suspect they wanted me in because I’m the only accountant amongst them and they needed me to track of whether or not we won. Pooling all our money together, we were able to buy 50 lottery tickets and just like that, we were in with a chance to win £155m! Spoiler alert – we didn’t win.
The lottery worked by drawing 5 Main Numbers and 2 Lucky Stars. There were 50 possible Main Numbers and 12 possible Lucky Stars with various structures of MN and LS resulting in various potential winnings. For example, 5MN+2LS was the jackpot of £155m whilst 2MN+0LS was the lowest winning structure, returning a handsome £3.30.
Above shows the syndicate tracker. I won’t go into it much but it just shows how much each person would win based on the different win structures. Nothing too complicated but it was useful because not everybody contributed the same amount to the syndicate.
Now for the juicier stuff. There’s quite a few sections in the image above so we’ll take it one section at a time.
Data Entry
Columns A to H is pure data entry. I manually entered the winning numbers for each ticket we bought, each number in a separate cell. Nothing complicated about that. I did put a conditional format in these cells to highlight that green colour based on any matches to the actual drawn numbers (O2 to U2).
Drawn Numbers
Cells O2 to U2 are the actual numbers drawn on the night. Again, just a manual input. I also used these cells prior to the draw to play out a few random scenarios to get a feel of what the expected payout was going to be.
Ticket Number Frequencies
Columns O to T, rows 13 to 35 has some basic analysis of the numbers in our 50 tickets. It just counts how many instances of each number come up in our tickets. Nothing too interesting – just used the =COUNTIF function. I guess it was interesting that we had at least one instance of every possible number.
Win Structure Lookup
Sorry for jumping around everywhere but I’m getting through the simple stuff before moving onto the complicated bits. Columns W and X is the win structure table. “5&2” refers to 5 matching MN’s with 2 matching LS’s, and so on. This was entirely manually input. Columns Y and Z are purely there for informational purposes only. Look at those win probabilities!
Determining Each Ticket’s Win Structure
OK this brings us to columns J to M. Column J counts the number of MN’s in each ticket that match the actual drawn numbers. Similarly, column K counts the number of LS’s in each ticket that match the actual drawn numbers. Column L combines the two in a format that matches the Win Structure lookup table then Column M shows the corresponding winnings that particular win structure pays out.
Results Summary
Finally this brings us to the small box in the middle. This gives a few stats in an easy to read format:
1) Total Return: sum of column M
2) Number of Winning Tickets: number of non-zero results in column M
3) Return per Share: we had 12.5 shares so it was just Total Return/12.5
4) Net Return per Share: each share cost £10, so this was simply the Return per Share, less the cost
Four winning tickets out of 50, with a net loss of £7.92 per share. Not a great result but it was a fun exercise anyway. Playing with some random scenarios prior to the draw was quite fun and it was a good exercise in performing various count functions.
If only we made some money out of it.
Excel Doodling 