The Product Mix tab lists all Ace SKUs* carried in your warehouse and sold by your peer stores--whether your store carries the SKU or not and is sorted by class. It is a versatile report with many uses: it can be filtered by Class or "Calculated Location" to do aisle level assortment planning, filtered by Status = Cancelled to review cancelled SKUs in your store, filtered by Status = New to see how newly added SKUs in your warehouse are selling, or filtered by Made in the USA to see if you are carrying (or have shelf identifiers) Made in USA products. These are just examples, as you play around with it, I'm sure you will find your own way to analyze inventory opportunities! *SKUs carried by less than 2% of your peer stores are excluded from this report if your store also does not carry the SKU; this helps reduce report size significantly.
Your Product Mix report is located in your store's Excel directory online. Click here to learn how to get online (it's easy).
Status
Active - This is a SKU your store currently carries.
Add - this is a good-selling SKU in your peer group and a suggested add in your Productivity Level 2 - Assortment Opportunity metric
Canceled - This SKU has been canceled or closed out from your warehouse.
Dropped - This SKU used to be carried in your assortment but is not currently carried.
New - This SKU has been recently added to your warehouse (within the past 9 months)
NIF - Not In File, your store does not carry this SKU nor is it represented in your point of sale system inventory file.
Obsolete - This SKU has been identified as obsolete by Mango's Closeout Management System.
Reorder - This SKU is in your inventory file, but is not currently being carried. It is suggested the item is reordered because of peer sales experience.
Slow - This SKU is a poor seller in its Product Group and you should consider removing it from your assortment if it is not an essential item.
Example uses below
-
1. Open Excel Assortment Report, Assortment Report tab
2. Filter Status column (F) to Cancelled only
3. Filter Replacement SKU (column U) to not include blanks (filter at end of list)
4. Filter Replacement Active (column V) to No
5. Sort or Filter on Location (column H) and/or Store QOH (column L) as desired to find canceled/replacement opportunities.
Hint, set Store QOH (column L) to less than 2 to exclude inventory where you are waiting on sell down before replacement. -
1. Open Excel Assortment Report, Assortment Report tab
2. Filter on the desired Class or Calculated Location
3. Set Status column (F) to "Add"
You can also- Filter Status column (F) to Reorder (to show SKUs you used to carry but are no no longer in your assortment)
- Sort by column AK (Class GP Rank) to see any top-ranked SKUs in the class that your store does not carry.
-
1. Open Excel Assortment Report, Assortment Report tab
2. Filter on Status (column F) = New
3. Sort Should Sell (column O) Largest to smallest to see the best selling SKUs first
Hint: look at PG Stores Selling column to see the percentage of stores selling the SKU in your peer group -
1. Open Excel Assortment Report, Assortment Report tab
2. Filter on Made in USA (column AF) = Yes -
Column F, Status
Active - This is a SKU your store currently carries.
Add - this is a good-selling SKU in your peer group and a suggested add in your Productivity Level 2 - Assortment Opportunity metric
Canceled - This SKU has been canceled or closed out from your warehouse.
Dropped - This SKU used to be carried in your assortment but is not currently carried.
New - This SKU has been recently added to your warehouse (within the past 9 months)
NIF - Not In File, your store does not carry this SKU nor is it represented in your point of sale system inventory file.
Obsolete - This SKU has been identified as obsolete by Mango's Closeout Management System.
Reorder - This SKU is in your inventory file, but is not currently being carried. It is suggested the item is reordered because of peer sales experience.
Slow - This SKU is a poor seller in its Product Group and you should consider removing it from your assortment if it is not an essential item.
Column H, Calculated Location
This is the most common active location code in your store for the SKU's product group (fineline). This will show where SKUs would most likely be located in your store if you were to carry them.
Column I, Discovery
Lists the SKU's Discovery classification from Ace.
Column J, Store Closeout
Indicates if Store Closeout has been set for this SKU in your store (codes tab of E4W Inventory Maintenance).
Column K, Store QOH
Shows the quantity on hand (QOH) in your store at the time the report was generated (usually on the last day of the month).
Column L, Peer QOH
This is the median store QOH in your peer group as of the report date.
Column M, Store Units Sold
Annual units your store has sold.
Column N, Should Sell
This is a calculated value based on sales in your peer stores adjusted for your store's size relative to the typical peer store. It shows how many annual units your store should be selling. It is a good estimate for NIF SKUs (not in file) and is fun to review the variance between your actual (column O) and your estimated value.
Column O, PG Stores Selling
This can be formatted as a percentage field for easier reading. It shows the percentage of stores in your peer group selling the SKU. It can be used with column P to determine how likely your store will sell the calculated amount. The more stores selling, the better the calculation in column P.
Column P, Sales Variance
Shows the difference in calculated (estimated) annual sales dollars between what your store is currently selling and what it "should" or what Mango is calculating it to sell. Negative numbers mean your store should sell more, positive numbers mean your store is selling more than the calculation.
Column Q, GP Variance
This is the same as column R except showing Gross Profit (GP) variance.Column R, Store Retail
The retail price for the SKU in your store.Column S, Peer Retail
The median retail price for the SKU in peer stores.
Column T, Replacement SKU
If a SKU is canceled and has a replacement SKU in your warehouse, it will be listed here.
Column U, Rep. Active
Indicates if the replacement SKU (column V) is active in your store.
Column V, Store Promo Percentage
Shows the percentage of sales a SKU sells on promotion. High numbers suggest an in/out or perpetually on promotion SKU.
Column W, Peer Promo Percentage
This is the same as Column X but shows your peer promo average.
Column X, Collection
Indicates if a SKU is part of a collection such as numbered drill bits, socket sets, o-ring displays, etc. These SKUs may be XX items, but may not be appropriate for closing out in your store because it would leave a gap in a fixed display.
Column Y, Date Added
Shows date of when the SKU was added to the system.Column Z, Last Purch
Shows date of when the SKU was last purchased.Column AA, Last Sale
Shows date of when the SKU was last sold.Column AB, Last Physical
Shows date of when the SKU was last counted.Column AC, UPC
Primary Universal Product Code for the SKU.Column AD, Discovery ID
Potential POG data, needs data quality testing.Column AE, Promo Price
Promotional Price of the SKU as of the 1st of the month.
Column AF, Discontinued
Indicates if the SKU is set to Discontinued Status in your store (codes tab of E4W Inventory Maintenance.)
Column AG, Order Point
The store's Order Point (at the end of the month) for the SKU.Column AH, Store / Estimated Sales
Shows your store's annual sales dollars if you carry the SKU, or the SKU's estimated sales if not.
Column AI, Store / Estimated Gross Profit
Shows your store's annual gross profit dollars if you carry the SKU, or the SKU's estimated gross profit if not.
Column AJ, Sales Rank
Indicates the SKU's overall sales rank in the assortment report. 1 has the highest annual sales.
Column AK, Gross Profit Rank
Indicates the SKU's overall gross profit rank in the assortment report, 1 has the highest annual gross profit.
Column AL, Class GP Rank
Indicates the SKU's gross profit rank within the SKU's class.
Column AM, Product Group GP Rank
Indicates the SKU's gross profit rank within the SKU's product group.