Your friend Tony Cook works directly under the manager of the Patio Division of the company. For the year just ended, the income statement and asset investment information is as follows: Sales Less Cost of Goods Sold Equals Gross Profit Less Operating Expenses Equals Operating Income Invested Assets $2,800,000 2,000,000 $ $ 800,000 450,000 350,000 $2,500,000 The Patio Division manager (Tonys boss) is out sick today. The president of the company (David Lawson) said that the Divisions rate of return must be increased to at least 13% by the end of the next year if operations in this area are to continue. Tonys boss has developed three proposals, and President Lawson has asked Tony to work with you and electronically send up to his office some numerical analysis and discussion of each proposal:

Proposal #1: Transfer some equipment to other divisions at no gain or loss and use rented manufacturing equipment instead. This would reduce the invested assets by $312,500 but increase cost of goods sold (because the rent is a product cost) by $77,000 per year.

Proposal #2: Purchase new and more efficient equipment which would increase depreciation but overall still result in a net decrease in the cost of goods sold by $294,000 per year. Sales would remain unchanged, and the old equipment, which is depreciated out, would be scrapped at no gain or loss. The new equipment would increase invested assets by an additional $1,875,000.

Proposal #3: Reduce invested assets by discontinuing a product line. This action would eliminate sales of $475,000 per year, reduce cost of goods sold by $300,000 per year, and reduce operating expenses by $95,000 per year. Assets of $750,000 would be transferred to other divisions at no gain or loss.

Create a spreadsheet with input cells containing all of the above information as well as an output section that: provides the DuPont net profit margin ratio, asset turnover ratio, rate of return on investment, and residual income (using the 13% rate) for the current situation provides an projected income statement and provides the amount of invested assets for Proposal #1 provides the DuPont net profit margin ratio, asset turnover ratio, rate of return on investment, and residual income for Proposal #1 provides an projected income statement and provides the amount of invested assets for Proposal #2 provides the DuPont net profit margin ratio, asset turnover ratio, rate of return on investment, and residual income for Proposal #2 provides an projected income statement and provides the amount of invested assets for Proposal #3 provides the DuPont net profit margin ratio, asset turnover ratio, rate of return on investment, and residual income for Proposal #3