For our second Exercise we will conduct a spend analysis using one month of transportation spend data for company ABC, based in North America. This company imports product from Asia and Mexico and distributes through its warehouse near Dallas,TX.
Rather than analyze all the company’s spend categories we will focus on the transportation category and analyze the modes (or subcategories) of the spend. In transportation, the Mode is the subcategory of service that is selected on the shipments that relates to the size and delivery speed.
In preparation for this exercise, read the “Spend Analysis Spreadsheet” section in our textbook on pages 202 – 206 (234 – 238 in Copia). In this exercise you will need to create similar charts and recommendations as described in the textbook. You will likely want to utilize the pivot table functions in Excel to help complete this exercise.
The objective of this exercise is to conduct a spend analysis on the transportation spend to provide insights and clarity into the company’s transportation category and provide recommendations for savings opportunities.
Use the excel file “Transportation Spend Data Nov 2015” Transportation Spend Data Nov 2015.xlsx
Create the tables and charts below to summarize and analyze the spend and provide recommendations. Each of the items below should be created and placed on a separate slide in Power Point or a PDF. (There should be 6 slides) You will need to submit both (2) your excel work file and your presentation slide files.
Provide a table that shows spend by carrier (supplier) sorted from largest spend to smallest. Similar to Exhibit 6.4 in our text.
Provide a table that summarizes spend by mode, including totals by carrier, by descending spend amounts.
Create a pareto chart (like Exhibit 6.5) that summarizes Spend by Mode
Create a pareto chart (like Exhibit 6.6) that summarizes number of Carriers by Mode
Find the average spend per carrier by mode. Create a pareto chart (like Exhibit 6.7) that summarizes the average spend by Carrier by Mode.
Applying the concept of Pareto analysis, what are your recommendations for savings opportunities for company ABC’s transportation spend? Provide your recommendations on a separate slide.
