You and a friend decide to create a CBD-infused canned coffee drink (CBD Coffee) with the usual jolt from coffee without the jitters. As a Financial Management student, your friend asks for your help in figuring out the return over the four-year investment period with the following assumptions:

- Her new beverage machine will cost $50,000 and her dad lets her use the back of his restaurant (with a property value of $100,000) for production and storage. You assume the machine is fully depreciated and restaurant free for other uses in four years.

- The MACRS depreciation allowance for the beverage machine over four years is 33.33%, 44.45%, 14.81% and 7.41% of the initial value per year, respectively

- You determine the business will require $5,000 of initial working capital and an additional $5,000 each year, all of which is returned at the end of year 4.

- You assume a discount rate of 10% for the investment

1.) Solve for the Total Cash Flow of the Investment for each year (Year 0 Year 4). What is the NPV of these cash flows?

2.) Your friend has ambitious projections for CBD Coffee as follows:

- She anticipates selling 10,000 cans of CBD Coffee for $5 each to stores in the first year, and expects sales to grow 30% each year with an annual price increase of 15% per year

- Operating costs are expected to equal sales revenue in the first year and grow by 10% each year

- Taxes are 21% of Income Before Tax

a.) What is the Net Income and Operating Cash Flow for Years 1 through 4?

b.) Calculate the NPV based upon the Incremental After-Tax Cash Flow.

c.) What is the IRR?

3.) You suggest to your friend that some of her assumptions about CBD Coffee seem overly optimistic and decide to stress test a few of her assumptions. Using the Goal Seek function in Excel, solve for each of the below variables while holding the others constant.

a.) Solve for the annual production growth necessary based on the above assumptions in order to reach the 10% rate of return.

b.) Solve for the minimum annual price increase necessary based on the above assumptions in order to reach the 10% rate of return

c.) Solve for the maximum annual expense growth possible based on the above assumptions in order to reach the 10% rate of return

a.) 24%, b.) 10% and c.) 19%

4.) You convince your friend it makes sense to consider a scenario analysis for CBD Coffee and agree to stress test her assumptions as follows:

What is the NPV of the investment @ 10% based upon your analysis?

Case Downside Base Optimistic Probability 25% 50% 25% Sales Growth | Price Increase | +10% +5% +30% +15% +50% +20% Expenses +20% +10% +25% Case Downside Base Optimistic Probability 25% 50% 25% Sales Growth | Price Increase | +10% +5% +30% +15% +50% +20% Expenses +20% +10% +25%