2015-16 Bowl Pool Spreadsheet with Scenario Analysis

I promised this in my previous post about this year's college football bowl pool spreadsheets, and now I'm delivering, even if a touch later than planned.

I've now got a version of the summary file out there with the aforementioned scenario analysis built in. Here's how it works:

• I'm using the betting lines (point spreads) for each game to determine the probabilities of the favorite teams winning. Essentially, I used the College Football table found on this website to convert the point spreads to the probabilities.
• I then run all not-yet-played games (meaning, the winner hasn't been set on the Picks tab) through 1,000 random scenarios, where the outcomes are weighted accordingly. That is, if I run 1,000 scenarios of a game that is a 7 point spread, the favorite in that game should come close to winning 70.3% of the time, but it's still random, so it make be more or less to some degree.
• For games that have already been played, I always use the actual winner in the scenarios.
• Then I apply the winning teams from each scenario to each participant's picks and points, and see which participant ends up with the most points and thereby wins each scenario. Actually I rank all participants' score for each scenario.
• Then I calculate each participant's overall odds of finishing at each possible rank, including their chances of winning the pool, based on that sample of 1,000 scenarios.
• Finally, I do some analysis for each participant to highlight which games are of particular importance to each person. For example, it might be that for a given participant, 100% of the scenarios for which they win the pool involve California beating Air Force in the Armed Forces bowl. It'd be handy for everyone to know that.
• This functionality is all found in two new tabs -- ODDS OF WINNING and CRITICAL GAMES -- as the rest of the sheet remains unchanged.

It's worth noting that running these 1,000 scenarios on my PC -- which is a decent Dell Latitude E6520 (2.40 GHz Intel i7 CPU with 8 GB of RAM and a Samsung 850 EVO 500 GB SSD) -- takes a touch over a minute. I do think that only running 1,000 scenarios is likely way under-sampling things, particularly when there are 20+ games remaining, which means there are over a million and up to 4.4 trillion possible outcomes! Given that, I could build out an option to run 10,000, 20,000, or even more scenarios. It's just that run time for such an option will go up in a linear fashion as you add more scenarios, meaning for 20,000 scenarios it could be half an hour or longer probably, and I don't know that folks really want to wait that long for what still amounts to a sampling of potential outcomes. Also, I don't know that the overall precision of the estimated odds to win for each participant will become that much better.

Without further ado, here are this year's college football bowl pool management files:

• NEW 2015-16_Bowl_Pool_Summary_Scenarios.xlsm
• 2015-16_Bowl_Pool_Entry_Form.xls

If you plan to migrate your data from the prior Summary file version to this one, you should be able to simply copy-paste all of the participants' picks and points from one to the other. I'd do each participant separately, and ALWAYS use the Paste Special... Values option.

As always, the file is protected, but not with a password, so feel free to unprotect the workbook and then unhide some of the sheets running this stuff behind the scenes to see how it works. And if you add some cool hacks/additions, pass them along, so we can all enjoy them.

Enjoy, everyone!