For this assignment you will need to use only formulas in most of the cells $($similar to assignments $1-4),$ though there will be several changes for this assignment.

Madison Manufacturing is looking to add a new assembly line to its manufacturing plant.

The project is expected to have a five year lifespan, after which it will be shutdown. The equipment needed for the assembly line will cost $$500,000$ and will be depreciated using the $3-$year MACRS depreciation schedule $($depreciation rates are $33\%,45\%,15\%,$ and $7\%$ for years $1,2,3,$ and $4,$ respectively. An additional $$35,000$ in working capital will also be needed at the start of the project. No additional working capital wil be needed after that.

Base Case: The assembly line is expected to generate $$300,000$ in revenue per year with associated operating expenses of $$130,000$ per year. It is forecast that the equipment will have a salvage value of $$50,000($pre$-$tax$)$ by the end of the project $($year $5).$ Madison's tax rate is $30\%$ and the company's CFO has estimated that projects of this type require a return $($wacc$)$ of $10\%.$

A$.$ Setup your calculation cells below to compute the Base Case NPV for this project. Once your spreadsheet is working, take the value you get in cell D$63$ and type it into cell E$67.$

B$.$ Now you should change you input cells $($leaving your calculation cells alone$)$ and compute NPV for the Best Case Scenario with the following changes to the Base Case Inputs: Revenue of $$400,000,$ Operating Expenses of $$150,000,$ and a year $5$ equipment salvage vaue of $120,000.$ Take the values you get in cell D$63$ and type it in cell E$68.$

C$.$ Now change your input cells $($leaving the calculation cells alone$)$ and compute NPV for the Worst Case Scenario with the following changes to the Base Case Inputs: Revenue of $$150,000,$ Operating Expenses of $$181,000,$ and a year $5$ equpment salvage va of $$0.$ Once again take the values you get in cell D$63$ and typet it into cell E$69.$

D$.$ To finish your scenario analysis, use a formula in cell E$71$ to compute the expected NPV from your three scenarios. Note that the probability of each scenario is already included below.

F$.$ In cell E$72$ type in your investment recommendation as a simple Accept or Reject.

INPUTS

WACC

Tax Rate

Revenue

Operating Expenses

Salvage Value

NOWC

Equipment Cost

Depreciation Rate

Year

$\backslash$table$[[10\%,,],[30\%,$einput cells,$]]$

Revenue

Operating Expense

Depreciation

EBIT

Tax

NOPAT

Depreciation

Change in LT Operating Assets

Change in NOWC

FCF