Cost Analysis of Job-Order Sheets Completing this activity will help you learn to: 1. build calculations to find cumulative costs and unit costs for a manufacturing job. 2. understand and utilize a manufacturing overhead rate. 3. understand what variable the direct labor, direct materials and manufacturing overhead costs depend on (hours, units, etc.). der sheet. Case cenario: You are w t. You do not have the specific identification of the good sold. You are given the building blocks of cost (labor, materials, etc.) for Job #26, which your plant completed. ted. Your company accumulates cost per job on a Job-Order Sheet. Required: Build a spreadsheet to calculate a manufacturing job's total cost and unit product cost. This lab uses "Job #26" information. HINT: STEP-BY-STEP WALKTHROUGH To help you get started, the file already has a job order costs sheet populated with information. To complete the task, complete the following steps: 1. Naming Cells: Individual cells, cell e. For example, the Total Material Subtotal (cell E15) can be named Subtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." 2. Manufacturin a. Calculate the total hours for Job # ours should NOT be h ded (or typed in), but a formula shoul date variations in total hours. hours b. Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26. Is the manufacturing overhead amount you found for the JOB or for EACH UNIT? The manufacturing overhead mount is found for Job #26 - v . 3. Cost Summary a. Calculate total direct materials for Job #26 in cell G18. Hint: Is the direct material information given the total for the job or for each unit? b. Calculate direct labor for Job #26 in cell G19. Hint: Is the direct labor information given the total for the job or for each unit? c. Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code. d. Calculate the total costs for Job #26 in cell G21. e. Calculate the unit product cost for Job #26 in cell G22. 4. Reflect on your calculation process to further understand cost concepts. a. How does manufacturing overhead differ from direct materials and direct labor? Manufacturing over n indirec * v cost, not direct - v costs, associated with manu indirect - ate. This differs lose costs are v traceable outright to producing a unit. b. What variable(s) does direct labor depend on? Direct labor depends o What variable(s) do direct materials depend on? Direct materials depends " What variable(s) does manufacturing overhead depend on? Manufacturing overhead depends on a variety of variables incl v c. What information can you gleam from the job-cost spreadsheet? What is the most expensive part of the job? least expensive? Direct labo * v is the m uring this job. It accounts for about |% of the job's cost. The least ex manufacturing over - v . It is about % of the job's cost. 5. Explain how job cost sheets are used to evaluate and control costs. The input in the box below will not be graded, but may be reviewed and considered by your instructor. ets provide a detailed breakdown of the costs associated with a particular job, including direct materials, direct labor, and manufacturingJob-Order Sheet Customer ABC, Inc. Date Initiated 4/1/20 Job Number 26 Date Completed 4/30/20 Department 1 Units Completed 20 Direct Materials per Unit Direct Labor per Unit Job Manufacturing Overhead (MOH) Item # Utilized per Unit Amount Subtotal Employee # Hours Amount Subtotal Hours DLH Rate Amount 52B 1 $40.00 $40.00 123456 $45.00 $135.00 $5.00 W W 52D 10 $0.22 $2.20 234567 $50.00 $150.00 60A 1 $85.00 $85.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Total Materials $127.20 Total Hours 6 $285.00 Total MOH $0.00 Cost Summary for Job #26 Direct Materials Direct Labor Manufacturing Overhead Total Cost Unit Product Cost