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-based 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 a Data area of the worksheet (the data area is shown as a blue box in the following worksheets). 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.

As 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

1) Start with the May Blending Dept Cost Report. Fill in the information about units and percentages of completion in the May blue box. The blue box is the only place on the worksheet where you are allowed to type in numbers. FORMAT percentages as %, not as decimals. Use the % formatting icon on the Excel Ribbon (top bar of the screen)

2) Fill in the cost information in the May blue box. (Hint on finding information: Gather information from April's cost report and from the General Ledger). Format in DOLLARS and CENTS using the $ formatting icon on the ribbon. If you get a ######### it simply means your column is not wide enough (if so, drag and drop the cell line so that the cell is wider, or double click on the cell line next to the column letter to auto-size the column width.)

3) Use cell references and formulas to populate the production cost report. For example, in cell E4 you should type in the following reference: = B3. In cell E6 you should type in the following formula: = E4+E5. NOTE: DO NOT TYPE IN ANY NUMBERS. ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX.

B G H Conversion EU Department Data Information about units: Units in Beginning WIP Started during month Units in Ending WIP 25,000 220,000 30,000 D F Production Cost Report: Blending Department - MAY 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 1 0.8 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 132500 78300 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 1153750 45000 $ $ $ 210,800.00 $ 1,816,350.00 $ 2,027,150.00 $ 132,500.00 $ 1,153,750.00 $ 1,286,250.00 $ 78,300.00 662,600.00 740,900.00 617600 Cost per equivalent unit $ 8.35 $ 5.25$ 3.10 NOTE: The blue box contains the data you'll need for completing the Production Cost Report (tan box). Certain figures in the blue box must be typed in. Other figures in the blue box can be cell referenced from another worksheet (= cell address). 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 ALL cells in the Production Cost Report (tan box) should be formulas or cell-referenced to figures in the blue box. DO NOT TYPE ANY FIGURES on the Production Cost Report or you will lose points on the assignment. Your goal is to create a Production Cost Report that can be used every month by just changing the data in the blue box. D E F G H K Conversion EU A B B 1 Production Cost Report: Blending Dept.- APRIL 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 4 Beginning work in process 5 Started in production 6 Total units to account for 7 NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. 25,000 Total DM Conversion 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 11 Total units accounted for 12 13 FLOW OF COSTS 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 23 Ending WIP 24 Total costs accounted for 25 $ 210,800 $ 132,500 $ 78,300 26 27 28 29 30 31 32 D E F H J Conversion EU NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. A B 1 Production Cost Report: Forming Dept.- MAY 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 Beginning work in process 5 Started in production 6 Total units to account for 7 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 11 Total units accounted for 12 13 FLOW OF COSTS Total 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 23 Ending WIP 24 Total costs accounted for 25 DM Conversion 1.50 $ 0.60 26 27 28 B C D E F G H Use a formula to find the total operating expenses for the month. $ 45,000 S 45,000 For Income Statement: Total Operating Expenses = $ 1,153,750 $ 1,153,750 $ 617,600 $ 617,600 $ 750,000 $ 750,000 S 45,000 $ 45,000 $ 617,600 $ 1. May General Journal Entries- Partial list 2 3 WIP-Blending 4 Wages 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) 10 11 WIP-Blending 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 21 (to record DM traced to the Forming Dept) 22 23 WIP-Forming 24 MOH-Forming 25 (to record MOH allocated to the Forming Dept) 26 27 Salary Expense 28 Wages Payable 29 (to record salaries and wages of selling, general and admin. staff) 30 31 Rent Expense 32 Rent Payable 33 (to record monthly rent) 34 35 Insurance Expense 36 Prepaid Insurance 37 (to record Insurance on selling, general, and admin. Building) 38 39 Accounts Receivable 40 Sales Revenue 41 (to record the sale of 180,000 units at a price of $20 per unit) 42 43 Utilities Expense 44 Utilities Payable 45 (to record Insurance on selling, general, and admin. Building) 46 47 Advertising expense 48 Accounts Payable 49 (to record advertising expenses) 617,600 $ 275,000 $ 275,000 S 25,000 $ 25,000 $ 30,000 $ 30,000 $ 3,600,000 $ 3,600,000 $ 10,000 $ 10,000 $ 120,000 $ 120,000 B G H Conversion EU Department Data Information about units: Units in Beginning WIP Started during month Units in Ending WIP 25,000 220,000 30,000 D F Production Cost Report: Blending Department - MAY 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 1 0.8 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 132500 78300 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 1153750 45000 $ $ $ 210,800.00 $ 1,816,350.00 $ 2,027,150.00 $ 132,500.00 $ 1,153,750.00 $ 1,286,250.00 $ 78,300.00 662,600.00 740,900.00 617600 Cost per equivalent unit $ 8.35 $ 5.25$ 3.10 NOTE: The blue box contains the data you'll need for completing the Production Cost Report (tan box). Certain figures in the blue box must be typed in. Other figures in the blue box can be cell referenced from another worksheet (= cell address). 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 ALL cells in the Production Cost Report (tan box) should be formulas or cell-referenced to figures in the blue box. DO NOT TYPE ANY FIGURES on the Production Cost Report or you will lose points on the assignment. Your goal is to create a Production Cost Report that can be used every month by just changing the data in the blue box. D E F G H K Conversion EU A B B 1 Production Cost Report: Blending Dept.- APRIL 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 4 Beginning work in process 5 Started in production 6 Total units to account for 7 NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. 25,000 Total DM Conversion 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 11 Total units accounted for 12 13 FLOW OF COSTS 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 23 Ending WIP 24 Total costs accounted for 25 $ 210,800 $ 132,500 $ 78,300 26 27 28 29 30 31 32 D E F H J Conversion EU NOTE: Do NOT complete this production cost report. Select information is provided for use elsewhere in the project. A B 1 Production Cost Report: Forming Dept.- MAY 2 FLOW OF UNITS Physical Units DM EU 3 Units to account for: 4 Beginning work in process 5 Started in production 6 Total units to account for 7 8 Units accounted for: 9 Completed and transferred out 10 Ending WIP 11 Total units accounted for 12 13 FLOW OF COSTS Total 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 23 Ending WIP 24 Total costs accounted for 25 DM Conversion 1.50 $ 0.60 26 27 28 B C D E F G H Use a formula to find the total operating expenses for the month. $ 45,000 S 45,000 For Income Statement: Total Operating Expenses = $ 1,153,750 $ 1,153,750 $ 617,600 $ 617,600 $ 750,000 $ 750,000 S 45,000 $ 45,000 $ 617,600 $ 1. May General Journal Entries- Partial list 2 3 WIP-Blending 4 Wages 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) 10 11 WIP-Blending 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 21 (to record DM traced to the Forming Dept) 22 23 WIP-Forming 24 MOH-Forming 25 (to record MOH allocated to the Forming Dept) 26 27 Salary Expense 28 Wages Payable 29 (to record salaries and wages of selling, general and admin. staff) 30 31 Rent Expense 32 Rent Payable 33 (to record monthly rent) 34 35 Insurance Expense 36 Prepaid Insurance 37 (to record Insurance on selling, general, and admin. Building) 38 39 Accounts Receivable 40 Sales Revenue 41 (to record the sale of 180,000 units at a price of $20 per unit) 42 43 Utilities Expense 44 Utilities Payable 45 (to record Insurance on selling, general, and admin. Building) 46 47 Advertising expense 48 Accounts Payable 49 (to record advertising expenses) 617,600 $ 275,000 $ 275,000 S 25,000 $ 25,000 $ 30,000 $ 30,000 $ 3,600,000 $ 3,600,000 $ 10,000 $ 10,000 $ 120,000 $ 120,000