Why Excel Is Bad For Inventory Tracking

Young businessman in warehouse checking goods on racks

No one can dispute Excel’s standing at the No. 1 spreadsheet application available today, or for the last 20 years. While not the first such program on the market, its wide availability and familiarity make Excel the name for computerized data logs. That familiarity also allowed the program to branch out into other specialties.

Take scheduling: You simply create your table of dates, employees, and shifts, assign as needed, and save. You can access the schedule as needed, change, and save again. Few people in your company would need access to this data, maybe no one more than yourself, and you would not necessarily need to (or want to) make it available on your network. Excel can easily handle this task.

Now, let’s move on to something a little more detailed, like inventory. If your business handles only a limited stock, Excel may suit your needs. What happens, though, if (and when) your business starts to grow? Suddenly, it doesn’t work quite as well as it did. Even the best typist will make one error for every 300 keystrokes.

Too many items sit in your stock room. You can’t keep an accurate running tally of what you need and what you carry. You might run into the same problem experienced by the University of Oxford’s telecommunications department:

“We would be completely out of important equipment in stock rooms before we were notified to order more,” said Jemima   Spare, Telecoms IT Support officer. “Also, there was not a tracking system for items, making it impossible to know where new phones were being installed.”

call-to-action-810x75-c

Even if you enlist a few good employees to help, can you trust the spreadsheet to show true real-time results?

No.

TopGolf found itself in a hazard with its inventory management and a lack of real-time insight.

“We didn’t know when orders were coming in,” said inventory analyst Bryan Harej, “and we had difficulty tracking what we were sending out. If a facility was running low on a part, sometimes we weren’t notified until a day or two before it was needed.”

What if you expand beyond a single building, or even a single stock room? It can prove difficult enough to find something you need in a single room: What happens with multiple rooms?

“Parts were scattered all over,” said Lee Letawsky of Precision Drilling, “and we wasted lots of time looking for things or ordering things we already had. It was often faster to order a new part because we couldn’t locate it in our supply room in a timely fashion.”


Related Article: INVENTORY MANAGEMENT DRIVES ECONOMIC GROWTH

If you divided these areas between employees to inventory, could more than one of them update your Excel data at once? What about access the file from the same network location?

No.

How long will updates take? How can these spreadsheets help you in the future? What if something goes wrong? Does the relative cost effectiveness of this program justify the potential and likely pitfalls beyond simple data entry?

No.

Despite these shortfalls, the State of Small Business Report finds that almost a quarter of reporting small businesses (24 percent) continue to use a spreadsheet to manage their inventory. Thirty percent use one to track their company assets!

Let’s take a walk through a hypothetical scenario that’s likely often played out when a small business relied on spreadsheets beyond their time.

Note: While Excel remains the industry-leading spreadsheet app, we can’t ignore the amount of reliable freeware that take their own share of the niche. “Spreadsheet” will replace “Excel” through the remainder of this article.

Portrait of a pensive young woman supervisor working on-line in warehouse. Young blond woman standing at distribution warehouse and wearing elegant suit. Industrial boss examining the stock. Large distribution storage in background with racks full of packages, boxes, pallets, crates ready to be delivered. Logistics, freight, shipping, receiving.

Reasons to bid spreadsheets a fond farewell

  1. There are more efficient ways to spend your time.

When you carry only a few dozen inventory items, the odd hour here and there doesn’t really add up to much time. You can easily incorporate this count time into your daily schedule. What happens when your stock calls for quicker updates to keep your records accurate and involves more items?  A spreadsheet cannot easily, or efficiently, keep up with such progress. You might find yourself in the stockroom several times a day in the middle of a count that likely will change before you finish.

What about the time invested in the spreadsheets themselves? The county, the number plug-ins, the updates, and the restart of the cycle. That hour you previously scheduled expanded into two and then several spent in an exercise in futility. The real time you spent can’t make your spreadsheets accurate in real-time and could be better spent on other business.

