ABC Sale Analysis, Stock Norm & Production Plan - User Guide
Navigate to Reports/Queries\Customer + Item Sale Analysis and click on "ABC Sale Analysis, Stock Norm & Production Plan".
For a step-by-step walkthrough with screenshots and video, see ABC Sale Analysis, Stock Norm & Production Plan.
What this report tells you
It tells you what to make next, and how much of it.
You run it, and you get one line per product with a quantity at the end — the amount you should produce to keep that product properly stocked. Products that sell fast are treated differently from products that barely move, so you are not carrying a warehouse full of slow stock while your best sellers run out.
The report works this out from your own sales. You do not enter forecasts or targets. It looks at what genuinely sold over a recent period and plans from that.
What you do decide is the policy — how much stock is worth holding for a fast seller compared with a slow one. You set that once, and the report applies it to every product consistently.
How it arrives at the number
Four steps. Once you have these, every column in the report makes sense.
Step 1 — It ranks your products. It totals what each product sold over your chosen period and puts them in order, best seller first.
Step 2 — It sorts them into categories. The ranked list is cut into groups. The top slice is A, the middle is B, the tail is C. You decide how wide each slice is.
Step 3 — It works out how much stock you should be holding. Each category carries a number of days — say 10 days for A, 20 for B, 30 for C. The report takes how much of that product sells on an average day and multiplies by those days. That gives the quantity you ought to have on the shelf.
Step 4 — It subtracts what you already have. Stock in hand comes off. What is left is what to make.
One detail worth knowing, because it surprises people: the categories are decided on sales value, but the buffer is worked out in quantity. That is deliberate. Value tells you which products matter to the business; quantity is what you actually produce.
So a cheap product selling in huge numbers may sit in category C — not because it is unimportant, but because it contributes less to turnover than its volume suggests. If that does not suit your business, you can rank by quantity instead.
Setting it up the first time
The first time you open the report there will be no saved settings, and it will not run until you create some. This is normal — the report cannot guess your stocking policy.
Open the settings, work down them, and save. It takes a few minutes and you only do it once. After that, running the report is a single click.
What you are deciding:
How far back to look at sales
Below
Your categories and how many days of stock each gets
Your categories and buffer days
What counts as a sale
Telling it what counts as a sale
Which branches hold the stock you can draw on
Which stock counts
How to round the production quantity
Below
You can save more than one set of settings under different names, which is useful if you plan different product ranges on different cycles — say a three-month view for regular lines and a shorter one for fast-moving seasonal stock.
How far back to look
Set the period in months or days. Three months is a sensible starting point. Long enough to even out a quiet week, short enough to reflect what is selling now.
Two things in this section are worth your attention:
How many days to divide by. To get an average daily sale, the report divides total sales by a number of days. If your business closes one day a week, dividing by every calendar day understates your true daily rate. You can set a fixed number of days instead.
Ignore the period before an item was created. Switch this on and a product launched part-way through the period is judged only on the time it has actually been on sale. Without it, a good new product looks like a poor old one.
Rounding the answer
At the end of the settings you can tidy up the production quantity so it is practical:
Round up to a batch size — so you are not asked to make 237 when your line runs in fifties.
Set a minimum — so tiny requirements are ignored rather than cluttering the list.
Take off work already in progress — so you do not order something twice.
Add pending sale orders — so goods customers have already ordered are produced on top of the buffer, not out of it.
Your categories and buffer days
This is the part that is genuinely yours to decide. It is a small grid:
A
10
10
B
50
20
C
100
30
That reads: my top 10% of products are category A and I want 10 days of stock for them. The next 40% are B and get 20 days. Everything else is C and gets 30 days.
Boundaries are cumulative, so each figure is a running total and the last row reaches 100.
You are not limited to three categories. Add rows for D and E if you want a finer policy.
Why slow movers often get more days
It looks backwards at first glance, but it is usually right. Your fast sellers are easy to replace — you make them often, so holding a small buffer is safe. A slow mover might only go through production occasionally, so if you run out you could be waiting weeks. More days of cover compensates for that.
If your production cycle is the same for everything, you may prefer the opposite. It is your policy; the report simply applies it.
Three ways to draw the lines
By share of products. The top 10% of the ranked list is category A. Straightforward and predictable — a good default.
By share of value. Take products down the list until they account for, say, 70% of your turnover; those are A. This suits businesses where a handful of products dominate sales.
By a fixed number. The top 100 products are A, the next 500 are B, the rest are C. Type the number in the boundary column instead of a percentage.
The fixed number is the practical choice if you have a very large catalogue. Ten per cent of fifty thousand products is five thousand — not a list anyone can work through. Setting A to 100 gives you a work list you can actually act on.
Two other useful options
Rank within item groups rather than across everything. Each group then gets its own A, B and C, so a strong group does not fill category A and leave your other ranges unplanned.
Show only one category once your policy is settled, so you can print just the A items for this week's planning.
Telling it what counts as a sale
Every number in the report comes from this, so it is worth five minutes of thought.
Inter-branch transfers — check this one first. When you move stock from your warehouse to one of your own branches, that movement can look like a sale. It usually is not one. At many businesses more goods leave the warehouse on transfers than on actual customer sales, so counting them makes every product look like it sells far more than it does, and scrambles the ranking.
Unless you have a specific reason not to, exclude inter-branch transfers. If your figures look inflated, this is the first setting to check.
The rest:
Net off sale returns
You want returns deducted, so a product with heavy returns is ranked on what it truly sold — usually yes
Include free, replacement and sample issues
You want giveaways counted as demand, because you still have to produce them
Include challans not yet invoiced
Goods have gone out but the invoice has not been raised, and you want them counted
Which value to rank on
Basic value, taxable value, or full net amount — basic or taxable keeps tax out of the comparison
Rank by value or quantity
Quantity is the better choice if your products are similar in price, or if value ranking is producing odd results
Which branches' sales to count
You want to plan from the whole company's demand, or only certain branches
Which stock counts
The report subtracts stock you already hold. You decide which stock that means.
Pick your head office branches. Usually the warehouses that feed production, not your retail branches. Stock sitting in a shop is not available to cover central production planning.
Then decide what is genuinely available. Some stock is in the building but already spoken for:
Reserved stock — set aside for a purpose
Stock allocated against sale orders — already promised to a customer
Pending delivery orders — due to go out but not yet despatched
You can take each of these off. It is worth doing. Stock that is already promised cannot cover your buffer, and counting it as available is how businesses end up short on their best sellers.
You can also narrow to particular godowns, and include or exclude bin stock.
One thing to know about past dates
You can ask for stock as it stood on an earlier date. If you do, you get the total that was on hand then — the breakdown into free, reserved and committed only exists for the present moment.
So if you pick a past date and notice the "less reserved" options no longer change anything, that is why. Use today's stock if you want those deductions to apply.
Reading your report
One row per product. The columns read left to right as the story of the calculation, so you can follow any row across and see exactly how its number was reached.
Company, Group, Item Name, Shade, Pack / Size
Which product this is. You choose which of these appear
Category
A, B or C
Sale Qty, Sale Value
What it sold in your chosen period
Avg Daily Qty
Sale quantity spread over the period
Buffer Days
Days of stock its category is entitled to
Reqd Buffer Qty
Average daily quantity × buffer days — what you should be holding
HO Stock
What you actually hold
Stock Value
Value of that stock
WIP Qty
Already in production
Pending SO Qty
Already ordered by customers
Production To Be Done
What to make
Production Value
Value of that production
A worked example
Take a row that reads:
A
1,200
13.33
10
133
40
93
Read across: this product sold 1,200 units over three months, which is about 13 a day. It is a category A product, so your policy says hold 10 days of cover — 133 units. You currently have 40. Make 93.
Every row works the same way. Once you have followed one, the whole report is readable.
Totals and grouping
Rows can be grouped and totalled by company or item group, so you can see production requirements by range rather than only product by product. If your business uses its own product classifications, those can be added as extra columns to group by.
If you do not need every column, several can be switched off for a shorter report.
Common questions
The stock total does not match my stock report.
By default this report only lists products that sold during the period, so its stock total covers only those products — not your whole warehouse. That is usually what you want for production planning.
If you need the totals to agree, switch on Include Zero Sale Items. Everything appears and the stock figure will then match exactly.
Be aware that this also changes your A/B/C split, because the categories are shares of the list and the list has just grown. Switch it on when you want to reconcile; switch it off when you are planning production. Do not leave it on permanently.
Category A has thousands of products in it.
That happens when sales are spread fairly evenly across a large catalogue rather than concentrated in a few lines. Ten per cent of a huge list is still a huge list.
Use the fixed-number method instead — set category A to a literal 100. You get a work list of exactly 100 products.
A product shows a negative production figure.
You have more stock than your policy calls for — a surplus. It is telling you not to make any, and by how much you are over. If you would rather not see negatives, switch on Floor Production At Zero.
Head office stock looks far too high.
Check whether you are grouping the report by company or item group rather than showing individual products. At a grouped level, if anything in that group sold, the group's entire stock is included. Switch to product level for a like-for-like view.
A product I know sells well is at the bottom of category C.
This can happen when a return processed during the period relates to an invoice from before it. The return is deducted but the original sale is outside the window, leaving a positive quantity with a negative value.
Switch Rank Products On to quantity and it will sit where you expect.
I added an extra grouping column and the categories changed.
Expected. Adding a grouping level changes the level at which products are ranked, so the categories re-form around the new grouping.
The figures look wrong generally.
Check two settings before anything else — the sales period, and whether inter-branch transfers are being counted as sales. Between them they explain most surprises.
Getting good results from it
Start simple. Three categories, a three-month period, transfers excluded. Run it, look at whether the products in category A are the ones you would have named yourself. If they are, the settings are right and you can refine from there.
Sense-check the first run against what you know. You already have a feel for your best sellers. If the report disagrees with that instinct, it is usually the sale definition — most often transfers being counted — rather than the report being wrong.
Review the policy occasionally, not constantly. Buffer days and category widths are a business decision. Once a quarter is plenty. Changing them every run makes your production plan jump about for no good reason.
Run it on a regular rhythm. Weekly or fortnightly works well. The value comes from acting on it consistently, not from running it once.
Use the fixed-number method if your catalogue is large. A list of 100 products you can act on beats a list of 5,000 you cannot.
Remember it is a proposal, not an instruction. The report applies your policy to your sales history and suggests a quantity. You still bring what it cannot know — a promotion coming up, a customer about to place a large order, a raw material you cannot get this month. Treat it as a well-informed starting point for your planning meeting.