In 2019, I built a mini Warehouse Management System (WMS) to use on a kitting project. Kitting, in the Logistics world, means preassembling individual items into ready-to-ship kits instead of picking and packing those individual items as orders are received. To manage this project, my boss suggested we use spreadsheets. Well, I must have heard him wrong. Because instead, I built a mini Warehouse Management System.
To do so in a relatively short period of time, I used templates for the front-end and focused heavily on the back end.
A Web App in a Real-World Scenario
In late August of 2019, our client, GE Lighting, hired us to help them with a mass roll-out of some of their products to a national hardware store chain in Canada. The national retailer wanted to launch simultaneously across all their stores as part of a sale. This sale’s start date was set to roughly two months out, and they had already sent the flyers out to get printed for the sale to start mid-November. Our client had a very short timeframe to get this done considering the logistics involved.
Scope of the project
Our client had to manufacture about 240,000 cases of different bulbs, fixtures, and accessories. They had to then ship them to us, from various distribution centers in the US, beginning in mid-October. This was the earliest they could get the product to us. We had to keep track of all inventory as it was received and report all receipt tallies back to the client. Then, once we had enough product, we were to start the kitting process.
There were four different kits that the retailer requested in order to accommodate their varying store types and sizes. Some bigger stores required all four kits, whereas others required some combination of them or even just a single kit. The retailer was a major new customer for our client, so naturally, they wanted to impress them and ensure 100% order accuracy. We were therefore tasked with auditing 100% of all kits once they were built.
Finally, in the first full week of November, we were to load the kits in a predetermined order, based on transit times. This was to allow the carriers to deliver on time and at roughly the same time throughout the country. The retailer didn’t want the product to sit in the back but rather wanted to stock their shelves directly the day before the sale started.
The Challenges
The challenges we encountered and discussed with our client were plenty. How would we keep track of inventory? How do we track each kit we built? Will there be enough product for each one? How will we know which kit is short when we load the trailer and have to provide a manifest? Of course, the overwhelming answer to each question was to use spreadsheets. I hated the idea as soon as it was suggested.
Given the scope of the project, which I’ll touch on below, I realized that Excel just wouldn’t cut it. But Excel was the one thing everyone agreed on. And with spreadsheets as prevalent as they are in business today, it’s no wonder that Excel is usually among the first tools we reach for. However, while I view spreadsheets as a good starting point, I also realize that they are not ideal for more than just storing data and creating reports.
Seeing is believing
I suggested we use an existing Warehouse Management System that we already had at our disposal. However, none of them were designed for kitting operations. Given that we only had a few weeks to get the project started, it was simply impossible for the IT team to reconfigure one of the existing systems. But I was still very much against the idea of running with spreadsheets. So I had to come up with a different solution.
Basically, everyone (but me) was in agreement that as the product rolls in, we’d log it on a spreadsheet. As we build the kits, log them on a spreadsheet. How do we audit? Also via spreadsheet. The client, actually, created an audit sheet for this. Fancy as it was, we needed to find a 100% accurate kit to log into the audit sheet as the master. Then we’d rebuild each kit of the same type, logging each one into the audit sheet which in turn would compare it to the master and highlight errors.
I could foresee the issues that could have arisen and was very much against using spreadsheets preferring to use a custom application instead. And, therein lay my biggest challenge. How do I convince everyone, including my team and the client, that what they were comfortable with, spreadsheets, was the wrong tool? Seeing is believing, so I decided to create it first and then show them.
Spreadsheets just would not do
The project was budgeted for 10 associates to work simultaneously during its peak. There were about one hundred different SKUs, one hundred and forty different stores, and 4 different kit profiles. The limitations of spreadsheets became very apparent very quickly. Managing this with Excel, within our short window, would have been a nightmare. Spreadsheets are limited, even being able to share a sheet, so web apps take the cake. When project requirements exceed the capabilities of spreadsheets, web apps are the next logical step up.
Note: I wrote a post diving deeper into the benefits of web apps over spreadsheets which you can check out here.
The Easy Part
I began by drawing out what the project required of a warehouse management system on a whiteboard. To start, we need a way to track inventory coming in. Since we will receive palletized products, each pallet will get its own unique ID tag. Building this receiving functionality was the easy part. It is
The client had provided us with the list of SKUs along with sizes, weights, descriptions, and so on. So I used that spreadsheet as a starting point and modeled the first database table after it to hold the SKU info. A second table will hold the receipt info with pallet ID, SKU, and quantity. And with those two tables created and loaded, I could build the user interface to allow associates to receive inventory. Page by page the system began to take shape and we were able to start receiving inventory.
The (not so)Hard Part
So, we need a way to keep track of which SKUs are already kitted, which kits are missing items, and what they are missing. You know, the actual kitting functionality. This is the part that I struggled with initially. I knew how I wanted the process to work for the associates but was not entirely sure how to translate it onto the database.
For the associates, the process should be straightforward. For the kitting function, the associate will scan the pallet ID of a full pallet. The system will then tell them the next kit to go to that requires the same product. Then, the associate needs to scan the UPC and place the item onto the kit. Then, scan the kit ID, to confirm the item is on the kit. Consequently, the system then tells the associate the next kit in line that requires the item they have.
I had not spent much time thinking about the inner workings of a warehouse management system. So naturally, working out the intricacies of how the pieces need to move in the back-end was a challenge. In fact, it took me a few days to work it out. I created a table to store the kit ID along with the SKU, quantity required and quantity kitted. Then, it’s just a matter of duplicating the kit configuration for each kit ID. Marking an item as kitted would then be done by incrementing the kitted quantity on the kit ID by 1.
Do you want to learn how you can create an app yourself?
Take a look at my ultimate guide on how to create an app. There I walk you through the step-by-step building process of how to build a web app. I’ll walk you through my process while showing you how I created a functional web app. A web app to help restaurants with Covid-19 reopening. Take the next step and learn how to create an app for yourself.
Don’t miss out on new posts! Subscribe below and we’ll notify you of new posts! I hate SPAM just as much as you do (if not more) so I promise not to spam your inbox. And, you can easily unsubscribe at any time.