How Excel 2010 helped me win Fantasy Football

John Stokdyk lifts the lid on the Excel 2010 KPI dashboard he used through the football season to pick his winning fantasy team. 

Now the football season is almost over, I’m crowing not about Chelsea’s triumph in the Premier Leauge, but ExcelZone Athletic’s somewhat less lucrative victory in the company Fantasy Football league.

It was a tightly fought contest in which I found it difficult to shake off the persistent challenge of my own Manchester United equivalent, Chic Geek City. But a consistent 8%-plus advantage over the course of the season was enough to see me through with a margin of 230 points.

What I can now reveal to my frustrated colleagues is that an Excel KPI dashboard made a key difference to my team’s success.  Since the same techniques can transfer to wider business scenarios, this article sets out my methods and shows how Excel 2010 and its new Sparklines feature helped me win.

The Moneyball factor
Anyone who read Michael Lewis’s book, Moneyball, on the low-budget success of the Oakland Athletics baseball team will know that fantasy sports present a classic performance management challenge.

In Fantasy Football points accrued by players during the season are based on different factors such as the goals, tackles and “assists” where a footballer makes the final pass to a goal scorer. These statistics are recorded and compiled into weekly totals.

Lost most similar simulations, the Yahoo! Fantasy Football league we played allocated each team manager a £100m budget to buy players, with prices fluctuating each week according to their on-field performance and the demand for their services.

Guided by the principles of Oakland A’s manager Billy Beane, I recognised that success lay in getting as many points per pound as I could from my players. Doing so required a certain amount of arbitrage in the marketplace to identify undervalued players who could contribute the most to my team.

Performance drivers
Goal-scorers and creative attacking players such as Didier Drogba, Wayne Rooney and Frank Lampard win the most fantasy football points, and consequently cost the most. Teams also need to include less expensive defensive players and goalkeepers to fit within the budget cap. The following information in the fantasy game’s database held the key to the performance drivers in my dashboard:

  • Total points scored
  • Player cost
  • Average points per week
  • Points scored per pound invested.

The fantasy game’s database provided all this information. Not only was it available to all the other players, the sheer mass of data made interpretation difficult. To secure the best quality players at the lowest price in a constantly changing market, I needed an indicator that could help me track the most effective prospects against the market average to snap them up early when their prices were still low.

I decided that along with total points scored, the best KPI was each player’s average weekly points per pound cost. This measure would help me spot those players who players were defying the market norms on a consistent, improving basis.

Assessing this measure for 250+ players each week was not easy, but in-cell Microcharts and conditional formatting for their total points would give me the visual aids I wanted. The first tool I tried in tandem with Excel 2003 was

Nanocharts

a free add-in from the How-to website. However I found the interface difficult to use and the results erratic and unstable. I had almost abandoned hope for my Fantasy Dashboard when I got my hands on the beta version of Excel 2010, whose built-in sparklines coped smoothly with the task.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Objection

Anonymous | | Permalink

Including Aston Villa in "overacheiving" teams and especially in the same breath as Birmingham !

Look at the points difference in the league table.

John Stokdyk's picture

Head over heart

John Stokdyk | | Permalink

Ah, my friend, you make the classic Fantasy mistake of letting your emotions cloud your judgement. There's no room for sentiment in this game. The only measure that counts is how a team or player performs against market averages and expectations.

Birmingham certainly trailed Aston Villa by 14 points in the final league table, but as a team that won promotion to the Premier League last year, Birmingham did much better than expected - and were particularly strong defensively at the outset.

As they have done for the past couple of years, Villa improved in the run up to Christmas (again on the back of a good defence) but slipped away from the top four Champions League places as the season concluded. Towards the end, the points differential between Villa strikers Gabriel Agbonlahor and John Carew and Chelsea's Didier Drogba and Frank Lampard was embarassing (I should know as I ended up owning both the Villa players after selling Drogba during the Africa Nations Cup - my biggest mistake of the season).

Fair Enough

Anonymous | | Permalink

But Villa never overachieved, we have spent a lot of money, finished in the top 6 for 3 seasons in a row, i would say that is exactly where we expect to finish.

Overachieving is saying we were expected to finish 10th and finished 6th, thats nonsense.  Birmingham overachieved as they were expected to relegated.  Fulham overachieved, getting to a europa league final.  Villa done exactly as expected or in some fans a bit worse as they were hoping for top 4.

I still feel including villa with Birmingham and Fulham is just wrong.

New Season Ahead    1 thanks

Anonymous | | Permalink

Are you going to allow us to download it then??????

IFERROR()    1 thanks

corgan | | Permalink

 You can replace: IF(ISERROR(VLOOKUP(A2,PremWk2,13,FALSE)),0,(VLOOKUP(A2,PremWk2,13,FALSE)))

 With: IfError(VLOOKUP(A2,PremWk2,13,FALSE),0) - I think IFError is new for Excel 2010.

EPL 2013-2014

slaird75 | | Permalink

Greetings from Calgary Canada!

is there a  new version spreadsheet design for the upcoming EPL season?

thanks Stu

slaird75ATshaw.ca