Assignment: Financial Statement Analysis &Modelling

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.

Detailed Objectives

• 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 shaeswrth 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.