Question
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
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started