Loading

Altria - Transportation Spend Analysis MBA 549 | Spring 2020

Altria's challenge

Altria's distribution company, Altria Group Distribution Company (AGDC), needed a way to more effectively track the spending and volume of its numerous carriers and the vast amounts of deliveries it made each year.

The assignment

A team of Jenkins MBA students was tasked with developing a spend analysis tool that would allow AGDC to visually and accurately track, analyze and report spending and volume for its carriers and deliveries.

The students' approach

The team started by working with Altria to gather and prepare data and to understand the analytical needs of AGDC’s operations.

They then conducted an enterprise-wide transportation spend analysis to segment the spend by carrier, Operating Company (OpCo), cost center, general ledger and time frame.

Using this data, they developed two digital workbooks made up of several dashboards.

Workbook 1

The first workbook contained Altria’s annual enterprise transportation spend and logistics spend from manufacturing sites to public warehouses.

Five different dashboards allowed for segmentation by date, carriers, OpCo, cost center general ledger and category.

Other dashboards displayed the STO data to identify the spend, volume and average shipment for each carrier and lane with adjustable time range for transportation from manufacturing plants to public warehouses.

Workbook 2

The second workbook included Altria’s spending on deliveries from public warehouses to customers, and incorporates KPI’s such as total shipment count, cost/mile, cost/ton-mile, number of cases, cost/lb, shipment cost, total weight, on-time delivery percentage, and delivery frequency.

Within this workbook, the dashboards grouped customers, carriers and public warehouses by geographic region and lane with adjustable time range, carrier type, carrier name, customer type and customer ID.

Outcomes

At the end of the semester, the NC State team provided:

  1. Two Tableau workbooks with interactive dashboards that can be toggled and filtered between different segments of the business
  2. An Excel macro application to clean stock transport report (STO) data
  3. A Tableau Prep application to organize and clean shipment data (SAP) for analysis
  4. A report on KPIs to monitor for efficient deliveries
  5. An instruction manual to help with any updates needed for the workbooks moving forward

The team also made the following recommendations to Altria based on their findings:

  • Concentrate on reducing the number of deliveries customers receive per week.
  • Research industry standards for costs per pound, costs per mile and costs per ton-mile, and then review AGDC carriers and routes whose costs exceed the industry norms.
"We were impressed with the students' resumes before we had a chance to meet. They had relevant work experience as well as supply chain, systems and engineering backgrounds. They quickly grasped the concept of our Transportation supply chain and cost structure. The tool is integral to our transportation cost saving and optimization strategy. It provides cost visibility as well as detailed KPIs. We have shared it across multiple functions and use it to conduct ongoing analysis on our customer supply chain." — Andrew Smith, Manager Transportation & Derrick Winston, Sr. Manager Transportation
“I had the opportunity to work in a professional setting for the first time in the USA, learning new software and developing data analysis skills. I also began to understand various aspects of project management, starting from chartering the project to meeting the client expectations while working on a project plan.” — Sourabh Mahajan, Industrial Engineering student
Sourabh Mahajan, Industrial Engineering student
“To meet the client’s expectations, this project required me to learn a new tool, Tableau, to create our key deliverable. This project not only strengthened my supply chain management and data analysis skills, but it also helped me become a Tableau Certified Associate. My favorite part of the project was that everyone on the team was able to experience every aspect of the project, such as leading project meetings, documenting weekly reports, data cleaning, data analysis, etc. The team’s focus during this challenging time was also noteworthy. We successfully overcame the challenge of executing a project of this magnitude while working 100% remotely after the COVID-19 pandemic began. Even though the team faced multiple challenges, we were able to adapt to the situation and deliver a tool that we're confident exceeded the client’s expectation.” — Rahul Jaiswal, Jenkins MBA student
Rahul Jaiswal, Jenkins MBA student

_______________________

_______________________

The NC State team

Uyen-Mai Doan

Rahul Jaiswal

Sourabh Mahajan

John Zapko (faculty advisor)

The Altria team

Derrick Winston, Sr. Manager Transportation

Andrew Smith, Manager Transportation

Morgan Hensley, Sr. Analyst Transportation

Travis Henning, Sr. Analyst Procurement

Clark Adrian, Associate Analyst Transportation