Amazon is on a mission to be net zero carbon by $2040.$ As part of that endeavor, they are considering using autonomous delivery vehicles which operate on alternative power sources $($solar$,$ battery, recycled oil, etc$).$ The autonomous vehicles are expected to cost $$100,000$ each. Amazon wants to invest in $30$ to start. Amazon will pay for $30\%$ of the total cost of the autonomous vehicles outright and borrow the other $70\%.$ The loan is borrowed at $11\%$ for $5$ years. Each vehicle is estimated to have a salvage vale of $$30,000$ after $8$ years. The autonomous delivery vehicles are expected to be able to deliver $\frac{24}{7}$ and therefore are expected to increase revenue by $$50,000$ per unit per year and add operating expenses of $$10,000$ per unit per year. Federal corporate income taxes follow the table below.

a$)$ Develop tables using a spreadsheet to determine the ATCF $($After Tax Cash Flow$)$ for the $8$ years of the AV's considering all $30$ AVs. Negative taxable income is treated a $$0$ taxable income.

a$.$ Using straight line depreciation $(8$ points$)$

b$.$ Using MACRS depreciation with the appropriate property class $(8$ points$)$

b$)$ If they keep all $30$ vehicles for $8$ years calculate the Present Worth $($PW$)$ of the investment of all $30$ vehicles for each method $($SL and MACRS$)$ using an after$-$tax MARR of $9\%.(2$ points$)$

c$)$ Calculate the IRR of the ATCF. Is this a good investment? $(2$ points$)$

Tips:

Every year's taxable income may not fall in the same tax bracket.

If you have a loss in a year assume $0$$ taxes paid.

You cannot claim depreciation past the salvage value year.

When solving for the PV of the after$-$tax cash flow DO NOT use the NPV formula. You need to make a new column and solve for the PV of each year and then sum them.

The NPV formula solves for net present value in year $0.$ This formula assumes the first value you select is one period from year $0$ and that there is $0$$ in year $0.$ If you really wanted to use it$.$ You could use the NPV formula to solve for years $1-8$ and add the year $0$ amount to it$.$

$\backslash$table$[[$Corporate Income Taxes$],[$Reported Income,$\%$ Taxes Paid$],[$$$,-,$$$,50,000\mathrm{.}00,15\mathrm{.}0\%$

INCLUDE RHE EXCEL FILE PLEAE