Using Excel To Simulate Villa's Demise.

In the previous post, I described a simple method to use expected or real goals to estimate the average number of goals each team might score and allow in a single game at a certain venue and hence derive the win/draw loss percentages for the game via a Poisson.

It's a handy trick, particularly if you want a method to frame you own match odds and compare them to the market. But the goal ratings can also be used to create passable odds for games that are due to be played over the remainder of the season.

The table above shows the home/draw/away odds for the final weekend of the season using team ratings from the first 230 matches of the season, expressed in expected goals.

It is likely that the abilities of the 20 Premier League teams will change over the remaining 150 matches, but often the change is gradual. Regression towards the mean may be used along with season to date trends to extrapolate each side's future ratings. But on this occasion the ratings from week 23 have simply been used throughout.




To download the estimated home win/draw/away win probabilities for the remainder of the 2015/16 Premier League season just click on the download icon above.

There are two worksheets. One with match odds, both home and away and a second which lists win/ draw (and loss) odds for each team's final 15 games.

We've now got the available ammunition to simulate the range of points that might be won by each of the 20 sides and eventually join up all the interconnected results in each iteration of a season to project final league positions.

But first we'll just use excel to simulate the range of final points a side might expect to get based on these match probabilities.

Here's Villa's final 15 games with their predicted win% in column D. In column G take their predicted draw probability from 1 and drag this formula down to G16.

Insert a random number in column H and again drag down to H16.

We need two columns. One for three points should Villa win and one for a single point should they draw. A win is assumed if the random number is less than the corresponding win probability in column D.

We've taken the draw probability from one in column D. So a draw is assumed in proportion to it's likelihood if the random number is greater than 1 minus the draw probability. We've also ensured that we don't get a win and a draw in the same game.

Now add up all the points won from wins and draws in Villa's final 15 games. Sum(I2:J16)

Now we need the data table/What if to run the simulation, in this case 1,000 times. count column L up from 1 to 1,000 and paste K16, the total points won by Villa from our projected odds into M1.

Select M1000 to L1. Click "What if", then Data Table, then Column input cell, then select an empty cell, K1 in this case. Click "OK" and the simulated points for Villa will auto fill into column M.

For a step by step screen grab for this stage refer back to this post.

Add the points Villa currently have to each iteration. With 15 games left it was 13. I've done this in column N. And then use =Countif($N$1:$N$1000,Q14) to sum the number of iterations from the 1,000 (or more) you've run to see Villa's most likely final points total.

It's 26, which is also around the mid point of the current quote on the various spread betting sites.

Next time I might get around to simulating league positions in excel, GD tie breakers and all that.