Supply Chain Sustainability Reporting with Python

4 steps to build an ESG reporting focusing on CO2 emissions of your Distribution Network

Supply Chain Sustainability Reporting with Python

4 steps to build an ESG reporting focusing on CO2 emissions of your Distribution Network

Article originally published on Medium.

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

Investors have placed an increased emphasis on the sustainability of the business when assessing the value and resiliency of an organization.

Therefore, more and more organizations 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 simple methodology to report the CO2 emissions of your Distribution Network using Python and PowerBI.

💡
SUMMARY
CO2 Emissions Formula for Transportation
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

Edit: You can find a Youtube version of this article with animations in the link below.

I. CO2 Emissions Formula for Transportation

Definition

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

  • Scope 1: direct emissions released to the atmosphere because of 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 our attention on the 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 (Link), the formula to estimate the CO2 emissions of transportation is:

Formula using Emission Factor — (Image by Author)

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 level of granularity of transportation data.

A more accurate approach would be to estimate the CO2 emissions of each delivery considering the model of the vehicle (truck, container carrier, plane or train) and the filling rate.

💡
Full code with dummy Data in my Github repository: Link

II. Data Processing with Python

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

Supply Chain Sustainability Reporting — Data Processing
Data Collection — (Image by Author)

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

Shipped order lines data frame — (Image by Author)

Add Unit of Measure Conversions

The next step is to convert the quantities ordered into 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.

Total Weight — (Image by Author)

Packaging is the container used to cover your finished product. In some ERP’s master data, you may find the net weight (without packaging) and the gross weight (with packaging).

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

Total Weight — (Image by Author)

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 need to perform piece picking (Luxury, Cosmetics, E-Commerce), the quantity per order line is so low that you rarely ship full cartons.

Weight Reference— (Image by Author)

In this case, there is no point in using full cartons 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

Shipped order lines with conversion ratios — (Image by Author)

Distances Collections and GPS Locations

We need to collect the distance by mode:

  • Air Freight
  • Sea Freight
  • Road Transportation: Trucks
  • Rail Transportation: Trains
Supply Chain Sustainability Reporting -Example of Calculation
Formula using Emission Factor — (Image by Author)

We will also add the GPS locations of the destination for our PowerBI reporting.

Final DataFrame after processing — (Image by Author)

Code

III. CO2 Calculation

1. Final Calculation of the CO2 Emissions

We have now all the information needed to be gathered in a single DataFrame. We can start to calculate 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

Final Report — (Image by Author)

2. What if you don’t have 100% of the distances?

A major 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

3. 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 euros for these items (kg/euros)
  3. Apply the average ratio on the c remaining items without conversions to get their weight

VI. Example of visualization using PowerBI

Bubble map with size = f(CO2 Total)

Bubble Map — (Image by Author)

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

Bar Chart — (Image by Author)

Product Portfolio Insights
For each market, which item has the highest environmental impact?

CO2 = f(Turnover) by City Destination

Scatter Plot — (Image by Author)

Financial Insights
The impacts of your future efforts for CO2 Emissions reductions on the profitability will probably be higher for the customers in PEINE-WOLTORF.

Next Steps

After measuring your emissions, a potential next step could be to optimize your Distribution Network using a methodology presented in the article below.

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