Easy Excel template for delivery route planning with Shipday
Properly planning routes in Excel can help your delivery drivers get your customer’s orders to them much faster and with less hassle, maximizing employee and customer happiness at the same time.
This article will give you a headache-free way to map out, plan, and organize your delivery routes so your business can ship on time, deliver on schedule, and thrive.
We’re going to break down the whole process step by step, using Shipday and Excel, so you can quickly put the planner together, optimize your routes, and get back to building your business.
First, let’s review some of the things we need to consider when planning a delivery route and what we need to think about when optimizing that plan.
How do you plan a delivery route?
Your business needs to consider multiple factors when planning a delivery route including vehicle types and capacities, customer preferences, travel time, and travel costs.
Let’s look at each of these in more detail:
- Vehicle types and capacities
You need to consider what kind of vehicles you have in your fleet and whether or not they can carry the number of shipments that you have for them on that route.
- Customer preferences
Depending on when your customers want their order and how they want it delivered you might need to adjust your delivery route according to those preferences.
For example, if a product needs to get to a customer around a certain time, you might need to adjust the route to fit the customer’s needs.
- Travel time
Travel duration is another valuable consideration. If you know a driver is going to be out all day on a particular route, that driving time needs to be taken into consideration when looking to assign other routes.
- Travel cost
Cost is a major factor in planning and executing a proper delivery route. You need to plan routes that are most cost-efficient, meaning leveraging the location of your driver to deliver as many orders the driver can within that particular locale before moving on to another.
How do you optimize a route?
Ordering and planning for optimal routes involves weighing each factor that might affect your route and then planning for the fastest, the shortest, and most cost-efficient way to get your product to its final destination.
For example, you might think a short route will save time, but the shortest route is not always the fastest route. Sometimes a drive along a highway, even though there might be more distance between one location and another, is faster than traveling busy city streets
Considering the entire route with the multiple daily routes you have scheduled during a day takes planning. This is where Excel and Shipday come into play.
How do you create a route in Excel?
Creating a route in Excel involves filling in vital information like the customer contact information, the date and time of the delivery, and the total number of items the driver will deliver.
Shipday makes route optimization easy. Shipday’s route optimization software not only helps you create your Excel route planner, but it also helps you optimize your routes.
If you haven’t already, sign up for your free Shipday account or sign in to an existing account.
Great, welcome aboard!
Now let’s show you how to download your bulk order Excel sheet, fill it out, and upload it back onto your Shipday account. If you’re just trying out Shipday for the first time, you can use the sample file for this demonstration. Otherwise, if you already have orders loaded up, let’s get them planned.
1. Choose the Delivery Only option in Shipday
Before we begin, let’s make sure your business type is set up correctly to use the route optimization feature. Click the circle in the upper-righthand corner and go to Settings.
In Business Settings, look at the Business type.
You’ll see two options, Pick Up and Delivery or Delivery Only. You’ll need to choose Delivery Only and input your business’s phone number and address. Delivery only means that your driver is always and only picking up orders from your location. This could be for restaurants, meal prep, or e-commerce deliveries.
We assume that the route will start and end at the same place, which is your business address. Drivers will pick up the order, drop off the order to the customer, and then come back to you to pick up more orders.
Route optimization will only work correctly if you are set up as a delivery-only business, and you fill in your information. Make sure you save your settings at the bottom.
2. Download Your Bulk Excel Sheet
Now you are ready to download your bulk order Excel sheet. To do this, click on the orders tab and then the Excel upload button.
3. Fill in your Excel route planner
Once you are on the screen, click download, this will download the Excel sheet to your computer. When you open up the file, there are several columns you’ll need to fill in.
They include the following:
- The order number, which you want to make different for every customer.
- Customer contact information, including email (optional), name, and phone.
- The delivery address column (if outside the US, add your country here)
- The delivery date and time (optional)
- The total number of items. If you are a meal prep business, you can simply put meal prep for every order. Keep in mind the capacity of your drivers’ cars. If your driver only has enough space for 10 boxes, then that will help you get an understanding of the total price. This information is necessary for uploading into Shipday.
- The delivery instructions tab. This is where you’ll put an apartment number, a gate code, a call me when you arrive or something along those lines.
Now let’s look at what a completed order form should look like. As you can see below, every column header has information filled out beneath it except for the customer email and delivery time. Both of these tabs are optional and not needed for your order to upload successfully onto your Shipday account.
4. Fill out your route planner faster
An easy way to fill out this Excel sheet fast is to copy and paste similar information to all of your orders. For example, if your delivery date is the same for every order, you can type that date into the first two rows. It has to be both rows or this won’t work.
You click on the first one, hold shift, click on the second and then drag that little plus sign to the end of the list. This will copy and paste the date onto every single order and save you some time. You can do the same for item description and every other column.
5. Upload your route planner into Shipday
Make sure you find your way back to the bulk order upload page. Go to the Orders tab and click on the Excel upload button. Once you are on this screen, click upload and find the file that you just created. Hit save and your Excel sheet will start uploading to your Shipday account. This should only take about a minute after you’ve successfully uploaded your bulk order sheet.
6. Look at your orders
In the Orders tab, you can now see a few different things–the customer’s name, the distance from your business, and specific order details. In the order details, you can see all of the information for that order and any specific delivery instructions extracted from the columns in your Excel route planner.
Also on the top left, you can select all of the orders and assign them to a driver or delete them. You can also do this by clicking on them individually.
How to optimize routes in an Excel route planner?
To optimize the routes you put into your Excel route planner, use Shipday to turn on your routing optimization settings, create routes on the interactive map, and assign those routes to drivers.
Route Optimization is difficult to achieve. It’s a problem that mathematicians have wrestled with for a very long time. Given several addresses you need to deliver to and various distances between these destinations, how do you find the route that is the shortest or the least amount of travel time for your drivers?
As you’ll see Shipday solves this by having you draw a cluster around addresses you want to turn into a route on an interactive map. Once the cluster is defined, then our software can easily find the optimal sequence for your drivers to deliver within each cluster.
The result of Shipday’s route optimization? Our route optimization saves you time and money and gets shipments to their destinations faster. With Shipday, your business decreases things like travel time and vehicle and gas use and increases employee efficiency and customer contentment.
And Shipday makes this optimization process so easy. Let’s find out how and optimize our first route.
1. Go to the Map in Shipday
If you go to the Map, you’ll be able to see all of the destinations of your orders.
The orange pin is the pickup location (your business) and the red bubbles are the delivery locations (your customers).
2. Turn on the route optimization setting
Click settings and go down until you see the routing option. Let’s turn that on
You can optimize by distance or travel time. After you set up your routing setting, make sure you go to the bottom and click save. Then go back to the map, and you can start creating routes.
3. Create Route Clusters
Two tools should pop up at the top of your map now that you have your routing optimization turned on. The first one is the hand tool, which just lets you move around the screen. The second tool is the shape tool, and this will allow you to add little points to create a route cluster.
If you wanted to make a route for these orders in the top left, you want to place points around them. Make sure you connect all of the points, especially the last one.
You need to have an enclosed shape for this to work. As you can see, when you create this cluster, this box will pop up. It gives you the length of the route, the estimated time it will take to deliver every order, the total number of orders, and the driver’s name. We haven’t assigned a driver to this cluster yet. Let’s do that now.
4. Assign Your Drivers to a Route
When you click on one of the destinations, a box comes up that prompts you to assign a driver. Click on Assign, and you’ll see a list of available drivers.
When a circle turns blue, that means you’ve assigned the driver to that destination. Click on one of these blue circles and you should see the driver’s name.
5. Optimize the route on the driver’s Shipday app
We recommend your drivers turn on Auto Order Accept so that when you assign them several orders at once, they won’t have to manually accept each one.
In the app, the driver can find Auto Order Accept by clicking on the hamburger menu and then going to settings> preferences. Click the toggle so that it turns green.
The driver also needs to click the top right button and choose Optimize Route. This will change the list so that orders are optimized and match what you see on your ship day map page.
The driver can find Optimize Route by going to the route options here:
Then they should choose Optimize Route from the popup menu.
From that same screen, we also recommend that the driver sends a notification to the customer so that the customer can expect their arrival. Immediately, the customer will receive a text message.
It will give them a link to a tracking page with an estimated delivery time and an option to call or text the driver.
With that, optimization is complete, and you’re ready for business.
Conclusion
Congratulations! You’ve successfully created an Excel Route Planner and optimized it with Shipday!
Whether you have a team of delivery drivers or just an individual driver, Shipday provides essential and advanced features to move your local deliveries forward, get your brand in front of your customers, and give them a delivery process they’re going to love.
Learn more about how our robust route planning tool can help you by signing up for one of our live webinars where we’ll discuss use-cases, share some of our customers’ success stories, and give you a live demo of the Shipday product.
