Cheat Sheet: How to manage inventory if you don’t have fancy software

Managing inventory is important. It’s also challenging to do if you aren’t working for a large-scale organization that has barcode scanners and large databases that track all of your products. If you’re an athletic trainer, own a gym/clinic, or work in a health care setting you may not have access to these automated inventory systems. If you manage inventory where you work or for your own business, you may benefit from some tips I’ve learned when I have managed inventory at various places I’ve worked.

Organization is critical

• “Organized Chaos” doesn’t work here. Take the time to lay things out properly. People who haven’t ever been in your storage area should be able to walk in and be able to use context clues to find things. 

• Grouping things together makes the most sense: Tapes should be stored near tapes, first aid gear should be together, boots/braces/etc should be together, and so on. This matters when you’re trying to find something in a rush, if things have a place you know where to go.

• Organize sizes from left to right. We read from left to right so having the smallest size on the left and the largest on the right makes sense. 

• Heavy things go on bottom shelves, lighter things go on top shelves. 

• Be aware of the temperature in the space: some items don’t do well in hot/humid spaces. If things are getting ruined you ought to move them to a new location or discuss that with someone who manages where your inventory is stored.

Create a system of counting

• Once you have your space organized, it makes counting your inventory much easier. The reason is, if you have to hand count everything, you can save your time because the way items are laid out can save you time if you can group things together in your visual field and then add them together.

• As far as when you’re counting your inventory, I recommend doing it at least monthly. It helps to schedule it in your calendar so you don’t forget to do it. Obviously you can monitor your inventory daily, but having monthly full counts can help you spot patterns in how long items stay on your shelves. 

• It’s annoying and tedious to count everything, but worth it so you don’t have a shortage or outage of a critical piece of inventory at the worst possible time.

Log everything in an excel chart or with google sheets

I tracked all of our data in a chart online. I was slightly ‘old school’ and would do my counting on a print out of the chart I made. I could have used a laptop and directly input my counts into my chart, but I preferred doing it by hand.

Here’s an example of how I organized the inventory chart

Here are a few functions that I’ve used that really helped make things more efficient

Sum across tabs

For our tape stock, I wanted to be able to look in one place and see our stock levels of each tape. Setting it up is simple. Where you want the data to populate, type “SUM(“ this will then allow you to click on any cell to select it. You’ll go to the cell one one chart where you have data point one, click on it. Then type +. Then go to the next cell where you have data you want to add in and click that. When you’re done, hit enter and you’re done. 

Tip: format your charts so you can use the drag feature to carry a formula with excel/google sheets. This saves you time on typing formulas multiple times. 

You can also use this feature if you want carry over the same numbers as last time. Say a section of your inventory went unchanged, you can select that section and drag it to the next column 

Conditional formatting 

I set our inventory chart to automatically highlight itself yellow if the inputted number was 3 or less. That’s the number I chose, but use what’s best for you. The reason I set that feature up was so I could have my attention called to the level of 3 or less. It didn’t always cause me to order that item, but it made me aware of the potential need to order more or to see that we may not order that item frequently, etc

How to use conditional formating

Precent Change

• You can use percent change formulas if you want to see how quickly you’re using up an item

• I wouldn’t input this function into a chart, you can just do it off to the side for a quick reference

Take home points:

• Stay organized

• Don’t be afraid to reorganize your space if something isn’t working

• Count everything

• Track everything so you minimize surprises

• Use spreadsheets to do simple math for you

If you’re managing inventory and have questions or need any help, click the link below and I’ll be happy to help you!