MY QUESTION IS THIS: I am confused on what numbers in respect to 100% and 80% as well as the 60%. I am not sure which goes into beginning and which goes into ending. You don't have to complete the entire problem as I have already done the first half, I just want to be put on the right track and as well as if I did the first part correctly.

018 E F G H 1 K M N 0 p R. S T U 7 8 9 10 O D PROJECT OVERVIEW 2 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 11 departments, the controller wants you to focus your attention on the Blending 12 department until she is sure that your spreadsheet will provide accurate information. 13 The controller has asked that you prepare production cost reports under both the 14 Weighted Average Cost Method and the FIFO Method, so that the company can 15 determine how to best assign costs to units produced. 16 17 Before you begin to formulate the May spreadsheet, you'll need to get production 18 and cost data for the month of May. Some of the information can be found on last 19 month's (April's) manually-prepared production cost report, while other pieces of 30 information can be found in the May general ledger. Ending inventory from April 30 was 100% complete with respect to materials and 60% complete with respect to 21 labor and overhead. In addition, the production supervisor has already reported that 22 220,000 units were started during May and 35,000 units were still in production, 23 80% of the way completed at the end of the month. During your initial plant tour, 24 you found out that all of the direct materials were added at the very beginning of the 25 Blending process. 26 27 Once you finish the spreadsheet, the controller has more questions and tasks for you 28 (see the Additional Analysis worksheet after you have completed the May 29 Production Cost Report under both the Weighted Average and FIFO methods.). 30 31 32 33 34 35 36 37 38 39 STEP-BY-STEP INSTRUCTIONS 40 41 NOTE: Complete the Weighted Average Production Report first, then complete the FIFO Production report 42 43 1) Fill in the information about units and percentages of completion in the May blue box 44 FORMAT percentages as percentages (not decimals). Fill in the cost information in the 45 May blue box. (Hint: Gather from April's cost report and from the General Ledger). 46 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). 47 48 2) Use cell references and formulas to populate the production cost reports. For example, 49 in cell E6 you should type in the following reference: = B3. In cell E8 you should type in 50 the following formula: = SUM(E6E7). NOTE: DO NOT TYPE IN ANY NUMBERS. 51 ALL CELLS SHOULD CONTAIN FORMULAS OR REFERENCES TO CELLS IN THE 52 BLUE BOX 53 3) Make sure all of the cost information is in dollars (use the dollar format button). 54 55 4) Clean up the spreadsheet formatting as necessary (for example, get rid of unnecessary 56 decimal places: align numbers property, etc.) 5.) Once you finish the production cost report, go to the "Additional Analysis" worksheet for 58 additional requirements and directions. 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. 57 59 60 61 Directions May Blending Dept Cost Rpt WA May Blending Dept Cost Rpt FIFO Additional Analysis April Blending Dept Cost Report General Ledger Grading Rubric + R24 B D F G H K E Production Report (Weigted Average Method) UNIT INFORMATION 30.000 220,000 35,000 Units to account for: Units in beginning work in process inventory Units started transferred in during the period Total units to account for 30.000 220,000 250.000 100% 80% Units accounted for: Units completed & transferred out Units in ending work in process inventory Total units accounted for 215.000 35,000 250.000 Equivalent Units Materials Conversion 215.000 172.000 35,000 28,000 250.000 200.000 A 1 Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP 6 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 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 23 24 NOTE: 25 Percentages must be formatted as 26 percentages (not as decimals) 27 Dollar amounts must be formatted as dollars 28 (round so that there are no decimals 29 showing). 30 31 NOTE: ALL CELLS on the production B2 cost report MUST BE cell references or formulas. NO hard numbers!!! 33 $136,500 $76.700 COST INFORMATION $1,027.250 $55,000 $723,200 Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for Total $213,200 $1.805.450 $2.018.650 Materials $136,500 $1,027,250 $1.163.750 Conversion $76,700 $778.200 $854,900 Total costs to account for Divided by equivalent units Cost per equivalent unit $1.163.750.00 $250,000.00 $4.66 $854,900.00 $200,000.00 $4.27 $8.93 Costs accounted for: Costs completed & transferred out Costs remaining in ending work in process inventory Total costs accounted for $1,736,039 $282.611 $2,018.650 $1,000,825 $162.925 $1,163.750 $735,214 $119.686 $854.900 34 35 36 37 38 File Home Insert Page Layout Formulas Data Review View Tell me what you want to do... D42 fi B C D F G H K E Production Report (FIFO Method) UNIT INFORMATION 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 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 1 Department Data 2 Information about units: 3 Units in Beginning WIP 4 Started during month 5 Units in Ending WIP 6 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 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 23 24 NOTE: 25 Percentages must be formatted as percentages (not as decimals). 26 27 Dollar amounts must be formatted as dollars 28 (round so that there are no decimals 29 showing 30 31 NOTE: ALL CELLS on the production B2 cost report MUST BE cell references or formulas. NO hard numbers!!! 33 34 Total Materials Conversion Costs to account for: Costs in beginning work in process inventory Costs incurred during the period Total costs to account for Costs incurred during the period Divided by equivalent units Cost per equivalent unit 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 35 36 37 38 39 40 41 42