Financial Modeling Using Excel Assignment- UTS

Assignment Task

Executive Summary

The purpose of Assignment 2 is twofold.

Firstly, you will need to show that you have the skills and understanding to build a simple cash flow model in Excel. The intention is to workshop the initial build in the forthcoming workshop.

Secondly, you will use the model you created to evaluate the input sensitivities by adjusting the input parameters to answer the questions raised in this brief – “Sensitivity Investigations”. Your submission for this assignment should contain the Excel model and the completed Sensitivity Investigations form.

Background

The scenario you will be modelling is based on a proposed industrial development known as Lot 109/1.

The development will be the first of four buildings to be constructed on Lot 109 and comprises approximately 1,000sqm of office space split into four distinct areas, plus workshop/warehouse space of 2,000sqm also designed to be subdivided.

Attached to this Brief are the architectural drawings and a cost summary, both of which were lodged with the planning authority as part of the development application for this development.

In order to focus on the modelling rather than an in depth development critique, certain assumptions are put forward which would normally be questioned and are commented on in this Brief. The scenario is based on the potential acquisition of land with the benefit of a Development Consent and a builder who will reportedly carry out the construction works in line with the cost summary. With the intention of having some similarity between the models produced, a suggested structure for the models is put forward, as well as a list of initial model assumptions to be adopted as a starting point.

The models are to be built using Excel

The suggested structure put forward below is the structure adopted in the example model that will be presented in the workshop session. Each of the sheets in the model will be discussed, including their links and the formulas used.

The intention is for all to have created a working model by the close of the workshop session

It is suggested that the model uses four sheets in Excel as follows:

Input & Summary Sheet

• This sheet is where sensitivity adjustments are made to the model, that will be required for the second part of the assignment. It contains four blocks of information Cost Assumptions
• Which is used to calculate total acquisition costs and total construction costs. These include the cost summary estimate plus additional construction costs Revenue Assumptions
• For each of the potential revenue generating areas a face rental figure plus incentives can be inputted, plus any additional income Outputs
• This shows the NPV and the IRR the model generates.
• Other Model Assumptions – This is where inputs are made for Rent Reviews, Discount Rate and Terminal Yield. Income Sheet
• A DCF Sheet showing for each income-producing area the annual income and the corresponding Net Present Values derived from the Input and Summary Sheet 
• Costs Sheet The costs are Discounted Cash Flowed in three groups
• Land Acquisition, Construction and Incentives.Consolidated Sheet
• This shows the costs and revenue with the resultant cash flow, and is used for the basis of the IRR Calculations

Questions

1. Using the initial assumptions what are the model outputs?

a. NPV

b. IRR

2. Your leasing agents have advised that the office market has weakened they are suggesting office face rents of $415psm and workshop rents of $195psm applying these changes what values do you now get for the:

a. NPV

b. IRR

3. With these new rental forecasts in the model – what land purchase rate could you afford to pay to the nearest $2.50psm to derive a positive NPV? (Please provide your answer to 2 decimal places i.e. ($123.45)

4. The agents have now come back again, this time they are saying the rent reviews are too high, they are above market at the initial assumption of 4%. 

5. Having considered the advice from the agents and the impact, it is decided to set the rent reviews to 3.5%. That said, to make the feasibility acceptable, and in recognition for lowering the rents and the rent reviews, you have decided to withdraw the fitout contributions and reduce the rent free incentives. Assuming all tenancies get the same rent free period – what is the maximum number of rent free months (dealing in whole months only) you can offer to achieve a positive NPV, and what is the IRR?

6. Your agents have some good news and some bad news. They have found a tenant that want to take the whole building – as a result the lobby areas on both floors are now part of the lettable area of the building – which is the good news. The bad news is that they will only pay $395 for the office space (which includes the lobbies – warehouse stays unchanged) and they want 2 months rent free as an incentive. Assuming there is no change in the construction costs, what does the model show as the outputs for this one tenant scenario?

a. NPV?

b. IRR?

7. You are close to agreeing terms with the new whole of building tenant – and you are aware that they have entered into another lease elsewhere with rent reviews higher than 3.5%, reportedly in the 3.5% to 4.0% range. This is good because the existing land owner has just rejected your lower land purchase rate and is insisting on $420psm for the land.

8. Setting the model to the now agreed rent review rate of fixed 3.75%, and the land rate at $420psm, your boss has just come round to your desk and asked what has the greatest and what has the least impact on the model, and asked you to consider individual 0.5% changes to the Rent Reviews Rate, Discount Rate and Terminal Yield. Remembering to reset the values back after each adjustment, identify which of the following has; “Greatest Impact”, “Middle Impact” or “Least Impact” in terms of NPV and IRR

9. To make sure we are aligned, the Rent Reviews should be at 3.75%, Discount Rate 8% and Terminal Yield 7.25%. Your builder has just told you for an extra $500k he can have the building built in one year not 2. Is it worth while paying an additional $500k to the builder, noting you should allow for the same percentage additional costs for contingency and project management as well?

10. You have decided that you are not going to take up the builder’s offer – you have concerns about the quality of the building if it is accelerated to that extent, so the feaso need to reflect the original 2 years for the building works. The new whole of building tenant has just called – they want 50 additional car parking spaces close by. You have available land, you know it cost $8,000 per space to build car parking and the tenant is willing to pay $8 per working day (220 working days per year). Put this new income and costs into the feaso so you can compare the NPV and IRR with and without the new 50 car parking spaces. (Side Note – the car park costs do not attract additional contingency and/or project management fees)

11. Your boss is back at your desk and just asked “So what are the most sensitive inputs for this model, what has the greatest impact on the model outputs?

This Computer Science has been solved by our PHD Experts at My Uni Paper.