Creating SKU Consolidation Reports

How to create SKU consolidation reports using the Warehouse Operator Dashboard, so you can optimize inventory storage and streamline physical inventory counts.

IN THIS TOPIC

• Creating SKU Consolidation Reports 
• Using SKU Consolidation Reports
• Issues with SKU Consolidation Reports

You can create SKU consolidation reports from the Warehouse Operator Dashboard in Looker. These reports are designed to help you optimize inventory storage, so as to streamline daily operations and special processes like cycle counts and physical inventories.

Note: Flexe recommends using these reports as frequently as possible--especially when preparing for year-end physical inventories. To learn more, go to Preparing for Year-End Physical Inventories. 

Here’s a quick overview of available reports: 

• Pallet Consolidation – This report identifies when there are multiple locations with the same SKU in the Flexe system. This can help you consolidate pallets at a single location, to maximize that location's capacity. 
• Multi-SKU Locations – This report shows locations that contain more than one SKU in the Flexe system. As a best practice, each SKU should have its own location (i.e. one SKU per location). 

Creating SKU Consolidation Reports

To create SKU consolidation reports: 

1. Log in to the Warehouse Operator Dashboard in Looker. 

Go to https://flexedata.looker.com/boards/26

When prompted, enter your company email address and password (i.e. what you used to set up your account). To learn more, go to Accessing the Warehouse Operator Dashboard. 

2. Select the Inventory board. 

3. Filter by your Reservation Name and select Update. 

Open the Reservation Name filter in the Filters menu at the top of the page. Enter your reservation ID and/or company name, then select Update.

4. Go to the Inventory on Hand tile and download report data.  

Scroll to the Inventory on Hand tile, and open the Tile Actions menu (three vertical dots) in the top right. Next, select Download Data. 

In the window that opens, select the CSV format option from the dropdown. Then, select Download. 

Note: Once the file downloads, make sure to save it somewhere you can easily find it--you'll use the file in the next steps. 

5. Open the Inventory Multi-SKU & Pallet Consolidation Tool and make a copy.

The Inventory Multi-SKU & Pallet Consolidation tool is a Google Sheets template configured with pivot tables that show locations with multiple SKUs and pallets that can be consolidated. 

Open the Inventory Multi-SKU and Pallet Consolidation Tool template, and make a copy (File > Make a Copy). 

Note: You can rename your copy of the spreadsheet file and adjust the share settings in the confirmation window, as needed. 

6. Import your downloaded Inventory on Hand report. 

Select File from the top menu, then select Import. 

Next, go to the Upload tab in the Import file window and upload the report data CSV file you just downloaded from where it's saved.

Note: You can use the Select a file from your device option or drag and drop the file into the window.  

When prompted, choose the Insert New Sheet(s) option for the Import Location, then select Import Data. 

The report data will then be added to a new tab on your copy of the Inventory Multi-SKU & Pallet Consolidation Report Tool.  

7. Go to the new Inventory on Hand tab. Delete columns J and K, then copy all data in columns B through L. 

Go to the Inventory on Hand tab you just imported into the tool. 

Delete columns J and K, as the information in these columns is not needed for the next step.

Next, select columns B through L.

Then copy all the highlighted data (right-click and select Copy or use the Control + C | Command + C keyboard shortcut). 

8. Open the Invonhand tab and paste the copied data into columns A through K. 

Open the Invonhand tab at the bottom of the spreadsheet. 

The sheet will initially contain sample data as a placeholder. You'll need to replace this sample data your data from the Inventory on Hand tab. 

Select columns A through K and delete all the sample data. You can then paste select the first cell in the sheet (A1) and paste in the data you copied from the Inventory On Hand tab (right-click and select Paste or use the Control + V | Command + V keyboard shortcut). 

The pivot table tabs in the tool (Consolidation Pivot and Multi-SKU Locations) will now be configured with your inventory data. 

Using SKU Consolidation Reports

The Inventory Multi-SKU & Pallet Consolidation Tool features pivot tables you can use to review how SKUs are stored across warehouse locations. Here's an overview of how to navigate these tables. 

