March 1, 2020

The Troubles of Inventory Control

For the first few years our Manufacturing Team operated out of massive, equation-and-macro-filled google sheets that contained the full manufacturing operations of the business (as most startups do!). This included every detail from the orders that came in and the inventory and manufacturing details of each part. As we grew, our google sheets could no longer keep up with the complexity of our operations. Employees would spin off copies of their own sheets to process what they needed for their daily functions, which caused major data-integrity issues. The risk with not having a system working correctly is that we are never sure if we are about to run out of a crucial part or if we are stocking way too much.

Fortunately the same problem exists for all manufacturing companies. The solution is to use MRP Software (material resource planning software). An MRP is a production planning, scheduling, and inventory control system used to manage manufacturing processes. After numerous internal needs acquisition activities and assessing the multitude of software packages available, we settled on a piece of software: MYSIS.

MYSIS was set up to track the full relationship between parts, vendors, and all manufacturing processes. In addition it also was set up to help us manage the relationship between all the parts and processes that make up the WAZER. This is accomplished by establishing a parent-child relationship between the parts in WAZER. This creates a tree structure that captures the complete machine and how it is made.

The parent-child structure is used to understand, for example, how many low pressure pumps there are in the tank and then how many there are in the whole unit (or how many crimping operations we need to order from our connector vendor, etc.).

The MYSIS MRP should have allowed us to maintain accurate counts of our inventory, enter our sales forecasts and have the software spit out all the parts we would need to order for the next manufacturing batch. As you can guess, it didn’t go as planned. Not only was the software expensive (five figures upfront plus annual service fees) it also took hundreds of hours to get all of our information into the system. From there the following problems plagued us:

The complexity of the program required almost full time management from a senior employee, even just to do the most basic of tasks or to train an employee on a certain process
Simple updates to the part information would take hours for the engineering and operation staff to enter
Basic queries to the system by our Manufacturing team also took a significant amount of time and caused a lot of confusion
MYSIS was also not cloud-based and required a third party to host the WAZER server.
The tedious nature of the software caused maintenance to be ignored
Crucial inventory counts didn’t get entered timely, causing a perpetual lag between the MRP and reality

As a result, we started maintaining two incomplete systems: the original google sheets and MYSIS. We were spending even more time reconciling data as opposed to doing real work! The flexibility we require to efficiently do business with small suppliers in China did not work well with the rigid structure of our new software package. Ultimately many google sheets were still being used for daily functions, only to be imported into MYSIS on a bulk basis later on.

Options

Things were not working, so we considered:

Hiring someone full time to manage MYSIS
Paying a contractor for a custom implementation of a new MRP software system that better matched the specifics and subtleties of our operations
Develop our own custom MRP software

Being engineering generalists, we naturally gravitated to the “develop our own” option. As we investigated feasibility, we were pleasantly surprised with what we found. Whereas 5-10 years ago this would require a talented software engineer, now SaaS database platforms (such as Airtable and Coda) have reached a level of maturity such that you can manage a sophisticated database without needing to write much code. These tools also have huge communities and enough support materials to give you confidence that you can solve the inevitable problems that will arise.

Solutions Choice

Given the options available, having a purpose built system offered the best potential to account for all of our specific workflows and manufacturing oddities. To make sure we weren’t making a huge mistake we decided to build a basic proof of concept. The test was to see if we can create a linked set of two bills of materials: a collapsed bill of materials (cBOM) and a parent-child bill of materials (pcBOM) and to do basic manipulations to each of them. This worked remarkably well and was quite straightforward to develop.

Coda

The platform we chose is Coda. The idea of Coda is that it effectively mixes all of the google document types (docs, sheets, slides, forms, etc) into one flexible document, where you can insert instructions, pictures, forms, tables, links, etc. Each basic table is far more powerful than a traditional spreadsheet table, because you can insert almost any data type into each table and link the data to any other part of your document. The document effectively represents a single variable space, so the table is available and accessible on any other page.

One of the features we are excited for in the future is that Coda integrates with Zapier at a very low level, allowing for significant integration into other company tools in the future. Coda also integrates with Google Docs so all the data and information we already have can be easily attached to relevant information in our database.

Build Process

The build took about 3 months to complete, starting with a full export of our previous software package. We evaluated all the workflows of each employee who has to regularly interact with the MRP to fully understand the requirements of each new module. We documented how data was supposed to be entered and interacted with, and we focused on identifying and preventing bad data from reaching the databases.

Part of the development time was taking the opportunity to clean up all imports to match the actual current state of the product and our operations. Once we had good inputs and the workflows fully understood, we began the iterative design process: build it, test it, break it, fix it, and repeat.

WAZER MRP Current State

We have released 82 purchase orders that were completely generated and now monitered by the MRP.
The China team has been tracking 425 unique parts and their inspection states.
The system is tracking the process of 43 assemblies for the China assembly team for part arrival, construction and inventory. The same module also provides the work instructions for each assembly they need to build.
The Engineering team has conducted dozens of significant restructurings of the pcBOM within the new MRP, saving hundreds of hours compared to the old process.
All parts that have arrived in the China facility have been tracked from check in, to inspection, to the final shipping container assignment.

The Sky Is The Limit

As we grow, our MRP can grow too. The only regret we have is not having chosen this as our MRP solution from the get go. We should have never bothered trying to make an off-the-shelf package work for our very unique operation and workflow - but oh well, live and learn. Who knows maybe our next product is an MRP? (hope not, we’re better at building waterjets!) Now the Order Fulfillment team is pleading for a module! We’ll see… :)

Writing Our Own Custom MRP Software