Inventory Management in Excel: Tips to Help You Track it All
When you're low on resources, Excel spreadsheets can be a good way to start tracking inventory management. It's also a very accessible tool to help get you up and running with an easy way to manage maintenance inventory. If you're looking to take your inventory management system to the next level from paper or sticky notes, then Excel is a good next step.
Excel Template Alternatives
Inventory control can be tricky. When it comes to keeping track of your various types of inventory and accessing data on each item that comes in and out of your organization, it can be easy to become overwhelmed and lose track altogether. Whether you’re using Excel templates for inventory management or something on paper, there is a better way!
Rather than trying to devise your own spreadsheet or tracking form, explore how easy-to-use software could help you improve your inventory management to save time and resources.
Inventory Management Solutions That Go Beyond Excel
An inventory management template is handy, but it doesn’t offer complete control of your items. Cloud-based software companies such as Brightly have taken inventory management to new levels with solutions designed around inventory needs. With inventory management software, your inventory management goes beyond the Excel template to provide:
- Material transactions and real-time levels of stock pools
- Assignment of inventory to location, person or project
- Online requests and email notifications for inventory use
- Detailed data through reports and graphs of inventory movement
Another advantage our software is that it’s cloud-based and can be used from any location on any supported mobile device. You can be miles away from your inventory but still check on numbers or make transactions. A built-in catalog even makes for quick and easy ordering of new items, all of which can be tracked in your inventory management system.
Our maintenance management solutions can be used to manage inventory for maintenance items, schools supplies, athletic equipment, IT products and more. Efficient inventory management can save time and money while boosting operational efficiency and accountability.
Brightly products, has been found to save between $7 and $10 per student annually through more streamlined inventory management and automatic request routing. Plus, you'll save on paper materials by taking your inventory management digital.
Can I use Excel for inventory management?
Excel inventory management can help you better organize and track inventory and stock items, so you have a better idea of what you have and what you need. An Excel-based inventory management system can allow you to better categorize, tag and label inventory items for improved awareness across your team. This will also help you budget for inventory needs.
And while Excel inventory management can help you get ahead, the next step is to utilize software to really understand and optimize your inventory.
How to keep track of inventory in Excel
Microsoft Excel is an amazing general spreadsheet solution that can be used for many business tasks. This includes basic spare parts inventory tracking. Although never a replacement for a true dedicated inventory solution, you can cover the basics and use as intermediate solution until your needs grow.
Inventory management is all about having a solid, detailed list of the spare parts you manage (stocked on-site or order just-in-time).
When you track inventory in an Excel spreadsheet, you can better understand:
-
How many of these parts you have on hand
-
When to order when low
-
Where to order items from
These spreadsheets are only good if people keep them updated, so updating as you use/consume those parts is essential. Periodic “counts” of inventory can be performed to see where there might be waste, spoilage or theft.
An Excel-based inventory management process will not replace a true, dynamic system that you would find in a CMMS (computerized maintenance management system) or other work order system, but it can be an effective basic, low-cost solution.
How to make an inventory spreadsheet in Excel
Managing inventory in Excel starts by making sure you are tracking the right key information.
There are a variety of free inventory templates available if you search online. In fact, Microsoft Excel has a template available – simply click on file then new and search for “inventory” in the templates.
The main source of information is a list of the parts you want to track. Below is the default generic Excel inventory template, which is a good starting point.
The critical basic inventory management fields you need to track in Excel include:
-
Inventory ID – This should be a unique identification number/letter combination. Some will use the primary vendor’s part # or similar. Or you can make up your own.
-
Name – Just make sure you use a name people will recognize with enough detail to not confuse with other parts.
-
Description – Add more detail for clarification.
-
Unit price – In the standard Excel template, this is one $ amount that most use for the price of an individually consumed unit. For example, if I order my eggs by the dozen (12) and the carton costs me $3, my unit cost is $3/12 or $.25.
-
Quantity in stock – To remain consistent, this would be how many overall units I have. To stay with my egg example, if I have 10 total cartons of eggs, I would have 120 eggs in stock.
-
Inventory value – Multiply the # of units by unit price. This shows you the total value of that part. You can also total this column for complete inventory valuation.
-
Reorder level – This is the # of units you need to have in stock before you consider it “low.” Typically, this will be the amount you get down to where you can still have time to reorder and replenish those parts.
-
Reorder time in days – This is also called “lead time” and measures how long it will take for you to get in new parts. This # will be updated in times where parts can take longer (or quicker) to receive.
-
Quantity on order – This shows how many parts you have on order so you can keep track.
There are numerous possible fields missing from the standard Excel template to consider – simply add these as new columns to your XLS.
-
Location – Show precise information on where the parts are physically stored.
-
“How ordered” vs. “how used” – This includes appropriate fields that differentiate between how you order something (typically by the case, bag, gallon or some other container title) vs. how you consume or use that item (typically by the each or ounce or similar). This ends up being several fields, as you have track these fields, plus the ordered/used cost and similar information.
-
Vendor information – Information to show who/where you order those parts.
-
Detailed part #s – Typically, this is used to expand and include barcode #, part #, vendor part #, etc.
-
Part Category – this can be helpful to track separate types of parts, especially for valuation.
The Excel template has basic sorting capabilities and even a filter to show what parts are below reorder (simple math determining if (quantity on hand + on order quantity) <= reorder point). You can also use Excel formulas to total or average inventory.
These basic inventory management solutions will not replace a full-fledged CMMS inventory system. There are lots of gaps. Some examples include managing for multiple storerooms, vendors, increments and decrements based on work orders, alerts via email when low, and creating or managing a requisition when ordering. It is also challenging to have multiple people sharing or managing an XLS based inventory system. As you grow from the basic inventory management needs, look at true cloud-based robust inventory management software.
