New Century Coffee $($NCC$)$ Company is a $124-$store coffee retailer headquartered in San Jose and operating throughout $5$ western states. Its stores are known for their stylish and artistic atmosphere,quick and friendly service, and social consciousness. One glaring, but intentional, omission from each location is a drive$-$through service channel. The company$$s model is to establish locations that serve as a gathering place for customers, as opposed to more traditional quick$-$service models that focus on high volume and efficiency.

Although NCC does not want to alter its traditional storefront appeal $($nor does it want the expense of adding drive$-$throughs$)$ it does want to add additional revenue streams by reaching new customers and servicing existing customers more frequently. To do this, NCC is considering acquisition options to diversify its service channel. One opportunity that is under consideration was initiated by Portable

Coffee Express CEO Maria Diaz.

Ms$.$ Diaz founded and has grown Portable, a privately held company, into a flourishing $358$ location drive$-$through and walk$-$up coffee stand business. Portable has a strong presence in NCC$$s three largest states $($by location$),$ but no presence in the other two states NCC operates in$.$ However, Portable has

many locations in large cities across the country, including New York, Chicago, Boston, Washington D$.$C$.$and Philadelphia.

To determine the viability of an acquisition of Portable Coffee Express by New Century Coffee Company, Ms$.$ Diaz has provided recent financials for her company to NCC CFO Marisa Thompson. Mrs$.$ Thompson has run cash flow forecasts for the consolidated company and estimated incremental cash inflows after taxes from the acquisition over the next $5$ years $($the relevant time horizon for the analysis$).$ The

pertinent financial data is included in the tables below. The applicable corporate tax rate is $21\%.$

Table $1$: Estimated Incremental Cash Flows over

$5$ years for combined company

Year $1$ $ $19,400,000$

Year $2$ $ $19,100,000$

Year $3$ $ $20,100,000$

Year $4$ $ $22,550,000$

Year $5$ $ $17,100,000$

Table $2$: Portable Coffee Express Earnings

Year EPS Year EPS

$2013$ $ $1\mathrm{.}852017$ $ $2\mathrm{.}95$

$2014$ $ $2\mathrm{.}002018$ $ $3\mathrm{.}25$

$2015$ $ $2\mathrm{.}202019$ $ $3\mathrm{.}45$

$2016$ $ $2\mathrm{.}652020$ $ $3\mathrm{.}70$

Table $3$: Portable Coffee Express Balance Sheet $($December $31,2020)$

Assets Liabilities & Equity

Cash $ $3,000,000$ Current liabilities $ $3,600,000$

Accounts Receivable $2,000,000$ Mortgages payable $5,900,000$

Inventory $7,000,000$ Common stock $14,800,000$

Land $7,250,000$ Retained Earnings $9,700,000$

Fixed assets $($net$)14,750,000$ Total Liabilities & Equity $ $34,000,000$

Total Assets $ $34,000,000$

Table $4$: NCC and Portable additional data $($December $31,2020)$

Item NCC Portable

Earnings available for

common stock

$$29,000,000$ $$12,580,000$

Number of shares of

Common stock

$7,000,0003,400,000$

Market price per share

$(*$Estimated by NCC$)$

$ $43\mathrm{.}30$ $ $19*$

Questions

Complete the questions below using Excel. Show all calculations and formulas for quantitative solutions. For questions that require text$-$based responses, create a text box in Excel for each $($Insert$-$Text$-$Text Box$).$

$9)(10$ points$)$ Assume for this question that NCC could borrow $$20$ million from a commercial bank to help pay for the acquisition. The loan would be for $10$ years at an interest rate of $4\mathrm{.}5\%$ and require quarterly Principal and Interest payments. Use Excel functions $($not an amortization schedule$)$ to get your answers to parts A and B$.($Show all work$/$calculations$.)$

A$.$ What would be the quarterly Principal and Interest payment?

B$.$ What would be the interest portion of the $20$th payment?

C$.$ What would be the tax benefit $($in dollars$)$ of the $20$th payment?