2017 Round 1: When it Rains it Pours

Description
This workbook is taken from the First Round of the 2017 ModelOff financial modeling world championships.

Follow the instructions in order to complete the model:

Your company wishes to better understand how its financing cashflows would be affected in times of cash shortage. They have asked you to model their two senior debt facilities, two reserve accounts and
shareholder loan, and then test how the debts are repaid and reserves are funded under different profiles of cash generated from operations.

Your model should be semi-annual with semi-annual periods ending in June and December. All debt
payments and reserve account transfers occur at the end of a semi-annual period. The model should take
as its starting point the debt and account balances on 31 December 2017, and should run through to 31
December 2035.

Questions 29 to 34 relate to the target payments and balances and can be answered based on the
details given for the individual facilities and accounts.

Questions 35 to 39 relate to the actual payments made under five different profiles of available
cash, and will require you to have implemented the priority of payments.

The cash profiles you are required to test are increasingly restricted, such that under the first profile everything is fully funded and target balances are met, and under the fifth profile an overdraft is drawn.

Prepare your model and then use it to answer the given questions.

Allotted Time: 45-65 minutes

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