Harris Company manufactures and sells a single product. A partially completed schedule of the company's total costs and costs per unit over the relevant range of 50,000 to 90,000 units is given below. Required: 1. Complete the schedule of the company's total costs and costs per unit as given in the relevant tab below. 2. Assume that the company produces and sells 80,000 units during the year at a selling price of $11.31 per unit. Prepare a contribution format income statement for the year. Complete this question by entering your answers in the tabs below. Required 1 Required 2 Complete the schedule of the company's total costs and costs per unit as given in the relevant tab below. (Round the per unit variable cost and fixed cost to 2 decimal places.) Units Produced and Sold 50,000 70,000 90,000 Total costs: Variable costs $ 180,000 Fixed costs 470,000 Total costs $ 650,000 $ 0 $ 0 Cost per unit Variable cost Fixed cost Total cost per unit $ 0.00 $ 0.00 $ 0.00 Assume that the company produces and sells 80,000 units during the year at a selling price of $11.31 per unit. Prepare a contribution format income statement for the year. Harris Company Contribution Format Income Statement 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 Direct Sales Materials $2,400 Direct Labor Applied Total Job: Overhead Cost Gross Margin ? 2 2 7 2345691 $5,400 ? 7 7 7 $9,000 2 2 7 2 $1,450 2 ? 2 $2,200 ? 2 7 $7,000 2 7 $1,700 2 ? 7 $1,600 7 7 9 $8,000 2 10 $4,100 7 11 $3,200 12 $2,800 2 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 Requisition # Department Job Cost MR1 Molding 1 $ 175.00 MR2 Molding 3 $ 188.00 MR3 Molding 8 $155.00 MR4 Molding 2 $ 305.00 MR5 Molding 11 $160.00 MR6 Molding 4 $ 168.00 MR7 Molding 5 $ 170.00 MR8 Molding 12 $ 210.00 MR9 Molding 10 $ 148.00 MR10 Molding 9 $ 192.00 MR11 Molding 6 $ 185.00 MR12 Molding 7 $ 144.00 MR13 Fabrication 2 $ 140.00 MR14 Fabrication 4 $ 138.00 MR15 Fabrication 6 $ 135.00 MR16 Fabrication 11 $129.00 MR17 Fabrication 1 $198.00 MR18 Fabrication 3 $ 134.00 MR19 Fabrication 12 $ 142.00 MR20 Fabrication 5 $ 136.00 MR21 Fabrication 7 $ 105.00 MR22 Fabrication 9 $ 131.00 MR23 Fabrication 8 $ 132.00 MR24 Fabrication 10 $119.00 MR25 Assembly 4 $ 118.00 MR26 Assembly 7 $ 80.00 MR27 Assembly 9 $ 112.00 MR28 Assembly 12 $ 116.00 MR29 Assembly 11 $ 113.00 MR30 Assembly 2 $ 115.00 MR31 Assembly 6 $112.00 MR32 Assembly 10 $114.00 MR33 Assembly 1 $ 169.00 Average Employee Allison Vanston Department Job Direct Labor-Hours Direct Labor Direct Labor Wage Rate Cost Assembly 10 17.00 $ 20 $ 340 Allison Vanston Assembly 12 3.25 $ 20 $ 65 Cathy Smithson Assembly 1 2.50 $ 20 $ 50 Cathy Smithson Assembly 4 0.75 $ 20 $ 15 Cathy Smithson Assembly 7 2.25 $ 20 $ 45 Cathy Smithson Assembly 8 3.50 $ 20 $ 70 Christopher Tanaka Assembly 1 1.50 $ 20 $ 30 Christopher Tanaka Assembly 7 2.50 $ 20 $ 50 Christopher Tanaka Assembly 8 3.75 $ 20 $ 75 Damien Charles Fabrication 1 4.50 $ 20 $ 90 Damien Charles Fabrication 3 26.50 $ 20 $ 530 Damien Charles Fabrication 6 11.00 $ 20 $ 220 Damien Charles Fabrication 9 27.00 $ 20 $ 540 David Chen Molding 4 1.00 $ 20 $ 20 David Chen Molding 6 12.00 $ 20 $ 240 David Chen Molding 7 2.25 $ 20 $ 45 Emily Evans Assembly 3 30.00 $ 20 $ 600 Emily Evans Assembly 6 20.50 $ 20 $ 410 Emily Evans Assembly 11 8.00 $ 20 $ 160 Emily Evans Assembly 12 5.00 $ 20 $ 100 Ernest Kroyman Assembly 2 15.00 $ 20 $ 300 Ernest Kroyman Assembly 3 31.00 $ 20 $ 620 Ernest Kroyman Assembly 12 3.75 $ 20 $ 75 Jeff Durand: Molding 2 10.50 $ 20 $ 210 Jeff Durand Molding 5 2.75 $ 20 S 55 Jeff Durand Molding 10 10.00 $ 20 S 200 Jeff Durand Molding 12 4.00 $ 20 $ 80 Julia Rhymer Assembly 5 6.50 $ 20 5 130 Julia Rhymer Assembly 11 1.00 $ Juwan Thompson Molding 1 3.25 S Juwan Thompson Molding 3 22.00 $ Juwan Thompson Molding 9 25.00 $ 2222 $ 20 20 $ 65 $ 440 $ 500 Kayla Burke Assembly 5 3.25 $ 20 $ 65 Loretta Hoyt Assembly 2 13.50 $ 20 $ 270 Loretta Hoyt Assembly 6 18.50 $ 20 $ 370 Loretta Hoyt Assembly 9 21.00 $ 20 S 420 Loretta Hoyt Assembly 11 4.00 $ 20 S 80 Madison Rodriquez Fabrication 5 4.50 $ 20 $ 90 Madison Rodriquez Fabrication 7 3.25 $ 20 $ 65 Marshall Toliver Molding 8 0.75 $ 20 S 15 Marshall Toliver Molding 11 6.00 $ 20 $ 120 Mar: Molding 11 6.00 $ 20 $ 120 Mary Jo Hawley Fabrication 2 11.25 $ 20 $ 225 Mary Jo Hawley Fabrication 10 8.00 $ 20 $ 160 Mary Jo Hawley Fabrication 11 11.00 $ 20 $ 220 Mary Jo Hawley Fabrication 12 6.00 $ 20 $ 120 Michael Kershaw Fabrication 4 1.00 $ 20 $ 20 Michael Kershaw Fabrication 8 4.00 $ 20 $ 80 Rebecca Wills Assembly 1 3.25 $ 20 $ 65 Rebecca Wills Assembly 2 12.75 $ 20 $ 255 Rebecca Wills Assembly 26.00 $ 20 $ 520 Rebecca Wills Assembly 8 3.50 $ 20 $ 70 Rebecca Wills Assembly 9 32.00 $ 20 $ 640 Rebecca Wills Assembly 10 7.00 $ 20 $ William Marks Assembly 3 15.50 $ William Marks Assembly 4 1.00 $ William Marks Assembly 7 1.25 $ 2222 20 20 20 $ SSSS 140 $ 310 $ 20 25 Actual Cost Estimated Account Equipment depreciation: Molding Amount Behavior Amount Cost Behavior $ 400,000 Fixed $ 400,000 Fixed Equipment depreciation: Fabrication $ 300,000 Fixed $ 300,000 Fixed Equipment depreciation: Assembly 50,000 Fixed $ 50,000 Fixed Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Fixed Equipment depreciation: Other $ 10,000 Fixed Production suprvision: Molding $ 60,000 Fixed Production supervision: Fabrication $ 65,000 Fixed Production supervision: Assembly S 58,000 Fixed Indirect labor: Maintenance & Repairs S 72,000 Fixed Indirect labor: Materials Handling $ 68,000 Fixed Indirect labor: Engineering S 170,000 Fixed SSSS555 $ 10,000 Fixed $ 63,000 Fixed 68,250 Fixed 60,900 Fixed $ 75,600 Fixed 71,400 Fixed $ 178,500 Fixed Indirect labor: Raw Materials Purchasing $ 93,000 Fixed $ 97,650 Fixed Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Fixed Indirect labor: Packaging & Shipping $ 99,000 Fixed S 103,950 Fixed Indirect materials: Molding $ 18,000 Variable Variable Indirect materials: Fabrication $ 13,000 Variable Variable Indirect materials: Assembly $ 8,000 Variable Variable Utilities: Molding $ 24,000 Variable Variable Utilities: Fabrication S 20,000. Variable Variable Utilities: Assembly $ 6,000 Variable Variable Utilties: Plant lighting and other equipment $ 12,000 Fixed $ 12,240 Fixed Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Fixed Property insurance $ 26,000 Fixed $ 26,520 Fixed Liability insurance $ 18,000 Fixed $ 18,360 Fixed Medical insurance $ 45,000 Fixed $ 51,750 Fixed Workers' compensation insurance 5 13,000 Fixed $ 14,300 Fixed Other salaries: Plant manager $ 165,000 Fixed $ 173,250 Fixed Other salaries: Accounting $ 89,000 Fixed $ 93,450 Fixed Other salaries: Administrative support $ 56,000 Fixed $ 58,800 Fixed Other salaries: Janitorial $ 48,000 Fixed $ 50,400 Fixed Other salaries: Security and grounds maintenance $ 44,000 Fixed $ 46,200 Fixed Property taxes S 18,000 Fixed S 18,540 Fixed Payroll taxes: Direct labor S 79,000 Variable Variable Payroll taxes: Other $ 165.000 Fixed S 173.250 Fixed Total Manufacturing Overhead $2.389,000 5.2.295.690 Total direct labor hours 41,400 42,500 Plantwide predetermined overhead rate VLOOKUP D Di Job Sales Mat 1 $ 2,400.00 2 $ 5,400.00 3 $ 9,000.00 4 $ 1,450.00 5 $ 2,200.00 6 $ 7,000.00 7 $1,700.00 8 $1,600.00 9 $ 8,000.00 10 $4,100.00 11 $3,200.00 12 $ 2,800.00 $4,500.00 $4,000.00 $3,500.00 $3,000.00 $2,500.00 $2,000.00 $1,500.00 Gross Margin Gross Margin by Job Increase Decrease Total $1,000.00 $500.00 S- 4 1 12 5 7 11 10 8 2 693 Total Job