6 00 Henkel Industries produces its product in two sequential departments: Blending and Forming. 7 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 9 production cost report for the month of MAY that will automatically calculate the cost per 10 equivalent unit, etc., when the monthly information is dropped into Data area of the worksheet 11 (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 13 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. The controller has asked 15 that you prepare production cost reports under both the Weighted Average Cost Method and 16 the FIFO Method, so that the company can determine how to best assign costs to units 17 produced 12 14 18 20 19 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 (April's) 21 manually-prepared production cost report, while other pieces of information can be found in the May general ledger. Ending inventory from April was 100% complete with respect to materials 22 and 60% complete with respect to labor and overhead. In addition, the production supervisor 23 has already reported that 220,000 units were started during May and 35,000 units were still in 24 production, 80% of the way completed at the end of the month. During your initial plant tour, 25 you found out that all of the direct materials were added at the very beginning of the Blending 26 process. 27 28 Once you finish the spreadsheet, the controller has more questions and tasks for you (see the 29 Additional Analysis worksheet after you have completed the May Production Cost Report under both the Weighted Average and FIFO methods.). 30 31 32 33 34 Departmeat Data Production Report (FIFO Methad) UNIT INFORMATION Taformation about units Units in Beinning WIP Started during month Units in Fading WIP List account for Units in beginning work in process inventory Units started transferred in during the period Total units to account for Percentage of completion Beglanleg lavestory Direct materials Conversion Ending inventory Direct materials Conversion Equivalent Unite Converse Units Accounted feri Units completed & transferred out From beginning work in process inventory Started a completed Unitsin ending work in process inventory Total units accounted for Costs in Beginning WIP DM in beginning WIP Conversion costs in beginning WIP COST INFORMATION Total Materials Converse 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 Cette count for Cosi begining work in process inventory Costs incurred during the period Total cost account for 4 NOTE: Percentages must be formed as percentages (not Costs incurred during the period Divided by equivalent units Com per equivaleat unit 7 Doll amounts must be formates dollar around there are se decimals showing Costaccounted for Costs assigned to units from beginning work in proces Cost from prior period Costs to complete BWIP units is current period Costs wigned to its marted and completed this period Total cost completed and transferred out Costs assigned to its remaining in ending work in process inventory Total costed for 31 NOTE: ALL CELLS on the productos cost por MUST BE cell references er formas. NO 34 Conversion EU This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU nits to account for: Beginning work in process Started in production Cotal units to account for Jnits accounted for: Completed and transferred out Ending WIP Total units accounted for 30,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: 3 Completed and transferred out 4 Ending WIP 25 Total costs accounted for 26 27 213,200 $ 136,500 $ 76,700 This is a data tab only - no work required on this page! May Journal Entries - Partial list m $ 1,027,250 4 WIP-Blending 5 Raw materials inventory 6 (to record DM traced to the Blending Dept) $ 1,027,250 $ 55,000 9 $ 55,000 $ 623,800 $ 623,800 8 WIP-Blending Wages Payable 10 (to record DL traced to the Blending Dept) 11 12 WIP-Blending 13 MOH 14 (to record MOH allocated to the Blending Dept) 15 16 WIP-Forming 17 Raw materials inventory 18 (to record DM traced to the Forming Dept) 19 20 WIP- Forming 21 Wages Payable 22 (to record DL traced to the Forming Dept) 23 24 WIP-Forming 25 MOH-Forming 26 (to record MOH allocated to the Forming Dept) 27 $ 55,000 $ 55,000 $ 850,000 850,000 $ 723,200 $ 723,200 6 00 Henkel Industries produces its product in two sequential departments: Blending and Forming. 7 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 9 production cost report for the month of MAY that will automatically calculate the cost per 10 equivalent unit, etc., when the monthly information is dropped into Data area of the worksheet 11 (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 13 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. The controller has asked 15 that you prepare production cost reports under both the Weighted Average Cost Method and 16 the FIFO Method, so that the company can determine how to best assign costs to units 17 produced 12 14 18 20 19 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 (April's) 21 manually-prepared production cost report, while other pieces of information can be found in the May general ledger. Ending inventory from April was 100% complete with respect to materials 22 and 60% complete with respect to labor and overhead. In addition, the production supervisor 23 has already reported that 220,000 units were started during May and 35,000 units were still in 24 production, 80% of the way completed at the end of the month. During your initial plant tour, 25 you found out that all of the direct materials were added at the very beginning of the Blending 26 process. 27 28 Once you finish the spreadsheet, the controller has more questions and tasks for you (see the 29 Additional Analysis worksheet after you have completed the May Production Cost Report under both the Weighted Average and FIFO methods.). 30 31 32 33 34 Departmeat Data Production Report (FIFO Methad) UNIT INFORMATION Taformation about units Units in Beinning WIP Started during month Units in Fading WIP List account for Units in beginning work in process inventory Units started transferred in during the period Total units to account for Percentage of completion Beglanleg lavestory Direct materials Conversion Ending inventory Direct materials Conversion Equivalent Unite Converse Units Accounted feri Units completed & transferred out From beginning work in process inventory Started a completed Unitsin ending work in process inventory Total units accounted for Costs in Beginning WIP DM in beginning WIP Conversion costs in beginning WIP COST INFORMATION Total Materials Converse 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 Cette count for Cosi begining work in process inventory Costs incurred during the period Total cost account for 4 NOTE: Percentages must be formed as percentages (not Costs incurred during the period Divided by equivalent units Com per equivaleat unit 7 Doll amounts must be formates dollar around there are se decimals showing Costaccounted for Costs assigned to units from beginning work in proces Cost from prior period Costs to complete BWIP units is current period Costs wigned to its marted and completed this period Total cost completed and transferred out Costs assigned to its remaining in ending work in process inventory Total costed for 31 NOTE: ALL CELLS on the productos cost por MUST BE cell references er formas. NO 34 Conversion EU This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU nits to account for: Beginning work in process Started in production Cotal units to account for Jnits accounted for: Completed and transferred out Ending WIP Total units accounted for 30,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: 3 Completed and transferred out 4 Ending WIP 25 Total costs accounted for 26 27 213,200 $ 136,500 $ 76,700 This is a data tab only - no work required on this page! May Journal Entries - Partial list m $ 1,027,250 4 WIP-Blending 5 Raw materials inventory 6 (to record DM traced to the Blending Dept) $ 1,027,250 $ 55,000 9 $ 55,000 $ 623,800 $ 623,800 8 WIP-Blending Wages Payable 10 (to record DL traced to the Blending Dept) 11 12 WIP-Blending 13 MOH 14 (to record MOH allocated to the Blending Dept) 15 16 WIP-Forming 17 Raw materials inventory 18 (to record DM traced to the Forming Dept) 19 20 WIP- Forming 21 Wages Payable 22 (to record DL traced to the Forming Dept) 23 24 WIP-Forming 25 MOH-Forming 26 (to record MOH allocated to the Forming Dept) 27 $ 55,000 $ 55,000 $ 850,000 850,000 $ 723,200 $ 723,200