Please answer the following questions by developing an Excel worksheet. Highlight your answers on the spreadsheet. Your completed worksheet will be turned in for this assignment.

Verratti lnc$,$ owns a vacant parcel of land and is trying to determine whether to build a retail shopping center. Using the following information, answer the questions below:

RETAIL SHOPPING CENTER

Building Size$125,000$ net leasable square feet

Land Cost$$$$1,500,000$

Construction cost per square foot : $$43$

Rent per square foot of leasable area : $$9\mathrm{.}50$ per square foot of leasable area in year $1$increasing at $6\%$ per annum thereafter

Common area maintenance charges$($CAM$$s$)$ : Each tenant pays $85\%$ of their pro$-$rata share of all expenses and taxes incurred based on the proportion of net leasable square footage to all rentable area

Real estate taxes : $$1\mathrm{.}29$ per square foot of leasable area, increasing at$5\mathrm{.}5\%$ annually thereafter

Vacancy : $12\%$ in year $1,15\%$ in year $2,13\%$ in year $3,$ and $8\%$in year $4$

Operating expenses : $$3\mathrm{.}25$ per square foot of leasable area in year $1,$ growing $6\%$ annually

Depreciation : $39$ years

Mortgage information : $75\%$ loan$-$to$-$value ratio, $8\mathrm{.}5\%$ interest for $25$ years, compounded annually $($annual payments$)$

Sales information : Property will be sold at End of Year $4$ for ten times NOl

Selling expenses : $4\%$

Required rate of return : $12\%$

Investor's marginal income tax rate : $36\%$

Capital gains tax : $20\%$

Depreciation recapture rate: $25\%$

$1.$ Estimate the before$-$tax cash flow for the property for years $1$ through $4.(8$ points$)$

$2.$ What proportion of the before$-$tax market value is from cash flows and what proportion is from reversion? $(3$ points$)$

$3.$ What is the value of the property using the Discounted Cash Flow $($DCF$)$ Method? $(4$ points$)$