Required information The following information applies to the questions displayed below.) 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 Direct Applied Materials Direct Labor Overhead Job 1 2 3 4 5 6 7 8 9 10 11 12 Sales $2,400 $5,400 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 Total Job Cost ? ? > ? ? ? ? ? ? ? ? Gross Margin ? 2 2 ? ? 2 2 ? ? ? 2 2 ? ? Click here to download the Excel template, which you will use to answer the questions that follow Click here for a brief tutorial on Pivot Tables in Excel. Click here for a brief tutorial on VLOOKUP in Excel Required: 5. Go to the tab titled "Job Profitability": o. Using formulas that refer to your answers from requirements 1-4, calculate the direct materials, direct labor, and applied overhead cost for each of the 12 jobs. (Hint: Use VLOOKUP to reference values from the plvot tables you created in requirements 1 and 2) b. What is the amount of applied overhead for Jobs 4 and 9? Job 4 Job D The applied overhead is Direct Labor HOUS 17.00 Dieet Labor Wage Job Direct Labor Cost 300 15 45 C 150 3.75 26.50 Molding Employee Departmen A Vancton Asie Alice Vandton Astebe Cwby Station 5 Cathy Seiten 6th Sethic Cat Sidor Christopher Astorby Christopher Tanaki Asembly Christophe Tonal Assembly 11 Damen Charles Fabac t2 Damien Chile Focion 1 Damien Charles Fabricion Omien Charles Fabricio 17 Oswd Chen David Chen Molding wid Chen Molding Emily Evans Assembly Ey Evans Aster 20 Emby Evans Assembly En Evans Askeby 22 Ernest Roman Assembly 23 Emekom Ascent 20 Emesko Ascort 25 Uell Owand Molding 26 Jeff Dund Melong 27 Jolluind Molding Jel Durand Molding James 30 W Antony wan Thompson Molding 3an Thompson Molding 3an Thompson Moldin 34 KBuke Assembly A comb Loretta Hoy Arter 37 Loretta Hoge Asseby 31 LoreHost Assent 38 Msdecode Fabrication 40 Medien Rodrouet Fabrication 41 Marchal Toliver Molding 42 Mais Tower Molding 49 Mar biconic 44 Mwy 45 May Jo Harley 46 47 49 49 27.00 $ 100 12.00 225 30.00 20.50 8.00 500 15.00 3100 3.75 10.50 2.75 10.00 8888888888888888888 530 20 540 20 240 as 600 410 150 200 300 520 75 210 SS 200 80 130 20 GS 440 500 65 270 370 420 80 Pero 200 Hoy 82 1225 120 20 80 51 52 Re CSW 59 Rebecca 56 Wils WeMule WAN 57 WinMake 8888888888 2888888888888 255 520 70 640 140 310 20 25 A AT 20 20 Direct Labor Summary Estimated MOH Job Profita E F G A B 1 Requisition # Department 2 MR1 Molding 3 MR2 Molding 4 MR3 Molding 5 MR4 Molding 6 MR5 Molding 7 MR6 Molding 8 MR7 Molding 9 MR8 Molding 10 MR9 Molding 11 MR10 Molding 12 MR11 Molding 13 MR12 Molding 14 MR13 Fabrication 15 MR14 Fabrication 16 MR15 Fabrication 17 MR16 Fabrication 18 MR17 Fabrication 19 MR18 Fabrication 20 MR19 Fabrication 21 MR20 Fabrication 22 MR21 Fabrication 23 MR22 Fabrication 24 MR23 Fabrication 25 MR24 Fabrication 26 MR25 Assembly 27 MR26 Assembly 28 MR27 Assembly 29 MR28 Assembly 30 MR29 Assembly 31 MR30 Assembly 32 MR31 Assembly 33 MR32 Assembly 34 MR33 Assembly 35 36 37 38 D Job Cost 1 $ 175.00 3 $ 188.00 8 $ 155.00 2 $ 305.00 11 $ 160.00 4 $ 168.00 5 $ 170.00 12 $ 210.00 10 $ 148.00 9 $ 192.00 6 $ 185.00 7 $ 144.00 2 $ 140.00 4 $ 138.00 6 $ 135.00 11 $ 129.00 1 $ 198.00 3 $ 134.00 12 $ 142.00 5 S 136.00 7 $ 105.00 9 $ 131.00 8 $ 132.00 10 $ 119.00 4 $ 118.00 7 S 80.00 9 $ 112.00 12 $ 116.00 11 S 113.00 2 $ 115.00 6 S 112.00 10 $ 114.00 1 $ 169.00 Required information The following information applies to the questions displayed below.) 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 Direct Applied Materials Direct Labor Overhead Job 1 2 3 4 5 6 7 8 9 10 11 12 Sales $2,400 $5,400 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 Total Job Cost ? ? > ? ? ? ? ? ? ? ? Gross Margin ? 2 2 ? ? 2 2 ? ? ? 2 2 ? ? Click here to download the Excel template, which you will use to answer the questions that follow Click here for a brief tutorial on Pivot Tables in Excel. Click here for a brief tutorial on VLOOKUP in Excel Required: 5. Go to the tab titled "Job Profitability": o. Using formulas that refer to your answers from requirements 1-4, calculate the direct materials, direct labor, and applied overhead cost for each of the 12 jobs. (Hint: Use VLOOKUP to reference values from the plvot tables you created in requirements 1 and 2) b. What is the amount of applied overhead for Jobs 4 and 9? Job 4 Job D The applied overhead is Direct Labor HOUS 17.00 Dieet Labor Wage Job Direct Labor Cost 300 15 45 C 150 3.75 26.50 Molding Employee Departmen A Vancton Asie Alice Vandton Astebe Cwby Station 5 Cathy Seiten 6th Sethic Cat Sidor Christopher Astorby Christopher Tanaki Asembly Christophe Tonal Assembly 11 Damen Charles Fabac t2 Damien Chile Focion 1 Damien Charles Fabricion Omien Charles Fabricio 17 Oswd Chen David Chen Molding wid Chen Molding Emily Evans Assembly Ey Evans Aster 20 Emby Evans Assembly En Evans Askeby 22 Ernest Roman Assembly 23 Emekom Ascent 20 Emesko Ascort 25 Uell Owand Molding 26 Jeff Dund Melong 27 Jolluind Molding Jel Durand Molding James 30 W Antony wan Thompson Molding 3an Thompson Molding 3an Thompson Moldin 34 KBuke Assembly A comb Loretta Hoy Arter 37 Loretta Hoge Asseby 31 LoreHost Assent 38 Msdecode Fabrication 40 Medien Rodrouet Fabrication 41 Marchal Toliver Molding 42 Mais Tower Molding 49 Mar biconic 44 Mwy 45 May Jo Harley 46 47 49 49 27.00 $ 100 12.00 225 30.00 20.50 8.00 500 15.00 3100 3.75 10.50 2.75 10.00 8888888888888888888 530 20 540 20 240 as 600 410 150 200 300 520 75 210 SS 200 80 130 20 GS 440 500 65 270 370 420 80 Pero 200 Hoy 82 1225 120 20 80 51 52 Re CSW 59 Rebecca 56 Wils WeMule WAN 57 WinMake 8888888888 2888888888888 255 520 70 640 140 310 20 25 A AT 20 20 Direct Labor Summary Estimated MOH Job Profita E F G A B 1 Requisition # Department 2 MR1 Molding 3 MR2 Molding 4 MR3 Molding 5 MR4 Molding 6 MR5 Molding 7 MR6 Molding 8 MR7 Molding 9 MR8 Molding 10 MR9 Molding 11 MR10 Molding 12 MR11 Molding 13 MR12 Molding 14 MR13 Fabrication 15 MR14 Fabrication 16 MR15 Fabrication 17 MR16 Fabrication 18 MR17 Fabrication 19 MR18 Fabrication 20 MR19 Fabrication 21 MR20 Fabrication 22 MR21 Fabrication 23 MR22 Fabrication 24 MR23 Fabrication 25 MR24 Fabrication 26 MR25 Assembly 27 MR26 Assembly 28 MR27 Assembly 29 MR28 Assembly 30 MR29 Assembly 31 MR30 Assembly 32 MR31 Assembly 33 MR32 Assembly 34 MR33 Assembly 35 36 37 38 D Job Cost 1 $ 175.00 3 $ 188.00 8 $ 155.00 2 $ 305.00 11 $ 160.00 4 $ 168.00 5 $ 170.00 12 $ 210.00 10 $ 148.00 9 $ 192.00 6 $ 185.00 7 $ 144.00 2 $ 140.00 4 $ 138.00 6 $ 135.00 11 $ 129.00 1 $ 198.00 3 $ 134.00 12 $ 142.00 5 S 136.00 7 $ 105.00 9 $ 131.00 8 $ 132.00 10 $ 119.00 4 $ 118.00 7 S 80.00 9 $ 112.00 12 $ 116.00 11 S 113.00 2 $ 115.00 6 S 112.00 10 $ 114.00 1 $ 169.00