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
*After purchase, you will be directed to the download page.

*Note this purchase will allow you to download the other versions at no extra cost. i.e. if you buy any of the 3 models, you will be directed to a download page with all 3 for the single price of $45.

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. 

No comments:

Post a Comment