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. 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). STEP-BY-STEP INSTRUCTIONS 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. 4) Make sure all of the cost information is in dollars and cents (use the $ format icon). 5) Clean up the spreadsheet formatting as necessary (for example, if anything is bold, change it back to regular font; get rid of unnecessary decimal places; align numbers properly, etc.) 6) Once you finish the production cost report, go to the "Additional Analysis" worksheet for additional requirements and directions. (Note: You may want to have your figures checked by myself or the TA before you move on to the additional analysis. EXCEL HINTS -An easy way to make a cell reference is to type an = sign in the destination cell, and then use your cursor to select the cell you want (rather than typing the cell reference) and then press enter. The same process works for referencing a cell that is in a different worksheet than the destination cell. To multiply, use * Example: =B2*E3 To divide, use / Example: = G20/A2 To add, use + Example: =A3+A4 To subtract, use - Example: = B10-A8 -Make use of the AutoSum button (on the top ribbon) for adding cells together. YOU SHOULD USE CELL REFERENCES, rather than typing in numbers, whenever possible. Points will be deducted if you type in numbers where cell references could have been used.