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 Direct Total Job Job Gross Sales Applied Materials Labor Overhead Cost 1 Margin $2,400 ? 2 ? $5,400 7 ? $9,000 ? ? ? $1,450 $2,200 ? $7,000 7 $1,700 8 $1,600 $8,000 $4,100 $3,200 12 $2,800 ? 3 4 7 ? 2 ? ? ? 2 6 ? ? ? 2 7 ? 7 ? 7 ? ? ? ? 2 2 2 7 ? ? ? ? 2 7 9 18 11 7 2 7 ? 2 2 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 PlvotTables In Excel. Click here for a brief tutorial on VLOOKUP In Excel 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? The plantwide predetermined overhead rate is K15 ex fe A B D E F 1 Last Year This Year Estimated 2 Account Actual Amount Cost Behavior Amount Cost Behavior 3 Equipment depreciation: Molding $ 400,000 Fixed $ 400,000 Fixed 4 Equipment depreciation: Fabrication $ 300,000 Fored $ 5 Equipment depreciation: Assembly 300,000 Fixed $ 50,000 Fixed $ 50,000 Fixed 6 Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Fixed 7 Equipment depreciation: Other $ 10,000 Fixed $ 10,000 Fixed 8 Production suprvision: Molding $ 60,000 Fixed $ 63,000 Fixed 9 Production supervision: Fabrication $ 65,000 Fixed S 68,250 Fixed 10 Production supervision: Assembly $ 58,000 Fixed $ 60,900 Fixed 11 Indirect labor: Maintenance & Repairs S 72,000 Fixed $ 75,600 Fixed 12 Indirect labor: Material Handling $ 68,000 Fixed $ 71,400 Fbied 13 Indirect labor: Engineering $ 170,000 Fixed $ 178,500 Fixed 14 Indirect labor: Raw Materials Purchasing $ 93,000 Fixed $ 97,650 Fixed 15 indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Fixed 16 Indirect labor: Packaging & Shipping $ 99,000 Fixed $ 103,950 Fixed 17 Indirect materials: Molding $ 18,000 Variable Variable Your recent changes 18 Indirect materials: Fabrication $ 13,000 Variable to continue working w Variable 19 Indirect materials: Assembly $ 8,000 Variable Variable 20 Utilities: Molding $ 24,000 Variable Variable 21 Utilities: Fabrication $ 20,000 Variable Variable 22 Utilities: Assembly $ 6,000 Variable Variable 23 Utilties: Plant lighting and other equipment $ 12,000 Fixed S 12,240 Fixed 24 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Fored 25 Property insurance $ 26,000 Fixed $ 26,520 Fixed 26 Liability insurance $ 18,000 Foxed s 18,360 Fixed 27 Medical Insurance $ 45,000 Fred $ 51,750 Fixed 28 Workers' compensation insurance $ 13,000 Fixed $ 14,300 Fixed Pivot Table VLOOKUP Direct Materials Requisitions Direct Labor Summary Estimated MOH Job Profitability Waterfall Ready Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? K15 1 x fx D E G $ $ $ $ 178,500 97,650 39,900 103,950 13 Indirect labor: Engineering 14 Indirect labor: Raw Materials Purchasing 15 Indirect labor: Quality Control 16 Indirect labor: Packaging & Shipping 17 Indirect materials: Molding 18 Indirect materials: Fabrication 19 Indirect materials: Assembly 20 Utilities: Molding 21 Utilities: Fabrication 22 Utilities: Assembly 23 Utilties: Plant lighting and other equipment 24 Utilities: Heating and cooling 25 Property insurance 26 Liability Insurance 27 Medical insurance 28 Workers' compensation insurance 29 Other salaries: Plant manager 30 Other salaries: Accounting 31 Other salaries: Administrative support 32 Other salaries: Janitorial 33 Other salaries: Security and grounds maintenance 34 Property taxes 35 Payroll taxes: Direct labor 36 Payroll taxes: Other a7 Total Manufacturing Overhead 38 39. Total direct labor hours 40 Plantwide predetermined overhead rate 41 B $ 170,000 $ 93,000 $ 38,000 $ 99,000 $ 18,000 $ 13,000 $ 8,000 $ 24,000 $ 20,000 $ 6,000 $ 12,000 $ 24,000 $ 26,000 $ 18,000 $ 45,000 $ 13,000 $ 165,000 $ 89,000 S 56,000 $ 48,000 $ 44,000 $ 18,000 $ 79,000 $ 165,000 $ 2,389,000 Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed $ $ $ $ $ $ S $ 12,240 24,480 26,520 18,360 51,750 14,300 173,250 93,450 58,800 50,400 46,200 18,540 F Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Your recent char to continue work $ $ $ $ 173,250 $ 2,295,690 41,400 42,500 Pohle Mn N 18W