Inventory tracking model

Rigorous inventory management is one of the keys to success for any company involved in the sale of goods. Poor inventory management can have serious financial consequences, tying up a significant portion of a company's working capital. To avoid this, it's essential to keep track of your inventory. You'll find a model here.

What are the different types of inventory?

Inventory refers to all the goods involved in a company's operating cycle, whether they are products to be processed, finished goods, raw materials, intermediate products or consumables.

A company's inventories are of vital importance to its development. They determine the company's ability to meet customer demand, but they also generate costs and can have a significant impact on the company's cash flow.

They must therefore be carefully managed and monitored. Stock must be sufficient to enable the company to operate normally and respond rapidly to customer demand, without being excessive so as not to generate excessive costs.

What are the risks of poor inventory control?

The two main risks associated with poor or non-existent stock monitoring are overstocking and understocking.


Overstocking, i.e. too much stock, has a number of negative consequences for a company, starting with higher costs. The larger the stock, the higher the storage costs: the company has to rent, and sometimes even build, additional storage space.

Handling costs also soar, since more personnel are needed, and/or for longer hours. The price of insurance also goes up.

At the same time, working capital requirements (WCR), i.e. the budget needed to meet operating costs, also increase, and capital is tied up. All the money held in stock, until the goods have been sold, is money that cannot be invested in other projects, and therefore prevents the company from growing.

Finally, stock quickly loses value - very quickly, in fact, in the case of perishable goods, for example. Summer clothes, for example, will be virtually unsellable by the time autumn arrives.


Conversely, too little stock can also have disastrous consequences for a company. It can disrupt the production chain, or even bring production to a halt. In the event of a stock shortage, losses are considerable, not to mention the negative impact on the company's image.

When customers are confronted with stock-outs, especially when they are frequent, they will quickly turn to the competition and are unlikely to return, which can lead to a gradual and lasting loss of sales.

Different inventory management and tracking methods

Order point method

This method of inventory monitoring involves defining a threshold below which stock levels are considered low, automatically triggering a new order. The predefined quantity of products ordered is fixed.

The point-of-order method prevents you from being caught unprepared, especially when the flow of stock is unpredictable and it's not possible to forecast replenishment dates. It also prevents the company from finding itself in a situation of overstocking or understocking.

The recompletion method

The inventory replenishment method involves the company replenishing supplies from its supplier(s) on a fixed date.

Unlike the reorder point method, the replenishment date is fixed in advance, but the quantity of products ordered is variable. The aim is to return to the previous stock level, which implies variations according to the number of sales made.

This method is suitable for regular sales.

The calendar replenishment method

In this case, replenishment is carried out on a fixed date, but also in fixed quantities. Most of the time, this method of inventory monitoring is governed by a contract signed by the company and its supplier.

It's simple to implement, but presents risks due to its lack of flexibility. It is not suitable for newly-established companies, which do not yet have sufficient visibility on their sales to anticipate dates and quantities sold.

The make-to-order replenishment method

This method of managing and monitoring inventory is extremely flexible, since both dates and quantities are variable. It avoids tying up too much stock, and therefore too much capital.

On the other hand, it requires very meticulous monitoring, and is not unanimously supported by suppliers, who have no visibility of the company's needs. The company is more exposed to the risk of out-of-stock situations.

How do I use the inventory tracking model?

The Inventory Tracking template, which is essential for effective company inventory management, consists of a "Receipts and Issues Journal" tab, an "Item Database" tab, and an "Inventory Statement" tab.

Inventory tracking model

In the "Receipts and dispatches log" tab, you must indicate which goods are being added to your stock, and which are being dispatched. Depending on your activity and the nature of the items making up your stock, receipts may correspond to a finished product or the receipt of raw materials, for example. Outgoing goods may correspond to the use of this raw material, or to the sale of the finished product.

You must enter the date, the product reference (e.g. barcode) and the corresponding quantities in the "In" and "Out" columns.

In the "Item database" tab, list and record the part numbers of all your items, and don't forget to add any new items to your catalog, or delete any items you've permanently retired.

Finally, in the "Inventory statement" tab, an Excel formula lets you simply enter the initial stock at the start of the activity period to find out the final stock.