Fantasy Football and OBIEE Part 2 : The Next Man City?
In the last blog entry, i basically did a quick run-through of our 1st part of the "Fantasy Football" demo we put together for the recent UKOUG Conference in Birmingham. Today, i shall be covering the 2nd part of the demo which is actually even more interesting. The 2nd part of our demo was in showing how a potential investor can use BI EE to do an ROI analysis of various Premier League teams and then make a decision based on that analysis. Any investor who likes to buy a football club has to spend his/her money in a couple of streams
1. Purchase Price of the club - This is the amount required to buy the club. Chris had come up with a formula to derive the Purchase price of a club as given below
(3 x Turnover) - (Debt + (3 x Annual Loss))
2. Extra Investment - This could be an investment on recruiting new players, constructing a new stadium etc.
The purchase price of the club is more or less a derived metric and is driven by a formula shown above. From a technical standpoint, we had a single database table containing the revenue, debt, income figures of each club. An example screenshot is shown below
If you notice, all the big clubs like Chelsea, Manchester United have huge debts and hence their purchase prices are actually negative. Now you will know why Abramovic purchased Chelsea for 1 Pound. He basically just acquired the debt of Chelsea.
An investor always would like look at a club that has a lower purchase price but has a good potential for growth i.e ROI will be much faster and will be more as well. So, whatever investment the investor is making should result in higher profits and hence higher returns soon. The investor can look at a couple of options.
1. Hire new players (Player/Manager Investment) - This is typically not dependent on a club but more on the amount of spending an investor can do. Hence we had used a formula to arrive at ROI for a specific investment range. That range is given here
An investment of 50 Million Pounds = 10% increase in income from prior year
An investment of 100 Million Pounds = 20% increase in income from prior year
An investment of 200 Million Pounds = 80% increase in income from prior year
An investment of 400 Million Pounds = 250% increase in income from prior year
2. Invest in a new stadium - This is completely dependent on the fan following of a club and the potential for growth in the number of followers for the club. Some clubs might have a smaller stadium and hence by building a new stadium with more capacity, the Gate Receipts income can potentially be increased.
For the 2nd part, Chris had come up with a nice little dataset for the population distribution in and around London. That is shown below
If you notice, we basically have identified population of the areas of each club and then compared that with the actual average attendance for all the matches of each club. How do we determine what will be the percentage growth of each club in terms of number of attendees? This where we introduced a couple of factors
1. Growth Potential 1 (Big Club Potential) - We allow the investor to enter an arbitrary number(0 to 1) for each club depending on what he thinks will be the potential for the club to be a big club
2. Growth Potential (Big neighbor factor) - If there is a big neighbor like Manchester United then there is only little growth that we can expect. This factor determines that. Again this accepted an arbitrary number (0 to 1) to be varied across the clubs.
So, we basically enabled a What-if scenario kind of report in BI EE using Writebacks. The screenshot is given below
If you notice, we have made the both the Growth Potential factors to be alterable in the report above using BI EE writebacks. A potential investor can vary these numbers and then look at the potential turnover graph (which will vary based on a formula). The final formula that we used to calculate the final fans growth potential is given below
10% * Conurbation population/competing clubs * Weighting Factor 1 * Weighting Factor 2 / Average Attendance* 100
We then used a projection to calculate the potential increase in Gate-Receipts revenue using the above growth potential. Once we had the potential increase in Gate-Receipts revenue and the revenue due to player manager investment, we calculated the ROI as
ROI = (Gate Receipts Revenue + Player Management Revenue) * Number of Years – Purchase Price – Debt – Investment in each year * Number of Years
To make it complete, we also provided possible investment range numbers to be variable in the what-if scenario. That way an investor can input certain Investment numbers, Growth Potential factors and the expected profit percentage, the graphs on the dashboard will automatically vary to reflect the new change. This was pretty sophisticated. Though there were quite a few variables, we did notice that among all the clubs, Birmingham city had the highest potential for growth for a small investment.