EXCEL Project: $$Uncles Caf$$ EXCEL Project: "Uncles Caf$"$

Uncle's Deli$/$Caf$$ sells regular coffee, but decides they want to offer Lattes to the

menu. They expect to sell one Latte for $$4\mathrm{.}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: $15\%$; Tax Rate: $29\%$

Costs:

Each machine costs $$220$ 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\mathrm{.}00$ per pod and

frothed milk $$0\mathrm{.}20$ per drink. Cups, lids, and other related expenses per cup are $$0\mathrm{.}20.$

How many Lattes must Uncle's sell to break$-$even on a Financial Basis $($NPV$)$ Basis?

You will create $(3)$ different spreadsheet tabs

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

$(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)$ run another breakeven NPV$.$ For this analysis,

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 Deli$/$Caf$$ sells regular coffee, but decides they want to offer Lattes to the menu. They expect to sell one Latte for $$4\mathrm{.}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: $15\%$; Tax Rate: $29\%$

Costs:

Each machine costs $$220$ 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\mathrm{.}00$ per pod and frothed milk $$0\mathrm{.}20$ per drink. Cups, lids, and other related expenses per cup are $$0\mathrm{.}20.$

How many Lattes must Uncle$$s sell to break$-$even on a Financial Basis $($NPV$)$ Basis?

You will create $(3)$ different spreadsheet tabs

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

$(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)$ run another breakeven NPV$.$ For this analysis, 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).$