Stock Control: what is it and how to do it?

stock control discipline
Excel Spreadsheets

O inventory control usually be confused as a purely operational activity. However, it encompasses several skills and can be decisive in the profit of an organization. In this article we will talk about all aspects of stock management: operation, accounting, financial, productivity and strategy.


What is Stock Control?

O inventory control covers the activities of planning, organizing and controlling the flow of materials in the organization. In other words, the handling and storage of raw materials, products (finished or unfinished), tools and equipment.

Some companies, mainly services, need only simple controls, not linked to their activity: warehouse, cleaning materials, food, among others. Companies that work with products - whether owned or resold - have in inventory management a key activity for their profitability.

stock control sheet

Why Stock Control?

Once we were called in to help a rubber shop understand why she lost money even though sales were "well." Really the company seemed in order, until we stepped into the stock. There was more than R $ 1 million in stopped tires in the stock. The owner explained to us that tire suppliers only sold kits. Some of the kits were luxury car tires with low demand. They were stuck in stock and were responsible for successive losses to the company. We recommend that they negotiate more advantageous deals with suppliers or join other rubber shops to make purchases.

This example seems to be unrealistic due to the size of the stock that the company had the most, but believe me, this is the reality of many Brazilian companies. Especially with many products, it becomes increasingly difficult to manage losses and gains.

Without communication, the sales industry will continue to sell. And the purchasing buying sector - or the producing production sector. Stock will continue to grow as long as there is room or the problem becomes unsustainable.

The stock of a company is a stopped capital volume.. In addition to the company spending money on storage, stopped capital means loss of money, since the company is giving up sales.

She could be spending space, time and resources with high turnover products, if stock management was being done well. Larger spin products also illustrate a common problem in inventory management. It is very common for a company to be registering an order from a customer, accessing the stock and ... It's over! Unsatisfied customer.

Curve ABC Stock: What is it and what is it for?

In addition to the abovementioned inventory management has a number of advantages, such as:

  • Efficient production management, geared to what turns more in stock
  • Efficient purchasing management
  • Improved communication between areas - purchasing, sales, inventory, logistics, finance.
  • Loss reduction
  • Productivity in general

stock control sheet

How to Do Stock Control

Inventory management has three critical success factors:

  • Physical management
  • Administrative-financial management
  • The coordination between the areas of the company that influence the stock: sales, purchasing, production, financial and operational.

Tips for Physical Inventory Management

Physical inventory management gains complexity as the business grows. With many subsidiaries, the company, for example, needs to decide how many distribution centers will have and how will the transit between them. The centers need to be treated as affiliates of the company, with some level of management of their own, but with central matrix management, too.

Smaller companies need to define just how they will organize the small physical space destined for activity and shipping activities, for the collection of deliveries or the arrival of new parts or products.

  • Separate products in different phases - unfinished products separated at the beginning of the stock, products finalized further.
  • Group married products - several companies usually make married sales. To facilitate shipment, it is interesting that complementary products are close.
  • Choose one in charge - no matter how many people use the stock, a person must wear the manager's hat, register and authorize the entrances and exits, and make inventories.
  • Take inventory from time to time - to analyze losses and counting errors and update the balance sheets.
  • Never leave to make records afterwards - for this, keep your stock control sheet, or other tool, open close to the physical location.
  • Train employees - it's no use waiting for zeal and commitment without teaching your team how to manage the stock. invest in them, if it is an end activity.

Coordination between company areas

In order for inventory control to succeed, the company must coordinate activities that relate to inventory: purchasing, sales, production, cash flow, etc.

The sales industry needs inventory to sell. The purchasing industry needs to create this inventory. The financial sector needs to have cash to make purchases. If the purchasing sector buys products that do not sell, the financial is out of cash and the sales sector is out of stock.

For companies that work with large inventories, coordination among their areas is a crucial strategic activity. Here are some tips to increase the efficiency of your inventory:

  • Map processes input and output - from the purchase of a product to its sale, there is a activity flowchart which must be fulfilled. Have them mapped to possible registered bottleneck zones.
  • Add inventory-related activities in job description - do not count on the good sense of your sales or sales professionals. Institutionalize communication between them.
  • Keep your controls up-to-date and accessible - be it a spreadsheet or a software, let employees know the products available before they sell or buy.
  • Stimulate stock burns - make aggressive promotions for smaller spinning products.
  • Use the PDCA cycle to improve inventory - the methodology will allow the processes of inventory management to be always in a regime of continuous improvement.
  • Registre lessons learned - stranded products, poorly executed processes, all errors of the operation can serve as learning in the future.

stock control sheet

Inventory Control and Accounting

inventory control procv formula individual analysisThere are two important aspects of stock control: physical and accounting. The physicist is about how your operation will be organized in practice. Storage of warehouses and distribution centers, transit of goods and equipment, dispatch and bureaucracy. The accounting part is about how the stock will be accounted for in balance sheet.

