2017 Round 2: Late Again

Description
This workbook is taken from the Second Round of the 2017 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You are employed by a local provider of heavy rail passenger services called ModelOff Trains (“MOT”).
You have been provided 5 weekdays of data on train performance and need to conduct analysis on the
data for senior management at MOT. There are three parts to the required analysis:

Part 1) Create a full schedule of all 840 data points that MOT collect daily (questions 14 - 15).

Part 2) Clean up the data provided from the MOT systems (of the actual time) into a form usable for
analysis(questions 16 - 19).

Part 3) Answer certain questions put to you by MOT senior management (questions 20 - 24).

The rail network has 3 lines. These are the Blue line (3 stations), Red line (6 stations) and Yellow line
(5 stations). All stations feed a central transport hub known as ‘H’. Travel occurs in both directions with
two sets of tracks per line (one track inbound, one track outbound). Travel towards ‘H’ is referred to as
Inbound and traffic away from it as Outbound.

There are four periods each day when service is provided (AM, Mid, PM and Evening). The frequency
within each period is provided. For example, for the Red Inbound AM service, the first train departs M at
7am and runs every 20 minutes. Therefore services will depart M at 7:00am, 7:20am, 7:40am, 8:00am,
8:20am and 8:40am. The first Mid service departs at 9:00am.

Allotted time: 50 - 75 minutes

Once finished, feel free to upload your model to your own dedicated author channel!