Development of Logistics Carbon Calculator
For this article I’m going to take you back with me to 2009 when I worked for a Global Logistics Company as a Customer Solutions expert specialising in Supply Chain Reporting.
I had a challenge set to me to create something I didn’t even think was possible. I haven’t written about it before, but as a website developer specialising in logistics, I wish I had once had a blog like this to help me find the information to meet this challenge.
I had been invited to go over to Sweden to meet Ericsson Logistics management team in Stockholm to discuss providing advanced KPI reports that they could use to measure the performance of the supply chain. Over the years I had developed many systems from hospitality databases to membership management databases, however what I had been asked to produce wasn’t available at this time.
Carbon Emissions Research
Carbon emissions were (and still are) a big thing in Sweden; every logistics company needs to provide some sort of Carbon Emissions for Road transport at least. Right towards the end of the meeting I was asked if we can provide a CO2 report for airfreight. My heart sank… The airfreight division systems and data quality at that time were let’s say, terrible and almost impossible to get any decent data from the systems. More importantly the data reports we were providing came from the track and trace system, KN Login. I knew from other reporting projects that chances were Ericsson wasn’t even properly setup on the system.
I thought to myself ‘do you know what, this is a good start to push for better data quality internally’. A large global company wants a data driven report and operations need to start putting in the right data.
Me being me, decided to be dead straight with the customer and explained that this is the first ever request like this and I don’t even know if the systems will be able to provide the data required. However, there must be a formula out there which we can use and if needs be, we will try and create a manual report. (At that time Kuehne + Nagel used a very advanced reporting system called Business Objects, it allowed non-technical users the ability to create super complex reports dragging and dropping data fields).
On the flight back my college was nattering the whole way back, it was late but my mind was thinking. We know the airports which the cargo flies from and we know the weight (if it’s entered correctly into the system). Once I got home I left it for a week, I then was off to Hamburg to meet my colleagues from around the world to talk about all things data and reporting, I always like going to Hamburg as we always had a nice hotel to stay in and we were always a stones-throw away from a bar or a restaurant. I got back from a very heavy session on new reporting capabilities but I couldn’t sleep, I also needed to work out how to resize 1000 images in photoshop automatically for one of Cocoonfxmedia’s first clients. I thought tomorrow isn’t going to be too heavy I can have little sleep and sit at the back of the room, nod if anyone talked and try not to snore.
I fired up the laptop, cracked open a bottle of red wine, made sure I had a top level of WiFi from the company and started to looking at the image resizing. I always told KN that I need this expensive Wifi to run reports, not that I was streaming video or building up a company on the side. I am sure they knew.
I noticed that my airplane ticket from Scandinavian Airlines (SAS) had on the back of the ticket the CO2 I had admitted on my flight from LHR (London Heathrow) to ARN (Stockholm – by the way is nowhere near Stockholm). So if SAS had calculated this for a human, why couldn’t it be done for cargo and more importantly how did they know how much I weighed. Is this a hidden technology in the airplane seats no one was aware of?
I can’t remember the figure as many bottles of wine has passed since then, so I thought ok they must be working on an average. So I started Googling, where I started to dig up some more information. At this stage I found plane spotter websites who were clearly geeks as they had written articles about how they had worked out what SAS had done.
I then found out that airlines back then used to work out that on average a person would weigh 70kgs. I then realised that I have two figures which I could potential reverse engineer the formula. I was never good at standard maths at school, I am dyslexic and at the best of times can’t even remember a telephone number or even a postcode, however I started to draw out a plane on a piece of paper and put a very sketchy seat plan configuration. This is sometimes how I work out formulas visually. So if there are 120 seats on this plane, which is an A320 and each seat pumped out X amount of CO2 then in theory I could at least work out the final piece of the jigsaw. So I thought 120 x 70 = Total Weight. I then divided it by the figure on the seat. I wasn’t too far off.
So how do I design a report for CO2 emissions? I am thinking that between SAS and the geeks on the internet I may be able to crack this problem easily. I then contacted Lufthansa as KN had a very good relationship with them and they said they can give me the total distance a plane travelled and total freight volume but they couldn’t give any CO2 information. I thought in this day in age CO2 is a big thing, a government possibly had something. I looked at the DEFRA website and found that a study had taken place around CO2 but to do with airport emissions around landing, taking off and taxiing but it was only around the fuel burnt.
I then thought a long shot is asking a pilot, luckily I knew a family friend who was working a 747 freighter captain for Korean Airlines. I asked him if he knew any of the formulas, and this is when the curve ball came in. NO, there are no formulas but to even work it out you would need to know the speed, distance, aeronautical wind speeds, head or tail speed and also if the plane was long haul or short haul just to work out the time in the air and what fuel load is needed. I thought damn how do you get this? My helpful friend said he can give me the total fuel needed and weight of fuel for a standard long haul journey. He also said on take-off and landing they burnt the most fuel. Also for security reasons airlines wouldn’t give you exact flight paths and also sometimes planes have to fly around war zones and storms.
So now I have a lot more complicated task. I wondered if any of Kuehne + Nagel systems record which plane is used and the data on distance.? If this was sea freight it would be dead easy as they were a lot more switched on when it came to data than their airfreight colleagues. So basically, we couldn’t pull out this kind of data from an operating system. Talking to another colleague who was a mathematical genius, I discovered that I want could complete the formula with the variables I had; long haul, speed, range, weight, load capacity. From this I could start engineering a formula which could be credible.
The Eureka Moment
I then Googled what is the most commonly used planes for long haul and short haul. Boeing planes where the most commonly used planes. A 757-200 and 747-400. So if I know the plane types, I can go to Boeing. I then was able to find everything I wanted speed, cargo capacity, range, fuel capacity.
So now the only thing I don’t have is the distance. Surely there must be something out there for distances between airports. I stumbled across a website which had straight line distances between codes (as the crow flies). However, you had to manually plug in the airport codes. OK I have LHR to ARN and a CO2 figure. I had the key distance, I could now try and work out the CO2 figure.
It just about worked, it seemed to be a little bit more than SAS but possibly my calculation was a little bit more refined but it’s was extremely close.
After a little bit more research I found a CO2 figure from DEFRA. It was a few grams out either way than what I had. I had cracked it! However, this still wouldn’t work for freight.
Development of the process
So I now had a figure, the issue however was developing this into a report for Ericsson. At this stage I had overlooked two key areas. An airfreight consignment didn’t fill the whole plane and I didn’t know if Kuehne + Nagel use freighters or passenger planes.
This required as straight forward simple call to ops and I would know. Predominately freighters are used for long haul and passenger planes for short haul. That made it easier to work out. I then need to work out if a consignment weighed 1Kg and was on a 747 it would emit the same CO2 as 10,000 kg, so I need to work out how to attribute the CO2 to each consignment if I was going to provide a CO2 emission report for Ericsson (as they would only want to know about their consignments). This took ages surprisingly, as I couldn’t get it right.
So I now have all the formulas. I know what I need to get the calculations to work, I just needed the data and more importantly I didn’t want to do this manually for clients, because as soon as I mention I have a formula, every sales rep would want to show this to their clients. I still hadn’t told anyone that I had worked this out and it would need some sort of approval from the Environmental Department.
So the next step prior to this is to have a full working model, as knowing how Kuehne + Nagel work they would try to stop it without supporting evidence.
The following step then is to automate this process. I know the reporting system allowed me to work with external data sources and I knew I can create my own variables (objects) so I can at least partially automate the report; the distances where still the problem. There were no databases with this data in the company and I know in the time of a recession KN wouldn’t purchase data, as it took 3 Directors to sign off on a £40 phone.
So the only way to get the data was to do this manually and I needed to find a customer which did airfreight, covering mainly trade lanes. Rolls Royce sprang to mind. I knew the data quality is good on this account and they did a ridiculous amount of airfreight. However, there could be 20,000+ different distances to get. I needed help.
This is when the cat got out of the bag, my fellow colleagues started to help out enough to service Ericsson’s needs at least. I then decided to get this built into the system so the reporting team in Hamburg very kindly agreed that even putting in the distance could be very useful and start to implement this.
The final out come
After some checking by several departments the formulas were verified as being as accurate as they could be. We then applied the same logic to both sea freight and road transport including rail, which all had slightly different calculations. Rail by far was the most environmental. When it came to off-setting the CO2, we worked out 9000 trees would have to be planted per ton of CO2
I did build and designed a website with the calculator at the time of working at Kuehne + Nagel which was built in PHP and MySQL and was another way to show KN they could potentially put this on their website. However, I was asked to take it down which I had to respect at the time.
It’s not often you get to work on such a large and important development, it took just over 15 months to get a fully automated and semi accurate calculation report for Ericsson which now at a click of a button KN could run CO2 reports. I have learnt that they’ve enhanced my initial work which is now an award winning system, which I was very proud to have started.
If you’re in Logistics and you need a website designed or an application built for a website just get in touch, as you can clearly see we understand your business and we can develop complex logistics systems.
For the maths geeks, below is the original formula which I will make an assumption Kuehne + Nagel now have enhanced and refined.
For sea freight please see the attachment. The formulas in there will need a PHD to understand but the seafreight was a lot easier to work out than the other modes of transports. A plug is what a shipping line calls a 20ft container. A logistics company calls a container a TEU (Twenty Foot Equivalent Unit).