Your goal is to make informed recommendations to the management team about which alternative

is better. You decide to build an excel model to conduct a series of analysis. You laid out the plan

below:

$1.$ Construct a model that calculates free cash flows for each relevant year.

$2$Note that the $15-$year class depreciates the asset over $16$ years.

$3$

$2.$ Compute NPV$,$ IRR, and modified IRR $($WACC $=13\%)$

$3.$ Use Scenario Manager under Data$/$What If Analysis to create the two plant alternatives.

Label the scenarios $$NC plant$$ and $$NM plant$\mathrm{.}3$

$4.$ For each scenario, compute the break$-$even $\%$ sales increase and break$-$even transportation

cost $\%$ of finished furniture, i$.$e$.,$ the $\%$ sales increase and transportation cost $\%$ that result in

$$0$ NPV$.$ Construct a two way table for NPV to analyze the sensitivity of these two variables.

$5.$ Construct a one$-$way sensitivity table to analyze the sensitivity of NPV to changes in $\%$

requirement of NOWC.

$6.$ Prepare a table summarizing the two scenarios. Include the values of the free cash flow in

each year and other relevant variables.

$7.$ Make sure that every output of the model will automatically update when you change an

assumption as you may be asked to alter some assumptions during the presentation to the

management team of Hickory Furniture.