Overall Purpose and Description: Create an integrated financial projection model in Excel

for determining the purchase price to be paid to buy a complete infrastructure project.. The infrastructure project will be owned by a Special Purpose Company which is a subsidiary of the Infrastructure Company. As part of the assignment you are to present the financial Statements of the Special Purpose Company.

Part 1 submission: You must submit an Excel file which calculates the purchase price you can pay 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 part of the Excel document or a separate word document.

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:

Part1– Price Calculator / 15 marks in total
Calculating the correct purchase price / 10
Features of your calculator; use of Excel, formatting etc / 5
Part 2 – Financial Statements / 15 marks in total
Correctness of Statements / 5
Presentation of the Statements; use of Excel, formatting etc / 5
Discussion / 5

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

Detailed Objectives

  • You work for an Infrastructure Development Company (IDC). The company’s business is to purchase interests in infrastructure projects, to operate the infrastructure and to selectively sell infrastructure assets.
  • To isolate risk, each infrastructure asset the company buys, is purchased in a new special purpose company (SPC). The SPC’s only assets are the infrastructure project and any related agreement2. The SPC will have 2 shares for a dollar each, owned by the parent company. The $2 will remain in Cash for the life of the project. It will not be invested. It does prove ownership and entitles the owners to all/any upside in the company.
  • In this case, the Government is building a toll road called MessConnex. It is estimated to be completed in July 2019, although the month may vary. The Government is running an open tender, seeking bids to buy and operate the completed project. Subject to other constraints which can be ignored for this assignment, the sale will be awarded to the highest bidder.
  • The policy of IDC is to evaluate the project over a suitable holding period by projecting income and expenses over that period; and to estimate the value of the asset at the end of that period given its operating skills.The company has policies and procedures in place to estimate the value of projects at the end of the holding period. This value will be used in any analysis and it will be assumed that the project is sold for that value. (Outside the scope of this exercise, the IDC can make a decision at the time as to their best options including Sale, partial Sale or continue to hold).
  • For this tollway, the holding period will be 10 years.
  • Income will be from tolls. The Government has given estimates of traffic volume which IDC will confirm and/or modify for the projection. The government has set a maximum initial individual vehicle toll and a maximum annual growth rate as part of the Purchase Contract.
  • IDC as part of its operations, will collect all tolls and pay the total amount to the SPC at the end of each calendar month.
  • Expenses will be covered by a monthly payment from the SPC to IDC, paid on the last day of the month. Any variations from this monthly fee will be taken by the IDC using their management/operating skills. The monthly payments will be subject to escalation annually.
  • The SPC will finance itself by borrowing under two loans. One loan will be secured by the operating income. That is there will be monthly repayment paid out of the net operating income, and the loan will be repaid to 20% of the initial loan over the holding period. A second loan will be made by the parent company and is ‘psuedo’ Equity. It will be lent to the SPC by the parent (IDC) at a documented rate. This loan will be used to fund the purchase of the project and any upfront expenses, net of other loan proceeds. All net cash at the end of each month will be used by the SPC to repay the equity loan. Any surplus cash after the presumed Sale of the project and repayment of any loans, will also be used to repay the equity loan
  • Assume that the interest rates on all loans are fixed, with monthly compounding, and known at the time of the tender.
  • Assume that all annual increases will occur on the anniversary of purchase and not on a new calendar year.

For Part 1, Produce a monthly 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 purchase price to meet all of the criteria below.
  • The holding period will be a whole number of years up to 10 as a maximum, but 10 in this case.
  • Following a successful tender, ownership will start on the last day of the month (and monthly net operating income and possible sale will be on the last day of the month)
  • 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 government’s estimate of traffic volume they will lend; in the case of the asset lender they will also advise of the percent of the (current) asset value they are willing to assume at the end of the holding period (the total amount due to them).
  • The return required on the loan from the parent (equity) for this toll road is 15%.
  • 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 purchase to be paid by the SPC.
  • Your company will fund the toll road through the equity loan. If there is insufficient money after the sale of the toll road at the end of the lease, then the loan will not be fully repaid. If the toll road 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, IDC, (and so also the SPC) will have a 31 Dec year end
  • The company has a policy, inherited by the SPC, that all toll roads will be depreciated over 25 years to 10% of the purchase price.
  • Assume that there are no taxes (in the ‘real’ world this is, of course, not the case)

For the specific case to be submitted in the model, the assumptions are:

  • The project will be purchased and be operational on 31 Jul 2019
  • Estimated final value is calculate as a multiple of the final month’s net income. For this project, the multiple is 150.
  • Government monthly traffic projection is 31,200 cars per day in each direction, and vehicles travelling in both directions have to pay a toll.
  • To work out monthly income you can assume that there are 365 days in a year and 12 equal months in a year.
  • IDC estimates that first year will be 70% of Government estimate and second year will be 90%, achieving Government estimate in the third year. Here is no scope with the projected road size of exceeding the Government projection.
  • Initial capped toll is $3.50 per car with annual increases limited to 3%
  • Initial Expenses incurred prior to start of operations (i.e. required in the bidding process) are 1,500,000 and are to be written off over the holding period
  • Initial Monthly ongoing operating expenses are 600,000 per month with an estimated annual increase of 2% p.a .
  • Income lender will lend to 60% of the Government’s traffic volume at 7% p.a. based on monthly repayments and a final balloon payment equal to 20% of the amount it initially lent.

The primary aim of the monthly model is to calculate the price the company can pay to purchase the project.

To calculate this amount you will need at least:

  • the value of the project at the end of the holding period,
  • the amount that the Lender will lend,
  • the amount of the equity loan, and
  • the maximum amount IDC is prepared to bid to buy the project.

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 model if delivery dates could be any day in the month (and Income, Expenses 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.