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 unite, Etc., when the monthly information is dropped into Data area of the worksheet ( The data area shown as a blue box). The controller has provided you with a basic template, but needs you to finish the spreadsheet by adding formulas and cell references as needed. the only place she ever wants to type in numbers is in yellow cells within the purple Department Data table. although there are two departments, the controller want you to focus your attention on the blending department until she is sure that your spreadsheet will provide accurate information.

Before you begin to formulate the May spreadsheet, you will need to get production and cost dadt 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 inofrmation can be found in the MAy general journal. 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.

31 STEP-BY-STEP INSTRUCTIONS 32 1) Fill in the information about units and percentages of completion in the May blue box. FORMAT 34 percentages as percentages (not decimals). 35 36 2) Fill in the cost information in the May blue box. (Hint: Gather from April's cost report and from 371 the General Ledger). FORMAT in DOLLARS (no cents). If you get a #########it simply means your 38 column is not wide enough (if so, drag and drop the cell line so that the cell is wider) 39 40 3) Use cell references 41 E4 you should type in the following reference: B3. In cell E6 you should type in 42 formula: = E4+ES NOTE: DO NOT TYPE IN ANY NUMBERS, ALL CELLS SHOULD CONTAIN 43 FORMULAS OR REFERENCES TO CELLS IN THE BLUE BOX. and formulas to populate the production cost report. For example, in cell the following 4) Make sure all of the cost information is in dollars (use the dollar format button). 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.) 47 48 49 6) Once you finish the production cost report, go to the "Additional Analysis" worksheet for 50 additional requirements and directions. 51 52