Pictures 2-5 are the excel template that it says you will need in the first picture.

Excel Analytics 07-1 (Static) Comparing Traditional and Activity-Based Product Costing (ROI) (L07-1,7-3, 7-4, 7-5) 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 Number of units produced and sold Selling price per unit Direct materials per unit Direct labor cost per unit Direct labor-hours per unit 20,000 150 $ $ Advanced 10, eee $ 200 $ 60 $ 30 1.50 30 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 a brief tutorial on Charts in Excel Excel Analytics 07-1 (Static) Part 1 Required: 1. Go to the tab titled "Plantwide Approach" a. Create a formula that calculates the direct labor-hours in the denominator of the plantwide overhead rate (cell F3). Also, create a formula that calculates the plantwide overhead rate (cell F4). What formulas correctly calculate these two amounts? b. What is the plantwide overhead rate? c. Create the appropriate formulas (within cells Bil through 017) to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What formulas correctly calculate the direct materials, direct labor and manufacturing overhead for the Standard model? G 1 A B D Background Information Plantwide Overhead Rate 2 Standard Advanced Manufacturing overhead costa) $ 1,346,250 3 Number of units produced and sold 20,000 10,000 Direct labor hours (b) 4 Selling price per unit $ 150 S 200 5 Direct materials per unit Plantwide overhead rate (a) (b) s 40 S 60 6 Direct labor cost per unit S 30 $ 30 7 Direct labor hours per unit 1.50 1.50 8 9 Gross Margin Analysis 10 Standard Advanced Total 11 Sales 12 Cost of goods sold: 13 Direct materials 14 Direct labor 15 Manufacturing overhead 16 Cost of goods sold 17 Gross margin 18 19 20 21 22 23 Charts Plantwide Approach Activity-Based Approach Activity Based Cost Analysis Cont Allocation CM) . E H Activity Rates Assemble and Pack: Manufacturing overhead cost (a) Direct labor hours (b) Activity rate (8) + (b) $ 292,500 45,000 $ Machining: Manufacturing overhead cost (a) Machine hours (6) Activity rate() (b) 440,000 40,000 $ F10 Activity rate= (b) A B D 1 Background Information 2 Standard Advanced 3 Number of units produced and sold 20,000 10,000 4. Direct labor-hours per unit 1.50 1.50 5. Machine-hours per unit 1 2 6 Average customer order size 400 50 7 Number of setups per customer order 1 1 8 Number of setup hours per setup 1 3 9 10 Product Margin Analysis 11 Standard Advanced Total 12 Sales $ S 13 Costs: 14 Direct materials S 15 Direct labor $ 16 Assemble and pack 17 Machining 18 Order processing 19 Setups 20 Total cost 21 Product margin 22 23 24 25 26 27 28 Charts Plantwide Approach Activity-Based Approach Activity Based Cost Analysis Order Processing Manufacturing overhead cost(a) Number of customer orders (b) Activity rate ()+(b) 256,250 250 $ Setups: Manufacturing overhead cost(a) Setup hours (b) Activity rate (a)+(b) 357,500 650 Cost Allocation Com E F G B C Standard Advanced D Total 1 0% 2 3 4 . Activity Assemble and pack Machining Order processing Setups 0% 0% 0% 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Charts Plantwide Approach Activity-Based Approach Activity-Bases Cost Analysis Cost Allocation Com... B E B Standard C Advanced 1 2 Plantwide approach: Total overhead cost allocated 3 Activity-based approach: Total overhead cost allocated 4 D Total $ $ - Nico 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Activity-Based Approach Activity-Based Cost Analysis Cost Allocation Comparison Required information Required: 1. Go to the tab titled "Plantwide Approach a. Create a formula that calculates the direct labor-hours in the denominator of the plantwide overhead rate (cell F3). Also create a formula that calculates the plantwide overhead rate (cell F4). What formulas correctly calculate these two amounts? b. What is the plantwide overhead rate? c. Create the appropriate formulas (within cells B11 through D17) to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What formulas correctly calculate the direct materials, direct labor and manufacturing overhead for the Standard model? d. What are the gross margins for the Standard model (cell B17) and the Advanced model (cell C17)? e. Using Charts, create a pie chart that shows the percent of total manufacturing overhead cost allocated to each product using the plantwide approach. (Your instructor may require you to upload this file in Part 6). f. What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? 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 organization sustaining costs or unused capacity costs) Manufacturing Activity Measure Activity Measure Assemble and pack Direct labor hours $ 292,500 Machining Machine-hours Order processing Number of customer orders 256,25e Setup hours $1,346,258 ces Overhead 400,eee Setups 357.500 The production manager also provided the following additional information with respect to the company's two products. Machine-hours per unit Average customer order size (in units) Number of setups per customer order Number of setup hours per setup Standard 1.6 400 1 1 Advanced 2.0 56 1 3 Reg 1A Req 1B Req 1C Reg 1D Req 1F What is the plantwide overhead rate? (Round your answer to 2 decimal places.) Plantwide overhead rate per direct labor-hour ) nces Req 1A Reg 1B Req 10 Req 10 Req IF What are the gross margins for the Standard model (cell B17) and the Advanced model (cell C17)? Standard Advanced Model Model Gross margin Reg 1A Reg 1B Req 1C Req 10 Req 1F What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? Percent of total overhead cost allocated % % Standard model Advanced model