You are required to value a new business, West Pentire Apartments Ltd, which involves the acquisition of a new complex of residential apartments to let. You must forecast the annual financial statements over a 6-year horizon, and perform a discounted cash flow valuation of the business. Further information about the business is provided below. Non-Current Assets: - Land and improvements: 6,820,000 - Buildings and improvements: 11,100,000 - Furnishings and equipment: 214,000 - Furnishing and Equipment has a useful life 6 years - Furnishing and equipment has no residual values - The straight-line depreciation method is used Equity and Long-Term Debt: - Long-term loan: 7,500,000 - Number of years: 25 years (repaid in equal installments) - Interest rate: 6.0% per annum - Equity (capital contributed by the owners): 11,500,000 - Number of shares outstanding: 12,000 - Expected return on equity: 10.5% per annum Income - Apartment units: - Two-Bedroom apartment: 55 units - One-Bedroom apartment: 150 units - Studio apartment: 85 units - Monthly rents for the first year: - Two-Bedroom apartment: E1800 per month - One-Bedroom apartment: 945 per month - Studio apartment: 650 per month 5 - The expected vacancy rates are shown below: - Monthly rents are expected to increase each year as shown below: Expenses - The operating expenses for the first year are shown below: Expenses - The operating expenses for the first year are shown below: - Management fees are equal to 5.0% of Effective Gross income (EGI). EGI is defined in the financial statement structure provided in AppendixC. - Property taxes are expected to increase to 205,000 for Year 5 onwards. - All other operating expenses (i.e. excluding management fees and property taxes) are expected to increase each year at 2.0% per annum. Other Information - All long-term debt and equity will be issued by the end of Year 0. All capital expenditures will be made by the end of Year 0 . - The tenant deposit amounts for each year are shown below: 6 - Each year, the closing receivables balance is 5.0% of EGI, and the closing payables balance is 5.0% of operating expenses excluding depreciation. - Corporation tax is equal to 20% of income before tax. The business only pays corporation tax if it is making a profit. - The company's weighted average cost of capital (WACC) should be calculated using book values for Year 0. Assume that the company's WACC is constant each year. - After Year 6 , the free cash flow of the business will grow at the perpetuity growth rate of 2.0% per annum. 3. Create a new worksheet named "Scenario Analysis" and perform a scenario analysis to examine net income and the closing cash balance under the three scenarios shown below. Provide two suitable charts: one for annual net income; and one for the annual closing cash balance. Your model and charts should update when the scenarios change
