Syntha Lab RU

Excel for retail, part II: ABC analysis and the open-to-buy (OTB) budget in a spreadsheet

The second part of the track shows how to assemble in a spreadsheet two working models of a buyer and a planner: ABC analysis of the range (which positions give the main revenue) and the OTB purchase budget by month (how much can be ordered without creating an excess). The document gives the structure of sheets, formulas with cell references, the order of building and control checks; the same logic is counted by the Lab calculations, and the spreadsheet is needed when the model has to be shown, edited and passed on in parts.

Finance Brand and retail Verified: 2026-11-15

Open interactively →

Why this document

ABC and OTB give a buyer and a planner two main answers: what the revenue rests on and how much can be purchased. A spreadsheet is chosen when a colleague has to see and edit the model: it is clear where every number comes from, you can change coverage or markdowns and immediately see how the budget changes. The risk is the same: a hidden error in a formula carries a purchase decision with it, so the document has control checks and input cells are separated from formulas. It is read by a buyer, a planner, the CFO and an analyst.

At a glance

What is this document and why is it needed?
The second part of the track shows how to assemble in a spreadsheet two working models of a buyer and a planner: ABC analysis of the range (which positions give the main revenue) and the OTB purchase budget by month (how much can be ordered without creating an excess). The document gives the structure of sheets, formulas with cell references, the order of building and control checks; the same logic is counted by the Lab calculations, and the spreadsheet is needed when the model has to be shown, edited and passed on in parts.
Who owns the document and how often is it rebuilt?
Kept by a planner or an analyst; ABC is recalculated once a season and after big sales, OTB every month; the file goes to the buyer and the CFO with a control sheet.
What mistakes are the most common?
Brand: Count ABC by revenue only: a model with large revenue and a small margin falls into A and takes the place of profitable ones. Brand: Type the opening stock by hand into each month: the chain breaks and an edit of one month does not move the rest. Brand: Count “on order” without confirmations: OTB comes out overstated and then the launch runs into a shortage of material.

For whom

Brand
A brand counts ABC by models and channels and OTB in the role of a production and materials purchase plan: the ending stock comes from the collection’s sales forecast and “ordered” from confirmed orders to the factory.
Retail
A retailer counts ABC by categories, brands and stores and OTB by categories and months at retail ex-VAT; the ending stock comes from coverage in weeks and “ordered” from confirmed orders to suppliers.

What it consists of

Structure of the workbook

  • The sheet “Data”: a flat table of positions with revenue or margin for the period; one key column, one value column, no totals inside.
  • The sheet “ABC”: a sorted list, the share, the cumulative share and the class; the class thresholds lie in separate cells at the top.
  • The sheet “OTB”: a column per month; rows: sales, markdowns, losses, ending stock, opening stock, on order, OTB; inputs are marked with color as in Lab exports.
  • The sheet “Control”: convergence checks, each must give zero; the sheet is opened first for any edit.

ABC analysis in a spreadsheet

  • Sorting: Data → Sort by value in descending order; the formulas of the share and the cumulative share recalculate by themselves after sorting.
  • The share: =value / SUM(all values); the cumulative share: =previous cumulative + share; for the first row the cumulative equals the share.
  • The class by the cumulative share before the row: =IF(accumulated before the row < threshold A; “A”; IF(accumulated before the row < threshold B; “B”; “C”)); the boundaries of 80 and 95% are illustrative and put in cells.
  • Brand: ABC is counted by the model’s margin and not only by revenue: a model with large revenue and a small margin must not fall into A without a reservation.
  • Retailer: ABC by categories and by brands is read together: a brand of class A in a category of class C is a signal to review the place on the shelf.
  • XYZ is added by the coefficient of variation over months: =STDEV(range) / AVERAGE(range); the ABC and XYZ link is described in the mathematical document.

OTB in a spreadsheet

  • The monthly formula: OTB = sales + markdowns + losses + ending stock − opening stock − on order, all at retail ex-VAT; the OTB cell refers to the rows above.
  • The ending stock of a month: =next month’s sales × coverage in months; for the last month of the plan the next month’s sales are entered in a separate cell.
  • The opening stock: the actual stock is entered for the first month, for the rest =the ending stock of the previous column; so the chain is linked without manual transfers.
  • Brand: The row “on order” takes confirmed orders to the factory; the brand’s OTB means how much can still be launched without an excess by the end of the season.
  • Retailer: OTB at retail is converted into cost by multiplying by (1 − the category IMU); the IMU of the segment itself, not the chain average.
  • A negative OTB means over-ordering: it must be canceled, shifted or sold, not “made zero”.

