Control Your Inventory

As discussed in our story about the inventory trap, if you sell physical products (as opposed to electronic downloads or items drop shipped directly from outside manufacturers to your customers) your ability to manage your inventory could be critical to the survival of your business.

Minimizing excess inventory is essential to ensure you’ll have enough cash on hand to pay bills. It’s equally important to avoid running out of items that customers want to buy so you’ll never lose sales or squander your advertising investments.

In our business, we find that inventory control amounts to three main tasks.

1. Keeping an accurate count of all stock quantities – with the ability to feed that information into our bookkeeping software
2. Creating a process to warn us in advance of items we need to buy to avoid running out
3. Taking frequent enough physical counts of our inventory to assure that our reports are correct

We first tried managing inventory using an expensive version QuickBooks, but found this to be hugely frustrating.

After struggling with the problem for a few years, I found our only solution was to track inventory with a spreadsheet. This required me to get a deeper knowledge of Microsoft Excel, a skill became useful for many other tasks.

Managing Inventory with Excel

Today we manage inventory using a single Excel workbook with different sheets where we record the data that determines our inventory.

• On one sheet we append newly completed (shipped) transactions reported by our shopping cart onto a long list. Each row shows an individual product ID purchased, order number, date of the sale, product cost, sales tax and shipping fees.
• On a second sheet we enter product refunds, mainly to keep track of refunded sales tax.
• On a third sheet we list the date, quantity and part number of items we receive from suppliers and any adjustments, say, for free replacements we send, changes in physical inventory counts, and so on.

With these three basic inputs, plus pages that use the Excel SUMIFS and SUMPRODUCT functions to calculate component parts consumed, we’re able to report the quantities of all items in stock on any prior day and time, and for any period the inventory used and the sales taxes collected and refunded. The workbook uses only conventional formulas, and no macros or add-ins.

We’re also able to see historical averages of components used, and alerts so that we know to order more stock when levels fall below thresholds we’ve set in days’ supply, accounting for each supplier’s normal lead times.

For our business, we’ve found that it’s critical to take physical inventory every few months, and to enter any adjustments into our spreadsheet so that we avoid any surprises.

We’ve also learned that it’s a good idea to keep plenty of critical, low-cost items (like labels, packaging materials and fasteners) in stock, while carefully monitoring our inventories of more expensive items.

Next, we’ll talk about bookkeeping processes we’ve found that work – and those that don’t – for running an online business.

Next: QuickBooks Hell