Retail Operations Transformation & Profitability Optimisation

The assignment asks five connected questions: which stores underperform, where operating cost is concentrated, which products generate sales without adequate contribution, how stock should be allocated, and which operational changes justify investment. Answering them together matters. A profitable category may still tie up too much cash; a weak store may have a lease problem rather than a demand problem; a stock reduction may improve carrying cost while damaging availability.

The supplied dataset covers April 2024 to March 2026, with FY25 and FY26 each containing twelve complete months. It contains 20 stores, 80 SKUs, eight categories, three store formats and five region labels. All six CSVs are available for download. The original report states four regions, but the store dimension also includes Central; the website follows the source table. The data were generated with fixed seed 2026 and calibrated to approximately ₹50 crore of FY26 sales. Rent-heavy malls, staffing mismatches, stock imbalance and discount creep are designed features of the simulation.

The method first aggregates store–SKU–year economics, then builds store P&Ls with an 18% annual inventory holding charge and HQ allocated by revenue share. Gross margin deducts COGS from net revenue. Product contribution also deducts freight, write-offs and holding cost. Fully loaded operating profit then deducts store operating expenses and allocated HQ. These definitions keep a product's direct economics distinct from the economics of the whole store.

Revenue rises from ₹46.48 crore to ₹50.00 crore, but COGS grows faster, from ₹28.84 crore to ₹32.23 crore. Gross-margin rupees barely move, while the gross-margin rate falls from 38.0% to 35.5%. After operating costs, profit falls by approximately ₹0.84 crore. The cost-to-revenue ratio therefore increases from 79.9% to 83.0%. Sales growth is real within the dataset, but it does not translate into healthier unit economics.

Discount creep explains the largest part of the rate deterioration. Repricing FY26 volumes at FY25 category discount rates lifts the counterfactual gross-margin rate by about 2.2 percentage points, approximately 70% of the 3.1-point operating-margin decline. This is a diagnostic counterfactual, not a forecast of recoverable profit: it holds units constant and recalculates both revenue and the margin denominator. The initiative model later allows for customer response to higher realised prices.

Inventory holding cost and write-offs together rise by approximately ₹0.26 crore. Holding cost grows 25.3% and write-offs 42.6%, well ahead of sales. Rent and staff cost remain broadly stable as shares of chain revenue, so blanket expense reductions would miss the dispersion across stores. The practical response is to address discount and stock processes across the chain while targeting structural store problems individually.

Six stores earn less than 15% operating margin: Lucknow, Bhopal, Patna, Mumbai Lower Parel, Surat and Kolkata. Together they generate approximately 26% of revenue and 17% of profit. Their average margin is about 11.3%, compared with 19.0% for the other fourteen stores. Applying that peer rate to their sales implies approximately ₹1.00 crore of annual profit opportunity. This benchmark is a diagnostic gap, not an additional benefit to add to the seven-initiative business case.

The store economics separate into four overlapping patterns. Kolkata and Lower Parel carry rent of 11.2% and 10.8% of sales, versus 5.2% for the chain. Lucknow, Surat and Patna produce only ₹820–₹930 per labour hour, compared with a chain median near ₹1,587. Patna and Bhopal have elevated shrinkage and freight burdens. Surat and Bhopal also show the weakest price realisation. A single cost-cutting programme would treat these mechanisms too crudely.

Every store remains profitable after allocated HQ. The proposed response therefore preserves the network while improving leases, rosters, stock discipline and discount controls. Lease events provide a natural point to reconsider the rent-heavy locations if negotiations fail. Real decisions would also require lease commitments, closure costs, customer overlap and the portion of HQ that could actually be avoided.

Packaged Staples and Electronics Accessories account for 29.9% of revenue but just 11.4% of product contribution. Staples contribute only 9.7% of their own sales after freight, write-offs and holding cost; Electronics Accessories contribute 16.5%. These categories also grow quickly in the constructed dataset, which makes revenue growth less profitable at the chain level. Contribution shares describe the retailer's own economics, not market share or a competitive advantage.

Personal Care and Health & Wellness convert a much larger share of revenue into contribution. Apparel and Home & Kitchen retain healthy contribution rates, yet their low turns tie up substantial inventory. Apparel combines a 27.5% average discount with 98 days of inventory; Home & Kitchen holds about 87 days. The decision is therefore two-dimensional: protect attractive contribution while reducing unnecessary stock and avoid assuming that high margin automatically means good cash productivity.

