Business Driven Technology 6th Edition by Paige Baltzan

Exercise 77

Operational Efficiencies
Hoover Transportation Inc. is a large distribution company located in Denver, Colorado. The company is currently seeking to gain operational efficiencies in its supply chain by reducing the number of transportation carriers that 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 that the number of carriers transporting products from its Denver distribution center to wholesalers in Arizona, Arkansas, Iowa, Missouri, Montana, Oklahoma, Oregon, and Washington be reduced from the current five carriers to two carriers.
Project Focus
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 data file AYK20_Data.xlsx.
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:
Calculate the average cost per carrier.
Calculate the total shipping costs per state.
Calculate the total shipping weights per state.
Calculate the average shipping costs per pound.
Calculate the average cost per carrier.
Data File: AYK20_Data.xlsx