Technical Report: Predictive Inventory & Workforce Planning System
1. Executive Summary
This report documents a decision-support system that forecasts daily demand for the EcoFit Smart Bottle and converts that single forecast into two parallel sets of recommendations: inventory (when to reorder, how much, and the stockout risk) and workforce (how many staff are needed, and when hiring must start). A cost model expresses the consequences of each policy in INR so options can be ranked by business impact.
All figures below were reproduced from the three supplied datasets (Sales, Inventory, Workforce; 730 daily rows, 1 Jan 2023 to 30 Dec 2024).
Headline results
Three findings matter most.
- Prophet with correctly lagged regressors cut error by 56% against the same model without regressors (MAPE 7.77% to 3.39%). Getting the lag structure right was worth more than model choice.
- Workforce backlogs are mostly a spike problem, not a trend problem. 24 of 47 backlog days had no staffing gap on the smoothed plan, so hiring cannot fix them; they need a same-day flex or overtime signal.
Labor is about 86% of modelled cost. Raising the inventory service level from 95% to 99% costs almost nothing (about INR 300 more holding per year), while adding two permanent staff costs INR 657,000 more in wages for INR 370,000 of overtime saved.
Problem Statement and Scope
Companies that sell physical goods must commit inventory and labor before demand is known. Planning by judgment produces stockouts, excess stock, understaffing and overtime. The platform supports human planners; it does not place orders or hire staff.
Core idea: one demand signal, two operational consequences. The forecast is produced once and consumed by independent inventory and workforce branches.
In scope: single-product daily forecasting (30-day horizon), reorder point, safety stock, EOQ, stockout risk, required headcount, hiring lead time, overtime and backlog exposure, and an integrated cost comparison.
Out of scope: multi-product or multi-store models, streaming data, automated procurement or HR actions, authentication and production deployment.Data and Exploratory Analysis
All three share the Date and Product keys. Inventory and Workforce carry UnitsSold from Sales for traceability but do not read each other, which keeps each branch independent and testable.
3.2 Demand patterns
• Trend: average demand rose from 216.3 units/day in 2023 to 237.5 in 2024 (+9.8%).
• Yearly seasonality: monthly mean demand troughs in January (181) and peaks in June (271), a 50% swing. Demand returns to about 198 by December.
• Weekly seasonality: Saturday is highest (245.6), then Sunday (239.0) and Friday (231.2); Tuesday is lowest (215.0). The pattern is consistent with a consumer product bought at weekends.
• Promotions: promo days average 253.2 units against 224.6 otherwise (+12.7%).
• Pricing: four price regimes (INR 250, 260, 245, 270) with changes on 30 Jun 2023, 1 Jan 2024 and 4 Jul 2024.
3.3 Anomalies and the cleaning decision
Anomalies were detected with a robust z-score on the residual from a 15-day centred rolling median (threshold |z| > 4).
Decision: all seven days were set to missing and linearly interpolated in the training target; the raw values were kept for evaluation. The crash days reflect unmet demand, so they understate true demand, and the spikes are one-offs the model is not expected to predict. A Prophet model trained on the uncleaned series scored MAPE 4.01% against 3.39% for the cleaned series. Note that the 60-day test window contained none of the anomalies, so this comparison measures the effect of cleaning on normal days only.
3.4 Lag structure of the regressors
Correlation of UnitsSold with each regressor shifted by 0 to 5 days:
Marketing spend clearly acts with a 1-day lag. Web search interest is ambiguous on correlation alone because both series share the same seasonal shape, which inflates every lag. A direct forecasting test resolved this (Section 5.3).
4. System Architecture
Data flow: Sales history, then cleaning and feature engineering, then the demand forecast (with uncertainty), which splits into an Inventory branch and a Workforce branch. Both feed the cost model, and all outputs converge on the dashboard.
Shared interface. The forecast module returns one dataframe with the columns Date, ForecastUnits, ForecastLower, ForecastUpper. Both downstream modules consume exactly this shape, whether it comes from Prophet, XGBoost or the historical actuals used as a stand-in forecast. This allowed the three modules to be developed in parallel and meant that swapping in the real forecast required no downstream changes.
Design principle. Machine learning is used where the problem is prediction under uncertainty (demand). Reorder point, safety stock, EOQ and headcount conversion are established operations-research formulas, parameterised by the forecast. They are deterministic, explainable and do not need training.
Stack: Python, Pandas, NumPy, SciPy, Prophet, XGBoost, scikit-learn (Streamlit and Plotly specified for the dashboard).
5. Module A: Demand Forecasting
5.1 Method
• Split: time-based. Training on 1 Jan 2023 to 31 Oct 2024; test on the final 60 days (1 Nov to 30 Dec 2024). No shuffling.
• Baseline: same weekday seven days earlier.
• Prophet: trend, weekly and yearly seasonality, plus five regressors: MarketingSpend (lag 1), WebSearchInterest (lag 2), CompetitorPriceIndex, IsPromo, IsHoliday. 95% prediction interval.
• XGBoost: 400 trees, depth 4, learning rate 0.03, subsample and column sample 0.8, seed 42. Features: sine/cosine encodings of day of week and day of year (so Sunday and Monday are adjacent), the lagged regressors, promo and holiday flags, lag-7 and lag-14 demand, and 7-day and 30-day rolling mean and standard deviation.
• All models were scored on the same held-out window.
5.2 Results
The one-step XGBoost variant uses rolling statistics up to the previous day, which are not available across a 30-day forecast without recursive prediction. The 30-day-safe variant shifts those features by 30 days and is the fair comparison for the planning use case. Prophet beat both. It also produced well-calibrated intervals: 95% of actual test values fell inside the 95% band.
Caveat. The test window is November to December, a low-demand period with no anomalies. A single holdout is a weaker test than rolling-origin backtesting across seasons, and MAPE in the June peak may be higher.
5.3 Lag ablation
Prophet MAPE on the same holdout with different regressor lags (competitor price, promo and holiday held fixed):
A 1-day marketing lag and 2-day search lag is the best combination, confirming the documented structure even though raw correlation alone pointed to lag 1 for search.
5.4 Explainability
Prophet components (standard deviation of contribution, units/day): yearly seasonality 24.5, weekly seasonality 11.3, trend 9.3, and regressors 13.9 in total. Within the regressors: lagged marketing 9.0 (up to +55.6 units on peak spend days), promo 8.2 (up to +32.1), holiday 4.4, lagged search 4.3, competitor price 2.5. The yearly cycle is therefore the largest driver of the swing between January and June.
XGBoost importance (gain-based):
When recent history is available, demand predicts itself through autocorrelation. When it is not (the 30-day horizon), the model leans on seasonality and the leading indicators, which supports the value of the lagged regressors. SHAP values were not computed in this run; the figures above are XGBoost gain importance.
5.5 Forward forecast: 31 Dec 2024 to 29 Jan 2025
Prophet retrained on all 730 days produced:
The mean is above January 2024 actuals (185.7) and close to the last 30 days of actuals (206.7), consistent with roughly 6% to 10% year-on-year growth. Assumption to note: future marketing spend, search interest and competitor price were held at their last known values and no promotions were scheduled. Real values will differ, so the interval is optimistic about regressor uncertainty. A planned promo would be added by setting IsPromo to 1 on that date, which adds about 29 units.
6. Module B: Inventory Optimization
6.1 Formulas
• Safety stock: SS = z × σ_d × √L, with z = 1.65 (95% service level), σ_d the 30-day rolling standard deviation of daily demand and L the lead time.
• Reorder point: ROP = μ_d × L + SS, with μ_d the 30-day rolling mean demand.
• Order quantity (EOQ): √(2 D S / H), with ordering cost S = INR 500 per order and holding cost H = INR 8 per unit per year. With 2024 demand (about 83,000 units/year) the EOQ is 3,305 units, matching the historical mean order of 3,219.
All three are configurable parameters.
6.2 Seasonal behaviour
Using a planning lead time of 4.96 days (the mean on non-disrupted days), the reorder point rises with demand:
Average ROP is 1,208 (historical dataset: 1,268) and average safety stock is 81 (dataset: 97), so the independent implementation is close to the generator logic.
6.3 Stockout risk scoring and backtest
For each day the module estimates the probability that demand over the expected wait for the next arrival exceeds stock on hand, using a normal approximation. The wait is the remaining time on any open order, otherwise the recent mean lead time. A day is flagged if the probability exceeds a threshold. A stockout is counted as caught if a flag was raised on that day or in the preceding 0, 3 or 5 days.
All 16 stockout days, grouped into 10 distinct episodes, were flagged at every threshold tested. This meets the 80% recall target, but two cautions apply.
• Precision is low. The base rate of a stockout is 2.2% of days, so 5% to 10% precision is a 2x to 5x lift but still means roughly 9 of 10 flags are false alarms. Flagging 20% to 46% of days is not operationally useful on its own.
• Recall did not vary across thresholds 0.05 to 0.50. Higher thresholds were not swept and are the obvious next step to find the point where recall begins to fall. The business should set the threshold from the relative cost of a false alarm and a missed stockout.
A simpler rule (flag when stock is at or below the reorder point) also reached 100% recall but at 6.5% precision on 34% of days.
6.4 What actually caused the stockouts
Inspecting the 10 episodes gave a more specific explanation than the documentation suggests.
• Only 1 of 16 stockout days (12 Jul 2023) coincides with a disrupted lead time (11 days or more). The other 15 occurred when the recorded lead time was 4 to 6 days.
• In every one of the 10 episodes, the triggering order had a lead time of 6 or 7 days, against a mean of 4.96. The reorder point assumed about 5 days.
• Orders were placed when stock was already 23 to 243 units below the reorder point, because the trigger is checked at end of day.
Implication: the failure mode is a planning lead time that is slightly too optimistic combined with a late trigger, not rare supply shocks. Planning on the 94th percentile lead time (6 days; 673 of 713 non-disrupted days are at or below 6) would address most of these.
6.5 Lead-time variability
A lead-time-aware safety stock was tested: SS = z × √(L σ_d² + μ_d² σ_L²), with σ_L the 30-day rolling standard deviation of lead time. It raised mean safety stock from 81 to 548 units (mean ROP 1,724). The disruption days (11 to 17 days) inflate σ_L so strongly that the buffer becomes uneconomic. Recommendation: winsorise lead times or cap the volatility term, and use it only as a warning that widens the buffer temporarily.
7. Module C: Workforce Planning
7.1 Method
• Required headcount: ceil(7-day forward-average forecast ÷ 25 units per worker). This reproduces the dataset column exactly on 71.6% of days (correlation 0.89); the remainder differ by rounding or a slightly different smoothing.
• Gap: required minus current staffed headcount, evaluated against the actual staffing level.
• Hiring lag: a new hire takes 10 days to become productive, so a hiring decision must be made at least 10 days before the projected shortfall.
• Overtime and backlog: overtime hours per worker = min(4, gap ÷ staffed × 8). Any remainder is projected backlog.
The lag-aware recommendation is specified and the dataset hiring events were examined (Section 7.3); a coded recommendation engine was not run as part of this analysis.
7.2 Backtest: why recall falls short
There are 47 backlog days. The finding is that most are invisible to a smoothed signal.
• 24 of 47 backlog days (51%) had no positive headcount gap on the 7-day plan: staffing looked adequate on paper.
• On all 47 days, actual demand exceeded staffed capacity (staffed × 25 units). The cause is a single-day demand peak that a 7-day average absorbs.
• Backlogs are small: 92 units in total, a median of 1 unit per day. The largest, 40 units, was the 28 Feb 2024 viral spike (378 units sold, 9 staff, 4 hours overtime), when inventory was also fine (2,220 on hand).
• Backlog days cluster in April to May (22 days across both years) and December to February (17 days), with the remainder scattered.
Even with perfect same-day hindsight, the 7-day signal reaches only 51% recall, which rules out a lag or logic error and confirms the shortfall is structural. The 80% target is not met by the smoothed signal.
Recommended design: two signals with two responses.
• The 7-day signal drives hiring recommendations. It is precise for sustained shortfalls, which hiring can fix.
• A short-window (1 to 3 day) spike signal drives same-day overtime or flex-staffing alerts. It reaches the target only when combined with the 7-day signal, at the cost of flagging roughly 42% of days.
The same-day result uses actual demand. Whether a forecast-based version can reach the same recall depends on forecast error on spike days (the model is not expected to predict viral spikes) and was not tested.
7.3 Hiring events and the lag rule
The workforce data contains 8 staff increases. Applying the 10-day lag, the decision date is:
The data also shows 9 attrition drops, each of which re-opens a gap that the next review must cover.
7.4 Forward view
On the 30-day forecast, required headcount is 9 (9 to 10 on the upper bound). With 11 staff at 30 Dec 2024, no hiring is recommended in the horizon and the team is carrying about 2 more workers than the forecast needs.
8. Module D: Integrated Cost Model
8.1 Components
The stockout term is policy-aware: it is computed from each policy’s own safety stock rather than from fixed historical lost sales. An early version used historical lost sales, so the cost never changed with the service level and the comparison was meaningless.
8.2 Scenario comparison (2024 demand, INR)
Scenarios hold staffing flat across the year to isolate the policy effect.
8.3 Findings
• More staff is not cheaper. Moving from 9 to 11 staff adds INR 657,000 in wages and saves INR 370,251 in overtime, 24,382 in backlog and 6,649 in stockout cost: a net cost of INR 256,025 (+7.4%).
• Inventory service level is nearly free. Going from z = 1.65 to z = 2.33 at the same staffing adds INR 307 of holding and removes INR 6,649 of expected stockout cost, a net saving of INR 6,342. Labor is 86% of the total, so inventory policy is a minor lever at this scale.
• Recommended policy: z = 2.33 with 9 to 10 staff, subject to the uncertainty below. Flexible seasonal staffing that follows the demand curve was not costed and is the most promising further option.
Limits: the stockout term is lost revenue, not margin; the backlog penalty is an assumption; flat staffing at 9 is below the actual 2024 average of 10.8; and the 4-hour overtime cap and normal-demand assumption simplify real behaviour.
8.3a What-if example
A +20% demand shock for 14 days raises the 30-day total from 6,193 to 6,761 units (+9.2%) and lifts peak required headcount from 9 to 10, which the current 11 staff absorb. It also brings the reorder date forward and enlarges the order needed.
8.4 Current inventory position (30 Dec 2024)
Stock on hand is 1,507 units with no order open (the last arrival was 22 Dec). At about 207 units per day, stock reaches the reorder point of roughly 1,127 within 2 days. Recommendation: place an order immediately for about 3,300 units, planning on a 6-day lead time. Without it, stock would run out within about 7 to 8 days.
*9. Module E: Dashboard *
The dashboard was deferred, as permitted for the MVP, and all analyses above were run offline. The specified design has four panels fed by the shared forecast interface:
- Forecast: 30-day forecast with confidence band, actual versus forecast overlay, and an explainability panel.
- Inventory: current stock, reorder point, days to projected stockout, recommended order size and date, risk tier.
- Workforce: current and required headcount, hiring decision date, projected overtime and backlog.
- Cost: side-by-side comparison of at least two scenarios. A demand-shock control (for example +20% for 14 days) should update panels 2 to 4. The what-if example in Section 8 shows the underlying calculations already work and are fast to run. **
- Validation Against Acceptance Criteria**
11. Limitations and Risks
• Synthetic data: results reflect the generator’s rules (for example, lagged regressor effects are built in), so accuracy will be lower on real data.
• Single holdout: one low-season 60-day window. Rolling-origin evaluation is needed before trusting MAPE across the year.
• Regressor assumptions: future marketing, search interest and competitor price were forward-filled, understating uncertainty.
• Inventory precision: high false-alarm rates until the threshold is tuned.
• Workforce spikes: viral spikes are unpredictable, so same-day flex staffing is a process answer, not a forecasting one.
• Cost assumptions: backlog penalty, wage structure and flat-staffing scenarios are simplifications.
• Data documentation: the discrepancies in Section 3.2 should be corrected before the datasets are reused.
12. Lessons and Next Steps
Bugs found and fixed:
• Falsy-value bug: param or default silently replaced a valid 0 with the default (for example a service level z of 0). Fixed with explicit is not None checks.
• Policy-blind cost: stockout cost used fixed historical lost sales, so it never responded to policy. Replaced by an expected-shortfall estimate.
• EOQ clipping: an early generator capped EOQ below its natural range, causing 59 false stockout days, found by comparing against the unclipped formula.
Appendix: Reproducibility
