The Mark of Canes

My previous post outlined the perils of using Excel as an inventory management tool. This time let’s look at one of the most powerful and appropriate business uses of Excel – the pivot table report. In my experience, this is one of the least understood tools available to those who use Excel to analyze and report on data. And yet for many it should be the tool of choice. 

Pivot tables facilitate summarizing and thereby analyzing data. Let’s use the example of an ERP system that stores all your inventory and sales data in a database format that can be accessed via Excel. You would create a live link to your ERP data from within the Excel workbook, and return the data as a Pivot Table report. What you’d get initially would look something like this: 

Click Image to Enlarge

So you have a list of the fields available from the database to which you’ve linked. You can now use simple drag and drop to determine what you’d like to analyze in the rows and columns of the Pivot Table, and what data should be summarized. In this example, I’m analyzing sales data by product by country for the year 2008. It took me less than 30 seconds from start to get this: 

Click Image to Enlarge

Once again, simple drag and drop will allow me to change the content and even layout of this report instantaneously. In a future post, I’ll explore this a little further and also try to answer any posted questions.

I recently visited a company that uses Microsoft Excel spreadsheets to manage its inventory. This is not unusual – many small businesses use Excel to manage areas of business for which they do not have a purpose-built system. But this visit reminded me how dangerous it can be to use Excel for certain types of business activity.

(To be clear: Excel is an outstanding business tool and I use it all the time. But like any tool, it works best when used for appropriate purposes.)

At this company, their Excel spreadsheets are manually updated when shipments arrive, when they get around to it. And they’re updated again when products are shipped out, some time after the fact. They use these spreadsheets for information about inventory: what’s in stock, when to replenish and how much to order. They also use them for costing inventory, including landed costs like duty, brokerage and freight. So these are pretty important business tools, right? Now read this question and answer exchange with the business owner:

Q: How closely do the on hand quantities in your inventory spreadsheets agree with physical inventory?

A: Well, whenever we do a count there are many differences. In fact most products are incorrect. And we often cannot find products in the warehouse that the spreadsheets say are in stock.

Q: In that case, how can you place reliance on them?

A: We use them more as an indicator than for absolute availability – we usually do a physical check when we need to know for sure…

Q: How much time is spent updating the spreadsheet (daily)?

A: Not too much – perhaps an hour or so at the end of the day, on busy days a little more.

By my calculation, this company spends 25 – 30 hours per month updating an “inventory management system” that is of little or no use and cannot be relied upon. It’s clearly a waste of time and effort, but is this also a limiting factor in terms of the company’s growth potential? I believe that it is, and that many small companies limit their growth potential by using inappropriate business tools. It’s the old “penny wise, pound foolish” trap.

If your inventory is managed as part of the overall business system (“ERP”) software, and implemented properly, inventory is updated naturally as the underlying business transactions are processed. You’re invoicing your customers and paying your suppliers for products anyway, so why not have the inventory updated automatically when you do so?

I had two phone meetings scheduled for today, and in each case the other party, a potential supplier, was to call me at the pre-arranged time. They had an engaged sales prospect who had requested a phone meeting, which in these tough economic times is gold – or so one would think.

My 10am meeting called me around 10:07, with no apology for being late. No big deal, you may think, after all it was only seven minutes. But remember, you only get one opportunity to make a positive first impression. My feeling when the meetings started was: if this is how I get treated when they’re trying to woo me, imagine what it’ll be like if we’re actually “married”.

My 2:30pm meeting has not as yet called, or emailed an apology, which I guess is why I have the time to write this now – at 2:50pm. As you can imagine, I have zero intention of doing any business with either of these companies. I have the perhaps old-fashioned view that if you ask someone to set aside time to meet with you, whether in person or by phone, you either show up on time, or let them know ahead of time if you’re running late.

Remember Sales 101? Treat your prospects and customers with respect. That means being polite, punctual and honest. When you go into a meeting, arrive on time, put away your Smartphone for the duration, and give them your 100% undivided attention.  And guess what? It’s even more effective than it used to be, because so many of your competitors have abandoned good manners.

My customers are my business partners, and I go out of my way to nurture those relationships. I’d like to feel the same way about my suppliers – and in general, I do, because I eliminate those that treat me poorly, no matter how good their product appears to be.

Potential Blue Link suppliers: you have been warned!