Tag Archives: UPS

One Easy Excel Formula to Track Shipments

Wouldn’t it be awesome if a Microsoft Excel formula could automatically pull tracking information for shipments sent via UPS, FedEx, USPS, YRC, and other major carriers? Well – you can. Jimmy Pena from ShipTrackAddin.com has created a great Excel add-in that does just that. Best of all, it’s free (although the author does accept donations if it helps save you time and effort in tracking shipments).

How it Works

After a quick download and an easy install of the add-in, your Excel will have a new formula called ShipTrack. Then, you just use the ShipTrack formula in your spreadsheet just as you would use a SUM formula. For example, I would type the following into a cell:


After a second of grabbing the information from the FedEx servers, the cell would return this value:

Delivered Monday, January 13, 2014 at 5:53 PM at signed by MMORRALIS FEDEXGROUND

It’s as easy as that. If you have a long list of tracking numbers, you can just copy the formula down and it will populate them all instantly.

You can also use references in the formula, so the following would work just as well:



Carriers Included in ShipTrack

Perhaps the best part about the ShipTrack formula add-in is the long list of carriers it covers. The current version, 4.3.1, supports the following carriers:

Carriers Included in the ShipTrack Formula
  • A1 Express
  • A-1 International
  • Blue Dart
  • CEVA Logistics
  • Conway Freight
  • DHL
  • DHL Global Forwarding
  • Expeditors
  • FedEx
  • Lasership
  • New England Motor Freight
  • Old Dominion
  • Ontrac
  • Pegasus Logistics Group
  • Purolator
  • R+L Carriers
  • Rworld Couriers
  • Safmarine
  • SAIA LTL Freight
  • TNT
  • United Parcel Service (including LTL)
  • U.S. Postal Service
  • YRC Freight
  • YRC Regional (including New Penn, Holland, Reddaway)

Carriers are also being added regularly, so check the site to see if a specific carrier is in the works to be added.

Download the ShipTrack Add-in for Free

You can learn more and download the ShipTrack add-in over at ShipTrackAddin.com.

How to Ship to Walmart, Target, Walgreens, and Other Big-box Retailers – Part 3 of 3

Shipping to Big Box Retailers

Part Three – Shipping the Order

Congratulations, you’ve received your first order from a new big-box retailer. In part one and two of this series, I described how to bring an order into your system, pick it, and label the product properly. With all of that completed correctly, we move onto the final steps of shipping an order to the big-box retailer.

Specifically, this article will touch on whether to ship via small package or on a pallet, common customer shipping requirements, advance ship notices (ASNs), and paperwork you’ll want to keep for future reference. As with the previous two parts, this article is no substitution for your customer’s routing guide, which is the ultimate authority to any questions you have. Nevertheless, the topics below will help guide you and your team through the process and avoid many costly mistakes.

To Palletize or Not to Palletize?

One of the first major decisions you need to make when shipping your order is whether to ship via a small package carrier (FedEx or UPS), or on a pallet. If the order is large, then palletizing it is the obvious choice. However, when the order is small, that decision becomes less clear.

If the customer is paying for freight, then they likely have firm guidelines about when orders should go small package or palletized. Consult the customer’s routing guide; the rules vary widely for each customer. For example, if the order is over fifteen cases, or weighs more than 150 lbs. total, then several retailers require the order to be shipped on a pallet. However, others put the threshold at 20 or 25 cases, and as much as 300 lbs.

If you are paying freight, then the customer’s case count or weight limit often does not apply, and you can ship whichever method is cheapest or most reliable. However, several big-box stores will refuse shipments over a certain weight, even if you are paying the freight bill.

Small Package Shipping Considerations

When using small package carriers to ship, usually FedEx or UPS, you’ll want to consider several important points. First is the actual shipping label. Because small package shipments do not require a formal bill of lading, many retailers require the shipping label and packing list to include specific information. For example, the customer’s PO#, department, or order type may need to be printed into the specified fields on the label. Even if not required, entering them can help avoid mistakes at the customer’s distribution center (DC), so I recommend it whenever possible.

