i need the third picture the ABC model for pen co case

this is the third picture again to make it more clear

these are what it needs to look like

i am stuck on the bottom part and need the formulas to find the level of the total activity

E Merge & Center $ ipboard Font Alignment N E F Sales Material Costs Direct Labor Overhead @ 300% Total Operating Income Return on Sales $ $ $ $ B C D Traditional Income Statement Blue Black Red 75,000 $ 60,000 $ 13,950 $ 25,000 $ 20,000 $ 4,680 $ 10,000 $ 8,000 $ 1,800 $ 30,000 $ 24,000 $ 5,400 $ 10,000 $ 8,000 $ 2,070 $ 13.3% 13.3% 14.8% Purple 1,650 $ 550 $ 200 $ 600 $ 300 $ 18.2% Total 150,600 50,230 20,000 60,000 20,370 13.5% am st VV +5+5 Merge & Center $ % Clipboard Font Alignment Nur E8 fx '12 E 000 C D Direct Costs and Activity Cost Drivers Blue Black Red 50,000 40,000 9,000 1.50 $ 1.50 $. 1.55 $ 0.50 0.52 $ 0.02 0.02 0.1 Purple 1,000 1.65 0.55 0.02 0.1 Total 100,000 6.20 $ $ 0.50 $ 3 Units Produced 4 Unit Price 5 Materials per Unit 6 DLH per Unit 7 Machine Hours per Unit 8 Production Runs 9 Setup Time per Run 10 Total Setup Time (Hours) 11 Parts Administration 2.07 2,000 10,000 150 15 526 200 50 228 4 Paste B I U A Merge & Center - $ % 9.898 Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number C4 X fx 2 First Stage Allocation Percentages Production Runs Record Maintenance Setup Time Machining Total 0% 0% 3 4 Indirect Labor 5 Computer Systems 6 Machinery 7 Maintenance 8 Energy 10 First Stage Allocation Production Runs Record Maintenance Setup Time Machining $ 11 12 Indirect Labor 13 Computer Systems 14 Machinery 15 Maintenance 16 Energy 17 Total Total 28,000 10,000 8,000 4,000 2,000 52,000 19 Classic Pen Company Activity Based Costing 20 Second Stage Allocation 29 Classic Pen Company Segment Income Statements 30 UI LIIA mpany: Developing an ABC Model Expense Category Expense Indirect Labor Fringe Benefits Computer Systems Machinery Maintenance Energy Total $20,000 16,000 10,000 8,000 4,000 2.000 $60,000 She determined that the fringe benefits were 40% of labor would thus represent just a percentage markup to be applied ges. Dempsey interviewed department heads in charge of indi ain activities accounted for their work. About half of indirect labo andling production runs. This included scheduling production or eleasing materials for the production run, first-item inspection perfor changed over, and some scrap loss at the beginning of each runt Another 40% of indirect labor was required just for the physical char another The time to change over to BLACK pens was relatively she previous color did not have to be completely eliminated from the mach 11 == Calibri BIU AA A 2 Wrap Text Merge & Center Custom $ % 9.68 E Alignment Font Number Classic Pen Company Activity Based Costing First Stage Allocation Percentages Production Runs Record Maintenance Setup Time Machining Total Indirect Labor Computer Systems Machinery Maintenance Energy 0% os First Stage Allocation Production Runs Setup Time Record Maintenance Machining Indirect Labor Computer Systems Machinery Maintenance Energy Total Total 28,000 10,000 8,000 4,000 2,000 52,000 $ Classic Pen Company Activity Based Costing Second Stage Allocation Classic Pen Company Segment Income Statements Exhibit 1 Exhibit 2 ABC Costing Ready Type here to search oto e Classic Pen Company Activity Based Couting First Stage Allocation Percentages Production Record Runs Setup Time Maintenance 50 804 Machin 404 Indirect Labor Computer Systems Machinery Maintenance Energy 1001 1001 100% First Stage Allocation Production Runs Setup Time Maintenance 14 000 $ 11,200 $ 2.800 8.000 $ Machining Indirect labor Computer Systems Machinery Maintenance Energy Total 28.000 10,000 8,000 4 000 $ $ 4000 $ 2000 14,000 $ 22,000 $ 11.200 4,800 Purple Unape Vrage Cont 5573 4,655 1,760 Classic Pen Company Activity Based Corting Second Stage Allocation Blue Usage Cost Usare 50 S 7,333 50S 200 $ 4,259 50 $ 1$ 1,200 S 5,000 $ 7.000 4,000 $ Total MOHS 19,792 $ 125 Activities Production Runs Setup Time Record Maintenance Machining Total Activity Level Activity Rate 150 $ 5265 2129 4 $ 1.200.00 10,000 $ 1.40 Cost Pools 22.000 11.200 4.800 14,000 52.000 1,002 Cort 7,333 1.06 1200 5.600 55.98 1200 45 1S 100 5 1300 140 900 $ $ 1260 12,568 4,122 Total Blue Purple Per Unit Te Overall erUnit P Per Unit Total 1.000 40,000 Units $ 1.650 $ Sales Classic Pen Company Segment Income Statements Black Total Per Unit Per Unit Total 50.000 9,000 75 000 $ 150 $ 60 000 $ 1.56 S 11,950 25,000 $ 0505 20.000 $ 0 52 5 14 000 $ 0.28 S 11.200 $ 0.28 5 2.520 19.7925 038 S 15.198 S 55,7925 116 $ 46,398 5 2.23 $ 16,2085 0.34 $ 13,602 S 10.68 5505 150 $ 050 $ 0.28 $ 0.40 $ 118 $ 0.32 $ S Materials Costs Direct Labor (Incl. Fringe Manufacturing Overhead Total Manufacturing Costs Operating income Total 100.000 150.600 50.230 25,000 $2.000 110.230 20,370 165 $ 0.55 $ 0.28 $ 4.12 $ 4.95 $ 13.30 $ 1.51 $ 0.50 $ 0.265 0.52 $ 130 $ 0.20 S 2005 4,122 5 4.9525 7,102) S S E Merge & Center $ ipboard Font Alignment N E F Sales Material Costs Direct Labor Overhead @ 300% Total Operating Income Return on Sales $ $ $ $ B C D Traditional Income Statement Blue Black Red 75,000 $ 60,000 $ 13,950 $ 25,000 $ 20,000 $ 4,680 $ 10,000 $ 8,000 $ 1,800 $ 30,000 $ 24,000 $ 5,400 $ 10,000 $ 8,000 $ 2,070 $ 13.3% 13.3% 14.8% Purple 1,650 $ 550 $ 200 $ 600 $ 300 $ 18.2% Total 150,600 50,230 20,000 60,000 20,370 13.5% am st VV +5+5 Merge & Center $ % Clipboard Font Alignment Nur E8 fx '12 E 000 C D Direct Costs and Activity Cost Drivers Blue Black Red 50,000 40,000 9,000 1.50 $ 1.50 $. 1.55 $ 0.50 0.52 $ 0.02 0.02 0.1 Purple 1,000 1.65 0.55 0.02 0.1 Total 100,000 6.20 $ $ 0.50 $ 3 Units Produced 4 Unit Price 5 Materials per Unit 6 DLH per Unit 7 Machine Hours per Unit 8 Production Runs 9 Setup Time per Run 10 Total Setup Time (Hours) 11 Parts Administration 2.07 2,000 10,000 150 15 526 200 50 228 4 Paste B I U A Merge & Center - $ % 9.898 Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number C4 X fx 2 First Stage Allocation Percentages Production Runs Record Maintenance Setup Time Machining Total 0% 0% 3 4 Indirect Labor 5 Computer Systems 6 Machinery 7 Maintenance 8 Energy 10 First Stage Allocation Production Runs Record Maintenance Setup Time Machining $ 11 12 Indirect Labor 13 Computer Systems 14 Machinery 15 Maintenance 16 Energy 17 Total Total 28,000 10,000 8,000 4,000 2,000 52,000 19 Classic Pen Company Activity Based Costing 20 Second Stage Allocation 29 Classic Pen Company Segment Income Statements 30 UI LIIA mpany: Developing an ABC Model Expense Category Expense Indirect Labor Fringe Benefits Computer Systems Machinery Maintenance Energy Total $20,000 16,000 10,000 8,000 4,000 2.000 $60,000 She determined that the fringe benefits were 40% of labor would thus represent just a percentage markup to be applied ges. Dempsey interviewed department heads in charge of indi ain activities accounted for their work. About half of indirect labo andling production runs. This included scheduling production or eleasing materials for the production run, first-item inspection perfor changed over, and some scrap loss at the beginning of each runt Another 40% of indirect labor was required just for the physical char another The time to change over to BLACK pens was relatively she previous color did not have to be completely eliminated from the mach 11 == Calibri BIU AA A 2 Wrap Text Merge & Center Custom $ % 9.68 E Alignment Font Number Classic Pen Company Activity Based Costing First Stage Allocation Percentages Production Runs Record Maintenance Setup Time Machining Total Indirect Labor Computer Systems Machinery Maintenance Energy 0% os First Stage Allocation Production Runs Setup Time Record Maintenance Machining Indirect Labor Computer Systems Machinery Maintenance Energy Total Total 28,000 10,000 8,000 4,000 2,000 52,000 $ Classic Pen Company Activity Based Costing Second Stage Allocation Classic Pen Company Segment Income Statements Exhibit 1 Exhibit 2 ABC Costing Ready Type here to search oto e Classic Pen Company Activity Based Couting First Stage Allocation Percentages Production Record Runs Setup Time Maintenance 50 804 Machin 404 Indirect Labor Computer Systems Machinery Maintenance Energy 1001 1001 100% First Stage Allocation Production Runs Setup Time Maintenance 14 000 $ 11,200 $ 2.800 8.000 $ Machining Indirect labor Computer Systems Machinery Maintenance Energy Total 28.000 10,000 8,000 4 000 $ $ 4000 $ 2000 14,000 $ 22,000 $ 11.200 4,800 Purple Unape Vrage Cont 5573 4,655 1,760 Classic Pen Company Activity Based Corting Second Stage Allocation Blue Usage Cost Usare 50 S 7,333 50S 200 $ 4,259 50 $ 1$ 1,200 S 5,000 $ 7.000 4,000 $ Total MOHS 19,792 $ 125 Activities Production Runs Setup Time Record Maintenance Machining Total Activity Level Activity Rate 150 $ 5265 2129 4 $ 1.200.00 10,000 $ 1.40 Cost Pools 22.000 11.200 4.800 14,000 52.000 1,002 Cort 7,333 1.06 1200 5.600 55.98 1200 45 1S 100 5 1300 140 900 $ $ 1260 12,568 4,122 Total Blue Purple Per Unit Te Overall erUnit P Per Unit Total 1.000 40,000 Units $ 1.650 $ Sales Classic Pen Company Segment Income Statements Black Total Per Unit Per Unit Total 50.000 9,000 75 000 $ 150 $ 60 000 $ 1.56 S 11,950 25,000 $ 0505 20.000 $ 0 52 5 14 000 $ 0.28 S 11.200 $ 0.28 5 2.520 19.7925 038 S 15.198 S 55,7925 116 $ 46,398 5 2.23 $ 16,2085 0.34 $ 13,602 S 10.68 5505 150 $ 050 $ 0.28 $ 0.40 $ 118 $ 0.32 $ S Materials Costs Direct Labor (Incl. Fringe Manufacturing Overhead Total Manufacturing Costs Operating income Total 100.000 150.600 50.230 25,000 $2.000 110.230 20,370 165 $ 0.55 $ 0.28 $ 4.12 $ 4.95 $ 13.30 $ 1.51 $ 0.50 $ 0.265 0.52 $ 130 $ 0.20 S 2005 4,122 5 4.9525 7,102) S S