Finish the excel and answer the question
haojin
hw.xlsxDB7-2 Income Statement
| Kinkaid Cabinets, Inc. | |||||
| Income Statement | |||||
| For the years ending 12/31/X4-X6 In $'s Thousands | |||||
| 20X6 | 20X5 | 20X4 | |||
| Net sales | $ 463,174.2 | $ 320,013.0 | $ 241,677.0 | ||
| Less: Cost of Goods Sold | 337,713.7 | 234,066.0 | 172,978.0 | ||
| Gross Margin | $ 125,460.4 | $ 85,947.0 | $ 68,699.0 | ||
| Less: SG&A | $ 48,781.0 | $ 40,287.3 | $ - 0 | $ 35,049.9 | |
| Operating Income | $ 76,679.4 | $ 45,659.7 | $ 33,649.1 | ||
| Interest expense | $ 470.0 | $ 363.0 | $ 795.0 | ||
| Other income | $ (364.0) | $ (999.0) | $ (755.0) | ||
| Income before taxes | $ 76,573.4 | $ 46,295.7 | $ 33,609.1 | ||
| Provision for income taxes | $ 23,872.0 | $ 13,888.7 | $ 10,082.7 | ||
| Net Income | $ 52,701.4 | $ 32,407.0 | $ 23,526.4 | ||
| Retained Earnings, BOY | $ 82,333.4 | $ 51,106.3 | $ 28,433.0 | ||
| Cash Dividends | $ (1,513.0) | $ (1,180.0) | $ (853.0) | ||
| Retained Earnings, EOY | $ 133,521.8 | $ 82,333.4 | $ 51,106.4 | ||
DB7-2 Product Cost Summaries-x6
| Kinkaid Cabinets Cost and Sales for 20X6 by Product Line | |||||||||
| Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Totals | ||
| KitchenCraft Line | |||||||||
| Units | 1,017,300 | 762,975 | 254,325 | 127,165 | 127,100 | 127,250 | 127,160 | 2,543,275 | |
| Selling Price | $ 98.25 | $ 126.60 | $ 157.26 | $ 183.25 | $ 208.58 | $ 193.91 | $ 234.57 | ||
| Materials | $ 29.64 | $ 40.98 | $ 41.36 | $ 49.88 | $ 47.70 | $ 49.93 | $ 49.03 | ||
| Labor | $ 2.08 | $ 4.27 | $ 4.15 | $ 3.36 | $ 2.82 | $ 3.37 | $ 4.69 | ||
| Overhead | |||||||||
| - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |||
| Totals | |||||||||
| Revenues | $ 99,949,725 | $ 96,592,635 | $ 39,995,150 | $ 23,302,986 | $ 26,510,518 | $ 24,675,048 | $ 29,827,921 | $ 340,853,982 | |
| Materials | $ 30,152,772 | $ 31,266,715 | $ 10,518,882 | $ 6,342,990 | $ 6,062,670 | $ 6,353,593 | $ 6,234,655 | $ 96,932,277 | |
| Labor | $ 2,115,984 | $ 3,257,903 | $ 1,055,449 | $ 427,274 | $ 358,422 | $ 428,833 | $ 596,380 | $ 8,240,245 | |
| Overhead | 131,625,189 | ||||||||
| $ 236,797,711 | |||||||||
| CustomCraft Line | |||||||||
| Units | 246,788 | 186,758 | 65,500 | 31,150 | 34,350 | 33,450 | 66,850 | 664,846 | |
| Selling Price | $ 138.66 | $ 174.20 | $ 191.62 | $ 217.68 | $ 256.46 | $ 235.17 | $ 292.59 | ||
| Materials | $ 48.69 | $ 65.00 | $ 65.59 | $ 78.63 | $ 75.29 | $ 78.71 | $ 77.34 | ||
| Labor | $ 3.64 | $ 7.34 | $ 7.13 | $ 5.77 | $ 4.85 | $ 5.79 | $ 8.05 | ||
| Overhead | |||||||||
| - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |||
| Totals | |||||||||
| Revenues | $ 34,219,624 | $ 32,533,244 | $ 12,551,110 | $ 6,780,732 | $ 8,809,401 | $ 7,866,437 | $ 19,559,642 | $ 122,320,189 | |
| Materials | $ 12,016,108 | $ 12,139,270 | $ 4,296,145 | $ 2,449,325 | $ 2,586,212 | $ 2,632,850 | $ 5,170,179 | $ 41,290,087 | |
| Labor | $ 898,308 | $ 1,370,804 | $ 467,015 | $ 179,736 | $ 166,598 | $ 193,676 | $ 538,143 | $ 3,814,278 | |
| Overhead | 55,811,658 | ||||||||
| $ 100,916,023 | |||||||||
| TOTAL REVENUES | $ 463,174,171 | ||||||||
| TOTAL COGS | $ 337,713,735 | ||||||||
| Gross Margin | $ 125,460,437 | ||||||||
| Gross Margin | ERROR:#REF! | ||||||||
DB7-2 Indirect Mfg Cost Centers
| Kinkaid Manufacturing Support Department Budgets | ||||||
| 20X6 | 20X5 | 20X4 | ||||
| Overhead from Support Depts | $ 55,596.60 | $ 46,775.89 | $ 34,678.29 | |||
| 40 | Material handling | $ 7,783.52 | $ 5,955.79 | $ 4,653.21 | ||
| 41 | Receiving | $ 4,558.92 | $ 3,436.03 | $ 2,445.48 | ||
| 42 | Inspection | $ 1,667.90 | $ 1,282.79 | $ 984.99 | ||
| 43 | Packaging | $ 7,505.54 | $ 6,413.93 | $ 4,789.07 | ||
| 44 | Shipping | $ 11,953.27 | $ 10,308.10 | $ 8,321.43 | ||
| 45 | Maintenance | $ 10,730.14 | $ 9,529.27 | $ 7,234.55 | ||
| 46 | Scheduling | $ 1,056.34 | $ 870.46 | $ 645.34 | ||
| 48 | General Administration | $ 1,167.53 | $ 962.09 | $ 713.27 | ||
| 47 | Engineering support | $ 9,173.44 | $ 8,017.41 | $ 4,890.96 | ||
DB7-2 ABC Driver Info-Mfg-20X6
| Kinkaid Cabinets Manufacturing Support Department ABC Data | |||||||||||
| Material Handling | Rec'g & Shipping | Maintenance | Packaging | Scheduling | Mech. Eng. | Proc. Eng. | Quality Assurance | Gen'l Factory | |||
| Budgeted Costs | $ 7,783,523 | $ 4,558,921 | $ 1,667,898 | $ 7,505,540 | $ 11,953,268 | $ 10,730,143 | $ 1,056,335 | $ 1,167,529 | $ 9,173,438 | ||
| Driver | Moves | Orders Rec'd | Line Hours | Units | Runs Scheduled | Hours Used | Hours Used | Inspec'n Hrs | Labor $'s | ||
| Driver Frequency | 108,000 | 100,850 | 60,960 | 3,210,246 | 1,700 | 16,050 | 29,650 | 62,375 | 12,072,095 | ||
| ABC Rate | $ 72.070 | $ 45.205 | $ 27.361 | $ 2.338 | $ 7,031.334 | $ 668.545 | $ 35.627 | $ 18.718 | 76.0% | ||
| Driver Frequencies | |||||||||||
| 30 | Cutting | Saws | 8,500 | 375 | 8,128 | 0 | 300 | 3,500 | 850 | 225 | $ 1,102,746 |
| 31 | Frame Assembly | Framing Table | 1,500 | 25 | 16,256 | 0 | 200 | 750 | 150 | 150 | $ 1,070,047 |
| 32 | Paint | Automated | 2,000 | 25 | 4,064 | 0 | 200 | 8,500 | 15,000 | 7,000 | $ 820,664 |
| 33 | Drawers Ass'y | Drawer Line | 1,500 | 175 | 4,064 | 0 | 100 | 1,500 | 7,500 | 3,500 | $ 568,013 |
| 34 | Door Assembly | Assembly | 20,000 | 250 | 12,192 | 0 | 100 | 150 | 150 | 7,000 | $ 1,514,593 |
| 35 | Wall Cabinet Assembly | Assembly Line | 15,000 | 30,000 | 4,064 | 1,264,089 | 200 | 150 | 750 | 14,500 | $ 2,110,181 |
| 36 | Floor Cabinet Assembly | Assembly Line | 30,000 | 40,000 | 4,064 | 1,270,759 | 200 | 300 | 1,500 | 11,500 | $ 2,404,236 |
| 37 | Tall Cabinet Assembly | Assembly Line | 13,500 | 20,000 | 4,064 | 321,025 | 200 | 750 | 750 | 17,000 | $ 1,044,126 |
| 38 | Special Assembly | Flex. Cell | 16,000 | 10,000 | 4,064 | 354,374 | 200 | 450 | 3,000 | 1,500 | $ 1,419,918 |
DB7-2 ABC Ind Mfg Cost Template
| Kinkaid Cabinets--ABC Indirect Manufacturing Cost Assignments--20X6 | |||||||||||||
| Material Handling | Rec'g & Shipping | Maintenance | Packaging | Scheduling | Mech. Eng. | Proc. Eng. | Quality Assurance | Gen'l Factory | TOTALS | ||||
| Budgeted Costs | $ 7,783,523 | $ 4,558,921 | $ 1,667,898 | $ 7,505,540 | $ 11,953,268 | $ 10,730,143 | $ 1,056,335 | $ 1,167,529 | $ 9,173,438 | $ 55,596,596 | |||
| Driver | Moves | Orders Rec'd | Line Hours | Units | Runs | Hours Used | Hours Used | Inspec'n Hrs | Labor $'s | ||||
| $ 12,072,095 | |||||||||||||
| ABC Rate | $ 72.07 | $ 45.20 | $ 27.36 | $ 2.34 | $ 7,031.33 | $ 668.54 | $ 35.63 | $ 18.72 | 76.0% | ||||
| Cost Assignments | |||||||||||||
| 30 | Cutting | Saws | $ 8,500 | $ 375 | $ 8,128 | $ - 0 | $ 300 | $ 3,500 | $ 850 | $ 225 | $ 1,102,746.00 | $ 1,124,624 | |
| 31 | Frame Assembly | Framing Table | $ 1,500 | $ 25 | $ 16,256 | $ - 0 | $ 200 | $ 750 | $ 150 | $ 150 | 1,070,047 | $ 19,031 | |
| 32 | Paint | Automated | $ 2,000 | $ 25 | $ 4,064 | $ - 0 | $ 200 | $ 8,500 | $ 15,000 | $ 7,000 | 820,664 | $ 36,789 | |
| 33 | Drawers Ass'y | Drawer Line | $ 1,500 | $ 175 | $ 4,064 | $ - 0 | $ 100 | $ 1,500 | $ 7,500 | $ 3,500 | $ 568,013.00 | $ 586,352 | |
| 34 | Door Assembly | Assembly | $ 20,000 | $ 250 | $ 12,192 | $ - 0 | $ 100 | $ 150 | $ 150 | $ 7,000 | $ 1,514,593.00 | $ 1,554,435 | |
| 35 | Wall Cabinet Assembly | Assembly Line | $ 15,000 | $ 30,000 | $ 4,064 | $ 1,264,089 | $ 200 | $ 150 | $ 750 | $ 14,500 | $ 2,110,181.00 | $ 3,438,934 | |
| 36 | Floor Cabinet Assembly | Assembly Line | $ 30,000 | $ 40,000 | $ 4,064 | $ 1,270,759 | $ 200 | $ 300 | $ 1,500 | $ 11,500 | $ 2,404,236.00 | $ 3,762,559 | |
| 37 | Tall Cabinet Assembly | Assembly Line | $ 13,500 | $ 20,000 | $ 4,064 | $ 321,025 | $ 200 | $ 750 | $ 750 | $ 17,000 | $ 1,044,126.00 | $ 1,421,415 | |
| 38 | Special Assembly | Flex. Cell | $ 16,000 | $ 10,000 | $ 4,064 | $ 354,374 | $ 200 | $ 450 | $ 3,000 | $ 1,500 | $ 1,419,918.00 | $ 1,809,506 | |
| ABM Assignment | $ 108,000 | $ 100,850 | $ 60,960 | $ 3,210,246 | $ 1,700 | $ 16,050 | $ 29,650 | $ 62,375 | $ 10,163,813 | $ 13,753,644 | |||
DB7-2 ABC Mfg CC Templat 20X6
| Kinkaid Cabinets Manufacturng ABC-Based Cost Center Accounts and Balances | |||||||||||
| For the Year Ending 12/31/20X6 | |||||||||||
| Stated in $ Thousands | |||||||||||
| Account | Account Name | Cutting Department | Frame Ass'y | Paint | Drawers Ass'y | Doors-- Ass'y | Wall Cab Ass'y | Floor Cabinet Ass'y | Tall Cabinet Ass'y | Specials--Flex Cells | Kinkaid Total for Year |
| 3200 | Direct labor | $ 1,102.75 | $ 1,070.05 | $ 820.66 | $ 568.01 | $ 1,514.59 | $ 2,110.18 | $ 2,404.24 | $ 1,044.13 | $ 1,419.92 | $ 12,054.52 |
| 3250 | Indirect labor | $ 922.14 | $ 544.29 | $ 933.14 | $ 728.21 | $ 2,554.96 | $ 1,754.06 | $ 2,163.79 | $ 1,122.30 | $ 1,331.87 | $ 12,054.74 |
| 3280 | Supervision | $ 590.22 | $ 155.84 | $ 1,007.08 | $ 218.34 | $ 1,174.19 | $ 1,073.98 | $ 1,461.36 | $ 531.98 | $ 848.62 | $ 7,061.61 |
| 3295 | Overtime | $ 18.25 | $ 35.42 | $ 27.16 | $ 18.80 | $ 50.13 | $ 69.85 | $ 79.58 | $ 34.56 | $ 47.00 | $ 380.76 |
| 3350 | Vacation and Holidays | $ 162.11 | $ 99.11 | $ 177.68 | $ 117.12 | $ 392.68 | $ 368.82 | $ 457.23 | $ 209.18 | $ 345.86 | $ 2,329.78 |
| 3360 | Benefits | $ 217.07 | $ 132.70 | $ 237.91 | $ 156.82 | $ 525.79 | $ 493.85 | $ 612.22 | $ 280.08 | $ 463.10 | $ 3,119.54 |
| 3370 | Payroll taxes | $ 400.48 | $ 240.63 | $ 409.94 | $ 279.39 | $ 1,010.47 | $ 926.75 | $ 1,177.94 | $ 530.03 | $ 882.82 | $ 5,858.44 |
| 3380 | Training | $ 101.95 | $ 8.01 | $ 18.80 | $ 4.60 | $ 174.19 | $ 179.92 | $ 220.33 | $ 85.12 | $ 88.78 | $ 881.69 |
| 3410 | Indirect materials | $ 761.92 | $ 169.20 | $ 4,210.94 | $ 275.80 | $ 1,101.37 | $ 1,064.81 | $ 1,079.15 | $ 464.27 | $ 729.29 | $ 9,856.75 |
| 3420 | Manufacturing supplies | $ 482.91 | $ 200.37 | $ 4,112.25 | $ 201.10 | $ 803.08 | $ 826.14 | $ 786.88 | $ 377.22 | $ 616.88 | $ 8,406.83 |
| 3550 | Electricity | $ 418.52 | $ 129.12 | $ 837.22 | $ 178.12 | $ 466.34 | $ 532.40 | $ 1,101.64 | $ 328.86 | $ 147.66 | $ 4,139.88 |
| 3560 | Other Utilities | $ 268.28 | $ 35.62 | $ 268.55 | $ 109.17 | $ 807.93 | $ 587.48 | $ 944.26 | $ 299.84 | $ 88.78 | $ 3,409.92 |
| 3570 | Outside services | $ 246.82 | $ 57.88 | $ 305.48 | $ 45.97 | $ 228.97 | $ 165.23 | $ 213.58 | $ 77.38 | $ 172.73 | $ 1,514.03 |
| 3650 | Depreciation--Equipment | $ 967.55 | $ 273.82 | $ 5,259.16 | $ 437.27 | $ 1,381.61 | $ 2,970.93 | $ 3,021.34 | $ 717.21 | $ 328.71 | $ 15,357.60 |
| 3660 | Tooling--Equipment | $ 482.91 | $ 155.84 | $ 1,957.09 | $ 114.92 | $ 669.61 | $ 1,266.75 | $ 1,596.25 | $ 280.50 | $ 137.09 | $ 6,660.95 |
| 3680 | Supplies | $ 450.71 | $ 53.43 | $ 2,803.71 | $ 126.41 | $ 267.59 | $ 1,064.81 | $ 1,551.28 | $ 367.55 | $ 112.02 | $ 6,797.51 |
| 3700 | Rent charges | $ 798.26 | $ 193.17 | $ 1,750.08 | $ 308.48 | $ 2,707.46 | $ 2,095.90 | $ 2,131.47 | $ 505.97 | $ 602.30 | $ 11,093.10 |
| 3800 | Insurance and fees | $ 107.31 | $ 40.07 | $ 346.44 | $ 51.71 | $ 294.73 | $ 200.11 | $ 157.38 | $ 106.40 | $ 45.83 | $ 1,349.97 |
| 3900 | Travel | $ 91.22 | $ 17.81 | $ 258.48 | $ 24.71 | $ 109.23 | $ 163.39 | $ 110.16 | $ 66.74 | $ 63.97 | $ 905.72 |
| 3910 | Office supplies | $ 41.85 | $ 5.79 | $ 111.90 | $ 17.24 | $ 62.48 | $ 82.61 | $ 83.18 | $ 87.05 | $ 30.03 | $ 522.13 |
| 3920 | Computers and Info Services | $ 246.82 | $ 17.81 | $ 290.93 | $ 51.71 | $ 213.29 | $ 170.74 | $ 155.13 | $ 174.10 | $ 88.78 | $ 1,409.32 |
| 3925 | Scrap | $ 1,019.47 | $ 333.94 | $ 1,158.14 | $ 545.85 | $ 2,417.33 | $ 1,927.67 | $ 2,203.27 | $ 1,209.04 | $ 2,056.28 | $ 12,870.99 |
| 3930 | Miscellaneous | $ 2,189.23 | $ 929.33 | $ 3,285.16 | $ 1,327.16 | $ 3,611.17 | $ 1,292.08 | $ 1,066.22 | $ 697.91 | $ 1,460.72 | $ 15,858.98 |
| Total Direct Conversion | $ 12,088.73 | $ 4,899.25 | $ 30,587.92 | $ 5,906.92 | $ 22,539.20 | $ 21,388.45 | $ 24,777.88 | $ 9,597.40 | $ 12,109.03 | $ 143,894.78 | |
| ABC Support Department Alloc'n | $ 1,124,624.00 | $ 19,031.00 | $ 36,789.00 | $ 586,352.00 | $ 1,554,435.00 | $ 3,438,933.60 | $ 3,762,558.54 | $ 1,421,414.60 | $ 1,809,506.29 | $ 13,753,644.03 | |
| Total Departmental Budget | $ 1,136,712.73 | $ 23,930.25 | $ 67,376.92 | $ 592,258.92 | $ 1,576,974.20 | $ 3,460,322.06 | $ 3,787,336.42 | $ 1,431,012.00 | $ 1,821,615.31 | $ 199,491.37 | |
| Total Overhead Using ABC | $ 1,135,609.99 | $ 22,860.20 | $ 66,556.25 | $ 591,690.91 | $ 1,575,459.60 | $ 3,458,211.87 | $ 3,784,932.18 | $ 1,429,967.88 | $ 1,820,195.40 | $ 13,885,484.28 | |
DB 7-2 Part Calcs-ABC Template
| Kinkaid Cabinets--Overhead per Part Using ABC--20X6 | |||||||||
| Cost Center | Machine/Step | Type of Production | # in CC | Output Measure | Total Parts Made by Step | Total Dept OH cj: Make sure you remember to multiply by 1000! | ABC Overhead per Part | ||
| 30 | Cutting Department | Saws | 2 | Pieces | 84,436,039 | $ 1,135,610 | $ 0.0134 | ||
| 31 | Frame Assembly | Framing Tble | 4 | Frames | 3,628,186 | $ 22,860 | $ 0.0063 | ||
| 32 | Paint | Auto Line | 1 | Doors Pted | 4,265,854 | $ 66,556 | $ 0.0156 | ||
| 33 | Drawers Ass'y | Assembly | 1 | Drawers | 2,229,033 | $ 591,691 | $ 0.2654 | ||
| 34 | Door Assembly | Assembly | 3 | Doors | 3,562,771 | $ 1,575,460 | $ 0.4422 | ||
| Cost Center | Machine/Step | Type of Production | # in CC | Output Measure | Total Units Made--Kitchen Craft | Total Units Made--Custom Craft | Total Units per FG Department | Total Dept OH | ABC Overhead per FG Unit |
| 35 | Wall Cabinet Assembly | Assembly Line | 1 | Units Made | 1,017,300 | $ 246,788 | 1,264,088 | $ 3,458,212 | $ 2.7357 |
| 36 | Floor Cabinet Assembly | Assembly Line | 1 | Units Made | 1,017,300 | $ 252,258 | 1,269,558 | $ 3,784,932 | $ 2.9813 |
| 37 | Tall Cabinet Assembly | Assembly Line | 1 | Units Made | 254,265 | $ 65,500 | 319,765 | $ 1,429,968 | $ 4.4719 |
| 38 | Special Assembly | Flex. Cell | 1 | Units Made | 254,410 | $ 100,300 | 354,710 | $ 1,820,195 | $ 5.1315 |
DB 7-2 Upstream $ to FG Templ.
| Kinkaid Cabinets ABC Overhead Charge from Upstream Departments to Units and Product Lines | |||||||||||||||
| Kitchen Craft Product Line | Custom Craft Product Line | ||||||||||||||
| Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Totals | |
| Finished Goods Units Made | 1,017,300 | 762,975 | 254,325 | 127,165 | 127,100 | 127,250 | 127,160 | 246,788 | 186,758 | 65,500 | 31,150 | 34,350 | 33,450 | 66,850 | 3,208,121 |
| Cutting Department Parts Calculations: | |||||||||||||||
| Parts per unit | 24 | 24 | 28 | 35 | 36 | 31 | 31 | 24 | 24 | 28 | 35 | 36 | 31 | 31 | |
| Total parts made | - 0 | ||||||||||||||
| ABC Cost per Part | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Frame Assembly Parts Calculations: | |||||||||||||||
| Parts per unit | 1 | 1 | 1 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | |
| Total parts made | - 0 | ||||||||||||||
| ABC Cost per Part | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Painting Department Calculations: | |||||||||||||||
| Parts per unit | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 1 | 2 | 4 | 2 | 2 | 2 | 2 | |
| Total parts made | - 0 | ||||||||||||||
| ABC Cost per Part | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Drawer Assembly Calculations: | |||||||||||||||
| Parts per unit | 0 | 1 | 4 | 0 | 0 | 0 | 0 | 0 | 1 | 4 | 0 | 0 | 0 | 0 | |
| Total parts made | - 0 | ||||||||||||||
| ABC Cost per Part | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Door Assembly Calculations: | |||||||||||||||
| Parts per unit | 1 | 1 | 0 | 2 | 2 | 2 | 2 | 1 | 1 | 0 | 2 | 2 | 2 | 2 | |
| Total parts made | - 0 | ||||||||||||||
| ABC Cost per Part | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Total Upstream Costs to Product Line | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
DB 7-2 FG Costs-ABC Template
| Kinkaid Cabinets Cost of Goods Sold by Product Including ABC Charges for Upstream Departments | |||||||||||||||
| Kitchen Craft Product Line | Custom Craft Product Line | ||||||||||||||
| Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Totals | |
| Finished Goods Units Made | 1,017,300 | 762,975 | 254,325 | 127,165 | 127,100 | 127,250 | 127,160 | 246,788 | 186,758 | 65,500 | 31,150 | 34,350 | 33,450 | 66,850 | 3,208,121 |
| FG Department ABC Overhead | |||||||||||||||
| ABC Cost per Unit | |||||||||||||||
| Total Cost to Product Line | $ - 0 | ||||||||||||||
| Material Cost per Unit | |||||||||||||||
| Labor Cost per Unit | |||||||||||||||
| FG Dept. ABC Overhead per Unit | |||||||||||||||
| Total Cost of Goods Sold by Product Line: | |||||||||||||||
| Total Materials | $ - 0 | ||||||||||||||
| Total Labor | $ - 0 | ||||||||||||||
| Total Upstream Costs-ABC | $ - 0 | ||||||||||||||
| Total FG Dept. Overhead--ABC | $ - 0 | ||||||||||||||
| Total COGS by Product | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
| COGS per Unit | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
DB 7-2 SG&A-ABC $-Rate Template
| Kinkaid Cabinets--ABC SG&A Information for 20X6 | |||||||||||||||||||
| Kitchen Craft Line | Custom Craft Line | ||||||||||||||||||
| CC # | Cost Center Name |
cj: The only things you need to do on this sheet are sum up the total number of drivers by SG&A department and then use this information along with the budget figure to calculate the ABC rate per driver. | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Total Driver Freq. | Rate per Occurrence | |
| Departmental Budget | Driver | ||||||||||||||||||
| 71 | Purchasing | $ 2,271,933 | Orders Placed | 1,934 | 1,451 | 484 | 242 | 242 | 242 | 242 | 469 | 355 | 125 | 59 | 65 | 64 | 126 | ||
| 72 | Finance | $ 2,396,168 | Transactions | 5,395 | 5,214 | 2,159 | 1,258 | 1,431 | 1,332 | 1,610 | 1,847 | 1,756 | 657 | 366 | 475 | 444 | 1,056 | ||
| 73 | Human Resources | $ 2,620,738 | Work Hours | 24,100 | 18,075 | 6,025 | 3,013 | 3,011 | 3,010 | 3,000 | 5,846 | 4,424 | 1,552 | 738 | 830 | 792 | 1,584 | ||
| 74 | Information Systems | $ 3,966,585 | Hours Used | 8,245 | 6,183 | 2,061 | 1,031 | 1,030 | 1,031 | 1,031 | 2,000 | 1,514 | 531 | 252 | 278 | 271 | 542 | ||
| 75 | Executive Administration | $ 2,801,050 | Revenue $'s | $ 99,949,725 | $ 96,592,635 | $ 39,995,150 | $ 23,302,986 | $ 26,510,518 | $ 24,675,048 | $ 29,827,921 | $ 34,219,624 | $ 32,533,244 | $ 12,551,110 | $ 6,780,732 | $ 8,809,401 | $ 7,866,437 | $ 19,559,642 | ||
| 76 | Research and Development | $ 4,265,478 | Hours Used | 6,342 | 4,757 | 1,586 | 793 | 792 | 793 | 793 | 1,539 | 1,164 | 408 | 194 | 214 | 208 | 417 | ||
| 77 | Sales & Marketing | $ 21,649,480 | Units Sold | 1,017,300 | 762,975 | 254,325 | 127,165 | 127,100 | 127,250 | 127,160 | 246,788 | 186,758 | 65,500 | 31,150 | 34,350 | 33,450 | 66,850 | ||
| 78 | Customer Service | $ 8,809,568 | Customer Calls | 21,404 | 16,053 | 5,351 | 2,676 | 2,674 | 2,677 | 2,675 | 5,193 | 3,929 | 1,378 | 655 | 723 | 705 | 1,407 | ||
| Total SG&A Costs | $ 48,781,000 | $ - 0 | $ 101,034,445 | $ 97,407,343 | $ 40,267,141 | $ 23,439,164 | $ 26,646,798 | $ 24,811,383 | $ 29,964,432 | $ 34,483,306 | $ 32,733,144 | $ 12,621,261 | $ 6,814,146 | $ 8,846,336 | $ 7,902,371 | $ 19,631,624 | |||
DB 7-2 SG&A-ABC Cost Template
| Kinkaid Cabinets--ABC SG&A Information for 20X6 | ||||||||||||||||||
| Kitchen Craft Line | Custom Craft Line | |||||||||||||||||
| CC # | Cost Center Name | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Total SG&A Costs Assigned | ||
| Departmental Budget | Driver | |||||||||||||||||
| 71 | Purchasing | $ 2,271,933 | Orders Placed | |||||||||||||||
| 72 | Finance | $ 2,396,168 | Transactions | |||||||||||||||
| 73 | Human Resources | $ 2,620,738 | Work Hours | |||||||||||||||
| 74 | Information Systems | $ 3,966,585 | Hours Used | |||||||||||||||
| 75 | Executive Administration | $ 2,801,050 | Revenue $'s | |||||||||||||||
| 76 | Research and Development | $ 4,265,478 | Hours Used | |||||||||||||||
| 77 | Sales & Marketing | $ 21,649,480 | Units Sold | |||||||||||||||
| 78 | Customer Service | $ 8,809,568 | Customer Calls | |||||||||||||||
| Total SG&A Costs | $ 48,781,000 | |||||||||||||||||
DB 7-2 Product Profit Template
| Kinkaid Product Line Profitability for 20X6 Using ABC Charging Rates for SG&A | ||||||||||||||
| Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Total for Product Line | |||||||
| Kitchen Craft Line | ||||||||||||||
| Units Made and Sold | 1,017,300 | 762,975 | 254,325 | 127,165 | 127,100 | 127,250 | 127,160 | 2,543,275 | ||||||
| Revenue | ||||||||||||||
| Total ABC Manufacturing Cost | ||||||||||||||
| Gross Margin | ||||||||||||||
| SG&A Expenses--ABC Based | ||||||||||||||
| Kitchen Craft Operating Income | ||||||||||||||
| Wall Cab--Med--2 shelves | Floor Cab--Med--2 shelves--1 Drawer | Floor Cab--Med--no shelves--4 drawers | Tall Cab---2 shelves--2 doors | Tall Cab---6 shelves--2 doors | Corner Cab--Med--One Shelf | Spinners & Other Specials | Total for Product Line | |||||||
| Custom Craft Line | ||||||||||||||
| Units Made and Sold | 139 | 174 | 192 | 218 | 256 | 235 | 293 | 1,506 | ||||||
| Revenue | ||||||||||||||
| Total ABC Manufacturing Cost | ||||||||||||||
| Gross Margin | ||||||||||||||
| SG&A Expenses--ABC Based | ||||||||||||||
| Custom Craft Operating Income | ||||||||||||||
| Company Total Operating Income | ||||||||||||||
| $ 43,453,641 | ||||||||||||||
| $ 88,558,006 | $ 88,558,006 |
