Well done to all the participants who were through to the second round of the ModelOff 2014 Financial Modelling World Championships!
Last week, we provided solutions to the questions from ModelOff 2014, Financial Modelling World Championships round one – Precise Debt Modelling and Dealing with Data. This week, I will walk you through a solution for the 'Purple City' case study, featured in Round two.
ModelOff Round two case study
ModelOff 2014, Financial Modelling World Championships 'Purple City' case study relates to a hypothetical underwater sporting event which involves teams travelling between cities, as the events are competed in different pools (was that an intentional pun by the question designers?).
Primarily, the challenge relates to dealing with data that is mixed across several columns and combining it into a model that allows you to easily accumulate results. When dealing with text inputs (twelve colourful cities), the first thing you should do is to convert the list of inputs into index numbers so that Excel can more easily work with them (see screenshot 1).
Screenshot 1: List of venue indices
Set up a 'game series' in place of a time series
After converting the list, think about how the model structure would work. Conventionally, you would build a financial model by setting up a time series to track the timing of flights as the teams moved from one city to the next. However, in this instance, a time series would be a tough alternative, as setting up flags and triggers to identify changes in cities would result in an unnecessarily large table.
In place of a time series, setting up a ‘game series’, to track each team as they play different games would be a better alternative. I used a combination of SUMIFS functions that found the city associated with each game from Team 1, and added that to the city associated with Team 2. As I created the index value for each city, SUMIFS allowed me to use multiple search criteria and to easily combine a search from Team 1 and Team 2.
By splitting the list up across teams, this creates a table that allows you to see which city a particular team played each game in (screenshot 2). With eight columns for up to eight rounds, plus a starting city and ending city, this results in a much cleaner table than using a daily time series over seven weeks.
Screenshot 2: List of teams and cities being played in by the game number
And to finish it off...
I filled out the table for the unsuccessful teams (those that didn’t make it to the final rounds), assuming that they would make no further flights before moving to Purple City upon departure of the tournament.
The flight costs between games can then be easily calculated by looking at where a team played their previous game (or where they started the tournament from), and where they played their ‘current’ game. As the inputs are provided in a triangular matrix, use the MAX and MIN functions to ensure that the row reference will always be greater than the column reference, and so it searches the correct input area. If the team stayed in the same city, an additional check is in place to set the cost to be zero. Finally, all costs are then multiplied by the squad size of 21 to determine the total cost for each team and for the tournament as a whole.
These tables allow you to calculate the answers to all of the questions provided. I used a simple SUMIF function to gather team or city specific answers, in keeping with the use of SUMIF throughout the rest of the model. Conversely, you could use an INDEX(MATCH) function to achieve the same result. You may be interested in our tutorial on VLOOKUP, LOOKUP and INDEX (MATCH)
By not hard-coding any references to start and end cities you can be ensured that inputs are very flexible. Scenarios can be set up to test changes in the inputs that would allow for correct answers. This helps with the final two questions. A simple answer would have been to change the input, but in order to preserve the results to previous questions; I have built in switches that would override the base input before being used in the model.
The final question required us to obtain a result for one set of inputs, followed by another result from a change in inputs. Rather than manually copying and pasting the result, instead, we used a data table to get the result for both inputs simultaneously, allowing us to answer the question with relative ease (screenshot 3).
Screenshot 3: Data table used to solve the final question
I hope that you enjoyed reading through this solution. Please let us know your thoughts – did you solve the question in a different way? We will be answering one more question before the finals come around, so look out for the next blog!