Whirlwind Industries is a multiproduct company with several manufacturing plants. The Brownwood Plant manufactures and distributes two carpet cleaning products, Household and Commercial, under the Karpet Kleen label. The forecasted operating results for the first six months of the year are presented in the following statement. kINDLY complete in excel with work shown. Would greatly appreciate it.

b Karpet Kleen - Brownwood Plant Forecasted Result of Operations For the six months ended June 30 Total Household 100,000 Commercial 100,000 Units 200,000 $ $ $ $ 3,000,000 1,900,000 Sales Revenue Cost of goods sold Gross profit Variable SGA Fixed SGA Total SGA Income(Loss) Before taxes 2,000,000 1,600,000 400,000 400.000 240,000 640,000 _(240,000) $ $ $ $ $ $ $ $ $ $ $ $ 1,100,000 700,000 360,000 1,060,000 40,000 $ S $ 5,000,000 3,500,000 1,500,000 1,100,000 600,000 1,700,000 _(200,000) Fixed SG&A expenses are allocated between the two products on the basis of relative sales dollars. These expenses are avoidable only if the entire plant is closed. The product costs per unit are as follows: Household Commercial Direct Materials Direct Labor Variable Manufacturing OH Fixed Manufacturing OH Total Product Cost $16 $19 Each product is manufactured on a separate production line. Normal manufacturing capacity is 200,000 cases of each product per year. However, the plant is capable of producing 250,000 cases of the Household product and 350,000 cases of the commercial product per year. Capacity levels assume an even flow of production throughout the year, so that the maximum capacity for the second half of the year is 125,000 cases of Household and 175,000 cases of Commercial. The following schedule reflects the top management's consensus regarding the price/volume alternatives for Karpet Kleen, products in the second six months of the year. These are essentially the same alternatives management faced during the first six months of the year. These figures represent the number of units the firm believes it could sell at the listed array of prices. Household Alterative Prices per Unit $18 $20 $21 Unit Sales Volume 120,000 100,000 90,000 80,000 50,000 Commercial Alterative Prices per Unit $25 $27 $30 $32 $35 Unit Sales Volume 175,000 140,000 100,000 55,000 35,000 $23 Top management believes the company's loss for the first six months of the year reflects a tight profit margin caused by intense competition. Management also believes that many companies will be forced out of the market by the next year and that long-term profits should improve. Other information: Fixed manufacturing overhead per unit is based on normal manufacturing capacity Depreciation constitutes 50% of the fixed manufacturing overhead cost of each product and is unavoidable. The remaining fixed manufacturing overhead expenses arise from factory personnel assigned to particular products and can be avoided if that product is discontinued. Variable selling and administrative expenses are $4 and 57 per unit, respectively, for the Household and Commercial products. FIRST - Enter all team member names in Cell A1 (shaded orange). ALL CELLS SHADED GREEN SHOULD BE INPUT BY YOU! 1. Calculate the Contribution Margin per unit and Total Contribution Margin for each type of product at each different Sales Price and Volume. See Cells C25 and D25 for a hint. Fill in cells C25-C29, D25-D29, C32-C36, and D32-36. Use these calculations to decide which selling price/sales volume combination should be used by the firm during the second six months. Highlight these cells yellow (whichever row you choose for each product highlight Columns A-D for that row). 2. Using the Unit Prices you choose in Part 1, calculate the net income for each product separately using the contribution margin format provided in Rows 40-50. Remember Avoidable Fixed Costs are only 50% of the remaining FOH of both products. Common fixed costs are composed of both Fixed S,G,&A as awell as 50% of the Fixed OH Costs. I have hard coded these for you. Please make sure you understand where they come from. Should either product be dropped for the second six months of the year? Why? There are formulas in some cells to automatically calculate sums. Please do not remove them. 3. Using the unit prices and volumes you selected above, make the following decision: Management has received a request for a special order from CleanMe Corporation for 80,000 cases of the commercial product at a price of $20/case. No sales commission would be required for this sale. Sales commission is normally $3.20 per case. Remember the maximum capcaity of Commercial for the second part of the year is only 175,000 units. So, if the firm accepts the special order they will have to cut regular production by 5,000 units. Should they accept this order? Use the template in Rows 54-70 to decide. There are formulas in some cells to automatically calculate sums. Please do not remove them. 4. MakeIt Corporation has offered to make Karpet Kleen's Household product for $13 per case. Karpet Kleen would still sell this product to its customers, so it would continue to pay the variable SGA costs even if it purchased this product from Makelt. Use the template in Rows 77-85 to decide whether the firm should accept this outsourcing offer. There are formulas in some cells to automatically calculate sums. Please do not remove them. 2 Operating Results First Six Months Household 100,000 Commercial 100,000 Total 200.000 Units 8 9 Variable S,G,&A 10 Fixed S.G.&A Sales Revenue $2,000,000 $3,000,000 Cost of Goods Sold $1,600,000 $1,900,000 Gross Profit $400,000 $1,100,000 $400,000 $700,000 $240,000 $360,000 Total S.G.&A $640,000 $1.060.000 Income(Loss) Before Taxes ($240,000) $40,000 $5,000,000 $3,500,000 $1,500,000 $1,100,000 $600,000 $1,700.000 ($200,000) 11 12 13 14 15 Product Costs Per Unit: 16 17 Direct Materials 18 Direct Labor 19 Variable OH 20 Fixed OH 21 Total Product Cost Household $7.00 $4.00 $1.00 $4.00 $16.00 Commercial $8.00 $4.00 $2.00 $5.00 $19.00 22 Unit Sales Volum 120.000 CM/Unit $2.00 Total CM $240,000 25 29 24 Alternative Prices/Unit Household $18.00 $20.00 $21.00 $22.00 $23.00 30 31 Alternative Prices/Unit Commercial $25.00 $27.00 34 $30.00 35 $32.00 $35.00 37 38 Second Six Months Expected Performance Unit Sales Volum 175.000 CM/Unit $4.00 Total CM $700,000 32 33 361 Household Commercial Total Units 0 Sales Revenue Variable Costs Contribution Margin Avoidable Fixed Costs Segment Margin Common Fixed Costs Income(Loss) Before Taxes $0 $200,000 ($200,000) $440,000 ($640,000) $0 $250,000 ($250,000) $610,000 ($860,000) $0 $450,000 ($450,000 $1,050.000 ($1,500,000) 51 Should Either product be dropped for the second six months? Enter answer below. 52 Special Order 80000 54 Special Order: 55 56 Units 57 58 Sales Revenue 59 Variable Costs 60 Profit From Special Order 61 62 Lost Regular Sales: 63 64 Units 65 66 Lost Sales Revenue 67 Gained Variable Costs 68 Lost Profit from Regular Sales Special Order Lost Sales 5000 69 Make Buy 70 Overall Profit Impact 71 72 Would you accept the special order? Why? Answer in box below. 73 74 75 Make/Buy Information (Not all listed costs are relevant) 76 77 78 Direct Materials 79 Direct Labor 80 Variable OH 81 Variable S,G,&A 82 Avoidable Fixed Costs 83 Common Fixed Costs 84 Purchase Price 85 $0.00 $0.00 86 87 Should the firm continue making or start buying the Household product? Answer in the box below. 88