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 order to pay for 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 your 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 the Data:

Answer the following 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 the NPV of each investment is different, even though all three investments received the same initial investment and the same total net cash flows.

$4.$ 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 change your analysis for questions $1$ & $2?$ If so$,$ how?