Free Excel template
Stock management in Excel: the stock in and stock out sheet
An Excel file ready to fill in: your products on one side, your stock in and stock out on the other. Current stock, its value in dirhams and the items to reorder are calculated automatically.
What the file contains
“Produits” tab (Products)
One row per item: reference, description, unit, purchase and selling price excl. VAT, opening stock and reorder threshold. Stock in, stock out, current stock, stock value in DH and the “À commander” (to reorder) alert are calculated automatically.
“Mouvements” tab (Movements)
One row per stock in or stock out: date, reference picked from a list, type Entrée (in) or Sortie (out), quantity, reason or document number. The description fills itself in from the reference.
“Mode d'emploi” tab (Instructions)
The entry rules on one page, how to correct an inventory difference, and four sample products highlighted in yellow, to delete before you start.
How to use the spreadsheet
The file is in French, as are its tab and column names. Start with the Produits (Products) tab: one row per item, with a unique reference (two rows with the same reference would count the same movements twice). Opening stock is the quantity counted on the day you start.
Recording stock in and stock out
Every movement of goods becomes a row in the Mouvements (Movements) tab. The quantity is always positive: the type gives the direction.
- Entrée (in): delivery from a supplier, return from a customer.
- Sortie (out): sale, breakage, expired product, internal use.
- Write the document number (goods received note, invoice) in the Motif (reason) column: that is what lets you trace a mistake later.
Setting the reorder threshold
The threshold is the quantity below which you need to reorder. When current stock is less than or equal to the threshold, the row turns red and shows “À commander” (to reorder). A good starting point: what you sell during your supplier's delivery time, plus a small margin. The guide avoiding stockouts explains the calculation.
Taking inventory
Regularly count what is really on the shelf and in the depot, and compare it with the Stock actuel (current stock) column. If there is a difference, add an Entrée or Sortie movement for the difference, with the reason “Écart d'inventaire” (inventory difference). The difference stays visible in the history instead of disappearing into a silent correction.
The limits of an Excel spreadsheet
For a small business that is starting out, this spreadsheet is enough. But you should know its limits before trusting it with all your stock:
- One user at a time. The salesperson at the counter and the person receiving goods at the depot cannot enter data in the same file at the same time.
- No link with sales. A sale, an invoice or a customer credit takes nothing out of stock until someone copies the stock out by hand.
- Entry mistakes go unnoticed. A mistyped quantity or a forgotten stock out skews the stock without warning, until the next inventory.
- One unit per product. If you sell by the piece and by the case, you have to convert yourself on every row.
When the spreadsheet is no longer enough: Gestio keeps the stock for you
- Every sale lowers stock and every purchase raises it, without re-typing.
- Sell by the unit or by the case of 12: stock stays kept in a single base unit.
- Stockout alerts, and stock counts that bring differences to light.
- Several depots, if you have them.
- Your spreadsheet is not lost: import your products and customers from an Excel or CSV file, with a preview and a report of the rows in error before saving.
FAQ
Frequently asked questions
Is the stock management spreadsheet really free?
Yes. The file downloads without signing up and without an email address. You can modify it, adapt it to your business and share it.
Which software does the file work with?
It is an .xlsx file, in French. The formulas used (SUMIFS, IF, INDEX/MATCH) have existed in Excel since version 2007 and in LibreOffice Calc: no recent function that would show #NAME? on an older Excel.
How do I record a stock in or a stock out?
In the Mouvements (Movements) tab, add a row: the date, the product reference, the type (Entrée for a delivery or a return, Sortie for a sale, a breakage or an expired product) and the quantity, always positive. The current stock in the Produits tab updates.
How do I correct the stock after an inventory?
Count what is actually there, compare it with the Stock actuel (current stock) column, then add an Entrée or Sortie movement for the difference with the reason “Écart d'inventaire”. Do not touch the opening stock: the difference would leave no trace.
How many products and movements can the spreadsheet track?
The formulas are ready for 500 products and 5,000 movements. Beyond that, copy the formulas down. If you reach these volumes, it is often a sign that stock software would save you time.