9. Model of inventories

Chapter 9



by: josavere


Now with the Digital Revolution, using Artificial Intelligence (AI) that is designed to identify contexts and scenarios, work with some predictive models and make autonomous decisions and Big Data with the capacity to process huge volumes of information, the design of models is greatly facilitated to manage inventories of all kinds following the guidelines presented in this chapter.

With the extraordinary advance in technology and computers, which have developed specific models of handling inventories and the accumulation of statistics for great information-storage capacity, variables make key in the utilization of experiences. It is very important for the great counseling facility, to use every means possible for the display of information (visual display screen, diskette, CD, printed matter), in addition, to offering exactness and promptness, basic elements.
The market offers a variety of inventory software. Some paid leave; others, free; etc. Which to choose? Well, the one that best suits the characteristics and requirements of the company.

The companies utilize the software for inventory control. This begins with a physical count of units and his record in the software, feeding it with data like:

  • Inventory
  • The purchase price for the article (code)
  • The sales price for the article (code)
  • The percentage that is paid in taxes

To measure that he does a sale, from the box properly connected to a terminal, he gets registered for the computer and immediately makes the discharge of the goods.

The advantages that offer this software are:

a. It permits immediate knowledge and update of the inventory at any time.

b. Reduce considerably the costs of the handling of the inventory.

c. When the sale comes true not only has he done the discharge in the inventory but also in bookkeeping doing it more agile and efficient.

d. It establishes very important statistics for the overtaking for example, the average of the shopping that they vary the purchase prices, etc, the average price, and the percentage.

e. The experience using these systems permits creating statistics that inform on the periods that are bigger inventory, the goods more sales in different epochs, than tendency have the pricing in determined periods and epochs of the scarcity of certain products, etc.

f. It permits creating standards of minimum inventory and peak out of every product in particular and an average inventory that it should be had in an epoch or determined period. This software, as maintains always updated the inventory, has a very important option if a minimal level out of every one of products has been fixed; the moment that it takes place to the minimum inventory of an article immediately orders a buy.

Regarding the advantages, the software can offer exact and immediate information, so you can take action faster. Let's remember, when the company has information, better is the competitive advantage regarding them besides companies. For example, when they fix standards remaining given products can be foreseen ahead of time, or also the stagnation of certain goods in a period, making easy the decisions.

Also, the information takes advantage of databases that the software for Knowledge throws, as periods for shopping, are better, determining the level of optimal inventory, among others.


A model tries to find that I allow to us the request (or production, as the case may be) without falling in excesses but without losing sales or giving up producing for lack of finished product or of available raw materials.
Utilizing excel we can do a simple and easy program to apply what we described using some input and combining with some formulas that indicate step by step.

a. A database with all of the goods that fix the portfolio is prepared, registering the consumption of the three last months (month to month).

A1: D22

b. The consumption of three months under consideration to calculate the
Consumption of the trimester consolidates (E = B + C + D). 

c. Calculates the average daily consumed in the last ones three months dividing the consumption of the trimester by the number of working days (G = E / # of working days).

d. He shares the total consumption of three months (column E) of every article, among the grand total of inventory to estimate the percentage participation of every item around consumption (F = E1 / E20).

e. Orders him the column principle's F to the minor (looking for the Pareto principle).

f. They insert themselves like input, or input data, the numbers of existence, according to physical inventory at the final of the trimester (column H).

g. Dividing the column the H for F (consume per day) calculates the dais's number which we have supplied for itself (J = H / G).

h. In column K registered input data the number of days that we needed to be replenished by the supplier, addend amount that we have like buffer stock for any eventuality (Lead time). 

i. Utilizing the tool Conditional format we give the following instructions:
If column J is inferior, to column K, mark with red (alert little India), column J.
j. In the same way, utilizing conditional format, we compared J (days which we have supplied for), with participation in consumption (column F). If the participation in consumption is superior to participation in supplies also marked with red.

k. We took care of initially the goods that search two reds (critical zone) and we went on with the ones that registered one to make a decision, backing them up in the information of the model.

l. One proceeds for the month following inserting the data of the latter month and omitting the first month, thus: January—March—February

  • February—March—April
  • April—May—June
  • June—July—August
  • And so on like he can see oneself in the example:

Equally important he is to employ us of the goods that they find themselves stored in shelves and do not register movement with a good opinion, we can establish suchlike reports:

The previous model must be interpreted as a guide or auxiliary mathematics, in order to make the overtaking of decisions; it is essential for the executive's good opinion.

Copyright © 2020