Python Retail Intelligence: Automating Inventory Forecasting, Stock Replenishment, and Store Performance Reporting

Project: Python Inventory Forecasting, Replenishment Automation, Sales Analytics, and Store Reporting

Duration: 17 weeks

UrbanCart Retail operates a growing chain of neighborhood stores selling household goods, personal care items, packaged food, seasonal products, stationery, and small electronics. The company had expanded from a few stores into a regional retail network with different store formats, local customer patterns, supplier lead times, promotional campaigns, and seasonal demand spikes. Each store used a point-of-sale system, while inventory and purchasing were handled through an ERP. Both systems contained valuable data, but store managers and replenishment planners still depended heavily on spreadsheets to understand sales velocity, stock availability, reorder needs, promotion performance, and slow-moving inventory. As the store network grew, manual planning became too slow and inconsistent.

UrbanCart had too much operational decision-making trapped inside spreadsheets. Replenishment planners manually exported sales and stock files, calculated reorder quantities, checked supplier rules, reviewed promotional calendars, and sent purchasing recommendations. Store managers often noticed stock problems only after products had already sold out. Some stores over-ordered slow-moving items, while others missed demand for fast-moving products. Leadership wanted better inventory control, but the existing process could not scale across more locations and product categories.

Business signals

• Popular products frequently went out of stock before replenishment orders were placed.
• Slow-moving products occupied shelf and storage space for too long.
• Store managers spent too much time preparing stock and sales reports manually.
• Replenishment planners used different spreadsheet logic across product categories.
• Promotional sales spikes were not consistently reflected in reorder planning.
• Supplier lead times were handled manually and often ignored during urgent planning.
• Leadership lacked a consistent view of stock health across stores.
• Inventory decisions depended heavily on individual planner experience.
• New store openings increased reporting workload.
• Seasonal categories created repeated overstock and stockout problems.

Technical signals

• POS sales data and ERP inventory data used different product identifiers.
• Product categories were inconsistent across stores and supplier files.
• Stock-on-hand values were sometimes delayed or corrected after store audits.
• Promotional flags were stored separately from sales history.
• Supplier minimum order quantities and pack sizes were not included in planner spreadsheets consistently.
• Manual reorder calculations did not reliably account for lead time.
• Spreadsheet reports became slow as more stores and SKUs were added.
• Historical sales data contained gaps from POS outages and store closures.
• Returns and stock adjustments distorted demand calculations.
• No automated validation existed for negative stock, duplicate products, or missing supplier mappings.
• Store-level reports used different formulas for sell-through and stock cover.
• Forecasting was mostly based on recent sales averages and manager judgment.

Metrics Supplier Rule Errors: Frequent manual correction for pack sizes and minimum order quantities

Average Stockout Rate: 9.7% across high-demand SKUs

Manual Reporting Time: 4 to 5.5 hours per day across retail operations staff

Overstocked S K U Share: 18.4% of active SKUs had excessive stock cover

New Store Reporting Setup: Several days of spreadsheet changes and validation

Promotion Forecast Accuracy: Low because promotional uplift was handled manually

Replenishment Planning Cycle: 2 to 3 days for full weekly review

Store Report Refresh Frequency: Mostly daily, with some reports updated weekly

Slow Moving Inventory Visibility: Mostly reviewed monthly

Stock Adjustment Issue Discovery: Usually found during manual report preparation

Pages Measured

• Daily sales report
• Store inventory report
• Replenishment planning workbook
• Stockout report
• Overstock report
• Promotion performance report
• Supplier order recommendation report
• Weekly retail operations review

Primary Audience: Replenishment planners, store managers, category managers, and retail leadership

Measurement Window: 75 days before implementation

The project began by studying how UrbanCart planned inventory across stores and categories. The team reviewed POS sales exports, ERP inventory records, supplier files, promotional calendars, stock adjustment logs, store manager reports, and replenishment workbooks. Discovery showed that Python could create major value by standardizing product and store data, automating reorder calculations, forecasting demand, and giving planners earlier visibility into stock risk.

What we inspected

• Title: Retail planning workflow review

  Description: The team observed replenishment planners and store managers preparing daily and weekly inventory reports. This revealed repeated manual steps, inconsistent formulas, and decisions that could be supported by automation.

• Title: Sales and inventory data audit

  Description: POS sales data, ERP stock records, product master files, supplier files, returns, adjustments, and promotion calendars were reviewed for structure, quality, and update frequency.

• Title: Product mapping analysis

  Description: The team reviewed how SKUs, barcodes, supplier product codes, category labels, and store-specific product names were connected. This exposed duplicate records and missing supplier mappings.

• Title: Replenishment rule documentation

  Description: Planner logic for reorder points, stock cover, supplier lead time, pack size, minimum order quantity, safety stock, and promotion uplift was converted into structured business rules.

• Title: Forecasting requirement review

  Description: Category managers explained which forecasts mattered most: normal weekly demand, promotional uplift, seasonal demand, new store ramp-up, and fast-moving product replenishment.

