Complete using FIFO. Include CELLS.
B D G H M N PROJECT OVERVIEW 3 4 First, save this filo as Last name_First name_Case Study #2. You will submit this file to the Case Study #2 Canvas dropbox when completed. Questions regarding this case study should be sent to your graduate assistant(s) Zack Tillotson. 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, cto., when the monthly information is dropped into Data area of the workshect (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. The controller has asked that 5 you prepare production cost reports under both the Weighted Average Cost Method and the -6 FIFO Method, so that the company can determine how to best assign costs to units produced. 18 Before you begin to formulate the May spreadsheet, you'll need to get production and cost data 19 for the month of May. Some of the information can be found on last month's (April's) 20 manually prepared production cost report, while other pieces of information can be found in the 21 May general ledger. Ending inventory from April was 100% complete with respect to materials and 60% complete with respect to labor and overhead. In addition, the production supervisor has already reported that 220,000 units were started during May and 35,000 units were still in production, 80% of the way completed at the end of the month. During your initial plant tour, 24 you found out that all of the direct matcrials were added at the very beginning of the Blending 25 process 27 Once you finish the spreadsheet, the controller has more questions and tasks for you (sce the 28 Additional Analysis worksheet after you have completed the May Production Cost Report under both the Weighted Average and FIFO methods.). 30 17 22 23 26 29 31 32 EXCEL HINTS 34 36 39 STEP-BY-STEP INSTRUCTIONS 40 NOTE: Complete the Weighted Average Production Report first the complete the FIFO Production 42 431) Fill in the information about it and percentages of completion in the May blue box. FORMAT 44 percentages as percentage of decimals). Falls the cost information in the May blue box. (Hint: 49 Gather from April's cost report and from the General Ledger). FORMAT IS DOLLARS (no cents). If you get it simply means your column is not wide enough of so, drag and drop the cell line so that the cell is wider) 482) Uscellence and formules to populated production cost reports. For example, in cell E6 you should type in the following worse. In cell you should type in the following formular - SUMOLE). NOTE: DO NOT TYPIN ANY NUMBERS. ALL CELLS SHOULD CONTAIN 50 FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX 51 523) Make all of telefonos dollars use the dollar format button). 53 Cles up the spread forming secary for example, trid of car decimal place - "Copy and paste" as much as possible rather than already on one of the spreadsheets (for example, som in the general ledger). -An easy way to make a cell reference is type an si highlight the cell you want to put in (rather than typin To multiply, use To divide, used To add, use + To subtract, use - - Make use of the AutoSum button for adding cells toget -Use "Paste Special Values" if what you are pasting is th formula and you only want the number copied rather than 365) Once you finish the productos report, go to the Ad Analysieworksheet for dial 57 uirements and directions Production Report (Welgted Average Method) UNIT INFORMATION 30000 220000 35000 1 Department Data 2 formata about units: 3 Units in Beginning WIP 4 Started during month 5 Unisin Ending WIP 5 > Percentage of completion: Beginning inventory 5 Direct mais 30 Comic mading lavery 12 Direct materials 1 Conversion Units for Units in beginning work in process invento Units started transferred in during the period Total units to see for 30000 220000 250000 100% 80% Units account Chits completed & transferred Unitsin ending work in process by Total unit counted for 5 S $ 215,000.00 35,000.00 35,313.00 34 Equivalent Materials 215,000.00 35,000.00 250,000.00 S 5 $ Conversion 215,000.00 28,000.00 243,000.00 1 tsis Beginning WIP 16 DM is beginning WIP 17 Cordon costs in beinni WIP 18 $ 5 136,500.00 76,700.00 COST INFORMATION 5 29 DM traced to Dep during month 21 DL trued to Del during the most MONT de dept during the more 1.027.250,00 $5,000.00 623,800.00 Contato Costs in aning work in processor Costa neurred during the red Total costesses for S 5 Total 213.200.00 1.100.000 1.919.250.00 5 5 $ Materials 136.500.00 1,027.250.00 1.169.750.00 Conversion 76,700.00 678,800.00 755. 500.00 $ $ 26 NOTE 25 Femt temas percentage Mecima) 26 29 but the formatos dos around on to decis shown 29 10 11 NOTEI ALL CELLS on the cost 12 MUST or som NO Telco Davided by qui Cost per equivalent 5 5 4.665 311 15 Costs completed transferred COMS Tending work inventory Total cost accounted for 1.650 21.50 249.978.50 1.919.250.00 ES 5 1.000 25.00 167,225.00 S 1.163.250.00 668446,50 87.053.50 755 500.00 5 Production Report WIPO Method) UNT INFORMATION Departo Information about Chits in Berlin WIP Started durim Laitis Ending WIP Units in beginning work is process is ventes Units sedere in dure the period Total units to count for Percentage of completion: Beginning went Tram Conveni 1 Endir insan 2 Direct mais us Convenion Materials Conversion Units completed transferred out From belesin work in processive Started comitted Units in die work in story Total units and for 15 Ceste in being WIP 16 Wishini COSTINOAMATION 10 19 Costilnaties for the current Total Materials Conversion Contato accer Cests in beginning work in process inventory Cests in the Period Total sconto TXtreed to Dept during the most 22 21 14 NOTE: 25 Percentagemate 20 27 Date de 25 Cened during the period Divided by equivalent Cost per equitatunit Casioinning wikin Completenit in een 31 NOTE: ALL CELLS 37 MUSTENO Casi toitmin Ocean 35 14 Lumayended Journal Entry Debat Credit 2) Total cost per un (Weighted Average) Blending Direct Materials Blading Convenion Costs Foring Direct Me Form Conversion Costs Test per Now that you have come the producator Complete the wing the post the stroller has decide to tie the Wind Aage Motos un 1) Record the journal aty that soods to be made to let the units completed and send out of the Bleding Drept during the month 2) the remedies, the Forming Det showed the follows per equivalent on their Maygroduction to repeti Duet Material Cow S10 CC-100 The emotions to know the coming one unit of product in May.tom START FINISH Show your calculations by loting each oft per left that the wall FORMAT the critindoles and HIHLIGHT 3) During the method 30,000 of the the produced during May at a sale price of 530 per ratings for the most 3525,000. The controller welke you to represent of the company's income statement. She would like you to include information about the polper wall, well as the total for the month, ending with opening income Make sure you fineteying in dollars and conta At the egg of May, the company had to begining ished Goods (FG) inventory and he beginning Works Process (WIP) level theorg Darms. All referred out of the Weeding. May we made it completely through the Forming Dept. Is Meg controller will you are of the compending IP and May a) wire Departem's Eating What is the color for her Finding WIP? chorary is reeds story 11 13 13 14 Henk Industries Income Start For the Math Ending May 31 Per unit Total 9) Ano de controller determined that the Company should use the weighted Average Method to main co to its produced, she wants you run your eyes to see what repet the wing the FIFO method would have on A) coat per unit, and by the company income tentent for the Binding Department). Ir FIFO method was used, would perking income be higher or lower, and toy bow much low cald agerinlait 19 10 21 12 23 24 25 wally, check the Ordege workert there you lose completed all requirem. These your ideal otte dupher 27 Us Co I 19 30 31 SA) TIPOS Winding Me 16 Blendinga Yong 10 5A) Total cost per unit (FIFO): Blending Direct Materials Blending Conversion Costs Forming Direct Materials Forming Conversion Costs Total cost per unit 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total Difference in operating income (Compute S difference): Increase or Decrease when FIFO is used? Briefly explain the reason for the difference. Also, if managers want to inflate earnings in a particular period, what can they do? Could managers manipulate earnings by changing their percentage of completion estimates? 1 2 63 54 55 56 57 58 59 60 61 62 63 64 65 66 67 Directions 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 Units to account for: Beginning work in process Started in production Total units to account for m 30,000. Total DM Conversion 9 Units accounted for: 10 Completed and transferred out 11 Ending WIP 12 Total units accounted for 13 14 FLOW OF COSTS 15 Costs to account for: 16 Beginning work in process 17 Costs added during month 18 Total costs to account for 19 20 Cost per equivalent unit 21 22 Costs accounted for: 23 Completed and transferred out 24 Ending WIP 25 Total costs accounted for 26 27 213,200 $ 136,500 S 76,700 May Journal Entries - Partial list $ 1,027,250 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) $ 1,027,250 $ 55,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) $ 55,000 $ 623,800 WIP-Blending MOH (to record MOH allocated to the Blending Dept) $ 623,800 $ 55,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) $ 55,000 $ WIP- Forming Wages Payable (to record DL traced to the Forming Dept) 850,000 $ 850,000 B $ WIP-Forming MOH-Forming 5 (to record MOH allocated to the Forming Dept) 5 1723,200 $ 723,200 10 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. The controller has asked that you prepare production cost reports under both the Weighted Average Cost Method and the FIFO Method, so that the company can determine how to best assign costs to units 7 produced. 8 19 Before you begin to formulate the May spreadsheet, you'll need to get production and cost data 20 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 22 May general ledger. Ending inventory from April was 100% complete with respect to materials and 60% complete with respect to labor and overhead. In addition, the production supervisor 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, you found out that all of the direct materials were added at the very beginning of the Blending U 23 25 26 process 27 28 29 30 31 32 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 under both the Weighted Average and FIFO methods.). Productos Report (PIPU Method) UNIT INFORMATION Departments Information about unite Units in Beinnine WIP Started during month Units in Hinding WIP Units to account for Units in beginning work in process inventory Units started transferred in during the period Total units to account for Percentage of completion Beginning inventory Direct materials Conversion Ending lavesten Direct materiale Conversion Equivalent Units Materials Converse Units accounted for: Units completed transferred out From beginning work in process inventory Started a completed Unitsin ending work in process inventory Total Waits sected for Con la elaning WIP DM is beginning WIP Conversion cousin beginning WIP CONT INFORMATION Materiale Cereri Con information for the current meth DM traced to Dept during the month 1 DL traced to Dept during the month 2 MOH allocated to dept during the most Contact for Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 NOTE: s Percentages must be formed as percentages et 26 27 Dolme befondas dos Costs incurred during the period Divided by equivalent units Cost per equivalent wait 23 10 11 NOTE: ALL CELLS on the production 32 par MUST BE cell forces or for NO bard ball! 3 Costurated for Costs assigned to units from beginning work in process Costs from prior period Costs to complete WWIP si in current period Costs assigned to units started and completed his period Total cost completed and transferred out Costs assigned to units remaining is ending work in process inventory Total costs accounted for B D G H M N PROJECT OVERVIEW 3 4 First, save this filo as Last name_First name_Case Study #2. You will submit this file to the Case Study #2 Canvas dropbox when completed. Questions regarding this case study should be sent to your graduate assistant(s) Zack Tillotson. 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, cto., when the monthly information is dropped into Data area of the workshect (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. The controller has asked that 5 you prepare production cost reports under both the Weighted Average Cost Method and the -6 FIFO Method, so that the company can determine how to best assign costs to units produced. 18 Before you begin to formulate the May spreadsheet, you'll need to get production and cost data 19 for the month of May. Some of the information can be found on last month's (April's) 20 manually prepared production cost report, while other pieces of information can be found in the 21 May general ledger. Ending inventory from April was 100% complete with respect to materials and 60% complete with respect to labor and overhead. In addition, the production supervisor has already reported that 220,000 units were started during May and 35,000 units were still in production, 80% of the way completed at the end of the month. During your initial plant tour, 24 you found out that all of the direct matcrials were added at the very beginning of the Blending 25 process 27 Once you finish the spreadsheet, the controller has more questions and tasks for you (sce the 28 Additional Analysis worksheet after you have completed the May Production Cost Report under both the Weighted Average and FIFO methods.). 30 17 22 23 26 29 31 32 EXCEL HINTS 34 36 39 STEP-BY-STEP INSTRUCTIONS 40 NOTE: Complete the Weighted Average Production Report first the complete the FIFO Production 42 431) Fill in the information about it and percentages of completion in the May blue box. FORMAT 44 percentages as percentage of decimals). Falls the cost information in the May blue box. (Hint: 49 Gather from April's cost report and from the General Ledger). FORMAT IS DOLLARS (no cents). If you get it simply means your column is not wide enough of so, drag and drop the cell line so that the cell is wider) 482) Uscellence and formules to populated production cost reports. For example, in cell E6 you should type in the following worse. In cell you should type in the following formular - SUMOLE). NOTE: DO NOT TYPIN ANY NUMBERS. ALL CELLS SHOULD CONTAIN 50 FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX 51 523) Make all of telefonos dollars use the dollar format button). 53 Cles up the spread forming secary for example, trid of car decimal place - "Copy and paste" as much as possible rather than already on one of the spreadsheets (for example, som in the general ledger). -An easy way to make a cell reference is type an si highlight the cell you want to put in (rather than typin To multiply, use To divide, used To add, use + To subtract, use - - Make use of the AutoSum button for adding cells toget -Use "Paste Special Values" if what you are pasting is th formula and you only want the number copied rather than 365) Once you finish the productos report, go to the Ad Analysieworksheet for dial 57 uirements and directions Production Report (Welgted Average Method) UNIT INFORMATION 30000 220000 35000 1 Department Data 2 formata about units: 3 Units in Beginning WIP 4 Started during month 5 Unisin Ending WIP 5 > Percentage of completion: Beginning inventory 5 Direct mais 30 Comic mading lavery 12 Direct materials 1 Conversion Units for Units in beginning work in process invento Units started transferred in during the period Total units to see for 30000 220000 250000 100% 80% Units account Chits completed & transferred Unitsin ending work in process by Total unit counted for 5 S $ 215,000.00 35,000.00 35,313.00 34 Equivalent Materials 215,000.00 35,000.00 250,000.00 S 5 $ Conversion 215,000.00 28,000.00 243,000.00 1 tsis Beginning WIP 16 DM is beginning WIP 17 Cordon costs in beinni WIP 18 $ 5 136,500.00 76,700.00 COST INFORMATION 5 29 DM traced to Dep during month 21 DL trued to Del during the most MONT de dept during the more 1.027.250,00 $5,000.00 623,800.00 Contato Costs in aning work in processor Costa neurred during the red Total costesses for S 5 Total 213.200.00 1.100.000 1.919.250.00 5 5 $ Materials 136.500.00 1,027.250.00 1.169.750.00 Conversion 76,700.00 678,800.00 755. 500.00 $ $ 26 NOTE 25 Femt temas percentage Mecima) 26 29 but the formatos dos around on to decis shown 29 10 11 NOTEI ALL CELLS on the cost 12 MUST or som NO Telco Davided by qui Cost per equivalent 5 5 4.665 311 15 Costs completed transferred COMS Tending work inventory Total cost accounted for 1.650 21.50 249.978.50 1.919.250.00 ES 5 1.000 25.00 167,225.00 S 1.163.250.00 668446,50 87.053.50 755 500.00 5 Production Report WIPO Method) UNT INFORMATION Departo Information about Chits in Berlin WIP Started durim Laitis Ending WIP Units in beginning work is process is ventes Units sedere in dure the period Total units to count for Percentage of completion: Beginning went Tram Conveni 1 Endir insan 2 Direct mais us Convenion Materials Conversion Units completed transferred out From belesin work in processive Started comitted Units in die work in story Total units and for 15 Ceste in being WIP 16 Wishini COSTINOAMATION 10 19 Costilnaties for the current Total Materials Conversion Contato accer Cests in beginning work in process inventory Cests in the Period Total sconto TXtreed to Dept during the most 22 21 14 NOTE: 25 Percentagemate 20 27 Date de 25 Cened during the period Divided by equivalent Cost per equitatunit Casioinning wikin Completenit in een 31 NOTE: ALL CELLS 37 MUSTENO Casi toitmin Ocean 35 14 Lumayended Journal Entry Debat Credit 2) Total cost per un (Weighted Average) Blending Direct Materials Blading Convenion Costs Foring Direct Me Form Conversion Costs Test per Now that you have come the producator Complete the wing the post the stroller has decide to tie the Wind Aage Motos un 1) Record the journal aty that soods to be made to let the units completed and send out of the Bleding Drept during the month 2) the remedies, the Forming Det showed the follows per equivalent on their Maygroduction to repeti Duet Material Cow S10 CC-100 The emotions to know the coming one unit of product in May.tom START FINISH Show your calculations by loting each oft per left that the wall FORMAT the critindoles and HIHLIGHT 3) During the method 30,000 of the the produced during May at a sale price of 530 per ratings for the most 3525,000. The controller welke you to represent of the company's income statement. She would like you to include information about the polper wall, well as the total for the month, ending with opening income Make sure you fineteying in dollars and conta At the egg of May, the company had to begining ished Goods (FG) inventory and he beginning Works Process (WIP) level theorg Darms. All referred out of the Weeding. May we made it completely through the Forming Dept. Is Meg controller will you are of the compending IP and May a) wire Departem's Eating What is the color for her Finding WIP? chorary is reeds story 11 13 13 14 Henk Industries Income Start For the Math Ending May 31 Per unit Total 9) Ano de controller determined that the Company should use the weighted Average Method to main co to its produced, she wants you run your eyes to see what repet the wing the FIFO method would have on A) coat per unit, and by the company income tentent for the Binding Department). Ir FIFO method was used, would perking income be higher or lower, and toy bow much low cald agerinlait 19 10 21 12 23 24 25 wally, check the Ordege workert there you lose completed all requirem. These your ideal otte dupher 27 Us Co I 19 30 31 SA) TIPOS Winding Me 16 Blendinga Yong 10 5A) Total cost per unit (FIFO): Blending Direct Materials Blending Conversion Costs Forming Direct Materials Forming Conversion Costs Total cost per unit 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total Difference in operating income (Compute S difference): Increase or Decrease when FIFO is used? Briefly explain the reason for the difference. Also, if managers want to inflate earnings in a particular period, what can they do? Could managers manipulate earnings by changing their percentage of completion estimates? 1 2 63 54 55 56 57 58 59 60 61 62 63 64 65 66 67 Directions 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 Units to account for: Beginning work in process Started in production Total units to account for m 30,000. Total DM Conversion 9 Units accounted for: 10 Completed and transferred out 11 Ending WIP 12 Total units accounted for 13 14 FLOW OF COSTS 15 Costs to account for: 16 Beginning work in process 17 Costs added during month 18 Total costs to account for 19 20 Cost per equivalent unit 21 22 Costs accounted for: 23 Completed and transferred out 24 Ending WIP 25 Total costs accounted for 26 27 213,200 $ 136,500 S 76,700 May Journal Entries - Partial list $ 1,027,250 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) $ 1,027,250 $ 55,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) $ 55,000 $ 623,800 WIP-Blending MOH (to record MOH allocated to the Blending Dept) $ 623,800 $ 55,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) $ 55,000 $ WIP- Forming Wages Payable (to record DL traced to the Forming Dept) 850,000 $ 850,000 B $ WIP-Forming MOH-Forming 5 (to record MOH allocated to the Forming Dept) 5 1723,200 $ 723,200 10 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. The controller has asked that you prepare production cost reports under both the Weighted Average Cost Method and the FIFO Method, so that the company can determine how to best assign costs to units 7 produced. 8 19 Before you begin to formulate the May spreadsheet, you'll need to get production and cost data 20 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 22 May general ledger. Ending inventory from April was 100% complete with respect to materials and 60% complete with respect to labor and overhead. In addition, the production supervisor 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, you found out that all of the direct materials were added at the very beginning of the Blending U 23 25 26 process 27 28 29 30 31 32 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 under both the Weighted Average and FIFO methods.). Productos Report (PIPU Method) UNIT INFORMATION Departments Information about unite Units in Beinnine WIP Started during month Units in Hinding WIP Units to account for Units in beginning work in process inventory Units started transferred in during the period Total units to account for Percentage of completion Beginning inventory Direct materials Conversion Ending lavesten Direct materiale Conversion Equivalent Units Materials Converse Units accounted for: Units completed transferred out From beginning work in process inventory Started a completed Unitsin ending work in process inventory Total Waits sected for Con la elaning WIP DM is beginning WIP Conversion cousin beginning WIP CONT INFORMATION Materiale Cereri Con information for the current meth DM traced to Dept during the month 1 DL traced to Dept during the month 2 MOH allocated to dept during the most Contact for Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 NOTE: s Percentages must be formed as percentages et 26 27 Dolme befondas dos Costs incurred during the period Divided by equivalent units Cost per equivalent wait 23 10 11 NOTE: ALL CELLS on the production 32 par MUST BE cell forces or for NO bard ball! 3 Costurated for Costs assigned to units from beginning work in process Costs from prior period Costs to complete WWIP si in current period Costs assigned to units started and completed his period Total cost completed and transferred out Costs assigned to units remaining is ending work in process inventory Total costs accounted for
