Please use the given instructions to complete the tables

this is all the data

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 produced. 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) 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 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, you found out that all of the direct materials were added at the very beginning of the Blending process. EXCEL HINTS 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.). - "Copy and paste" as much as possible rather than typing in numbers already on one of the spreadsheets (for example, some of the cost data in the general ledger). -An easy way to make a cell reference is type an = sign and then highlight the cell you want to put in (rather than typing it). STEP-BY-STEP INSTRUCTIONS NOTE: Complete the Weighted Average Production Report first, then complete the FIFO Production report To multiply, use * To divide, use To add, use + To subtract, use - 1) Fill in the information about units and percentages of completion in the May blue box. FORMAT percentages as percentages (not decimals). Fill in the cost information in the May blue box. (Hint: Gather from April's cost report and from the General Ledger). FORMAT in DOLLARS (no cents). 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). -Make use of the AutoSum button for adding cells together. -Use "Paste Special- Values" if what you are pasting is the result of a formula and you only want the number copied rather than the formula. 2) Use cell references and formulas to populate the production cost reports. For example, in cell E6 you should type in the following reference: = B3. In cell E8 you should type in the following formula: =SUM(E6:E7). NOTE: DO NOT TYPE IN ANY NUMBERS. ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX 3) Make sure all of the cost information is in dollars (use the dollar format button). 4) Clean up the spreadsheet formatting as necessary (for example, get rid of unnecessary decimal places; align numbers properly, etc.) 5) Once you finish the production cost report, go to the "Additional Analysis' worksheet for additional requirements and directions. Directions May Blending Dept Cost Rpt WA M ay Blending Dept Cost Rpt FIFO I... G H E F Production Report (Weigted Average Method) A Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP UNIT INFORMATION 6 Units to account for: Units in beginning work in process inventory Units started transferred in during the period Total units to account for Equivalent Units Materials Conversion 7 Percentage of completion: 8 Beginning inventory 9 Direct materials 10 Conversion 11 Ending inventory 12 Direct materials 13 Conversion 1 14 15 Costs in Beginning WIP 16 DM in beginning WIP 17 Conversion costs in beginning WIP 18 19 Cost information for the current month: 20 DM traced to Dept during the month 21 DL traced to Dept during the month 22 MOH allocated to dept during the month Units accounted for: Units completed & transferred out Units in ending work in process inventory Total units accounted for COST INFORMATION Total Materials Conversion Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 Total costs to account for Divided by equivalent units Cost per equivalent unit NOTE: 25 Percentages must be formatted as 26 percentages (not as decimals). Dollar amounts must be formatted as dollars 28 (round so that there are no decimals showing) 30 31 NOTE: ALL CELLS on the production 32 cost report MUST BE cell references or 33 formulas. NO hard numbers!!! 29 Costs accounted for: Costs completed & transferred out Costs remaining in ending work in process inventory Total costs accounted for G H | F Production Report (FIFO Method) UNIT INFORMATION 2 3 4 5 Department Data Information about units Units in Beginning WIP Started during month Units in Ending WIP Units to account for: Units in beginning work in process inventory Units started/transferred in during the period Total units to account for Equivalent Units Materials Conversion 7 Percentage of completion: 8 Beginning inventory 9 Direct materials 10 Conversion 11 Ending inventory 12 Direct materials 13 Conversion 14 15 Costs in Beginning WIP 16 DM in beginning WIP 17 Conversion costs in beginning WIP Units accounted for: Units completed & transferred out: From beginning work in process inventory Started & completed Units in ending work in process inventory Total units accounted for COST INFORMATION Materials Conversion 19 Cost information for the current month: 20 DM traced to Dept during the month 21 DL traced to Dept during the month 22 MOH allocated to dept during the month Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 NOTE: Costs incurred during the period Divided by equivalent units Cost per equivalent unit Percentages must be formatted as percentages (not as decimals). 988888988XNXXNNNN Dollar amounts must be formatted as dollars (round so that there are no decimals showing) Costs accounted for: Costs assigned to units from beginning work in process: Costs from prior period Costs to complete BWIP units in current period Costs assigned to units started and completed this period Total cost completed and transferred out Costs assigned to units remaining in ending work in process inventory Total costs accounted for 31 NOTE: ALL CELLS on the production 32 cost report MUST BE cell references or 33 formulas. NO hard numbers!!! L C D D | E F B 1 Additional analysis | G | H | I | J | K | L | M | N | O | P | Q R S DIRECTIONS Now that you have completed the production cost reports, the controller would like you to do a few more tasks. Use the space at the left to document your responses. Complete these tasks using the 29sumption that the Controller has decided to utilize the Weighted Average Method to assign costs to units. 31) Journal entry needed: 4 Journal Entry 5 Debit Credit 1) Record the joumal entry that needs to be made to reflect the units completed and transferred out of the Blending Dept. during the month 8 10 12 13 14 15 16 17 18 2) Total cost per unit (Weighted Average): Blending Direct Materials Blending Conversion Costs Forming Direct Materials Forming Conversion Costs Total cost per unit 3) Henkel Industries Income Statement For the Month Ending May 31 Per unit 2) In addition to the transferred in costs, the Forming Dept. showed the following costs per equivalent unit on their May production cost report: Direct Material Cost/EU = $1.50 Conversion Cost EU= 50.60 The controller wants to know the cost of making one unit of product in May, from START to FINISH. Show your calculations by listing each of the costs per EU from all of the departments that make up the total cost FORMAT the cost per unit in dollars and cents and HIGHLIGHT the total cost per unit. 3) During the month Henkel sold 180,000 of the units it produced during May at a sales price of $30 per unit. Operating expenses for the month totaled $525.000. The controller would like you to prepare a draft of the company's income statement. She would like you to include information about the gross profit per unit, as well as the total for the month, ending with operating income. Make sure you format everything in dollars and cents. Total 4) At the beginning of May, the company had no beginning Finished Goods (FG) inventory and no beginning Work-in-Process (WIP) inventory in the Forming Department. All units transferred out of the Blending Dept. in May also made it completely through the Forming Dept. in May. The controller would like you to prepare an analysis of the company's ending WIP and FG inventory at May 31. a) How many units are in each Department's Ending WIP? b) What is the cost balance for each Department's Ending WIP? How many units are in Finished Goods Inventory? d) What is the total cost balance in Finished Goods Inventory? 5) Although the controller determined that the Company should use the weighted Average Method to assign costs to units produced, she wants you to re-run your analysis to see what impact the using the FIFO method would have on A) total cost per unit, and B) the company's income statement for the month (Note that Foming department unit costs would not change since there was no beginning inventory - only inventory transferred in from the Blending Department). If the FIFO method was used, would operating income be higher or lower, and by how much? How could managers inflate eamings? 26 4) 27 Inventory Analysis 28 WIP-Blending 29 WIP-Forming 30 Finished Goods Units 33 35 36 32 5A) Total cost per unit (FIFO): Blending Direct Materials 34 Blending Conversion Costs Forming Direct Materials Forming Conversion Costs 37 Total cost per unit 38 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total a Difference in operating Increase or 49 income Decrease when 50 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? D E F G H C This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU 4 Units to account for: 5 Beginning work in process 6 Started in production 7 Total units to account for Conversion EU 9 Units accounted for: 10 Completed and transferred out 11 Ending WIP 12 Total units accounted for 30,000 Lotal DM Conversion FLOW OF COSTS 15 Costs to account for: 16 Beginning work in process 17 Costs added during month 18 Total costs to account for 20 Cost per equivalent unit 22 Costs accounted for: 23 Completed and transferred out 24 Ending WIP 25 Total costs accounted for 213.200S 136,500S 76,700 This is a data tab only - no work required on this page! 2 May Journal Entries - Partial list 3 4 WIP-Blending $1,027.250 5 Raw materials inventory $1,027.250 6 (to record DM traced to the Blending Dept) $ 55,000 8 WIP-Blending 9 Wages Payable 10 (to record DL traced to the Blending Dept) S 55.000 11 $ 623.800 12 WIP- Blending 13 MOH 14 (to record MOH allocated to the Blending Dept) S 623,800 15 $ 55,000 16 WIP-Forming 17 Raw materials inventory 18 (to record DM traced to the Forming Dept) $ 55,000 19 $ 850.000 S 850.000 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) $ 723,200 S 723,200 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 produced. 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) 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 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, you found out that all of the direct materials were added at the very beginning of the Blending process. EXCEL HINTS 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.). - "Copy and paste" as much as possible rather than typing in numbers already on one of the spreadsheets (for example, some of the cost data in the general ledger). -An easy way to make a cell reference is type an = sign and then highlight the cell you want to put in (rather than typing it). STEP-BY-STEP INSTRUCTIONS NOTE: Complete the Weighted Average Production Report first, then complete the FIFO Production report To multiply, use * To divide, use To add, use + To subtract, use - 1) Fill in the information about units and percentages of completion in the May blue box. FORMAT percentages as percentages (not decimals). Fill in the cost information in the May blue box. (Hint: Gather from April's cost report and from the General Ledger). FORMAT in DOLLARS (no cents). 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). -Make use of the AutoSum button for adding cells together. -Use "Paste Special- Values" if what you are pasting is the result of a formula and you only want the number copied rather than the formula. 2) Use cell references and formulas to populate the production cost reports. For example, in cell E6 you should type in the following reference: = B3. In cell E8 you should type in the following formula: =SUM(E6:E7). NOTE: DO NOT TYPE IN ANY NUMBERS. ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX 3) Make sure all of the cost information is in dollars (use the dollar format button). 4) Clean up the spreadsheet formatting as necessary (for example, get rid of unnecessary decimal places; align numbers properly, etc.) 5) Once you finish the production cost report, go to the "Additional Analysis' worksheet for additional requirements and directions. Directions May Blending Dept Cost Rpt WA M ay Blending Dept Cost Rpt FIFO I... G H E F Production Report (Weigted Average Method) A Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP UNIT INFORMATION 6 Units to account for: Units in beginning work in process inventory Units started transferred in during the period Total units to account for Equivalent Units Materials Conversion 7 Percentage of completion: 8 Beginning inventory 9 Direct materials 10 Conversion 11 Ending inventory 12 Direct materials 13 Conversion 1 14 15 Costs in Beginning WIP 16 DM in beginning WIP 17 Conversion costs in beginning WIP 18 19 Cost information for the current month: 20 DM traced to Dept during the month 21 DL traced to Dept during the month 22 MOH allocated to dept during the month Units accounted for: Units completed & transferred out Units in ending work in process inventory Total units accounted for COST INFORMATION Total Materials Conversion Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 Total costs to account for Divided by equivalent units Cost per equivalent unit NOTE: 25 Percentages must be formatted as 26 percentages (not as decimals). Dollar amounts must be formatted as dollars 28 (round so that there are no decimals showing) 30 31 NOTE: ALL CELLS on the production 32 cost report MUST BE cell references or 33 formulas. NO hard numbers!!! 29 Costs accounted for: Costs completed & transferred out Costs remaining in ending work in process inventory Total costs accounted for G H | F Production Report (FIFO Method) UNIT INFORMATION 2 3 4 5 Department Data Information about units Units in Beginning WIP Started during month Units in Ending WIP Units to account for: Units in beginning work in process inventory Units started/transferred in during the period Total units to account for Equivalent Units Materials Conversion 7 Percentage of completion: 8 Beginning inventory 9 Direct materials 10 Conversion 11 Ending inventory 12 Direct materials 13 Conversion 14 15 Costs in Beginning WIP 16 DM in beginning WIP 17 Conversion costs in beginning WIP Units accounted for: Units completed & transferred out: From beginning work in process inventory Started & completed Units in ending work in process inventory Total units accounted for COST INFORMATION Materials Conversion 19 Cost information for the current month: 20 DM traced to Dept during the month 21 DL traced to Dept during the month 22 MOH allocated to dept during the month Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for 24 NOTE: Costs incurred during the period Divided by equivalent units Cost per equivalent unit Percentages must be formatted as percentages (not as decimals). 988888988XNXXNNNN Dollar amounts must be formatted as dollars (round so that there are no decimals showing) Costs accounted for: Costs assigned to units from beginning work in process: Costs from prior period Costs to complete BWIP units in current period Costs assigned to units started and completed this period Total cost completed and transferred out Costs assigned to units remaining in ending work in process inventory Total costs accounted for 31 NOTE: ALL CELLS on the production 32 cost report MUST BE cell references or 33 formulas. NO hard numbers!!! L C D D | E F B 1 Additional analysis | G | H | I | J | K | L | M | N | O | P | Q R S DIRECTIONS Now that you have completed the production cost reports, the controller would like you to do a few more tasks. Use the space at the left to document your responses. Complete these tasks using the 29sumption that the Controller has decided to utilize the Weighted Average Method to assign costs to units. 31) Journal entry needed: 4 Journal Entry 5 Debit Credit 1) Record the joumal entry that needs to be made to reflect the units completed and transferred out of the Blending Dept. during the month 8 10 12 13 14 15 16 17 18 2) Total cost per unit (Weighted Average): Blending Direct Materials Blending Conversion Costs Forming Direct Materials Forming Conversion Costs Total cost per unit 3) Henkel Industries Income Statement For the Month Ending May 31 Per unit 2) In addition to the transferred in costs, the Forming Dept. showed the following costs per equivalent unit on their May production cost report: Direct Material Cost/EU = $1.50 Conversion Cost EU= 50.60 The controller wants to know the cost of making one unit of product in May, from START to FINISH. Show your calculations by listing each of the costs per EU from all of the departments that make up the total cost FORMAT the cost per unit in dollars and cents and HIGHLIGHT the total cost per unit. 3) During the month Henkel sold 180,000 of the units it produced during May at a sales price of $30 per unit. Operating expenses for the month totaled $525.000. The controller would like you to prepare a draft of the company's income statement. She would like you to include information about the gross profit per unit, as well as the total for the month, ending with operating income. Make sure you format everything in dollars and cents. Total 4) At the beginning of May, the company had no beginning Finished Goods (FG) inventory and no beginning Work-in-Process (WIP) inventory in the Forming Department. All units transferred out of the Blending Dept. in May also made it completely through the Forming Dept. in May. The controller would like you to prepare an analysis of the company's ending WIP and FG inventory at May 31. a) How many units are in each Department's Ending WIP? b) What is the cost balance for each Department's Ending WIP? How many units are in Finished Goods Inventory? d) What is the total cost balance in Finished Goods Inventory? 5) Although the controller determined that the Company should use the weighted Average Method to assign costs to units produced, she wants you to re-run your analysis to see what impact the using the FIFO method would have on A) total cost per unit, and B) the company's income statement for the month (Note that Foming department unit costs would not change since there was no beginning inventory - only inventory transferred in from the Blending Department). If the FIFO method was used, would operating income be higher or lower, and by how much? How could managers inflate eamings? 26 4) 27 Inventory Analysis 28 WIP-Blending 29 WIP-Forming 30 Finished Goods Units 33 35 36 32 5A) Total cost per unit (FIFO): Blending Direct Materials 34 Blending Conversion Costs Forming Direct Materials Forming Conversion Costs 37 Total cost per unit 38 5B) Henkel Industries Income Statement For the Month Ending May 31 Per unit Total a Difference in operating Increase or 49 income Decrease when 50 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? D E F G H C This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU 4 Units to account for: 5 Beginning work in process 6 Started in production 7 Total units to account for Conversion EU 9 Units accounted for: 10 Completed and transferred out 11 Ending WIP 12 Total units accounted for 30,000 Lotal DM Conversion FLOW OF COSTS 15 Costs to account for: 16 Beginning work in process 17 Costs added during month 18 Total costs to account for 20 Cost per equivalent unit 22 Costs accounted for: 23 Completed and transferred out 24 Ending WIP 25 Total costs accounted for 213.200S 136,500S 76,700 This is a data tab only - no work required on this page! 2 May Journal Entries - Partial list 3 4 WIP-Blending $1,027.250 5 Raw materials inventory $1,027.250 6 (to record DM traced to the Blending Dept) $ 55,000 8 WIP-Blending 9 Wages Payable 10 (to record DL traced to the Blending Dept) S 55.000 11 $ 623.800 12 WIP- Blending 13 MOH 14 (to record MOH allocated to the Blending Dept) S 623,800 15 $ 55,000 16 WIP-Forming 17 Raw materials inventory 18 (to record DM traced to the Forming Dept) $ 55,000 19 $ 850.000 S 850.000 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) $ 723,200 S 723,200