Another consideration is whether to band the boxes together or not. Often, banding small boxes together can save money with small carriers because they round up the package weight. Thus, two packages that weigh 4.1 lbs. each would cost the same as two 5 lbs. packages. Bound together though, the bill would charge for one 9 lb. box. While this method can sometimes save money, there are many retailers that will fine vendors that send them boxes bound together. Check with your customer, and run price calculations, to see if this method will indeed save you money, and if your customer will accept banded boxes.

Pallet Shipping Considerations

“A” Good Base

If your order is big enough, then you then have to consider the requirements for shipping on a pallet. To begin with, you’ll need a high-quality pallet to stack the product on. Most large retailers require #1 or Grade A pallets. Essentially, these are pallets in very good condition that have not experienced any damage and have all their original parts in strong, sturdy condition. Any pallets that have visible damage or missing parts are not Grade A and should not be sent to customers that require them. Some retailers accept two-way pallets (pallets that can only be picked up on the short ends, as pictured), but many require four-way pallets.

Stacking the Product – Pallet Height Limits

Once you’ve found a good pallet that will meet the customer’s requirements, stack the product on the pallet to evenly distribute the weight and support the product. Most retailers do not allow any overhang, so be careful to stack everything within the 40 x 48 inch space. As the product stacks up, you’ll want to consider pallet height requirements. Most large retailers limit how high pallets can be in order to maximize their DC’s racking space. Some retailers will publish this limit in their vendor guide, but often you must call the DC and ask what their limit is. This varies widely by retailer. The lowest I’ve seen is 55 inches (60 inches including the 5-inch pallet). Some allow as high as 96 inches. The most difficult customers are the few who have different requirements for each DC. CVS is an example of this. My team calls each DC to get a pallet-height limit, which sometimes changes. We could ship shorter pallets to all the DCs, which is what we often do, but sometimes stacking the pallet a bit higher can save us a significant amount in freight costs.

Once the pallet is built, be sure to securely shrink wrap it to protect the product while in transit. If the product is light, secure the product very well to the pallet with several layers of shrink wrap. This will prevent it from coming off the pallet when it is moved.

Some retailers require black shrink wrap, especially if the product is a high-value item. Black shrink wrap hides the product from those moving it, which can reduce theft or other problems.

Advance Ship Notices (ASNs)

As discussed in part 1, most large retailers communicate with vendors via EDI (Electronic Data Interchange). An increasing number of big-box stores are requiring  vendors to send advance ship notices (ASNs) when they ship orders. An ASN is an EDI document (EDI document number 856) that tells the customer how each order was shipped. More than just a tracking number, ASNs detail the carrier, tracking information, and ship date, as well as a detailed breakdown of how the order was packaged. Vendors usually require special ASN labels that show a serial shipping container code (SSCC-18) that is 18 digits long. These codes are unique to each package or pallet sent to a customer, and the customer’s receiving department can know exactly what is contained in the pallet or package by scanning the 18-digit barcode. One of the key benefits of ASNs is that they easily communicate such detailed information that they reduce the work load on the receiving end, as well as decrease opportunities for error. The label shown here is an example of an ASN label with the 18-digit barcode at the bottom. If your customer requires an ASN label, it will have very detailed requirements, which should further explain what you would need to put on the label. Additionally, many retailers recognize the limits on small suppliers, and many offer a free, web-based ASN system that allows you to print labels from the vendor’s website.

Scheduling the Shipment and Delivery

Preferred Carriers

