Data Manufacturing overhead $500,000 Selling and administrative overhead $300,000 Assembling Units Processing Orders Supporting Customers Other Manufacturing overhead 50% 35% 5% 10% Selling and administrative overhead 10% 45% 25% 20% Total activity 1,000 250 100 units orders customers OfficeMart orders: Customers 1 customer Orders 4 orders Number of filing cabinets ordered in total 80 units Selling price $595 Direct materials $180 Direct labor $50 Enter a formula into each of the cells marked with a ? below Review Problem: Activity-Based Costing Perform the first stage allocations Assembling Units Processing Orders Supporting Customers Other Total Manufacturing overhead ? ? ? ? ? Selling and administrative overhead ? ? ? ? ? Total cost ? ? ? ? ? Compute the activity rates Activity Cost Pools Total Cost Total Activity Activity Rate Assembling units ? ? units ? per unit Processing orders ? ? orders ? per order Supporting customers ? ? customers ? per customer Compute the overhead cost attributable to the OfficeMart orders Activity Cost Pools Activity Rate Activity ABC Cost Assembling units ? per unit ? units ? Processing orders ? per order ? orders ? Supporting customers ? per customer ? customer ? Determine the customer margin for the OfficeMart orders under Activity-Based Costing Sales ? Costs: Direct materials ? Direct labor ? Unit-related overhead ? Order-related overhead ? Customer-related overhead ? ? Customer margin ? Determine the product margin for the OfficeMart orders under a traditional cost system Manufacturing overhead ? Total activity ? units Manufacturing overhead per unit ? per unit Sales ? Costs: Direct materials ? Direct labor ? Manufacturing overhead ? ? Traditional costing product margin ?
Use the template attached below to complete this activity: (Videos are available in the C4 module on using the ebook + copying worksheets).
Chapter_7_Applying_Excel_template-1.xlsx
Use the data found in the template + use the additional data found in the ebook section, "Applying Excel" (p. 330 print text). All calculations should be performed within the cell using cell referencing/formulas so that I can see how you arrived at your answers! Do not use your calculator and just type in numbers!
You will submit four worksheets within your assignment file:
sheet 1 = original
sheet 2 = requirement 1 quantitative + question responses
sheet 3 = requirement 2 quantitative + question responses
sheet 4 = requirement 3 quantitative + question responses
- In scenario 1, 2, and 3.... copy the original worksheet after inputting your cell formulas and change the values as indicated for each requirement. Name each tab by the question #.
Chapter 7: Applying Excel | |||||
Data | |||||
Manufacturing |
$500,000 | ||||
Selling and administrative overhead | $300,000 | ||||
Assembling Units | Processing Orders | Supporting Customers | Other | ||
Manufacturing overhead | 50% | 35% | 5% | 10% | |
Selling and administrative overhead | 10% | 45% | 25% | 20% | |
Total activity | 1,000 | 250 | 100 | ||
units | orders | customers | |||
OfficeMart orders: | |||||
Customers | 1 | customer | |||
Orders | 4 | orders | |||
Number of filing cabinets ordered in total | 80 | units | |||
Selling price | $595 | ||||
Direct materials | $180 | ||||
Direct labor | $50 | ||||
Enter a formula into each of the cells marked with a ? below | |||||
Review Problem: Activity-Based Costing | |||||
Perform the first stage allocations | |||||
Assembling Units | Processing Orders | Supporting Customers | Other | Total | |
Manufacturing overhead | ? | ? | ? | ? | ? |
Selling and administrative overhead | ? | ? | ? | ? | ? |
Total cost | ? | ? | ? | ? | ? |
Compute the activity rates | |||||
Activity Cost Pools | Total Cost | Total Activity | Activity Rate | ||
Assembling units | ? | ? | units | ? | per unit |
Processing orders | ? | ? | orders | ? | per order |
Supporting customers | ? | ? | customers | ? | per customer |
Compute the overhead cost attributable to the OfficeMart orders | |||||
Activity Cost Pools | Activity Rate | Activity | ABC Cost | ||
Assembling units | ? | per unit | ? | units | ? |
Processing orders | ? | per order | ? | orders | ? |
Supporting customers | ? | per customer | ? | customer | ? |
Determine the customer margin for the OfficeMart orders under Activity-Based Costing | |||||
Sales | ? | ||||
Costs: | |||||
Direct materials | ? | ||||
Direct labor | ? | ||||
Unit-related overhead | ? | ||||
Order-related overhead | ? | ||||
Customer-related overhead | ? | ? | |||
Customer margin | ? | ||||
Determine the product margin for the OfficeMart orders under a traditional cost system | |||||
Manufacturing overhead | ? | ||||
Total activity | ? | units | |||
Manufacturing overhead per unit | ? | per unit | |||
Sales | ? | ||||
Costs: | |||||
Direct materials | ? | ||||
Direct labor | ? | ||||
Manufacturing overhead | ? | ? | |||
Traditional costing product margin | ? |
![](/static/compass_v2/shared-icons/check-mark.png)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 5 images
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![FINANCIAL ACCOUNTING](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781259964947/9781259964947_smallCoverImage.jpg)
![Accounting](https://www.bartleby.com/isbn_cover_images/9781337272094/9781337272094_smallCoverImage.gif)
![Accounting Information Systems](https://www.bartleby.com/isbn_cover_images/9781337619202/9781337619202_smallCoverImage.gif)
![FINANCIAL ACCOUNTING](https://compass-isbn-assets.s3.amazonaws.com/isbn_cover_images/9781259964947/9781259964947_smallCoverImage.jpg)
![Accounting](https://www.bartleby.com/isbn_cover_images/9781337272094/9781337272094_smallCoverImage.gif)
![Accounting Information Systems](https://www.bartleby.com/isbn_cover_images/9781337619202/9781337619202_smallCoverImage.gif)
![Horngren's Cost Accounting: A Managerial Emphasis…](https://www.bartleby.com/isbn_cover_images/9780134475585/9780134475585_smallCoverImage.gif)
![Intermediate Accounting](https://www.bartleby.com/isbn_cover_images/9781259722660/9781259722660_smallCoverImage.gif)
![Financial and Managerial Accounting](https://www.bartleby.com/isbn_cover_images/9781259726705/9781259726705_smallCoverImage.gif)