This is my second post of the series, #IDidntKnowExcelCouldDoThat, inspired by my clients saying, “wow, I didn’t know Excel could do that!”. Earlier this week I started this series with an article on LinkedIn about leveraging public data for business intelligence purposes. The end of that article resulted in looking at a data set geographically on a map using Power BI. This article takes a different step towards working with geography for trucking and logistics using Excel with a little help from our friends at Google Maps. I have selected an example file from my consulting practice in order to introduce you to some of the hidden power and flexibility of Excel, and to demonstrate what is possible when you go beyond the basics.
Have you ever wished Excel had a formula to calculate the distance between two street address locations? Or between two latitude/longitude coordinates? Or dreamed of importing a list of ‘origin-destination’ lookups from Google Maps into Excel? What about performing a batch lookup of the driving distances and estimated driving times between 1500 different combinations of locations? To do something like that manually one-by-one by searching on the internet would take a very long time. Instead, lets look at a lightning fast and efficient (automated) approach to batch-calculating driving distances and times between locations. I have chosen a workbook that was originally created a few years ago for analyzing various trucking and hauling scenarios.
Before we dive in too deep, allow me to provide some context by listing a few real-life examples of business situations where I have used Google Maps connections in spreadsheets to generate significant value for clients:
1) Trucking companies (sales reps) – for quickly generating batches of quotations based on origin and destination addresses, or a series of locations for a ‘milk run’ of deliveries. They can also determine reasonably accurate estimates for both $/hour and $/volume billing rates for any situation. This type of programming logic can be inserted into quotation templates, so it is embedded in a company branded template that prints off a PDF for a customer.
2) Customers of trucking, hauling, and courier companies (primarily procurement and cost control professionals) – for estimating the time required to perform services, and charges they should expect to receive on an invoice. If there is a significant variance between the estimate expected and what was actually invoiced, they like to look into it to determine what caused the variance. Perhaps the billing was inaccurate, or maybe there are other factors involved that need to be discovered which could lead to opportunities to increase performance and reduce costs.
3) Oilfield wellsite operators and supervisors – to estimate total expected trucking charges based on a variety of rate structures such as $/hour and $/volume (tonne or M3). For services like fluid hauling and waste disposal, many vendors provide the option to pay by the hour or by volume. Making the most economical choice is a factor of many variables (distance, avg speed of travel, hourly rate, tonnage rate, and hauling capacity). Being able to compare multiple scenarios, suppliers, vehicle types, and rate structures in advance is a significant competitive advantage. Without a sophisticated tool, people rely on assumptions or “common knowledge” that can be misleading and drive costs up instead of down.
This list goes on… but I think you get the idea. Now lets get into a few details to explain how this is possible, and show an example of how Google Maps connections can be incorporated into a spreadsheet.
The Opportunity: Excel can “talk” to the internet
In this case, we are looking at making our Excel spreadsheet communicate with Google Maps. Most people are familiar with searching for driving directions between two locations using Google Maps. Google will tell you the most direct route, approximate driving distance, and estimated driving time. What most people don’t know, is that it is possible to communicate with Google Maps programmatically using various programming languages. Microsoft makes it possible for Excel users to write their own code using the Visual Basic for Applications (VBA) language. Those who can program in VBA have the opportunity to create really powerful tools and customized applications, such as… a Google Maps connection within Excel to automate the calculation of driving distances and times between locations that are listed in a spreadsheet.
The Challenge: Programming and analytical modeling skills required
Not everybody has the time or interest in becoming a programmer and an analytical developer. For this reason, I will clearly state that the following example is a rather advanced endeavor that isn’t suitable for a weekend do-it-yourself Excel project for most people. However, the technical challenges to create a model like this made it a good candidate for a developing a template solution so it could be reused, reconfigured, and redeployed with relative ease.
The Solution: An analysis template developed using Excel, VBA, and the Google Maps API (application programming interface) with Geo-referenced distance and time calculation abilities
As mentioned, in the image below you will see a standalone analysis template that has all the Google Maps goodness built right into it. This allows for an end user to make inputs for locations and rates, and everything else just calculates automatically provided there is an internet connection. It doesn’t get much more user friendly than that!
Let me show you around and give you a tour of the template shown. The light green cells are input cells and have been populated to demonstrate several different calculations for a few scenarios. Excel sends the addresses as entered to Google Maps, and Google replies with a message containing the driving distance and times between the two points, which in turn is populated in the appropriate columns of the table. The orange cells are average travel speed overrides that have been applied for situations where driving speed is capped below posted speed limits due to vehicle type or road conditions. All cells that appear in the light grey striped rows are formula calculations. The bright green cells are automatically highlighted by the formulas to indicate which rate structure option is the more economical option for each item.
Now lets walk through the three scenarios depicted in the table. Each scenario is separated by an empty row and set up to illustrate a few common situations.
1) The first entry is a simple calculation to produce an estimate for a delivery from an address in Calgary to an address in Red Deer, at a known hourly rate.
2) The second group of two entries is a comparison of two different fluid disposal options. Two suppliers, with different disposal locations without street addresses (only Lat/Long), different hauling capacities, and different hourly and tonnage rates. Based on the auto-calculations, the better rate structure for each situation is highlighted in bright green. Counter intuitively, in this example the supplier with a higher hourly rate can provide a lower total project cost. (this was due to a larger hauling capacity being the differentiator)
3) The last group of three entries is a comparison of two different water hauling suppliers. One supplier has two different types of trucks that have different over-the-road speed capabilities and different hourly rates. Both suppliers have different source locations they are hauling from, different rates, but the same hauling volume capacities. If you don’t take all the steps to do the math, how would you make your choice? Most people default to the supplier that offers the lowest hourly rate and hope for the best. In this scenario that would have ended up being the most expensive option. The supplier that is furthest away with the highest hourly rate was able to provide the lowest total project cost. (this was due to faster driving speeds a larger and newer truck was capable of)
Some of you are probably thinking, that’s great but what about other variables such as loading time, unloading time, and round-trip calculations? Since Excel provides an incredible amount of flexibility, we can configure the table to handle this too. Within the example model shown in the next image below, there are additional features that have been incorporated to take into account the additional variables and perform more specific calculations. You will note there are the following:
1) Inputs that can be used for more advanced and accurate calculations:
- Override for adjusted average travel speeds (the orange cells mentioned previously). This is very important for calculations involving large vehicles and heavy equipment that are restricted to lower speed limits than passenger vehicles, or when seasonal restrictions have been put in place that reduces the driving speeds as compared to Google’s knowledge of official speed limits.
- Loading and unloading time. If this time needs to be accounted for, there are individual inputs for each in separate columns.
2) Additional columns have been added to calculate:
- Total estimated time and prices for both one-way and round-trip projects.
- Breakeven rates – calculating what the price points would need to be to make $/hour and $/volume exactly the same.
- Savings / Cost Avoidance – showing the total difference between the $/hour and $/volume options if the two rate structures are offered. If you are working at optimizing costs based on choosing the right options, this allows you to determine what the savings or cost avoidance opportunities are.
3) A totals section for when you want to add up all the items in the analysis table. This is important for those who populate the model with a series of stops and want to add up the cumulative distance, time, and associated costs.
At the end of the day, this model could be reconfigured in a number of ways to calculate other factors such as mileage ($/km), wait times, different rates for driving vs waiting or loading, and so on. The possibilities are nearly endless.
If you found this example interesting, insightful, or inspiring please consider liking it or sharing it with your network. If you want to know more about how these methods or tools could add value to your business, please send me a note on LinkedIn. I would love to hear from you.
#BusinessIntelligence #BusinessDevelopment #Procurement #Sales #VBA #Excel #FluidHauling #Trucking #Oilfield #Transportation #Logistics