• Title: Store reporting review

  Description: Store managers needed simple outputs: what is selling fast, what is running out, what is overstocked, what needs ordering, and what products require investigation.

The main challenge was improving inventory visibility and replenishment accuracy without replacing the POS or ERP systems. UrbanCart needed a Python-based analytics and automation layer that could combine sales, inventory, supplier, product, promotion, and store data. The system had to forecast demand, detect stockout risk, recommend replenishment quantities, highlight overstock, and give store managers practical reporting. It also had to handle messy retail data, including missing product codes, inconsistent category names, delayed stock updates, supplier minimum order quantities, and seasonal demand changes.

The solution was a Python-based retail intelligence system that automated data ingestion, cleaned product and store records, forecasted SKU-level demand, generated replenishment recommendations, identified stockout and overstock risk, and delivered consistent reports to planners and store managers. UrbanCart kept its existing POS and ERP systems while Python became the decision-support layer for inventory operations.

Strategy

• Create a standardized data model for products, stores, sales, inventory, suppliers, promotions, and replenishment rules.
• Replace manual spreadsheet calculations with tested Python workflows.
• Use PostgreSQL as the shared reporting and planning database.
• Forecast demand by SKU, store, category, weekday, and promotion status.
• Generate replenishment recommendations using stock cover, lead time, safety stock, and supplier constraints.
• Highlight high-risk stockouts and excessive stock cover.
• Standardize reporting definitions across all stores.
• Keep reports simple and exportable for retail teams.
• Add validation checks to catch product, stock, and supplier data issues early.

Phase1 Title: Retail data model creation

Actions

• Defined canonical tables for stores, products, categories, suppliers, sales, stock levels, returns, adjustments, promotions, and replenishment rules.
• Mapped POS product IDs, ERP SKUs, supplier codes, and barcodes into a unified product reference.
• Standardized store names, region codes, category labels, and product hierarchy fields.
• Separated gross sales, net sales, returns, stock adjustments, and transfer movements.
• Created fields for supplier lead time, pack size, minimum order quantity, and ordering calendar.
• Defined stock cover, sell-through, stockout, and overstock calculations.
• Created exception categories for missing SKU mapping, negative stock, duplicate products, and invalid supplier rules.

Description: The first phase created a shared structure for retail sales, inventory, product, supplier, and promotion data.

Phase2 Title: Python ingestion and cleaning

Actions

• Built Pandas workflows to ingest POS sales exports, ERP stock files, supplier sheets, and promotion calendars.
• Cleaned inconsistent product names, category labels, store names, and supplier references.
• Validated required fields such as SKU, store, sales date, quantity sold, stock on hand, and supplier ID.
• Detected duplicate SKU records and missing product mappings.
• Flagged negative stock and unusual stock adjustment values.
• Loaded cleaned data into PostgreSQL.
• Created processing logs showing row counts, rejected records, warnings, and refresh status.

Description: Python pipelines replaced manual file cleanup and created repeatable data refreshes.

Phase3 Title: Demand forecasting

Actions

• Aggregated sales history by SKU, store, category, weekday, and promotion period.
• Removed periods affected by store closures, stockouts, and known data outages.
• Created baseline forecasts using recent sales velocity and seasonal patterns.
• Used scikit-learn models for higher-volume SKUs where additional accuracy was useful.
• Created separate uplift logic for promotions and seasonal categories.
• Generated forecast ranges instead of only single-point forecasts.
• Compared model output against simple moving-average baselines.
• Stored forecast results by SKU, store, and planning week.

Description: The team built practical demand forecasts to support replenishment planning without overcomplicating the workflow.

Phase4 Title: Replenishment recommendation engine

Actions

• Calculated expected demand during supplier lead time.
• Added safety stock based on demand volatility and product importance.
• Applied minimum order quantities and supplier pack sizes.
• Excluded inactive, discontinued, and blocked products from recommendations.
• Adjusted recommendations for current stock, open purchase orders, and planned promotions.
• Separated urgent replenishment from normal weekly ordering.
• Generated recommended order quantities by store, SKU, and supplier.
• Explained each recommendation using demand, stock cover, lead time, and supplier rules.

Description: The core planning engine converted forecasts and inventory data into recommended order quantities.

Phase5 Title: Stockout and overstock detection

Actions

• Flagged products likely to stock out before the next supplier delivery.
• Detected products with excessive stock cover based on forecasted demand.
• Identified stores with repeated stockout patterns for the same categories.
• Separated true low demand from low sales caused by stockouts.
• Created slow-moving inventory lists by store and category.
• Highlighted products with high stock value and weak recent sales.
• Generated daily risk summaries for planners and store managers.

Description: The system highlighted inventory risk before it became a customer-facing problem.

Phase6 Title: Reporting and API layer

Actions

