No worries if you cant answer all the questions but any help is awesome

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: Job 1 2 3 4 5 6 7 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 Direct Materials ? 2 ? ? 2 2 2 ? 2 ? 7 2 Direct Labor 2 ? ? 7 2 2 2 ? 2 ? 2 ? Applied Overhead 7 2 ? 2 2 ? ? ? 7 ? 7 Total Job Cost 2 2 2 ? ? ? 7 2 ? 2 Gross Margin ? ? 2 ? 2 2 2 2 ? 2 2 2 9 10 11 12 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 Pivot Tables in Excel Click here for a brief tutorial on VLOOKUP in Excel Required: Go to the tab titled "Estimated MOH": Direct Materials Requisitions Estimal Job 10 12 1 Employee Alicon Vanston Allison Vanston Cathy Smithson Cathy Smithson Cathy Smithson Cathy Smithson Christopher Tanaka Christopher Tanaka Christopher anala Damien Charles Damien Charles Damien Charles Damien Charles David Chen David Chen David Chen Emily Evans Emily Evans Emily Evans Emily Evans Ernest Kroyman Ernest Kroyman Ernest Kroyman Jeff Durand Jeff Durand Jeff Durand Jeff Durand Julia Rhymer Julia Rhymer Juwan Thompson Juwan Thompson Juwan Thompson Kayla Burke Loretta Hoyt Loretta Hoyt Loretta Hoyt Loretta Hoyt Madison Rodriquez Madison Rodriquez Marshall Toliver Marshall Toliver Mary Jo Hawley Mary Jo Hawley Mary Jo Hawley Mary Jo Hawley Michael Kershaw Michael Kershaw Rebecca Wills Rebecca Wills Rebecca Wills Rebecca Wills Rebecca Wilis Rebecca Wills William Marks William Morks William Marks Department Assembly Assembly Assembly Assembly Assembly Assembly Assembly Assembly Assembly Fabrication Fabrication Fabrication Fabrication Molding Molding Molding Assembly Assembly Assembly Assembly Assembly Assembly Assembly Molding Molding Molding Molding Assembly Assembly Molding Molding Molding Assembly Assembly Assembly Assembly Assembly Fabrication Fabrication Molding Molding Fabrication Fabrication Fabrication Fabrication Fabrication Fabrication Assembly Assembly Assembly Assembly Assembly Assembly Assembly Assembly Assembly 7 8 1 7 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 4.00 Direct Labor Summary Average Direct Labor Direct Labor Direct Labor Hours Wage Rate Cost 17.00 5 20$ 340 3.25 20 S 65 2.50 $ 20 S 50 0.75$ 20 S 15 2.25$ 205 45 2.50 20 S 70 1.505 205 30 2.50 S 20 $ 50 3.75 20$ 75 4.50 5 20 S 90 26.50 S 20 S 530 11.00 20 $ 220 27.00 $ 20 S 540 1.00 S 20 $ 20 12.00 20$ 240 2.25$ 20 $ 45 30.00 205 600 20.50 $ 20$ 410 8.00 $ 20 $ 160 5.00 S 20 $ 100 15.00 $ 20$ 300 31.00 $ 20 $ 620 3.75 S 20 $ 75 10.50 $ 20 S 210 2.75 S 20 S 55 10.00 $ 20 $ 200 S 205 80 6.50 $ 20$ 130 1.00 $ 20 S 20 3.25 $ 20 $ 65 22.00 $ 20 S 440 25.00 $ 20 $ 3.25$ 20 $ 65 13.50 $ 20 $ 270 18.50 $ 20 $ 370 21.00 $ 20 $ 420 4.00 $ 20 $ 80 4.50 $ 20 $ 90 3.25 $ 20 $ 65 0.75 S 20 $ 15 6.00 $ 20$ 120 11.25 $ 20 $ 225 8.00 $ 20 $ 160 11.00 $ 20 $ 220 6.00 $ 20 $ 120 1.00 $ 20 S 20 4.00 S 20 $ 80 3.25 S 20$ 65 12.75 20 $ 255 26.00 $ 20 $ 3.50 $ 20 $ 70 32.00 $ 20 S 640 7.00 $ 20$ 140 15.50 $ 20 $ 310 1.00 $ 20 S 20 1.25 $ 20 $ 25 11 5 7 8 11 2 10 11 12 4 8 1 2 6 8 9 10 3 4 7 SER Direct Materials Requisitions Direct Labor Summary Estimated MOH Job Profitability Waterfall Chart Last Year Foxed Account Equipment depreciation: Molding Equipment depreciation: Fabrication Equipment depreciation: Assembly Equipment depreciation: Material Handling Equipment depreciation: Other Production suprvision: Molding Production supervision: Fabrication Production supervision: Assembly Indirect labor: Maintenance & Repairs Indirect labor: Materials Handling Indirect labor: Engineering Indirect labor: Raw Materials Purchasing Indirect labor: Quality Control Indirect labor: Packaging & Shipping Indirect materials: Molding Indirect materials: Fabrication Indirect materials: Assembly Utilities: Molding Utilities: Fabrication Utilities: Assembly Utilties: Plant lighting and other equipment Utilities: Heating and cooling Property insurance Liability insurance Medical Insurance Workers' compensation insurance Other salaries: Plant manager Other salaries: Accounting Other salaries: Administrative support Other salaries: Janitorial Other salaries: Security and grounds maintenance Property taxes Payroll taxes: Direct labor Payroll taxes: Other Total Manufacturing Overhead Actual Amount Cost Behavior $ 400,000 Fixed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Feed $ 10,000 Fixed $ 60,000 Fixed $ 65,000 Fored $ 58,000 $ 72.000 Fixed $ 68,000 Fixed $ 170,000 Fored $ 93,000 Fixed $ 38,000 Fixed S 99,000 Fixed $ 18,000 Variable $ 13,000 Variable $ 8,000 Variable $ 24,000 Variable $ 20,000 Variable $ 6,000 Variable $ 12,000 Fixed S 24,000 Fixed $ 26,000 Fixed $ 18,000 Fixed $ 45,000 Fixed $ 13,000 Fixed $ 165,000 Fixed $ 89,000 Fioved $ 56,000 Fixed $ 48,000 Fixed $ 44,000 Fixed $ 18,000 Fixed $ 79,000 Variable $ 165.000 Fixed S2389.000 This Year Estimated Amount Cost Behavior $ 400,000 Flowed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Fixed $ 10,000 Fixed $ 63,000 Fixed $ 68,250 Fixed $ 60,900 Fixed $ 75,600 Fixed $ 71,400 Fixed $ 178,500 Fixed $ 97,650 Fixed $ 39,900 Fixed $ 103.950 Fixed Variable Variable Variable Variable Variable Variable $ 12,240 Fixed $ 24,480 Fixed $ 26,520 Fixed $ 18,360 Fixed $ 51,750 Fixed $ 14,300 Fixed $ 173,250 Fixed $ 93,450 Fixed $ 58,800 Fixed $ 50,400 Fixed $ 46,200 Fixed $ 18,540 Fixed Variable $ 173.250 Fixed S2295.690 42.500 41,400 Total direct labor hours Plantwide predetermined overhead rate Insert Table Zoom Add Category Direct Materials Requisitions Direct Labor Summary Chart -Shape Media Comment Estimated MOH Job Profitability Waterfall Chart Direct Materials Job Applied Overhead Direct Labor Total Job Cost Gross Margin 1 $ 2 $ 3 $ 4 $ 5 $ 6 $ 7 $ 8 $ 9 S 10 $ 11 $ 12 S Sales 2,400.00 5,400.00 9,000.00 1,450.00 2,200.00 7,000.00 1,700.00 1,600,00 8,000.00 4,100.00 3,200.00 2,800.00 Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel. 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? Job 5 Job 11 The total direct materials cost is 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? Complete this question by entering your answers in the tabs below. Reg 2B Reg 2C What are the total direct labor-hours for Jobs 3 and 10? Job 3 Job 10 The total direct labor hours are 7 B 9 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 7 2 2 2 2 2 2 2 2 2 7 2 ? 7 2 2 2 7 2 10. 11 12 Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PlvotTables in Excel Click here for a brief tutorial on VLOOKUP in Excel Required: 3. Go to the tab titled "Estimated MOH.": a. For each of the seven variable manufacturing overhead expenses (highlighted in yellow), create a formula that calculates the estimated amount of the expense for this year. (Hint: Use last year's actual amount of each expense and the actual total labor hours (cell B39) as the basis for this year's estimates.) 3b. and 3c. What is the estimated indirect materials cost in the Molding Department (cell E17) and the estimated utilities cost in the Fabrication Department (cell E21)? 3-b. 3-c The estimated indirect materials cost in Molding is The estimated utilities cost in Fabrication is Required: 4. Within the "Estimated MOH" tab. a. Create a formula in cell E40 that calculates a plantwide predetermined overhead rate this year. b. What is the plantwide predetermined overhead rate for this year? Answer is complete but not entirely correct. The plantwide predetermined overhead rate is $ 54.02 3 Required: 5. Go to the tab titled "Job Profitability": a. 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 pivot tables you created in requirements 1 and 2.) b. What is the amount of applied overhead for Jobs 4 and 9? Job 4 Job 9 The applied overhead is Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 127 c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Reg 68 Req 6C What is the total cost of Jobs 3 and 12? Job 3 Job 12 The total cost is Req GC > Required information Click here for a brief tutorial on PivotTables in Excel Click here for a brief tutorial on VLOOKUP in Excel Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 12? c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Reg 6B Req 6C What is the gross margin Jobs 1 and 8? Job 1 The gross margin is Job B 7 B 9 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 7 2 2 2 2 2 2 2 2 2 7 2 ? 7 2 2 2 7 2 10. 11 12 Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PlvotTables in Excel Click here for a brief tutorial on VLOOKUP in Excel Required: 3. Go to the tab titled "Estimated MOH.": a. For each of the seven variable manufacturing overhead expenses (highlighted in yellow), create a formula that calculates the estimated amount of the expense for this year. (Hint: Use last year's actual amount of each expense and the actual total labor hours (cell B39) as the basis for this year's estimates.) 3b. and 3c. What is the estimated indirect materials cost in the Molding Department (cell E17) and the estimated utilities cost in the Fabrication Department (cell E21)? 3-b. 3-c The estimated indirect materials cost in Molding is The estimated utilities cost in Fabrication is Required: 4. Within the "Estimated MOH" tab. a. Create a formula in cell E40 that calculates a plantwide predetermined overhead rate this year. b. What is the plantwide predetermined overhead rate for this year? Answer is complete but not entirely correct. The plantwide predetermined overhead rate is $ 54.02 3 Required: 5. Go to the tab titled "Job Profitability": a. 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 pivot tables you created in requirements 1 and 2.) b. What is the amount of applied overhead for Jobs 4 and 9? Job 4 Job 9 The applied overhead is Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 127 c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Reg 68 Req 6C What is the total cost of Jobs 3 and 12? Job 3 Job 12 The total cost is Req GC > Required information Click here for a brief tutorial on PivotTables in Excel Click here for a brief tutorial on VLOOKUP in Excel Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 12? c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Reg 6B Req 6C What is the gross margin Jobs 1 and 8? Job 1 The gross margin is Job B