Model control

  • The sum of ABC shares equals 100%, the cumulative share of the last row equals 100%; the sum of values on the ABC sheet equals the sum on the “Data” sheet.
  • The opening stock of every month equals the ending stock of the previous one; a difference means a broken reference.
  • Brand: A check of the assumption about coverage: the ending stock converted into weeks does not go beyond the category’s boundaries and the production lead time.
  • Retailer: Reconciling OTB with the finance plan’s purchase budget: the sum over categories does not exceed the approved budget of the season.

Where the data comes from

Data sourceFields
Sales by positionsRevenue or margin by models, categories, brands for a season or a year, ex-VAT.
The sales plan and coverageSales by the months of the plan, the next month’s sales, coverage in months, the share of markdowns and losses.
Stocks and ordersThe actual opening stock, confirmed orders by months of delivery, the category IMU.

How to build it step by step

  1. Step 1: Put the position data on the “Data” sheet as a flat table and check the total with the accounting system.
  2. Step 2: Brand: decide by what to count ABC: revenue, margin or units; for a collection margin is more often taken.
  3. Step 3: Sort the positions in descending order, add shares, the cumulative share and the class; put the thresholds in separate cells.
  4. Step 4: Check that the sum of shares equals 100% and the number of positions of the classes sums to the number of rows.
  5. Step 5: Retailer: enter the plan sales by months, the share of markdowns and losses, the coverage and the next month’s sales in separate cells.
  6. Step 6: Build the rows: markdowns = sales × share; ending stock = the next month’s sales × coverage; opening stock by a reference to the previous ending.
  7. Step 7: Brand: add the row “on order” from confirmed orders to the factory and count OTB as the balance of a possible launch.
  8. Step 8: Count OTB by the formula and convert it into cost; put the sums by months and the total for the season on the control sheet.
  9. Step 9: Run scenarios: change coverage or markdowns in one cell and write down how the total and the negative months changed.

Formulas

MeasureFormulasNote
Share and cumulative shareshare = value ÷ sum; cumulative = previous cumulative + shareThe class is assigned by the cumulative share before the row: A up to 80%, B up to 95% (illustrative boundaries).
OTB of a monthOTB = sales + markdowns + losses + ending stock − opening stock − on orderAll at retail ex-VAT; to convert into cost multiply by (1 − IMU).
Ending stockthe ending stock of a month = the next month’s sales × coverage in monthsCoverage is set in steps by the season: higher before the peak, lower towards the end.
Coefficient of variationCV = the standard deviation ÷ the mean over monthsFor XYZ: the lower the CV, the steadier the demand.

Worked example

OTB for six months at retail prices ex-VAT, RUB thousand, an illustrative example

MonthSalesMarkdownsLossesEnding stockOpening stockOn orderOTB
September9001891,8001,900300527
October1,20036122,2501,8002501,448
November1,50075151,9502,2502001,090
December1,300156131,2001,9500719
January80014481,0501,2000802
February7007071,1251,0500852
Total for the season6,400499641,1251,9007505,438

Conditions: coverage of 1.5 months of the next month’s sales, losses 1% of sales, the sales of the month after the plan 750 thousand ₽, the opening stock of the first month 1,900 thousand ₽. The opening stock of every following month equals the ending stock of the previous one. OTB for the season is 5,438 thousand ₽ at retail ex-VAT, that is 2,066 thousand ₽ at cost at IMU 62%. ABC on the same conditions: of 10 positions class A is 5 and gives 82.5% of revenue, class B 3 positions (15.0%), class C 2 positions (2.5%). All numbers are illustrative.

Figures

Share of a position in revenue, % (an illustrative ABC for ten positions)
Bar chart: M-1 26.3, M-2 19.4, M-3 15.6, M-4 11.9, M-5 9.4, M-6 6.9051015202530M-1M-1Share of revenue: 26.326.3M-2M-2Share of revenue: 19.419.4M-3M-3Share of revenue: 15.615.6M-4M-4Share of revenue: 11.911.9M-5M-5Share of revenue: 9.49.4M-6M-6Share of revenue: 6.96.9M-7M-7Share of revenue: 55M-8M-8Share of revenue: 3.13.1M-9M-9Share of revenue: 1.91.9M-10M-10Share of revenue: 0.60.6%
Data as a table
MeasureShare of revenue
M-126.3
M-219.4
M-315.6
M-411.9
M-59.4
M-66.9
M-75
M-83.1
M-91.9
M-100.6

Share of a position in revenue, % (an illustrative ABC for ten positions). Positions of class A (5) give 82.5% of revenue; the tail of 2 class C positions gives about 2.5%.

