Supply chain woes: How to add certainty in a very uncertain world

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:

1. Having sufficient quantities of raw materials and products on hand to fulfill current or anticipated sales demand.
2. 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.

Custom Reports

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:

A more complex, but currently unanswerable, question is:

“Hey QuickBooks Company File! How many of our component items do we need to build the assembly items required to ship currently open sales orders, and how many do we have on hand?” That is the unicorn report that many QuickBooks Enterprise customers dream about.

If your software can’t answer those essential questions right out of the box, you may think your only option is to leave QuickBooks Enterprise behind and implement something like Fishbowl, Acctivate, or NetSuite. Not necessarily so.

This kind of unicorn custom report already exists. With some valuable help from colleagues at Alembic Computer Services, we were able to develop two powerful reports using SQL code and Crystal Reports. These reports answer those questions and more at a glance – and without any “Excel export gymnastics.”

I invite you to zoom in to look at all the disparate data elements brought together into a single view:

Here’s a video tour of the unique aspects of our SQL-based inventory reports.

But what are the benefits of this kind of custom report?

So glad you asked.

You do not have to export anything to Excel. The reports run faster than reports inside of QuickBooks – even if the files are really large (over 2 GB). No additional software is needed (except a report viewer). The report is accurate, even when the file contains corruptions. And, finally, the users who run the report don’t require access to QuickBooks, which helps keep the data secure, reduces wear and tear on the file, and may cut down on the cost of user licenses.

Necessity truly is the mother of invention. There are thousands of apps in the QuickBooks marketplace, and probably thousands of other solutions that aren’t even listed there because, like these SQL-based reports, they aren’t “apps,” yet they can be game-changing solutions. My personal favorite? Melio Payments!

When it comes to serving clients that rely on QuickBooks Enterprise Solutions, firms of the future will welcome the news that using SQL with Custom Reporting can greatly enhance inventory management. QuickBooks Enterprise is powerful, and whether you learn to build custom reports yourself or engage the help of specialists in this niche, QuickBooks Enterprise has all the data your clients need at their fingertips.

The future of manufacturing and wholesale definitely looks easier, more profitable, and a lot more certain.