FarmaStock AI — Predictive Pharmacy Stock Optimization
FarmaStock AI is an end-to-end data science project that transforms operational data exported from Nixfarma into stock metrics, demand forecasts and explainable replenishment recommendations for community pharmacy.
Business problem
Community pharmacies need to balance two opposite risks: stockouts, which can reduce service quality and cause lost sales, and overstock, which immobilizes capital, occupies space and increases expiry risk.
The goal of this project is to support replenishment decisions using operational data, demand behaviour, stock coverage and explainable rules.
What this project shows
- End-to-end data pipeline from raw Nixfarma exports to processed analytical tables.
- Stock reconstruction using movement history and stock deltas.
- Demand analysis, rotation, coverage and stockout/overstock risk indicators.
- Demand forecasting with baseline validation and supervised model comparison.
- Rule-based replenishment recommendations with explainable reasons.
- Streamlit app for operational exploration and product-level detail.
End-to-end pipeline
The project is structured as a reproducible notebook pipeline. Each step produces validated intermediate or processed outputs used by the following stage.
Inventory of raw files, robust reading and source traceability for sales, movements and formal purchases.
Column standardization, type conversion, controlled parsing and validation of Nixfarma exports.
Operational stock reconstruction using movement chronology, previous stock, final stock and delta stock.
Creation of analytical tables such as product daily metrics, product dimension and enriched facts.
Demand, rotation, stock coverage, replenishment behaviour, stockout risk and overstock review signals.
Demand forecast using a weighted recent-demand baseline, backtesting and supervised challenger models.
Rule-based replenishment recommendations combining forecast, stock, coverage, safety stock and review flags.
Preparation of app KPIs, filters, labels and Streamlit-oriented review outputs.
Data sources
The project uses three Nixfarma data families with different roles:
- Movements: main source for stock, operational demand, entries, exits and adjustments.
- Sales: commercial enrichment for PVP, sales type, operators, entities and amounts.
- Formal purchases: documentary and supplier-related contrast, not automatic stock input.
Reconciliation logic
The project does not assume that all sources match. Stock and demand are reconstructed primarily from movements.
In movement data, the stock column is interpreted as final stock after each operation:
stock_prev: previous movement stock for the same product.stock_after: stock after the current movement.delta_stock: net stock variation between movements.
This is especially important for manual stock modifications, recounts and stock adjustments where original units may not represent real stock movement.
Forecasting approach
The forecasting layer estimates expected operational demand for 7, 14 and 30 days. The main model for version 1 is an explainable weighted recent-demand baseline.
- Demand series are converted into continuous daily product-level data.
- Products are classified by forecastability and confidence level.
- The baseline is backtested before being accepted as the final forecast version.
- Supervised models are trained as challengers, not automatic replacements.
- Metrics include MAE, RMSE, sMAPE, WAPE and bias.
Replenishment recommendations
Recommendations are generated using a rule-based and interpretable policy. The system converts forecast and stock context into operational actions.
recommended_order_qty: initial suggested order quantity.replenishment_action: action category for each product.replenishment_urgency_level: urgency based on risk and quantity.replenishment_reason: explanation of the recommendation.- Manual review is prioritized when the data does not support automatic ordering.
Streamlit app
The project includes a Streamlit app designed to explore stock recommendations visually and operationally.
Global KPIs, recommendation volume, product status and action distribution.
Products to order or review, with quantity, urgency and explanation.
Stockout risk, estimated coverage and forecast context.
Products where no order is recommended due to overstock or low demand signals.
Individual product card with stock, forecast, recommendation and decision rationale.
Guided interpretation of recommendations and supplier campaign scenarios.
Final outputs
fact_stock_movements_cleanfact_sales_enrichedfact_formal_purchasesdim_productsproduct_daily_metricsproduct_stock_metricsproduct_demand_forecastproduct_replenishment_recommendations
Why it matters
This project connects pharmacy domain knowledge with data engineering, forecasting and decision-support design.
Instead of focusing only on model complexity, the project prioritizes traceability, validation, operational interpretation and business usefulness.
The final result is a structured data product that can support better stock decisions in a pharmacy context.
Limitations
- Real data cannot be published due to confidentiality.
- Lead times and safety stock are rule-based initial assumptions.
- The app requires locally generated processed data to run with real outputs.
- Supplier constraints, package multiples, expiry dates and economic margins are not yet included.
- Recommendations are decision-support outputs, not blind purchase orders.
Related project
This stock optimization pipeline is complemented by a separate generative AI project:
Knowledge Assistant — Expert RAG Agent, an assistant for answering pharmacy stock optimization questions using Gemini, ChromaDB, LangGraph and Streamlit.