Cumulative share of revenue, %
Line chart: Cumulative share: 26.3 → 100020406080100M-1M-3M-5M-7M-9M-10Cumulative share, M-1: 26.3Cumulative share, M-2: 45.6Cumulative share, M-3: 61.3Cumulative share, M-4: 73.1Cumulative share, M-5: 82.5Cumulative share, M-6: 89.4Cumulative share, M-7: 94.4Cumulative share, M-8: 97.5Cumulative share, M-9: 99.4Cumulative share, M-10: 100% of revenuePositions in descending revenue
Data as a table
Positions in descending revenueCumulative share
M-126.3
M-245.6
M-361.3
M-473.1
M-582.5
M-689.4
M-794.4
M-897.5
M-999.4
M-10100

Cumulative share of revenue, %. The curve grows fast on the first positions: crossing 80% and 95% gives the boundaries of classes A and B.

What the OTB of the first month consists of, RUB thousand
Waterfall: Sales +900, Markdowns +18, Losses +9, Ending stock +1,800, Opening stock −1,900, On order −300, OTB 52705001,0001,5002,0002,5003,000SalesSalesSales: +900+900MarkdownsMarkdownsMarkdowns: +18+18LossesLossesLosses: +9+9Ending stockEnding stockEnding stock: +1,800+1,800Opening stockOpening stockOpening stock: −1,900−1,900On orderOn orderOn order: −300−300OTBOTBOTB: 527527RUB thousand
  • positive
  • negative
  • total
Data as a table
StepChangeLevel
Sales+900900
Markdowns+18918
Losses+9927
Ending stock+1,8002,727
Opening stock−1,900827
On order−300527
OTB—527

What the OTB of the first month consists of, RUB thousand. Sales, markdowns, losses and the ending stock increase the budget, the opening stock and what is on order decrease it.

Specifics by category

Apparel
In apparel ABC is counted by models and coverage in OTB is stepped: higher before the seasonal peak and lower before a sale. Markdowns in the winter and summer branches differ, and rare sizes must not give a false class C; they are looked at in a separate slice.
Footwear
In footwear coverage is higher because the size run is needed, and OTB is counted by the model-size pair where the run breaks. Markdowns are deeper at the end of the season and ABC by models hides holes in sizes, so a pivot by sizes is placed alongside.
Accessories
In accessories there are many positions and the average receipt is low: ABC by quantity gives another picture than by revenue and both are counted. Coverage is higher because of the display minimum, and gift peaks shift OTB to November and December.
Kids
In children’s OTB is tied to the seasons of school and kindergarten and to the child’s growth: sizes become obsolete quickly and markdowns at the end of the season are harsher. ABC by age groups and sizes shows which groups feed revenue and which are left as stock.
Men
In the men’s segment there are more basic positions and the demand is steadier: ABC is stable between seasons and coverage can be held lower. Markdowns are smaller and OTB is updated less often; XYZ is useful for separating stable basic positions from seasonal ones.
Women
In the women’s segment there are more fashion positions and seasonality is stronger: ABC changes faster and the share of markdowns is noticeably higher. Coverage for fashion positions is lower, for basic ones higher; ABC and OTB are counted by branch, not by the whole category.

Common mistakes

  • Brand: Count ABC by revenue only: a model with large revenue and a small margin falls into A and takes the place of profitable ones.
  • Brand: Type the opening stock by hand into each month: the chain breaks and an edit of one month does not move the rest.
  • Brand: Count “on order” without confirmations: OTB comes out overstated and then the launch runs into a shortage of material.
  • Retailer: Define the class by the cumulative share after the row rather than before it: the last position crossing 80% ends up outside A.
  • Retailer: Count OTB at retail with VAT: the purchase is overstated by the tax rate and the warehouse is by the same percent above the plan.
  • Retailer: Take a negative OTB for zero: the over-ordering stays in the plan and the excess surfaces at a sale.

Russian specifics

  • Retailer: Retail prices are counted ex-VAT; the general VAT rate in Russia is 22% (from 01.01.2026), for some goods the rate may differ; check the rate by position. Rule: Law 425-FZ of 28.11.2025: Federal Law of 28.11.2025 No. 425-FZ (amendments to parts one and two of the Tax Code; VAT 22%).
  • Brand: Confirmed orders to the factory and stocks of materials in the table must match the accounting: a discrepancy with the books on stocks is looked for before the purchase is approved.
  • Retailer: The valuation of stocks and the reserve for a decline in value are made under accounting rules; OTB is a management model and does not replace accounting, check with an accountant. Rule: Law 402-FZ of 06.12.2011: Federal Law of 06.12.2011 No. 402-FZ on accounting.
  • Brand: In the EAEU countries VAT rates and the rules of documentation differ: in OTB for sales to another country take the local rate and local documents, check them.

Open interactively →