How We Built the Women’s Soccer Prediction Spreadsheet in Excel
Published Jun 03 2019 08:11 AM 22.7K Views
Microsoft

 

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 RoundsFigure 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 TabFigure 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 PredictionsFigure 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:

 

 

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

 

4 Comments
Steel Contributor

Very cool. 

I just wasted 15min trying to figure out why your function in cell J5 of the [Group Stage Predictions] worked until I finally realized you were using SUMIF() and not SUMIFS(). Smiley Indifferent

I haven't used SUMIF() since SUMIFS() was introduced even for a single criteria. Will teach me to pay more attention. Smiley Happy

Microsoft

@Ed Hansberry, thanks for your comment!  I agree with you, and mostly use sumifs() as well.  In this case I leveraged the Men's Championship Template, and only modified what had changed (number of groups, round of 16 logic, etc.).

Copper Contributor

hello

i want to learn how to embed and analyze soccer data on Excel

also using formulas like Poisson distribution,Kelly criterion etc

 

where should i start learning?

 

thanks in advance

Copper Contributor

Hi @David Monroy, your model reminds me of the model we built at Modano to predict the outcome of the 2018 World Cup, which you can download and check out by clicking this link.

 

Like your model, it allows users to specify a range of assumptions regarding different teams, but this model then performs Monte Carlo simulations to predict the outcomes. Interesting stuff.

 

Fifa.png

Version history
Last update:
‎Jun 03 2019 08:55 AM
Updated by: