Additional analysis DIRECTIONS Now that you have completed the production cost report the controller would like you to do a few more tasks. Use the space at the left to document your responses. 1) Journal entry needed WIP-Forming WIP-Blending 1405976 1405975 IMPORTANT NOTE: Every figure on this spread sheet (dollar amounts and units) should be cell referenced from elsewhere (not typed in) EXCEPT for the blue boxes (number of units sold, Sales price per unit, and WIP-Forming End units and cost). Line labels and account names will need to be typed in. To reference a cell in another worksheet, first select the cell where you want the answer and type =. Then, select the cell you want to get from a different sheet, and hit the Enter button. 2) Total cost per unit: Blending DM 1) How much cost, in total, needs to get transferred to the Forming Department? Complete the journal entry with the appropriate amount 3) 2) The controller wants to know the cost of making one unit of product in May, from START TO FINISH (including both the Blending and Forming Departments). Show your calculations by listing each of the costs per EU from all of the departments that make up the total cost leg., Blending DM, Blending cc, etc.). FORMAT the cost per unit in dollars and cents and HIGHLIGHT the total cost per unit (use the spilling paint bucket icon to fill in the cell with neon yellow color.) Units Sold Sales price/unit Henkel Industries Income Statement For the Month Ending May 31 Per unit Total 3) The controller would like you to prepare a draft of the company's income statement. In addition, she would like to see the sales price per unit, cost per unit, and gross profit per unit. Hint: See general ledger for the information about transactions that occurred during the month. Make sure you format everything in dollars and cents. 4) At the beginning of May, the company had no Finished Goods inventory and no WIP 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. The controller wants to know the following: a) How many units are in each Department's ending WIP Inventory? b) What is the cost balance for each Department's ending WIP Inventory? c) How many units are in ending Finished Goods Inventory? (Use side purple box to figure it out.) d) What is the total cost balance in ending Finished Goods Inventory? Ending Units 4) Inventory Analysis WIP Inventory- Blending WIP Inventory. Forrning Finished Goods Inventory Total Cost Reconciling UNITS in End FG Inv Bec FG - Made Sold -End FG 5A) Total cost per unit 5) Although the production manager said that the units in WIP at the end of May were 80% of the way through the process, the controller knows that figure is somewhat subjective. She has talked to the assistant production manager, who believes the units were only about 50% complete. She wants you to run your spreadsheet using 50% as the percent of completion to see what impact the estimate has on A) total cost per unit, and B) the company's Income statement for the month IMPORTANT: BEFORE doing the analysis in Step 5, copy your May production cost report worksheet to a new tab and rename it "What-if 50%?" To do this, right click on the May tab at the bottom of the screen and choose "move or copy 5B) rename it "What-if 50%?" To do this, right click on the May tab at the bottom of the screen and choose "move or copy" then check the box, "make a copy" Then right click on the new copled spreadsheet and choose "rename". Henkel Industries Income Statement For the Month Ending May 3 Per unit Total 5A) Calculate the total cost per unit, using 60% as the percentage of completion. 5B) Complete the company's income statement for the month, using 60% as the percentage of completion. 5C) Calculate the difference in operating income between the two possible percentages of completion (80% vs 60%) 5D) Briefly explain WHY the difference in income occurs. SE) In general, if managers want to inflate earnings in a particular period, what could they do? 5C) Difference in operating income: Lower or higher when 60% is used? 5D) Briefly explain the reason for the difference. SE) If managers want to inflate earings in a particular period, what can they do? Conversion EU Department Data Information about units: Units in Beginning WIP Started during month Units in Ending WIP 25,000 220,000 30,000 Production Cost Report: Blending Department - MAY FLOW OF UNITS Physical Units DM EU Units to account for: Beginning work in process 25,000 Started in production 220,000 Total units to account for 245,000 Percentage of completion: Direct materials Conversion 100% 800 Units accounted for Completed and transferred out Ending WIP Total units accounted for 215000 30,000 245,000 215,000 30,000 245.000 215000 24,000 239,000 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 Depit during the month DL traced to Dept during the month MOH allocated to depl during the month FLOW OF COSTS Costs to account for: Beginning work in process Costs added during manth Total costs to account for 1153750 45000 617600 210800 18162501 2027150 132500 1158750 1286250 78300 662600 7409001 Cost per equivalent unit 8.35 5 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 1795250 231900 2027150 1,128,750 157500 1,286,250 666,500 74400 740,900 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. May General Journal Entries. Partial list Use a formula to find the total operating expenses for the month $ 45,000 WIP-Blending Wages Payable (to record DL traced to the Blending Dept) $ 45,000 For Income Statement: Total Operating Expenses = $ 1,153,750 WIP-Blending Raw materials inventory (to record DM traced to the Blending Dept) $ 1,153,750 $ 617,600 WIP-Blending MOH (to record MOH allocated to the Blending Dept) $ 617,600 $ 750,000 WIP Forming Wages Payable (to record DL traced to the Forming Dept) LLLLLLLL $ 750,000 $ 45,000 WIP-Forming Raw materials inventory (to record DM traced to the Forming Dept) $ 45,000 $ $ 617,600 WIP-Forming MOH-Forming (to record MOH allocated to the Forming Dept) $ 617,600 $ 275,000 Salary Expense Wages Payable (to record solaries and wages of selling, general and admin. stoff $ 275,000 $ 25,000 Rent Expense Rent Payable (to record monthly rent) $ 25,000 $ 30,000 Insurance Expense Prepaid Insurance (to record Insurance on selling, general, and admin. Building) $ 30,000 $ 30,000 Insurance Expense Prepaid Insurance (to record Insurance on selling, general, and admin. Building) $ 30,000 $ 3,600,000 Accounts Receivable Sales Revenue (to record the sale of 180,000 units at a price of $20 per unit) $ 3,600,000 $ 10,000 Utilities Expense Utilities Payable (to record Insurance on selling, general, and admin. Building) $ 10,000 $ 120,000 Advertising expense Accounts Payable (to record advertising expenses) $ 120,000