So for a little fun after the USA qualified for the World Cup I worked on an excel workbook where I can generate World Cup draws.
We have 10 teams that have qualified out of the 32 so far. And my current setup is basing the draw on what happened in 2010. This could change when FIFA meets on December 3rd to decide on how to do the group draw on December 6th.
For the remaining teams, I mostly picked who goes based on current standings in their respective qualifications and/or randomly picked a few teams that can make it. You could essentially pick whatever nations that have yet to qualify and toss them in yourself. Nation rankings are based on the FIFA September 2013 rankings that came out this morning. For example, I've gone with assuming Uruguay finishes 5th and beats Jordan in the AFC v CONMEBOL playoff. And for the OFC v CONCACAF, I'm just assuming Panama takes 4th as they are currently in that position and that they beat New Zealand. This also makes Pot 2 easy as it is 4 and 4 for the nations.
Outside of this, each Pot is organized as follows:
Pot 1 - Host nation plus the top 7 ranked teams
Pot 2 - CONCACAF and AFC Nations
Pot 3 - CONMEBOL and CAF Nations
Pot 4 - UEFA Nations
During my first draft and the August standings it ended up working out that UEFA had 5 nations in Pot 1 leaving me a perfect 8 nations for Pot 2. But this draft and the Sept rankings left me with 9 UEFA nations. I'm not sure how FIFA would do it but I assume the last UEFA team would get bumped around to another Pot. In my current example, I moved Iceland to Pot 3 since it was an additional CONMEBOL team that moved into Pot 1.
Next up, each Pot is organized top to bottom based on current FIFA rankings. It doesn't have to be done this way, it is just how I felt like organizing the Pots. Each Group A-H has 1 team from each pot. The easy part is Pot 1 as each team goes into each group. For Pots 2-4 I was able to create a formula that allowed excel to randomly generate unique numbers within a small range. Therefore I could hit the F9 key and change up my draw as often as I wanted to. And in my example below I had to switch Ecuador and Iceland in their respective groups to stick with the group limits of 2 UEFA teams max or 1 team per confederation max.
Below I've copied what I made right before posting this. I will see if I can upload my entire workbook to google documents so if anyone would like to download it and play with it, they are welcome to. And please let me know what you think in the comments below. This was fun to do and I would love any input or requests.
Pot 1 | Pot 2 | Pot 3 | Pot 4 | |||||||||||||
1 | Brazil | 9 | USA | 17 | Chile | 25 | Netherlands | Each Group may not exceed 2 UEFA teams per group | ||||||||
2 | Spain | 10 | Costa Rica | 18 | Ivory Coast | 26 | Greece | Each Group may not exceed 1 team from each confederation | ||||||||
3 | Argentina | 11 | Panama | 19 | Ecuador | 27 | Switzerland | w/ exception to UEFA as listed above | ||||||||
4 | Germany | 12 | Honduras | 20 | Ghana | 28 | Russia | |||||||||
5 | Italy | 13 | Japan | 21 | Algeria | 29 | England | |||||||||
6 | Colombia | 14 | Iran | 22 | Nigeria | 30 | Bosnia | |||||||||
7 | Belgium | 15 | Austrailia | 23 | Egypt | 31 | France | |||||||||
8 | Uruguay | 16 | South Korea | 24 | Iceland | 32 | Ukraine | |||||||||
Country | FIFA Ranking Sept 2013 | |||||||||||||||
Brazil | 8 | Japan | 42 | Chile | 16 | Netherlands | 9 | |||||||||
Argentina | 2 | Austrailia | 53 | Ecuador | 20 | Switzerland | 14 | |||||||||
Italy | 4 | Iran | 48 | Ivory Coast | 19 | Russia | 15 | |||||||||
Colombia | 5 | South Korea | 58 | Ghana | 24 | Bosnia | 18 | |||||||||
Uruguay | 7 | USA | 13 | Algeria | 28 | England | 17 | |||||||||
Spain | 1 | Costa Rica | 33 | Nigeria | 36 | Greece | 12 | |||||||||
Germany | 3 | Honduras | 40 | Egypt | 50 | France | 25 | |||||||||
Belgium | 6 | Panama | 35 | Iceland | 54 | Ukraine | 26 | |||||||||
Group A | Group B | Group C | Group D | Group E | Group F | Group G | Group H | |||||||||
Brazil | Spain | Argentina | Germany | Italy | Colombia | Belgium | Uruguay | |||||||||
Iran | 14 | Panama | 11 | Austrailia | 15 | Costa Rice | 10 | Honduras | 12 | Japan | 13 | South Korea | 16 | USA | 9 | |
Algeria | 21 | Nigeria | 22 | Egypt | 23 | Ivory Coast | 18 | Ecuador | 24 | Iceland | 19 | Ghana | 20 | Chile | 17 | |
Netherlands | 25 | Bosnia | 30 | England | 29 | Ukraine | 32 | Russia | 28 | France | 31 | Switzerland | 27 | Greece | 26 |