The report treats staples as traffic-driving products, but no basket-level dataset is supplied to prove that role. The proposed policy keeps essential ranges available while testing vendor terms, selective repricing and discount guardrails. Removing a whole category on direct margin alone would ignore possible customer and basket effects that this dataset cannot measure.

Eight SKUs generate about ₹6.2 crore of annual sales while earning less than 8% contribution margin. Four are negative at chain level. The weakest toy line has a gross margin above 50%, yet loses money after its small sales base is charged for freight, write-offs and inventory. This illustrates why gross margin alone is insufficient for assortment decisions.

The scenario applies inventory right-sizing before evaluating range removal. Only 31 of the 1,600 store–SKU lines remain negative after that adjustment, representing four SKUs and approximately ₹0.35 crore of revenue. This ordering prevents the model from delisting products merely because the existing stock policy makes them appear uneconomic. The range model assumes 40% of displaced sales transfer to retained products and a 30% clearance loss on remaining inventory; both assumptions require real-world validation.

Thin-margin staple and accessory lines that remain positive are candidates for supplier negotiation or selective repricing. Negative lines should first be checked for strategic basket roles, local demand, availability and substitution. The analysis supports a review list and controlled range trials, not an automatic nationwide deletion rule.

Average inventory rises from ₹3.57 crore to ₹4.47 crore, while turns fall from 8.1× to 7.2× and days of inventory rise from about 45 to 51. Every category slows. Ranking the 1,600 store–SKU lines by annual revenue reveals the underlying allocation problem: the slowest quartile holds 21.7% of inventory for 6.3% of sales, while the fastest quartile generates 54.8% of sales and 84.9% of estimated lost sales from 29.7% of inventory.

The generator applies category-level stock rules and minimum display quantities rather than sizing every line directly to its own demand. That constructed mechanism can leave slow items overstocked and fast items unavailable at the same time. Under the model's demand-based cover targets, 809 store–SKU lines hold more than 1.5 times their target inventory, representing ₹2.51 crore of existing stock. The conclusion is to rebalance stock; simply cutting the chain's inventory budget would leave the availability problem unresolved.

Estimated lost revenue is ₹3.91 crore, or about 7.2% of estimated demand. The estimator divides observed units by in-stock days, extrapolates that rate over stock-out days, and values the result at realised prices. This assumes unmet demand resembles observed in-stock demand and does not fully model substitution or shopping elsewhere. The initiative recovers 40% of those estimated units, releases ₹1.38 crore from excess stock and reinvests ₹0.97 crore into shortages. Inventory rebalancing releases approximately ₹0.41 crore net; the subsequent assortment exit adds about ₹0.05 crore, taking programme working-capital release to ₹0.46 crore.

Demand-driven replenishment is the largest proposed intervention. The base model combines recovered sales contribution, reduced write-offs and a modest holding-cost saving, then deducts ₹0.10 crore of annual running cost. Category cover targets range from 20 days for Staples to 70 days for Apparel, with a minimum of four units at each store–SKU line. Only 75% of calculated excess stock is assumed releasable. These constraints acknowledge commitments and practical stock floors without claiming they have been tested with an actual supply chain.

Discount governance separates store-level leakage from category-wide discount creep. The model recaptures 70% of local leakage at an assumed price elasticity of 0.5 and 35% of category creep at 1.5, against a category guardrail of the FY25 discount rate plus two percentage points. Benefits are net of estimated volume loss. Vendor terms assume a 1% saving on retained Staples, Electronics Accessories and Beverages COGS. These are negotiation and customer-response assumptions, not achieved savings.

Labour targets use 90% of median revenue per FTE, a 4.5-FTE floor and 60% capture through attrition, with a 3% roster saving elsewhere. The result corresponds to approximately 7.6 FTE of captured capacity change across six stores. Rent assumes a 12% reduction with 60% negotiation success at the two stores above 8% rent-to-sales. Shrinkage uses a process intervention at Patna and Bhopal; the more expensive EAS/CCTV option is rejected by the detailed economics. This resolves an earlier report archetype that mentions technology as a possible response.

