Cost Analysis of Job-Order Sheets
Chapter 19 Homework 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.). 4. reflect and utilize the helpful information you created in the job-order sheet. Case scenario: You are working at a manufacturing plant. 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. Your company accumulates cost per job on a Job-Order Sheet. Required: Download spreadsheet JobOrderCaseData-901f95.xlsx 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 ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). a. Calculate the total hours for Job #26 in cell 19. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. 90 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 amount is found for Job #26 - V. 3. Cost Summary 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 overhead is an indirect cost, not direct costs, associated with manufacturing. It requires analyzation of total indirect costs, a rational method of allocating the cost, and a rate. This differs from direct material and labor because those costs are v traceable outright to producing a unit. b. What variable(s) does direct labor depend on? Direct labor depends on units. What variable(s) do direct materials depend on? Direct materials depends on units. What variable(s) does manufacturing overhead depend on? Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc. C. What information can you gleam from the job-cost spreadsheet? What is the most expensive part of the job? least expensive? Direct labor v is the most expensive part of manufacturing this job. It accounts for % of the job's cost. about The least expensive part of this manufacturing job is manufacturing overhead V. It is about % of the job's cost. A1 Xv fx Job-Order Sheet B D E F G H j K L M ABC, Inc. 26 1 Date Initiated Date Completed Units Completed 4/1/2020 4/30/2020 18 Item # Direct Materials per Unit Utilized per Unit Amount 1 $35.00 10 $0.18 1 $70.00 Direct Labor per Unit Hours Amount 2 $40.00 3 $50.00 52B 52D 60A A 1 Job-Order Sheet 2 3 Customer 4 Job Number 5 Department 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Subtotal Employee # $0.00 123456 $0.00 234567 $0.00 $ $0.00 $0.00 $0.00 $0.00 Total Hours Job Manufacturing Overhead (MOH) Subtotal Hours DLH Rate Amount $0.00 $7.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Total MOH $ $0.00 Total Materials 0 Cost Summary for Job #26 Direct Materials Direct Labor Manufacturing Overhead Total Cost Unit Product Cost WALKTHROUGH: Cost Analysis of Job-Order Sheets 1. Naming Cells: Individual cells, cell ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." Name cell G5 by selecting the cell and typing Units into the box at the top left of the toolbar: 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). Manufacturing overhead is allocated based on the total direct labor hours. Therefore, $5 should be allocated to each direct labor hour the job utilized. a. Calculate the total hours for Job #26 in cell J9. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. Create a formula for total direct labor hours used in Job #26 by multiplying the subtotal of direct hours per unit (cell G15; 6) by the number of units in the job (cell G5; 20). b. Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26. Multiply the hours by the DLH rate to find the amount of manufacturing overhead. Is the manufacturing overhead amount you found for the JOB or for EACH UNIT? The manufacturing overhead amount is found for Job #26 as a whole, not per unit, because the rate is allocated based on the total labor for the job, not the labor for one unit. 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? You must multiply the direct materials used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $2.544.00. 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? You must multiple the direct labor amount used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $5,700.00. c. Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code. The manufacturing overhead for Job #26 has already been calculated. Instead of typing the overhead amount ($600.00) in the cell, reference the cell (L15) in which the total is located. Note you do NOT need to multiple this rate by the 20 units because the amount is already a per job amount. The correct answer is $600.00. d. Calculate the total costs for Job #26 in cell G21. The total job costs can now be found for Job #26 by adding the direct labor, direct materials, and manufacturing overhead totals. The SUM formula is most efficient. The correct answer is $8.844.00. e. Calculate the unit product cost for Job #26 in cell G22. The unit product cost can be found by dividing the total cost (cell G21) by the number of units in Job #16 (cell G5; 20). The correct answer is $442.20. Chapter 19 Homework 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.). 4. reflect and utilize the helpful information you created in the job-order sheet. Case scenario: You are working at a manufacturing plant. 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. Your company accumulates cost per job on a Job-Order Sheet. Required: Download spreadsheet JobOrderCaseData-901f95.xlsx 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 ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). a. Calculate the total hours for Job #26 in cell 19. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. 90 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 amount is found for Job #26 - V. 3. Cost Summary 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 overhead is an indirect cost, not direct costs, associated with manufacturing. It requires analyzation of total indirect costs, a rational method of allocating the cost, and a rate. This differs from direct material and labor because those costs are v traceable outright to producing a unit. b. What variable(s) does direct labor depend on? Direct labor depends on units. What variable(s) do direct materials depend on? Direct materials depends on units. What variable(s) does manufacturing overhead depend on? Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc. C. What information can you gleam from the job-cost spreadsheet? What is the most expensive part of the job? least expensive? Direct labor v is the most expensive part of manufacturing this job. It accounts for % of the job's cost. about The least expensive part of this manufacturing job is manufacturing overhead V. It is about % of the job's cost. A1 Xv fx Job-Order Sheet B D E F G H j K L M ABC, Inc. 26 1 Date Initiated Date Completed Units Completed 4/1/2020 4/30/2020 18 Item # Direct Materials per Unit Utilized per Unit Amount 1 $35.00 10 $0.18 1 $70.00 Direct Labor per Unit Hours Amount 2 $40.00 3 $50.00 52B 52D 60A A 1 Job-Order Sheet 2 3 Customer 4 Job Number 5 Department 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Subtotal Employee # $0.00 123456 $0.00 234567 $0.00 $ $0.00 $0.00 $0.00 $0.00 Total Hours Job Manufacturing Overhead (MOH) Subtotal Hours DLH Rate Amount $0.00 $7.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Total MOH $ $0.00 Total Materials 0 Cost Summary for Job #26 Direct Materials Direct Labor Manufacturing Overhead Total Cost Unit Product Cost WALKTHROUGH: Cost Analysis of Job-Order Sheets 1. Naming Cells: Individual cells, cell ranges, and tables can be named within Excel for formula ease. For example, the Total Material Subtotal (cell E15) can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units (cell G5), "Units." Name cell G5 by selecting the cell and typing Units into the box at the top left of the toolbar: 2. Manufacturing Overhead: Review the manufacturing overhead rate in cell K9 of the job-order spreadsheet and observe how the manufacturing overhead is allocated for Job #26. (Hint: "DLH" stands for Direct Labor Hours). Manufacturing overhead is allocated based on the total direct labor hours. Therefore, $5 should be allocated to each direct labor hour the job utilized. a. Calculate the total hours for Job #26 in cell J9. Remember, your goal is to create a reusable spreadsheet. Total labor hours should NOT be hardcoded (or typed in), but a formula should be used to accommodate variations in total hours. Create a formula for total direct labor hours used in Job #26 by multiplying the subtotal of direct hours per unit (cell G15; 6) by the number of units in the job (cell G5; 20). b. Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26. Multiply the hours by the DLH rate to find the amount of manufacturing overhead. Is the manufacturing overhead amount you found for the JOB or for EACH UNIT? The manufacturing overhead amount is found for Job #26 as a whole, not per unit, because the rate is allocated based on the total labor for the job, not the labor for one unit. 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? You must multiply the direct materials used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $2.544.00. 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? You must multiple the direct labor amount used on a per unit bases by how many units are produced in Job #26 (20). The correct answer is $5,700.00. c. Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code. The manufacturing overhead for Job #26 has already been calculated. Instead of typing the overhead amount ($600.00) in the cell, reference the cell (L15) in which the total is located. Note you do NOT need to multiple this rate by the 20 units because the amount is already a per job amount. The correct answer is $600.00. d. Calculate the total costs for Job #26 in cell G21. The total job costs can now be found for Job #26 by adding the direct labor, direct materials, and manufacturing overhead totals. The SUM formula is most efficient. The correct answer is $8.844.00. e. Calculate the unit product cost for Job #26 in cell G22. The unit product cost can be found by dividing the total cost (cell G21) by the number of units in Job #16 (cell G5; 20). The correct answer is $442.20
