By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.
Stock sheet: more effective explanations and alternatives
2/9/2024
Every business should be able to track the status of its inventory. To do this, there is a fairly simple method, which consists in creating inventory cards. This is a register in which all inventory movements are entered manually.
How do I create an inventory sheet and use it properly? Is it really effective and are there alternatives? Let us explain these types of registers.
An inventory sheet: what is it?
Definition
An inventory sheet (or register) is a document that allows you to track and manage the various levels of inventory effectively. It includes detailed information on:
the quantities of goods or raw materials available;
Inventory movements over time (products, dates and quantities ordered sold, renewal, prices...).
The 4 levels of inventory
To successfully create these records effectively, you need to know the 4 levels of inventory of a company:
Maximum inventory: This is the one not to exceed to avoid overstock;
Theoretical inventory: This is the inventory level that the company believes it has thanks to the information in the inventory sheet;
Available inventory: it corresponds to the inventory actually held and which is counted in the inventory;
Minimum inventory: it is the inventory below which you could find yourself running out of stock.
Usefulness of the inventory sheet
The inventory sheet is a useful document for several reasons:
You know exactly the state of your reserves at a given moment, thus avoiding obsolete or forgotten inventory;
You avoid overstocking and therefore the mobilization of capital;
You avoid stockouts, making it possible to ensure customer satisfaction;
You quickly assess the value of your inventory because the unit price is in the inventory sheet and you can calculate different values such as the inventory turnover rate;
You identify future product orders and the goods to be sold first.
Create your inventory sheet effectively
You can create your inventory sheet on an Excel spreadsheet. You are going to use The weighted average cost (WAC) method at the end of the period. We know, it may seem complex... It is an average defined at the end of the month, of the purchase prices of goods according to the quantities purchased.
The first step in creating this document is to gather all the details of the product being sold:
its name;
its commercial margin rate;
its average unit purchase price;
its average selling price.
Second, you need to make three columns:
Input;
Output;
Final stock.
For each product output and output, add sub-categories to these columns, including:
the date;
the name;
the quantity of stock;
monetary value.
Finally, fill them in chronological order and include a column to calculate the margin rate.
Use the inventory sheet properly
Once you have created your inventory sheet, the work does not end there, quite the opposite! The challenge for good inventory management is to keep your records up to date as often as possible. You should record all purchases and sales, without exception.
For this, you can use delivery notes and exit vouchers. Here's what the three are for and how they're connected:
delivery notes: they allow you to update the inventory sheet as you go;
inventory sheet: you control stock inventory, streamline order management and avoid stock shortages;
exit vouchers: they allow you to update the inventory sheet.
To keep your inventory sheet up to date, it's simple, follow these steps:
The entrances: as soon as you receive a delivery, you must record the quantities of products entering into the stock sheet. You must absolutely fill in the date of receipt, the quantity received, the lot number...;
The outings: when you ship products, the corresponding quantities are recorded as outputs in your stock sheet. You should note the release date, the quantity shipped, the destination...;
The name of the movement: all stock movements must have a label in order to be accurately traced. For example, “customer sale [customer name]”, “internal delivery”, or even “supplier return”. So you will know exactly the reasons for the stock movement.
The limits of the inventory sheet
Even if Excel is still a good free tool for creating inventory sheets, it is not a miracle solution:
Lack of connectivity with business solutions and the company website: as it cannot connect to other software, you must manually gather the data in your accounting software or CRM for example.
Mandatory manual entry: if your inventory sheets are controlled by an entire team, it is likely that there will be errors. Making the wrong column or forgetting to add information regarding your goods can have serious consequences on overall inventory management. In other words, your inventory sheet cannot be 100% reliable.
Not very secure: with an Excel document, all you need to do is simply duplicate the inventory sheet to be able to change the data, despite security macros and cell locking. Changing incorrect data can have disastrous consequences for inventory management.
No automatic update: you have to update supplies, raw material usage, and sales by hand every day, which can be a waste of time.
Lack of global visibility: Since the data in your register is not updated in real time and becomes obsolete quickly, it is difficult to have an overview of your inventory.
No task automation: due to the lack of connectivity of your Excel files with other software, the lack of real-time data updates, and the automatic generation of reports and analyses, it is not possible to automate processes.
Fortunately, there are solutions to counter the limitations of these manual records: inventory management software.
The alternative to the inventory sheet
To optimize the management of your stocks, digitalization and automation have become essential, which is why it is important to use an inventory management system.
This tool allows you to:
improved productivity thanks to numerous functionalities designed to make day-to-day business easier;
automate tasks with low added value allowing considerable time savings and to focus on important actions for the business, such as your sales strategy;
greatly reduce the risk of human error and therefore to have reliable information available;
update entries and exits in real time of your goods to be able to anticipate stock shortages or optimize the use of the storage area for example;
have overviews and reports that help you make the best decisions.
Erplain, a complete and affordable SaaS solution for small businesses and SMEs, effectively meets your inventory management needs. The software offers you real-time visibility on all storage locations, thus avoiding stockouts. Replenishment is simplified with integrated order points and alerts, while purchase orders are created in a few clicks. In the end, you considerably improve your sales strategy and the control of your goods. No need to create inventory sheets in Excel anymore!
As you will see, Erplain offers an intelligent solution to manage your SME/VSB in real time.
