Please Solve the Table using Excel Formulas

A B C D E F G 1 2 3 $ 4 5 6 7 8 Units produced & sold per year Sales Revenue Prime Cost # of Direct Labor Hours # of Machine hours # of Set Ups # of Inspection runs # of packing orders Product Data Model A 20,000 600,000.00 $ 100,000.00 $ 140,000 20,000 40 600 9,000 Model B 200,000 6,000,000.00 1,000,000.00 300,000 180,000 160 1,400 81,000 9 10 11 12 13 14 15 16 17 18 Manufacturing Overhead Costs: Machining Setup costs Inspection costs Packing Costs Total to to toto 160,000.00 180,000.00 140,000.00 180,000.00 660,000.00 $ D E F G H Model A Model B 1.50 $ 1.50 $ $ 210,000.00 $ 450,000.00 $ $ $ 100,000.00 210,000.00 310,000.00 $ $ $ 1,000,000.00 450,000.00 1,450,000.00 $ 15.50 $ 7.25 A tA tA 30.00 15.50 14.50 A A A 30.00 7.25 22.75 A B 18 19 Simple Cost Allocation 20 Plant-wide MOH Rate 21 22 Allocated MOH 23 24 Direct Materials & Direct Labor 25 Allocated MOH 26 Total Product Costs 27 28 Product Cost (CGS) per unit 29 30 Selling Price per unit 31 CGS per unit 32 Gross Profit per Unit 33 34 35 Activity-Based Costing 36 37 Machining 38 Setup costs 39 Inspection costs 40 Packing Costs 41 42 Allocated MOH 43 44 Direct Materials & Direct Labor 45 Allocated MOH 46 Total Product Costs 47 48 Product Cost (CGS) per unit 49 50 Selling Price per unit 51 CGS per unit 52 Gross Profit per Unit 53 54 Model A Model B Activity Rates $ 0.80 $ 900.00 $ 70.00 $ 2.00 $ $ $ $ 16,000.00 36,000.00 42,000.00 18,000.00 $ $ $ $ 144,000.00 144,000.00 98,000.00 162,000.00 $ 112,000.00 $ 548,000.00 $ $ $ 100,000.00 112,000.00 212,000.00 A tato 1,000,000.00 548,000.00 1,548,000.00 $ 10.60 $ 7.74 $ 30.00 10.60 19.40 A tA tA 30.00 7.74 22.26 B92 fx A B C D E F G H I 4 5 Departmental MOH Rates: $ 1.25 Department 2 Assembly 6 $ 2.75 Department 1 Fabrication Machine Hours 55,000 145,000 7 8 Model A Model B Direct Labor Hours 110,000 330,000 9 O 1 Model A Model B 2 Departmental OH Allocation Direct Materials & Direct Labor Allocated MOH Dept 1 Allocated MOH Dept 2 Total Product Costs Product Cost (CGS) per unit Selling Price per unit CGS per unit Gross Profit per Unit 3 4 5 -6 7 8 -9 O 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 -9 0 1 2 3 4 Compare the results for the simple cost allocation system (plant-wide) with that of an ABC system. Plant wide Departmental Activity Based Model A Selling Price per unit CGS per unit Gross Profit per Unit Model B Selling Price per unit CGS per unit Gross Profit per Unit Which do you think is more accurate and why? What circumstances would favor Ramsey adopting ABC as their allocation method? (List 3 Circumstances )