Monday, December 28, 2015

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:

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!

Saturday, December 12, 2015

2015-16 NCAA Football Bowl Pool Files Are Ready

UPDATE as of 12/14/2015: Initially, I left off one of this year's now 42 bowl games -- actually the very first one: the Air Force Reserve Celebration Bowl, featuring Alcorn State and North Carolina A&T. Thank you, readers "Anonymous" and Tron Guy, for setting me straight. For some reason this bowl doesn't appear on the NCAA's website, nor on Wikipedia. And none of the betting line service websites I looked at to get odds shows it. Odd. This has been corrected in both files now.

UPDATE as of 12/28/2015: I've now got a version of the summary file out there with the aforementioned scenario analysis built in. I'm using the betting lines (point spreads) to determine the probabilities of the favorites winning, and then running 1,000 random scenarios, where the outcomes are weighted accordingly. Then I apply the winning teams from each scenario to each participant's picks and points, and see who wins each scenario. Then I calculate each participant's overall odds 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. 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) -- 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.

At long last, here are this year's college football bowl pool management files:

It took me a bit longer than normal to get the files out this season, and I've gotten many comments, emails, Facebook messages, etc., asking when they'd be ready. Well...they're ready.

For those unfamiliar with the two-file system, the idea is that pool managers can distribute the simple, single person Entry Form version to those in their pool to make their picks and assign confidence points. The participants them email back that file, and the manager can simply copy, paste special... values those picks and points into the Summary file, which is where they manage everything.

Note: Here's how I dealt with the playoff semi-finals and championship: I've used formulas to automatically fill in the two winners of the semifinals for the left part of the summary file. For each person's picks, they'll have the two teams they selected to win those semi-finals to pick from in the championship. Let me know if that doesn't work well or if you're confused by it. It does make the Individual Chart for the championship game a bit odd if a participant picked a team to win that game that didn't even play in it, but the points system still works fine.

Another note: I don't have the final record for the Navy team, since they're currently playing in the annual Army-Navy game this weekend. I've also left that cell unprotected so you can easily update the record for the Midshipmen if you want to.

Also, I don't yet have my personal picks completed, but will add them, as well as those for my wife (MRS. EXCEL_GEEK) and son (EXCEL_GEEK, JR.) and update the files once we're ready. Some of you have voiced a preference to have those picks included, to give you a benchmark for your own picks (and to see me lose each year to my wife.) UPDATE: Our picks are now included.

Finally, I'm planning on working in a new feature that I'll release in a few days that will enable the pool manager to run a series of simulated scenarios to see which Participants, given their picks and points vs. games that have already been played vs. point spreads, have the greatest chances of winning the pool. It should be a neat feature.

Enjoy, everyone!