My company and its investors are considering putting in an offer on an office building located in the suburbs of Grand Rapids. The property$/$building has the following attributes:

$24,000$ square feet of total rentable space on three floors with each floor offering $8,000$ square feet of rentable space. There are a total of $12$ suites ranging in size from $500$ square feet to $3,000$ square feet. The building was built in $2015.$

$100\%$ fully leased $($No current vacancy$).$ While the building is fully occupied as of now, there is some risk that tenants will vacate as the office market continues to soften post$-$Covid. To account for the risk of turnover or tenants struggling to pay their rent, we will incorporate a $10\%$ turnover$/$vacancy loss in our estimates. This loss will lower our overall revenues for the property.

$$ Rental rates vary depending on floor. $1$st floor averages $$16/$sqft$/$yr; $2$nd floor average $$18/$sqft$/$yr and the $3$rd floor averages $$20/$sqft$/$yr$.$ Rental rates are projected to increase annually by $4\%$

$$ Operating expenses for the building are as follows: Property Management Fees of $$0\mathrm{.}50/$sqft$/$yr$,$ Common Area Expenses including water$/$sewerage are $$2\mathrm{.}00/$sqft$/$yr$,$ property taxes are $$2\mathrm{.}00/$sqft$/$yr and insurance expenses are $$0\mathrm{.}25/$sqft$/$yr$.$ Gas and Electric expenses are paid directly by the tenants through their own separate meters. The building operating expenses above are projected to increase $3\%$ per year for the entire analysis period.

$$ Because this is an office building, the Tenants all have Gross leases $-$ Landlord is responsible for all operating expenses except for the utility expenses which are paid directly by the tenants.

$$ The existing lease terms vary and there is some evidence from our due diligence efforts that collections will be an issue with a few tenants. We will assume an additional bad debt$/$collection reserve of $3\%$ of the total net revenues as bad debt expense for our analysis.

$$ Given its recent construction, the building is in decent shape from a deferred maintenance standpoint so we will only reserve $$0\mathrm{.}50/$sqft$/$yr for building related capital expenditures. This deferred maintenance assumption will increase by $$0\mathrm{.}05/$sqft$/$yr$.$

$$ We do not anticipate any other non$-$operational or ancillary income like interest income but we do feel our legal and accounting costs$/$expenses will be approximately $$15,000$ per year.

$$ To account for upcoming vacancy issues and the replacement costs of tenants, we will establish a tenant improvement and lease commission reserve of $$1\mathrm{.}00/$sqft$/$yr and this will increase by $$0\mathrm{.}10/$sqft$/$yr$.$

$$ The property is listed at $$2\mathrm{.}8$ million. We would project holding the building for $5$ years and selling the building after the five year period for $$3\mathrm{.}6$ million.

$$ Purchase costs will total $1\%($$$28,000)$ and Sales Costs will total $3\%($$$108,000)$

$$ For simplicity, we will assume that depreciation will be determined using straight$-$line depreciation over a $40$ year time period. Assume a tax rate of $25\%.$

$$ Debt$/$Equity Info:

$70\%$ LTV $$ Loan to Value. Loan amount will total and not exceed $$1,960,000.$

$$ The remainder of the funds needed to purchase will be equity funds from a pool of investors who seek a return on their equity of $15\%.$

$$ The debt is an Interest only debt arrangement

$6\mathrm{.}25\%$ interest rate

From this information, I would like you to do the following:

$1)$ Develop a full five year projected income statement $($pro forma$)$ for this project,

$2)$ Determine the cash flow generated by this project for each of the five operational years,

$3)$ Determine how much equity is needed to buy the property in Year $0,$

$4)$ Determine the cost of capital we should use to assess the purchase of this property,

$5)$ Determine the net proceeds from selling the property after five years of operations $($Watch out for tax implications$),$ and

$6)$ Using NPV and IRR, determine if this property is a good buy for our company and investors.

Please show all work and calculations in Excel. Numbers $1$ and $2$ should show all $5$ years.Pro Forma Income Statement:

$\backslash$table$[[-,,$lear $1,,$Year $2,,$Year $3,,$Year $4,,$Year $5],[$Total Rents,$$,432,000\mathrm{.}00,$$$,449,280\mathrm{.}00,$$$,467,251\mathrm{.}20,$$$,485,941\mathrm{.}25,$$$,505,378\mathrm{.}90$