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 ntem, to develop an Excel production cost report for the month of MAY that will automaticaly ate 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 yellow cells within the purple Department Dota table. Although there are 5 two depertments, the controller wants you to focus your attention on the Blending 6 department until she is sure that your spreadsheet will provide accurate information Before you beein to formulate the May spreadsheet, you'll need to get production and g cost data for the month of May. Some of the inforrmation can be found on last month's manually prepared producion cost report, whilc other pieces of information can be found in the May general journal. In addition the production supervisor has already reported 20 that 220,000 units were started during May and that 30,000 units were 80% of the wey 22 completed at the end of the month. Curing your initial plant tour, you found out that all 23 of the direct materiats were added at the very beginning of the Blendine process 24 Orce you finish the spreadsheet, the controller has more questions and tusis for you (see the Additicnal Analysis worksheet after you heve completed the May Production Co 26 27 Report). 28 29 30 STEP-BY STEP INSTRUCTIONS 31 EXCEL HINTS 32Fn the information about units and percentages of completion in the Depertment 33 Data Table for May FORMAT percentages as percentages (not dedmes) 34 Copy and paste as uch as poss ble rather than typing in numbers alreedy cri one of the spreedsheets (for example, some of the cost data in the gereral lecger) (Hint: Gather from An easy way to make a cell reterence is type an-simand then h ghlight the ce l you war: to put in (rather than typing it) 2) Fill in the cost information in the Department Data Table for May 35 Aprits cost report and from the General Ledper) FORMAT in DOLLARS Ino cents) If you se l eet a ####### it simply means your column is rot wide enc ugh (if so, drag and drop 37 the ceiline so that the cell is wider) 36 38 39 3) Use cell references and formules to populate the blue cels in the production cost 41 E6 you should typc in the following formula To multipy, use To drvide, use To add, use+ To sabtrect, use report for example, in cell E4 you should type in the followinc reference B3. In cell : -E4 ES NOTE 0O NOT TYPE IN ANY NUMBERS. ALL BLUE CELLS SHOULD CONTAIN FORMULAS CR REFERENCES TO CEUS IN 4) Make surc allof the cost information is in dollars (use the dollar formst button) change it back to regular fons; cet rid of urnecessary decimal places, align nembers 6) Once you hinish the production cost report, po to the "Additional Analysis worksheet Make une of the Autosum button for adding cells together 43 THE YELLOW CELLS OVER IN THE PURPLE AREA ON THE LEFT Use Paste Special-Values" it what you are pasting is the result of a formula and you only ward the number copied rather than the 5) Clean up the spreadsheet formatting as necessary ( 48 prcperty,etc) for exemple, if arything is bold You can abo review the Lynda.com videos for Exce 51 for additionel requirerments and drecions. inns MY lendinq Dept Cost Report A pril Blending Dept Cost Report May General Journal Addional An Department Data - May Production Cost Report: FLOW OF UNITS Blending Department -May 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP DM EU Conversion EU Units to account for: Beginning work in process Started in production Total units to account for 7 Percentage of completion 8 Direct materials 9 Conversion 10 11 Costs in Beginning WIP 12 IDM in beginning wip 13 Conversion costs in beginning WIP 14 15 Cost information for the current month 16 DM traced to Dept during the month 17 DL traced to Dept during the month 18 MOH allocated to dept during the month 19 20 21 Units accounted for Completed and transfered out Ending WlP Total units accounted for Check figure 245,000 FLOW OF COSTS DM Conversion Costs to account for Beginnine work in process Costs added during month Total costs to account for Cost per equivalent unit ts accounted for. Completed and transferred out Ending WIP Cos 23 24 25 Total costs accounted for Check figure $ Production Cost Report: FLOW OF UNITS Blending Department - April Physical Units 2 DMEU ConversionEU 3 Units to account for: 4 Beginning work inprocess 5 Started in production 6 Total units to account for 8 Units accounted for: 9 Completed and transferred out Ending WIP 25,000 11 Total units accounted for 12 13 14 Costs to account for 15 Beginning work in process 16 Costs added during month 17 Total costs to account for 18 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out FLOW OF COSTS Total DMI Conversion 23 Ending WIP 24 Total costs accounted for 25 26 1 May Journal Entries- Partial list 2 3 WIP Blending 4Wages Payable 5 (to record Dl traced to the Blending Dept) 6 7 WIP-Blending 8 Raw materials inventory 9(to record DM traced to the Blending Dept) 45,000 45,000 1,153,750 1,153,750 10 11 WIP-Blending 617,600 12 MOH 13 (to record MOH allocated to the Blending Dept 14 15 WIP- Forming 16 Wages Payable 17 (to record DL traced to the Forming Dept) 18 19 WIP-Forming 20 Raw materials inventory 617,600 750,000 750,000 45,000 45,000 21 I(to record DM traced to the Forming Dept) 23 WIP- Forming 24MOH- Forming 25 (to record MOH allocated to the Forming Dept) 617,600 617,600 26 1 Additional Analysis 2)Total coet per sit ,-I-BlendingM 1 Blending CC 12 Forming cc 13 Total coat per unit 14 Cleck figure 10.45 Henkel Industric 17 Forthe Moth Ending Mst31 Per unit Total 25 2prting expences 23 26 WIP- Bleed 21 4 Blending CC Forming CC lecome Slatement F or the Month Edies May 3 2.579,600 Check figsre 60% is ssed? Briefly explhis the resson for the difference. Also.i This will be filled in by your instructor Points Possible 2 Requirement 3 File name 4 Submitted properly in Blackboard 2 ay Blending Dept- Correct cost and unit 6 information 10 May Blending Dept Cost Report-correct figures using ONLY cell references and 7 formulas 30 Spreadsheet "cleaned up" (dollars, 8 decimals aligned or deleted, etc. 2 9 ADDITIONALANALYSIS 10 Req 1: Journal entry 11 Req 2: Cost per unit 12 Req 3: Income Statement 13 Req 4: Inventory Analysis 14 Req 5A: Cost per unit 15 Req 5B: Income Statement 16 Req 5C: Difference 17 Req 5D: Explanation 18 |What if 60%? worksheet 19 20 13 12 2 2 100 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 ntem, to develop an Excel production cost report for the month of MAY that will automaticaly ate 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 yellow cells within the purple Department Dota table. Although there are 5 two depertments, the controller wants you to focus your attention on the Blending 6 department until she is sure that your spreadsheet will provide accurate information Before you beein to formulate the May spreadsheet, you'll need to get production and g cost data for the month of May. Some of the inforrmation can be found on last month's manually prepared producion cost report, whilc other pieces of information can be found in the May general journal. In addition the production supervisor has already reported 20 that 220,000 units were started during May and that 30,000 units were 80% of the wey 22 completed at the end of the month. Curing your initial plant tour, you found out that all 23 of the direct materiats were added at the very beginning of the Blendine process 24 Orce you finish the spreadsheet, the controller has more questions and tusis for you (see the Additicnal Analysis worksheet after you heve completed the May Production Co 26 27 Report). 28 29 30 STEP-BY STEP INSTRUCTIONS 31 EXCEL HINTS 32Fn the information about units and percentages of completion in the Depertment 33 Data Table for May FORMAT percentages as percentages (not dedmes) 34 Copy and paste as uch as poss ble rather than typing in numbers alreedy cri one of the spreedsheets (for example, some of the cost data in the gereral lecger) (Hint: Gather from An easy way to make a cell reterence is type an-simand then h ghlight the ce l you war: to put in (rather than typing it) 2) Fill in the cost information in the Department Data Table for May 35 Aprits cost report and from the General Ledper) FORMAT in DOLLARS Ino cents) If you se l eet a ####### it simply means your column is rot wide enc ugh (if so, drag and drop 37 the ceiline so that the cell is wider) 36 38 39 3) Use cell references and formules to populate the blue cels in the production cost 41 E6 you should typc in the following formula To multipy, use To drvide, use To add, use+ To sabtrect, use report for example, in cell E4 you should type in the followinc reference B3. In cell : -E4 ES NOTE 0O NOT TYPE IN ANY NUMBERS. ALL BLUE CELLS SHOULD CONTAIN FORMULAS CR REFERENCES TO CEUS IN 4) Make surc allof the cost information is in dollars (use the dollar formst button) change it back to regular fons; cet rid of urnecessary decimal places, align nembers 6) Once you hinish the production cost report, po to the "Additional Analysis worksheet Make une of the Autosum button for adding cells together 43 THE YELLOW CELLS OVER IN THE PURPLE AREA ON THE LEFT Use Paste Special-Values" it what you are pasting is the result of a formula and you only ward the number copied rather than the 5) Clean up the spreadsheet formatting as necessary ( 48 prcperty,etc) for exemple, if arything is bold You can abo review the Lynda.com videos for Exce 51 for additionel requirerments and drecions. inns MY lendinq Dept Cost Report A pril Blending Dept Cost Report May General Journal Addional An Department Data - May Production Cost Report: FLOW OF UNITS Blending Department -May 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP DM EU Conversion EU Units to account for: Beginning work in process Started in production Total units to account for 7 Percentage of completion 8 Direct materials 9 Conversion 10 11 Costs in Beginning WIP 12 IDM in beginning wip 13 Conversion costs in beginning WIP 14 15 Cost information for the current month 16 DM traced to Dept during the month 17 DL traced to Dept during the month 18 MOH allocated to dept during the month 19 20 21 Units accounted for Completed and transfered out Ending WlP Total units accounted for Check figure 245,000 FLOW OF COSTS DM Conversion Costs to account for Beginnine work in process Costs added during month Total costs to account for Cost per equivalent unit ts accounted for. Completed and transferred out Ending WIP Cos 23 24 25 Total costs accounted for Check figure $ Production Cost Report: FLOW OF UNITS Blending Department - April Physical Units 2 DMEU ConversionEU 3 Units to account for: 4 Beginning work inprocess 5 Started in production 6 Total units to account for 8 Units accounted for: 9 Completed and transferred out Ending WIP 25,000 11 Total units accounted for 12 13 14 Costs to account for 15 Beginning work in process 16 Costs added during month 17 Total costs to account for 18 19 Cost per equivalent unit 20 21 Costs accounted for: 22 Completed and transferred out FLOW OF COSTS Total DMI Conversion 23 Ending WIP 24 Total costs accounted for 25 26 1 May Journal Entries- Partial list 2 3 WIP Blending 4Wages Payable 5 (to record Dl traced to the Blending Dept) 6 7 WIP-Blending 8 Raw materials inventory 9(to record DM traced to the Blending Dept) 45,000 45,000 1,153,750 1,153,750 10 11 WIP-Blending 617,600 12 MOH 13 (to record MOH allocated to the Blending Dept 14 15 WIP- Forming 16 Wages Payable 17 (to record DL traced to the Forming Dept) 18 19 WIP-Forming 20 Raw materials inventory 617,600 750,000 750,000 45,000 45,000 21 I(to record DM traced to the Forming Dept) 23 WIP- Forming 24MOH- Forming 25 (to record MOH allocated to the Forming Dept) 617,600 617,600 26 1 Additional Analysis 2)Total coet per sit ,-I-BlendingM 1 Blending CC 12 Forming cc 13 Total coat per unit 14 Cleck figure 10.45 Henkel Industric 17 Forthe Moth Ending Mst31 Per unit Total 25 2prting expences 23 26 WIP- Bleed 21 4 Blending CC Forming CC lecome Slatement F or the Month Edies May 3 2.579,600 Check figsre 60% is ssed? Briefly explhis the resson for the difference. Also.i This will be filled in by your instructor Points Possible 2 Requirement 3 File name 4 Submitted properly in Blackboard 2 ay Blending Dept- Correct cost and unit 6 information 10 May Blending Dept Cost Report-correct figures using ONLY cell references and 7 formulas 30 Spreadsheet "cleaned up" (dollars, 8 decimals aligned or deleted, etc. 2 9 ADDITIONALANALYSIS 10 Req 1: Journal entry 11 Req 2: Cost per unit 12 Req 3: Income Statement 13 Req 4: Inventory Analysis 14 Req 5A: Cost per unit 15 Req 5B: Income Statement 16 Req 5C: Difference 17 Req 5D: Explanation 18 |What if 60%? worksheet 19 20 13 12 2 2 100