We've done enough preparation in the introductory concepts - now it's time for us to try to build a statistical sports model in Excel that might help with football trading.  To help us, we'll adapt Andrew Macks' model from his book Statistical Sports Models In Excel.  Yes, you should absolutely buy this book.  You can access his Excel models that I reference in these videos when you do so, but I do not intend to share them; but, you can of course access the files I use, as usual.

The truth is that, for the non-specialist, the models are rather complicated.  So, let's take the first part of the Bradley Terry model ONLY and attempt to adapt it to our purpose.  The model uses different types of regression analysis to generate so-called 'logistic strengths' (simply 'ratings') for each team; these are translated into 'margins of victory' (MOV) which gives us a result, and implied odds.  Also included in the model is a facility to 'predict' a result and find value by comparing with real-world odds - we'll get to that later!

In the first video on this model, I explain the concept of 'regression' (as far as I understand it!) and demonstrate how the model is built according to Andrew's explanation.  Along the way, we tap into some of Excel's most powerful facilities (Solver, anyone?) and even improve the layout and structure of the proposed model.  Sorry, I just couldn't resist ...

Tools And Techniques

- Concept of Regression
- Calculate MOV (Margin Of Victory)
- Model Layout And Structure
- VLOOKUP
- Named Ranges
- EXP
- LN
- IF
- Excel Solver