The total overhead equals to sum of three overhead account

how can I use excel to figure out what is thebest cost driver to use?

Acct 242 Project 1 Arizona Drilling Company (ADC) specializes in geothermal systems and water wells. In geothermal construction cementing is the process used to make sure that a well is firmly protected. The current costing system is a traditional costing system to account for each of the individual wells. Specifically, (1) The direct material is mainly composed of the pipe (both rigid and flexible) and connective components. The direct material cost is charged to the individual jobs. (2) The direct labor is the cost of all labor used on each job (both supervisory and hourly drillers), including the salary, bonus, fringe benefits, and any overtime premium. (3) The overhead cost is recorded under the following accounts: fuel cost, rigging-related cost and the cost of mud and concrete to set the wells and geothermal units. Overhead is charged to jobs on the basis of the actual depth of each well. The rate is currently $5.50 per linear foot drilled. (4) The monthly actual overhead costs are recorded to the appropriate overhead accounts (Fuel, Mud and Rigging Supplies). ADC's overhead cost c o m p r i s e s about 60% of the total cost. Under the current costing system, overhead cost is primarily allocated to the individual wells based on the actual well depth. As a result, the current overhead allocation method is over-simplified. In the last several years, over-allocated overhead has been substantial. As a result, Sandy Spudder, the owner (known fondly as \"Spud\"), is concerned about the overhead allocation method and believes ADC may be losing jobs to competitors. Therefore, Spud decided to investigate improving the accuracy of the current costing system by engaging your team of consultants. There are five possible cost drivers that may be associated with changes in overhead costs. These cost drivers include the number of wells, the total distance driven for the job, the weight of the materials pipe, mud and supplies, the total drilling depth, and the ton-miles. (40 months of overhead and driver use are attached) Your team is required to prepare an assessment of single pool/single driver allocation now used and any recommendations for future overhead cost allocations. Overhead\tAccounts Number\tof Wells 18 34 31 26 28 41 37 38 42 19 18 33 29 31 23 27 31 35 34 17 20 35 35 33 37 43 41 34 33 19 3 15 21 24 22 27 28 27 26 28 Distance 1574 2898 2457 1919 1982 2836 2606 3240 3900 2159 1113 2051 2547 2638 1842 2340 1910 2018 2315 1287 2725 4700 4690 4260 4880 5735 5815 4430 3960 2260 485 2570 3035 3000 2745 3760 3755 4405 3380 4625 Weight 1078 2064 1912 1648 2100 2613 2460 2453 2990 1293 865 1439 1358 1454 1360 1513 1739 2021 1976 1174 1387 2481 2437 2356 2586 2924 2827 2318 2353 1577 192 1984 2655 3043 3656 4604 4830 4335 4402 4340 Depth 31819 58549 55212 47346 53604 73987 69004 70923 81379 38487 27428 48684 43217 43917 36279 42293 48247 53542 53053 27383 40648 74962 74457 65377 74398 88501 82697 67495 68219 38004 7049 42228 65197 73771 71526 85405 92269 83872 83409 89135 Ton-Miles 94713 178028 153561 123646 145325 182578 174594 217348 287355 145787 53959 89653 117759 122244 110196 131797 107294 118724 137418 90455 191753 333434 326856 306573 341338 389499 400816 300920 278510 184797 31118 339253 372239 371278 453465 616432 647494 692815 557021 709685 Total Overhead 121271 275256 230251 229382 229869 326486 318771 331822 443623 272881 119142 259586 227875 240007 183164 210390 252519 275807 290430 178702 146382 299127 280100 256656 303995 339361 344641 315754 325135 317178 45582 291981 239864 439057 223592 398425 432081 379958 608845 726166 Rig\tSupplies 67723 125087 108974 87380 97496 161602 145273 148349 156782 74784 71047 137281 125382 128222 102950 95964 126864 141730 144540 71509 83235 142659 121914 118370 129621 153067 160772 135356 117232 80605 16186 60643 72093 80284 76560 118891 113896 116510 96517 108970 Mud 39170 71580 69380 52730 64720 93130 83560 86300 98730 47240 33700 59210 52860 53210 34340 51530 58700 65170 64600 33800 54210 97940 90890 79330 90570 107580 98050 82090 83420 46470 6430 49870 73270 89780 91220 99760 12600 102050 103610 105730 Fuel 14378 78589 51897 89272 67653 71754 89938 97173 188111 150857 14395 63095 49633 58575 45874 62896 66955 68907 81290 73393 8937 58528 67296 58956 83804 78714 85819 98308 124483 190103 22966 181468 94501 268993 55812 179774 305585 161398 408718 511466