Stock and assortment: inventory valuation, carrying cost, money in transit, budget by category, sizes, clusters and the SKU tail
The document brings together calculations that turn stock and assortment into decisions in roubles: how the valuation method changes profit, what holding and money in transit cost, how to split the budget between categories, how to restore the size curve, group stores, spread a new item and decide the fate of the assortment tail. The decision it supports: what to buy, where to hold and what to cut.
Why this document
Stock and assortment are usually discussed in units and shares, but decisions are taken in roubles. The valuation method shifts profit, the cost of holding and money in transit are hidden in the cost of capital, a curve by sales understates sizes without stock and the SKU tail looks loss-making by revenue although its price is the fixed costs. The document gives for each question a short calculation with the limits of applicability. It is read by the finance director, the buyer and the category manager before closing a period and reviewing the assortment.
At a glance
- What is this document and why is it needed?
- The document brings together calculations that turn stock and assortment into decisions in roubles: how the valuation method changes profit, what holding and money in transit cost, how to split the budget between categories, how to restore the size curve, group stores, spread a new item and decide the fate of the assortment tail. The decision it supports: what to buy, where to hold and what to cut.
- Who owns the document and how often is it rebuilt?
- Finance and category management: the finance director is responsible for the valuation method and the carrying rate, the category manager for sizes, clusters and the assortment tail; recounted once a season and when the key rate changes.
- What mistakes are the most common?
- Mixing valuation methods in accounting → reporting is not comparable → compare methods only in a managerial calculation. Taking only the credit interest into the carrying rate → the decision to hold looks cheap → add storage, shrink, obsolescence and handling. Counting only the transit days → money in transit is understated → weight the payments by days before receipt.
For whom
- Brand
- A brand holds stock both in production and in the warehouse: money in transit and the carrying rate matter more than for a retailer, and the SKU tail costs cards, samples and minimum batches.
- Retail
- A retailer sees sizes and clusters by store: take in-stock days and the category profile by point of sale and spread a new item by grades with a reserve.
What it consists of
Inventory valuation by three methods
- FIFO writes off the earliest batches, the remainder is valued at the latest prices; the weighted average takes one average price; the retail method converts the retail value of the remainder into cost with a ratio. All three give one total cost of batches but split it differently between the sold and the remainder.
- On three illustrative batches of 100 units and a sale of 180 units FIFO gives a cost of RUB 196,000 and a remainder of RUB 154,000, the average 210,000 and 140,000, the retail method 188,000 and 162,000; the gross profit spread is RUB 22,000.
- The method is fixed by the accounting policy; a comparison of methods is a managerial calculation, they cannot be mixed in accounting. The retail method applies when batch markups are close and requires counting discounts and revaluation. Check the permitted methods and the procedure with an accountant.
The cost of holding and money in transit
- The carrying cost rate is built from capital, storage, insurance and shrink, obsolescence and handling; at the illustrative 37.5% a unit of RUB 1,500 costs RUB 46.9 a month, an average stock of RUB 21 million costs RUB 7.9 million a year. Capital gives 59% of the rate.
- Money in transit equals purchases per day times the weighted term from payment to receipt: at purchases of RUB 120 million a year, an advance of 30% 85 days and a balance of 70% 40 days the term is 53.5 days, RUB 17.6 million is tied up, the cost of capital is RUB 3.9 million a year, a term shorter by 10 days saves RUB 0.7 million.
- Use one rate in all stock calculations and record the date: the key rate changes. Check the cost of capital against the financing terms in force.
Budget by category and sizes
- The budget is split in steps to the category with the largest marginal return; at illustrative parameters RUB 40 million are spread 18.6, 12.2, 9.2 million RUB, the profit is RUB 25.51 million against RUB 25.37 million by last year shares. The parameters are estimated over several seasons and the category minimum is limited.
- The size curve is restored by dividing the sales of a size by the days it was in stock: on illustrative data the share of size L is 31.1% against 21.9% by sales, 65 units were lost.
Clusters, a new item and the assortment tail
- Stores are grouped by the productivity index (sales per m² to the network average) and by the similarity of the category profile. New stores are not in the average. A new item without history is spread by the weights of grades with a central reserve for top-ups, rounding by the largest fractional remainder.
- The assortment tail is estimated by the cutting benefit: the fixed costs per SKU minus the part of the contribution that does not flow over. On illustrative bands it pays to cut 240 of 300 SKUs with a benefit of RUB 6.18 million; a cut share above one half requires checking the flow-over and image SKUs.
- The category forecast and the sum of SKU forecasts are reconciled by a scale with a share of trust in the category; the weather adjustment is applied only at a sufficient reliability of the link and inside the observed range.
Where the data comes from
| Data source | Fields |
|---|---|
| Accounting and warehouse | batches (quantity, purchase and retail price excl. VAT), sales and revenue, stock by size and in-stock days |
| Finance | the cost of capital at the date, the components of the carrying cost, the payment schedule to the supplier |
| Assortment and network | the number of SKUs, revenue and contribution margin by band, fixed cost per SKU, the area and sales of stores, category shares |
How to build it step by step
- Step 1: Check the valuation method against the accounting policy and compare it with the other two.
- Step 2: Build the carrying cost rate from five components and record the date.
- Step 3: Count money in transit by the payment schedule and compare with the price of speeding up.
- Step 4: Estimate the category parameters over several seasons and split the budget by marginal return.
- Step 5: Restore the size curve by in-stock days and buy by it.
- Step 6: Group stores into clusters and mark special profiles.
- Step 7: Spread a new item by grades with a reserve and top up by the sales of the first weeks.
- Step 8: Estimate the assortment tail by the cutting benefit and check image SKUs.
Formulas
| Measure | Formulas | Note |
|---|---|---|
| Stock by the retail method | (retail value of available − sales revenue) × cost of available ÷ retail value of available | Suits close batch markups. |
| Money in transit | purchases per year ÷ 365 × sum(payment share × days before receipt) | Negative days mean a deferral. |
| Cutting benefit of a band | fixed costs × SKUs − (1 − flow-over) × contribution | A positive number: profit is higher without the band. |
Formulas in notation
- Marginal return of buying
GM'(x) = \frac{a}{b} e^{-x/b}
a: the profit ceiling of the category, b: the saturation scale, x: the purchase.
Worked example
Three valuation methods on three illustrative batches: 180 units sold for RUB 376,000
| Method | Cost of goods sold, RUB | Stock, RUB | Gross profit, RUB |
|---|---|---|---|
| FIFO | 196,000 | 154,000 | 180,000 |
| Average | 210,000 | 140,000 | 166,000 |
| Retail prices | 188,000 | 162,000 | 188,000 |
The total cost of the batches RUB 350,000 is the same in all methods. All numbers are illustrative.
Figures
Data as a table
| Measure | Cost of goods sold |
|---|---|
| FIFO | 196 |
| Average | 210 |
| Retail prices | 188 |
Cost of goods sold by method. When purchase prices rise FIFO gives the lowest cost.
- positive
- negative
- total
Data as a table
| Step | Change | Level |
|---|---|---|
| Purchases per year | +120 | 120 |
| Money in transit | — | 17.6 |
| Annual cost of capital | +3.9 | 21.5 |
From purchases to money in transit. Prepayment makes money in transit larger than the physical stock.
Specifics by category
- Apparel
- Apparel: the size curve and the SKU tail are critical; clusters set the curve and the display.
- Footwear
- Footwear: a shortage of extreme sizes costs more, so the restored curve matters especially.
- Accessories
- Accessories: few sizes but many SKUs; the main question is the assortment tail.
- Kids
- Kids: height-sizes and the school season; obsolescence in the carrying rate is higher.
- Men
- Men: a carried-over range, the valuation method and the carrying rate matter more than novelties.
- Women
- Women: many novelties without history, their allocation by grades with a reserve is the main task.
Common mistakes
- Mixing valuation methods in accounting → reporting is not comparable → compare methods only in a managerial calculation.
- Taking only the credit interest into the carrying rate → the decision to hold looks cheap → add storage, shrink, obsolescence and handling.
- Counting only the transit days → money in transit is understated → weight the payments by days before receipt.
- Estimating category parameters from one season → the budget is fitted to an accident → take two or three seasons.
- Building the curve from sales at incomplete availability → large sizes are understated → divide sales by in-stock days.
- Cutting the tail by revenue without fixed costs and flow-over → you lose image SKUs → count the cutting benefit.
Russian specifics
- Inventory valuation methods (including those allowed by the accounting standard on inventory), the cost of capital and payment terms should be checked with an accountant and against contracts; the calculations are managerial and do not replace accounting.