DCF model speadsheet using this information (assumptions tab, tenancy schedule and risk analysis)

The current date for the purpose of this Assessment is December 31st, 2023.

Zedgreen Investments proposes to purchase an office building in the city of Kashyyyk, which is located in a jurisdiction in Oceania.

Your task is to produce a Discounted Cash Flow model based on the limited market information that has been provided. The general rule in the country is that significant investments are priced in Australian dollars.

Your model should be used to generate the IRR and NPV of the proposed investment. In particular, it should enable Zedgreen management to make a decision as to:

1. Which type of office investment in Kashyyyk holds the best opportunity going forward, and
2. What are the key parameters which will determine the success of the Kashyyyk investment?
3. What are the effects on the success of the investment of changing the assumptions for the Kashyyk market?
4. What would a typical Tenancy Schedule look like for an office investment in Kashyyyk?
5. How can the principal risks of the investment be reflected in the DCF model and how do changes in their values affect IRR and NPV?

This is an individual Assessment.

One document is required to be submitted for the Assessment, an Excel file.

You will need to construct a DCF model. You will need to:

1. Build an Assumptions sheet based on the data provided and then to model the NOIs, debt, NPV and IRR that flow from these assumptions
2. Introduce a Tenancy Schedule of your own making
3. Carry out risk analysis.

Kashyyyk Final Market Analysis: November 2023

Office Market Data

	Review
	Class A	Class B	Kashyyyk seemed to be slower to recover from the recession than other medium-sized cities in Oceania even before the revolutions and crises that began in December 2020, and it has never quite recovered as well as it perhaps ought to have done. Firms are now continuing to leave the country after the regime change in January 2022. The Class A CBD vacancy rate has further deteriorated, reaching a level not seen since December 2011. Class B vacancy rates are also at an historical high. Only one building is under construction in the CBD. Rental rates have also continued their fall with 2010 levels now being reached for new tenancies and the gap between Class A and Class B diminishing. Yet property operating expenses have continued to rise with inflation. Fresh property tax incentives have been introduced by the Kashyyyk Municipality in an attempt to reverse the tide, but these are not a sufficient incentive of themselves to revive the stagnant markety. Borrowing costs are currently around 5% annually for Class A office investments and 7% for Class B. Corporate taxes in Oceania have remained steady at 20%. It is not possible to utilise any form of partnership or other structure in order to avoid corporate tax. Oceania requires that funding for property investment of this size and nature is wholly sourced locally in Australian dollars. From an accountancy point of view, there are no capital allowances, depreciation is allowed on all buildings on a straight-line basis over 39 years, and all interest costs are tax deductible. Local tax authorities have recently been assuming that land constitutes half the value of all purchases in the CBD and a third outside it. Forecast The new Government has made several pro-business announcements and promised a fund for rebuilding, but international firms are still concerned about tax policy and the potential direction of policy. Isolated problems of law and order remain and are likely to continue for some months. A high level of vacancies and lower rents than in past years are inevitable but there is now considerable optimism being expressed by international agencies (e.g, Global Foresight) about the medium-term political prospects of the national economy. Growth is expected to return in 2024 and the property market should recover on the back of it in late 2025 and beyond. Taxes are not expected to change and interest rates are expected to remain stable, with a possibility that they may fall.
Inventory (m2)	CBD	Suburban	CBD	Suburban
Total	7,808,	8,311,	4,522	5,155,
Vacant	800,	1.100,	1,700,	1.400
Vacancy Rate	10.3%	13.2%	37.6%	27.2%
Rental Rates ($/m2)
Lowest	$15.5	$11	$12	$10
Highest	$22	$22	$20	$13
Average	$16.75	$15.50	$16.00	$11.50
Sales Prices ($/m2)
Lowest	$55.16	$51.60	$42.77	$38.75
Highest	$89.00	$76.00	$48.23	$45.45
Weighted Average	$67.55	$66.30	$45.45	$41.00
Operating Expenses ($/m2)	(Excluding Property Taxes)
Lowest	$3.1	$2.6	$1.8	$1.3
Highest	$3.3	$3.1	$2.4	$2.3
Weighted Average	$2.9	$2.7	$2.1	$1.9
Property Tax Expense ($/m2)
Lowest	$7.50	$3.00	$5.50	$2.00
Highest	$10.00	$5.50	$7.25	$4.00
Weighted Average	$8.75	$4.25	$6.20	$3.00

	Parking Ratio: *CBD - 1 per 1,100 m2 Outside CBD - 1 per 150 m2
	Operating Cost Escalation: Base Year
Market Rate of Return: Cap Rate: 7.0% IRR 12.0%	Mortgage Money Supply: Low, but tising Prime Source of Financing: Commercial Banks, Government grants. Estimated LTV: 60-70% maximum No secondary debt available
Leasing Activity Profile: Major Activity - Government, military Minor Activity - Major nationalised companies, Finance/Banking, Engineering/Architecture, Government, Energy	OUTLOOK: recovery in 2025/26 Vacancies Down 5% Landlord Concessions None Sales Class A CBD Up 10-15% Prices Outside CBD Up 10-20% Class B CBD Up 5-10% Outside CBD Up 10-15%
Source: Korpacz Real Estate Analysis, Short Smith Roome Kahyyyk 2023 Analysis