2015 Round 2: More Money Please

Description
This workbook is taken from the 2015 Modeloff financial modeling world championships.

Follow the instructions in order to complete the model:

INTRODUCTION

You work for a Project Company that has an existing senior debt facility which is due to be refinanced. The company already has an annual financial model covering the period from 1 January 2015 to 31 December 2039, which has been provided to you. The model forecasts the project’s cashflows, including the existing senior debt. Your manager has provided this spreadsheet to you and asked you to update it to include the upcoming refinancing, and to amend the project’s forecast financial statements to account for this.

PART A

The inputs below are provided in the “Inputs” worksheet of the provided model. Terms of the refinancing debt are as follows:

 The facility will be drawn in one single drawdown on 31 December 2016.
 An arrangement fee of 2% is payable on the amount drawn.
 The amount drawn will be equal to the sum of:
o The balance of the existing senior debt after the repayment on 31 December 2016 is made and;
o The arrangement fee amount.
 The drawdown will be used to pay the arrangement fee and repay the existing debt.
 Repayments will be made at the end of each calendar year, apart from in the final year when the repayment is made on the final repayment date.
 The final repayment date should be 30 June 2036, and the debt should be repaid in full on this date.
 The repayment amount in Year N = Opening Balance at start of year * [Days in year N for which refinancing debt is active] / [Total days from the start of Year N until the debt maturity date inclusive]. Repayments start from the 2017 year (i.e. there is no repayment relating to 31 December 2016).
 The interest rate for the loan is 6% and interest should be calculated on an actual/365 basis.

The company should account for the debt as follows:

 Interest should be expensed through the Profit and Loss accounts as incurred.
 The arrangement fee should be amortized through the Profit and Loss accounts over the life of the loan according to the following formula:
o Amortization charge in Year N = Fee Charged * [Days in year N for which refinancing debt is active] / [Total number of days across all years for which refinancing debt is active]
 On the Cash Flow Statement, the arrangement fee should be incorporated after the Project Cashflow line, and it should feed in to the Dividends calculation.
 The project will hold on the balance sheet both the refinancing debt balance, and an asset relating to the arrangement fee.
ModelOff 2015 – Round 2

PART B

Your manager has also asked you to calculate a Debt Service Cover Ratio (“DSCR”) for the refinancing debt to show how much buffer the project has in its cashflows above the amount required to pay the interest and repayments for the refinancing debt.

In each year that the refinanced loan is active (excluding 2016) this DSCR should be calculated as:

Numerator: Project cashflow (as displayed on the Cash Flow Statement) Apportioned for the number of days in the year that falls within the term of the refinancing debt.

Denominator: Interest and Principal Repayments made for the refinancing debt during the year. You will also need to calculate a minimum and average DSCR. The minimum DSCR should be the minimum of all the DSCRs over the term of the refinancing debt, and the average DSCR should be the average of all the DSCRs over the term of the refinancing debt.

You should answer question 6 before proceeding with the remainder of Part B.

On seeing the minimum DSCR that the project achieves, your manager tells you that this is higher than the banks require – they are willing to lend money to the project as long as the DSCR remains at or above 1.80 in each year. As the DSCR is well above this level, the project could afford to make larger repayments in each period, and therefore the initial drawdown could have been larger.

Your manager wishes to amend the model so that as much debt is borrowed as possible given the constraint that the DSCR cannot ever be below 1.80. Any excess amount drawn over and above the amount needed to repay the existing senior debt and pay the arrangement fee should be distributed
immediately to the shareholders. To this end, you have been asked to adjust the drawdown amount and the debt repayments (which may now vary independently in each year) so that in each year a DSCR of exactly 1.80 is achieved and the debt is repaid in full on the final repayment date.


Allotted time: 35 minutes

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