I'm looking for some help finishing this excel challenge. I'm stuck on residual income.

Car Deals Inc. has two divisions: New Cars and Used Cars. The following segmented financial information is for the most recent

fiscal year:

New Cars

Division

Used Cars

Division

Sales $9,000,000 $18,000,000

Cost of goods sold 3,300,000 8,700,000

Allocated overhead 1,050,000 2,550,000

Selling and administrative expenses 585,000 630,000

The New Cars division had average operating assets totaling $17,400,000 for the year, and the Used Cars division had average

operating assets of $22,800,000. Assume the cost of capital rate is 15%, and the company's tax rate is 40%.

Required:

a. Prepare a segmented income statement, including the profit margin ratio for each division at the bottom of the segmented

income statement.

b. Calculate return on investment (ROI) for each division.

c. Calculate residual income for each division.

d. Summarize the answers to parts a, b, and c. What does this information tell you about each division?

	New Cars	Used Cars
	Division	Division
Sales	$9,000,000	$18,000,000
Cost of goods sold	3,300,000	8,700,000
Allocated overhead	1,050,000	2,550,000
Selling and administrative expenses	585,000	630,000
Segmented Income Statement
Particulars	New Car Division	Used Car Division
Sales	$ 9,000,000.00	$ 18,000,000.00
Cost of goods sold	($3,300,000.00)	($8,700,000.00)
Gross Margin	$ 5,700,000.00	$ 9,300,000.00
Allocated Overhead	($1,050,000.00)	($2,550,000.00)
Selling/Administrative	($585,000.00)	($630,000.00)
Operating Income	$ 4,065,000.00	$ 6,120,000.00
Tax @ 40%	$ 1,626,000.00	$ 2,448,000.00
Net Operating Income	$ 2,439,000.00	$ 3,672,000.00
Profit Margin Ratio	27%	20%
Return on Investment
Particulars	New Car Division	Used Car Division
Net Income	$ 2,439,000.00	$ 3,672,000.00
Operating Assets	$ 17,400,000.00	$ 22,800,000.00
ROI	14%	16%