Supply Chain Sustainability Reporting with Python

The demand for transparency in sustainable development from investors and customers has grown over the years.

Investors have placed greater emphasis on a business's sustainability when assessing its value and resilience.

Therefore, more and more companies invest resources to build capabilities for sustainability reporting and determine the best strategies for a sustainable supply chain.

In this article, we will introduce a straightforward methodology to report the CO2 emissions of your Distribution Network using Python and Power BI.

💌 New articles straight to your inbox for free: Newsletter

If you prefer watching, you have this explainer video

CO2 Emissions Formula for Transportation

Definition

Based on the GHG Protocol corporate standard, greenhouse gas emissions are classified into three scopes:

• Scope 1: direct emissions released to the atmosphere because of the company’s activities
• Scope 2: indirect emissions from the generation of purchased energy
• 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 focus on Scope 3 calculations related to downstream transportation.

What is the environmental impact of your distribution network?

Formula

Following the protocol of the French Environmental Agency Ademe, the formula to estimate the CO2 emissions of transportation is:

With,

This formula provides a rough estimate of CO2 emissions without requiring high-granularity transportation data.

A more accurate approach would be to estimate each delivery's CO2 emissions, accounting for the vehicle type (truck, container ship, plane, or train) and the load factor.

Data Processing with Python

Based on this formula, we can now start to collect data to calculate the emissions.

Import Shipped Order Lines

Let's start by extracting the shipped order lines from our ERP (or WMS): all purchase orders for 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 ordered quantities to weight (kg).

Net Weight vs. Total Weight
Before going into the details of the calculation, we need to explain the difference between gross and net weight.

Packaging is the container that covers your finished product.

In some ERPs’ master data, you may find the net weight (without packaging) and the gross weight (with packaging).

For this report, we need to use 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, cartons (grouping several units) or pallets (grouping several cartons).

If you have carton or pallet weights, you can use them when your customer orders full cases or full pallets.

Assumption of Mixed Cartons
For certain logistic operations, where you need to 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 need to use the full carton weight; we can rely only on the per-unit weight.

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 destination GPS coordinates to our Power BI reporting.

Code

CO2 Calculation

Final Calculation of the CO2 Emissions

We now have all the information needed in a single DataFrame.

We can begin calculating CO2 emissions using emission factors for 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 distances?

A major challenge here is determining distances when 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 the Google Maps API
• Estimate the distance using the Haversine formula
• 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?

In some cases, the master data is not updated, and you cannot get the unit of measure conversions for all the items.

In that case, you can

1. Focus your data collection efforts on the high runners (turnover)
2. Estimate the weight per euro for these items (kg/euros)
3. Apply the average ratio on the c remaining items without conversions to get their weight

Example of visualisation using PowerBI

Bubble map with size = f(CO2 Total)

Visual Insights
You can observe where you have the highest CO2 emissions (large bubbles) with colour-coded transportation modes.

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
The impact of your future CO2 emissions reduction efforts on profitability will likely be greater for customers in PEINE-WOLTORF.

About Me

Let’s connect on LinkedIn and Twitter. I am a Supply Chain Engineer using data analytics to improve logistics operations and reduce costs.

If you’re looking for tailored consulting solutions to optimize your supply chain and meet sustainability goals, please contact me.