The accounting aspect sounds like bullshit, but I'll show you a quick example of how things can get complicated. Let's say you have a stationery, that is, a reseller business. You do the following operations:

  • 1 Operation: Buy 10 Pencils at R $ 1,00 - total of R $ 10,00 in stock
  • 2 Operation: Sale of 5 Pencils - total of $ 5,00 in stock
  • 3 Operation: Buy 10 Pencils at R $ 2,00 - total of R $ 25,00 in stock
  • 4 Operation: Sale of 10 Pencils - and now, I should consider that I have sold the 5 1,00 and 5 pencils from R $ 2,00 or sold the 10 of R $ 2,00 soon? We will talk about these possibilities in the next section, of inventory control methodologies.

To do this management correctly, you will need the help of a Inventory Control Worksheet.

stock control sheet

Stock Control Methodologies

The valuation method chosen will affect the total profit to be calculated for a given accounting period. Considering that several factors can vary the acquisition price of materials between two or more purchases (inflation, transportation cost, etc.), the problem arises of selecting the method to be used to evaluate the inventories.

Inventory control - inputs and outputs


First in first out (first in first out). At the PEPS method, the cost of the oldest batch is used when the merchandise is sold until the quantities of that stock run out, from there it goes to the second oldest lot and so on.

In our example of stationery, when making the 4 operation, selling 10 pencil units, we would consider the 5 units of R $ 1,00 (longer in stock) and 5 units of R $ 2,00. Total output of R $ 15,00 and the stock balance would be in R $ 10,00.


Last In, First Out (last in). The cost of inventory is determined as if the most recent units added to the stock (last to enter) were the first units sold (first out). At the UEPS method, the cost of items sold / withdrawn tends to reflect the cost of the most recently purchased items (bought or produced, and thus the most recent prices).

In our stationery example, when doing operation 4, selling 10 units of pencil, the 10 units of R $ 2,00 (last to enter) would be considered. Total output of R $ 20,00 and the stock balance would be R $ 5,00.

Medium Cost

O average cost method, also called weighted average or moving average method, is based on the application of average costs rather than actual costs. The method of valuation of the stock at medium cost is accepted by the Treasury and used widely.

In our stationery example, when doing operation 4, selling 10 units of pencil, the average value of each unit in stock would be calculated: (5 x 1,00 + 10 x 2,00) / 15 = R $ 1,67, 16,67. Total outflow of R $ 8,33 and the stock balance would be R $ XNUMX.

In Brazil, income tax legislation allows only PEPS and Medium Cost for purposes of cost accounting.

stock control sheet

ABC Curve - sorting inventory control information

A ABC curve is a method of sorting information in order to separate the most important items. Usually, they are at lower volume but represent high value. The objective of this method is that the manager can prioritize the management of the most valuable and representative items within the stock. And what do the letters A, B and C stand for?

  • Class A: Main items in stock and high priority. 20% of items correspond to about 80% of value.
  • Class B: Items that are still considered economically precious. 30% of items correspond to about 15% of value.
  • Class C: 50% of items that match about 5% of value.

inventory control

The interesting thing about the ABC curve is define different strategies for the 3 product groups. Class A products will have priority in repurchase, more refined methods of storage and transit and will be a priority in any and all decisions of the company.

Class C products will not necessarily have the same treatment. There are several ways use the ABC curve in stock management.

stock control sheet

Stock Turn and Repurchase Point

One of the key competencies of a retailer is to thoroughly understand the stock turnover for calculate the repurchase point. Let's illustrate the situation to make it easier to understand:

João usually keeps 200 cell phone units in stock. It sells 100 cell phones per month, that is, every 60 days, 2 months, the cell product completes a complete inventory turnover.

If he knows that the vendor works with minimum orders for 100 units and delivers within 30 days, the safety stock of this product is 100 units. Every time the stock hits 100 units, it reaches the point of repurchase, meaning it will need to make a new order of at least 100 new units.

If he misses this calculation or loses his repurchase point, it will lose sales as it will have no units in stock after 30 days.

stock situation

In this way, the stock safety formula, which defines the repurchase point is:

Security Stock = Average Daily Sales x Delivery Time (in days)

How to Organize Inventory Control on a Spreadsheet?

Did you see how important it is to track inventory of your business? LUZ has developed a Inventory Control Worksheet, specially prepared to help you in this role! Get this spreadsheet now and start improving your management in the next few days. You can download a demo version for free.

stock control sheet


Excel Spreadsheets


  1. Good afternoon,

    Have a question?
    I have a value in a cell (B32) that value changes every day.
    How do I get that when I change this value in cell (B32) to be in another cell (G2) but keeping the previous day's value in cell (G1)?


Please, write your comment
Please enter your name