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.
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:
- Two Tableau workbooks with interactive dashboards that can be toggled and filtered between different segments of the business
- An Excel macro application to clean stock transport report (STO) data
- A Tableau Prep application to organize and clean shipment data (SAP) for analysis
- A report on KPIs to monitor for efficient deliveries
- 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
“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
_______________________
_______________________
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