One of my clients sells products over the Internet.  Their major investment is in inventory.  They collect by credit card, so there is very little in receivables and they don’t have much in capital equipment.

 

Inventory was the place to look to see if we were going to be able to cut down the investment in the business and free up some additional capital for the owner.

 

The client has good reporting on the month end inventory.  They can use that to see areas where they might be able to cut back.  That is easier to tell with some of the larger items.

 

However, they have a broad product line.  There was some money tied up in these other items.  While no individual items had a large balance, collectively it added up.

 

What I did was put together a different report, which I call lining up the inventory.  The report was laid out as follows:

 

·         Columns going across

o    Quantities by month

o    Dollars by month

o    Far right- comparison against sales

·         Rows going down

o    Products

 

This report gave a very different perspective.  Seeing the monthly balances lined up side by side helped show which items were barely moving.  These became candidates for running some specials on to reduce their balances.  In some extreme situations, it made sense to drop items from the line.

 

For example, a quantity of 1,000 in a CD might not jump out at you in a month end inventory report.  However, seeing that same balance repeat itself month by month in the monthly inventory comparison can spur you to taking action on the item.

 

It wasn’t difficult to create such a report.  We already had the month end inventories in Excel.  I was able to create the new report by using lookups to pull in the data from the monthly reports and avoid having to rekey the data.  An alternate approach would be to create a small database of the monthly inventory balances and then create the monthly summary using a pivot table in Excel.

 

Consider adding this monthly inventory comparison to your monthly inventory report if that fits your line of business.  It might be eye-opening on ways to cut down your inventory investment and improve cash flow.