If you’ve submitted routing for the order (as discussed in part 1), then you will likely receive instruction on which carrier to use. However, if you must choose your own carrier, you may want to check if the customer has any preferred carriers. Many have a handful of favorite carriers, which reduces congestion at their receiving docks and helps avoid errors from unfamiliar shipping companies. Some retailers will not accept shipments from carriers not on their approved list. Others will grant special leniencies if you use their preferred carriers. For example, if a shipment leaves your warehouse on time with a customer’s preferred carrier but still arrives late at the destination DC, some vendors will not issue a fine. Be sure to note which freight carriers to use and to implement a process that ensures only those carriers are used. Even if preferred carriers are slightly more expensive, they are often cheaper overall when considering the potential fines or issues with a problem shipment.

Delivery Appointments

Another requirement for many retailers is a delivery appointment. Usually, the trucking company handles the delivery appointment, often coordinating the delivery a day or two in advance or arrival. However, some retailers require you to call and schedule the delivery before you even ship the product. In this case, be sure to communicate clearly with the trucking company about the scheduled appointment.

Signed, Sealed, Delivered

Packing Lists and Bills of Lading

With the order correctly labeled and packaged, you’re ready to hand over the product to the freight company. Your job is almost done if you’re using a small package carrier, but you still have a few more important steps ahead if you are shipping a palletized load, most involving paperwork. The packing list should go in a marked pouch on the lead carton so that the receiver can quickly find it. In addition, many retailers will require you to put specific information on the packing list and bill of lading. Be sure to follow all these requirements to avoid chargebacks and fines.

A Foolproof Paper Trail

A common problem when shipping to large retailers is loss or damage of product when they receive it. Sometimes the product really is lost in transit, but other times the receiver may miscount your product when receiving it. When this happens, the customer will usually take a deduction from your invoice or fine you through a chargeback for the product that they did not receive. You can still obtain the money from these deductions or chargebacks if you have the proper paperwork, signatures, and information. Specifically, place a seal on the trailer and record the seal number, and instruct the driver to count your product and sign accordingly on each bill of lading. If you are having frequent problems, take detailed pictures of your loads when they leave for additional proof that you shipped the order complete. Recording all this information will help you easily dispute chargebacks or file freight claims with carriers. If you have all your paperwork in order, then in most situations you will likely receive compensation from either the customer or carrier.

Tracking the Order

The final step in the process is tracking your order and ensuring delivery. Tracking orders helps to find delays, which can often be resolved quickly by calling the carrier. Most retailers are much happier when you call and inform them that the carrier will be a day late, but that you have resolved the issue, than if they have to call you two weeks after the product should have arrived asking where it is. Once the product has delivered, print and file a copy of the proof of delivery (POD). Sometimes large retailers will fine you for shipments that arrived many months ago. To prove that you fulfilled the order, you will often need a POD. However, many carriers delete and purge their records after 9 to 12 months, so without a printed copy, you may never be able to prove delivery. Although most PODs will just sit in a  file, the few that you do need will often justify the cost of tracking each shipment to your larger customers.

Final Thoughts

Shipping to big-box retailers can require a lot of effort and staff. This three-part series has given an overview of how to receive an order into your system, prepare and label the order, and ship it to the destination. Always keep in mind that the goal is to move your product efficiently through your customers’ supply chains and into consumers’ homes. Most of the customer requirements have this same goal in mind – even if they sometimes feel like hoops to jump through. You should consider adding to your standard fulfillment process anything that reduces opportunities for error, improves efficiency and accuracy, or helps your customer’s team correctly receive and process your product. By collaborating, sometimes even visiting, with your customer’s receiving team, you may come up with additional ideas on how to better move your product through the supply chain.

What experiences do you have with shipping to big-box retailers? What additional questions do you have? Although my team has shipped to several dozen large customers, we also are always looking for advice and opportunities to improve.

Please leave your success stories or questions below in a comment, and click here to receive future Supply Chain Cowboy articles by email.

How to Ship to Walmart, Target, Walgreens, and Other Big-box Retailers – Part 2 of 3

Shipping to Big Box Retailers

Part Two – Preparing the Order

