this is an excel assignment
ACCT 204 Intro to Accounting II Excel Assignment Instructor: Dr. Jean Zhang Associate Professor Department of Accounting Virginia Commonwealth University Please download the Excel worksheet from Canvas. Required: a) Perform the allocation of overhead costs to the activity cost pools. b) Compute activity rates for the activity cost pools. c) OfficeMart is one of Ferris Corporation's customers. Last year, Office Mart ordered filing cabinets four different times. OfficeMart ordered a total of 80 filing cabinets during the year. Construct a table showing the overhead costs attributable to Office Mart. d) The selling price of a filing cabinet is $595. The cost of direct materials is $180 per filing cabinet, and direct labor is $50 per filing cabinet. What is the customer margin of OfficeMart? Please submit your Excel solution file to Canvas. Ferris Corporation makes a single product-a fire-resistant commercial filing cabinet, that it sells to office furniture distributors. The company has a simple ABC system that it uses for internal decision making. The company has two overhead departments whose costs are as follows: Manufacturing overhead $500,000 Selling and administrative overhead 300,000 Total overhead costs $800,000 The company's ABC system has the following activity cost pools and activity measures: Activity Cost Pool Activity Measure Assembling units Number of units Processing orders Number of orders Supporting customers Number of customers Other Not applicable Costs assigned to the "Other activity cost pool have no activity measure; they consist of organization-sustaining costs. Ferris Corporation distributes the costs of manufacturing overhead and selling and administrative overhead to the activity cost pools based on employee interviews, the results of which are reported below: Distribution of Resource Consumption Across Activity Cost Pools Assembling Processing Supporting Units Orders Customers Other Total Manufacturing overhead 50% 35% 5% 10% 100% Selling and administrative overhead 10% 45% 25% 20% 100% Total activity 1,000 units 250 orders 100 customers 2 E F G H K L M N O Q R S 533 Other 10% 20% A B C D 3 Data 4 Manufacturing overhead $500,000 5 Selling and administrative overhead $300.000 6 Assembling Processing Supporting 7 Units Orders Customers 8 Manufacturing overhead 50% 35% 5% 9 Selling and administrative overhead 10% 45% 25% 10 Total activity 1,000 250 100 11 units orders customers 12 13 OfficeMart orders 14 Customers 1 customer 15 Orders 4 orders 16 Number of filing cabinets ordered in total 80 units 17 Selling price $595 18 Direct materials $180 19 Direct labor $50 20 21 Enter a formula into each of the cells marked with a ? below 22 Review Problem: Activity-Based Costing 23 24 a) Perform the first stage allocations Assembling Processing Supporting 25 Units Orders Customers 26 Manufacturing overhead ? ? ? 27 Selling and administrative overhead ? ? 28 Total cost ? ? ? 29 30 b) Compute the activity rates 31 Activity Cost Pools Total Cost Total Activity 32 Assembling units ? ? units 33 Processing orders ? ? orders student form Other ? Total ? 2 ? ? ? ? Activity Rate ? per unit ? per order G H . 1 K L M. N o o Q R S T ? units A B D E F 23 24 a) Perform the first stage allocations Assembling Processing Supporting 25 Units Orders Customers Other Total 26 Manufacturing overhead ? ? ? ? ? ? ? 27 Selling and administrative overhead 2 ? ? ? ? ? 28 Total cost ? ? ? ? ? 29 30 b) Compute the activity rates 31 Activity Cost Pools Total Cost Total Activity Activity Rate 32 Assembling units 2 ? per unit 33 Processing orders ? ? orders ? per order 34 Supporting customers ? ? ? customers ? per customer 35 36 c) Compute the overhead cost attributable to the Office Mart orders 37 Activity Cost Pools Activity Rate Activity ABC Cost 38 Assembling units ? per unit ? units ? 39 Processing orders ? per order ? orders ? 40 Supporting customers ? per customer ? customer ? 41 42 d) Determine the customer margin for the OfficeMart orders under Activity-Based Costing 43 Sales ? 44 Costs: 45 Direct materials ? 46 Direct labor ? 47 Unit-related overhead ? 48 | Order-related overhead 2 49 Customer-related overhead ? 50 Customer margin ? ? 51
