Name: Rawan Instructions Excel Cash Flow Analysis & Valuation Exam Using Excel, develop a 5-year discounted...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Name: Rawan Instructions Excel Cash Flow Analysis & Valuation Exam Using Excel, develop a 5-year discounted cash flow to solve the case problem below. Your Manager has asked you to analyze an investment opportunity by performing a five year Excel cash flow analysis with with associated investment returns to answer the five questions below. The subject property is a 100,000 s.f. medical office building with an asking price of $9,500,000. The property has doctors' practices as tenants and the suites range in size from 5,000 to 10,000 s.f. The building is currently 90% leased. Each of the tenants pay $15.00 s.f. on a triple-net basis and the rents increase 3% on an annualized basis. Below is a table with last year's operating expenses. The Landlord pays the expenses and is then reimbursed by the Tenants. The asset management and administrative fees cannot be passed through to the tenants per the lease terms. All expenses increase by 2% on an annual basis. In addition, please include $0.25 s.f. in capital reserves starting in year one below net operating income. Capital reserves should be increased by 2% in each of the subsequent years. The equity investors will contribute $3,000,000. Chase Bank will provide an acquisition loan of $6,500,000 for a five-year term on a twenty-five year amortization at 4.5% interest. The loan origination fee is 1% of the loan amount. REIMBURSABLE EXPENSES Real Estate Taxes Insurance Maintenance Repairs Landscaping Security Expense Statement (Previous 12 months) $79,000 $15,000 $27,000 $19,000 $ 6,000 $12,000 Pest Control Utilities Property Management Fee 3% of EGI (including reimbursable expenses) Administrative Fees Asset Management $ 2,500 $18,500 3% $ 4,000 $20,000 The company is only interested in acquiring the medical office building if it can obtain an annual 11% unlevered and 13% leveraged rate of return over a five-year holding period. At the end of the holding period, the property is anticipated to sell for a 9.5% terminal cap rate (applied to the sixth year NOI) and sale costs are expected to equal 4% of the gross sale proceeds. Please answer the following questions to help your company analyze this investment opportunity: 1. What is the going-in capitalization rate of the investment assuming an acquisition price of $9,500,000 and that year 1 NOI is calculated using "below the line" treatment of capital expenses? 2. What are the estimated net proceeds received from the sale of the property at the end of year 5 based on the assumptions discussed above? 3. 4. What is the maximum amount your firm should be willing to pay for the property based on a discounted cash flow analysis? What are the leveraged and unleveraged NPVS and IRRS generated by the property assuming an $9,500,000 acquisition price? 5. Should your firm invest in the Medical Office Building? EMAIL THE COMPLETED EXCEL SPREADSHEET AND THIS COMPLETED DOCUMENT BY 9:45 PM TODAY TO: Julie.Lynch@UTDallas.edu The file must have your name example: julielynch CF Excel Exam.xls and julielynch investment questions Name: Rawan Instructions Excel Cash Flow Analysis & Valuation Exam Using Excel, develop a 5-year discounted cash flow to solve the case problem below. Your Manager has asked you to analyze an investment opportunity by performing a five year Excel cash flow analysis with with associated investment returns to answer the five questions below. The subject property is a 100,000 s.f. medical office building with an asking price of $9,500,000. The property has doctors' practices as tenants and the suites range in size from 5,000 to 10,000 s.f. The building is currently 90% leased. Each of the tenants pay $15.00 s.f. on a triple-net basis and the rents increase 3% on an annualized basis. Below is a table with last year's operating expenses. The Landlord pays the expenses and is then reimbursed by the Tenants. The asset management and administrative fees cannot be passed through to the tenants per the lease terms. All expenses increase by 2% on an annual basis. In addition, please include $0.25 s.f. in capital reserves starting in year one below net operating income. Capital reserves should be increased by 2% in each of the subsequent years. The equity investors will contribute $3,000,000. Chase Bank will provide an acquisition loan of $6,500,000 for a five-year term on a twenty-five year amortization at 4.5% interest. The loan origination fee is 1% of the loan amount. REIMBURSABLE EXPENSES Real Estate Taxes Insurance Maintenance Repairs Landscaping Security Expense Statement (Previous 12 months) $79,000 $15,000 $27,000 $19,000 $ 6,000 $12,000 Pest Control Utilities Property Management Fee 3% of EGI (including reimbursable expenses) Administrative Fees Asset Management $ 2,500 $18,500 3% $ 4,000 $20,000 The company is only interested in acquiring the medical office building if it can obtain an annual 11% unlevered and 13% leveraged rate of return over a five-year holding period. At the end of the holding period, the property is anticipated to sell for a 9.5% terminal cap rate (applied to the sixth year NOI) and sale costs are expected to equal 4% of the gross sale proceeds. Please answer the following questions to help your company analyze this investment opportunity: 1. What is the going-in capitalization rate of the investment assuming an acquisition price of $9,500,000 and that year 1 NOI is calculated using "below the line" treatment of capital expenses? 2. What are the estimated net proceeds received from the sale of the property at the end of year 5 based on the assumptions discussed above? 3. 4. What is the maximum amount your firm should be willing to pay for the property based on a discounted cash flow analysis? What are the leveraged and unleveraged NPVS and IRRS generated by the property assuming an $9,500,000 acquisition price? 5. Should your firm invest in the Medical Office Building? EMAIL THE COMPLETED EXCEL SPREADSHEET AND THIS COMPLETED DOCUMENT BY 9:45 PM TODAY TO: Julie.Lynch@UTDallas.edu The file must have your name example: julielynch CF Excel Exam.xls and julielynch investment questions
Expert Answer:
Answer rating: 100% (QA)
To effectively analyze the investment opportunity presented in the case study you will need to perform a detailed Excel cash flow analysis Here are th... View the full answer
Related Book For
Auditing An International Approach
ISBN: 978-0071051415
6th edition
Authors: Wally J. Smieliauskas, Kathryn Bewley
Posted Date:
Students also viewed these finance questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
You have been assigned to the audit of the financial statements of Office Moving and Storage Limited (OMS) for its year ending December 31, 20X2. The company started 10 years ago and is in the...
-
An analyst expects that 20% of all publicly traded companies will experience a decline in earnings next year. The analyst has developed a ratio to help forecast this decline. If the company is headed...
-
Bruno SpA has decided to expand its operations. The bookkeeper recently completed the following statement of financial position in order to obtain additional funds for expansion. Instructions Prepare...
-
Carla Vista Corp. sponsors a defined benefit pension plan for its employees. On January 1, 2025, the following balances relate to this plan Plan assets $489,900 Projected benefit obligation 616,700...
-
Use information from Section 6.7 to estimate which form of electromagnetic radiation is the lowest energy ionizing radiation. Data from section 6.7 When we first introduced the concept of the...
-
Financial Statements Circus City issued an 8%, 10-year $2,000,000 bond to build a monorail mass transit system. The city received $1,754,217 cash from the bond issuance on January 1, 2012. The bond...
-
If $300,000 is to be saved over 15 years, how much should be deposited monthly if the investment earns 7% interest compounded monthly?
-
Jones Company Edward Jones started the Jones Company Limited (JCL) as a sole proprietorship. It was later incor-- porated; ownership is now 50 percent controlled by Mr. Jones, who is 71 years old,...
-
A water tank is in the shape of a cone with base radius 2 m and altitude 3 m. The circular base is flat against the ground. If the tank is full, which integral represents the work (in Joules) needed...
-
Identify four types of corporations excluded from PHC status.
-
Must an estate use the calendar year only for its tax year?
-
In determining the dependency exemption, how is the value of lodging provided a dependent computed for the support test?
-
Generally, how will a suspended loss attributable to a passive activity that is transferred by reason of death be treated for federal income tax purposes?
-
Explain the concept of exchanged basis as it relates to shareholders who exchange stock in corporations that are a party to a reorganization.
-
a) An item of plant and equipment was purchased on 1 April 20X1 for R100,000. At the date of acquisition its expected useful economic life was ten years. Depreciation was provided on a straight line...
-
Determine two different Hamilton circuits in each of the following graphs. A B F G
-
What are the three components of business performance analysis? Why does the auditor conduct business performance analysis?
-
Two PAs are discussing review engagements. A: As I see it, in a review we are providing a lower level of assurance, so we dont need the same extent of knowledge of the clients business and industry...
-
Assume you audited control compliance in the Kingston Company for the deviations related to a random selection of sales transactions, as shown in Exhibit EP 10-4. For different sample sizes, the...
-
A client who is a director of a publicly listed corporation is required by law to refrain from trading that companys stock at certain points of the year when disclosure of financial results are...
-
Consider the pairwise correlations of monthly returns of the following asset classes: Based solely on the information in the preceding table, which equity asset class is most sharply distinguished...
-
Investing the majority of the portfolio on a passive or low active risk basis while a minority of the assets is managed aggressively in smaller portfolios is best described as: A. The coresatellite...
Study smarter with the SolutionInn App