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:

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 E$15)$ can be named "TotalMaterialSubtotal." Going forward, cells can be referenced using words instead of the Excel coordinates. Label the completed units $($cell G$5),$ "Units."

$2.$ Manufacturing Overhead: Review the manufacturing overhead rate in cell K$9$ 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 J$9.$ 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 $1$ of $1$$ hours

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

fill in the blank $1$ of $1$$

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

The manufacturing overhead amount is found for

$.$

$3.$ Cost Summary

a$.$ Calculate total direct materials for Job #$26$ in cell G$18.$ Hint: Is the direct material information given the total for the job or for each unit?

fill in the blank $1$ of $1$$

b$.$ Calculate direct labor for Job #$26$ in cell G$19.$ Hint: Is the direct labor information given the total for the job or for each unit?

fill in the blank $1$ of $1$$

c$.$ Calculate manufacturing overhead for Job #$26$ in cell G$20.$ Do not hard$-$code.

fill in the blank $1$ of $1$$

d$.$ Calculate the total costs for Job #$26$ in cell G$21.$

fill in the blank $1$ of $1$$

e$.$ Calculate the unit product cost for Job #$26$ in cell G$22.$

fill in the blank $1$ of $1$$

$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 fill in the blank $1$ of $4$

cost$,$ not fill in the blank $2$ of $4$

costs, associated with manufacturing. It requires analyzation of total fill in the blank $3$ of $4$

costs, a rational method of allocating the cost, and a rate. This differs from direct material and labor because those costs fill in the blank $4$ of $4$

traceable outright to producing a unit.

b$.$ What variable$($s$)$ does direct labor depend on$?$

What variable$($s$)$ do direct materials depend on$?$

What variable$($s$)$ does manufacturing overhead depend on$?$

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

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

The least expensive part of this manufacturing job is

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

$5.$ Explain how job cost sheets are used to evaluate and control costs.