Wildcard Time: Using Excel to Pick a New Fantasy Squad

See our Wildcard Team optimised by pints scored so far this season plus down the spreadsheet to have a go yourself!

The Premier League took an FA Cup break at the weekend and this provided a nice opportunity for managers to take stock of their FPL seasons so far. For many this likely also meant planning how best to take advantage of the newly available transfer wildcard. We at Inside FPL were no different and have been using this enforced break to road-test a slightly different way of picking a fantasy squad.

Taking inspiration from the fantasy NFL work of Professor Wayne Winston we have created an Excel spreadsheet to help us pick the best value squad with our wildcard. This file can be freely downloaded by following the link at the bottom of this page.

***** Reported User Problems *****

Some users have reported problems when trying to use this file. The instructions tab has been expanded and clarified somewhat which will hopefully solve some of the issues some users are experiencing.

For reference, the file was created using Windows 7 and Excel 2010 so it is possible some issues may be due to users running different versions.

* Update: If you are running the file on Excel 2007 you may need to check a box within Solver called "Assume Linear Model"

(Thank you to Twitter follower @2nd_Man for this update)

***************************************

So how can a spreadsheet pick a fantasy squad? Well, without wishing to turn this article into a computing textbook, we use a tool called Excel Solver (you will likely have to install this to use the spreadsheet). We then set up our spreadsheet in such a way as to tell Solver the following information:

1. What are we trying to achieve? What is our 'objective'?

- The highest possible fantasy score

2. What are we changing? What are our 'variables'?

- The different possible combinations of players in our squad

3. What are the rules? What are our 'constraints'?

Simply the rules of Fantasy FPL

- No more than 3 players from any one club

- 1 Goalkeeper, 5 Defenders, 5 Midfielders, 3 Forwards

- Maximum total cost of £96 million

(note I have assumed here that most squads will use one squad slot for a £4m reserve goalkeeper)

Gameweek 1-20: Best Possible Squad

For more detailed instructions see the first tab in the file, but for now let's skip to the interesting bit - what would be the best possible squad to pick based on points scored to date and current prices? Using our rules above we come up with the following:

Looking pretty good so far? Using this squad in a 3-4-3 formation with Suarez as captain would have scored you 1,272 points so far, enough to be snugly ensconced in the top 10,000 (a rank around the 7,000 mark). It is worth noting that this takes no account of week-to-week line-up changes and assumes you had Suarez in the team and as captain from Gameweek 1 (he didn't play until GW6).

In addition, the team above is simply based on points scored so far and current prices. Any player who has missed a significant proportion of his teams games so far will be unfairly penalised and what we are really interested in is picking the best possible squad for the remainder of the season. So how might we go about this?

Gameweek 21-38: Best Projected Squad

To project the best squad for the rest of the season we obviously have to try to project how many points each player will get the rest of the way. For this we are going to take each players points per minute scored so far and then project forward based on how many minutes we think they will play the remainder of the season.

Initially, we assumed the following:

GK / DEF: 90 minutes per game = 1,620 minutes

MID / FWD: 75 minutes per game = 1,350 minutes

The reasoning here is that midfielders and forwards are subject to more squad rotation than defenders and goalkeepers. This is obviously not a perfect assumption but it can be easily adjusted for individual players later as you see fit.

For example, there are several players with good points per minute that I have had to manually adjust down to account for either being injured (Winston Reid, Theo Walcott) or for not being every week starters (Hatem Ben Arfa, Danny Welbeck). Conversely, I have also adjusted up some every week starters (Yaya Toure, Luis Suarez) and also accounted for the imminent return of Sergio Aguero.

After a few trials here is what it has come up with:

So what do we think? The assumptions and rules in the spreadsheet can all be adjusted so feel free to play around and see what you can come up with. You may disagree with some of the assumptions about how many minutes some players with play. Players who have done well in limited appearances so far may be overrated. For example, is Vito Mannone now cemented as Sunderland's number one? Are Adebayor and Chiriches going to remain regular starters under Tim Sherwood?

Ultimately no spreadsheet is going to be perfect (where would the fun be in that?) but if it can give us any kind of edge it must be worth experimenting with.