Problem Text

$*$Please build a model in excel and show the formulas you used to create the model, Link the completed sheet$*$

For a new product, the initial cost to launch the product $($e$.$g$.,$ product development, equipment and tooling,

market testing, etc.$)$ is estimated to be $$600,000.$ Sales volume in the first year is estimated to be $60,000$ units

and is projected to grow at a rate of $4\%$ per year. The year $1$ selling price is $$13\mathrm{.}25$ and will increase by $$0\mathrm{.}25$

each year. Year $1$ unit variable costs are $$4\mathrm{.}00$ and fixed costs are $$380,000.$ Unit costs are expected to

increase $2\%$ per year. Fixed costs are expected to increase $5\%$ per year. Develop a spreadsheet model to

predict the net present value of profit based on a four year time horizon, assuming a $8\%$ discount rate. Treat

the product launch cost as occurring at time $0,$ and the annual inflows and outflows to be at the ends of the

respective years.

Instructions & Guidelines

$$ Base Case. Develop a correct, flexible, and documented model for the problem. Hint: The NPV for the base

case model should be $$45,896.$ Most of your time will $($should$)$ be used on this step. Note that there are $10$

separate inputs values to this problem. After the base case is completed, you should be able to change any

of the input values and have the NPV recomputed correctly $($group the inputs together so the user can

clearly see what can be changed$).$ You can assume this is always a $4-$year project; you don$$t need to make

it flexible with respect to the time horizon of the project.

$$ Text Box. On your worksheet, create a textbox $($add to this same text box in the following steps$)$ and record

the NPV$.$ With the inputs at the base case values, should the company pursue the project?

$$ Critical Values $1($Breakeven$).$ Find breakeven points $($i$.$e$.,$ where NPV $=$ $$0)$ with respect to the year $1$ sales

quantity, the year $1$ selling price, and the unit variable cost growth rate. For each, start with all the inputs

at their base case values. Record these values in the text box.

$$ Critical Values $2.$ With all the other inputs at their base case values, find the discount rate that results in

NPV $=$ $$0.$ What name is used for this special discount rate? Record the value and the name in the text box.

$1-$Way Data Table. Create a $1-$way data table tracking NPV$,$ by changing the discount rate. Vary the

discount rate from $0\%$ to $20\%$ in increments of $1\%.$ Create a line chart based on the result. Make the Data

Table flexible so the user can change the starting and increment values for the discount rate. Hint: The

curve should cross the x$-$axis at the breakeven discount rate you found in the previous step.

$2-$Way Data Table. Create a $2-$way Data Table tracking NPV$,$ by changing Sales Volume Growth Rate in a

column from $-10\%$ to $+10\%$ in increments of $1\%,$ and changing year $1$ Fixed Cost in a row from $$250,000$ to

$$450,000$ in increments of $$25,000.$ Use conditional formatting on the results so the user can easily see how the NPV changes based on these two variables,