• Built FastAPI endpoints for sales summaries, stock risk, replenishment recommendations, promotion performance, and slow-moving inventory.
• Created filters for store, region, category, supplier, SKU, and date range.
• Created exportable replenishment lists for purchasing teams.
• Created store-level views showing top sellers, low stock, overstock, and recommended actions.
• Created leadership summaries for stockout rate, stock cover, sales performance, and forecast accuracy.
• Added report explanations so users could understand why a product was flagged.

Description: Retail teams received simple reporting views and reusable API outputs for future tools.

Phase7 Title: Testing, rollout, and adoption

Actions

• Added tests for stock cover, reorder quantity, supplier rule application, promotion uplift, and SKU mapping.
• Ran Python recommendations in parallel with manual replenishment workbooks for four weeks.
• Reviewed differences with planners and category managers.
• Adjusted rules for seasonal items, discontinued products, and supplier delivery calendars.
• Containerized the system with Docker.
• Created documentation for forecasting logic and replenishment rules.
• Trained store managers on stock risk reports and slow-moving inventory views.
• Retired manual spreadsheets only after planners trusted the automated outputs.

Description: The system was rolled out carefully so planners could compare Python recommendations against existing spreadsheet decisions.

• Manual reporting time dropped from 4-5.5 hours per day to under 45 minutes of review.
• Average stockout rate across high-demand SKUs decreased from 9.7% to 5.8%.
• Overstocked SKU share decreased from 18.4% to 11.2%.
• Weekly replenishment planning cycle dropped from 2-3 days to less than one day.
• Supplier pack size and minimum order quantity errors were reduced through automated rule checks.
• Promotion planning improved because forecast logic included promotional uplift assumptions.
• Store managers gained daily visibility into low stock, overstock, and fast-moving products.
• Slow-moving inventory became visible weekly instead of mostly monthly.
• Leadership gained consistent stock health reporting across all stores.
• New store reporting setup became faster because the data model and reports were reusable.
• Stock adjustment issues were flagged earlier during ingestion.
• Planners spent more time reviewing exceptions and less time building spreadsheets.
• Replenishment recommendations became more consistent across stores and categories.
• The business gained a scalable retail analytics foundation without replacing its POS or ERP systems.

The Python retail intelligence system improved inventory control, reduced manual planning effort, and gave UrbanCart earlier visibility into product demand and stock risk. The company kept its existing operational systems while creating a more reliable planning layer for replenishment and store performance.

Outcomes

• Fewer lost sales from preventable stockouts.
• Lower working capital tied up in slow-moving inventory.
• Faster replenishment planning cycles.
• More consistent ordering decisions across stores.
• Better promotion readiness.
• Improved store manager visibility into inventory actions.
• Reduced spreadsheet dependency.
• Stronger leadership reporting across the retail network.
• A scalable foundation for future pricing, assortment, and supply-chain analytics.

• Keep POS and ERP systems unchanged.

  The existing systems handled transactions and inventory records. The problem was poor decision support between those systems, which Python could solve as an analytics layer.

• Standardize product mapping before forecasting.

  Forecasting would be unreliable if POS IDs, ERP SKUs, supplier codes, and barcodes were not connected correctly.

• Use simple baselines before advanced models.

  Many retail products could be forecasted effectively with clean historical demand and seasonality. More complex models were used only where they improved planning.

• Explain replenishment recommendations.

  Planners needed to trust why a product was recommended for ordering before relying on the system.

• Apply supplier constraints directly in Python.

  Ignoring pack sizes, lead times, and minimum order quantities created unrealistic ordering recommendations.

• Separate stockout-driven low sales from true low demand.

  A product may appear to have weak sales only because it was unavailable. The system needed to avoid reducing orders incorrectly.

• Inventory forecasting is only useful when product and store data are clean.
• Retail replenishment must include supplier constraints, not just demand estimates.
• Stockouts can hide true demand and distort future forecasts.
• Python can reduce spreadsheet dependency without replacing core retail systems.
• Planners trust recommendations more when the reasoning is visible.
• Simple forecasting methods often outperform complex models when data quality is limited.
• Promotions need separate demand logic because normal sales history is not enough.
• Overstock and stockout reporting should be reviewed together.
• Store managers need action lists, not only charts.
• Automated validation prevents bad product and stock data from damaging planning decisions.

Role: Head of Retail Operations

Quote: The system gave our planners and store managers a much clearer view of what needed action. We did not lose control to automation; we gained better recommendations and spent less time fighting spreadsheets.

Person: Rebecca Shaw

Company: UrbanCart Retail

UrbanCart Retail used Python to transform inventory planning from a spreadsheet-heavy process into a reliable retail intelligence system. By standardizing product data, automating sales and stock ingestion, forecasting demand, applying supplier rules, generating replenishment recommendations, and highlighting stockout and overstock risk, the company improved planning speed and inventory control. The result was fewer stockouts, lower overstock exposure, better promotion readiness, and a scalable analytics layer built around existing POS and ERP systems.