Trevor Hardy of the Canadian Football League -- Canada's counterpart to the NFL -- faced the task of scheduling 81 games for nine clubs across a 20-week season, balancing competing priorities for revenue, TV ratings, or player rest -- while meeting a wide range of constraints, from times of day for fans in four time zones to traditional Labor Day rivalry match-ups. Here's how he used optimization in Microsoft Excel to create significantly better schedules than the league had been able to create by hand.
Trevor Hardy has a unique background. An accountant by trade, he has also dabbled in investigative forensics and has an MBA. Ten years ago, he was hired by the Canadian Football League to implement a salary cap for the league. The initiative took a few years to get up and running, but once it was a well-oiled machine, Trevor started looking for other projects that he could take on at CFL. That is how he discovered optimized scheduling.
Several years ago, Hardy learned of a consultant that CFL had hired to help with the scheduling process, which was increasingly becoming a focus of the business. In a sports league, the schedule is the starting point for a lot of business interactions, such as coordination with the broadcast rights holder for television programming and stadium ticket sales. It impacts various things, and if created poorly without optimization, could result in missed opportunities. With Hardy’s interest in analytics, he decided to tackle the scheduling problem and take on the consultant’s responsibilities.
From Sports Betting to Scheduling
One day, while having a casual conversation with a friend about sports betting, Hardy learned about a tool called Solver in Excel that could help him predict the scores of National Football League games based on past scores. While he had no formal education in analytics or optimization, he was an expert Excel user, which was a great starting point for someone new to these methods. After Hardy was able to figure out how to use Solver for his hobby, he started thinking about how he could use the same program for the scheduling problem.
Competing Priorities
Once Hardy decided that he was going to use Solver for scheduling, he needed to figure out what he was trying to optimize, or what CFL was trying to do as a league. He was responsible for scheduling 81 games for nine clubs across a 20-week season, and there were competing priorities:
- Revenue – He could set a schedule where the most lucrative games and time slots are given to teams that sell the most tickets, merchandise and concessions.
- TV Ratings – He could set a schedule that would optimize TV ratings for the single Canadian network that broadcasts CFL games.
- Rest Days – He could set a schedule such that the number of rest days (days since last game) for both teams in a match were as similar as possible.
Frequently, though, the interests of the broadcasting company were directly opposed to the interests of individual clubs. For example, in television, weeknight games are great times for sports because people are home those nights and eager to watch TV. However, from a club perspective, fans are less likely to buy tickets and drinks on weekdays, and prefer weekends where, for example, they can bring their kids out to the stadium and spend a day with the family.
After weighing their options, the league decided to optimize the schedule from a football operations perspective. In this respect, it was determined that ticket sales and TV ratings are a function of presenting the best product on the field, and the way to ensure the best product was by making sure that all nine clubs would have a fighting chance in each game. So, he looked to optimize the rest days of each club going into a game.
Solver For Optimization
Hardy’s scheduling problem was too large for the Solver built-in to Excel, but fortunately there was an easy upgrade – Premium Solver Platform from Frontline Systems, the firm that developed Solver in Excel for Microsoft. Instead of learning a completely different package and re-creating his model from scratch, Hardy could simply open his workbook in Excel, scale it up in size (from 200 decisions at once to 8,000, and later to even more), and solve it. His next step was to add in industry specific constraints, such as:
- Games must be at reasonable times of day for fans in all four time zones.
- Double headers cannot overlap, since they are all on the same broadcast network.
- Games for a single club must not be within five days of each other, except in a few specific instances.
- All traditional Labor Day rivalry match-ups must be honored.
- Specific club requests should be satisfied, if possible.
When he ran into an issue when running the model, he reached out to a Frontline Systems consultant who helped him turn his non-linear problem into a linear problem, so that Premium Solver Platform’s linear programming “engine” could be used. Once he got the model running, adding in additional constraints was easy. Hardy said that “the Solver software was incredibly helpful in allowing us to set those constraints and then not have to worry about them again.”
Optimization: Art + Science
Once Hardy created an initial schedule for the 2016 season, his job wasn’t done. He then took the schedule and went off to negotiate with clubs and broadcasters. These audiences didn’t care about optimization models and constraints. All they would say is “I need this game on Saturday,” and Hardy needed to take that into account. He took that information back to his model and had to adjust his optimized schedule.
However, that wasn’t necessarily a bad thing. He said that scheduling is 25 percent science and 75 percent art. Even if he was giving up the most mathematically perfect schedule, by considering these additional desires, he ended up with the best result that satisfied the majority of his stakeholders. It’s an iterative process, but one that was well worth it for CFL’s most sophisticated season schedule to date.