ABC Company has purchased Small Books publishing company that has three books a cookbook, travel guide, and handy helper. Each book sells for $10. The attached data file provides Small Books income statement for the most recent month.

After examining the income statement, the manager of ABC, Inc. is anxious to improve Small Books 5% return on sales. The manager believes the cookbook needs to be eliminated and all available resources should be used to promote the travel guide. However, before making the final decision, the manager wants you to do a more thorough analysis. The following additional information is available about Small Books

Only printing costs are variable; all other costs are fixed. The printing costs include material, labor , and variable overhead., which are traceable to the three product lines as shown in the income statement. Sales Commissions are 10% of sales for any product.

The same equipment is used to produce all three books, so the equipment depreciation cost has been allocated equally among the three product lines. The analysis of the companys activities indicates that the equipment is used 30% of the time to produce cookbooks, 50% of the time to produce travel guides and 20% of the time to product handy helpers.

The warehouse is used to store finished units of product so the rental cost has been allocated to the product lines on the basis of sales dollars. The warehouse rental cost is $3 per square foot per year. The warehouse contains 48,000 square feet of space, of which 7,200 square feet is used by the cookbook line, 24,000 square feet by the travel guide line and 16,800 square feet by the handy helper line.

The general sales cost shown in the income statement includes the salary of the sales managers and other sales costs not traceable to any specific product line. This cost has been allocated to the product lines on the basis of sales dollars.

The general administration cost and depreciation of office facilities both relate to the administration of the Small Books as a whole. These costa have been allocated equally to the three product lines. All other costs are traceable to the three product lines in the amounts show on the statement.

In your Excel file, below the imported data starting in row 20, prepare a segment income statement in good form. Adjust allocations of traceable costs for equipment depreciation and the warehouse rent as indicated in the additional information above. Where appropriate use cell referencing (do not hard type numbers) into the segment income statement and use formulas so the segment income statement is reusable. Be sure to include a total column for Small Books. See chapter 10 for proper segment income statement formatting. Keep rows 1-19 as area for data. Use the Warehouse Allocation and Equipment usage area in the imported data to enter data needed to allocate the expenses in the costs in the segment income statement. Make sure the Segment income statement can be reused.

Compute the contribution margin ratio for each product and assume an ample market is available for all three product lines. Prepare a 2-D bar chart comparing the segment contribution margins

Place answers to the following questions below the segment income statement in rows indicated. You will need to merge columns A-F and use word wrap and top and left align in order to place the answers into a single cell.

Based on your analysis has ABCs management made the right decision to eliminate the cookbook? Explain. Place Answer in Row 45.

Based on the statement you prepared and the contribution margins for each segment, do you agree with the decision to focus all available resources on promoting the travel guide? Explain. Prepare a 2-D or 3-D bar chart showing the sales and advertising expense (as two separate bars) per segment to help explain your answer. Place answer in Row 47.

What I have so far:

Small Books Publishers Segment Income Statement For Current Month Sales Revenue Less Variable Expenses CM Less Tracable Fixed Expenses Division Margin Common Expenses Net Operating Income