Reference no: EM131871126
FINANCIAL MODELLING TEST
INTRODUCTION -
This modelling exercise has been designed to test your approach to problem solving as well as your knowledge of Microsoft Excel. Your approach to the problem is as - if not more - important than the final product.
Please follow the instructions below, using the assumptions provided. Where you believe information is missing, please make your own assumption, clearly stating what this is.
Once you have submitted your model, we will arrange a meeting in which you will be asked to walk us through your thought process and the rationale behind this. You should come to this meeting prepared to discuss your model in detail, in particular any areas you found challenging.
CASE STUDY -
The ABC Group ("ABC") recently received the approval of the local council to build a nursery in XYZ town. ABC wants to build and operate a nursery, Little Toddlers Nursery ("LTN"), of 50 places (capacity).
ABC has secured the following agreements required to operate the nursery:
1. ABC must pay a ground land rent of £5k per annum to the local council.
2. A Construction contract from Build Uppon ltd ("BU"). Under the terms of the contract, the contractor will build the nursery and provide the required equipment. The construction period will be 12 months at a cost of £6m.
Immediately following the construction period, the nursery would operate. ABC has made the following assumptions:
1. Fees of £50/child/session
2. Opening times: 10 sessions/week, 50 weeks a year.
3. First year occupancy forecasted: 75%. A YoY occupancy growth of 5% is assumed.
After an initial operating period of four years, it is assumed that fees will be reviewed for the next four-year period. Fees will increase to an assumed £60/child/session.
Whilst operating, ABC is expected to incur the following operating costs:
1. Annual maintenance of £30k per annum
2. One-off maintenance every 4 years of £100k
The above costs are assumed to increase in line with the Retail Price Index ("RPI").
The owner of ABC, Sun Education ("SE") does not wish to pay for the construction of the nursery and is looking for an investor to buy ABC.
Investment Co Ltd ("ICL") is interested in acquiring ABC. They require a pre-tax IRR of 15%.
INSTRUCTIONS -
You have been asked by ICL to help them determine the price they are willing to pay for ABC. Your job is to create a simple financial model in order to calculate the acquisition price which ICL can afford to pay, whilst still meeting their return requirement.
1. The model should have separate sheets for Inputs, Calculations and Outputs
2. All inputs should be clearly identifiable
3. Calculations should be annual and cash based. No accounting or tax adjustments are required as ICL are targeting a pre-tax IRR.
4. Calculation rows should use consistent formulae across the entire row.
5. Outputs should, at a minimum, show the total project cost, the pre-tax IRR and the payback period. Please include any additional information/charts which you think would be helpful.
6. Assume that ICL may use debt to fund part of the transaction and ensure the model has the ability to fund using a combination of debt and equity.
7. Free cash flow is assumed to be distributed to the investor.
8. RPI is assumed to be 2.5% p.a.
To accompany the model, please produce a short Word report, summarising the opportunity and your conclusion regarding the appropriate bid price. Include key assumptions and highlight any risks which you have identified, as well as any further work or questions you would need answered to refine the bid price.