Pandemic Reports for QuickBooks

by

If your company uses QuickBooks Desktop for managing inventory for sales and/or manufacturing, you’ve likely bumped up against the limitations of the in-app reports. I happen to be familiar with these limitations - because we spend our days figuring out how to go beyond them. After all - the data is in there.

I come at this particular topic not from being a QuickBooks expert, nor an accountant or bookkeeper, but rather from a 24-year career in designing custom reports. My first exposure to QuickBooks was in 2013, when I said to my CFO friend who’d requested a split commission report, “It’s just QuickBooks. How hard can it be???” 

It was hard. Insanely hard. And unless you want to learn to write SQL Code to stitch the data tables together, I don’t recommend it.

Fortunately, I soon learned about QQube for QuickBooks from clearify.com. If this data warehouse didn’t exist, I promise you, I wouldn’t have stuck around in the QB world. And when you combine QQube with super-specialized knowledge in custom report design, some amazing things become possible. 

Back to inventory. Imagine your company produces and sells disinfectant wipes. And then a virus pandemic hits. Are you prepared for a flood of 10+ times your usual sales volume? My customer wasn’t either. The situation produced two key problems:

  1. Incredible uncertainty about when raw materials and purchased product would arrive and when they could ship product to customers
  2. Deciding how to allocate what product they 'did' have

Data to the Rescue

A crystal ball would be a nice business tool, but I just use Crystal Reports to glance into the future. In this case, we wanted a sum of the quantity of all Open Sales Orders (based on Ship Date) - right alongside the Open Purchase Order quantities (based on Expected Date) from vendors. But we wanted it week-by-week.

Such a report would look like this one:

When the date columns turn red for any item, the item will be out of stock. It allows the purchasing team to get in action ahead of time. Of course, it depends on vendors keeping their promises, or the purchasing team to keep the Expected Dates on the POs current. 

For additional detail, we created this view - to see the movement between SOs and POs:

What makes this even more valuable is that QQube can even show Inventory Assembly Parts - those that are only used as raw materials - and calculate how much is needed to fulfill orders of the items that use them. That’s powerful.

Equitable Allocation

The second problem was about allocation. How would you allocate limited quantities to satisfy more orders than you can handle? There are so many ways - and so many questions to consider:

  • What if a long-time customer orders a new (scarce) product?
  • What if a desirable new customer comes along, one we’ve been coveting for years? Do they jump the queue?
  • What if we can’t ship a full pallet of product (our minimum)? Who should bear the cost of shipping, something normally included?

I was able to put together some Crystal Reports that took data from QQube and exported it to Excel. We had three worksheets of details in our homemade Excel database:

  1. Expected Production Quantities for each item
  2. Customer Details for all customers who had purchased in the last year
  3. Allocation Worksheet

The last one was certainly the most interesting - it contained several columns to help the “decider” with the allocation of limited supplies.

These include:

  • Average cases per month before the pandemic
  • Current Open Orders
  • What was shipped recently
  • And finally, a quantity of the project allocation based on the typical % of Sales

Unfortunately, that’s when the manual line-by-line decision-making has to happen. But this approach helped speed things up.

Once the blue cells were edited manually, I used the worksheets as the new database and produced lovely looking PDFs that could be emailed (in a single click using an amazing tool called PDF-Explode) to customers to keep them informed. Customers usually like that sort of thing!

The truth is none of us know how to handle this pandemic situation. But hopefully, good data, meaningful questions, and skillful application can help your company deal with the uncertainty, recover from the quarantine period, and prepare to be more effective in the future.

About the Author

Angela Meharg is the owner of Datisfy Inc. in Toronto. She has been a specialist with database reporting and analytics for 24 years.

Angela's company has helped over 100 QuickBooks clients in the US and Canada succeed with custom reports for QuickBooks Desktop and Online.

Angela is also an Advanced Certified QuickBooks ProAdvisor.