You run a clothing shop and want to answer, without walking the shelves: which colour and size has sold out, what should I order this week, and how much money is sitting in stock. Counting by model hides the real gaps. You may have eighteen red T-shirts and still have none in size S, which is the one a customer asks for.
This free Excel sheet gives every colour-size combination its own code and works out its balance from one movement log. The sheet and its page are in Arabic: clothing shop inventory sheet. All free sheets are listed at /templates (in Arabic).
What is inside the sheet
- Start here: how to use it and the colour key.
- Items: one row per code: model, colour, size, purchase price, selling price, reorder level and opening stock. The sheet calculates the receipts, sales, returns and adjustments for each code, its current balance, its value at purchase price and at selling price, and its status.
- Movements: one line per event: date, code, movement type (receipt, sale, customer return, supplier return, adjustment), quantity and a note. You pick the code and the type from lists.
- Summary: number of codes, total pieces, stock value, how many codes need ordering and how many are out, and the totals of each movement type.
- Why still on Excel?: when a sheet stops being enough.
A worked example (sample data)
These are the sample numbers in the sheet, not a real shop. There are two models, a cotton T-shirt (purchase price 120, selling price 220, reorder level 4) and a classic shirt (250 and 450, reorder level 3), each in red and black and sizes S, M and L: 12 codes. The log covers September and early October.
- TS-RED-S (red T-shirt, S): opening stock 10, sales of 4, 3 and 3. The balance is 0 and the status is out.
- TS-BLK-S (black T-shirt, S): opening 10, sales of 3 and 4. The balance is 3, at or under the level of 4, so the status is reorder.
- SH-RED-S (red shirt, S): opening 6, sold 2, returned 1 to the supplier because of a stitching fault. The balance is 3: reorder. SH-BLK-M also ends at 3 and shows reorder.
- TS-BLK-L (black T-shirt, L): opening 10, and a stocktake adjustment of -2 ("shortage found at stocktake") on 20 September, then a customer return of 1 on 3 October. The balance is 9.
- TS-RED-M: opening 12, one sale of 2, a customer return of 1 ("wrong size") and an adjustment of +2 ("surplus found at stocktake"): 13.
In the summary: 12 codes, 84 pieces in stock, stock value 15,280 at purchase price and 27,680 at selling price, 3 codes to reorder and 1 out. The log holds 28 pieces received, 50 sold, 2 customer returns and 1 supplier return.
Notice red T-shirts: 0 in S, 13 in M, 5 in L. By model that is 18 pieces and looks fine. By code it shows exactly what to buy.
Why these features matter
- One log, no separate stock cards. The balance is calculated from the movements, so it cannot disagree with them.
- A status for every code, not only a number. "Out" and "reorder" are the lines you act on.
- Stocktake adjustments are recorded, not hidden. A shortage appears with a date and a note instead of silently changing a total. If you want to understand reorder levels more generally, the logic in reorder point and safety stock is the same, even though that article is written for a pharmacy.
- Value at cost and at selling price, so you see both the money tied up and the sales potential.
- Free, no sign-up, and it works in Excel 2016 and later, Google Sheets and LibreOffice.
Using the online version
The template page opens directly on the worked example above. You edit the white (input) cells right in the page and the grey calculated cells update immediately. You can import your own .xlsx file made from this template, export your work to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server.
Start by replacing the 12 sample codes with your best-selling models, give each its opening stock and reorder level, then log this week's sales.
Common mistakes
- Writing a sale as a negative number. Keep every quantity positive and choose the type; only adjustments are negative.
- Making up a code per model instead of per colour and size.
- Setting the same reorder level for everything. A fast size needs a higher level than a slow one.
- Skipping the stocktake, so the sheet and the shelf drift apart.
When the sheet stops being enough: somebody has to type every sale and receipt, and the file lives on one device, so whoever edits it last wins. Softwiro's Accsys gives each colour-size combination its own barcode, deducts it from stock at the time of sale and restores it on a return, and syncs between all the shop's devices after an offline stretch.
Next step
Choose your five fastest models, create a code for each colour and size, enter today's counted stock as the opening balance, and log the next week of sales. The first "reorder" you see will be the sheet paying for itself.