A deli sells regular coffee, but decides they want to add Lattes to the menu. They expect to sell one Latte for $4.10. They want to evaluate this decision using a break-even analysis. They want to purchase three (3) Nespresso machines for the business to support this new product line. Details on costs are below. For this exercise, assume the following:

The useful life of the machine is 6-years

Assume no salvage value in 6-years

Discount Rate: 12%; Tax Rate: 24%

Costs: Each machine costs $260 one-time, upfront. The cost of the machines are not depreciated. To make each Latte drink, the machine requires a coffee pod which costs $1.10 per pod and frothed milk $0.30 per drink. They also require special cups, lids and other related expenses which cost $0.28 per cup in total. How many Lattes must they sell to break-even on a Financial Basis (NPV) Basis?

(1) Initially model your cash flows in Excel and calculate the NPV at 100 cups.

(2) Then, create a separate tab (copy the original) and calculate the Breakeven units using the goal seek function.

(3) Next, on another separate tab (copy the sheet from #2 to another tab) assume that taxes will rise to 25% in years 3, 4 and 5 and that pod costs will increase by 5% each year starting in year 2 (through 5).

Please explain steps and show on excel thanks