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.
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
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.