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

After spending $$10,000$ on client$-$development, you have just been offered a big production contract by

a new client. The contract will add $$200,000$ to your revenues for each of the next $5$ years and it will

cost you $$100,000$ per year to make the additional product. You will have to use some existing

equipment and buy new equipment as well. The existing equipment is fully depreciated but could be

sold for $$50,000$ now. If you use it in the project, it will be worthless at the end of the project. You will

buy new equipment valued at $$30,000,$ put it into use in year $1,$ and use the $5-$year MACRS schedule to

depreciate it$.$ It will be worthless at the end of the project. Your current production manager earns

$$80,000$ per year. Since she is busy with ongoing projects, you are planning to hire an assistant at

$$40,000$ per year to help with the expansion. You will have to immediately increase your inventory from

$$20,000$ to $$30,000.$ It will return to $$20,000$ at the end of the project. Your company's tax rate is $21\%$

and your discount rate is $15\%.$ Do the capital budgeting analysis for this project and calculate its NPV$.$

Depreciation schedule is as shown in the table below:

$37.$a: Tabulate the project's MACRS schedule and Depreciation from Year $1$ to Year $5$ in Excel. $(5$ points$)$

$37.$b: Tabulate the project's net working capital and change in net working capital from Year $0$ to Year $5$

and show formulas to calculate net working capital and net working capital change. $(5$ points$)$

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

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

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

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

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