Minimum Version Required: 25.03
Pre-Conditions
Global Settings:
Product Ranking will be calculated based on the product requirements listed below, however, in order to display product ranking codes within PO Generator, setting 3306 - Enable Product Ranking Codes for Forecasting must be set to True.
Review Forecast Locations to confirm your configuration for ranking period and ABC percentage thresholds
Percentage A - D must equal 100
Forecast Locations - Create Forecast Records = True
Product Requirements:
Products must be on a verified invoice during the ranking period for Sale account type customers
Products must belong to a forecast group
Products have to be in a product type where Include in Sales Reports by Default = True
Steps
Business Case:
Product Ranking Codes can be used for fulfillment reporting, prioritization of purchasing and warehouse management.
Ranking Percentages:
Rankings will be based on the cumulative totals for cases and dollars sold using the A, B, C, and D thresholds described below.
Percentage A: These are the products in the top the "A" percentage of total cases or dollars sold for the location. For example, if percentage A = 80, this is the top 80% of total cases or dollars.
Percentage B: These products represent the next "B" percentage of total cases and dollars sold for the location. For example, if B is 10 and percentage A is 80, then B products contribute to the next 10-20% of the cumulative total.
Percentage C: These products represent the next "C" percentage of total cases or dollars sold for the location. For example, if C is 5 and percentage B is 10, then C products contribute to the next 5-10% of the cumulative total.
Percentage D: The bottom percentage or the remaining products represent the bottom "D" percentage of cases sold. For example, if D is 5, then D products contribute to the last 5% of your total cases sold.
-When setting the percentage ranking thresholds. The total of A - D should add up to 100.
-Default values set by the system are A - 80, B - 10, C - 5, D - 5.
Other thresholds examples could be:
A - 90, B - 5, C - 3, D - 2 (A is Top 90%, B is the next 5 - 10%, C is the next 3 - 5%, and D is the remaining 2%)
A - 75, B - 15, C - 5, D - 5 (A is Top 75%, B is the next 15 - 25%, C is the next 5 - 15%, and D is the remaining 5%)
A - 60, B - 30, C - 5, D - 5 (A is Top 60%, B is the next 30 - 40%, C is the next 5 - 30%, and D is the remaining 5%)
Product Ranking Codes:
Product Ranking Codes are two-dimensional, giving users the insights for both cases and dollars with a single ranking. The possible values are AA, AB, AC, AD, BA, BB, BC, BD, CA, CB, CC, CD, DA, DB, DC, DD. Each product is given a 2-character ranking code. The first letter is for volume or cases, the second letter is for sales dollars. So a BA product has a B ranking for case sold and an A ranking for $ volume.
How often are Product Ranking Codes updated?
The Set_ProductRankingCodes scheduled task will run weekly on Fridays at 10:00 pm
How Product Ranking Codes are calculated
The following examples will use 80, 10, 5, 5 for the A, B, C, D percentages respectively.
Step 1: Calculate Total Cases Sold and $Vol
Calculate the total cases sold and $vol across all qualified invoices and products based on the Forecast Locations Ranking Period. Options include 3, 4, 6, and 12 months.
Step 2: Sort products by Units Sold
Sort the products in descending order of Cases sold. This ranking will allow you to determine which products are in the top 80%, next 10-20%, and so on.
The same will be done for $Vol
Step 3: Calculate Percentile Cutoffs
Once sorted the products, the system will determine the cutoffs for each category:
Category A (Top 80%): These are the products with the highest sales volume (the top 80% of your total cases sold).
Category B (Next 10-20%): These products represent the next 10-20% of total cases sold.
Category C (Next 5-10%): These products represent the next 5-10% of total cases sold.
Category D (Bottom 5%): The remaining products represent the bottom 5% of cases sold.
Step 4: Assign Categories (A, B, C, D)
Example: Say there are 10 products with the following total cases and $vol sold over the last 12 months. Note that Product 7 has a higher $Vol, but lower case volume than product 6.
Product | Cases Sold | Cumulative Total Cases | Product | $Vol | Cumulative Total $Vol |
1 | 1,000 | 1,000 | 1 | $30,000 | $30,000 |
2 | 900 | 1,900 | 2 | $27,000 | $57,000 |
3 | 800 | 2,700 | 3 | $24,000 | $81,000 |
4 | 700 | 3,400 | 4 | $21,000 | $102,000 |
5 | 600 | 4,000 | 5 | $18,000 | $120,000 |
6 | 500 | 4,500 | 7 | $16,000 | $136,000 |
7 | 400 | 4,900 | 6 | $15,000 | $151,000 |
8 | 300 | 5,200 | 8 | $9,000 | $160,000 |
9 | 200 | 5,400 | 9 | $6,000 | $166,000 |
10 | 100 | 5,500 | 10 | $4,000 | $170,000 |
Step-by-step breakdown
Total cases Sold=5,500
Total $vol=$170,000
Sort products by Volume (already sorted in the table above)
Calculate the cumulative percentage of total units sold
80% of total cases and $vol is 4,400 cases and $136,000
Find the point at which the total cumulative cases sold reaches 4,400 and $136,000.
The first 6 products contribute to the top 80% of total cases and $Vol (cumulative total: 4,500 cases and $136000)
Products 1 to 6 are in Category A
Products 1 - 5, and product 7 are in Category A
Next, calculate for B ranking, the next 10-20%, or 550 - 1,100 cases and $17,000 - $34,000
The next 10-20% of the products sold have cumulative ranges of 4,400 cases to 4,950 cases, and $136,000 to $153,000
Only product 7 fits in this cases range with a cumulative total = 4,900 cases
Only product 6 fits in the $Vol range with a cumulative total of $151,000
Next, calculate for C ranking, the next 5-10%, or 275 - 550 cases and $8,500 to $17,000
The next 5-10% of the products sold have cumulative ranges of 4,950 to 5,225 cases, and $153,000 to $161,500
Only product 8 fit in the cases and $Vol range with a cumulative totals of 5,200 cases and $160,000
Calculating for the D ranking will include the bottom 5%, or the remaining products
Products 9 and 10 will have a D ranking for both cases and $Vol
Final Categorization Example
Product | Cases Sold | $Vol | Ranking |
1 | 1,000 | $30,000 | AA |
2 | 900 | $27,000 | AA |
3 | 800 | $24,000 | AA |
4 | 700 | $21,000 | AA |
5 | 600 | $18,000 | AA |
6 | 500 | $15,000 | AB |
7 | 400 | $16,000 | BA |
8 | 300 | $9,000 | CC |
9 | 200 | $6,000 | DD |
10 | 100 | $3,000 | DD |
Fields And Definitions
Forecast Locations:
Ranking Period: How many months from the current date will be used to calculate sales. Only sales from verified invoices will be used.
Percentage A: Products with an A ranking are within the top percentage of volume range based on the ranking period.
Percentage B: Products with a B ranking are within the next percentage range
Percentage C: Products with a C ranking are within the next percentage range
Percentage D: Remaining products, the bottom percentage.
Best practice is that the percentage thresholds for A, B, C, and D should add up to 100
Inventory:
Product Ranking Code: Ranking code for the product and location. Ranking values are AA, AB, AC, AD, BA, BB, BC, BD, CA, CB, CC, CD, DA, DB, DC, DD. Each product is given a 2-character ranking code. The first letter is for volume or cases, the second letter is for sales dollars.