Excel Analytics 07-1 (Static) Comparing Traditional and Activity-Based Product Costing (ROI) [LO7-1, 7-3, 7-4, 7-5]

Skip to question

[The following information applies to the questions displayed below.] Cary Company manufactures two models of industrial componentsa 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 a a brief tutorial on Charts in Excel.

rev: 05_07_2020_QC_CS-210952

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 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? Carys production manager has suggested replacing the companys current cost system with an activity-based costing system that assigns all of the companys manufacturing overhead costs to four activity cost pools as follows (the company does not have any organization-sustaining costs or unused capacity costs):

Activity Measure	Activity Measure	Manufacturing Overhead
Assemble and pack	Direct labor hours	$	292,500
Machining	Machine-hours		440,000
Order processing	Number of customer orders		256,250
Setups	Setup hours		357,500
		$	1,346,250

The production manager also provided the following additional information with respect to the companys two products:

	Standard		Advanced
Machine-hours per unit	1.0		2.0
Average customer order size (in units)	400		50
Number of setups per customer order	1		1
Number of setup hours per setup	1		3

Reg 1A Reg 1B Reg 10 Req 1D Reg 1F 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? Direct labor-hours =(B3*B7)+(C3*C7) =(B3*B7)-(C3*C7) =(B3*B7(C3*C7) =(B3/B7)*(C3/07) Plantwide overhead rate =F2/F3 =F2*F3 =F2+F3 =F2-F3 Reg 1A Reg 1B Reg 10 Req 1D Reg 1F What is the plantwide overhead rate? (Round your answer to 2 decimal places.) Plantwide overhead rate per direct labor-hour Reg 1A Reg 1B Reg 1C Req 1D Reg 1F 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? Direct materials =B3+B5 =B3-B5 =B3*B5 =B3/B5 Direct labor =B3+B6 =B3-B6 =B3*B6 =B3/B6 Manufacturing overhead =B3+B7*F4 =B3-B7*F4 =B3*B7*F4 =B3/B7*F4 Reg 1A Reg 1B Reg 10 Req 1D Req 1F What are the gross margins for the Standard model (cell B17) and the Advanced model (cell C17)? Standard Model Advanced Model Gross margin Reg 1A Reg 1B Reg 10 Req 1D 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 67 % 33% Standard model Advanced model Reg 1A Reg 1B Reg 10 Req 1D Reg 1F What is the plantwide overhead rate? (Round your answer to 2 decimal places.) Plantwide overhead rate per direct labor-hour Reg 1A Reg 1B Reg 1C Req 1D Reg 1F 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? Direct materials =B3+B5 =B3-B5 =B3*B5 =B3/B5 Direct labor =B3+B6 =B3-B6 =B3*B6 =B3/B6 Manufacturing overhead =B3+B7*F4 =B3-B7*F4 =B3*B7*F4 =B3/B7*F4 Reg 1A Reg 1B Reg 10 Req 1D Req 1F What are the gross margins for the Standard model (cell B17) and the Advanced model (cell C17)? Standard Model Advanced Model Gross margin Reg 1A Reg 1B Reg 10 Req 1D 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 67 % 33% Standard model Advanced model