Are you interested in learning about how to manage inventory for eCommerce business, specifically one working through drop ship relationships?
I’ve been running my own Amazon Marketplace store for the past 8 years and all of my supplier relationships have been through the drop ship business model. When I was first starting in college, I had no idea what it would take to manage all of the inventory from the suppliers that we were creating relationships with, but over time I learned the ins and outs and became an expert at the process.
I must say that managing inventory for your eCommerce business is one of the most tedious processes when you are using drop shipping. It takes an amazing amount of focus in order to ensure that all of your products are in stock and ready to ship according to the data from your suppliers. At the peak of my Amazon business, my team and I were working with over 1,000 drop ship suppliers and managing over 1,000,000 products on our Amazon store. It was no joke and if we weren’t doing it right, it could lead to bad sales and angry customers.
In this article, I will break down what it takes to manage inventory for your eCommerce business when working with drop ship suppliers. I’ll explain how data is exchanged between you and the supplier and the system that you should set up in order to properly manage your inventory and make sure that all of your products are accurately displayed for sale.
The System to Manage Inventory for your eCommerce Business with Drop Shipping
First, let’s set the scene. You are a relatively new eCommerce seller (maybe just starting your store or you’ve been in business for 6 months) and you’re setting up the systems and processes that will allow your store to run efficiently without you involved every single minute.
You’ve formed a relationship with a reliable supplier that has the ability to drop ship and you’re getting your inventory methods set up so that you can make sure that the products you are listing on your store are always in stock and ready to ship. You’ve received the initial product data and inventory levels from your supplier and you’ve taken the time to upload them to your online store. The product listings are looking good with content, images, and even some video tutorials, and you’re ready to push them live.
Inventory Update Sheets
Through speaking with your supplier in the initial relationship formation steps, you’ve learned that they send inventory updates every week. That means that their products move fast enough that they need to tell their network of sellers which are in stock still, which are out of stock, and which are close to going out of stock. They’ve sent you a sample spreadsheet displaying the inventory updates and here is what it looks like:
For your reference, inventory update sheets can come in many different forms. Here is another example:
As you can see, some suppliers will send inventory updates with the actual stock level that they have in their warehouse while others will simply give you a Y or N to indicate if it is in stock. Both of these sheets also show estimated “back in stock” dates for the out of stock products, but that is more of a luxury. Most suppliers will not provide that simply because they don’t have their inventory and warehouse organized enough.
Regardless of how the inventory update is received, it is your job to make sure that the data is properly mapped to the products listed on your store so that out of stock (OOS) products are pulled down and in stock products are up for sale. In this next section, we’ll dive into the mechanics to make that happen with Excel spreadsheets.
How to Update Inventory Levels from Supplier Stock Updates
Now that you understand how you will be receiving inventory updates from your supplier, you must figure out the best way to compare their sheet to your master file so that you can see which products are in stock and which are out of stock. This is a key step to managing inventory for your eCommerce business when working with a drop ship business model.
Depending on which platform you are selling on (Amazon, eBay, Shopify, WooCommerce, BigCommerce, etc.), your master inventory file will be different, but they should all have common pieces of data that you’ll need to update your inventory status for each product.
As seen in the screenshots above, the supplier will most likely supply you with at least the product SKU when providing inventory. The product SKU is a unique identifier that is specific to a particular product, meaning that no other product from their supply of goods will have the same SKU. In order to update your inventory in bulk, the SKU becomes your best friend.
V Look Up in Excel or Google Sheets
To properly update the inventory levels of your current products on your store, complete the following steps.
- Download your full inventory from your selling platform. Find the area where your inventory is displayed and find the proper way to export the inventory file. Most likely in CSV or Excel format.
- Open the file, filter out all products for this specific supplier that you are updating, and delete the rest.
- On the left hand side of the file, place the Product SKU and the current inventory status, as it was when you downloaded the master file. Keep the product name and remove the rest of the information.
- Open a new tab in the Excel file that is blank. Navigate to the supplier update file (as depicted above) and copy the information from that supplier update file. Paste it into the new blank Excel tab in the file where your master inventory is.
- For each tab, select all and sort by Product SKU so that the lowest is at the top and the highest is at the bottom. Like I said, do that for both tabs. The reason we do this is to make the V Look Up formula as effective as possible.
- Go to the main tab of the Excel file. In a blank column next to the old inventory information, enter a V look up formula. It looks like this: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For more advanced tutorial on using Vlookup, search on Youtube and you will find hundreds of examples of using Vlookup. You can probably even find one specific to eCommerce.
- After running the Vlookup formula, you will have updated inventory data for all of the SKUs for this particular supplier.
Upload new inventory data to your eCommerce platform
Once you have the new inventory data sorted out to the SKUs from that supplier, you want to go into your eCommerce platform (Amazon, eBay, Shopify, WooCommerce, etc.), and create a file that allows you to bulk update products on your store.
Fill out the product update file with the SKU, the new inventory levels, and any other information that the platform requires. Upload the file to the platform and wait for it to say that it has been accepted. Make sure that there are not any errors when the file is completed. If there are errors, open the file, make adjustments and upload again.
Your inventory changes have been successfully uploaded!
Once you’ve submitted the file to your platform and have received the confirmation that all SKUs have been properly updated, you can rest easy. Your inventory is up to date…for now.
With a drop ship business model, inventory changes are a daily occurrence (depending on your supplier network) so it is of utmost importance that you have a system and set of processes like this documented for your team. Ideally, you will be the one to forge the path towards the optimized process for updating inventory and then you will hire an eCommerce freelancer to manage it for you so that you can focus on other growth initiatives of the company.
The process of managing inventory levels for a drop ship business model can be an extremely tedious task that must be performed every single day to ensure that you are not selling products that are out of stock. If you don’t take the time to create your system and build strong relationship with your supplier, your store will suffer from inventory issues, you will have angry customers, and you will be pushed out of business by competition. I’ve seen it happen to sellers and it’s a common cause of failure in drop ship eCommerce.
This article and lesson serves as a real look into one aspect of running a drop ship eCommerce business. Whoever says that drop shipping is easy and that you can make millions overnight is absolutely crazy. Don’t listen to the advertisements and websites out there that just want to make a quick buck off you as a new eCommerce seller.
If you are planning to get into eCommerce through drop shipping, make sure that you read guides like this, speak with others who have built drop ship businesses before, and create your own set of processes so that your store can thrive in the long run.
Do you feel better prepared to manage your inventory for your drop ship eCommerce business? If not, let me know what questions you have in the comments!