The model first changes inventory, then evaluates remaining negative lines and applies pricing and vendor calculations to retained lines. It also charges one-time clearance, redeployment and setup costs. This treatment reduces important overlaps. A real programme would still need a common benefits ledger to prevent two owners from claiming the same improvement and to distinguish avoidable cash savings from allocated accounting charges.

The base programme requires ₹0.80 crore upfront and ₹0.19 crore in annual running cost. Initiative-specific ramps produce net cash flows of approximately ₹0.36 crore, ₹0.98 crore and ₹1.11 crore in years one to three. Discounted at 12%, the three-year NPV is ₹1.09 crore and cumulative undiscounted cash breaks even after approximately 17 months. The model excludes the ₹0.46 crore working-capital release from NPV because the carrying charge already includes capital cost; that release is reported separately as a cash memo item.

Recovered sales, assortment changes and price-volume effects increase base-case annual revenue to ₹51.24 crore. Operating profit reaches ₹9.61 crore, so the correct modelled margin is 18.8%, not 19.2% calculated against the unchanged ₹50 crore baseline. The detailed report uses the changing denominator; an executive slide does not. The 1.8-point improvement recovers about 57% of the original margin decline and leaves further performance work unresolved.

Conservative delivery assumes 70% of gross benefits, a 15% cost overrun and a slower first-year ramp. Its three-year NPV is only ₹0.09 crore and payback extends to 29 months. Stress delivery assumes 45% of benefits, 30% higher costs and a slower multi-year ramp; NPV falls to −₹0.80 crore, with no payback inside five years. Although its nominal full-delivery run-rate benefit is ₹0.34 crore, the stress ramp reaches only 90% of that benefit schedule, leaving year-three annual cash benefit near ₹0.28 crore. Staged funding is therefore material to the investment logic.

The programme needs approximately 54% of modelled gross benefits to reach zero three-year NPV at budgeted costs. With a 30% cost overrun and the stress ramp, that requirement rises to about 88%. The most consequential uncertainties are the proportion of stock-out demand that is recoverable and the price response to discount changes. These are causal questions that descriptive SQL cannot resolve.

The report's 20,000-run Monte Carlo varies initiative delivery from 40% to 120% using triangular distributions, applies a shared execution factor of 85%–110%, and varies cost from 95% to 150% of budget. It reports 96% of draws with positive three-year NPV and net-benefit percentiles of ₹0.70, ₹0.89 and ₹1.06 crore. This is a sensitivity result conditional on chosen distributions. It is not an empirically validated 96% probability of success; the ₹1.11 crore base case sits near the optimistic end of those benefit draws.

The proposed validation programme uses four inventory pilot stores, including two flagged and two healthier stores, plus an eight-week matched-store pricing test. The design should preserve manual replenishment overrides, track availability and customer response, and compare equivalent trading periods. Supplier savings need documented terms; labour savings need payroll or redeployment evidence; carrying-cost improvements need a reconciliation between accounting benefit and avoidable cash. No such pilots or negotiations were conducted in this simulation.

The study ends with a Power BI specification rather than a deployed dashboard. Five pages would cover executive performance, store economics, product contribution, stock and availability, and scenario assumptions. The executive view would connect margin and discount trends with store rankings, inventory turns and the velocity-quartile mismatch. Store drill-through would help operations teams see whether an issue is lease cost, staffing, shrinkage, availability or price realisation.

Before implementation, the model needs a proper calendar and fiscal-year dimension, unique relationship keys and reconciled HQ allocations. The report's suggested direct HQ-to-month relationship does not provide a simple many-to-one relationship because fiscal year repeats across months; a fiscal-year dimension or explicit annual allocation is needed. Measures should also retain complete-year denominators when calculating annual inventory costs and avoid reallocating company HQ merely because a user filters the store view.

A monthly benefits review would assign one accountable owner per initiative, compare actual implementation cost with the budget, reconcile operational measures to the P&L and record exceptions. The analytical contribution is the traceable chain from raw tables to diagnosis, intervention assumptions, cash flows and scale gates. The report, all six original datasets and SQL analysis are downloadable for readers who want to inspect the work in detail. No Power BI file or Excel companion was supplied, so neither is presented as a finished deliverable.