How We Built the Women’s Soccer Prediction Spreadsheet in Excel

 

Another tournament means another opportunity for us to geek out with spreadsheets!  If you follow our blog, you may have seen our previous models: Basketball, Soccer, Football.  Now we are back with the Women’s Soccer Championship Predictions!  Speaking of our previous models, our track record isn’t bad – our Basketball template correctly predicted Virginia as the winner!  Read on to better understand how the tool was built and how to use it.

 

You can download the template here: English, French, German, Italian, Japanese, Portuguese, Spanish

 

Note: This tool is offered by Microsoft Corporation, and is not sponsored, endorsed by, or affiliated with FIFA. This tool is for fun only and is not in any way intended for use in betting or other uses of value.  No representation is made to the accuracy of data, predictions, and brackets derived from this tool.

 

The Tournament

 

Before going over the details, it is useful to understand how the Women's Championship is structured, since this will help understand the model:

 

Figure 1: Women's Championship Rounds

 

 

Building and Adjusting your Bracket

 

The model has three main components: Group Stage Predictions, Group Stage Adjustments, and Playoff Predictions.  The model is pre-filled with data and ready to go, but you can change parameters if you want to customize a team’s performance or override specific match results in the Group Stage matches.  

 

Note: Because the model looks at past performance and runs different possible outcomes using a Poisson distribution, it recalculates every time you modify values, tap F9, or choose Formulas – Calculate Now.

 

For the Group Stage portion of the championship, you can do one of two adjustments:

• Overall Team Performance Adjustment (%) in the “Group Stage Predictions” tab
• Overriding a specific match outcome, in the “This Year’s Matches” tab

Figure 2: Group Stage Predictions Tab

 

Once you have made your adjustments for the Group Stage, you can see the outcome through the Round of 16, Quarter Finals, Semi Finals and Finals in the “Playoff Predictions” tab:

 

Figure 3: Playoff Predictions

 

About the Prediction Model

 

Last year we built the 2018 prediction model, so instead of re-inventing the wheel, we leveraged this work and updated the input with the international women’s soccer ranking and historic data.  We also adjusted the model to account for differences in the Women’s version: fewer teams (24 instead of 32), fewer groups (6 instead of 8), and a different logic to pair teams for the Round of 16.  If you want all the details of how the model was built, and the concepts behind it, check this blog post.

 

Concerning data, for the men’s championship we used a consolidated dataset that included all games played over the last 146 years. Unfortunately, international women soccer is much younger than its men counterpart, and there is less data available. However, we were able to consolidate game results from several sources:

 

• "Cameroon women's national football team". Wikipedia.
• “Canada women's national soccer team results“. Wikipedia.
• “China women’s national football team results and fixtures”. Wikipedia.
• “Japan women's national football team results and fixtures”. Wikipedia.
• “Norway women's national football team”. Wikipedia
• “Scotland women's national football team results”. Wikipedia.
• “Spain women's national football team results”. Wikipedia.
• “Thailand women's national football team results”. Wikipedia.
• "US Women's National Team Results & Statistics". US Soccer.
• "World football”. Worldfoodtball.net.

 

You can download the template here: English, French, German, Italian, Japanese, Portuguese, Spanish

 

To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter. 

 

David Monroy, PM, Excel