$\backslash$table$[[$A$,$B$,$c$,$D$,$E$,$F$,$G$,$H$,1,$J$]]$

Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $$10$ million at Year $0$ to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to $10\%$ of the year's projected sales; for example, NWC$0=10\%($Sales$1).$

The firm believes it could sell $1,000$ units per year. The servers would sell for $$24,000$ per unit, and Webmasters believes that variable costs would amount to $$18,000$ per unit. After Year $1,$ the sales price and variable costs will increase at the inflation rate of $3\%.$ The company's nonvariable costs would be $$1$ million at Year $1$ and also would increase at the $3\%$ inflation rate.

The server project would have a life of $4$ years. If the project is undertaken, it must be continued for the entire $4$ years. The equipment would be depreciated over a $5-$year period, using MACRS rates. The estimated market value of the equipment at the end of the project's $4-$year life is $$500,000.$

Webmasters' federal$-$plus$-$state tax rate is $25\%.$ Its cost of capital is $10\%$ for average$-$risk projects, defined as projects with a coefficient of variation of NPV between $0\mathrm{.}8$ and $1\mathrm{.}2.$ Low$-$risk projects are evaluated with a WACC of $8\%,$ and high$-$risk projects at $13\%.$ Also, the project's returns are expected to be highly correlated with returns on the firm's other assets.

a$.$ Develop a spreadsheet model, and use it to find the project's NPV$,$ IRR, and payback.

Desk $1\backslash$table$[[$C$,,$D$,$E$,$F$,$G$,$H$,1],[,,10\%,,,,,],[$Intermediate Calculations,,,$0,1,2,3,4],[$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,,,,,,,$],[\backslash$table$[[$Annual equipment depr. rate$],[$Annual depreciation expense$],[$Ending Bk Val: Cost $-$ Accum Dep'rn$]],,,,20\mathrm{.}00\%,32\mathrm{.}00\%,19\mathrm{.}20\%,11\mathrm{.}52\%],[,,,,,,,],[,,,,,,,],[\backslash$table$[[$Salvage value$],[$Profit $($or loss$)$ on salvage$],[$Tax on profit $($or loss$)],[$Net cash flow due to salvage$]]],[],[],[]]$

$\backslash$table$[[,,,,],[$Cash Flow Forecast,$0,1,2,3,4],[$Sales revenue,,,,,$],[$Variable costs,,,,,$],[$Nonvariable operating costs,,,,,$],[$Denreriation $($erutinment$),,,,,]]$

Desk $1100\%$

$123$

Arial

$10$

B

$I$

A

$52$

$fx$

$\backslash$table$[[\backslash$table$[[$A$],[$B$],[$c$]],$D$,$E$,$F$,$G$,$H$,1,$J$],[$Enalng EK var: Lest $-$ Accum Dep Tn$,,,,,,,],[$Salvage value,,,,,,,$],[$Profit $($or loss$)$ on salvage,,,,,,,$],[$Tax on profit $($or loss$),,,,,,,],[$Met cash flow due to salvage,,,,,,,$],[,,$Years$],[$Cash Flow Forecast,,$0,1,2,3,4,],[$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\%)=$

Build a Model

Desk $1$C$181$

fx

$\backslash$table$[[$A$,$B$,$c$,$D$,$E$,$F$,$G$,$H$,1],[$Key Results: Appraisal of the Proposed Project,,,,,$],[$Net Present Value $($at $10$

IRR $=$

MIRR $=$

Payback $=$

Discounted Payback $=,,,,,],[$Data for Payback Years,Years$],[0,1,2,3,4],[$Net cash flow Cumulative CF Part of year required for payback,,,,,$],[,,,,]]$

Data for Discounted Payback Years

$\backslash$table$[[0,1,2,3,4]]$

Part of year required for discounted payback

Build a Mode

$$ l

Desk $1$