You’ve received your first order from a new big-box retailer. In part oneof this series, we described many of the steps necessary to bring an order into your system correctly as well as guidelines for requesting routing. Now we’ll head out to the warehouse and examine the steps in preparing an order to ship. This article will give an overview of some important steps to consider while getting your product ready to ship. You’ll always want to refer to the customer’s routing guide as the ultimate authority on fulfillment requirements. However, considering the topics below will help you avoid chargebacks, improve processes, and become a preferred vendor to your big-box customer.

Picking the Order

Depending on your system, the first step to picking the order is likely printing a pick sheet. The ideal pick sheet should allow anyone to walk off the street into your warehouse, and with no training at all, be able to pick an order correctly from the information on the pick sheet. I sometimes test a company’s pick sheet by asking someone from another department to pick an order and see what questions he or she asks. Getting to the ideal pick sheet level requires feedback from your team, correct information in your system, willingness to change, and frequent experimentation. One issue to pay particular attention to with the order-picking process is the product’s packaging configuration. Specifically, different big-box retailers often require you to ship the same product in different inner pack, master carton, and assortment quantities. When one of your customer orders four cases of product, that could mean four cases of 48 units to CVS Pharmacy and four cases of 144 units to Walgreens. Your picking system must therefore provide the correct detailed information to distinguish the different configurations and help your team always pick the correct configuration that the customer ordered. Specific bar codes for each configuration, which we will cover in more detail below, can help increase picking efficiency and accuracy. Another issue that many small to medium businesses encounter is the control of pick sheets. Duplicate or missing pick sheets can cause significant problems in your order fulfillment process. As a solution, many small businesses restrict pick sheet printing to just one or two people, who then have complete ownership over the task. Alternatively, you can build a dashboard or other automated system that prevents duplicate picking or missing orders. Eventually, when you can afford to implement a robust warehouse management system (WMS), this issue becomes less troublesome. In addition to avoiding mistakes, you will also want to organize your operations to pick orders quickly. When you are shipping to several large customers, group items in your warehouse based on the customer for whom they are scheduled to ship. For example, if you are shipping six different products to Target, place a pallet of each product in six consecutive picking locations. With your products located accordingly, each Target order is quick and easy to pick. Even if the same product is also shipping to Walmart, stocking it in two locations can greatly simplify the picking process for each of your customers. Positioning products’ pick locations in the same order they appear on the pick sheet is another easy step to decrease errors and simplify the picking process. Once the orders are correctly picked, the next step is to ensure they are labeled according to the customers’ requirements.

Labeling the Product

Ensuring that every label is correct and contains all the necessary information a customer requires is often a difficult task. Many customers are quite strict, charging large fines if labels have incorrect or missing information, are incorrectly placed, or are unreadable. Unless your labeling system is completely automated and you’ve never experienced a problem, ask two people to check all labels. This includes checking to make sure they are placed correctly, scan well, and have all required information. If a customer is particularly harsh in fines and chargebacks, use a written out checklist for labels and have at least one or two other people sign off on the labels. The purpose of labeling product is to process products efficiently and accurately through the supply chain. Most customers require the same basic information on their labels; often you can use the same or a very similar label for various customers. The following are some specific information that customers often require be included on product sent to them:

  • Item Number
  • Item Description
  • Item Bar Code (Either 12-digit or 14-digit)
  • Ship To and Ship From Locations
  • Company/Manufacturer Name and Address
  • Customer PO Number
  • Customer-specific Item Number
  • Number of Cartons
  • Country of Origin
  • Dimensions and Weight
  • Expiration Dates (Perishable Products)
  • Lot Numbers
  • Case pack details (number of inner packs and eaches)

In addition to the above information, some customers require order-specific labeling with information sent to the customer at the time of shipment. These labels include information used in advanced shipment notices (ASNs), which part three will cover. Often, big-box retailers will show an example label in their routing guide. These labels are usually four by six inches, a common size frequently printed by thermal label printers. If you don’t yet own a label printer, talk with your FedEx or UPS representative about using one of theirs. If you ship a steady volume with either of them, they can likely loan you one or more label printers, which will save you some money.

