DoPlant$-$Based Meat Substitutes

Year Revenues Expenses Net Annual CF$\text{'}$s

$1650,000200,000450,000$

$2650,000200,000$

$3650,000200,000$

$4650,000200,000$

$5650,000200,000$

Total

Interest Rate

$2,000,000$ Initial Investment

Salvage Value of Equipment

Plant$-$Based Meat Substitutes

Year Revenues Expenses Net Annual CF$\text{'}$s

$1650,000200,000450,000$

$2650,000200,000450,000$

$3650,000200,000450,000$

$4650,000200,000450,000$

$5650,000200,000450,000$

Total $3,250,0001,000,0002,250,000$

Plant$-$Based Protein Bars

Year Revenues Expenses Net Annual CF$\text{'}$s

$1525,000300,000$

$2600,000225,000$

$3650,000205,000$

$4775,000180,000$

$5700,00090,000$

Total

$2,000,000$ Initial Investment

total Salvage Value of Equipment

Plant$-$Based Frozen Meals

Year Revenues Expenses Net Annual CF$\text{'}$s

$1700,00090,000$

$2775,000180,000$

$3650,000205,000$

$4600,000225,000$

$5525,000300,000$

Total

$2,000,000$ Initial Investment

total Salvage Value of Equipment

NPV Using

Tables & Factors: Description Cash Flow PV Factor PV of Cash Flow

NPV $0$

Description Cash Flow PV Factor PV of Cash Flow

NPV $0$

Description Cash Flow PV Factor PV of Cash Flow

NPV $0$

For Plant$-$Based Meat Substitutes

NPV Using

NPV Formula: NPV Formula: Cash Flows: Yr $1$

Yr $2$

Yr $3$

Yr $4$

Yr $5$

For Plant$-$Based Protein Bars

NPV Using

NPV Formula: NPV Formula: Cash Flows: Yr $1$

Yr $2$

Yr $3$

Yr $4$

Yr $5$

For Plant$-$Based Frozen Meals

NPV Using

NPV Formula: NPV Formula: Cash Flows: Yr $1$

Yr $2$

Yr $3$

Yr $4$

Yr $5$

You work in the product development department of plant$-$based milk company. The company has been booming and is ready to add a new product line. There are three potential lines management is considering: meat alternatives, protein bars or frozen meals.

You have been asked to evaluate the financial viability of each option. The company has $$2,000,000$ to invest into the project, and each product has the potential to bring in an estimated $$2,250,000$ of future net cash flows, although the timing of the cash flows varies per product.

In $1$ order to payfor the project, the company will have to finance at a $4\%$ interest rate.

Instructions:

a$.$ Fill out all yellow cells in the Data section $($rows $2-15).$ Use formulas to create calculations when applicable. The company does not expect to sell the equipment acquired for this project at the end of its $5$ year useful life.

b$.$ Fill out all yellow cells in the NPV Using Tables & Factors section $($rows $16-27).$ Use the method we learned in class. The Description may be hard coded, but use cell referencing or formulas to fill out the rest of the table. The only exception to this is that you may hard code a $"1"$ for the PV Factor for any cash flow that is happening today. For the rest of the factors, Present Value tables are located on the additional tabs$.$

c$.$ Fill out all yellow cells in the NPV Using NPV Formula section $($rows $28-34).$ The syntax of the NPV formula is $=$NPV$($rate$,($value $1),[$value $2]...)$ This means that after starting the formula you can calculate the NPV of a series of cash flows if you know the interest rate. Note: The NPV calculation assumes that all cash flows start in one year from today, and occur in equal time intervals. Thus, you cannot include your initial investment in the NPV formula. You will have to subtract it after you close the final paranthesis.

Consider Data:

Answer questions. Please answer in red text.

$1.$ Which investment looks like it will be the most promising, and why?

$2.$ Are there any investments that you would avoid? Why?

$3$A$.$ Pay special attention to how the net cash flows for each investment flow in $($i$.$e$.$: review rows $6-10$ in columns E$,$ J and O$).$ What do you notice about the value and timing of the cash flows? Do they stay the same, get bigger as time goes on$,$ or decrease as time goes on$?$

$3$B$.$ Next consider the time value of money. What do we expect for the present value of a cash flow to do as it extends further into time $($i$.$e$.,$ would we expect the PV of $$100$ received in $1$ year to be the same as the PV of $$100$ received in $10$ years$)?$

$3$C$.$ Lastly, consider the NPV of each outcome. Explain why NPV of each investment is different, even though all three investments received the same initial investment and the same total net cash flows.

$3.$ The company realizes that there is some equipment that can be salvaged at the end of year $5.$ For each investment the salvage value would be $$40,000.$ Enter that value into the spreadsheet, cell B$14.$ Does this analysis change questions $1$ & $2?$ If so$,$ how? Given:

PV $($single Sum$)$

$4\mathrm{.}00\%$

Year $10\mathrm{.}961$

Year $20\mathrm{.}924$

Yr $30\mathrm{.}889$

Yr $40\mathrm{.}8548$

Yr $50\mathrm{.}82$

PV $($Annuity$)$

Yr $5=4\%=4\mathrm{.}452$