Reports and Spreadsheets– An Unproductive Combination for Inventory Planning
Reports and spreadsheets should be used to provide a basic level of information. However, many companies and planners use reports & spreadsheets to completely manage their inventory. To understand the use of these tools on effectiveness and efficiency in inventory planning let’s explore each one independently.
Many ERP and accounting systems have many standard reports that provide useful information. Usually, however, a company will create or build custom reports either in-house or through their 3rd party IT provider (Value Added Reseller – VAR). OK, so this may help because it provides a level of detail that a company or planner feels is necessary to better manage the inventory.
Assume your outside IT firm builds the report. It costs $5,000 to develop. Let’s also assume it is refreshed, updated, each day. Now what? This is static data and yes it is current but it just sits there and provides no efficiency for the planner. The planner must review and decide what the data suggests and what to do with the data.
But the real cost is much higher than the cost to develop the report. Every report, like spreadsheets must be maintained. Much like a car, the purchase price is just the beginning. Hours of a planner’s time are spent pouring over the results to make decisions. You need to count those hours as an associated cost. So, if a planner is paid say $45,000 per year, plus taxes and benefits the total annual cost is over $50,000. If 35% of a planner’s time is spent reviewing just the reports to manage the inventory, then the first year cost is $5,000 + $17,500 = $22,500. That is some expensive report. Are you getting your money’s worth? You still may not be done.
Most people who have reports will export the data from the report into a spreadsheet so they can better manipulate the data. Here is where the fun comes in.
The data from the report is now in the spreadsheet that you spent countless hours creating. The formulas in the cells calculate the numbers you need to evaluate your inventory and plan out the replenishments required.
The dangers inherent in spreadsheets are:
1. Lack of Control
2. Errors from:
- Formula and data entry mistakes and typos
- Cell formatting and range definition mistakes (or errors)
- Data exporting problems
- Cell referencing problems
3. Complicated Calculations using incorrect assumptions
4. Potential for fraud
Lack of adequate training can result in poor to mediocre spreadsheet results, such as improper referencing, linking to other spreadsheets, or using inaccurate formulas to master complex calculations.
If the policies and procedures to mitigate spreadsheet risks are inadequate, errors will become more common and lack of consistency will show up in internal control audit reports. Therefore, the style, content, and accountability for spreadsheets should be documented in the organization's policies and procedures or in the spreadsheet used.
People are creatures of habit, which is one reason why spreadsheets are reused from year to year. Unfortunately, after cutting and pasting information, the spreadsheet might not work the way it did before — formulas can be damaged, links can be broken, or cells can be overwritten.
Keeping track of reorder points and safety stock for hundreds or thousands of items is becoming an overwhelming task. The time involved to update and maintain these spreadsheets can take hundreds of hours every week. Plus, demand is constantly changing which can make the data stale and behind the curve for the most recent trends. This can lead to under-stocking causing lost sales and overstock creating excess inventory. In cases, a loss and a waste of valuable cash.
Now, a planner has consumed another up to 60% of their available time working with the spreadsheets. At 60%, the cost on an annual basis using the compensation listed above is 60% x $50,000 = $30,000.
The total cost of ownership for the $5,000 report is now a whopping $52,500. Not included in the number is the cost of:
- Lost sales due to out of stocks
- Carrying costs for excess inventory
- Expedited shipping costs both inbound and outbound
- Loss of customers because they are dissatisfied
Having your outside IT provider develop custom reports for you is doing yourself, your company and your customers a disservice. Using precious company resources to develop custom reports for inventory management is wasteful. Reports and spreadsheets are useful but not to manage your inventory. Instead, purchase a complete advanced inventory planning and optimization solution. It will reduce your planning time by up to 90% and dramatically improve your inventory mix while lowering costs.