Financial Statement Analysis And Modelling
Financial Statement Analysis & Modelling
Session 1 2017
Overall Purpose and Description: Create an integrated financial projection model in Excel
for determining the rents to be charged for the lease of an aircraft to an airline from an Aircraft Leasing Company. The aircraft will be owned and leased from a Special Purpose Company which is a subsidiary of the Aircraft Leasing Company. As part of the assignment you are to present the financial Statements of the Special Purpose Company.
Assignment submission: You should submit your assignment in iLearn at
https://ilearn.mq.edu.au/login/MQ/. There are instructions on how to submit your assignment in the Major Assignment section of the Financial Statement Analysis & Modelling iLearn site.
What to submit: The Assignment is in two parts thus requiring two submissions on two different dates. Extra information will be released after submission of Part 1 that you will need in order to do Part 2.
Part 1 submission: You must submit an Excel file which calculates the rent you can charge for a specific set of assumptions.
Part 2 submission: You must submit an Excel file with the Financial Statements for the Special Purpose Company containing I/S, B/S and Cash Flow Statement (CFS). For the second part you will also include a discussion which can be par of the Excel document or a separate word document.
In the Inputs sheet (or similar) in both documents please include your name and student ID.
Submission date: Part 1 and Part 2 – Please see iLearn for details
Penalty for late submission: If you have extenuating circumstances that prevent you from submitting your assignment by the due date, please make arrangements with your lecturer prior to the due date. Unless prior arrangements have been made, any late submission of case studies will automatically be penalised. In the absence of special circumstances, a zero mark will apply.
Nature: This is an individual assignment. General discussion of the topic is allowed but the final conclusions and written submission must be your own work. Please do not share files or written work with other students. Make sure you are also following the discussion of the assignment as it develops on the Financial Statement Analysis & Modelling iLearn site.
Copies: Students should retain a back‑up copy of their assignment submission.
Value: The assignment component of the assessments is worth 30%. Marks will be awarded
for the following features:
Part 1 – Rent Calculator
15 marks in total
Calculating the correct rent
Features of your calculator; use of Excel, formatting etc
Part 2 – Financial Statements
15 marks in total
Correctness of Statements
Presentation of the Statements; use of Excel, formatting etc
The marker reserves the right to allocate the marks for use of Excel and formatting in Parts 1 and 2, at his/her own discretion.
*** IMPORTANT ***
In completing this assignment, you must not in any way use the work of any other person and present it as your own. You must always clearly and accurately acknowledge the source of any material you use. In particular, you must NOT without clear and accurate acknowledgement:
· Copy material (including Audio‑visual or computer based material);
· Use another person’s concepts, results, or conclusions; or
· Summarise another person’s work.
If you infringe these rules or encourage or assist another person to infringe them, severe penalties can apply (see the University’s policy on plagiarism http://www.mq.edu.au/academichonesty).
· You work for an Aircraft Leasing Company. The company’s business is to purchase aircraft that an airline has ordered and lease it to the aircraft for a period up to 10 years in return for monthly rents (paid at the end of each month of the contract).
· The airline has ordered the aircraft and so the price is settled. The airline nominates the start date and term and seeks competitive tenders for the lease and, all things being equal, the tender will be won by the company that offers the lowest rent.
· At the end of the lease it is the responsibility of the company to either sell the aircraft or find a new Lessor (someone else to lease the aircraft). The airline may or may not be interested in leasing or buying the aircraft. The company can not assume the value of the aircraft at the end of the lease will be guaranteed in any way by the airline.
· The company has policies and procedures in place to estimate the value of the aircraft at the end of the lease. This value will be used in any analysis and it will be assumed that the aircraft is sold for that value. (Outside the scope of this exercise, the Aircraft Leasing Company can make a decision at the time as to their best options).
· To isolate risk, each aircraft the company buys, is purchased in a new special purpose company (SPC). The SPC’s only assets are the aircraft and the lease agreement. The SPC will have 2 shaes wrth a dollar each, owned by the parent company. The $2 will remain in Cash for the life of the lease. It will not be invesed. It does prove ownership.
· The SPC will borrow three loans. One loan will be secured by the lease rentals. That is there will be equal monthly repayments paid out of the monthly rentals to repay the loan over the 10 year term. The other loan will be secured by the value of the aircraft. Interest on the loan will be compounded each month and the total amount will be repaid at the end of the lease either out of proceeds of the sale of the aircraft or by re-financing. A third loan will be for the Equity. It will be lent to the SPC by the parent at a documented rate.
· Assume that the interest rates on all loans are fixed and known at the time of the tender.
· As a result the rent you quote will be fixed for the term of the lease.
For Part 1, Produce an Excel Model that can be used as a template and enable the company to quote on all tenders they are offered. To simplify the calculations for this assignment, you will assume that:
· The aim of the model is to calculate the rent to meet all of the criteria below.
· The lease term will be a whole number of years up to 10 as a maximum
· All leases will start on the last day of the month (and rentals and possible sale will be on the last day of the month)
· The airline will provide you with the aircraft cost and the lease term
· Lenders will provide you with an interest rate; in the case of the lender secured against the rentals, they will also advise the maximum percentage of the aircraft value they will lend; in the case of the aircraft lender they will also advise of the percent of the (current) aircraft value they are willing to assume at lease end (the total amount due to them).
· The return required on the loan from the parent (equity) is 12%.
· For both ‘external’ lenders and the equity loan, you can assume for this assignment that interest for a month is calculated as if one month was 1/12 of a year.
· There will be fixed expenses at the beginning of the lease to be paid by the SPC.
· Your company will fund the aircraft through the equity loan. If there is insufficient money after the sale of the aircraft at the end of the lease, then the loan will not be fully repaid. If the aircraft is sold for an amount in excess of the valuation, then the equity loan will be paid out and the remaining cash paid to the owner as a dividend.
· The company (and so also the SPC) will have a 31 Dec year end
· The company has a policy, inherited by the SPC, that all aircraft will be depreciated over 25 years to 10% of the purchase price.
· Assume that there are no taxes
For the specific case to be submitted in the model, the assumptions are:
· The lease will commence on 30 Sept 2017
· Aircraft cost 40 million, lease term 9 years, estimated final value 28 million
· Expenses are 500,000
· Rental lender will lend to 65% of the initial aircraft cost at 7% p.a.
· Aircraft lender will lend at 9% p.a. up to a final value of 45% of the initial aircraft cost
For Part 2, based on the common downloaded Pricing Model, produce a set of Financial Statements for the SPC ( I/S, B/S and CFS). For the Cash Flow Statement, use the Direct method for Operating Cash Flows. The download will be available after all Part 1’s have been submitted and will ensure that everyone is working from the same starting point.
Also include a discussion on what you would have to change in your models if delivery dates could be any day in the month (and rentals and Sale all occur on the same day of each month, as far as possible), and if interest is calculated using the actual number of days and not just 1/12 of a year.