Publication number: ELQ-56672-1
View all versions & Certificate
2014 Round 2: Purple City
Excel training/competition model from the 2014 Financial Modeling World Championships
To help finance professionals transform their Excel, financial modelling, data visualisation & analytics skillsFollow 70
This workbook is taken from the 2nd round of the 2014 Modeloff financial modeling world chamionpships.
Follow the instructions in order to complete the model:
You have taken on a role in the organising committee for the 2015 Underwater Football World Championship. Your first task is to help the committee understand the potential total cost of flights for the championship. As part of this, you will need to build a model to allow you to identify flight details and costs
for all domestic flights for each team in the Championship. In order to calculate the potential cost, you are provided with the following information about the
1) The tournament consists of 24 teams divided into four pools, each containing six teams. Each has an animal name that the team is referred to by.
2) There are 60 pool games followed by 7 finals games.
3) There are 12 cities in the country that games are held at. Each city is named after a colour.
4) All teams will start and end the tournament at Purple City as this has the country's main international airport. The costs of the international flights to arrive at, and depart from, the tournament are paid for by the individual teams so are not to be included in this analysis.
5) Whenever a team travels, flights for the entire squad of team personnel must be arranged.
6) The organising committee has taken a view on which teams will progress to each game in the finals for the purposes of calculating these costs. Base your modelling on the assumption that the finals will be played by the specified teams (shown in the supporting Excel file).
The required information has been collated by the organising team and given to you in an Excel file. This file includes:
1) A list of the teams competing
2) A list of the cities where games are played
3) The total number of personnel per team requiring flights (the squad size)
4) The teams involved in, date of, and venue used, for each game
5) The flight costs per person between each location
HINT: When designing your model, to answer all questions you may find it useful for your model to calculate: Flights costs by team; Flight costs by departure city; Flight costs by arrival city; Flights costs within a specified range of dates; Flight costs if some game venues change from the base case.
Allotted time: 30 minutes
Once finished, feel free to upload your model to your own dedicated author channel!
This Best Practice includes
1 Excel Workbook, 1 PDF
Full Stack Modeller offers you this Best Practice for free!
download for free
Add to bookmarks