A 1 FACT SET 2 1 Company produces 12 items across 4 work centers. Individual items can only be produced in one work center each. 2 3 The "Work Center Data" tab shows relevant data related to the items they produce, which work centers they are produced in, and the rates at which they are run. 4 3 The "Expense Data" tab shows selected expense accounts from the company's P&L. The company runs all work centers 5 days per week, 50 weeks per year. They run two 8 hour shifts per day. When performing any analyses 4 5 using practical capacity per work center, they estimate 80% uptime. 6 5 Direct Labor is incurred at the following split: WC 1 = 40%, WC 2 = 15%, WC 3 = 30%, WC 4 = 15% 6 The plant manager supports all 4 work centers evenly. Finance and their supplies also are considered to support all 4 work centers evenly. 7 8 7 Production Supplies are used in the work centers as follows: WC 1 = 30%, WC 2 = 25%, WC 3 = 35%, WC 4 = 10% 9 8 Equipment maintenance is spread based on the estimated machine hours for the year in each work center. 10 9 Fixed assets are assigned and tracked the work center level. See the Other Data tab for further detail. 11 10 The company has one OH rate per work center, based on machine hours. 12 11 Rent is allocated based on square footage. See the Other Data tab for further detail. 13 12 Round all calculations to the nearest dollar, EXCEPT for the OH rates. Round those to the nearest 2 decimal places. 14 15 DELIVERABLES 16 1 Fill in the missing data on the Work Center Data tab for any column that has a heading but is missing data. 17 2 Determine the proper classification of each expense item on the Expense Data tab. (three options can be found in the column heading) 18 10 3 Develop Work Center OH rates based on budgeted '23 expenses. Three sets of OH rates should be developed, one using prior year actual hours, one using '23 budgeted hours, one using practical capacity. Keep in mind the classification you assigned in step 2. Scenario Work Center Data Expense Data Other Data D A B E F G H 2023 Plan K Planned L M N Work 2022 Actual 2023 Planned Practical Work 2022 Qty/ 2022 % Qty 2023 Plan Efficiency 2023 Qty/ 1 Center Hours Hours Capacity Item Center Hour Qty Change Qty Gain Hour 2 A 3,052 3,083 1 3 B 2,800 2,716 2 456 C 2,789 2,554 3 D 3,082 3,178 4 5 7 11,722 6 7 8 9 10 11 12 13 14 15 16 8 9 10 11 12 ABCD ABCOABCO 100 101,023 0% 101,023 0% 100 75 74,993 -5% 71,243 0% 75 200 251,323 15% 289,021 5% 210 50 24,857 3% 25,603 5% 53 110 113,294 6% 120,092 0% 110 80 81,046 -8% 74,562 0% 80 180 267,594 -20% 214,075 5% 189 D 65 55,679 15% 64,031 8% 70 95 96,108 -3% 93,225 0% 95 85 66,895 6% 70,909 0% 85 225 10,235 5% 10,747 5% 236 D 45 77,767 7% 83,211 4% 47 AutoSave Off File Paste 2 - OH Rate Development (1) Home Insert Draw Page Layout Formulas X Calibri 11 A A BIU A Font Clipboard 1 A B D Data Revie Wrap Merge Alignment Estimated Classification 2022 % Change 2023 Expense (COGS, OH or SGA) Amount for 2023 Amount Plant Manager Salary SGA 125,000 3% 128,750 Direct Labor COGS 1,249,356 5% 1,311,824 Finance Salaries SGA 400,000 2% 408,000 Production Supplies COGS 274,326 10% 301,759 Office Supplies SGA 57,923 2% 59,081 Rent - Manufacturing OH 300,000 3% 309,000 Equipment Maintenace OH 602,379 4% 626,474 Depreciation OH 270,359 -10% 243,572 3,388,460 L C D Work Alignment Number Work L Center Sq Ft Asset # Center Acq Value Years Prior Prior Accum 22 Depr 23 Depr 2 1 75,000 A1 1 200,000 6.5 185,712 14,286 3 2 60,000 A2 1 50,000 6.5 46,430 3,572 45 3 80,000 A3 1 125,000 6.5 116,071 8,929 4 105,000 A4 2 100,000 3.5 50,001 14,286 14,286 6 A5 2 35,000 3.5 17,500 5,000 5,000 320,000 A6 3 20,000 2.5 7,143 2,857 2,857 3 A7 3 30,000 2,5 10,715 4,286 4,286 A8 3 85,000 2.5 30,358 12,143 12,143 A9 3 20,000 2.5 7,143 2,857 2,857 A10 4 1,100,000 0.5 78,572 157,143 157,143 A11 4 300,000 0.5 21,429 42,857 42,857 A12 15,000 0.5 1,072 2,143 2,143 270,359 243,572 K