2014 Round 2: Purple City

Description
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:

INTRODUCTION

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
tournament:

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).

SUPPORTING DATA
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!