O Required information (The following information applies to the questions displayed below.) Cary Company manufactures two models of industrial components-a Standard model and an Advanced Model. It has provided the following information with respect to these two products: Standard Advanced Number of units produced and sold 20,000 10,000 Selling price per unit $ 150 $ 200 Direct materials per unit $.40 $ 60 Direct labor cost per unit $ 30 $ 30 Direct labor-hours per unit 1.50 1.50 The company considers all of its manufacturing overhead costs ($1,346,250) to be fixed and it uses plantwide manufacturing overhead cost allocation based on direct labor-hours. Click here to download the Excel template, which you will use to answer the questions that follow Click here for.o.o. bref tutorial on Charts.in Excel Cary's production manager has suggested replacing the company's current cost system with an activity-based costing system that assigns all of the company's manufacturing overhead costs to four activity cost pools as follows the company does not have any facility-level costs); Activity Measure Assemble and pack Machining Order processing Setups Activity Measure Direct labor hours Machine-hours Number of customer orders Setup hours Manufacturing Overhead $ 292,500 440,000 256,250 357,500 $ 1,346,250 Cary's production manager has suggested replacing the company's current cost system with an activity-based costing system that assigns all of the company's manufacturing overhead costs to four activity cost pools as follows the company does not have any facility-level costs): Activity Measure Assemble and pack Machining Order processing Setup Activity Measure Direct labor hours Machine-hours Number of customer orders Setup hours Manufacturing Overhead $ 292,500 440,000 256,250 357,500 $ 1,346,250 The production manager also provided the following additional information with respect to the company's two products: Standard 1.0 400 Machine-hours per unit Average customer order size (in units) Number of setups per customer order Number of setup hours per setup Advanced 2.0 50 1 20. Go to the tab titled "Activity Based Approach. Within the Activity Rates section of this tab, create formulas that calculate the activity rates in cells G5, G10, G15, and G20 2b. What is the activity rate for each of the four activities? (Round your answers to 2 decimal places.) Activity Rate Activity Cost Pool Assemble and pack Machining Order processing Setups per direct labor hour per machine-hour per customer order per setup hour 3. Within the tab titled "Activity Based Approach." go to the section called "Product Margin Analysis." Create formulas (within cells B16 through C19) that calculate the amounts of overhead cost that should be allocated from each activity cost pool to each product. Also (in cells B21 through D21), create the formulas that are needed to compute the product margins for each product and the company as a whole (Hint: The product margin equals each product's sales minus its direct materials, direct labor, and activity-based cost assignments) a. What formulas correctly calculate the cost assignments from the Order Processing and Setups activities to the Advanced model? b. How much overhead cost is assigned from each of the four activities to the Standard and Advanced models? c. What is the product margin for the Standard model? The Advanced model? Complete this question by entering your answers in the tabs below. Reg 3A Req 38 Reg 3C What formulas correctly calculate the cost assignments from the Order Processing and Setups activities to the Advanced model? ces Order Processing C'CHG15 C3 C8615 -C3-C6G15 C3/06G15 Setups C3%C6 C8'G20 (C3+C67C8*020 C3/C6 C8-620 -CMC6C8020 pod RA Req38 3. Within the tab titled "Activity Based Approach" go to the section called "Product Margin Analysis." Create formulas (within cells B16 through C19) that calculate the amounts of overhead cost that should be allocated from each activity cost pool to each product. Also (in cells B21 through D21), create the formulas that are needed to compute the product margins for each product and the company as a whole (Hint: The product margin equals each product's sales minus its direct materials, direct labor, and activity-based cost assignments). a. What formulas correctly calculate the cost assignments from the Order Processing and Setups activities to the Advanced model? b. How much overhead cost is assigned from each of the four activities to the Standard and Advanced models? c. What is the product margin for the Standard model? The Advanced model? Complete this question by entering your answers in the tabs below. Reg 3A Reg 38 Reg 3C How much overhead cost is assigned from each of the four activities to the Standard and Advanced models? Standard Model Advanced Model Assemble and pack Machining Order processing Setups 3. Within the tab titled "Activity Based Approach, go to the section called "Product Margin Analysis." Create formulas (within cells 816 through C19) that calculate the amounts of overhead cost that should be allocated from each activity cost pool to each product. Also (in cells B21 through D21), create the formulas that are needed to compute the product margins for each product and the company as a whole (Hint: The product margin equals each product's sales minus its direct materials, direct labor, and activity-based cost assignments) a. What formulas correctly calculate the cost assignments from the Order Processing and Setups activities to the Advanced model? b. How much overhead cost is assigned from each of the four activities to the Standard and Advanced models? c. What is the product margin for the Standard model? The Advanced model? Complete this question by entering your answers in the tabs below. Reg 3A Reg 38 Reg 30 What is the product margin for the Standard model? The Advanced model? Standard Model Advanced Model Product margin 3. Within the tab titled "Activity Based Approach, go to the section called "Product Margin Analysis." Create formulas (within cells 816 through C19) that calculate the amounts of overhead cost that should be allocated from each activity cost pool to each product. Also (in cells B21 through D21), create the formulas that are needed to compute the product margins for each product and the company as a whole (Hint: The product margin equals each product's sales minus its direct materials, direct labor, and activity-based cost assignments) a. What formulas correctly calculate the cost assignments from the Order Processing and Setups activities to the Advanced model? b. How much overhead cost is assigned from each of the four activities to the Standard and Advanced models? c. What is the product margin for the Standard model? The Advanced model? Complete this question by entering your answers in the tabs below. Reg 3A Reg 38 Reg 30 What is the product margin for the Standard model? The Advanced model? Standard Model Advanced Model Product margin