Warehouse Inventory Control Free Excel spreadsheet

Warehouse Inventory Control Free Excel spreadsheet.

Although it is not recommended to manage a warehouse inventory control performed with Excel, may be in some occasion or for a specific function can be useful to you.

It is not advisable to carry out a warehouse inventory control with Excel because normally you have to manage a large number of incoming and outgoing movements and inventory adjustments of a great number of products, in addition, warehouse movements are usually related to other processes in the warehouse company.

For example, outgoing movements are usually related to sales orders, in this way it confirms whether the sale order has been completely delivered or has been partially delivered. On the other hand, incoming and outgoing documents (delivery notes) are used to automate billing processes.

And an even more complex matter is the valuation of the inventory in monetary terms, something essential to keep a correct accounting, since different valuation methods can be used such as the standard cost, the average cost, fifo method or lifo …

Anyway, if you just need something simple, a little control of inputs, outputs and adjustments to calculate the ending inventory, you can download this template for free.

I explain how warehouse inventory control works with an Excel spreadsheet that you can use for free.

All inventory control starts from an initial balance, just like the balance of the accounting accounts.

Everything depends when the inventory control starts. If the company has just been created, it will not have inventory, so there will be no beginning balance.

If the company is already operating and does not keep a warehouse inventory tracking control, one day without activity in your company, you will have to count the existing inventory and write it down as the beginning balance of the inventory.

From that moment, movements begin to be counted or recorded.

In any warehouse there are always 3 types of movements.

Incoming movements. These can be originated from a supplier, another replenishment warehouse, an in-warehouse location, or a customer return.
Outgoing movements. The destination of these movements can be a customer, another warehouse, a location within the warehouse or the return to a supplier.
Movements due to inventory adjustments. These movements are made when we verify that the physical inventory does not match the inventory registered in the control system. Therefore, an adjustment will have to be made in the control system to adapt it to reality.

If we apply the incoming, outgoing and adjustment movements to the initial inventory, it will provide us with the final inventory. That simple.

The formula is as follows:

Final Inventory = Initial Inventory + Incomings – Outgoings + inventory adjustments. The inventory adjustments can take a positive or negative sign.

The file Warehouse Invetory Excel Template is structured as follows:

A table to define the customers, another to define the suppliers, a table to define the products, another table to record the initial inventory of the products and that at the same time will calculate the final inventory.

And the most important table, the inventory movements, this is where the type of movement must be recorded, the product that generated the movement and the quantity.

In the table where initial inventory is recorded, the next column shows the calculated final inventory. Then you can check the changes derived from the movements.