2017 Finals: Castles in the Air

Description
This workbook is taken from the First Round of the 2017 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You are working for a company which is considering purchasing a number of properties. You have been
asked to model each of the available investments to assist in choosing a portfolio (up to a maximum
purchase price of $1,700,000) that maximises the value to the company, as measured by an increase in
net present value. The company’s cost of capital is 8%.

Full details of the investments may be found on the table on the subsequent page
• The model should be monthly. For NPV purposes assume that all payments occur at the end of the month and use the XNPV function.
• The purchase price for each property should be paid on 31 December 2017.
• The company holds the property for a number of years (the investment length).
• During the investment length, the company receives rental revenue and pays operating costs.
• Where amounts are indexed the base date is 1 January 2018 and the index should step annually (i.e. a full year of indexation should first be applied on 1 January 2019). Do NOT round inflated prices to whole cents in interim calculations.
• At the end of the investment length, the company will sell the property for the terminal value. The terminal value is not indexed.
• For property 4, the company has the option of overhauling the property. Details of the property without overhaul are listed under property 4a, Details of the property with overhaul are listed under property 4b. The overhaul cost should not be considered in the purchase price constraint.
It is NOT possible to invest in both property 4a and property 4b. The overhaul cost (which is not indexed).

Allotted time: 30-45 minutes

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