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-7c1036.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:

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."

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).

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.

fill in the blank 2 hours

Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26.

$fill in the blank 3

Is the manufacturing overhead amount you found for the JOB or for EACH UNIT?

The manufacturing overhead amount is found for

each unitJob #26Job #26

1. .

Cost Summary

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?

$fill in the blank 5

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?

$fill in the blank 6

Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code.

$fill in the blank 7

Calculate the total costs for Job #26 in cell G21.

$fill in the blank 8

Calculate the unit product cost for Job #26 in cell G22.

$fill in the blank 9

Reflect on your calculation process to further understand cost concepts.

How does manufacturing overhead differ from direct materials and direct labor?

Manufacturing overhead is

a directan indirectan indirect

1. cost, notcosts, associated with manufacturing. It requires analyzation of totalcosts, a rational method of allocating the cost, and a rate. This differs from direct material and labor because those coststraceable outright to producing a unit.

What variable(s) does direct labor depend on?

Direct labor depends on material costs.Direct labor depends on units.Direct labor depends on a variety of variables including units, hours, jobs, etc.Direct labor depends on units.

What variable(s) do direct materials depend on?

Direct materials depends on labor hours spent.Direct materials depends on units. Direct materials depends on a variety of variables including units, hours, jobs, etc.Direct materials depends on units.

What variable(s) does manufacturing overhead depend on?

Manufacturing overhead depends on material costs.Manufacturing overhead depends on units. Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc.Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc.

What information can you gleam from the job-cost spreadsheet? What is the most expensive part of the job? least expensive?

Direct laborDirect materialsManufacturing overheadDirect labor

1. is the most expensive part of manufacturing this job. It accounts for about fill in the blank 18 % of the job's cost.

The least expensive part of this manufacturing job is

direct labordirect materialsmanufacturing overheadmanufacturing overhead

1. . It is about fill in the blank 20 % of the job's cost.

areare notare

directindirectindirect

directindirectdirect

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.

Feedback Area

Feedback

Partially correct

Check My Work1 more Check My Work uses remaining.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-7c1036.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:

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."

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).

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.

fill in the blank 2 hours

Now you have the building blocks to calculate the amount of manufacturing overhead. Calculate the manufacturing overhead in cell L9 for Job #26.

$fill in the blank 3

Is the manufacturing overhead amount you found for the JOB or for EACH UNIT?

The manufacturing overhead amount is found for

each unitJob #26Job #26

1. .

Cost Summary

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?

$fill in the blank 5

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?

$fill in the blank 6

Calculate manufacturing overhead for Job #26 in cell G20. Do not hard-code.

$fill in the blank 7

Calculate the total costs for Job #26 in cell G21.

$fill in the blank 8

Calculate the unit product cost for Job #26 in cell G22.

$fill in the blank 9

Reflect on your calculation process to further understand cost concepts.

How does manufacturing overhead differ from direct materials and direct labor?

Manufacturing overhead is

a directan indirectan indirect

1. cost, notcosts, associated with manufacturing. It requires analyzation of totalcosts, a rational method of allocating the cost, and a rate. This differs from direct material and labor because those coststraceable outright to producing a unit.

What variable(s) does direct labor depend on?

Direct labor depends on material costs.Direct labor depends on units.Direct labor depends on a variety of variables including units, hours, jobs, etc.Direct labor depends on units.

What variable(s) do direct materials depend on?

Direct materials depends on labor hours spent.Direct materials depends on units. Direct materials depends on a variety of variables including units, hours, jobs, etc.Direct materials depends on units.

What variable(s) does manufacturing overhead depend on?

Manufacturing overhead depends on material costs.Manufacturing overhead depends on units. Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc.Manufacturing overhead depends on a variety of variables including units, hours, jobs, etc.

What information can you gleam from the job-cost spreadsheet? What is the most expensive part of the job? least expensive?

Direct laborDirect materialsManufacturing overheadDirect labor

1. is the most expensive part of manufacturing this job. It accounts for about fill in the blank 18 % of the job's cost.

The least expensive part of this manufacturing job is

direct labordirect materialsmanufacturing overheadmanufacturing overhead

1. . It is about fill in the blank 20 % of the job's cost.

areare notare

directindirectindirect

directindirectdirect

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.

Feedback Area

Feedback

Partially correct

Check My Work1 more Check My Work uses remaining.