PLEASE ANSWER IN EXCEL WITH FORMULAS INCLUDED

Managerial Accounting - Prof. Beg Chapter 5: ABC Free work cells Answer cells Quinnipiac Padfolio Club makes two products (Padfolios and Business Cards) for students to use during interview season. The company has two production departments (Forming and Assembly) and operates at capacity. The controller has asked you to compare plant-wide, department, and activity-based cost allocation. Direct Material and Direct Labor is incurred in both production departments (Forming & Assembly) and is directly traced to the two product lines (Padfolios & Business Cards). There are also two types of overhead (Setup & Supervision) incurred in the departments. Overhead costs need to be allocated to the two product lines so the Quinnipiac Padfolio Club can calculate the costs of each product line. Budgeted information for the year ended December 31, 2017 is as follows: $ $ Forming Department Direct Materials Direct Manufacturing Labor Overhead Costs Setup Supervision Padfolios Business Cards 25,000 $ 24,000 $ 41,000 $ 17,000 $ Total 49,000 58,000 $ $ 22,500 20,500 Total $ $ Assembly Department Direct Materials Direct Manufacturing Labor Overhead Costs Setup Supervision Padfolios Business Cards 4,500 $ 20,000 $ 17,500 $ 22,000 $ 24,500 39,500 $ $ 50,000 24,000 Other information about the departments' activities follows: Setup costs in each department vary with the number of batches processed in each department. The budgeted number of batches for each product line in each department is as follows: 24 Other information about the departments' activities follows: Setup costs in each department vary with the number of batches processed in each department. The budgeted number of batches for each product line 25 in each department is as follows: 26 27 Setup Batches Padfolios Business Cards Total 28 Forming Department 28 120 148 29 Assembly Department 43 90 133 30 31 Supervision Costs: Vary with direct manufacturing labor costs in each department. 32 1 Calculate the total budgeted cost of each product line (Padfolios vs. Business Cards) based on a single plant-wide overhead rate, if total overhead is allocated based on total direct costs. 33 34 (Direct costs should be traced to the products as identified above. Indirect costs should be allocated to the products based on a single overhead allocation rate.) 35 36 OH Pool Description OH Pool Amount Budgeted Overhead Rate OH Rate 1 Alloc Base Amount Allocation Rate Alloc Base Description Total Costs Assigned Padfolios Business Cards Total 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Total Direct Costs 45 46 47 48 Total Direct Costs 49 50 51 52 53 54 55 Total Overhead Costs Total Manufacturing Costs Calculate the budgeted cost of each product line (Padfolios vs. Business Cards) based on departmental overhead rates, where: 2 (a) Forming department overhead costs are allocated based on direct manufacturing labor costs of the forming department, and (b) Assembly department overhead costs are allocated based on total direct costs of the assembly department. 56 57 (Direct costs should be traced to the products as identified above. Indirect costs should be allocated to the products based on two overhead allocation rates -- one for each department.) 58 59 OH Pool Description OH Pool Amount Alloc Base Amount Allocation Rate Alloc Base Description Budgeted Overhead Rate OH Rate 1 OH Rate 2 60 61 62 63 64 65 66 67 68 69 70 71 Total Costs Assigned Padfolios Business Cards Total Total Direct Costs 1 Total Direct Costs 2 13 14 75 26 27 28 Total Overhead Costs Total Manufacturing Costs 3 Calculate the budgeted cost of each product line (Padfolios vs. Business Cards) if Quinnipiac Padfolio Club allocates overhead costs in each department using activity-based costing. (i.e. Each type of overhead in each department is allocated using the relevant activity in the department.) 79 30 (Direct costs should be traced to the products as identified above. Indirect costs should be allocated to the products based on four overhead allocation rates -- one for each activity.) 31 32 OH Pool Description OH Pool Amount Alloc Base Amount Allocation Rate Alloc Base Description 33 34 35 36 37 88 39 90 Budgeted Overhead Rate OH Rate 1 OH Rate 2 OH Rate 3 OH Rate 4 Total Costs Assigned Padfolios Business Cards Total 91 Total Direct Costs 92 3 94 95 96 27 38 un overhead allocation rates -- one for each activity.) OH Pool Description OH Pool Amount Alloc Base Amount Allocation Rate Alloc Base Description Budgeted Overhead Rate OH Rate 1 OH Rate 2 OH Rate 3 OH Rate 4 Total Costs Assigned Padfolios Business Cards Total Total Direct Costs Total Overhead Costs Total Manufacturing Costs