Inventory Control Software and a Monster Truck
  1. How accurate are your counts?

Go back to your stockroom and count one of your moderately popular or demanded products. By the time you finish your count, one of your employees sends out five units. Before your final count for the day, another five go. And somehow, at the opening count the next morning, you wind up off from the previous night’s total by three.

So where did the mistake happen?

Did someone miscount the number available out front? Did you accidentally double count in the stockroom? Did someone not account for someone brought out, accidentally or otherwise?

And all the while as you and your staff hunt from the source of this confusion, business carries on as usual and your county gets more and more off with each transaction.

  1. When you try to forecast demand…

Let’s use the best case scenario: You didn’t know that you moved three move units of this moderately-demanded item. This puts your count back on target, at least with last night’s, and allows you to breathe a little easier.

As you look over the figure, you think demand for this same item went up last week and the week before as well. You could check your previous inventory sheets, if you saved one for each individual day, or at least a weekly, and if you can find the time to sift through the columns and rows for that particular item. But you need to place your order!

Should you go ahead and increase your order in case your sales continue the possible upward trend, or should you reorder as before? Did output really increase? Is the cause seasonal? Can you honestly tell from your spreadsheets?

Can you trust your counts? Did other units go out without your knowledge? Did you accidentally press the wrong key when you made the entry? You’re only human, just as the others you trust to help keep track of your stock: Any one of you could make a mistake for any number of reasons. Maybe you didn’t sleep well the night before and lost your place. Your staff could be overworked due to call-ins for illnesses. Whatever the causes, errors in manual data entry can easily lead to major consequences.

  1. Who can edit and who can access?

Your business caught on and now you need more storage space. You now house stock at your main office and two smaller spaces nearby. Daily inventory counts now require updates from three separate locations to only one spreadsheet that allows one user to update at a time.  Add to that the fact that the application provides no notice of modification unless too inspect the file’s properties and you could easily double the amount of time spent on manual inventory.

You also placed a great deal of faith, to say nothing of potential power over your business, into the hands of a few people. Under the influence of internal or external stressors or find your faith misplaced thanks to shrinkage, you might not get an accurate count of items that get lost in transition.

Your next step

Once you expand beyond the inventory capabilities of a spreadsheet, your next best progression is to an automated management service to help reduce your inventory hassles and worries. The use of a barcode system will allow you to easily identify, enter, and update your stock with its own unique marks. You can input specific data about each individual item and each scan of a code automatically updates your database. Your inventory’s movements are tracked in real time from its arrival to your facilities to its departure from them.

Professional Cooling and Control Systems (PCACS) implemented an automated system and recouped the cost of the program within six months.

“We no longer had to spend excessive amounts on shipping items we thought we had in stock,” said Tommy Gugliotta. “We now have in-depth insight into our entire inventory.”

You can remove much of the guess work involved in future orders. Instead of increased write-downs thanks to excess or backlogged orders due to lacks, you can monitor your sales trends as sales happen and plan accordingly.

The real-time tracking also reduces the likelihood of inventory shortages due to shrinkage. It will be no trouble to compare the ordered and “received” amounts to find any discrepancies. These scan point will also help cut back on any misplaced products through the combined efforts of visual confirmation and electronic scans.

Still think an automated system is beyond your business? Learn more on how the University of Oxford cleared the static out of its telecom department. Read to see how Precision Drilling cut two days off its inventory time. See how TopGolf got out of the sand when it came to asset management. Look to see more on how PCACS moved from its informal tracking system to a more efficient style.

No matter the size of your business, inventory control can help ease your inventory worries and leave you more time to further your business plans. How could an automated system help you?

 

VN:F [1.9.22_1171]

Rate this article

Rating: 3.9/5 (16 votes cast)
Why Excel Is Bad For Inventory Tracking, 3.9 out of 5 based on 16 ratings
Paul Trujillo

Paul Trujillo

