Source: https://oguzerol.com/en/variant-stock-tracking-app/

[![Example scenario: a black M is returned and a black L is issued; both movements are linked to one exchange, and the new stock stays in a preview until staff approve it.](https://oguzerol.com/gorseller/senaryo/variant-stock-tracking-app-senaryo-en.webp)

View full size](https://oguzerol.com/gorseller/senaryo/variant-stock-tracking-app-senaryo-en.webp)

# Variant Stock Tracking: Size and Color Inventory, Channel Buffers and Reorder Points

[Oğuz Erol](https://oguzerol.com/en/about-me/)  |  21 min read

Variant stock tracking means treating every color, size or fit combination of a product as its own stock unit: black 41 and black 42 are two shelves of the same model, each with its own quantity in the warehouse, in every store and on every sales channel. Running variant stock in a spreadsheet has three traps: the model total looks healthy while the best-selling size runs out; the same pair sells on two channels while the stock file waits for its next upload; and a count shows one size short and the next one over, and both are written off as loss. I build custom web apps that close these traps with explicit rules and that a team uses with different permissions per role.

People rarely search for this by name: in October 2026, Google Keyword Planner showed “size and color inventory tracking” at zero monthly searches in both the US and the UK.

Below I follow one month of a fictional footwear brand. In apparel or spare parts only the variant name changes (fit instead of size, compatible vehicle instead of color).

## The variant matrix: separating the product family from the stock unit

A variant matrix is the table that lists every color and size combination a model is actually sold in, each with its own stock keeping unit (SKU). *Sample data:* a sneaker called KS-210 comes in three colors (black, white, brown) and eight sizes (EU 36 to 43). Brown is never made in the three smallest sizes, so of 24 theoretical combinations the app opens only the 21 that are sold; a closed combination never appears in a selection list.

Every SKU follows one pattern: `KS210-BLK-41` (model, color, size). The barcode belongs to the SKU, not the model. A discontinued variant is deactivated, not deleted, so its sales, counts and returns stay on record.

Six terms appear on this page, always with the same meaning:

- **Physical stock:** pairs on the shelf.
- **Available stock:** physical stock minus pairs allocated to orders and pairs set aside as damaged; the quantity that can be offered for sale. I cover order allocation on the [reserved stock tracking app](https://oguzerol.com/en/reserved-stock-tracking-app/) page.
- **Buffer:** the part of available stock never shown to a channel. It equals the highest number of units the other central-warehouse channels (excluding this one) sold, in any one of this channel’s longest update intervals, over the last eight weeks. It is calculated per variant and per channel.
- **Channel cap:** an upper limit on the promotion’s extra sales, the pairs one channel may sell above its normal sales during a set period (for example, a promotion).
- **Reorder point (ROP):** the level at which available plus incoming stock should trigger a replenishment order.
- **Paired variance:** two count differences of equal size and opposite sign in two sizes of the same model and color, at the same store or warehouse (−3 in black 42, +3 in black 43).

## Sample company and sample month: four channels, one stockout, one oversell

*Sample data, fictional.* A footwear brand using contract manufacturing: 140 models, 2,380 active SKUs, one central warehouse and six stores that sell only from their own shelves. The brand’s webshop and two marketplace storefronts (A and B) sell from the central warehouse, and every central-warehouse figure below covers only those three channels. Manufacturing lead time is 21 days.

In the app’s first month, Marketplace A is connected and receives available stock every fifteen minutes (an example interval). Marketplace B is not; its stock file is still uploaded by hand at 9:00 and 16:00.

| first month | pairs / count |
| --- | --- |
| Store sales (6 stores) | 5,200 |
| Webshop | 1,600 |
| Marketplace A | 2,000 |
| Marketplace B | 600 |
| **Total sales** | **9,400** |
| SKUs that raised a reorder alert | 46 (39 approved, 7 deferred as end-of-season models) |
| SKUs that hit zero on at least one day | 18 (11 from promoted models) |
| Orders canceled because of overselling | 3 (all on Marketplace B) |
| Month-end cycle count | 52 model-color groups, 410 SKUs; 23 SKUs with a difference |

Four things happened that month.

**Day 9, the alert.** `KS210-WHT-40` (white 40) opened with 150 pairs; the three online channels together sold 4.5 a day. At the end of day 9 available stock fell to 110, below its reorder point of 111. The planner approved the order on day 10; with a 21-day lead time it would arrive on day 31.

**Days 14 to 21, the stockout.** The e-commerce and marketplace lead had joined a seven-day promotion on Marketplace A without telling planning. White 40 shipped 90 pairs that week: 31 at the normal rate (webshop, Marketplace B and A’s usual share) and 59 as extra promotion sales on A. Stock hit zero on day 21 and stayed there until the day 31 delivery, about ten days, while the KS-210 family total on screen still read 312 pairs.

**Day 16, the oversell.** Black 41 started the day with 6 pairs. By 13:40 the webshop had sold 2 and Marketplace A 4; A showed zero within fifteen minutes. Marketplace B, still on the morning file that said 6, took 3 more orders before the 16:00 upload, and they were canceled.

**Month end, the count.** Differences appeared in 23 SKUs: 14 formed 7 paired variances (for example −3 in black 42 and +3 in black 43 in store 4), and 9 were single differences (for example −2 in brown 39 in store 2).

**The month in total.** About 43 pairs of white 40 demand went unfilled between day 21 and day 30. The figure comes from a day-by-day count, not 10 × 4.5 = 45: 2 pairs on day 21, then 9 days × 4.5 = 40.5, 42.5 in all (an estimate, assuming demand kept its normal rate). 3 orders were canceled for overselling.

**Month-end decisions.** The operations manager set two rules. Variants going into a promotion must reach planning at least one lead time (21 days) ahead; with less time, the promotion runs with a channel cap calculated by the formula below, which may come out at zero. And until Marketplace B is connected, a variant whose available stock is at or below its own buffer on B (11 for black 41) is not sent to B.

## Rules table

All rules below are *my proposal* for the sample company; thresholds depend on your sales velocity, lead times and channel agreements.

| rule | my proposal | what it prevents |
| --- | --- | --- |
| What channels receive | channels get available stock, not physical stock; a pair allocated to an order is invisible to other channels | one pair promised to two orders |
| Buffer and low stock | shown to a channel = available stock − buffer, never below zero; buffer as defined above, per variant and per channel | selling the last pairs while an update is pending; overselling on manually updated channels |
| Channel cap | formula below; calculated by the planner, approved by the operations manager, removed when the promotion ends | one channel draining all stock |
| Reorder point | formula below; trigger when available + incoming ≤ ROP; parameters refreshed every four weeks | a size running out unnoticed |
| Count variance approval | counts cover a whole model-color group; paired variances approved by the operations manager, single differences up to ±2 pairs by the warehouse lead or store manager, above that by the operations manager; nobody approves their own count, and those differences go to the operations manager | a wrong variant pick being written off as loss; a variance closed by the person who found it |

## How is a reorder point calculated per variant?

A reorder point is calculated for each SKU separately, because size 40 and size 36 of the same model never sell at the same pace. The starting formula I use:

**ROP = average daily sales × lead time (days) + safety stock** **Safety stock = 1.65 × standard deviation of daily sales × √lead time**

The standard deviation measures how much daily sales swing: small for a size that always sells 4 to 5 pairs, large for one that sells 0 one day and 12 the next. The 1.65 factor aims for roughly a 95% chance of not running out in each replenishment cycle, assuming a fixed lead time and independent, normally distributed daily sales; an assumption, not a guarantee.

Safety stock and buffer are different things. Safety stock is the margin allowed for demand swings during the lead time, and it decides when to reorder; it is not hidden from channels, stays on sale and is protected only in the channel-cap calculation. The buffer is never shown to a channel and only covers that channel’s update delay: only the buffer is deducted from what a channel is shown, never the safety stock.

*Sample data, white 40:* from the promotion-free days of the last eight weeks, average daily sales 4.5 pairs and standard deviation 2.0 (sample values). Lead time 21 days.

- Safety stock: 1.65 × 2.0 × √21 = 15.12 → rounded up, **16 pairs**
- ROP: 4.5 × 21 + 16 = 110.5 → rounded up, **111 pairs**

Leave promotion days out of the average and the deviation, or both inflate the ROP; promotion demand is added separately, below.

In Excel the same calculation uses these columns: `A` SKU, `B` average daily sales, `C` lead time in days, `D` standard deviation of daily sales (from the last 8 weeks of promotion-free daily sales with `STDEV.S`), `E` safety stock, `F` ROP, `G` available stock, `H` incoming stock, `I` decision.

- `E2`: `=ROUNDUP(1.65*D2*SQRT(C2),0)` → 16
- `F2`: `=ROUNDUP(B2*C2+E2,0)` → 111
- `I2`: `=IF(G2+H2<=F2,"Reorder","")` → “Reorder” when G2 = 110 and H2 = 0

**What the formula does not know is the promotion.** Had it been known at the start, expected lead-time demand would be 14 normal days × 4.5 + 7 promotion days × 13 (90 ÷ 7 = 12.9 a day, rounded to 13) = 154 pairs, and the ROP 154 + 16 = 170. With 150 on hand the order goes out on day 1 and arrives on day 22. Without a cap, stock still runs out on day 21 and the safety stock is gone.

Hence the second tool, the channel cap:

**Cap = available stock at the start of the promotion − expected normal-rate sales until the delivery arrives − safety stock** (never below zero)

The cap limits only the extra pairs the promotion brings; the channel’s normal daily share is already inside the 4.5-a-day rate.

- Order placed on day 1, delivery on day 22: 92 − (days 14 to 21, 8 days × 4.5 = 36) − 16 = **40 pairs**. Stock ends day 20 at 21 and day 21 at 17, never below the safety stock of 16.
- Promotion learned only on day 14, delivery on day 31: 92 − (17 days × 4.5 = 76.5) − 16 = −0.5, so the **cap is 0**. White 40 could only have joined that promotion by knowingly eating into its safety stock.

A cap does not raise the total number of pairs sold: stock is fixed, and the cap only changes which channel gets the pairs and when stock runs out. Compare within the same delivery timing:

- **Order placed on day 1:** without a cap, stock runs out on day 21 and 2 pairs of normal demand are lost. With a cap of 40, 19 of Marketplace A’s 59 promotion pairs go unfilled; those 19 stay in stock.
- **The real month (order on day 10, delivery on day 31):** without a cap, 59 promotion pairs sold and about 43 pairs of normal demand were lost. With a cap of 0, no normal demand is lost, the 59 promotion pairs go unfilled, and about 16 pairs remain when the delivery lands.

The cap’s real gain is twofold: insurance (if the delivery is late, the uncapped month stays empty longer, the capped one does not) and the margin difference between channels (if a promotion pair earns less than a normal sale, the cap keeps stock for the more profitable one). Weigh it with your own margins and what days without stock cost you; I did not measure those in the sample data.

**For slow-moving variants the formula breaks down.** For brown 43 at 0.2 pairs a day, average and deviation come from a handful of sales. I suggest forecasting at family level and splitting by the last two seasons’ size share (say, size 43 at 4%), or a simple min/max range; the screen shows each SKU’s method. How a below-threshold variant becomes a purchase request is on the [minimum stock and purchasing app](https://oguzerol.com/en/minimum-stock-purchasing-tracking-app/) page.

## Sharing stock across channels: where does overselling come from, and how big should the buffer be?

Overselling happens when two or more channels believe the same pair is available at the same moment, and the cause is almost always the update interval.

I do not give the buffer as a percentage; I use the definition above. A channel’s own sales are left out of its buffer, on the assumption that the channel deducts its own orders from its own listing (to be confirmed per channel); stores are excluded because they sell from their own shelves. For black 41 (sample values):

- **Marketplace A** (every fifteen minutes): the highest sales by the webshop and Marketplace B in any fifteen-minute window were 2 pairs. Buffer 2; with 6 pairs available, A is shown 4.
- **Marketplace B** (by hand at 9:00 and 16:00): two gaps, 7 hours by day and 17 hours overnight. The webshop and A sold at most 7 in the daytime gap and 11 overnight, so the buffer is the larger, 11. With 6 available, 6 − 11 is negative and is floored at zero: black 41 is not listed on B. On the incident day the webshop and A sold 6 in the daytime gap, within the daytime figure of 7.

The honest consequence: if you upload stock by hand twice a day, do not sell low-stock variants there. The cost is measurable: in the sample data the rule pulls 290 SKUs from B, which brought in 85 of B’s 600 pairs (about 14%) the month before; count that from your own last eight weeks first.

**I make no promises about marketplace or e-commerce connections.** How often and by what route stock reaches a platform depends on what it offers sellers: an API, a file upload or an integration partner. I do not promise real-time sync before I have seen the connection. If it is missing or limited, the app prepares the current file for that channel and the buffer is sized to its real update interval.

## Count variances: a paired variance can point to a wrong variant pick, not a loss

With variant products, some count differences are a wrong variant pick, not loss. If black 42 was scanned at checkout instead of black 43, or two boxes swapped shelves, one shows short and the other over. Correcting them separately as loss and surplus creates two wrong records and hides the real problem, the checkout selection or the shelf layout.

For pairing to work, the count unit is the model-color group: all sizes of one color of a model are counted the same day. Otherwise the +3 in black 43 behind the −3 in black 42 may never be counted and the pair stays invisible. That is why the sample month’s 410 SKUs come from 52 model-color groups.

When a count closes, the app shows paired variances side by side. The match is a suggestion: the approver checks checkout records or the shelf. An approved pair is posted as one correcting movement between the two variants; single differences follow the rules table. A pair repeating in the same store is flagged, since that store’s checkout selection or shelf labels need checking. In the sample month 14 of 23 differences (7 pairs) closed this way.

Count sessions, barcode scanning and keeping stock unchanged until approval are covered on the [barcode inventory count app](https://oguzerol.com/en/barcode-inventory-count-app/) page.

## Roles and audit trail: who sees what, who changes what?

| action | warehouse lead | store manager | e-commerce and marketplace lead | planner | operations manager |
| --- | --- | --- | --- | --- | --- |
| View stock | central warehouse | own store only | central available stock and channel listings | all locations and channels | all |
| Receipts, issues, transfers | enters in central warehouse | enters in own store | — | — | corrects |
| Buffer | — | — | sees | app calculates; raises manually with a reason | approves manual changes |
| Channel cap | — | — | proposes | calculates | approves |
| Reorder alert | sees | sees | sees | approves or defers with a reason | sees |
| Count | counts or has warehouse staff count | counts or has store staff count | — | — | — |
| Single difference approval (up to ±2) | approves counts they did not do | approves counts in own store they did not do | — | — | approves counts the warehouse lead or store manager did themselves |
| Single difference above ±2, and paired variances | — | — | — | — | approves |

The audit trail records who changed what, when, the old and new value and the reason: buffer manually raised from 2 to 4, campaign cap opened or closed, ROP parameters refreshed, variance approved. Each of the 7 deferred alerts shows “end-of-season model” and who deferred it.

## What does AI do here, and what does it not do?

Stock quantities, buffers, caps and reorder points are calculated by the table, not by AI, and a person approves every decision. AI has two jobs, both suggestions:

1. **Demand forecast suggestion.** From past sales, the promotion calendar and season, it suggests a four-week range per variant, such as “95 to 140 pairs” for white 40; the planner picks the demand for the ROP. For slow sizes and new models the family-share method above replaces the range. A promotion missing from the calendar is invisible to it, so AI would not have prevented the sample month’s stockout.
2. **Variant matching from descriptions.** It suggests matching a line such as “Men’s sneaker black size 41”, or delivery-note text, to `KS210-BLK-41`; uncertain lines go to an unmatched queue. It never creates a SKU or changes a quantity.

No row reaches the model without passing the user’s permission filter. A forecast needs product codes, descriptions and quantities; customer names and addresses are not sent. If order files contain personal data and the model runs at a provider in another country, then if you operate in the EU or UK, data-protection law such as EU GDPR or UK GDPR may apply to that transfer. I do not give legal advice; I provide a written list of the fields sent to the model. **If you operate in Turkey**, that transfer falls under [Article 9 of Law No. 6698](https://www.mevzuat.gov.tr/mevzuatmetin/1.5.6698.pdf) as amended by [Article 34 of Law No. 7499](https://www.resmigazete.gov.tr/eskiler/2024/03/20240312-1.htm) (Official Gazette 12.03.2024, no. 32487, in force since 1 June 2024). I read the article as amended in the consolidated text on mevzuat.gov.tr on 15 October 2026.

## Where does a spreadsheet break with variant stock?

Excel calculates the reorder point. It breaks on collecting data and on simultaneous selling:

- **Channel reports arrive in different shapes.** One file writes `KS210-BLK-41`, another “KS-210 Black 41”; `VLOOKUP` errors on the unmatched row, or silently returns zero if errors are wrapped, and across 2,380 SKUs nobody sees it.
- **A stock file is a snapshot.** It cannot know about a pair another channel sold after upload; that is the sample month’s 3 cancellations.
- **Count corrections overwrite the cell.** Version history shows who changed the file, not why black 42 went down by 3 or whether the other side is in black 43.

## If your ERP or e-commerce platform already does this, you do not need this app

Do not commission a separate app if:

1. You sell on one channel, or your channels do not share stock.
2. Your ERP or e-commerce platform already has a variant matrix, per-channel buffers and marketplace connections, and your team actually uses them.
3. When you measure your last three months, these four numbers come out low for you: (a) variants that hit zero for even one day, and the days each stayed there; (b) orders canceled for overselling; (c) differences found in counts; (d) promotions launched without telling stock planning. The sample month had 18 SKUs, 3 cancellations, 23 differences and 1 unannounced promotion. Estimate lost sales as days at zero × normal daily sales (white 40: 2 pairs on day 21 plus 9 days × 4.5, about 43 pairs), multiply by your margin, and compare with an add-on for your current system or the custom app’s server and maintenance cost.
4. Your count differences are small and who approved them is not an audit question for you.

Fixing the existing system’s settings usually takes less effort then. A custom app pays off when channels share stock and your system cannot hold the channel rules (buffer, cap, paired variance approval), or the team has drifted back to spreadsheets. This app’s first screen shows only SKUs waiting on an alert.

## How do you start, who owns the data and code, and how do users sign in?

In automation work, the mistake I see most often is picking the most complex process first. So the first version covers a few best-selling model families, the central warehouse and the one channel with the clearest connection. Where it fits, I link your current stock file to the variant matrix, and opening stock for the families in scope is confirmed with one count. We test the variant matrix, available stock and buffer rule against real movements, then add stores and other channels. Once I have reviewed your current setup, I send the scope and delivery schedule in writing.

The app runs on a server (a VPS) and the records live in its database. The server can be opened in your name. The data is yours, and you can export all of it as Excel or CSV at any time. The full source code sits in a repository you have access to, delivered with written run instructions, a dependency list and known limitations. Keys and accounts are opened in your name as well. There is no per-user license; the cost is the monthly server fee plus maintenance, and adding users in a small or mid-sized team does not change it.

Everyone signs in with their own username, sees and changes the fields that belong to their job, and every change is recorded with who made it. It works in the browser on phone and computer. Where payments, personal data or critical operations are involved, I assess access, security, hosting and maintenance requirements separately.

Tell me how many models and SKUs you have, which channels you sell on and which files you use to update stock today on the [contact page](https://oguzerol.com/en/contact/), and we can map your variant matrix and channel rules as the first step.

## Frequently Asked Questions

### Is a returned pair put back on sale straight away?

My proposal is no. A returned pair shows in physical stock as “awaiting inspection” but does not join available stock; if it passes, it moves to available, and if it is damaged it is set aside as damaged stock. A marketplace return is posted when the item reaches the warehouse and is inspected, not when the channel announces it. This is a design proposal; we set the inspection step around your returns process.

### How is stock recorded when a customer exchanges for a different size?

A return is recorded for the incoming variant and an issue for the replacement, linked under one exchange so quantities never mix and the report shows which size was swapped for which.

### Store sales come from our checkout system. How do they reach the app?

Through whatever your checkout (point-of-sale) system offers: an exported end-of-day file, a scheduled transfer or the system’s own connection. I will not say which is possible before seeing the system. If we start with files, store stock updates at end of day rather than during it, which is why that store’s stock is not offered to online channels.

### How do you set a reorder point for a new model with no sales history?

The formula cannot be used in the first season. A temporary threshold is set from a similar model’s sales pace and size share, and it is marked “temporary” on screen. Once a few weeks of real sales build up, the variant moves to its own calculation. Whoever set the temporary threshold is recorded by name in the audit trail.

[Back to top ↑](https://oguzerol.com/en/variant-stock-tracking-app/#icerik)

## You may also find these useful

[Custom Inventory Tracking Software Your Whole Team UsesWhen off-the-shelf inventory software doesn't fit, I turn your stock sheet into a web app with warehouses, reserved stock and roles your team uses on phones.](https://oguzerol.com/en/inventory-tracking-system-setup/)[See Stock by Warehouse on One ScreenSee quantities by warehouse, goods in transit and received transfers in one custom panel, starting from your Excel or Google Sheets records.](https://oguzerol.com/en/multi-warehouse-stock-tracking-app/)[Separate Reserved Stock from Available QuantitySeparate physical stock, reserved quantities and available stock in a custom app built around your Excel or Google Sheets allocation workflow.](https://oguzerol.com/en/reserved-stock-tracking-app/)

[Browse all articles →](https://oguzerol.com/en/articles/)

WHAT YOU CAN ASK FOR

## How do I work?
Work-hour packages

Tell me about your business and what you need on WhatsApp or by phone; we agree on **a work block of somewhere between 5 and 40 hours** and I start doing what is needed. What you get is not a calendar window — it is net working hours spent directly on your business. I can use 40 hours working three days straight, or deliberately spread it out and finish it over a year — whatever the work calls for. I do not spend my time estimating how long something will take, I spend it doing the work; every hour I spend leaves a lasting result in your business. We keep going for as long as you find it useful.

WORK BLOCKS

## Why not monthly?
Is 40 hours a week?

On a fixed monthly fee both sides are watching the wrong thing: you watch a fixed cost, the other side watches how many more contracts like this they can line up. Working by the hour puts us both in front of the same thing — the real value of the work that got done. I know not having an upfront answer to "how much will this cost in total?" is the natural cost of this model. But instead of paying a fixed fee for months to a setup that is not producing results, you only pay for the qualified time spent on your work — and you see exactly where that time went, start to finish. That is why my clients keep coming back.

5hours300€ + VAT60€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%205-hour%20work%20block.)

10hours500€ + VAT50€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2010-hour%20work%20block.)

20hours800€ + VAT40€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2020-hour%20work%20block.)

40hours1,400€ + VAT35€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2040-hour%20work%20block.)