September 13, 2017 Accounting & Finance en_US Excel might be a good place to start, but it’s not a good way to scale. learn how managing inventory in Excel can cost you money. 5 Ways Excel Is Adding Costs to Your Inventory Value
Accounting & Finance

5 Ways Excel Is Adding Costs to Your Inventory Value

By Marjorie Adams September 13, 2017

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.

Rate This Article
Marjorie Adams is founder and CEO of Fourlane. After several years as controller for a large, private manufacturing company, Marjorie decided training was her specialty and ventured into the accounting software industry. Marjorie is considered one of the top QuickBooks trainers in the country. She was awarded Insightful Accountant’s Top QuickBooks Desktop ProAdvisor For 2015 and is CPA Practice Advisor Top 40 Under 40. She is instrumental in providing product feedback to Intuit and currently sits on the Intuit Solution Provider Council, QuickBooks Enterprise Council and is an ACE and CPE certified trainer. Read more