How to Optimize Vegetable Restocking and Pricing with Data‑Driven Models

Problem Restatement

This is a mathematical modeling problem about automatic pricing and restocking decisions for perishable vegetable products. The task provides six vegetable categories, sales data from 2020‑2023, wholesale prices, and spoilage rates, and requires a model to analyze inter‑category relationships, sales distribution, and to propose weekly restocking quantities and pricing strategies while keeping the total number of SKUs between 27 and 33.

Abstract

With increasing competition in the fresh‑produce market, effective inventory and pricing strategies are crucial for supermarkets. This analysis uses historical sales, wholesale prices, and spoilage rates to forecast future sales of vegetable categories and to recommend restocking and pricing plans.

First, historical sales are examined and a moving‑average method predicts next‑week sales for each category. Adjusted forecasts account for spoilage to compute restocking quantities. Pricing follows a cost‑plus approach, applying a fixed markup to the latest wholesale price for each category and SKU.

For SKU‑level analysis, the 22 most popular items (selected from the latest week’s sales) receive individualized restocking forecasts and a 20% markup pricing recommendation.

The resulting recommendations help supermarkets balance inventory, avoid stock‑outs or overstock, and align prices with market demand.

While the suggestions are based on historical data, actual market response may differ; therefore, supermarkets should remain flexible and consider collecting additional market and consumer data for more sophisticated forecasting.

Overall Approach

Problem 1: Sales Distribution and Inter‑Category Relationships

Model building steps:

Data preprocessing: aggregate daily sales for each vegetable.

Association analysis: apply Apriori algorithm to discover relationships between vegetables.

Sales distribution: use time‑series models such as ARIMA to analyze and forecast sales trends.

Problem 2: Category‑Level Restocking and Pricing

Model building steps:

Demand forecasting: apply time‑series analysis to predict next‑week sales for each category.

Cost‑plus pricing model: retail price = wholesale cost × (1 + markup).

Profit maximization: maximize total supermarket profit subject to restocking constraints.

Problem 3: SKU‑Level Restocking and Pricing

Model building steps:

Demand forecasting: predict daily sales for each vegetable.

SKU selection: choose 27‑33 SKUs with highest predicted sales after accounting for spoilage.

Profit maximization: similar to Problem 2 but focused on the selected SKUs.

Problem 4: Additional Data Requirements

Customer preference data to understand demand intensity.

Seasonal factor data to capture seasonal effects on vegetable demand.

Competitor data to benchmark pricing and restocking strategies.

Macro‑economic data to assess broader economic impacts on sales.

Exploratory Findings

Data import and preprocessing were performed using pandas.

<code>import pandas as pd
# Load data from the provided Excel file
combined_data = pd.ExcelFile("data/combined_data.xlsx")
attachment1 = combined_data.parse('附件1')
attachment2 = combined_data.parse('附件2')
attachment3 = combined_data.parse('附件3')
attachment4 = combined_data.parse('附件4')
attachment1.head(), attachment2.head(), attachment3.head(), attachment4.head()
</code>

Visualization of total sales volume by vegetable category revealed the most popular categories.

Correlation analysis within the top category was conducted using a pivot table and heatmap.

<code># Selecting the top category for further analysis
top_category = category_sales.index[0]
category_data = merged_data[merged_data["分类名称"] == top_category]
pivot_data = category_data.pivot_table(index="销售日期", columns="单品名称", values="销量(千克)", aggfunc="sum").fillna(0)
correlation_matrix = pivot_data.corr()
</code>

Scatter plot of sales volume versus profit for each category highlighted that some low‑volume categories generate high profit.

<code># Merge sales data with wholesale price data
merged_data_price = pd.merge(merged_data, attachment3, left_on=["单品编码", "销售日期"], right_on=["单品编码", "日期"], how="left")
merged_data_price["销售金额"] = merged_data_price["销售单价(元/千克)"] * merged_data_price["销量(千克)"]
merged_data_price["利润"] = merged_data_price["销售金额"] - (merged_data_price["销量(千克)"] * merged_data_price["批发价格(元/千克)"])
category_profit = merged_data_price.groupby("分类名称")["利润"].sum()
category_sales_volume = merged_data_price.groupby("分类名称")["销量(千克)"].sum()
</code>

Future weekly sales were forecast using a 7‑day moving average.

<code># Restructure data for time series analysis
ts_data = merged_data.groupby(["销售日期", "分类名称"])["销量(千克)"].sum().reset_index()
ts_data = ts_data.pivot(index="销售日期", columns="分类名称", values="销量(千克)").fillna(0)
forecast_corrected = ts_data.rolling(window=7).mean().iloc[-7:]
</code>

Restocking quantities were adjusted for spoilage, and retail prices were set with a 20% markup.

<code># Adjust forecast for spoilage
for category in forecast_with_spoilage.columns[:-1]:
    forecast_with_spoilage[category] = forecast_with_spoilage[category] / (1 - forecast_with_spoilage["损耗率(%)"])
restock_volume_final = forecast_with_spoilage.drop(columns=["损耗率(%)"])
markup_percentage = 0.20
retail_prices = category_prices * (1 + markup_percentage)
</code>

SKU‑level analysis selected the top 22 items (within the 27‑33 SKU constraint) and computed their retail prices using the same markup.

<code># Filter recent week data and select top SKUs
filtered_sales = merged_data_price[(merged_data_price["销售日期"] >= "2023-06-24") & (merged_data_price["销售日期"] <= "2023-06-30")]
product_sales_volume = filtered_sales.groupby("单品名称")["销量(千克)"].sum().sort_values(ascending=False)
selected_products = product_sales_volume.head(33).index
selected_wholesale_prices = price_category.set_index("单品名称")["批发价格(元/千克)"][selected_products]
selected_retail_prices = selected_wholesale_prices * (1 + markup_percentage)
</code>

Finally, comprehensive weekly restocking and pricing recommendations for the supermarket were formulated based on the analyses above.

Reference: National College Student Mathematical Modeling Competition (mcm.edu.cn)