Paul Trujillo is a Product Marketing Manager at Informatics specializing in Inventory Warehouse Management and Supply Chain product lines. His nearly 15 years of experience has put him at the forefront of industry technology and developing trends.
  • Pingback: How to Choose an Inventory Tracking Solution for Your Business()

  • Chef

    I disagree that excell is bad. It forms to a restaurant’s needs unlike ALL the other software thats out there!!! We tract our purchases by the invoices and we need a inventory sheet that matches our inventory on the shelves and NOT by the accounting sheets!!!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • Ashutosh

      Your are right Chef, On the contrary I would say excel is very much suited to small businesses. You can download a full version freeware of ABCAUS Excel Inventory Tracker.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • waspbarcode

        If Excel works well for you, it’s good to hear! We usually encourage customers to consider what their time is worth. Wasp solutions add efficiencies that save time and improve accuracy – you have the ability from a mobile device to audit your inventory in one step versus running the risk of entry errors with multiple steps in the audit process. Likewise, the operational value afforded by Wasp Inventory Control reports are helpful for trending, forecasting and other data management needs. For a very small investment, automation adds much value to your process.

        VA:F [1.9.22_1171]
        Rating: 3.0/5 (1 vote cast)
  • waspbarcode

    If Excel works well for you, it’s good to hear! We usually encourage customers to consider what their time is worth. Wasp solutions add efficiencies that save time and improve accuracy – you have the ability from a mobile device to audit your inventory in one step versus running the risk of entry errors with multiple steps in the audit process. Likewise, the operational value afforded by Wasp Inventory Control reports are helpful for trending, forecasting and other data management needs. For a very small investment, automation adds much value to your process.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • roy

    which program is useful to trace inventory materials ?

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Pingback: Fight inventory shrinkage | Wasp Buzz()

  • Pingback: If time is money, why am I always a dollar short? | Wasp Buzz()

  • Pingback: Still Tracking Chemical Inventory by Hand? Utilize Digital Management to Bolster Workflow()

  • Roland

    I self taught myself excel coding and I was able to build a Professional Inventory system for the company I work for. It has its own menu, log in passwords for multiple users, printable pick list that update the inventory with a push of a button, it can create reports, lets me know when inventory is running low on products, and has the ability to import data from Quick Books. We don’t have the luxury of barcodes and scan guns so I turned excel into a mean inventory machine.

    VA:F [1.9.22_1171]
    Rating: 5.0/5 (1 vote cast)
  • NIBB

    It depends. If you have thousand of items which change every day, yes, Excel may be bad, but not because Excel is bad but because humans makes mistakes and with so many items which have to be manually maintained chances of making errors are high. A system is only as good as the data entered into it. A bar code scanner and customized software may be the best but….

    If your inventory consists mainly of things that don’t change offend, which are like 99% of small business. Lets say computer equipments, restaurants, small offices, you get the idea. Excel is the best, because its very open, it works with almost everything and in almost every platform, mobile, PC, tablets, etc. And it allows a high degree of customization to make something very complex in the future. I think most business owners will never outgrow Excel since most don’t know even half of the features Excel can do, in particular once you bundle it with other software.

    The key here is customization. While I understand that out of the box software may sound nice at first, all of them have eventually the problem to fit into a business model. Excel may be harder to start, but as you go along a business can tweak it to make anything they want. This is true for most Office products. You can fit them into your business and not the other way around, which means make your business work for a specific software. Companies don’t like vendor lock in, Office is affordable, it does not cost a small fortune like most special market softwares and I would rather prefer to be locked into Microsoft vs some company which may be out of business in a few years.

    To conclude, if you are tracking a huge number of items, then sure, Excel will be awful to manage. Just get something that can work with a bar code scanner and that allows as much automation as possible without any human intervention if possible.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  • Pingback: Stock Control Case Study: Monster Truck-Sized Inventory Solution()

  • Pingback: Warehouse Inventory Tracking |()