Uncle's Caf sells regular coffee, but decides they want to offer Latte's to the menu. They...

 

Transcribed Image Text:

Uncle's Café sells regular coffee, but decides they want to offer Latte's to the menu. They expect to sell one Latte for $4.00. They want to evaluate this decision using a break-even analysis and have hired you to do the work! They want to purchase two (2) 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 5-years Assume no salvage value in 5-years Discount Rate: 14%; Tax Rate: 24% Costs: Each machine costs $220 one-time, upfront. The cost of the machines are not amortized. To make each Latte drink the machine requires a coffee pod which costs $1.00 per pod and frothed milk $0.20 per drink. Cups, lids and other related expenses per cup are $0.20. How many Lattes must Uncle's sell per year to break-even on a Financial Basis (NPV) Basis? (1) Initially model your cash flows in Excel and calculate the NPV at 100 cups per year. (2) Then, create a separate tab (copy original) and calculate the Breakeven units using the goal seek function. Use the "Coffee Express" solution in your review problem sets plus the other resources at your disposal for guidance (notes, video lessons, etc...) Goalseek video link (3) Next, on another separate tab (copy #2) assume that taxes will rise to 35% in years 3, 4 and 5 and that pod costs will increase by 5% each year starting in year 2 (through 5). Uncle's Café sells regular coffee, but decides they want to offer Latte's to the menu. They expect to sell one Latte for $4.00. They want to evaluate this decision using a break-even analysis and have hired you to do the work! They want to purchase two (2) 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 5-years Assume no salvage value in 5-years Discount Rate: 14%; Tax Rate: 24% Costs: Each machine costs $220 one-time, upfront. The cost of the machines are not amortized. To make each Latte drink the machine requires a coffee pod which costs $1.00 per pod and frothed milk $0.20 per drink. Cups, lids and other related expenses per cup are $0.20. How many Lattes must Uncle's sell per year to break-even on a Financial Basis (NPV) Basis? (1) Initially model your cash flows in Excel and calculate the NPV at 100 cups per year. (2) Then, create a separate tab (copy original) and calculate the Breakeven units using the goal seek function. Use the "Coffee Express" solution in your review problem sets plus the other resources at your disposal for guidance (notes, video lessons, etc...) Goalseek video link (3) Next, on another separate tab (copy #2) assume that taxes will rise to 35% in years 3, 4 and 5 and that pod costs will increase by 5% each year starting in year 2 (through 5).

Answer rating: 100% (QA)

1 Computation of NPV at 100 cups per year Sale quantity 100 Sale price 400 Costs pod 100 frothed mil... View the full answer