Need both the narrative explanation and the work show in excel. 
ACC 650 Team Blue CLC Assignment Info Case: C-14-62 Found on page 634 (type in 670) of textbook. Ontario Pump Company, a small manufacturing company in Toronto, Ontario, manufactures three types of pumps used in a variety of applications. For many years the company has been profitable and has operated at capacity. However, in the last two years prices on all pumps were reduced and selling expenses increased to meet competition and keep the plant operating at capacity. Second-quarter results for the current year, which follow, typify recent experience. ONTARIO PUMP COMPANY Income Statement Second Quarter (in thousands) R-Pump Sales ........................................................................... $10,200 Cost of goods sold ........................................................ 8,304 Gross margin ................................................................ 1,896 Selling and administrative expenses ............................... 2,070 Income before taxes ...................................................... (174) F-Pump $4,800 S -Pump Total $2,700 3,144 $2,700 2,310 2,850 $1,656 $ 390 $ (150) 1,110 555 405 $ 546 $ (165) $ (555) Maria Carlo, the company's president, is concerned about the results of the pricing, selling, and production prices. After reviewing the second-quarter results she asked her management staff to consider the following three suggestions: Discontinue the S-Pump line immediately. S-Pumps would not be returned to the product line unless the problems with the pump can be identified and resolved. Increase quarterly sales promotion by $300,000 on the R-Pump product line in order to increase sales volume by 15 percent. Cut production on the F-Pump line by 50 percent, and cut the traceable advertising and promotion for this line to $60,000 each quarter. Justin Sperry, the controller, suggested a more careful study of the financial relationships to determine the possible effects on the company's operating results of the president's proposed course of action. The president agreed and assigned JoAnn Brower, the assistant controller, to prepare an analysis. Brower has gathered the following information. The unit sales prices for the three pumps are as follows: R-Pump ................................................................................................... $600 F-Pump .................................................................................................... 270 S-Pump ................................................................................................... 540 The company is manufacturing at capacity and is selling all the pumps it produces. All three pumps are manufactured with common equipment and facilities. The selling and administrative expense is allocated to the three pump lines based on average sales volume over the past three years. Special selling expenses (primarily advertising, promotion, and shipping) are incurred for each pump as follows: $ $ Quarterly Advertising and Promotion R-Pump ................................................ F-Pump ................................................. S-Pump ................................................ $630,000 120,000 Shipping Expenses $30 per unit 12 per unit 30 per unit 300,000 The unit manufacturing costs for the three pumps are as follows: R-Pump Direct material .......................................................................... Direct labor .............................................................................. Variable manufacturing overhead ............................................... Fixed manufacturing overhead ................................................... Total ......................................................................................... F-Pump S -Pump $ 93 120 $135 45 $393 $ 51 60 $ 90 30 $231 $150 180 $180 60 $570 Required: 1. JoAnn Brower says that Ontario Pump Company's product-line income statement for the second quarter is not suitable for analyzing proposals and making decisions such as the ones suggested by Maria Carlo. Write a memo to Ontario Pump's president that addresses the following points. a. Explain why the product-line income statement as presented is not suitable for analysis and decision making. b. Describe an alternative income-statement format that would be more suitable for analysis and decision making, and explain why it is better. 2. Use the operating data presented for Ontario Pump Company and assume that the president's proposed course of action had been implemented at the beginning of the second quarter. Then evaluate the president's proposal by specifically responding to the following points. a. Are each of the three suggestions cost-effective? Support your discussion with an analysis that shows the net impact on income before taxes for each of the three suggestions. 2(a). Unit contribution margin, F-pump: $57 b. Was the president correct in proposing that the S-Pump line be eliminated? Explain your answer. c. Was the president correct in promoting the R-Pump line rather than the F-Pump line? Explain your answer. 2(c). Contribution per direct labor dollar, R-pump: $1.85 d. Does the proposed course of action make effective use of the company's capacity? Explain your answer. 3. Are there any qualitative factors that Ontario Pump Company's management should consider before it drops the S-Pump line? Explain your
