Please answer in Excel Format

QUESTION #4 (15 points) Sin millions Cost of machine Cost of Goods Sold Percentage Annual costs as a % of revenue Initial working capital Sale/Residual value end of year 10 Tax Rate Required Rate of Return Texron Industries, Inc. is a manufacturer of machine parts with locations in the United States. It is considering entering into a eight year agreement with a customer where it will supply certain parts to the customer for their products. It will require Texron to purchase a new machine and incur additional costs. The cost of the machine is $10,250,000 and will require additional annual costs of 10.0% of revenues. There is an initial working capital investment of $50,000 and it is returned in the final year. Revenues from the contract per year are shown below. Cost of goods sold is expected to be 55% of revenues. At the end of its life it is expected the machine can be sold in the used market for 10% of the original cost. Five year MACRS depreciation will be used for tax purposes and the company's average tax rate is 24% and the marginal rate is 26%. Given the risk level of this project Texron requires a rate of return 1% below its weighted cost of capital. Its weighted cost of capital is 11%. Prepare an analysis to determine if this project will generate an attractive level of economic benefits. Specifically determine the net present value and internal rate of return. Should the project be accepted or rejected and why? PERIOD 0 1 2 3 4 5 6 7 8 $ 5,500,000 $ 6,500,000 $ 7,500,000 $8,000,000 $ 8,000,000 $7,000,000 $5,000,000 $ 4,000,000 20.0% 32.0% 19.0% 12.0% 12.0% 5.0% Expected Contract Revenues Less COGS Less annual costs Less Depreciation MACRS Depreciation Rates Add Residual Value Taxable income Less Taxes After Tax Income Add: Depreciation Working capital return After-Tax Cash Flow NPV IRR