Free Inventory Management Excel Template

We totally understand that some retailers may not be ready for advanced retail software like RetailOps. But regardless, you should be using some type of system or process to track your inventory. Perhaps you just want to see what additional metrics you should be tracking? That’s cool too.

So we thought, ‘who better to bring this system to you than the guys who wrote the book on Inventory Management’?

If you’re looking for a free and flexible inventory management tool (one that also has a dashboard!) for Microsoft Excel or Google Sheets, you’ve come to the right place. This is a simple and efficient template for inventory management - because Excel can only do so much.

It’s worth noting that this template is intended to be used in a historical format.

What does that mean and why does it matter?

Each event should have its own line of data. So when you receive inventory you will record that as one record. The same goes for when you sell or adjust your inventory - you will record that as one record. This method is beneficial for historical referencing, reporting, and accuracy.

Here’s an animated example:

We received 30 units of SKU EX1001 - a men’s black Nike shoe, size 11. Each shoe cost us $15. Here’s how we would record this event:

Hang on - someone has just bought a pair of these Nike shoes (EX1001)! This is how we record the event and decrement our inventory levels:

Note the -1 for the quantity. Sale and Adjust will typically be a negative number. A return will be a positive adjust.

Laying out events like this will give you useful historical information, which we will show and explain over time.

Mini Dashboard

The pivot table shown above is being automated by pulling in the data from the inventory sheet.

What’s Included In The Template?

The how-to-use and basics are down - let’s talk about why we chose to include what we did.

(Note: This template is also not a one-size-fits-all tool. We encourage you to customize your template to best fit your needs and business model.)

SKU Number (or custom ID)

The template starts with a SKU number that acts as our unique identifier. Every good inventory management process needs to have some sort of unique identifier to keep everything organized. You can use a custom ID that you created and assigned to a SKU if your process requires it, but for most retail businesses the SKU number is the recommended choice.

Timestamp

This is the date the event occurred. There’s clearly no need to go deep on the reasoning behind this column, except that it’s a must for data mining and reporting at a later stage.

Description

Describe the event or SKU. When you receive an item it makes sense to describe the SKU that was received. When you adjust or make a sale I would still recommend the same receiving description but then appending the action or reasoning. For example, let’s say that EX1001 sold on Amazon. We can write that description as ‘Mens Black Nike Shoe Size 11 - Sold, Amazon’. What if you are giving away a couple of pairs for a charity event? In this scenario I would use the ‘adjust’ reason followed by ‘Mens Black Nike Shoe Size 11 - Adjust, Boys and Girls Club’ as the description.

Quantity

This is another obvious one - how many SKUs were received, sold, adjusted, and so on.

Units of Measurement

This column may not fit or be necessary for every business. However, those that sell an assortment of products or have different accounting principles may find this field useful. UoM can be represented as units, sq ft, meters, liters, or pretty much any other unit of measurement.

Cost Per Unit

You should be tracking either the cost per unit or cost of goods sold. This is one of the most important metrics to keep your eyes on. Having strong accounting of your CPU or COGS will pay dividends to your business.

Here are some other fields included, that are not shown in my example:

Retail Price

Since this template is meant to be used as an inventory tracker including retail price as a column isn’t super critical. Regardless, I choose to include it along with an array formula to help determine revenue and costs.

Other things you may think about reporting on: cost to fulfill, sales channels, cost of revenue (including marketing expenses), among others.

Now you know how to use our Excel Inventory Management Template and what all the fields refer to, it’s time to get your hands dirty and use it!

Happy retailing!

P.S. On Part 2 of this template I will show you how to connect a Google Form to your new template to ramp up your tracking efforts in the shortest amount of time. Subscribe to stay up-to-date!