b

please fill out blanks on excel sheet only. thanks!

PROBLEM 11-19 Finely Segmented Income Statements (CLOU excel Severo S.A. of Sao Paulo, Brazil, is organized into two divisions. The company's contribution format segmented income statement in terms of the Brazilian currency, the real, R) for last month is given below: Division Total Company Cloth Leather R3,500,000 1.721.000 1.779,000 R2,000,000 960,000 1.040,000 R1,500,000 761,000 739,000 Sales Variable expenses Contribution margin Traceable fixed expenses Advertising Selling and administrative Depreciation Total traceable fixed expenses Divisional segment margin Common fixed expenses Operating income 612.000 427,000 229,000 1.268.000 511.000 300,000 210,000 115.000 625.000 R 415.000 312.000 217,000 114.000 643.000 R 96,000 390,000 R121,000 Top management can't understand why the Leather Division has such a low segment margin when its sales are only 25% less than sales in the Cloth Division. As one step in isolating the problem, management has directed that the Leather Division be further segmented into product lines. The following information is available on the product lines in the Leather Division Leather Division Product Line Garments Shoes Handbags R500,000 R 700,000 R300,000 R120,000 Sales Traceable fixed expenses Advertising Selling and administrative Depreciation Variable expenses as a percentage of sales R 80.000 R 30,000 R 25.000 65% R 112.000 R 35.000 R 56,000 40% R 42,000 R 33.000 52% Analysis shows that R110,000 of the Leather Division's selling and administrative expenses are common to the product lines Page 507 Page 507 Analysis shows that R110,000 of the Leather Division's selling and administrative expenses are common to the product lines, Required: 1. Prepare a contribution format segmented income statement for the Leather Division, with segments defined as product lines. 2. Management is surprised by the handhug product line's poor showing and would like to have the product line segmented by market. The following information is available about the markets in which the handbag line is sold: Handbag Markets Domestic Foreign R200,000 R 100,000 Sales Traceable fixed expenses: Advertising Variable expenses as a percentage of sales R 40.000 43% R 80.000 70% Handbag Markets Domestie Foreign R200,000 R100,000 Sales Traceable fixed expenses Advertising Variable expenses as a percentage of sales R 40.000 43% R 80,000 70% All of the handbag product line's selling and administrative expenses and depreciation are common to the markets in which the product is sold. Prepare a contribution format segmented income statement for the handbag product line with segments defined as markets 3. Refer to the statement prepared in (1) above. The sales manager wants to run a special promotional campaign on one of the product lines over the next month. A marketing study indicates that such a campaign would increase sales of the Garments product line by R200.000 or sales of the Shoes product line by R145.000. The campaign would cost R30.000. Show computations to determine which product line should be chosen B . D E G Product Line Shoes Total Leather Division Garments Handbags 1 Problem 11-19 2 Part 1 3 4 Sales 5 Variable expenses 6 CM 7 Traceable fixed expenses 8 Advertising 9 Selling and Admin 10 Depreciation 11 Total traceable fixed expenses 12 Prdouct line segment margin 13 Common fixed expense 14 Selling and admin 15 Divisional segment margin 16 17 Handbao Markets 16 17 Part 2 Handbag Markets 18 Handbags Domestic Foreign 19 Sales 20 Variable expenses 21 CM 22 Traceable foxed expenses 23 Advertising 24 Total traceable foxed expenses 25 Market segment margin 26 Common faced expense 27 Selling and admin 28 Depreciation 29 Total common foued expenses 30 Product line segment margin 31 32 Part 3 33 Garments Shoes 34 Sales increase 35 CM ratio 36 Increase in CM 37 Less campaign cost 38 Increase in operating income 39 40 Based on these data, the campaign should be directed toward the shoes product line. Notice that the analysis uses the contribution margin 41 42