But not for primary forecasting and replenishment 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.
Reports
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.
Spreadsheets
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: