Have you given up on QuickBooks® Enterprise to manage inventory? While many have moved on to MRP (manufacturing resource planning) apps, there is another way.
The decision to move to an MRP may be valid. However, in many instances, the valuable data users need is locked inside QuickBooks Enterprise the entire time. You just need the right key to unlock the hidden gems inside.
Keys to great inventory management:
- Having sufficient quantities of raw materials and products on hand to fulfill current or anticipated sales demand.
- Purchasing just enough, so that you don’t tie up critical cashflow.
That sweet spot requires reliable data, and although no one can predict the future (as the last two-plus years have demonstrated), a QuickBooks Enterprise file contains a surprising gold mine.
Really, though, it shouldn’t be surprising. There should be a report that tells a production manager how many units of Component X are needed to build Assemblies Y and Z today, tomorrow, and next week.
There should be a report to tell a purchasing manager the quantity of Components A, B, and C to order – and by when.
Unfortunately, those reports don’t exist in QuickBooks Enterprise. However, the data to build those reports does!
QuickBooks Enterprise is a powerful software that allows manufacturing customers to really push the limits. I have seen some incredibly sophisticated and clever uses of Advanced Inventory functionality that go lightyears beyond just the basics of Quantity On Hand, On Sales Orders, and On Purchase Orders. The very good news is that if the data is in there, it’s highly likely that a data-driven solution can be found.
Because the data is, indeed, in there, it’s waiting to be emancipated.
Pro tip: Custom Reports does not mean clicking the “Customize” button at the top of a report. That feature is limited. The data needed to manage inventory is found by connecting external tools to the database to design reports that don’t readily exist.
Custom Report development satisfies very specific, real-world customer requirements. The designs don’t come out of thin air. Usually, the requirement is to address a limitation with the QuickBooks Enterprise in-application reports. That is not unique to QuickBooks. Even the reports included in top-tier applications from SAP and Oracle can’t satisfy everything to every business in every industry. That’s unrealistic.
Developing a custom report is not only possible, but also can yield a greater outcome than most QuickBooks professionals realize. Possibilities are abound with the data deep inside QuickBooks Enterprise, without adding new software or complexity.
Let’s talk about this existing gold mine of inventory-related data in QuickBooks Enterprise. Here is what we know:
- The date of each PO sent to a supplier.
- The date of the Item Receipt and/or Bill received from the supplier.
- Component item quantities required for building Assemblies.
- The requested Ship Date and quantities of items on open Sales Orders.
These five data points, alone, can provide fairly accurate estimates of when replenishments will arrive and when customer orders can be shipped. They can also be used to calculate the quantity of component parts needed to build and fulfill assemblies and stand-alone items on Open Sales Orders. This calculation is called a BOM (Bill of Materials) Explosion. These facts also offer the ability to communicate with customers to manage their (sometimes demanding) expectations.
Sounds simple, right? The problem is that while the data is in the database, it’s not easy to get to. Liken it to the black box on an airplane. It’s also highly unlikely that a bookkeeper, accountant, or generalist QuickBooks ProAdvisor® – let alone a business owner – is able, or even willing, to learn the technical skills required to do so.
The primary skill required is SQL (Structured Query Language) programming (shameless data geek alert!). It’s how you ask questions of a database. In English, it would sound like this:
“Hey QuickBooks Company File! How many of Item X do we need to fulfill the currently open sales orders, and how many do we have on hand?”
The equivalent SQL code looks like this: