Name: Date: Instructor: Course: Managerial Accounting, 3rd Edition, by Weygandt, Kieso, and Kimmel Solving Managerial Accounting Problems Using Microsoft Excel for Windows by Rex A Schildhouse Problem P4-1B, Waves Galore, Inc. manufactures hair curlers and blow-dryers. The handheld hair curler is Waves Galore's high volume product, 80,000 units annually. It is a "large barrel", 20-watt, triple-heat appliance designed to appeal to the teenage market segment with its glow-in-the-dark handle. The handheld blow-dryer is Waves Galore's lower-volume product, 40,000 units annually. IT is a three-speed, 2,000 watt appliance with a "cool setting" and a removable filter. It also is designed for the teen market. Both products require 1 hour of direct labor for completion. Therefore, total annual direct labor hours are 120,000 (80,000 + 40,000) Expected annual manufacturing overhead is $441,600 Thus, the predetermined overhead rate is $3.65 per direct labor hour. The direct material cost per unit is $5.25 for the hair curler and $9.75 for the blow-dryer. The direct labor cost is $8.00 per unit for the hair curler and blow-dryer. Waves Galore purchases most of its parts from suppliers and assembles the finished products at its Fargo, North Dakota plant. It recently adopted activity-based costing, which after year-end will totally replace its traditional direct labor-based cost accounting system. Waves Galore has identified the following six activity cost pools and related cost drivers and has assembled the following information. Expected Expected Use of Drivers by Product Estimated Use of Cost Activity Cost Pool Cost Driver Overhead Drivers Curlers Dryers Purchasing Orders $57,500 500 170 330 Receiving Pounds 42,000 140,000 58,000 82,000 Assembling Parts 166,000 830,000 415,000 415,000 Testing Tests 52,000 130,000 82,000 48,000 Finishing Units 60,000 120,000 80,000 40,000 Packing and shipping Cartons 60,500 12,100 8,040 4,060 $438,000 Instructions: (a) Under traditional product costing, compute the total unit cost of both products. Prepare a single comparative schedule of the individual costs by products, in the format provided below. Products Manufacturing Costs Home Model Commercial Model Label Amount Amount Label Amount Amount Label Amount Amount Total unit cost Formula Formula (b) Under ABC, prepare a schedule showing the computations of the activity-based overhead rates (per cost driver). Estimated Total Expected Use of Activity-Based Activity Cost Pool ÷ = Overhead Cost Drivers Overhead Rate Purchasing Amount ÷ Amount Label = Amount Label Receiving Amount ÷ Amount Label = Amount Label Assembling Amount ÷ Amount Label = Amount Label Testing Amount ÷ Amount Label = Amount Label Finishing Amount ÷ Amount Label = Amount Label Packing and shipping Amount ÷ Amount Label = Amount Label Formula FileName: b2ac86a2-3fad-4926-89bf-c5a090bf1099.xls, Tab: Problem P4-1B, Page 1 of 3, 4/21/2011, 1:31 PM (c) Prepare a schedule assigning each activity's overhead cost pool to each product based on the use of cost drivers. (Include a computation of overhead cost per unit, rounding to the nearest cent.) Curler Dryer Activity- Activity- Expected Expected Based Cost Based Cost Activity Cost Pool Use of Use of Overhead Assigned Overhead Assigned Drivers Drivers Rates Rates Label Amount Amount Formula Amount Amount Formula Label Amount Amount Formula Amount Amount Formula Label Amount Amount Formula Amount Amount Formula Label Amount Amount Formula Amount Amount Formula Label Amount Amount Formula Amount Amount Formula Label Amount Amount Formula Amount Amount Formula Total costs assigned Formula Formula Units produced Amount Amount Overhead cost per unit Formula Formula (d) Compute the total cost per unit for each product under ABC. ABC Manufacturing Costs Curler Dryer Direct materials Amount Amount Direct labor Amount Amount Overhead Amount Amount Total cost per unit Formula Formula (e) Classify each of the activities as a value-added activity or a non-value-added activity. Activity Value - Vs - Non-Value-Added Label Value added or Non-value-added Label Value added or Non-value-added Label Value added or Non-value-added Label Value added or Non-value-added Label Value added or Non-value-added Label Value added or Non-value-added (f)(1) Comment on the comparative overhead cost per unit for the two products under ABC. (f)(2) Comment on the comparative total costs per unit under traditional costing and ABC. FileName: b2ac86a2-3fad-4926-89bf-c5a090bf1099.xls, Tab: Problem P4-1B, Page 2 of 3, 4/21/2011, 1:31 PM Instructions for the Microsoft Excel Templates Detail and information on Excel is contained within the manual. Striking the "F1" key or following the path "Windows>Excel Help" will invoke the Office Assistant and bring up one of several help menus. Type your name into the cell "D5". This will be copied by formula to the rest of the pages as required. Type the due date of your problem into cell "I5". This will be copied by formula to the rest of the pages as required. Type the instructor's name into cell "D6". This will be copied by formula to the rest of the pages as required. Type the course identifier into cell "I6". This will be copied by formula to the rest of the pages as required. The problem is identified for you in cell "B7". In "DATE" cells enter the date in any of several formats and Excel will format it correctly. If more than one page is preformatted into the problem, page breaks are preset and formulas are set to copy the header into the remaining pages. Place the proper account title in the cell where the word "ACCOUNT" appears on the template. Place the amount in the cell where the word "AMOUNT" appears on the template. A formula may be placed in Enter a number like 914 to signify units or gallons where the word "NUMBER" appears. Write a formula into cells where the word "FORMULA" appears. In these cells, an amount calculated outside of Place the explanation for the entry in the cell where the word "EXPLANATION" appears on the template. Insert the account number where "ACCT #" appears on the template during posting. Insert the journal reference where "JOURN #" appears on the template during posting. Insert the title in the cell where "TITLE" appears on the template. The print area is defined to fit onto 8 1/2" X 11" sheets in portrait or landscape mode as required. The gray filled cells define the perimeter of the problem and the print area. The problem is formatted for whole dollars with comma separations (no cents) except where required. The display may have "Freeze Pane" invoked so column titles remain visible during data entry. Negative values may be shown as ($400) vice -$400. Enter a string like: ($259,417 X 12 months) + (0.3651 X 5,434,631) where the word TEXT appears.