Home > Excel Reports > Product Mix
Product Mix - Excel Workbook 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.
Online Report Access Help
Steps for retrieving online reports
1. Locate one of Mango's monthly Action, Review or Excel emails.
2. You will find the login link, username, and password in the body of the email.
3. Click the link and enter your credentials to log in.
4. On the left-hand navigation window, you'll see a Data Import + Multi-Store folder as well as a listing of all the stores in your organization.
5. Click on the desired folder and the contents will display on the right-hand side.
6. You may want to change the view to be Detail as it's easier to navigate.
7. If you click on a specific store you'll need to select the Excel or PDF folder depending on what report you're after.
8. The Excel folder houses store-specific workbooks like the Mango_IVA, Location, and Class Productivity, Delete Report, Vendor Analysis, etc.
9. You can either go back on your web browser or on the left-hand side, click the PDF folder.
10. The PDF folder contains that same reports that are emailed each month in the Action, Review, and Excel email.
11. Click on the file you'd like to download, a new page will load and you can click on the "Page icon" or the download button in the upper right.
12. If you'd like to download an entire folder of reports, say Data Import + Multi-Store, click on the root folder and then the download button.
13. And if you want to download EVERYTHING, then click on the highest root folder and then the download button.
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 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
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