5 Ways Excel Is Adding Costs to Your Inventory Value

By Marjorie Adams

4 min read

Using Microsoft Excel to track your inventory is a hard habit to quit. It is accessible, seemingly cost effective, and so easy to manipulate. However, sometimes the easiest path is not the most cost efficient. Over the years we have trained thousands of clients to track their inventory inside QuickBooks instead. Clients are motivated to break their Excel dependence once they realize the five ways its costing them money.

1. There Is No Audit Trail

An audit trail is incredibly helpful to find areas which your team needs further training. Without having access to see who is doing what with your inventory products, you are not able to hold users accountable in the shipping and receiving process. Accountability is key in the success of any role in your company and an audit trail helps your team take responsibility.

Also, audit trails are effective to help prevent theft and fraud. Part in preventing fraud is letting user know you have ways to track their modifications in your process. QuickBooks audit trail helps save money by deterring fraud and showing where we need more targeted training.

2. You Need a Purchasing to Payables Workflow

When you use the workflow of Purchase Order to Item Receipt to Vendor Bill, you can segregate users and their roles in your process. Using QuickBooks Enterprise allows you to give View, Create, Modify, Delete or Print rights to each of these transactions types. The procurement members may be able to view bills but can not create them as an example. By using this segregation of duties, you are building checks and balances into your process.

Let’s say you have a PO for 10 Widgets. Next the warehouse receives a box of 5 Widgets against that PO. The warehouse can scan in the packing slip and attach the actual document to the transaction in QuickBooks.

Then let’s pretend the AP clerk gets an invoice for the 10 Widgets. When the Accounts Payable team then enters the Bill, QuickBooks will trigger that only 5 items have been received. They can look at the scanned packing slip or reference it when challenging the bill from the vendor. This linkage between Purchasing, Warehousing, and Accounts Payable helps prevent from overpaying for items that have not actually been received. It helps communicate between the departments inside the system–saving everyone time. Excel will not be able to maintain these important workflows for your business.

3. Lack of Historical Visibility

Generally when we see Excel documents that companies are using to track inventory, the quantities are replaced instead of depleted. Let’s run through the example above where we have 10 of Widget A on a PO.

On the spreadsheet, there is a column for Quantity on PO, Quantity Received, Quantity Shipped and Quantity on Hand. So initially we have Quantity 10 on the PO, but have now received 5. So the process is usually to replace the number 10 in the cell with the number 5, then enter 5 in the received column. Then they put 3 in the Shipped column leaving 2 on hand. What I will not know using this method of replacing quantities in Excel cells is how long it took to get rid of those Widgets? What was the balance of my inventory as of last week?

Historical reporting and visibility is crucial when making business decisions on growth and spending. QuickBooks holds the details behind the quantities so we can have more data to make better money-saving decisions.

4. Too Much Time Spent Reinventing the Wheel

When users start to bulk up their Excel spreadsheets with more permissions and functionality, they are spending time being programmers instead of doing their jobs. QuickBooks is used by millions of companies so they can keep the cost of the software down. QuickBooks software is supported by Intuit as well–your excel document may be supported by the employee who created it, but what happens when they leave? Reinventing the wheel for something that a software like QuickBooks already does is a waste of time and money in your business.

5. Multiple User Access

Most people with inventory have moving quantities and values that are changed by multiple people. There are two options with Excel. First, to only allow one person in the excel file at a time–possibly causing other employees to have to wait to complete their work. Second, Excel does have the ability to have a shared workbook, however the workbook administrator then has to spend time managing and resolving conflicts. The conflicts do not come with backup or documentation, so the worksheet manager needs to spend time to figure out which conflict should win in the spreadsheet.

Time or labor is one of the most valuable assets an employer has and it is limited. By using QuickBooks, with its built in workflow and linked transactions, the time spent resolving conflicts can instead be spent managing efficiency and production.

Information may be abridged and therefore incomplete. This document/information does not constitute, and should not be considered a substitute for, legal or financial advice. Each financial situation is different, the advice provided is intended to be general. Please contact your financial or legal advisors for information specific to your situation.

Related Articles

Looking to Get Acquired? Don’t Run Your Company on Excel

Looking to get acquired? If you are using Excel as your accounting…

Read more

5 Reasons You Shouldn’t Use Excel to Manage Payroll

If you operate a growing business, you probably invest a great deal…

Read more

Why You Shouldn’t Use Excel for Accounting

2012 was a tough year for spreadsheets. First, the London Olympics accidentally…

Read more