I do not have April Data

PROJECT OVERVIEW 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. 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.). EXCEL HINTS - "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). To multiply, use To divide, use To add, use + To subtract, use - -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. STEP-BY-STEP INSTRUCTIONS NOTE: Complete the Weighted Average Production Report first, then complete the FIFO Production report 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). 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. Production Report (Weighed Arere Method) Duartenen Date Information about mit in ein WIP Shuningan is in Edinn WIP UNIT INFORMATION us to rent for Uning work in process in Listed for induring the pri Total witte account for Percentare a repletler Reglanleg Inventory Dictatorials Cension Sadiyyatory Duret materiale Conversion Equivalent Units Material Care Lits act Units completed transferred out Unitsin endine work is csiny Total units accounted for Costin legno WIP DMI mine WIP Comenon is in beginning WIR COST INFORMATION Total Cost information for the curry month DM traced te during the tooth DI traced to Derdin the math MOHL allocated to dept during the mouth Materials Conversion Cost to cut for Costs in tinning work in processer Couts incurred during the period Total cost to count for NOTLI Pembe total de Doli me fond adults de har demas Total costs to account for Divided by equivalentni Cost per qualcatusit NOTE ALL CELLS on the production cost report MUST Dolce formue NO hann Costs accounted for Cost completed sorodou Coming in ending work is very Total costs accounted for Department Data Professor Med UNIT INFORMATION Chartered in the period Total coste Percentage completion legioning invest Die CON LA DINN key Total de C Castlege WIP DM WIP Covenance COSTINFORMATION Tel Material Car Confort for the current DMDhe Dessed to the MOI Meddering the mood Cousing work Cethered Total cost for NOTT Page Bode Good that the Debyen Costellen Cos cuated for Colore period Costs to comum period Destined to add moted this period Teca complesferred | Totalcohol for NOTE: ALL CELL the produce cost mort UST eller fames NO and Additional anabis DIRECTIONS Mile 12 Turtha ma kai 3 Journal entry seeded Jornal Entry Debi Credit 2) Total cost per un Weited Average Binding Direct Mat Blending Convenios Corts Format Duct Mana Forming Conversion Costa Totalco per 1) Remail Elias Attithean -Famns beyes Dund Mail C550 Cows CE-3040 The worst Mas, STALT TIH Solutions by rachu bro phone. FORMAT per dolls GELIGHTS 3) Talal INDOf 53237000 Teden tweet Seed you to the fact about the power to them, the operating com Manten Rekel Industries Income Statement For the Math Fadia May Pernik Total 4 At the beginning Marghering W.Prore (WTP) investory is thering Department All of the Big Data so se completo Farming Dell Me The words of WIPFO Mer wa DP What is the color WIP How Goode list Swill animaran Alle deed We Ave Mathiacordul FIFOR Aalaal pa , - mil - Dura, , Finallycheck the el. The wyobb dropbox Cits Cost Inveer Analysis Venn WTPF Fished Goods SA) Total cost per una (FIFO) Blending Direct Materials Blending Conversion Costa Foung Duet Mules Forming Conversion Costs Total cost per 58) Hal Industries Income Statement For the Month Fading May 31 Per in Total Dance operating income Increase or Decrease Comptes adference when FIFO ? Belly esperes for the nece Aheap wat inflate a parlar period, why do Ceala matapos mapolate wamp by percentage of completion 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 assumption that the Controller has decided to utilize the Weighted Average Method to assign costs to units 1) Record the journal entry that needs to be made to reflect the units completed and transferred out of the Blending Dept. during the month 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 SI SO Conversion Cost EUM $0.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 about 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 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 4) At the beginning of May, the company had no beginning Finished Goods (FG) inventory and so 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 Forming 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 earnings? Finally, check the Grading Rubric worksheet to make sure you have completed all requirements. Then save your file and submit it to the dropbox This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU Conversion EU Units to account for: Beginning work in process Started in production Total units to account for Units accounted for: Completed and transferred out Ending WIP 30,000 Total units accounted for FLOW OF COSTS Total DM Conversion Costs to account for: Beginning work in process Costs added during month Total costs to account for Cost per equivalent unit Costs accounted for: Completed and transferred out Ending WIP $ 213,200 S 136,500 $ 76.700 Total costs accounted for $ A This is a data tab only - no work required on this page! May Journal Entries . Partial list WIP-Blending $ 1,027.250 Raw materials inventory S 1,027.250 (to record DM traced to the Blending Dept) WIP-Blending 55,000 Wages Payable IS 55,000 (to record DL traced to the Blending Dept WIP-Blending 623,800 MOH $ 623,800 (to record MOH allocated to the Blending Dept) WIP-Forming $ 55,000 Raw materials inventory $ 55,000 (to record DM traced to the Forming Dept) WIP- Forming $ 850,000 $ Wages Payable 850,000 (to record DL traced to the Forming Dept) WIP- Forming $ 723,200 MOH-Forming 723,200 (to record MOH allocated to the Forming Dept) A WA - Points Possible FIFO Points Possible Total Points Points Deducted Points Earned 2.00 2.00 4.00 4.00 8.00 8,00 16,00 16.00 2.00 2.00 4.00 4.00 Requirement May Blending Dept. Correct cost and uni information May Blending Dept Cost Report correct figures using ONLY cell references and formulas Spreadsheet"cleaned up" (dollars, decimals aligned or deleted, ete. ADDITIONAL ANALYSIS: Req 1: Journal entry Req 2: Cost per unit Req 3: Income Statement Reg 4: Inventory Analysis Re SA: Cost per unit Reg SB: Income Statement Req 5C: Difference Req 5D Explanation 2.00 2.00 2.00 2.00 2.00 2.00 2.00 200 20.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 40.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 40.00 20.00 PROJECT OVERVIEW 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. 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.). EXCEL HINTS - "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). To multiply, use To divide, use To add, use + To subtract, use - -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. STEP-BY-STEP INSTRUCTIONS NOTE: Complete the Weighted Average Production Report first, then complete the FIFO Production report 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). 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. Production Report (Weighed Arere Method) Duartenen Date Information about mit in ein WIP Shuningan is in Edinn WIP UNIT INFORMATION us to rent for Uning work in process in Listed for induring the pri Total witte account for Percentare a repletler Reglanleg Inventory Dictatorials Cension Sadiyyatory Duret materiale Conversion Equivalent Units Material Care Lits act Units completed transferred out Unitsin endine work is csiny Total units accounted for Costin legno WIP DMI mine WIP Comenon is in beginning WIR COST INFORMATION Total Cost information for the curry month DM traced te during the tooth DI traced to Derdin the math MOHL allocated to dept during the mouth Materials Conversion Cost to cut for Costs in tinning work in processer Couts incurred during the period Total cost to count for NOTLI Pembe total de Doli me fond adults de har demas Total costs to account for Divided by equivalentni Cost per qualcatusit NOTE ALL CELLS on the production cost report MUST Dolce formue NO hann Costs accounted for Cost completed sorodou Coming in ending work is very Total costs accounted for Department Data Professor Med UNIT INFORMATION Chartered in the period Total coste Percentage completion legioning invest Die CON LA DINN key Total de C Castlege WIP DM WIP Covenance COSTINFORMATION Tel Material Car Confort for the current DMDhe Dessed to the MOI Meddering the mood Cousing work Cethered Total cost for NOTT Page Bode Good that the Debyen Costellen Cos cuated for Colore period Costs to comum period Destined to add moted this period Teca complesferred | Totalcohol for NOTE: ALL CELL the produce cost mort UST eller fames NO and Additional anabis DIRECTIONS Mile 12 Turtha ma kai 3 Journal entry seeded Jornal Entry Debi Credit 2) Total cost per un Weited Average Binding Direct Mat Blending Convenios Corts Format Duct Mana Forming Conversion Costa Totalco per 1) Remail Elias Attithean -Famns beyes Dund Mail C550 Cows CE-3040 The worst Mas, STALT TIH Solutions by rachu bro phone. FORMAT per dolls GELIGHTS 3) Talal INDOf 53237000 Teden tweet Seed you to the fact about the power to them, the operating com Manten Rekel Industries Income Statement For the Math Fadia May Pernik Total 4 At the beginning Marghering W.Prore (WTP) investory is thering Department All of the Big Data so se completo Farming Dell Me The words of WIPFO Mer wa DP What is the color WIP How Goode list Swill animaran Alle deed We Ave Mathiacordul FIFOR Aalaal pa , - mil - Dura, , Finallycheck the el. The wyobb dropbox Cits Cost Inveer Analysis Venn WTPF Fished Goods SA) Total cost per una (FIFO) Blending Direct Materials Blending Conversion Costa Foung Duet Mules Forming Conversion Costs Total cost per 58) Hal Industries Income Statement For the Month Fading May 31 Per in Total Dance operating income Increase or Decrease Comptes adference when FIFO ? Belly esperes for the nece Aheap wat inflate a parlar period, why do Ceala matapos mapolate wamp by percentage of completion 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 assumption that the Controller has decided to utilize the Weighted Average Method to assign costs to units 1) Record the journal entry that needs to be made to reflect the units completed and transferred out of the Blending Dept. during the month 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 SI SO Conversion Cost EUM $0.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 about 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 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 4) At the beginning of May, the company had no beginning Finished Goods (FG) inventory and so 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 Forming 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 earnings? Finally, check the Grading Rubric worksheet to make sure you have completed all requirements. Then save your file and submit it to the dropbox This is a data tab only - no work required on this page! Production Cost Report: Dept. FLOW OF UNITS Physical Units DM EU Conversion EU Units to account for: Beginning work in process Started in production Total units to account for Units accounted for: Completed and transferred out Ending WIP 30,000 Total units accounted for FLOW OF COSTS Total DM Conversion Costs to account for: Beginning work in process Costs added during month Total costs to account for Cost per equivalent unit Costs accounted for: Completed and transferred out Ending WIP $ 213,200 S 136,500 $ 76.700 Total costs accounted for $ A This is a data tab only - no work required on this page! May Journal Entries . Partial list WIP-Blending $ 1,027.250 Raw materials inventory S 1,027.250 (to record DM traced to the Blending Dept) WIP-Blending 55,000 Wages Payable IS 55,000 (to record DL traced to the Blending Dept WIP-Blending 623,800 MOH $ 623,800 (to record MOH allocated to the Blending Dept) WIP-Forming $ 55,000 Raw materials inventory $ 55,000 (to record DM traced to the Forming Dept) WIP- Forming $ 850,000 $ Wages Payable 850,000 (to record DL traced to the Forming Dept) WIP- Forming $ 723,200 MOH-Forming 723,200 (to record MOH allocated to the Forming Dept) A WA - Points Possible FIFO Points Possible Total Points Points Deducted Points Earned 2.00 2.00 4.00 4.00 8.00 8,00 16,00 16.00 2.00 2.00 4.00 4.00 Requirement May Blending Dept. Correct cost and uni information May Blending Dept Cost Report correct figures using ONLY cell references and formulas Spreadsheet"cleaned up" (dollars, decimals aligned or deleted, ete. ADDITIONAL ANALYSIS: Req 1: Journal entry Req 2: Cost per unit Req 3: Income Statement Reg 4: Inventory Analysis Re SA: Cost per unit Reg SB: Income Statement Req 5C: Difference Req 5D Explanation 2.00 2.00 2.00 2.00 2.00 2.00 2.00 200 20.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 40.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 40.00 20.00