Texas plant is a window manufactured plant. Analyze the profitability of it using the ABC accounting in EXCEL
Exhibit 1. Projected Product Line Income Statements -- Texas Plant Standard Percent Architectural Percent Total Revenue $81,000,000 100.0% $40,750,000 100.0% $121,750,000 Variable Costs Wood 31,129,313 38.4% 11,264,688 27.6% 42,394,000 Glass 15,012,422 18.5% 9,530,078 23.4% 24,542,500 Cladding, Hardware, etc. 6,750,000 8.3% 7,250,000 17.8% 14,000,000 Total Materials Cost 52,891,734 65.3% 28,044,766 68.8% 80,936,500 Wages, skilled carpenters Wages, helpers Total wages 13,253,625 6,834,375 20,088,000 16.4% 8.4% 24.8% 2,454,375 1,265,625 3,720,000 6.0% 3.1% 9.1% 15,708,000 8,100,000 23,808,000 2.0% Variable Overhead Total Variable Costs 1,635,267 74,615,001 898,067 32,662,832 2.2% 80.2% 2,533,333 107,277,833 6,384,999 6,182,367 8,087,168 1,144,883 19.8% 2.8% 14,472,167 7,327,250 7.6% 2,700,000 3.3% Contribution Margin Fixed Production Overhead Marketing and G&A Costs Accounting Profit Capital Charge Economic Profit 500,000 1.2% 3,200,000 -2,497,368 -3.1% 6,442,285 15.8% 3,944,917 5,128,500 -$1,183,583 4A. Sales and Administrative Cost and Resource Drivers Sales and Administrative Costs Full Time Equivalent Employees Sales account representatives 6.00 Order entry clerks 4.00 Shipping and Packing Salaries 6.00 Billing Associate Salaries 5.00 General administrative staff salaries 12.00 Packing materials and supplies Annual furniture and fixtures depreciation Building rent, heat, and other occupancy costs Total 33.00 Space Used 9.00% 7.00% 46.00% 18.00% 20,00% 100.00% Total Costs $900,000 165,000 300,000 235,000 1,058,000 21,000 205,000 316,000 $3,200,000 Total 4. Bill Accounts 235,000 5. General Admin 1,058,000 4B. Sales and Administrative Resources Used by Activities Activities and Resources 1. Call on 2. Process Customers Orders Salaries and Wages 900,000 165,000 Packing Supplies and Boxes Equipment Depreciation 37,273 24,848 Occupancy Costs 28,440 22, 120 Total Activity Cost $965,713 $211,968 4C. Activity Drivers Used by Products Activity Driver Standard Usage 1. # Customer Contact Hours 17,500 2. # Orders 13,300 3. # of Boxes Packed 27,000 4. # of Billing transactions 16,000 5. # of Billing transactions 16,000 3. Pack & Ship 300,000 21,000 37,273 145,360 $503,633 31,061 56,880 $322,941 74,545 63,200 $1,195,745 2,658,000 21,000 205,000 316,000 $3,200,000 Architectural Usage 16,000 7,700 26,000 6,000 6,000 Total Usage 33,500 21,000 53,000 22,000 22,000 5A. Resources Used by Activities 1. Cut & Resources Used Plane Skilled Workers 30 Hours Per Year 60,000 Unskilled Workers 30 Hours Per Year 60,000 Total Labor Hours 120,000 Square Footage 20,000 Supervisory/Support Staff 13 Total Machinery Cost 1,050,000 Depreciation Expense 78,750 * Based on annual depreciation rate 7.5% 2. Form & Shape 40 80,000 40 80,000 160,000 12,000 16 840,000 63,000 3. Insert Glass 15 30,000 15 30,000 60,000 15,000 8 900,000 67,500 4. Attach 5. Apply 6. Install Hardware Primer Cladding 30 25 30 60,000 50,000 60,000 30 25 30 60,000 50,000 60,000 120,000 100,000 120,000 15,000 13,000 9,000 6 6 3 2,100,000 675,000 750,000 157,500 50,625 56,250 7. Inspection 30 60,000 30 60,000 120,000 4,000 4 8. Stamp & Bar Code Total 25 225 50,000 450,000 25 225 50,000 450,000 100,000 900,000 12,000 100,000 4 60 875,000 7,190,000 65,625 539,250 5B. Resource Drivers Direct Labor Variable Overhead Supervisor & support salaries Occupancy costs Machinery Depreciation Other Depreciation Traced directly to process steps by direct labor hours Assigned to process steps based on direct labor hours Assigned to process steps based on percentage of total supervisor/support headcount Assigned to process steps based on percentage of square footage occupied Traced directly to process steps Assigned to process steps based on percentage of total supervisor/support headcount 5C. Activity Drivers Used by Products 1. Number of saw cuts 2. Skill saw time 3. Glass insert time units 4. Hardware pieces attached 5. Paint gun hours 6. Proportion of windows clad 7. Number of windows inspected 8. Number of windows produced Total 2,520,000 470,000 840,000 940,000 185,000 106,000 18,500 320,000 Per Unit Standard 6.0 1.0 2.0 2.0 0.5 0.3 0.05 1.0 Architectural 18.0 4.0 6.0 8.0 1.0 0.5 0.1 1.0 Exhibit 1. Projected Product Line Income Statements -- Texas Plant Standard Percent Architectural Percent Total Revenue $81,000,000 100.0% $40,750,000 100.0% $121,750,000 Variable Costs Wood 31,129,313 38.4% 11,264,688 27.6% 42,394,000 Glass 15,012,422 18.5% 9,530,078 23.4% 24,542,500 Cladding, Hardware, etc. 6,750,000 8.3% 7,250,000 17.8% 14,000,000 Total Materials Cost 52,891,734 65.3% 28,044,766 68.8% 80,936,500 Wages, skilled carpenters Wages, helpers Total wages 13,253,625 6,834,375 20,088,000 16.4% 8.4% 24.8% 2,454,375 1,265,625 3,720,000 6.0% 3.1% 9.1% 15,708,000 8,100,000 23,808,000 2.0% Variable Overhead Total Variable Costs 1,635,267 74,615,001 898,067 32,662,832 2.2% 80.2% 2,533,333 107,277,833 6,384,999 6,182,367 8,087,168 1,144,883 19.8% 2.8% 14,472,167 7,327,250 7.6% 2,700,000 3.3% Contribution Margin Fixed Production Overhead Marketing and G&A Costs Accounting Profit Capital Charge Economic Profit 500,000 1.2% 3,200,000 -2,497,368 -3.1% 6,442,285 15.8% 3,944,917 5,128,500 -$1,183,583 4A. Sales and Administrative Cost and Resource Drivers Sales and Administrative Costs Full Time Equivalent Employees Sales account representatives 6.00 Order entry clerks 4.00 Shipping and Packing Salaries 6.00 Billing Associate Salaries 5.00 General administrative staff salaries 12.00 Packing materials and supplies Annual furniture and fixtures depreciation Building rent, heat, and other occupancy costs Total 33.00 Space Used 9.00% 7.00% 46.00% 18.00% 20,00% 100.00% Total Costs $900,000 165,000 300,000 235,000 1,058,000 21,000 205,000 316,000 $3,200,000 Total 4. Bill Accounts 235,000 5. General Admin 1,058,000 4B. Sales and Administrative Resources Used by Activities Activities and Resources 1. Call on 2. Process Customers Orders Salaries and Wages 900,000 165,000 Packing Supplies and Boxes Equipment Depreciation 37,273 24,848 Occupancy Costs 28,440 22, 120 Total Activity Cost $965,713 $211,968 4C. Activity Drivers Used by Products Activity Driver Standard Usage 1. # Customer Contact Hours 17,500 2. # Orders 13,300 3. # of Boxes Packed 27,000 4. # of Billing transactions 16,000 5. # of Billing transactions 16,000 3. Pack & Ship 300,000 21,000 37,273 145,360 $503,633 31,061 56,880 $322,941 74,545 63,200 $1,195,745 2,658,000 21,000 205,000 316,000 $3,200,000 Architectural Usage 16,000 7,700 26,000 6,000 6,000 Total Usage 33,500 21,000 53,000 22,000 22,000 5A. Resources Used by Activities 1. Cut & Resources Used Plane Skilled Workers 30 Hours Per Year 60,000 Unskilled Workers 30 Hours Per Year 60,000 Total Labor Hours 120,000 Square Footage 20,000 Supervisory/Support Staff 13 Total Machinery Cost 1,050,000 Depreciation Expense 78,750 * Based on annual depreciation rate 7.5% 2. Form & Shape 40 80,000 40 80,000 160,000 12,000 16 840,000 63,000 3. Insert Glass 15 30,000 15 30,000 60,000 15,000 8 900,000 67,500 4. Attach 5. Apply 6. Install Hardware Primer Cladding 30 25 30 60,000 50,000 60,000 30 25 30 60,000 50,000 60,000 120,000 100,000 120,000 15,000 13,000 9,000 6 6 3 2,100,000 675,000 750,000 157,500 50,625 56,250 7. Inspection 30 60,000 30 60,000 120,000 4,000 4 8. Stamp & Bar Code Total 25 225 50,000 450,000 25 225 50,000 450,000 100,000 900,000 12,000 100,000 4 60 875,000 7,190,000 65,625 539,250 5B. Resource Drivers Direct Labor Variable Overhead Supervisor & support salaries Occupancy costs Machinery Depreciation Other Depreciation Traced directly to process steps by direct labor hours Assigned to process steps based on direct labor hours Assigned to process steps based on percentage of total supervisor/support headcount Assigned to process steps based on percentage of square footage occupied Traced directly to process steps Assigned to process steps based on percentage of total supervisor/support headcount 5C. Activity Drivers Used by Products 1. Number of saw cuts 2. Skill saw time 3. Glass insert time units 4. Hardware pieces attached 5. Paint gun hours 6. Proportion of windows clad 7. Number of windows inspected 8. Number of windows produced Total 2,520,000 470,000 840,000 940,000 185,000 106,000 18,500 320,000 Per Unit Standard 6.0 1.0 2.0 2.0 0.5 0.3 0.05 1.0 Architectural 18.0 4.0 6.0 8.0 1.0 0.5 0.1 1.0
