Free Cash Flows and Net Present Value calculation:

$1.$ When entering numbers that involve multiples $($such as Revenue $=$ unit sales Price$/$unit x

Quantity$)$ enter formulas so that you can change either Price$/$unit or Quantity and see how

changes influence your final answer.

$2.$ The details for the project are as follows:

a$.$ All cash flows occur at the end of the year, today is time $0.$ All investment occurs today

$($time $0).$ The first annual net cash flow occurs $1$ year from now at time $1.$

b$.$ Buying equipment: $$320,000$ cost $+$ $$50,000$ shipping $+$ $$20,000$ installation.

c$.$ The equipment is depreciated over $6$ years but only used for $4$ years.

d$.$ Selling the equipment at the end of year $4=$ $$150,000.$

i$.$ Hint: The assets will be sold at the end of year $4.$ If the sale price exceeds the net

book value of the assets at the end of year $4$ you must pay tax on the difference

$($use the tax rate mentioned below$).$

e$.$ Depreciation Method for Tax Purposes: MACRS $5-$year class starting in year $1.$

Year MACRS factor

X

Initial

Basis $=$Depreciation

$10\mathrm{.}2000$

$20\mathrm{.}3200$

$30\mathrm{.}1920$

$40\mathrm{.}1152$

$50\mathrm{.}1152$

$60\mathrm{.}0576$

f$.$ Annual unit sales $=1,500.$

g$.$ Sales price per unit $=$ $$200.$

h$.$ Cost per unit $=$ $$120.$

i$.$ Assume the sales price and the unit cost escalate at $5\%$ per year.

j$.$ Other incremental cash costs $=32000$ per year.

k$.$ Net working capital at each date:

i$.$ Accounts receivables $($AR$)$ t $=14\%($Sales t$)[$the AR at time $0$ equals $\mathrm{.}14$ times

expected total sales at time $0]$

ii$.$ Accounts payables $($AP$)$ t $=16\%($COGSt$+1)[$the AP at time $0$ equals $\mathrm{.}16$ times

expected total sales at time $1]$

l$.$ Tax rate $=32\%.$

m$.$ The discount rate is $9\%.$

$2$

$3.$ Compute the NPV using the discount rate given, also compute the IRR.

a$.$ Hint: use the NPV function but recall that in Excel the NPV function will compute the

present value of the periodic annual net cash flows $($starting in years $1),$ you must then

subtract the total outlay that occurs at time $0($if you have entered negative numbers for

the initial outlays, you will add that$).$ Use the IRR function in Excel to compute the IRR.

$4.$ Scenario analysis:

Recalculate the NPV and IRR for the following cases $($only changing one parameter at a time$)$:

$1.$ Sales price and the unit cost escalate at $3\%$

$2.$ AR becomes $20\%$ of Sales

$3.$ Discount rate increases to $11\%$

$4.$ Switch to straight$-$line depreciation

$5.$ Breakeven analysis:

Start with the base case each time to answer the following questions:

$1.$ What is the breakeven discount rate

$2.$ What is the breakeven annual unit sales

$3.$ What is the breakeven sales price per unit

$4.$ What is the breakeven cost per unit