PROJECT OVERVIEW First, save this file as Process Costing_group_number Henkel Industries produces its product in two sequential departments: Blending and Forming. The old controller, who has just retired, has been manually preparing the production cost reports each month. The new controller would like you, as an intern, to develop an Excel production cost report for the month of MAY that will automatically calculate the cost per equivalent unit, etc., when the monthly information is dropped into Data area of the worksheet (the data area is shown as a blue box). The controller has provided you with a basic template, but needs to you finish the spreadsheet by adding formulas and cell references as needed. The only place she ever wants to type in numbers is in the blue box. Although there are two departments, the controller wants you to focus your attention on the Blending department until she is sure that your spreadsheet will provide accurate information. Before you begin to formulate the May spreadsheet, you'll need to get production and cost data for the month of May. Some of the information can be found on last month's manually-prepared production cost report, while other pieces of information can be found in the May general ledger. In addition the production supervisor has already reported that 220,000 units were started during May and that 30,000 units were 80% of the way completed at the end of the month. During your initial plant tour, you found out that all of the direct materials were added at the very beginning of the Blending process. Once you finish the spreadsheet, the controller has more questions and tasks for you (see the Additional Analysis worksheet after you have completed the May Production Cost Report). Conversion EU Department Data Information about units: Units in Beginning WIP Started during month Units in Ending WIP $ 25,000.00 $ 220,000.00 $ 30,000.00 Production Cost Report: Blending Department FLOW OF UNITS Physical Units DM EU Units to account for: Beginning work in process $ 25,000.00 Started in production $ 220,000.00 Total units to account for $ 245,000.00 Percentage of completion: Direct materials Conversion 100% 80% Units accounted for: Completed and transferred out Ending WIP Total units accounted for $ $ $ 215,000.00 $ 30,000.00 $ 245,000.00 $ 215,000.00 $ 30,000.00 $ 245,000.00 $ 215,000.00 24,000.00 239,000.00 Costs in Beginning WIP DM in beginning WIP Conversion costs in beginning WIP $ 132,500.00 $ 78,300.00 Total DM Conversion Cost information for the current month: DM traced to Dept during the month DL traced to Dept during the month MOH allocated to dept during the month FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for $ 1,153,750.00 $ 45,000.00 $ 617,600.00 $ $ 210800 1,816,350.00 $ 2,027,150.00 $ 132500 1,153,750.00 $ 1,286,250.00 $ 78300 662,600.00 740,900.00 Cost per equivalent unit $ 8.35 $ 5.25 $ 3.10 Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ $ $ 1,795,250.00 $ 231,900.00 $ 2,027,150.00 $ 1,128,750.00 $ 157,500.00 $ 1,286,250.00 $ 666,500.00 74,400.00 740,900.00 Dept. DM EU Conversion EU Production Cost Report: FLOW OF UNITS Physical Units Units to account for: Beginning work in process Started in production Total units to account for Units accounted for: Completed and transferred out Ending WIP Total units accounted for 25,000 Total DM Conversion FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for Cost per equivalent unit Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ 210,800 $ 132,500 $ 78,300 May Journal Entries- Partial list 45,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) 45,000 1,153,750 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) 1,153,750 617,600 WIP-Blending MOH (to record MOH allocated to the Blending Dept) 617,600 750,000 WIP- Forming Wages Payable (to record DL traced to the Forming Dept) 750,000 45,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) 45,000 617,600 WIP- Forming MOH-Forming (to record MOH allocated to the Forming Dept) 617,600 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. If 60% is used as the percentage of completion, would operating income be higher or lower, and by how much? How could managers inflate earnings? PROJECT OVERVIEW First, save this file as Process Costing_group_number Henkel Industries produces its product in two sequential departments: Blending and Forming. The old controller, who has just retired, has been manually preparing the production cost reports each month. The new controller would like you, as an intern, to develop an Excel production cost report for the month of MAY that will automatically calculate the cost per equivalent unit, etc., when the monthly information is dropped into Data area of the worksheet (the data area is shown as a blue box). The controller has provided you with a basic template, but needs to you finish the spreadsheet by adding formulas and cell references as needed. The only place she ever wants to type in numbers is in the blue box. Although there are two departments, the controller wants you to focus your attention on the Blending department until she is sure that your spreadsheet will provide accurate information. Before you begin to formulate the May spreadsheet, you'll need to get production and cost data for the month of May. Some of the information can be found on last month's manually-prepared production cost report, while other pieces of information can be found in the May general ledger. In addition the production supervisor has already reported that 220,000 units were started during May and that 30,000 units were 80% of the way completed at the end of the month. During your initial plant tour, you found out that all of the direct materials were added at the very beginning of the Blending process. Once you finish the spreadsheet, the controller has more questions and tasks for you (see the Additional Analysis worksheet after you have completed the May Production Cost Report). Conversion EU Department Data Information about units: Units in Beginning WIP Started during month Units in Ending WIP $ 25,000.00 $ 220,000.00 $ 30,000.00 Production Cost Report: Blending Department FLOW OF UNITS Physical Units DM EU Units to account for: Beginning work in process $ 25,000.00 Started in production $ 220,000.00 Total units to account for $ 245,000.00 Percentage of completion: Direct materials Conversion 100% 80% Units accounted for: Completed and transferred out Ending WIP Total units accounted for $ $ $ 215,000.00 $ 30,000.00 $ 245,000.00 $ 215,000.00 $ 30,000.00 $ 245,000.00 $ 215,000.00 24,000.00 239,000.00 Costs in Beginning WIP DM in beginning WIP Conversion costs in beginning WIP $ 132,500.00 $ 78,300.00 Total DM Conversion Cost information for the current month: DM traced to Dept during the month DL traced to Dept during the month MOH allocated to dept during the month FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for $ 1,153,750.00 $ 45,000.00 $ 617,600.00 $ $ 210800 1,816,350.00 $ 2,027,150.00 $ 132500 1,153,750.00 $ 1,286,250.00 $ 78300 662,600.00 740,900.00 Cost per equivalent unit $ 8.35 $ 5.25 $ 3.10 Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ $ $ 1,795,250.00 $ 231,900.00 $ 2,027,150.00 $ 1,128,750.00 $ 157,500.00 $ 1,286,250.00 $ 666,500.00 74,400.00 740,900.00 Dept. DM EU Conversion EU Production Cost Report: FLOW OF UNITS Physical Units Units to account for: Beginning work in process Started in production Total units to account for Units accounted for: Completed and transferred out Ending WIP Total units accounted for 25,000 Total DM Conversion FLOW OF COSTS Costs to account for: Beginning work in process Costs added during month Total costs to account for Cost per equivalent unit Costs accounted for: Completed and transferred out Ending WIP Total costs accounted for $ 210,800 $ 132,500 $ 78,300 May Journal Entries- Partial list 45,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) 45,000 1,153,750 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) 1,153,750 617,600 WIP-Blending MOH (to record MOH allocated to the Blending Dept) 617,600 750,000 WIP- Forming Wages Payable (to record DL traced to the Forming Dept) 750,000 45,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) 45,000 617,600 WIP- Forming MOH-Forming (to record MOH allocated to the Forming Dept) 617,600 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 60% complete. She wants you to run your spreadsheet using 60% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's income statement for the month. If 60% is used as the percentage of completion, would operating income be higher or lower, and by how much? How could managers inflate earnings