Question

1 Approved Answer

Posted on Jul 16, 2024

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

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 1 2 3 4 Direct Sales Materials $2,400 $5,400 ? $9,000 ? $1,450 ? $2,200 ? $7,000 ? $1,700 ? $1,600 7 $8,000 ? $4,100 7 $3,200 ? $2,800 ? 6 7 8 9 10 11 12 Direct Labor ? 7 ? ? ? ? 7 7 7 ? ? ? Applied Overhead ? ? ? ? ? Total Job Cost ? ? 7 ? ? ? 2 ? ? ? ? ? Gross Margin ? ? ? ? 7 2 ? 7 7 ? ? ? ? 2 ? 7 ? ? 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. 2. Go to the tab titled "Direct Labor Summary": a. Using PivotTable, calculate the total direct labor cost and total direct labor-hours for each of the 12 jobs. b. What is the total direct labor cost for Jobs 3 and 10? c. What are the total direct labor-hours for Jobs 3 and 10? C 89. Employee Department 2 Allison Vanston Assembly 3 Allison Vanston Assembly 4 Cathy Smithson Assembly 5 Cathy Smithson Assembly 6 Cathy Smithson Assembly 7 Cathy Smithson Assembly 8 Christopher Tanaka Assembly 9 Christopher Tanaka Assembly 10 Christopher Tanaka Assembly 11 Damien Charles Fabrication 12 Damien Charles Fabrication 13 Damien Charles Fabrication 14 Damien Charles Fabrication 15 David Chen Molding 16 David Chen Molding 17 David Chen Molding 18 Emily Evans Assembly 19 Emily Evans Assembly 20 Emily Evans Assembly 21 Emily Evans Assembly 22 Ernest Kroyman Assembly 23 Emest Kroyman Assembly 24 Ernest Kroyman Assembly 25 let Durand Molding 25 Jeff Durand Molding 27 Teff Durand Molding 28 Jeff Durand Molding 29 Julia Rhymer Assembly 30 Julia Rhymer Assembly 31 Juwan Thompson Molding 32 juwan Thompson Molding 33 Juwan Thompson Molding 34 Kayla Burke Assembly 35 Loretta Hoyt Assembly 36 Loretta Hoyt Assembly 37 Loretta Hoyt Assembly 38 Loretta Hoyt Assembly 39 Madison Rodriguez Fabrication 40 Madison Rodriquez Fabrication 41 Marshall Tollver Molding 42 Marshall Toliver Molding 43 Mary Jo Hawley Fabrication 44 Mary Jo Hawley Fabrication 45 Mary Jo Hawley Fabrication 46 Mary Jo Hawley Fabrication 47 Michael Kershaw Fabrication 48 Michael Kershaw Fabrication 49 Rebecca Wills Assembly So Rebecca Wills Assembly 51 Rebecca Wills Assembly 52 Rebecca Wils Assembly 53 Rebecca Wils Assembly 54 Rebecca Willis Assembly 55 waliam Marks Assembly 56 William Marks Assembly 57 William Marks Assembly 58 59 CO 12 1 4 7 8 1 2 8 1 3 6 9 4 6 7 3 6 11 12 2 3 12 2 5 10 12 5 11 1 3 9 5 2 6 9 11 5 7 8 11 2 10 11 12 4 8 1 2 6 8 9 10 3 4 7 Average Direct Labor Direct Labor Direct labor Hours Wage Rate Cost 17.00 $ 20$ 340 3.25 20 S 65 2.50 S 20 S 0.755 20 $ 15 2355 20 S 45 3.505 20 S 70 1.5015 20 S 30 20$ 20 50 3.755 20 S 75 4505 20 s 90 25 50 S 20 S 530 11.00 $ 20 $ 220 27.00 $ 20 $ 540 100 S 205 20 12.00 S 20$ 240 2.25$ 20 S 45 30.00 $ 205 600 20.50 $ 20 S 410 8.00 S 20 $ 160 5.00 $ 20 $ 100 15.00 $ 20 $ 300 31.00 $ 20 5 620 3.25$ 20 S 75 10.50 $ 20 S 210 2755 20 S 55 10.00 $ 20 S 200 4.00 $ 20$ 80 6.50 5 20$ 130 100 5 2015 325 20 S 65 22.00 $ 20 S 440 25.00 $ 20$ 500 3.25 S 20 S 65 13:50 $ 20 S 270 18. SOS 20 S 370 21.00 20 S 420 4.00 $ 20 S 80 450 $ 20 S 90 225$ 20 S 55 0.75$ 20 S 15 6.00S 205 120 11.25 5 20 S 8.00 5 20 s 160 11.00 S 205 6.00 5 205 120 2009 20 S 20 4.00 5 20$ 80 125$ 20 S 65 12.75$ 20 $ 255 26.00 $ 20$ 20 3.505 20 S 70 32.00 5 205 640 7.00 $ 20 S 140 15 SOS 205 310 1005 2015 20 1255 20$ 25 220 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 1 2 3 4 Direct Sales Materials $2,400 $5,400 ? $9,000 ? $1,450 ? $2,200 ? $7,000 ? $1,700 ? $1,600 7 $8,000 ? $4,100 7 $3,200 ? $2,800 ? 6 7 8 9 10 11 12 Direct Labor ? 7 ? ? ? ? 7 7 7 ? ? ? Applied Overhead ? ? ? ? ? Total Job Cost ? ? 7 ? ? ? 2 ? ? ? ? ? Gross Margin ? ? ? ? 7 2 ? 7 7 ? ? ? ? 2 ? 7 ? ? 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. 2. Go to the tab titled "Direct Labor Summary": a. Using PivotTable, calculate the total direct labor cost and total direct labor-hours for each of the 12 jobs. b. What is the total direct labor cost for Jobs 3 and 10? c. What are the total direct labor-hours for Jobs 3 and 10? C 89. Employee Department 2 Allison Vanston Assembly 3 Allison Vanston Assembly 4 Cathy Smithson Assembly 5 Cathy Smithson Assembly 6 Cathy Smithson Assembly 7 Cathy Smithson Assembly 8 Christopher Tanaka Assembly 9 Christopher Tanaka Assembly 10 Christopher Tanaka Assembly 11 Damien Charles Fabrication 12 Damien Charles Fabrication 13 Damien Charles Fabrication 14 Damien Charles Fabrication 15 David Chen Molding 16 David Chen Molding 17 David Chen Molding 18 Emily Evans Assembly 19 Emily Evans Assembly 20 Emily Evans Assembly 21 Emily Evans Assembly 22 Ernest Kroyman Assembly 23 Emest Kroyman Assembly 24 Ernest Kroyman Assembly 25 let Durand Molding 25 Jeff Durand Molding 27 Teff Durand Molding 28 Jeff Durand Molding 29 Julia Rhymer Assembly 30 Julia Rhymer Assembly 31 Juwan Thompson Molding 32 juwan Thompson Molding 33 Juwan Thompson Molding 34 Kayla Burke Assembly 35 Loretta Hoyt Assembly 36 Loretta Hoyt Assembly 37 Loretta Hoyt Assembly 38 Loretta Hoyt Assembly 39 Madison Rodriguez Fabrication 40 Madison Rodriquez Fabrication 41 Marshall Tollver Molding 42 Marshall Toliver Molding 43 Mary Jo Hawley Fabrication 44 Mary Jo Hawley Fabrication 45 Mary Jo Hawley Fabrication 46 Mary Jo Hawley Fabrication 47 Michael Kershaw Fabrication 48 Michael Kershaw Fabrication 49 Rebecca Wills Assembly So Rebecca Wills Assembly 51 Rebecca Wills Assembly 52 Rebecca Wils Assembly 53 Rebecca Wils Assembly 54 Rebecca Willis Assembly 55 waliam Marks Assembly 56 William Marks Assembly 57 William Marks Assembly 58 59 CO 12 1 4 7 8 1 2 8 1 3 6 9 4 6 7 3 6 11 12 2 3 12 2 5 10 12 5 11 1 3 9 5 2 6 9 11 5 7 8 11 2 10 11 12 4 8 1 2 6 8 9 10 3 4 7 Average Direct Labor Direct Labor Direct labor Hours Wage Rate Cost 17.00 $ 20$ 340 3.25 20 S 65 2.50 S 20 S 0.755 20 $ 15 2355 20 S 45 3.505 20 S 70 1.5015 20 S 30 20$ 20 50 3.755 20 S 75 4505 20 s 90 25 50 S 20 S 530 11.00 $ 20 $ 220 27.00 $ 20 $ 540 100 S 205 20 12.00 S 20$ 240 2.25$ 20 S 45 30.00 $ 205 600 20.50 $ 20 S 410 8.00 S 20 $ 160 5.00 $ 20 $ 100 15.00 $ 20 $ 300 31.00 $ 20 5 620 3.25$ 20 S 75 10.50 $ 20 S 210 2755 20 S 55 10.00 $ 20 S 200 4.00 $ 20$ 80 6.50 5 20$ 130 100 5 2015 325 20 S 65 22.00 $ 20 S 440 25.00 $ 20$ 500 3.25 S 20 S 65 13:50 $ 20 S 270 18. SOS 20 S 370 21.00 20 S 420 4.00 $ 20 S 80 450 $ 20 S 90 225$ 20 S 55 0.75$ 20 S 15 6.00S 205 120 11.25 5 20 S 8.00 5 20 s 160 11.00 S 205 6.00 5 205 120 2009 20 S 20 4.00 5 20$ 80 125$ 20 S 65 12.75$ 20 $ 255 26.00 $ 20$ 20 3.505 20 S 70 32.00 5 205 640 7.00 $ 20 S 140 15 SOS 205 310 1005 2015 20 1255 20$ 25 220