Valuation and Financial Modeling Problem $36(25$ points$)$

Your company has been doing well, reaching $$1$ million in earnings, and is considering launching a new

product. Designing the new product has already cost $$500,000.$ The company estimates that it will sell

$800,000$ units per year for $$3$ per unit and variable non$-$labor costs will be $$1$ per unit. Production will

end after year $3.$ New equipment costing $$1$ million will be required. The equipment will be put into use

in year $1$ and depreciated to zero using the $7-$year MACRS schedule. You plan to sell the equipment for

book value at the end of year $3.$ Your current level of working capital is $$300,000.$ The new product will

require the working capital to increase to a level of $$380,000$ immediately, then to $$400,000$ in year $1,$ in

year $2$ the level will be $$350,000,$ and finally in year $3$ the level will return to $$300,000.$ Your tax rate is

$21\%.$ The discount rate for this project is $10\%.$ Do the capital budgeting analysis for this project and

calculate its NPV$.$

Depreciation schedule is as shown in the table below:

$36.$a: Tabulate the project's MACRS schedule and Depreciation $($in thousands$)$ from Year $1$ to Year $8$ in

Excel. $(5$ points$)$

$36.$b: Tabulate the project's net working capital $($in thousands$)$ and change in net working capital $($in

thousands$)$ from Year $0$ to Year $3$ and show formulas to calculate net working capital and net working

capital change. $(5$ points$)$

$36.$c: Tabulate the project's income statement $($in thousands$)$ from Year $1$ to Year $3(5$ points$).$ Income

statement must include Revenues, Cost, Depreciation, Taxable Income, Tax, and Net Income.

$36.$d: Tabulate the project's free cash flows $($in thousands$)$ from Year $0$ to Year $3.$ Every year's FCF will

need to be itemized $($in thousands$)$ in the table and show formulas to calculate FCF$.(5$ points$)$

$36.$e: Calculate the project NPV and your investment decision on this project. $(5$ points$)$