SKU Date Module

Last version update is System-Utilization6 – 14/05/2024

Master

File Name: System-Utilization6.py

Number of lines: 297

Slave

File Name: SkuDate4.py

Number of lines: 347

Introduction

The SKU Date Module is designed to calculate system utilization based on the total number of items sold per week or day. Beyond basic utilization reporting, the module also allows users to input a customer utilization target (e.g. 85%), and based on this input, it determines which SKUs should be temporarily excluded from automation to meet the desired throughput level.

The tool outputs an Excel file listing:

·      Which SKUs need to be removed

·      On which dates

·      To achieve the specific utilization threshold

This functionality is particularly valuable during peak periods, where a warehouse may face surges in demand. Rather than investing in high-throughput ASRS or automation picking system that may be underutilized for much of the year, this module helps balance workload by shifting excess volume to manual picking zones as needed.

Running the code

This module is composed of two separate Python scripts that work in tandem:

1.        System-Utilization6.py

This script extracts data from the primary file, KOROUEI-ANALYSIS.xlsx, to generate utilization bar charts by week or day.

2.         SKUDate4.py

This script processes the original customer e-commerce file, extracting SKU descriptions and quantities to generate the detailed output Excel file that reflects SKU-level adjustments.

The script begins by prompting the user to input a customer threshold value, which represents the maximum number of items that can be handled per selected time interval (either per day or per week). This threshold is used to assess system utilization and determine whether SKU volume exceeds the operational capacity.

·      If a threshold value is entered, the tool proceeds with the analysis.

·      To skip this step, the user can input ‘n’ to bypass the threshold setting.

Note: In its current version, the user must enter the threshold value before seeing any visual feedback. A future update should reverse this flow, first displaying the utilization bar chart to give users context, then allowing them to set an informed threshold. Additionally, the loop prompting multiple inputs is unnecessary and should be removed in later iterations to streamline the user experience.

Export Files

Once the customer threshold and the time interval (day or week) are selected, the tool generates a bar chart to visualize item volumes over the specified time periods.

The chart includes:

·      Weekly or daily item totals, derived from the input data file.

·      Color-coded peaks to identify the top 3 busiest periods (1st, 2nd, and 3rd peak).

·      Three horizontal reference lines:

o   Peak Utilization (maximum recorded throughput)

o   Average Utilization

o   Customer defined Utilization Threshold

These utilization lines are calculated based on the area under the curve, reflecting total volume distribution across the selected time frame. This visualization helps users quickly assess system load, identify bottlenecks, and validate whether their operations are approaching or exceeding capacity.

Once the bar chart is generated and the system utilization figures are calculated, the second script, SKUDate4.py, runs automatically.

This module analyses the original e-commerce data file to determine:

·      Which SKUs must be removed

·      On which dates

·      To meet the custom utilization threshold defined by the user

By identifying excess SKUs beyond the system’s processing capacity, the tool enables more strategic SKU allocation, especially valuable during peak demand periods. The output helps facilitate temporary SKU diversion to manual picking zones or overflow areas, without overwhelming automated systems or exceeding warehouse throughput limits.

Once the module has finished running, an excel file will be automatically generated. SKU-Dates.xlsx

This file will contain all the necessary SKU information for the customer to use.

The first page (Detailed) shows us all the unique SKU that were used on each of the individual dates (week or days) as selected from the initial section.

The Summary tab in the output Excel file provides a high-level overview of SKU activity an utilization adjustments across each selected week or day.

It includes:

·      Unique SKUs used per time interval

·      Total items sold during that interval

·      Actual item count versus the threshold

·      SKUs to remove in order to meet the defined utilization limit

·      Remaining SKU count after removal

·      Remaining item count that stays within system capacity

This tab offers warehouse planners a quick snapshot of when and where the system is likely to be overloaded, and what adjustments are necessary to stay within the target utilization range. It supports better manual vs automated picking decisions, workload balancing, and resource planning during high-demand periods.

Shaved Tab

The Shaved tab provides detailed insight into which specific SKUs must be excluded from the automated system on each selected date or week in order to stay within the defined utilization threshold.

This tab lists:

·      SKU numbers

·      The exact dates or weeks which they should be removed

·      The quantities associated with each removal

This data is critical for operational planning, particularly during peak periods when excess demand needs to be diverted to manual picking area. By identifying which SKUs to shift out of automation, warehouse managers can proactively allocate labor, reduce system strain, and maintain throughput efficiency without over-investing in underutilized automation infrastructure.

Remaining Tab

Summary Tab

The Remaining tab details all SKUs that are to be retained within the automated system (ASRS) after lower-priority SKUs have been removed to meet the user-defined utilization threshold.

It includes:

·      SKU IDs

·      Date or Week based breakdowns of retained SKUs

·      Associated quantities, ensuring visibility into what remains within the ASRS scope

This tab provides clear visibility into the final SKU set that stays in the system under constrained throughput conditions. It enables warehouse planners to align automation capacity with operational demand, ensuring high-impact SKUs are prioritized.