Intermediate Calculations $01234$

Units sold

Sales price per unit $($excl$.$ depr.$)$

Variable costs per unit $($excl$.$ depr.$)$

Nonvariable costs $($excl$.$ depr.$)$

Sales revenue

Required level of net operating working capital

Basis for depreciation

Annual equipment depr. rate $20\mathrm{.}00\%32\mathrm{.}00\%19\mathrm{.}20\%11\mathrm{.}52\%$

Annual depreciation expense

Ending Bk Val: Cost $$ Accum Dep'rn

Salvage value

Profit $($or loss$)$ on salvage

Tax on profit $($or loss$)$

Net cash flow due to salvage

Years

Cash Flow Forecast $01234$

Sales revenue

Variable costs

Nonvariable operating costs

Depreciation $($equipment$)$

Oper. income before taxes $($EBIT$)$

Taxes on operating income $(40\%)$

Net operating profit after taxes

Add back depreciation

Equipment purchases

Cash flow due to change in NOWC

Net cash flow due to salvage

Net Cash Flow $($Time line of cash flows$)$

Key Results: Appraisal of the Proposed Project

Net Present Value $($at $10\%)=$

IRR $=$

MIRR $=$

Payback $=$

Discounted Payback $=$

Data for Payback Years Years

$01234$

Net cash flow

Cumulative CF

Part of year required for payback

Data for Discounted Payback Years Years

$01234$

Net cash flow

Discounted cash flow

Cumulative CF

Part of year required for discounted payback

b$.$ Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the sales price, variable costs per unit, and number of units sold. Set these variables$$ values at $10\%$ and $20\%$ above and below their base$-$case values. Include a graph in your analysis.

$\%$ Deviation $1$st YEAR UNIT SALES Note about data tables. The data in the column input should NOT be input using a cell reference to the column input cell. For example, the base case $1$st Year Unit Sales in Cell B$100$ should be the number $1,000$ and NOT have the formula $=$D$31$ in that cell. This is because you'll use D$31$ as the column input cell in the data table and if Excel tries to iteratively replace Cell D$31$ with the formula $=$D$31$ rather than a series of numbers, Excel will calculate the wrong answer. Unfortunately, Excel won't tell you that there is a problem, so you'll just get the wrong values for the data table!

from Base NPV

Base Case $1,000$

$-20\%$

$-10\%$

$0\%$

$10\%$

$20\%$

$\%$ Deviation SALES PRICE $\%$ Deviation VARIABLE COST

from Base NPV from Base NPV

Base Case $$24\mathrm{.}00$ Base Case $$18\mathrm{.}00$

$-20\%-20\%$

$-10\%-10\%$

$0\%0\%$

$10\%10\%$

$20\%20\%$

Deviation NPV at Different Deviations from Base

from Sales Variable

Base Case Units Sold Price Cost$/$Unit

$-20\%$ $$0$ $$0$ $$0$

$-10\%$ $$0$ $$0$ $$0$

$0\%$ $$0$ $$0$ $$0$

$10\%$ $$0$ $$0$ $$0$

$20\%$ $$0$ $$0$ $$0$

Range

c$.$ Now conduct a scenario analysis. Assume that there is a $25\%$ probability that best$-$case conditions, with each of the variables discussed in Part b being $20\%$ better than its base$-$case value, will occur. There is a $25\%$ probability of worst$-$case conditions, with the variables $20\%$ worse than base, and a $50\%$ probability of base$-$case conditions. $($Hint: Use Scenario Manager. Go to the Data menu, choose What$-$If$-$Analyis, the choose Scenario Manager. After you create the Scenario's, you can pick a scenario and type in the resulting NPV $($but be sure to return the Scenario to the base$-$case afterward$).$ Or you can create a Scenario Summary and use a cell reference to the Scenario Summary worksheet to show the NPV for each scenario.$)$

Unit Sales Sales Price per Unit Variable Costs per Unit

Scenario Probability NPV

$$14\mathrm{.}40$

Best Case $25\%1,200$ $$28\mathrm{.}80$ $$14\mathrm{.}40$

Base Case $50\%1,000$ $$24\mathrm{.}00$ $$18\mathrm{.}00$

Worst Case $25\%800$ $$19\mathrm{.}20$ $$21\mathrm{.}60$ $$21\mathrm{.}60$

Expected NPV $=$

Standard Deviation $=$

Coefficient of Variation $=$ Std Dev $/$ Expected NPV $=$

d$.$ If the project appears to be more or less risky than an average project, find its risk$-$adjusted NPV$,$ IRR, and payback.

CV range of firm's average$-$risk project: $0\mathrm{.}8$ to $1\mathrm{.}2$

Low$-$risk WACC $=8\%$

WACC $=10\%$

High$-$risk WACC $=13\%$

Risk$-$adjusted WACC $=$

Risk adjus