pivot
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs completed by the company during the year. Your goal is to complete the table shown below and comment on the insights that it provides:
| Job | Sales | Direct Materials | Direct Labor | Applied Overhead | Total Job Cost | Gross Margin | |
| 1 | $ | 2,400 | ? | ? | ? | ? | ? |
| 2 | $ | 5,400 | ? | ? | ? | ? | ? |
| 3 | $ | 9,000 | ? | ? | ? | ? | ? |
| 4 | $ | 1,450 | ? | ? | ? | ? | ? |
| 5 | $ | 2,200 | ? | ? | ? | ? | ? |
| 6 | $ | 7,000 | ? | ? | ? | ? | ? |
| 7 | $ | 1,700 | ? | ? | ? | ? | ? |
| 8 | $ | 1,600 | ? | ? | ? | ? | ? |
| 9 | $ | 8,000 | ? | ? | ? | ? | ? |
| 10 | $ | 4,100 | ? | ? | ? | ? | ? |
| 11 | $ | 3,200 | ? | ? | ? | ? | ? |
| 12 | $ | 2,800 | ? | ? | ? | ? | ? |
Click here to download the Excel template, which you will use to answer the questions that follow.
Click here for a brief tutorial on PivotTables in Excel.
Click here for a brief tutorial on VLOOKUP in Excel.
Excel Analytics 02-02 (Static) Part 1
Required:
1. Go to the tab titled “Direct Materials Requisitions.”:
a. Using PivotTable, calculate the total direct materials cost for each of the 12 jobs.
b. What is the total direct materials cost for Jobs 5 and 11?
Homework Answers
Direct Labor
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Total Cost
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Applied Overhead
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
MOH
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Costs
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Gross Margin
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Labor Percents
Ozuna Company uses a job-order costing system with a plantwide predetermined overhead rate based on direct labor-hours. For job costing purposes, it uses an average direct labor wage rate of $20 per hour. The company has been struggling financially; accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs...
Hahn Company uses a job-order costing system. Its plantwide predetermined overhead rate uses direct labor-hours as...
Hahn Company uses a job-order costing system. Its plantwide predetermined overhead rate uses direct labor-hours as the allocation base. The company pays its direct laborers $21.00 per hour. During the year, the company started and completed only two jobs—Job Alpha, which used 69,500 direct labor-hours, and Job Omega. The job cost sheets for the these two jobs are shown below: Job Alpha Direct materials ? Direct labor ? Manufacturing overhead applied ? Total job cost $ 2,926,000 Job Omega Direct...
Pritchet Computer Company uses a job order costing system in which each batch manufactured is a...
Pritchet Computer Company uses a job order costing system in which each batch manufactured is a different job. Pritchet Computer Company assigns direct materials and direct labor to each job. The company assigns labor costs at $22 per hour. It allocates manufacturing overhead to jobs based on a predetermined overhead allocation rate, computed as a percentage of direct labor costs. At the beginning of 2020, the controller prepared the following budget: (Click the icon to view the budget.) In November...
Phyllo Computer Company uses a job order costing system in which each batch manufactured is a...
Phyllo Computer Company uses a job order costing system in which each batch manufactured is a different job. Phyllo Computer Company assigns direct materials and direct labor to each job. The company assigns labor costs at $23 per hour. It allocates manufacturing overhead to jobs based on a predetermined overhead allocation rate, computed as a percentage of direct labor costs. At the beginning of 2020, the controller prepared the following budget: (Click the icon to view the budget.) In November...
Pritchet Computer Company uses a job order costing system in which each batch manufactured is a different job. Pritchet Computer Company assigns direct materials and direct labor to each job. The company assigns labor costs at $22 per hour. It allocates manufacturing overhead to jobs based on a predetermined overhead allocation rate, computed as a percentage of direct labor costs. At the beginning of 2020, the controller prepared the following budget: (Click the icon to view the budget.) In November...
Phyllo Computer Company uses a job order costing system in which each batch manufactured is a different job. Phyllo Computer Company assigns direct materials and direct labor to each job. The company assigns labor costs at $23 per hour. It allocates manufacturing overhead to jobs based on a predetermined overhead allocation rate, computed as a percentage of direct labor costs. At the beginning of 2020, the controller prepared the following budget: (Click the icon to view the budget.) In November...
Most questions answered within 3 hours.
-
accorsing to the data from thw college board, the mean wuantitive
Sat score for s demale...
asked 29 minutes ago -
1- What are 5 general characteristics of Botticelli’s
paintings?
2- Describe 5 important traits of Flemish...
asked 2 hours ago -
A golfer claims that his average golf score at the course he
plays regularly is less...
asked 2 hours ago -
On May 1, First Bank perfected a security interest in Debtor’s
inventory. On June 1, Bob...
asked 3 hours ago -
A new kind of typhoid shot is being developed by a medical
research team. The old...
asked 4 hours ago -
Write a chemical equation for these reactions. (Use
the lowest possible whole number coefficients.) Include
states-of-matter...
asked 6 hours ago -
For each of the Z-scores below, determine two probabilities
(show four three decimal places): (1) the...
asked 7 hours ago -
Oxidative phosphorylation is achieved through chemiosmotic
coupling, which turns chemical energy into osmotic potential energy
that...
asked 10 hours ago -
Hollywood Shoes would like to maintain their cash account at a
minimum level of $64,000, but...
asked 10 hours ago -
Proteins CtrA-P, DnaA, and GcrA behave similarly to what
molecule in the Eukaryotic Cell Cycle?
asked 10 hours ago -
TRUE OR FALSE
In case of a competitive tender procedure, in the request for
quotation, the...
asked 10 hours ago -
I am a little behind please provide some explanation, will
provide thumbs up.
This question considers...
asked 10 hours ago