Problem $10-2$

An auto$-$part manufacturing company is considering the purchase of an industrial robot to do spot welding, which is currently done by skilled labor. The initial cost of the robot is $\backslash (\backslash$$ $250,000\backslash ),$ and the annual labor savings are projected to be $\backslash (\backslash$$ $125,000\backslash ).$ If purchased, the robot will be depreciated under MACRS as a sevenyear recovery property. This robot will be used for five years after which the firm expects to sell it for $\backslash (\backslash$$ $50,000\backslash ).$ The company's marginal tax rate is $\backslash (25\backslash \%\backslash )$ over the project period.

Determine the net after$-$tax cash flows for each period over the project life. Assume MARR $\backslash (=15\backslash \%\backslash ).$

Marginal Tax Rate

Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy$/$paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. $1.$ Start Excel$-$completed.

$2.$ In cell E$16,$ by using cell references, calculate the annual depreciation deduction using the MACRS method for year $1.(1$ pt$.)$ Note: Enter the depreciation amount as a positive value.

$3.$ To calculate the annual depreciation deductions using the MACRS method for years $2$ through $4,$ copy cell E$16$ and paste it onto cells F$16$:H$16.(1$ pt$.)$

$4.$ In cell I$16,$ by using cell references, calculate the annual depreciation deduction using the MACRS method for year $5.(1$ pt$.)$

Note: Half$-$year convention applies to year $5$ due to disposal of asset.

$5.$ In cell E$17,$ by using cell references, calculate the taxable income for year $1.(1$ pt$.)$

$6.$ To calculate the annual taxable income for years $2$ through $5,$ copy cell E$17$ and paste it onto cells F$17$:I$17.(1$ pt$.)$

$7.$ In cell E$18,$ by using cell references, calculate the income taxes for year $1.(1$ pt$.)$

Note:

$1.$ Use a cell reference to the taxable income from Step $5$ in your calculations.

$2.$ Enter the income taxes amount as a positive value.

$8.$ To calculate the annual income taxes for years $2$ through $5,$ copy cell E$18$ and paste it onto cells F$18$:I$18.(1$ pt$.)$

$9.$ In cell E$19,$ by using cell references, calculate the net income for year $1.(1$ pt$.)$

Note: Use cell references to the taxable income from Step $5$ and to the income taxes from Step $7$ in your calculations.

$10.$ To calculate the net income for years $2$ through $5,$ copy cell E$19$ and paste it onto cells F$19$:I$19.(1$ pt$.)$

$11.$ In cells E$23$:I$23,$ by using cell references, calculate the net income for years $1$ through $5.(1$ pt$.)$

Note: Use cell references to the net income from Steps $9$ and $10.$

$12.$ In cells E$24$:I$24,$ by using cell references, calculate the annual depreciation deductions for years $1$ through $5.(1$ pt$.)$

Note:

$1.$ Use cell references to the depreciation deductions from Steps $2,3,$ and $4.$

$2.$ Enter the annual depreciation deductions as positive values.

$13.$ In cell I$28,$ by using cell references, calculate the gains tax for year $5.(1$ pt$.)$

Note:

$1.$ Use cell references to the depreciation deductions from Step $12.$

$2.$ Note that there can be a reduction in tax due to the loss. Enter gain tax, if any, as a negative value or enter tax reduction, if any, as a positive value.

$14.$ In cell D$29,$ by using cell references, calculate the net cash flow for year $0.(1$ pt $.)$

$15.$ To calculate the net cash flows for years $1$ through $5,$ copy cell D$29$ and paste it onto cells E$29$:$129.(1$ pt$.)$

$16.$ Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.