To complete this activity, you will need to have Excel installed on your computer. This exercise...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
To complete this activity, you will need to have Excel installed on your computer. This exercise requires you to work in Excel and answer questions in Connect. You will read a brief scenario and then download an Excel file that you will need to complete the requirements in Parts 1 through 5 of this exercise. In Part 6, your instructor may require you to upload your completed workbook. Some of the requirements include brief video tutorials on using Excel functions. After viewing the tutorials, you will then use what you learned to work directly in Excel to answer the required questions in Connect. [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: Number of units produced and sold Selling price per unit Direct materials per unit Direct labor cost per unit Direct labor-hours per unit Standard 20,000 $ 150 Advanced 10,000 $ 200 $ 40 $ 60 $ 30 $ 30 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 workbook titled, "ExcelAnalytics_Comparing Traditionaland Activity Based ProductCosting_Template which you will use to complete the requirements in this exercise. 1. Navigate to the "Plantwide Approach" tab of the Excel workbook. Complete requirements 1a. through 1f. as follows by working directly in Excel and entering your answers in the tabs below. a. In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation. Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? b. What is the plantwide overhead rate calculated in Cell F4? c. Within cells B11 through D17, create the appropriate formulas () to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What are the formulas used to correctly calculate the direct materials, direct labor, and manufacturing overhead for the Standard model? d. What are the gross margins for the Standard model calculated in Cell B17and the Advanced model calculated in Cell C17. e. Within the "Plantwide Approach tab of the Excel workbook, use Charts to create a pie chart that shows the percent of total manufacturing overhead cost allocated to each product using the plantwide approach. f. Refer to the pie chart you created in 1e. What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Required 1A Required 1B Required 1C Required 1D Required 1F In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation. Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? Direct labor-hours Plantwide overhead rate =(B3*B7)+(C3*C7) =F2/F3 =(B3*B7)-(C3*C7) =F2*F3 =(B3 B7)/(C3 C7) =F2+F3 =(B3/B7)*(C3/C7) =F2-F3 a. In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? b. What is the plantwide overhead rate calculated in Cell F4? c. Within cells B11 through D17, create the appropriate formulas () to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What are the formulas used to correctly calculate the direct materials, direct labor, and manufacturing overhead for the Standard model? d. What are the gross margins for the Standard model calculated in Cell B17and the Advanced model calculated in Cell C17. e. Within the "Plantwide Approach tab of the Excel workbook, use Charts to create a pie chart that shows the percent of total manufacturing overhead cost allocated to each product using the plantwide approach. f. Refer to the pie chart you created in 1e. What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Required 1A Required 1B Required 1C Required 1D Required 1F What is the plantwide overhead rate calculated in Cell F4? lote: Round your answer to 2 decimal places. antwide overhead rate per direct labor-hour To complete this activity, you will need to have Excel installed on your computer. This exercise requires you to work in Excel and answer questions in Connect. You will read a brief scenario and then download an Excel file that you will need to complete the requirements in Parts 1 through 5 of this exercise. In Part 6, your instructor may require you to upload your completed workbook. Some of the requirements include brief video tutorials on using Excel functions. After viewing the tutorials, you will then use what you learned to work directly in Excel to answer the required questions in Connect. [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: Number of units produced and sold Selling price per unit Direct materials per unit Direct labor cost per unit Direct labor-hours per unit Standard 20,000 $ 150 Advanced 10,000 $ 200 $ 40 $ 60 $ 30 $ 30 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 workbook titled, "ExcelAnalytics_Comparing Traditionaland Activity Based ProductCosting_Template which you will use to complete the requirements in this exercise. 1. Navigate to the "Plantwide Approach" tab of the Excel workbook. Complete requirements 1a. through 1f. as follows by working directly in Excel and entering your answers in the tabs below. a. In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation. Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? b. What is the plantwide overhead rate calculated in Cell F4? c. Within cells B11 through D17, create the appropriate formulas () to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What are the formulas used to correctly calculate the direct materials, direct labor, and manufacturing overhead for the Standard model? d. What are the gross margins for the Standard model calculated in Cell B17and the Advanced model calculated in Cell C17. e. Within the "Plantwide Approach tab of the Excel workbook, use Charts to create a pie chart that shows the percent of total manufacturing overhead cost allocated to each product using the plantwide approach. f. Refer to the pie chart you created in 1e. What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Required 1A Required 1B Required 1C Required 1D Required 1F In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation. Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? Direct labor-hours Plantwide overhead rate =(B3*B7)+(C3*C7) =F2/F3 =(B3*B7)-(C3*C7) =F2*F3 =(B3 B7)/(C3 C7) =F2+F3 =(B3/B7)*(C3/C7) =F2-F3 a. In Cell F3, create a formula that calculates the direct labor-hours for the denominator of the plantwide overhead rate calculation Then, in Cell F4, create a formula that calculates the plantwide overhead rate. What are the formulas used to correctly calculate these two amounts? b. What is the plantwide overhead rate calculated in Cell F4? c. Within cells B11 through D17, create the appropriate formulas () to compute the gross margins for the Standard and Advanced models as well as the company as a whole. What are the formulas used to correctly calculate the direct materials, direct labor, and manufacturing overhead for the Standard model? d. What are the gross margins for the Standard model calculated in Cell B17and the Advanced model calculated in Cell C17. e. Within the "Plantwide Approach tab of the Excel workbook, use Charts to create a pie chart that shows the percent of total manufacturing overhead cost allocated to each product using the plantwide approach. f. Refer to the pie chart you created in 1e. What percent of the total manufacturing overhead cost is allocated to the Standard model using the plantwide approach? The Advanced model? Save your work in Excel and enter your answers to requirements 1a. through 1f. in the tabs below. Required 1A Required 1B Required 1C Required 1D Required 1F What is the plantwide overhead rate calculated in Cell F4? lote: Round your answer to 2 decimal places. antwide overhead rate per direct labor-hour
Expert Answer:
Related Book For
Statistics For Business And Economics
ISBN: 9781292227085
13th Global Edition
Authors: Terry Sincich James Mcclave, P. George Benson
Posted Date:
Students also viewed these accounting questions
-
3. Consider the following reaction and kinetic data: 2 HgCl2(aq) + C2O4 (aq) 2 Cl-(aq) + Hg2Cl2(aq) + 2CO2(g) Expt. # [HgC](M) [CO2] (M) Initial rate (Ms) 1 0.10 0.15 1.8 x 10-5 2 0.10 0.30 7.2 x...
-
An 02 series deep groove ball bearing supports a radial load of 2.16 kN and an axial load of 2.02 kN. The life of the bearing, based upon 95 % survival is to be 2.19 kHrs with the inner ring rotating...
-
Which statement does NOT reflect a way that journals require authors to disclose potential or actual conflicts of interest? Group of answer choices Require researcher's Federal tax statements Require...
-
Why do you think that neurons cannot simply be transplanted from other areas of the body? Neurons cannot be transplanted from other areas of the body:
-
On January 1, McNeil Company borrows $100,000 cash by signing a four-year, 9% installment note. The note requires four equal payments consisting of accrued interest and principal on December 31 for...
-
On June 14, 1988, Thomas John Heck Jr. executed a note promising to pay Paul D. Heck \($51,000\) at 7 percent interest compounded annually. The note contains the following payment terms: Perpetual 90...
-
Plevin Company ended its fiscal year on July 31, 2014. The companys adjusted trial balance as of the end of its fiscal year is shown below. Instructions (a) Prepare the closing entries using page...
-
Assets Cash Accounts receivable Inventory Plant and equipment, net Joel de Paris, Incorporated Balance Sheet Beginning Balance $ 135,000 342,000 Ending Balance $ 135,000 485,000 Investment in...
-
The total cost to prepare 30 pounds of fruit salad is $89.95.If the fruit salad is sold in 4-ounce portions, what is the target price per portion based on a target cost percentage of 26%?
-
Why are financial statements important to a business and how do they help businesses determine current financial health?
-
Compare results of the model for Adobe (ADBE) value and stock price to the actual value and stock price.What explains the differences?
-
1.Based on the Book Financial Markets and Institutions 20th Edition of Cabrera, What are the Different Private Non-Bank Financial Institutions available in the Philippines? What are their major...
-
500 kgmole/hr mutilcomponent mixture with 40% n-hexane, 30% heptanes and 30% n-octane is to be fed to a distillation column. The recovery of n-hexane in the top product is 98% and for the n-heptane...
-
Describe the complexity of managing multinational corporations and the risks they face when conducting international deals that are different from domestic deals?
-
A Corporate bond currently yield 8.00%. Municipal bound with the same risk, maturity, and liquidity currently yield 5.5%. At what tax rate would investors be indifferent between the two bonds? 28.75%...
-
Explain why each of the following is either a private good or a public good: traffic lights, in line skates, a city park, a chicken salad sandwich, a tennis racket, national defense, a coastal...
-
According to the Earth Policy Institute (July 2014), 65% of the world's solar energy cells are manufactured in China. Consider a random sample of five solar energy cells and let x represent the...
-
For two events, A and B, P(A) = .4, P(B) = .2, and P(A | B) = .6: a. Find P(A B). b. Find P(B | A).
-
Consider the following probability distribution: a. Find ( and (2. b. Find the sampling distribution of the sample mean x for a random sample of n = 2 measurements from this distribution. c. Show...
-
Compare and contrast the terms public interest and public benefit in the context of public financial management.
-
Discuss M1, M2 and M3 monetary aggregates.
-
Compare and contrast direct and indirect taxes.
Study smarter with the SolutionInn App