I need help putting the data into tables $to$ build a worksheet to derive the NPV and IRR.

Free Cash Flows and Net Present Value calculation:

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.

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: $$350,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=$$$125,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.

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

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

h$.$ Cost per unit $=$$$110.$

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

j$.$ Other incrememtal cash costs $=30000$ per year.

k$.$ Net working capital at each date:

i$.$ Accounts receviables Sales $\{$:${?}_{t+1})[$the AR at time $0$ equals $\mathrm{.}14$ times

expected total sales at time $1]$

ii$.$ Accounts payables $(AP{)}_t=17\%(COG{S}_{t+1})[$the AP at time $0$ equals $\mathrm{.}17$ times

expected total sales at time $1]$

Tax rate $=35\%.$

m $.$ The discount rate is $8\mathrm{.}5\%.$

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.

Scenario analysis:

Recalculate the NPV and IRR for the following cases:

Unit sales $=1,200$ instead of $1400$

Sales price and the unit cost escalate at $6\%$

The discount rate increases to $10\%$

AR becomes $20\%$ of Sales

Switch to straight$-$line depreciation