on suon your textbook. Project 20: Operational Efficiencies Hoover Transportation, Inc., is a large distribution company located in Denver, Colorado. The com pany is currently seeking to gain operational efficiencies in its supply chain by reducing the number of transportation carriers it is using to outsource. Operational efficiencies for Hoover Transportation, Inc., suggest that reducing the number of carriers from the Denver distribution center to warehouses in the selected states will lead to reduced costs. Brian Hoover, the CEO of Hoover Transportation, requests the number of carriers transporting products from its Denver distribution center to wholesal ers in Arizona, Arkansas, lowa, Missouri Montana, Oklahoma Oregon, and Washington be reduced from the current five carriers to two carriers. Carrier selection should be based on the assumptions that all environmental factors are equal and historical cost trends will continue. Review the historical data from the past several years to determine your recommendation for the top two carriers that Hoover Transportation should continue to use. 1. Analyze the last 24 months of Hoover's Transportation carrier transactions found in the AYK20_ Data.xlsx data file. 2. Create a report detailing your recommendation for the top two carriers with which Hoover Transportation should continue to do business. Be sure to use PivotTables and PivotCharts in your report. A few questions to get you started include: - What is the average cost per carrier? What are the total shipping costs per state? What are the total shipping weights per state? - What are the average shipping costs per pound? What is the average cost per carrier? Ve Nube F Involce Number Date Carrier Name Destination State Involce Amount Shipping Cost por Pound 30.88 OH CO OR AX NY PA 3 $0 3097 $1.02 $107 $1.05 $1.06 $0.00 $0 5097 10001 10002 TOY 10004 1000 1000 1000 10000 1009 10010 11 TO012 10013 10014 10015 10010 10017 1001B 10019 10020 1002 10022 10023 10024 100% 100 1002 10090 100 1000 10031 0 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 02 Jan 11 03.11 04.10 051 0-11 02 Jan 11 On 11 09 Jan 11 10:11 11 Jan 11 12-Jan-11 13 Jan 11 14.Jan-11 15 Jant 15 11 17 J-11 18 Jan 11 1911 20 Jan 11 21 Jan 11 Soro Shoping Suto Shipping Tasting Time 24 Sum 24 Sevin Count Us 24 Seven Soro Supping Sun Shu Sun Shipping Tosing line Count on Us Sure Shippine Count on 24 SO Count on Count on Us Testing Time Count on US Testing Time 24 Seven Count on Suto Ship Count on us Suro Shipping Thor Tomorrow There Tomomow Thore TOW $2.13 $1,054 $3.987 $5,870 $4,575 $1,587 $ 100 $4,578 $7.000 $8 875 $/01 $9.999 $944 $4705 $1712 $2,735 E Shipping Weight 2437 MIM 4356 579 130 10 583 MAY 1457 8987 ZG 9330 1087 4:05 1258 2114 8578 1808 900 2233 4305 3812 8732 500 1209 - 7887 3452 4887 NY PA PA CO PA NY TX OR NY ON OH CO WA NY WA CO OH CHE WA TX WA WA WA NY NY SEOT 503 51 09 S18 $130 $0.90 $0.93 5101 $0.90 3151 $1.00 $0.12 23-Jan-11 $9.100 $9,888 $ 000 $8.578 $6.999 $8.321 $5.500 $1,100 55 800 $7.800 $4.500 $100 25.11 29.11 SO $0.99 5130 3100 300 30 31 32 33 34 35 36 There Tomorrow Thien Tomorrow Thore Tomorrow 0507 11.11 01-11 02 Feb 11 03-11 11 55.4.12 $3278 $3.000 53812 10033 10004 09 AYN20 0907 509 300 31.00 50 24 Seven 35467 F Average Invoice Cost Per Carrier 24 Seven Count on Us Sure Shipping Testing Time There Tomorrow 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Shipping Weights Per State PA AZ OR TX GO OH WA NY Total Shipping Costs Per State PA AZ OR TX CO Average Shipping Costs Per Pound 24 Seven Count on US Sure Shipping Testing Time There Tomorrow OH WA NY 22 23 24 25 Recommendations: 25 27 28 29 30 31 32 33 34 35 30 AYK 20 Operational Effidencies Solution on suon your textbook. Project 20: Operational Efficiencies Hoover Transportation, Inc., is a large distribution company located in Denver, Colorado. The com pany is currently seeking to gain operational efficiencies in its supply chain by reducing the number of transportation carriers it is using to outsource. Operational efficiencies for Hoover Transportation, Inc., suggest that reducing the number of carriers from the Denver distribution center to warehouses in the selected states will lead to reduced costs. Brian Hoover, the CEO of Hoover Transportation, requests the number of carriers transporting products from its Denver distribution center to wholesal ers in Arizona, Arkansas, lowa, Missouri Montana, Oklahoma Oregon, and Washington be reduced from the current five carriers to two carriers. Carrier selection should be based on the assumptions that all environmental factors are equal and historical cost trends will continue. Review the historical data from the past several years to determine your recommendation for the top two carriers that Hoover Transportation should continue to use. 1. Analyze the last 24 months of Hoover's Transportation carrier transactions found in the AYK20_ Data.xlsx data file. 2. Create a report detailing your recommendation for the top two carriers with which Hoover Transportation should continue to do business. Be sure to use PivotTables and PivotCharts in your report. A few questions to get you started include: - What is the average cost per carrier? What are the total shipping costs per state? What are the total shipping weights per state? - What are the average shipping costs per pound? What is the average cost per carrier? Ve Nube F Involce Number Date Carrier Name Destination State Involce Amount Shipping Cost por Pound 30.88 OH CO OR AX NY PA 3 $0 3097 $1.02 $107 $1.05 $1.06 $0.00 $0 5097 10001 10002 TOY 10004 1000 1000 1000 10000 1009 10010 11 TO012 10013 10014 10015 10010 10017 1001B 10019 10020 1002 10022 10023 10024 100% 100 1002 10090 100 1000 10031 0 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 02 Jan 11 03.11 04.10 051 0-11 02 Jan 11 On 11 09 Jan 11 10:11 11 Jan 11 12-Jan-11 13 Jan 11 14.Jan-11 15 Jant 15 11 17 J-11 18 Jan 11 1911 20 Jan 11 21 Jan 11 Soro Shoping Suto Shipping Tasting Time 24 Sum 24 Sevin Count Us 24 Seven Soro Supping Sun Shu Sun Shipping Tosing line Count on Us Sure Shippine Count on 24 SO Count on Count on Us Testing Time Count on US Testing Time 24 Seven Count on Suto Ship Count on us Suro Shipping Thor Tomorrow There Tomomow Thore TOW $2.13 $1,054 $3.987 $5,870 $4,575 $1,587 $ 100 $4,578 $7.000 $8 875 $/01 $9.999 $944 $4705 $1712 $2,735 E Shipping Weight 2437 MIM 4356 579 130 10 583 MAY 1457 8987 ZG 9330 1087 4:05 1258 2114 8578 1808 900 2233 4305 3812 8732 500 1209 - 7887 3452 4887 NY PA PA CO PA NY TX OR NY ON OH CO WA NY WA CO OH CHE WA TX WA WA WA NY NY SEOT 503 51 09 S18 $130 $0.90 $0.93 5101 $0.90 3151 $1.00 $0.12 23-Jan-11 $9.100 $9,888 $ 000 $8.578 $6.999 $8.321 $5.500 $1,100 55 800 $7.800 $4.500 $100 25.11 29.11 SO $0.99 5130 3100 300 30 31 32 33 34 35 36 There Tomorrow Thien Tomorrow Thore Tomorrow 0507 11.11 01-11 02 Feb 11 03-11 11 55.4.12 $3278 $3.000 53812 10033 10004 09 AYN20 0907 509 300 31.00 50 24 Seven 35467 F Average Invoice Cost Per Carrier 24 Seven Count on Us Sure Shipping Testing Time There Tomorrow 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Shipping Weights Per State PA AZ OR TX GO OH WA NY Total Shipping Costs Per State PA AZ OR TX CO Average Shipping Costs Per Pound 24 Seven Count on US Sure Shipping Testing Time There Tomorrow OH WA NY 22 23 24 25 Recommendations: 25 27 28 29 30 31 32 33 34 35 30 AYK 20 Operational Effidencies Solution