Toss Your Sticky Notes! We’re Getting Efficient with Fuel Surcharges!

I am a very lazy person. You think I’m kidding, but I’m the kind of person that doesn’t wear a coat, even when its negative degrees outside because I don’t want to have to deal with finding a place to put it when I get inside and I’m not cold anymore. I especially hate repetitive and tedious tasks, and will go out of my way to avoid them.  On my resume, this is listed as “constantly looking  for ways to increase efficiencies”.

When I first moved to CSR, I found myself doing the same tasks week after week and my lazy inner child started screaming, “We just did this last week! Why the heck are we doing it again?” One such activity was updating fuel surcharges.

The Problem

Anyone who works in the trucking industry is aware of the ever changing state of fuel surcharges. If it isn’t bad enough that the fuel rate itself changes every week, each customer has their own corresponding fuel surcharge. To make a long story short, I found myself spending a good 20-30 minutes every Tuesday tracking down paper copies of these rates, tracing my finger down and across the page to discover that customer’s fuel surcharge, and recording this all-important number on my handy, dandy sticky note. I then found myself repeating the process 5-6 more times for every customer.

After about a month, my lazy self decided this needed to change.  My original idea was just to digitize those paper documents, to have them all in one location. I briefly considered scanning copies of these documents and throwing them into a word document that I could save to my desktop, where it would be in the same location every time I needed it. But the lazy kid inside of me kept telling me that was not a satisfactory solution. I really wanted the computer to do as much of my work as possible. After much deliberation and arguing with myself, I decided Excel was definitely the way to go.

The Solution: Choose Excel

Anyone who has not had the opportunity to explore the mystical world which is an excel document has never truly lived. Ok, ok. I’m being dramatic. However, my point is still valid. Excel is designed to be programmed, and with a little determination and persistence it can be persuaded to do almost anything you can think of – and even a few dozen things that you can’t!

Even if I wasn’t going to try to sweet talk Excel into doing my work for me, Excel would have been a prime choice because of its table like attributes, and ability to run multiple complex functions simultaneously.           

I finally decided to digitize all of the fuel surcharges into their own Excel pages. Then, through a series of if-then statements, and a few dozen links, I would be able to update the current DOE value of fuel, and it would make a list of all of my fuel surcharges, potentially saving me hours and hours of work. 

 I really wanted my fuel spreadsheet to be user friendly, so I decided to put all the important information on worksheet 1, and all the “behind the scenes” information on another worksheet. This would have been an “ok” idea for my 5 accounts, but after telling my boss my idea, a small mountain of fuel charts which mysteriously appeared next to my computer.  In order to accommodate a much larger pool of information, I ended up creating a worksheet for each individual customer.  

The Process

I started by digitizing each of my fuel charts, pulling off pertinent information “at least,” “less than” and “fuel surcharge.” That was the easy part. Getting the computer to do my work ended up proving considerably more difficult. I created an “input” cell, where I could type the current DOE price, and I wanted excel to compare the DOE to the lowest, highest, and assign a fuel surcharge if applicable. Not as simple as it sounds. As hard as I tried, and as many times as I begged, Excel simply would not do everything in one step. 

In order to simplify the process, I created an If-then statement that would determine if the value of the DOE was between the “at least” and “less than” values on my fuel chart. I ran another If-then statement to determine if the DOE value was equal to either the “at least” or “less than” values.  These formulas resulted in a “true” or “false” value appearing in each cell. I then added a “fuel” column to my chart. If either value returned a “true” value, the fuel surcharge would appear in the corresponding fuel box”. For example, if the DOE was 1.24, my chart would look something like this:

The project was turning out great, now all I had to do was find that worksheet that I had assigned to each customer, and the number showing up on the right hand side would be my fuel surcharge, much easier than the original finger-sliding method;  but not quite satisfactory enough for my lazy inner child. I really wanted all the numbers to be in one centralized location, similar to how they had been displayed on my sticky note.  After a few dozen failed attempts, and a few dozen more words they will not allow me to use in this blog, I had come up with a solution.  Because the Fuel only appears in the right hand column if it is the active fuel surcharge, by using the SUM function in Excel, I could make sure the number I needed was always in the same location on each worksheet.

Once the needed information was in a consistent location, I was able to create a formula on the home page, which would use that number to create a slightly prettier version of my original sticky note.

At last, I had sweet talked, and manipulated Excel into doing all my work for me. Each week, I simply have to update the DOE price, and every one of my customers corresponding fuel surcharges updates automatically. I had finally found a solution even my lazy inner child could be content with!