As I wrote just yesterday, I've been impatiently awaiting the beginning of this year's college football season. This impatience can lead a person to do crazy things! I decided to while away my time by creating an annoyingly large spreadsheet. My passion for well-crafted spreadsheets is something I should blog about sometime ... and maybe see a therapist about as well.
When the Associated Press released its pre-season Top 25 rankings a couple weeks back, I started to think about what process I would go through if I was an AP voter. It was interesting me to think about how I'd project the season as a whole -- not really a pre-season Top 25, but a projection of the season's final Top 25 poll. So I built a way to do this.
The spreadsheet contains fields to predict the win-loss outcome of every game that will be played by a Power 5 conference team this season, plus a few teams outside the power conferences which are expected to be good or which are of general interest to the average college football fan. As I plugged in my W-L projections for each game, the spreadsheet compiles records for each team, sorted by which division of which conference they're in. It becomes clear which teams I project to win their divisions, then I just predicted the outcome of the conference title games.
After you factor in the conference title game results, it becomes relatively easy to piece together a projected final Top 25 -- final as of the conference championship game week, before bowl season starts. There's only a small leap from there to plugging in your College Football Playoff participants and projecting your eventual national champion. I had Alabama winning -- it'll be so nice to see a non-traditional power take home the hardware!!
Here's a publicly available copy of the spreadsheet, so you can take part in this exercise at home, if you want to. Why would you want to? I don't know, but I bet some people will think it's fun in a twisted way. Just follow the instructions in the very top row to get the spreadsheet copied over to a document that you can easily edit in Google Sheets. (P.S., if you notice anything wrong with it, feel free to let me know. Update 9/12, something wrong was noticed so I made a fix at the bottom of this page for it -- go look down there.) As the season progresses, you'll input the actual W/L result for each game in a currently empty column; you'll also input whether your personal prediction was correct or not. The spreadsheet will compile each IRL team's W/L record for you, and compile your personal correct/incorrect prediction record on a team, conference, and national level. I learned some new Excel formulas over the past week!
Here's what I wound up with...
People are hyped on LSU and Tennessee, but the SEC Championship Game will be a rematch of last year's. I don't see why I would project LSU to beat Alabama in the regular season. I have 'Bama going 11-1 with a loss to Arkansas, just because I see them losing a game they aren't supposed to lose, but I don't have any confidence in picking which game that will be.
I'm currently cautiously optimistic about Florida's offense this year, and somehow that lead me to predicting an 11-1 season with a loss to only FSU. While it's hard to see this best-case scenario happening for UF, I do think they're capable of winning any game on the schedule. They could go 8-4, they could go 11-1. I have them losing to Alabama in the SECCG. Meanwhile, I have LSU losing three games and getting Les Miles fired; I've got Auburn at 6-6 with Gus Malzahn getting fired; I've got Tennessee at 10-2, and Georgia at 8-4. 'Bama is Playoff-bound.
Clemson holds on to beat FSU, and Pitt stiff-arms North Carolina. Deshaun Watson leads Clemson to a 12-0 record, beating FSU at Doak, while FSU also drops a surprise game to Louisville. That gives Louisville a second-place finish in the Atlantic Division, with their only conference loss coming to Clemson. They also lose at Houston. Pittsburgh edges North Carolina with a 10-2 record (the Tar Heels go 9-3), even though Pitt actually loses to UNC head-to-head in my projections. UNC winds up losing to FSU, Miami and Duke here. I have Miami going 6-6 with a loss at Appalachian State, because this is my world. Clemson is heading to the Playoff.
Michigan is going to be fine, but Ohio State is still king in the Big Ten. Ohio State drops an early out-of-conference game at Oklahoma; they also get surprised on the road and lose to Penn State, which winds up costing them big-time. But they demolish the rest of the Big Ten, plowing through a 10-2 Michigan, 9-3 Michigan State and 10-2 Iowa in the conference title game. Closing the season with these three huge wins almost bumps them up to the Playoff, but they're gonna be upset, because...
The Pac-12 will be a ton of fun to watch, but I trust Stanford most. I have the Cardinal losing on the road at Washington, but I predict two losses for the Huskies (at Oregon and against Southern Cal), so Stanford makes the Pac-12 title game and faces off against a 10-2 UCLA, who they beat once already. The Bruins lose again, punching 12-1 Stanford a Playoff ticket with a super-strong strength of schedule. Meanwhile, Oregon goes 10-2, and USC finishes 9-3.
The Big 12 doesn't have a championship game yet, which seems ridiculous, but I guess Oklahoma will win it. And boy, are the Sooners going to be mad when they finish the season 11-1 and watching the Playoff at home. I think Oklahoma loses to Houston in Week 1, then rolls through the season with 11 straight wins from there. But a 1-loss Alabama and a 1-loss Stanford are more impressive to the committee since the Big 12 has a down year (no other team in the conference finishes with double-digit wins). So even though Oklahoma's only loss is to a Houston team that finishes undefeated, they get edged out in favor of three conference champions that played in a conference title game. The Big 12 adds two more teams in the offseason if they haven't done so already.
Three non-Power 5 teams will finish with one loss or fewer. Houston begins the season beating Oklahoma at home, and ends the season pounding a 10-2 Lousville as well. So we have our first-ever non-Power 5 playoff participant in the 13-0 Cougars. Elsewhere, Appalachian State opens with a close loss at Tennessee, attracting plenty of attention to the team; even more light is shed on them when they upset Miami at home (lol) in Week 3 and roll through the rest of the season undefeated. San Diego State finishes 11-1 after dropping the ball in Week 2 against Cal, by virtue of playing a soft schedule; BYU goes an impressive 9-3, while Notre Dame's Playoff hopes are dashed in the final week of the season as they finish 10-2 with a loss at Southern Cal. Their only other loss was earlier in the year to Stanford. South Florida drops back-to-back games to Temple and Navy, tarnishing a season that otherwise would have ended with only a loss to FSU.
Edit, 9/12: I wrote above that San Diego State would lose to Cal ... but this was not correct based on what I actually input in my spreadsheet. I had SD State going 12-0, but I made an error in compiling my results, so I had them 11-1 when I was ranking the Top 25. Since I changed them to 12-0 just now, I have them finishing at No. 11 in the country (previously No. 18), with everyone above them finishing one spot back now. This is the only edit that's been made to the sheet as of the end of Week 2.
Stanford upsets Clemson to meet Alabama in the title game, which pounded Houston. Alabama wins it again.
That's an overview of my predictions; you can see my full W-L spreadsheet here, if you're interested, but use this version if you wanna do it yourself. This was super fun to me and I hope people find some enjoyment out of using the spreadsheet!
Update, 9/12: I've discovered there was an error in public version of the spreadsheet. If you grabbed the spreadsheet before September 12, your copy has this error. The formulas in boxes that tabulate your correct/incorrect record for each conference are missing boxes they're supposed to pull info from, so your conference-by-conference and nationwide correct-incorrect records will be incomplete because of this. There's a semi-easy fix, just do the below.
First, make sure you've deleted the top line that once held the spreadsheet instructions. Then, add the following boxes into the following formulas by double-clicking the formula boxes and writing the text of the new boxes you're adding in. Add "E114" to the formula line in box E201, and add "F114" to the formula line in box F201. Add K114 and L114 into the formulas in K201 and L201. Add Q114 and R114 to the formulas in Q201 and R201. Add W86 and X86 to the formulas in W173 and X173. Add AC58 and AD58 to the formulas in AC145 and AD145. Then you're good to go!