GTIN Prefixes and ITF-14s

Most big-box retailers require you to define bar codes in their systems that designate how your product is packaged. Each product configurations has its unique Global Trade Item Numbers, or GTIN, prefix that defines the package configuration. This two-digit prefix goes in front of the product’s normal 12-digit UPC to create a 14-digit bar code called an ITF-14. For a product that has a UPC of 123456789999, a GTIN prefix of 10 (ITF-14 of 10123456789996), may designate a pack of six. Each Inner Pack and Master Carton Configuration needs its own GTIN prefix defined. While defining prefixes is a straightforward process, there is one exception. The 90 prefix is reserved for variable weight items, such as food, and is often not to beused on standard weight items. In addition, if you have more than eight configurations of a single product, you can then proceed to additional prefixes, starting with 11, 21, etc. The following table gives an example of how one product may have multiple product configurations that utilize the same base 12-digit UPC.

Description GTIN Prefix ITF-14 Bar Code, Including End Check Digit
Individual Item None (UPC Only) 123456789999
Inner Pack of 3 10 10123456789996
Master Carton of 144, with 48 Inner Packs of 3 20 20123456789993
Inner Pack of 6 30 30123456789990
Master Carton of 144, with 24 Inner Packs of 6 40 40123456789997
Master Carton of 48, with 16 Inner Packs of 3 50 50123456789994
Master Carton of 30, with 5 Inner Packs of 6 60 60123456789991
Master Carton of 288, with 48 Inner Packs of 6 70 70123456789998
Pallet containing 55 Master Cartons of 144, with 24 Inner Packs of 6 80 80123456789995
Used for variable weight items such as food, often not allowed for standard weight items 90 90123456789992
Pallet containing 55 Master Cartons of 144, with 48 Inner Packs of 3 11 11123456789995
Pallet containing 165 Master Cartons of 48, with 16 Inner Packs of 3 12 12123456789994

You will want to work with the retailer and your own internal system to set up all these configurations and ensure your product is labeled accordingly. GS1, the creaters of the GTIN system, have a website that provides  a more in depth description of how to calculate the check digit, as well as additional details about the GTIN system.

Promotional and Other Labeling

Depending on the type of order your customer sends, your product might also require special promotion labels, colors, and/or icons. For example, a promotion for Easter may need light blue lines along the outer carton, a picture of an Easter egg, and the words “Eater Feature, Set the Week of Feb. 20.” This type of labeling helps the employees at the final destination store quickly set the product out on the store’s floor. Even if such labeling is not required, I often recommend including a sticker with display instructions, if the customer will not fine you for doing so. Often, that extra label helps get your product out of the back stock room and in front of consumers sooner, generating better sell-through and more reorders. In addition to promotional labeling, you may also want to add other labels to help operations at your customers’ distribution centers (DCs). Big-box DCs process millions of units each week, so anything you can do to help them correctly process your product will help both of you avoid a multitude of problems that occur when product is poorly labeled. Once again, refer to the customer’s routing guidelines, but if allowed, using bright, colored labels to call out the lead carton that contains the packing list or partial cartons will help the DCs avoid mistakes. A simple rule of thumb is that if an eight-year-old would have questions about of how to receive your product, then your labels could be easier to understand. Sometimes it may seem like overkill, but if it helps avoid chargebacks and gets your product in front of consumers quicker, then it’s likely worth the extra effort.

Concluding Thoughts

