Simplified Inventory Management Tool in Excel (in/out)

I have built more complex and advanced inventory tracking tools and templates for a single location as well as multiple locations in the past. The single location was more about determining cost of goods sold and looking at the accounting / financial impact of inventory movement. The multiple locations version looked more at simply the unit movements to and from the main warehouse to various locations and inter-movements to and from all locations and the warehouse.

What I have built now is a really simplified and user-friendly template for tracking the current state of your inventory. It has 3 main tabs.



Pricing
The templates will be sent to you after purchase via your PayPal/purchasing e-mail. This purchase will allow access to the other inventory templates mentioned above (advanced single location and multiple locations) at no extra cost. This is included in the Accounting Templates for Excel bundle.

Main Inventory Template Tab Functions
  • In- where you enter inventory coming in.
  • Current State- where you can see the on-hand inventory of all your sku's in one spot (filters added to headers)
  • Out- where you enter inventory leaving
The current state tab is where you define your unique list of inventory items by sku/upc and then with up to 3 descriptions. 

You will also define a given re-order level where the row will change colors if the on-hand amount gets at or below the trigger level.

You will see the on-hand inventory amount per sku/upc. That calculation will sum up all the "units in" less all "units out" plus the "starting balance" of each item.

In order for you to have a way to have a starting balance of units per each inventory item, you will be able to define that number in its own column. This will effect the on-hand amount and allow you to periodically remove all the data in the in/out tab and still have a unit count balance for each item that will then change with new in/out movements. This is to try and make the template more evergreen with on-going data.

I added a monthly summary for the change in $ value in and out as well as the change in unit counts each month at a consolidated level.

You might also be interested in this cash flow planning tool for inventory re-stocking: http://www.smarthelping.com/2018/06/12-month-inventory-re-stocking-and-cash.html

or the re-ordering templatehttps://www.smarthelping.com/2019/05/when-and-how-much-inventory-to-re-order.html

as well as this FIFO compliant COGS inventory valuation tool in excel

Screenshots of Template:
inventory tracker

inventory purchasing tracker

inventory management Excel

tracking inventory balance (count)

tracking inventory balance (value)