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 we support the sustainable transformation of your company with data analytics?
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 data collection and processing for Sustainability Reporting.
In this article, we will introduce a simple methodology to report the CO2 emissions of your Distribution Network with visuals generated with Python and PowerBI.
The approach presented here has been implemented for the scope 3 reporting of a large FMCG group with a focus on the distribution network.
Create a 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
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)
Result
Add Unit of Measure Conversions
The next step is to convert the quantities ordered into weight (kg).
Net Weight vs. Total 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.
Total Weight including cartons and pallets — (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
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.
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.
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).
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
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.
Any question?
For any inquiries, please contact us at contact@logi-green.com
This blog post is based on articles originally published on Medium
- Supply Chain Sustainability Reporting with Python by Samir Saci