Madden Roster Optimizer

I thought it would be interesting from both a quantitative and qualitative perspective to try to optimize the "best" football roster (according to Madden ratings) to meet salary cap constraints, and I was right! Immediately I realized the fact that there is really no way to define "best" in this sense, and there is no mathematically optimal solution when you consider that tradeoffs and choices have to be made in terms of things like position. It's impossible to fill the lineup with 99 OVRs, so what positions do you put them at? Would you rather have a lineup of 5% all-stars and 95% scrubs or a deeper but worse-on-average roster? Due to this fact, I opted to use Excel's evolutionary solver, which allows you to essentially choose weights to encourage or discourage certain optimization choices via bonuses and penalties.

One other cool thing that I had was access to Madden's "scheme" definitions, some examples of which are partially shown in the bottom left image above. This added a new dimension, I could create the "best" lineups for various schemes by utilizing the fact that Madden also provides skill ratings for each player in the individual categories/archetypes as well as their OVR.

After organizing the data and setting up the sheets for the various schemes so that they pulled from the data sheet properly, I was able to start thinking about the penalties and bonuses. I added severe penalties for things like the position constraints and salary caps to prevent them from being broken, but also gave softer ones to things like having too high of an average age. I also gave penalties for having players with horrendously low ratings to add some sense of realism because they would probably not be on any "best" NFL roster. I gave bonuses for things like having college teammates or players with exceedlingly high ratings or certain tags like Superstar and Hidden.

All in all, there is work that could be done to tweak the weights and try to dial in an exceedingly realistic roster builder, but it was really cool just to see how the computed optimal lineups changed with different weights and schemes and how you can mathematically build in certain qualitative roster desires and considerations.