Discounted Cash Flow Model & Analysis

Problem Text

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.

$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 NPV changes based on these two variables.

Could anyone buld this in an excel and share? I willm be greatful.