The amount of information available on the Internet on Inventory Management, and specifically purchasing / replenishment / procurement, is quite staggering. One would think that, with so much advice and so many tools at our fingertips, we’d all be doing a much better job of purchase planning and management of inventory levels than, say, 10 years ago.
One would be wrong.
Like all tools (and weapons), information used inappropriately can be quite dangerous. And we’ve seen many people in many organizations make some basic mistakes in their purchasing process. Specifically, I’ve witnessed incredibly sophisticated spreadsheet based predictive models that draw data from an ERP Software package, use EOQ calculations and conditional formulae to project demand, and yet result in poor purchasing decisions.
Here are 3 common mistakes when using a spreadsheet model to manage the process:
- Ignoring one key factor: any model needs to take into account not only forecast demand and on hand quantities, but also units committed on existing orders, those on unreceived purchase orders, needed for production or assembly, and in some businesses projected returns. A company that typically has upwards of 25% of products shipped pre-December returned in saleable condition in January, totally ignored this in planning and placing orders for February, resulting in major overstocking of those products.
- Formula errors: sounds ridiculous, I know, but even the most carefully crafted spreadsheet models tend to become increasingly complex over time, as the author tweaks the model – and at some point makes a mistake on a formula. Any example I’ve seen was a change in the formula that computes the recommended purchase quantity, that was copied to only part of the model, resulting in a different (older) formula being applied to around 30% of the products.
- Manual transposition errors: key-punch errors when (manually) generating the purchase orders per the spreadsheet calculations.
The easiest way to avoid these errors: use the purchasing tools available in modern wholesale distribution software to assist with purchasing. And if your purchasing model is too complex for your ERP Software to handle, then either you need to change your software, or you may want to re-think your purchasing model.
Agree? Disagree? Or simply don’t care? Please feel free to comment.