Crafting strong processes with your people and systems is essential as you gain new customers. Checklists and clear direction will ensure you fulfill big-box orders smoothly and without errors. When you bring on a new customer, download their entire vendor routing guide and read through it completely. Make a detailed list of special requirements. Then, take that list, and create a system to ensure all those requirements are fulfilled and verified. You could start by condensing the list into a checklist, and program that checklist to print on each pick sheet for that customer. When that customers’ orders print, you and your team will know right away what to do for that customer. While this method works well, there’s also many other ways to fulfill this need. What’s important is putting a system in place, experiment on improving it, and contacting the customer with questions when guidelines are unclear. The end goal is to get your product into consumers’ hands, so whatever creative solutions you can invent to accomplish that are worth your effort. Above, we explored several parts of the fulfillment process involved with preparing an order. In the third and final article of this series, we’ll complete the cycle by shipping the product. What experiences do you have with preparing shipments for big-box retailers? What additional questions do you have? While I’ve definitely seen mistakes, I would love to share any advice that will help you get it right. Please leave your success stories or questions below in a comment, and click here to receive future Supply Chain Cowboy articles by email.

71lbs.com Review – Stop Overpaying FedEx and UPS with 60 Seconds at 71lbs.com

I recently recovered $284 from a FedEx shipment without doing anything at all. Here’s how you too can save money on your FedEx and UPS bills.

71lbs Service Logo

Several weeks ago, I stumbled across a freight auditing service called 71lbs. Intrigued by the service’s promise, I did a little research to find out exactly what the company did. Basically, 71lbs combs through your FedEx and UPS accounts to find late small parcel shipments. When they find a late shipment, 71lbs requests a refund on your behalf. You get that shipment charge deducted on your next UPS or FedEx invoice, and 71lbs invoices you for half of the refund.

Sounds like a great service, but since they are a relatively new company, I wanted a few more details before signing up. My first concern was a service fee. I’m familiar with a few similar services, but they usually require a monthly fee whether they find anything or not. Other service’s monthly fees usually outweigh the potential gain if shipping volumes are low or everything delivers on time. To see whether this service was indeed free, I dug a little deeper into their business model.

I read 71lbs’s entire user agreement, but just to make sure, I also emailed the company. The CEO, Jose Li, responded personally to my questions. He guaranteed me that the only charge my company would ever receive was half of what we saved. Additionally, he told me that if I was not satisfied for any reason, I could simply send an email and they would cancel the service – there was no long-term contract. I went ahead and signed up my company’s FedEx account for 71lbs’s service.

Now I certainly cannot say that your business will experience the same quick results that mine did, but I received two emails within two weeks of signing up explaining successful refunds that 71lbs secured on my behalf. One was just a $17 domestic shipment, but the other was for a 26-piece shipment to Germany worth $569. Of course, half of those refunds will go toward 71lbs, but that’s still $293 in my company’s pocket that we would have paid to FedEx. I see it as a definite win for me, a win for my company, a win for 71lbs. It’s also a bit of motivation for FedEx to increase its service levels.

Now to be realistic, these two shipments were quick successes that represent far less than 1% of the shipments my company sends out. Certainly, I wouldn’t lower my freight budget forecast because of this service – at least not at first. Nevertheless, a quick and easy setup has the potential to save money you weren’t expecting.

71lbs is also working on a few other services that could help your business even more in the future. One is a service that audits your FedEx and UPS invoices to make sure the pricing matches contract pricing. I see huge potential savings for this, since I often have to dispute pricing errors. Additionally, I asked Mr. Li if 71lbs could notify me the morning a shipment is late so that I can contact the customers and manage damage control for a late shipment. He responded, “I know exactly what you are being faced with. I’ll add it to our product pipeline and will get back to you when we have it done.” I look forward to what other great services 71lbs creates.

Update Since Posting

My company is a 71lbs customer, but I did not receive any compensation for this review. I think 71lbs’s service is a great add-on for any business wanting to lower small parcel freight expenditure. A year after posting this review in Sept. 2012, 71lbs began advertising with Supply Chain Cowboy. As an update, my company saved over $2000 in the past year from their service.

Questions or comments? Please join the discussion below.