Stock balance by category
Stock movement at cost for a month: opening, receipts, supplier returns, cost of sales, write-offs, closing.
What the template is for
Reconciling the warehouse with the books and calculating turnover.
- What one row is
- one row = category × month
Columns
| Column | Code | Type | Required | Validation rule |
|---|---|---|---|---|
| Month | month | Text | yes | month as YYYY-MM |
| Category | category | List value | yes | a value from the category reference |
| Opening stock, thousand, at cost | begin | Number | yes | a number not below 0 |
| Receipts, thousand | receipts | Number | yes | a number not below 0 |
| Supplier returns, thousand | supret | Number | yes | a number not below 0 |
| Cost of sales, thousand | cogs | Number | yes | a number not below 0 |
| Write-offs and losses, thousand | writeoff | Number | yes | a number not below 0 |
| Closing stock, thousand | end | Number | yes | calculated: opening + receipts − returns − cost of sales − write-offs |
Example rows
| Month | Category | Opening stock, thousand, at cost | Receipts, thousand | Supplier returns, thousand | Cost of sales, thousand | Write-offs and losses, thousand | Closing stock, thousand |
|---|---|---|---|---|---|---|---|
| 2027-01 | women | 24,800 | 9,200 | 300 | 7,600 | 260 | 25,840 |
| 2027-01 | men | 10,400 | 3,300 | 0 | 3,300 | 90 | 10,310 |
| 2027-02 | women | 25,840 | 8,100 | 200 | 6,900 | 310 | 26,530 |
| 2027-02 | men | 10,310 | 3,000 | 50 | 3,100 | 70 | 10,090 |
The example data is illustrative: an exercise, replace it with your own.
Note
The next month opening stock must equal the previous closing stock; settle a gap against the stocktake on a separate losses line. The example data is illustrative: an exercise, not a norm or an industry benchmark.
How to open the file
The file is UTF-8 with a BOM, comma separated, with a decimal point. Set the text format for code columns (SKU, article, GTIN) so that long numbers and leading zeros are not distorted. The validation rules are suggested defaults, not norms: your systems and contracts may require something else.
Calculations that use these fields
- Days of inventory and excess stock
- Average stock for the year and turnover
- Opening stock: stock-to-sales and the monthly buying budget
- Reorder point and safety stock
- Stock age and price-reduction provision
- Customer credit limit and wholesale receivables aging
- Defects: losses, supplier recovery and a reserve
- EOQ: economic order quantity with a minimum lot and volume discounts
Documents that need this data
- Merchandise stock balance
- Stock turnover and dead stock
- Accounting for defects, mis-sorts and stock losses: acts, liable parties, corporate income tax
- Allocation and replenishment
- Management balance sheet
- The financial audit of a brand: P&L, profit bridge, loss map and checklist
- Financial condition: ratios, the Altman model with caveats and counterparty scoring
- Warehouse and fulfillment: slotting, order picking, capacity and peak, KPIs, own warehouse or a logistics operator
- Business insurance: cargo, property, liability, receivables, how to read a policy and when insurance pays off
- Reader question: how to count safety stock for rare sizes when demand is not normal
- Management report pack
- The six-month merchandise plan and model stock: sales, coverage, safety stock, purchases by month