Supply Chain Sustainability Reporting with Python
Build a sustainability report focusing on the CO2 emissions of your distribution network using Python.
The demand for transparency in sustainable development from investors and customers has grown.
Investors have increasingly emphasised business sustainability when assessing an organization's value and resiliency.
How can you support the sustainable transformation of your company with Data Science?
Therefore, more organizations are investing resources to build capabilities for sustainability reporting and determine the best strategies for a sustainable supply chain.
We can automate Sustainability Reporting with Python.
In this article, we will introduce a simple methodology to report the CO2 emissions of your Distribution Network using Python and PowerBI.
SUMMARYI. How to create a Supply Chain Sustainability report?
Calculate the CO2 emissions of multi-modal transportation ?
II. Data Processing with Python
List of the datasets needed to build your report
1. Import Shipped Order Lines
All the purchased orders of your customers that have been shipped
2. Add Unit of Measure Conversions
Merge with Master Data to convert quantities in weight
3. Distances Collections
Collect the distances by transportation mode
III. CO2 Calculation
1. Final Calculation of the CO2 Emissions
2. What can you do if you have missing data?
VI. Example of visualization using PowerBI
V. Conclusion
What the next step?
1. Transportation Route Visualization with Python
Provide insights to support the decarbonization of transportation.
2. Simulate different initiatives with a Digital Twin
Answer questions like: "What if you deliver only large customers?"
3. Have you heard about ESG Reporting?
What are the benefits for the company in terms of compliance?
4. Expand to Warehouse Operations
Cover the entire distribution chain with initiative to reduce packaging material usage
5. Automatically Generate PowerPoint Report with Python
Create PowerPoint slides with visuals and comments
How do you create a Supply Chain Sustainability report?
Definition of CO2 emissions by Scope
Based on the GHG Protocol corporate standard, greenhouse gas emissions are classified into three scopes:
- Scope 1: direct emissions released into the atmosphere because of the company’s activities (Company’s facilities like manufacturing plant/warehouses, company’s vehicles)
- Scope 2: indirect emissions from the generation of purchased energy
(purchased electricity, gas, ..) - Scope 3: all indirect emissions (out of scope 2) occurring in the value chain of the company (Transportation, Waste of Operations, Business Travels, …)
In this article, we will focus on the Scope 3 calculations related to downstream transportation.
What is the environmental impact of your distribution network?
The Formula of CO2 emissions using Emissions Factors
With,E_CO2: emissions in kilograms of CO2 equivalent (kgCO2eq)
W_goods: weight of the goods (Ton)
D: distance from your warehouse to the final destination(km)
F_mode: emissions factor for each transportation mode (kgCO2eq/t.km)
This formula provides a gross estimation of the CO2 emissions without requiring a high granularity of transportation data.
A more accurate approach would be to estimate the CO2 emissions of each delivery, considering the vehicle model (truck, container carrier, plane, or train) and the filling rate.
Calculating Supply Chain Carbon Footprint with Python
We can now collect data to calculate the emissions based on this formula.
Import Shipped Order Lines
Let‘s start by extracting from our ERP (or WMS) the shipped order lines: all purchased orders of your customers that have been shipped from your warehouses.
This dataset includes
- Order information: Order Number, Line Number
- Item Code: Identification number of the item in the master data
- Warehouse Code: the business unit from where these orders are shipped (Distribution Center, Factory)
- Customer Code: the final destination where the orders are delivered (Store, Distribution Center)
Code
Result
Add Unit of Measure Conversions
The next step is to convert the quantities ordered into weight (kg).
Net Weight vs. Total Weight
Before discussing the calculation's details, we must explain the difference between gross and net weight.
Packaging is the container used to cover your finished product. In some ERP master data, you may find the net weight (without packaging) and the gross weight (with packaging).
For this report, we need to take the gross weight to estimate the total weight, including packaging.
Handling Unit (Cartons, Pallet)
Depending on the order quantity, your customer can order by unit, carton (grouping several units), or pallet (grouping several cartons).
If you are lucky enough to have the weights of cartons or pallets, you can use them if your customer is ordering full cases or full pallets.
Assumption of Mixed Cartons
For certain logistic operations where you must perform piece picking (Luxury, Cosmetics, E-Commerce), the quantity per order line is so low that you rarely ship full cartons.
In this case, there is no point in using full carton weight.
We can only rely on the total weight per unit.
For our example, we will assume that we are in this situation.
Code
Results
Distances Collections and GPS Locations
We need to collect the distance by mode:
- Air Freight
- Sea Freight
- Road Transportation: Trucks
- Rail Transportation: Trains
We will also add the GPS locations of the destination for our PowerBI reporting.
Code
🏫 Discover 70+ case studies using data analytics for supply chain sustainability🌳and business optimization 🏪 in this: Cheat Sheet
CO2 Emissions Calculations using Emissions Factors
Final Calculation of the CO2 Emissions
We now have all the information to be gathered in a single data frame.
We can start calculating the CO2 emissions using emissions factors associated with your transportation network.
Sum the weight by Order.
For reporting purposes, let us calculate the CO2 emissions for each order number (linked with a customer and a date).
Final Results
What if you don’t have 100% of the distance?
A significant challenge here is to get the distances if you have several thousand delivery locations.
If you are not able to collect 100% of the distance from your carriers, you can:
- Get the road distances using Google Maps API / OR
- Estimate the distance using the Haversine formula / OR
- Compute the average distance of your top customers (in Euros) and apply the average to the locations without distances
What if you don’t have 100% of the weight conversions?
Sometimes, the master data is not updated, and you cannot get the unit-of-measure conversions for all the items.
In that case, you can
- Focus your data collection efforts on the high runners (turnover)
- Estimate the weight per euro for these items (kg/euros)
- Apply the average ratio on the c remaining items without conversions to get their weight
Please find the complete code with dummy Data in my GitHub repository:
💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.
Example of visualizations using PowerBI
Bubble map with size = f(CO2 Total)
Visual Insights
You can observe where you have the majority of CO2 emissions (large bubbles) with a colour coding by transportation mode.
Split by Country Destination and Item Code
Product Portfolio Insights
For each market, which item has the highest environmental impact?
CO2 = f(Turnover) by City Destination
Financial Insights
Your future efforts to reduce CO2 emissions will probably have a higher impact on the profitability of the PEINE-WOLTORF customers.
Next Steps
You have now generated a simple dashboard to visualize the emissions of your distribution network with different granularities.
You’re going to be audited; be prepared!
Your results will be used for non-financial reporting, so your data extraction and processing process may be audited internally or by external actors.
What’s next?
Provide insights to support the decarbonization of your logistic operations.
Visualization of your Transportation Network
After measuring your emissions, a potential next step could be to analyze your Distribution Network.
You want to look for opportunities to
- Maximize the filling rate of trucks (% of the volume occupied)
- Combine shipments to reduce the number of trucks in your fleet
- Minimize the empty run distance (backhaul)
You can support this process with Python.
For more details, have a look at this article 👇
The reengineering team has drafted multiple initiatives to reduce the carbon footprint.
They require your support to estimate their impact.
Let’s simulate several ‘What if’ scenarios.
Simulate different initiatives with a Digital Twin
A digital twin is a digital replica of a physical object or system.
A Supply Chain digital twin is a computer model representing various components and processes involved in the supply chain.
After you have measured your baseline of CO2 emissions and fixed your reduction target, you can start building a roadmap.
What do we want to achieve?
For each initiative, you can use this model to estimate the impact on the
- CO2 emissions: % of reduction
- Service Level: delivery lead time(s)
- Costs: additional CAPEX and operational costs
And validate the initiatives that reduce emissions without impacting the service level while respecting budget constraints.
What is the most effective initiative?
Initiative 1: you want to set up local warehouses and use electric vehicles for the last-mile delivery
- What would be the impact on the transportation costs?
- What would be the impact of warehousing costs (with more locations)?
- How much CO2 emissions reduction can we reach?
Initiative 2: you would like to stop air freight to reduce your CO2 emissions
- What would be the impact on the stores’ replenishment lead times?
- How far ahead do the distribution planners need to create replenishment orders?
- What would the impact be on the reduction of CO2 emissions?
Initiative 3: you want to set up additional factories to produce locally for all markets
- What would be the impact on the production costs?
- What would be the impact of transportation costs (with factories close to the warehouse locations)?
- How much CO2 emissions reduction can we reach?
Adapt your model to include each initiative and see how much your service level is impacted.
Then, you can adapt the other metric (e.g., improving warehouse capacity, adding trucks) until you restore the service level.
For more details on how to implement it, 👇
The top management is ready to invest in your initiatives, but they want to know the company's return on investment.
Have you heard about ESG reporting?
Expand to ESG Reporting
Environmental, Social, and Governance (ESG) reporting can be defined as a method by which corporations disclose their governance structures, societal impacts, and ecological footprint to shareholders.
CO2 emissions reports are included in environmental reporting (E).
This can be completed with a whole life cycle assessment, which includes natural resource usage and waste generation.
ESG also considers the social impact and governance, tackling topics like diversity, inclusion and CSR.
How can we use data analytics to improve ESG reporting?
💡 For more information about ESG Reporting,
Now that you can estimate the impact on compliance and ESG scoring, you can expand the scope of re-engineering to the whole distribution chain.
We focused on transportation. What about warehousing?
Consider local initiatives
This article's example focused on reducing the CO2 emissions of your transportation network by optimizing flows and warehouse locations.
However, smaller local initiatives, such as managing waste and cutting consumable usage, can be implemented to reduce the footprint of warehouse operations.
Your digital twin can also help you estimate the impact of these small initiatives on the overall network (if applied to all warehouses).
- Calculate the current consumption using actual operational data
- Include these parameters in your digital twin
- After a Proof Of Concept estimate, the savings target
- Extrapolate the savings (per warehouse) to the whole network
All these initiatives align with the efforts of cost reduction and operational excellence.
You can adapt your continuous improvement methodologies to track consumables usage and find alternatives that will not impact your productivity.
For more details about it,
How would you like to share your results?
Even if the adoption of visualization tools like PowerBI has improved, people still rely on PowerPoint slides to communicate.
Automate PowerPoint Slide Creation with Python
Do you want to spend more time creating PowerPoint presentations than analyzing the results of this analysis?
I guess no!
Don’t worry.
I have a solution for you, which I shared in another article.
The idea was to create a completely automated workflow to extract and process data from multiple systems to generate graphs and comments that will be included in PowerPoint slides.
The Python library Python-ppt provides complete freedom to create tailored slides that can be filled with automatically generated data.
You can adapt this workflow for our CO2 emissions reporting tool.
For more details, have a look at the article linked below 👇
If you prefer to watch, have a look at the video version of the article
About Me
Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer who uses data analytics to improve logistics operations and reduce costs.
For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.
If you are interested in Data Analytics and Supply Chain, look at my website.
💌 New articles straight in your inbox for free: Newsletter
📘 Your complete guide for Supply Chain Analytics: Analytics Cheat Sheet
References
[1] GHG Protocol corporate standard, Greenhouse Gas Protocol, Link
[2] French Environmental Agency Ademe, Bilan GES, Link
[3] GitHub Repository with source code and data, Samir Saci, Link
