Automating Grocery Store Replenishment with Excel and Python

Automate the Process of Restocking Shelves Based on Sales Velocity

Efficiently restocking shelves is critical for maintaining optimal inventory levels in grocery stores. By using Python and Excel, you can automate replenishment based on sales velocity, ensuring that products are restocked in time to meet customer demand. This reduces the chances of stockouts and helps in maintaining the right balance of inventory without overstocking.

Code Example:

import pandas as pd

# Load sales data from Excel (sales_id, product_id, units_sold, sales_date)
sales_data = pd.read_excel('sales_data.xlsx')

# Calculate average sales velocity per product (units sold per day)
sales_data['sales_date'] = pd.to_datetime(sales_data['sales_date'])
sales_data['days_since_sale'] = (pd.to_datetime('today') - sales_data['sales_date']).dt.days
sales_velocity = sales_data.groupby('product_id')['units_sold'].sum() / sales_data.groupby('product_id')['days_since_sale'].max()

# Load inventory data (product_id, current_stock)
inventory_data = pd.read_excel('inventory_data.xlsx')

# Calculate the required stock based on sales velocity (example: reorder if stock < 2 days of sales)
inventory_data['reorder_quantity'] = inventory_data['product_id'].apply(lambda x: max(0, sales_velocity[x] * 2 - inventory_data.loc[inventory_data['product_id'] == x, 'current_stock'].values[0]))

# Save reorder suggestions to Excel
inventory_data[['product_id', 'reorder_quantity']].to_excel('reorder_suggestions.xlsx', index=False)
print("Reorder suggestions generated and saved.")

This Python script uses Excel data to calculate sales velocity per product and then determines if restocking is necessary. If the current stock is lower than the expected demand over the next 2 days, the script will automatically generate reorder suggestions. This process eliminates the need for manual calculations, saving time and reducing human error.

Use Python to Pull Data from Excel Sheets and Generate Reordering Suggestions

Using Python, you can pull data from multiple Excel sheets, including sales and inventory, and cross-reference it to determine which products need replenishment. This allows for a more data-driven and efficient approach to inventory management.

Code Example:

import pandas as pd

# Load sales data and inventory data from Excel sheets
sales_data = pd.read_excel('sales_data.xlsx')
inventory_data = pd.read_excel('inventory_data.xlsx')

# Calculate sales velocity for each product
sales_velocity = sales_data.groupby('product_id')['units_sold'].sum() / sales_data.groupby('product_id')['sales_date'].nunique()

# Merge the sales velocity with inventory data
inventory_data = inventory_data.merge(sales_velocity, on='product_id', how='left', suffixes=('_inventory', '_velocity'))

# Calculate reorder quantity (replenish to 3 days of sales velocity)
inventory_data['reorder_quantity'] = inventory_data['sales_velocity'] * 3 - inventory_data['current_stock_inventory']

# Filter out products that don’t need restocking
inventory_data['reorder_quantity'] = inventory_data['reorder_quantity'].apply(lambda x: max(0, x))

# Save the reorder suggestions to Excel
inventory_data[['product_id', 'reorder_quantity']].to_excel('reorder_suggestions.xlsx', index=False)
print("Reorder suggestions generated and saved.")

In this example, sales velocity is calculated for each product, and reorder quantities are generated based on the difference between current stock and 3 days’ worth of sales. This ensures dynamic replenishment and avoids running out of stock.

Save Time and Reduce Human Errors in Inventory Management

Automating the replenishment process with Python and Excel reduces manual errors and frees up time for store managers and employees. By continuously analyzing sales data and inventory levels, stores can ensure they always have the right products on hand while minimizing excess stock.

Benefits of Automation:

• Real-time updates: Automation allows for real-time replenishment based on up-to-date sales data.
• Data-driven decisions: Reorder suggestions are based on actual sales patterns, reducing reliance on guesswork.
• Error reduction: Automation minimizes human errors in stock management and order placements.

Why Choose Lillqvist Strat?

At Lillqvist Strat, we specialize in providing custom automation solutions that streamline your inventory management. Our expertise in Excel, Python, and pandas enables us to:

• Automate replenishment based on sales velocity to avoid stockouts and excess inventory.
• Generate data-driven reorder suggestions to keep your shelves stocked without overstocking.
• Save time and reduce errors in the manual inventory management process.

Let Lillqvist Strat help you optimize your replenishment processes and improve your operational efficiency. Contact us today to implement a custom solution tailored to your grocery store’s needs.