Pallet Consolidation Report

Open the Consolidation Pivot tab in the tool to review whether there are multiple locations with the same SKU. 

Scroll to columns U through Z to review pivot table. If there are any SKUs in your inventory with more than one location, they will appear here.

These SKUs will be grouped together, and the table will indicate the details including the SKU, Location Name, Location Category, Each Quantity and Pallet Equivalent. 

Here are steps you can take to use the information in this report: 

1. Review the SKUs

Check the SKUs in the report to determine whether the quantities seem correct, compared to what's entered in the Flexe system. 

2. Identify SKUs and locations that can be consolidated together.

Look for opportunities to combine the same SKUs together in the same location(s). 

Note: Make sure to follow rotation, lot code or any other general rules that would otherwise prohibit these SKUs from being moved into a new location.

3. Move inventory to the consolidated location(s).  

Designate a warehouse team member to move the inventory to the preferred consolidated location(s). This should happen both physically and in the Flexe system. 

4. Run new Inventory on Hand reporting to validate the moves 

Once the inventory has been moved, the best practice is to audit everything for accuracy by running a new inventory on hand report.

You should be able to see the changes reflected in the report. To learn more, go to Inventory On Hand Reports. 

Multi-SKU Locations Report

Open the Multi-SKU Locations tab to review locations that contain more than one SKU. 

As a best practice, a single SKU should be stored in a single location whenever possible.

Note: Some exceptions to this are inbound and outbound staging lanes, Quality Audit (QA), packing stations, etc.

Here are steps you can take to use the information in this report: 

1. Review SKUs in each location. 

The pivot table shows all locations with inventory, as well as the SKUs currently stored in each one: 

• Use the Location Name and Location Category columns for details about the location
• The COUNTUNIQUE of SKU column shows the number of unique SKUs in that location
• The SKUs in Location column lists each individual SKU, separated by a vertical bracket (| |)

Note: SKU quantities are not included as part of this table. You can review quantity for stored SKUs directly in the Invonhand tab, as needed. 

2. Identify locations where SKUs can be separated and/or consolidated.  

Look for opportunities to move SKUs to separate and/or consolidated locations.

3. Move inventory to the consolidated location(s).  

Designate a warehouse team member to move the SKUs to the preferred consolidated location(s). This should happen both physically and in the Flexe system. 

4. Run new Inventory on Hand reporting to validate the moves 

Once the inventory has been moved, the best practice is to audit everything for accuracy by running a new inventory on hand report.

You should be able to see the changes reflected in the report. To learn more, go to Inventory On Hand Reports. 

Issues with Consolidation Reports

If you're having trouble using the reports or don't see any results in the pivot tables, check the filters to make sure the correct options are selected.

Before you start

You'll need to access the Pivot Table Editor from cell B1 in the table. If you can't find cell B1, it might be hidden. 

Right-click the column bar at the top of the spreadsheet, then look for the Unhide Columns option. Make sure columns A through T are unhidden. 

Note: You can re-hide the columns if you want to condense the report view again. Just select the columns you want to hide, then right-click and select Hide Columns.  

To check your pivot table filters: 

1. Open the Pivot Table Editor. 

The Pivot Table Editor is automatically linked to the first cell in the pivot table.

To access the editor: 

• For the Consolidation Pivot tab - This sheet contains two separate pivot tables--you'll want to check the filers for each one. Select cell B1 to open the editor for the first pivot table; select cell U1 for the second pivot table. 
• For the Multi-SKU Locations tab - Select cell B1 to access the editor. 

The Pivot Table Editor will then open in the right sidebar

2. Check the Location Category Filters. 

Scroll to the bottom of the pivot table and look for the Filters section. The Location Category filter should be applied.

Open the Status dropdown to make sure that the Floor and Rack Storage options are both selected. Then, select OK. 

The filters should then update within the pivot tables to show all storage locations with inventory.

Note: If you're still having trouble using consolidation reports, reach out to your Flexe Logistics Analyst.