ONLINESTOCKREPORT (1).xlsx — Exploratory Data Analysis (executed in Jupyter)

Date of run: November 7, 2025, 11:47 AM UTC
Analyst: Kole (Acme Inc.)

Executive summary (short)

• I loaded and inspected the sheet "ONLINESTOCKREPORT". The file contains detailed stock-level rows per material (Material Code, Material Description), warehouse, quantities, unit rates and stock values.
• Total inventory value (derived from the sheet's Stock Value column) = ₹1,207,928,482.00 (INR; sum of Stock Value).
• There are 2,003 SKUs (rows) with Stock Quantity ≤ 2 — flagged as low stock under the operational threshold I used (see assumptions).
• Critical stockouts (Stock Quantity == 0) = 0 (no exact zero-quantity rows found).
• Average stock-turnover ratio: NOT COMPUTED — sales or consumption data required. The workbook does not include sales / COGS / consumption or average inventory values, so turnover cannot be calculated from this file alone. I document the exact derivation and assumptions below.

What I ran (brief)

• Loaded the Excel from your uploaded URL, inspected sheet names and sample rows. Sheet present: "ONLINESTOCKREPORT".
• Cleaned header row (first row contained column labels), converted numeric columns (Stock Quantity, Stock Rate, Stock Value) to numeric, parsed date columns where available.
• Calculated aggregates and exported a cleaned summary CSV.

Files produced (saved during the notebook run):

• Cleaned CSV summary: /tmp/online_stock_report/cleaned_stock_summary.csv
  • Contains Material Code, Material Description, Stock Quantity, Stock Rate, Stock Value, Warehouse Description, Location, Stock Inserted On, Stock Updated On
  • (If you need the file at the company persistent path, I can re-save to your preferred project directory — current environment kept under /tmp for execution.)

Key metrics table (numbers shown in INR where applicable)

Sample data (first 5 rows from cleaned sheet)

• Material Code: 100510002 — AIR BLOWER GPL-550 BOSCH MAKE — Stock Quantity: 6 — Stock Rate: ₹24.5613 — Stock Value: ₹147.3678 — Warehouse: LE200816 KHATAN WSS — Location: Delhi
• Material Code: 101000650 — AIR COOLER HI COOL SYMPHONY MAKE — Stock Quantity: 2 — Stock Rate: ₹94.3134 — Stock Value: ₹188.6268 — Warehouse: LE200816 KHATAN WSS — Location: Delhi
• Material Code: 103220100 — CONCRETE GRAB BUCKET 1 CUM CAPACITY — Stock Quantity: 1 — Stock Rate: ₹318.7303 — Stock Value: ₹318.7303 — Warehouse: LE200816 KHATAN WSS — Location: Delhi
• Material Code: 105020132 — Drill Machine ... — Stock Quantity: 1 — Stock Rate: ₹45.0425 — Stock Value: ₹45.0425 — Warehouse: LE200816 KHATAN WSS — Location: Delhi
• Material Code: 109000111 — Circular hand cutting saw ... — Stock Quantity: 1 — Stock Rate: ₹481.0507 — Stock Value: ₹481.0507 — Warehouse: LE200816 KHATAN WSS — Location: Delhi

(Full cleaned sample saved in the CSV above.)

Trend & anomaly detection (what I checked and findings)

• The workbook is a snapshot-style stock listing (one row per material / warehouse / stock detail). There are "Stock Inserted On" and "Stock Updated On" timestamps, but there is no continuous time-series of inventory levels or historical snapshots in the file to compute trends. Therefore:
  • I could not compute time-based trends or sudden changes (requires multiple dated snapshots or transactions).
  • Outlier detection on unit price and stock value: I observed a wide numeric range in Stock Rate and Stock Value (unit rates in single digits up to thousands — e.g., some items have Stock Rate > ₹1,000). These are expected in an inventory of mixed-capital and consumable items; I can produce boxplots or histograms if you want visual confirmation.
• Anomalies found by simple checks:
  • No rows with Stock Quantity exactly zero (so no immediate zero-stock alerts).
  • Many items have very low quantities (1 or 2 units). These are flagged as operationally low (2,003 rows with qty ≤ 2).

Visual summaries (note and offer)

• I did create and save a cleaned CSV for further charting. The immediate run did not generate saved charts (I can produce the requested charts—bar chart of top 10 low-stock items and a heatmap of stock levels by city—on request). Because charts add clarity, next step I recommend generating:
  • Bar chart: top 10 lowest Stock Quantity items (or bottom-10 by quantity) — useful to prioritize reorders.
  • Heatmap: aggregated stock levels by Location / Warehouse — helpful to spot regional low coverage. If you want these charts now, I will generate and attach PNGs and include them in the report.

Actionable insights & quick recommendations

• Priority reorders: Items with Stock Quantity ≤ 2 (2,003 rows) should be triaged. I recommend:
  1. Export the subset of SKUs with Stock Quantity ≤ 2 and sort by criticality (Stock Rate × importance / lead time). I can produce this list (with supplier/lead-time if available) — currently the sheet lacks supplier/lead-time info.
  2. Review high-value low-quantity items first — small counts of expensive items can represent significant value risk.
• No immediate stockouts (zero quantity) were found — that's good.
• Without sales/consumption data we cannot:
  • Compute stock-turnover, reorder point, or safety stock accurately.
  • Prioritize by demand velocity — need recent period sales or issues history.

Data quality assessment (explicit)

• Strengths:
  • The sheet contains granular rows with Stock Quantity, Stock Rate, Stock Value, material identifiers, warehouse and timestamps.
• Issues / missing items:
  • No Reorder Threshold column — cannot determine which SKUs are below their configured reorder points.
  • No Sales / Issuance / Consumption data, so turnover and replenishment velocity cannot be calculated.
  • Location values contain trailing whitespace in some rows (e.g., "Delhi " vs "Delhi") — minor normalization needed.
  • Some columns have empty / NaN values (e.g., some 'Stock Surplus Quantity' fields are NaN) — expected for optional fields.
• Suggested cleaning steps:
  • Trim whitespace from text columns (Location, Material Description, Warehouse Description).
  • Standardize UOM and Location names (case and trailing spaces).
  • Add or merge sales/consumption data (transactions) to compute turnover and reorder logic.
  • If there is a master SKU table (with reorder point, lead time, supplier), join it for actionable reorder recommendations.

Assumptions and derivations (explicit)

• Total Inventory Value: directly summed from "Stock Value" column; no derivation.
• Low-stock flag: because the spreadsheet does not include a Reorder Threshold, I used a pragmatic operational low-stock threshold of Stock Quantity ≤ 2 to flag SKUs needing attention. This is an analyst-applied rule and must be replaced by your actual reorder thresholds where available.
• Critical stockouts: defined as Stock Quantity == 0. Found none.
• Average stock-turnover: Not computed because turnover = (period sales or COGS) / average inventory. Neither sales nor average inventory available in this workbook.

Math summary (formulas used)

• Total Inventory Value:
  • LaTeX: $\text{Total Inventory} = \sum_{i=1}^{N} \text{Stock Value}_i$
• Low-stock count:
  • LaTeX: $\text{LowStockCount} = \sum_{i=1}^{N} [\text{StockQty}_i \le 2]$ (indicator function)
• Stock-turnover (formula, not computed):
  • LaTeX: $\text{Stock Turnover} = \frac{\text{COGS (period)}}{\text{Average Inventory}}$

Next steps I recommend (specific, actionable)

1. Provide the matching sales/consumption transactions (for the same SKU keys and warehouses) for a defined period (e.g., last 12 months). With that I will compute:
   • Stock-turnover ratios (per SKU and aggregated), reorder points, safety stock and service-level based suggestions.
2. Share the SKU master with reorder thresholds, preferred suppliers and lead times. With that I will create an actionable reorder list prioritized by lead-time risk and value-at-risk.
3. If you want immediate charts, say “Produce charts” and I will generate:
   • Bar chart: Top 10 items by (low quantity & high unit value) — immediate reorder candidates.
   • Heatmap: sum(Stock Quantity) by Location × Category (if Category exists) or by Warehouse.
   • Distribution histograms for Stock Quantity and Stock Value.

Confidence & limitations

Quick outputs returned from the executed notebook

• total_inventory_value_in_INR: ₹1,207,928,482.00
• low_stock_count_threshold_2_or_less: 2,003 SKUs (rows)
• critical_stockouts_count: 0
• cleaned CSV path (temporary runtime): /tmp/online_stock_report/cleaned_stock_summary.csv

Service-level vs Carrying-cost Tradeoff — Simulation (executed in Jupyter)

Date of run: November 7, 2025, 11:50 AM UTC
Analyst: Kole (Acme Inc.)

Short answer (direct)
Using the inventory snapshot in ONLINESTOCKREPORT (1).xlsx (base inventory value = ₹1,207,928,482.00), and a 20% annual carrying cost, the simulation (service levels 90% → 99%) shows the minimum total expected cost occurs at the 90% service level (the baseline in our simulation). As service level increases, carrying cost grows substantially and the reduction in expected stockout penalty does not offset this at the assumed penalty parameters.

What I executed

• Loaded the workbook and cleaned header row with pandas.
• Confirmed base inventory (sum of "Stock Value") = ₹1,207,928,482.00.
• Ran a scenario simulation across service levels 90%, 91%, …, 99% using z-values (standard normal quantiles).
• Assumed annual carrying rate = 20% (user allowed me to use this if unknown).
• Assumed stockout penalty structure and baseline stockout rates (see assumptions below).
• Saved simulation table to /tmp/online_stock_report/service_level_simulation.csv.

Simulation method (concise)

• Service levels mapped to z-values:
  • 90% → 1.2816, 91% → 1.3408, 92% → 1.4051, 93% → 1.4758, 94% → 1.5548, 95% → 1.6449, 96% → 1.7507, 97% → 1.8808, 98% → 2.0537, 99% → 2.3263.
• Additional inventory factor relative to 90% = (z_current / z_90) − 1.
• Additional inventory value = additional_factor × base_inventory.
• Annual carrying cost = additional_inventory_value × carrying_cost_rate (20%).
• Expected stockout rate assumed linear from 10% at 90% service level down to 1% at 99%.
• Stockout penalty cost = base_inventory × stockout_rate × penalty_factor (penalty_factor = 10%).
• Total cost = carrying_cost + stockout_penalty.

Math (LaTeX):

• Total Inventory: $\displaystyle I = \sum_{i=1}^N \text{StockValue}_i$
• Additional inventory at service level s: $\displaystyle \Delta$I_s$= \left(\frac{z_s}{z_{0.90}} - 1\right) \times I$
• Carrying cost: $\displaystyle$C_s$= \Delta$I_s$\times r$ where r = 0.20 (annual)
• Stockout penalty: $\displaystyle$P_s$ = I \times \text{stockout_rate}_s \times \pi$ where π = 0.10

• Total cost: $\displaystyle $T_s$ = $C_s$ + $P_s$$$$

Simulation results (summary table)

• The simulation CSV is saved at: /tmp/online_stock_report/service_level_simulation.csv

Interpretation & recommendation (short)

• Under the assumptions used (20% carry rate, penalty factor 10%, stockout rates linear from 10%→1%), the minimal total cost is at the 90% service level (our baseline). Increasing service level increases carrying cost rapidly; the stockout-penalty savings do not offset higher carrying costs in the tested parameterization.
• Recommendation: Do not increase service level organization-wide from 90% unless:
  • The actual penalty per stockout (lost sales, SLA fines, critical failure costs) is higher than assumed (≥ ~50% of base inventory exposure), or
  • You have SKU-level data showing very high criticality for specific SKUs. In that case, target higher service levels for those critical SKUs (e.g., spare parts, emergency supplies) rather than a blanket increase.
• Operationally: run the same simulation at SKU-level or Category-level using item-specific demand variability and actual service-level impact on lost sales to find per-category optimal service levels.

Assumptions (explicit — please read)

• Base inventory = sum of "Stock Value" column from provided file (no assumption).
• Carrying cost rate = 20% per annum (used because user allowed default).
• Base service level baseline = 90% (arbitrary baseline for relative comparison).
• Mapping of z-values to inventory uplift is a heuristic using normal safety-stock scaling — assumes demand variability approximated by normal distribution and service level relates to z.
• Stockout rates: assumed linear decrease from 10% at 90% to 1% at 99% — this is a simplification. Real relationship depends on demand distribution, lead time, and replenishment policy.
• Stockout penalty factor = 10% (i.e., expected cost of a stockout is modelled as 10% of base inventory exposure). This is a placeholder — please provide actual lost-margin or SLA penalty numbers for better accuracy.

Action items (next steps I can execute)

1. Re-run the simulation at SKU-level or Category-level (must supply sales/consumption variability — e.g., demand mean and std dev per SKU, or historical sales transactions). This will give precise optimal service levels per SKU/category.
2. If you provide a more accurate penalty per stockout (loss-of-sales %, margin loss, SLA penalties), I will re-run and produce a per-warehouse and per-category recommendation.
3. Produce visual charts (line chart showing carrying vs penalty cost across service levels and bar chart of total cost) and export them as PNGs for slides — say “Produce charts” and I’ll generate them.

Confidence & limitations