Inventory Analytics, Optimisation and Managerial Decisions

Introduction

Retail inventory management has moved from periodic stock counting to continuous, data-enabled decision-making. A modern retailer may manage millions of SKU–location combinations across stores, warehouses, dark stores, marketplaces and fulfilment centres. The managerial challenge is not merely to know how much inventory exists, but to determine whether it is in the right place, in the right condition, at the right time and at an economically justified cost.

Inventory analytics integrates transactional, operational and financial data to support decisions about replenishment, allocation, markdowns, transfers, assortment, working capital and service levels. It also provides the evidence required for inventory audit: unexplained variances, ageing, shrinkage, negative stock, obsolete items and inconsistencies between physical and system records.

This chapter develops a decision framework using SKU–location data. It explains dashboard design, variance and ageing analysis, dead-stock identification, service-cost trade-offs, constrained optimisation, scenario analysis and executive communication. The emphasis is managerial: analytical outputs must be converted into accountable actions.

Learning Objectives

After studying this chapter, the learner should be able to:

1. Construct an analytical model at SKU–location level.
2. Design dashboards for inventory accuracy, availability, ageing and working capital.
3. Calculate and interpret inventory variance, ageing, dead stock, service and cost measures.
4. Apply inventory optimisation under budget, capacity and service constraints.
5. Evaluate scenarios and communicate recommendations to executives.
6. Recognise data, model, governance and ethical limitations in inventory analytics.

1. The SKU–Location Decision Unit

A stock-keeping unit (SKU) is a uniquely identifiable product configuration, such as a particular brand, size, colour and pack. A location may be a store, distribution centre, fulfilment node or online inventory pool. The basic analytical unit is therefore the pair \(i,j\), where \(i\) denotes SKU and \(j\) denotes location.

A useful fact table contains, at minimum:

The available-to-promise quantity is commonly represented as:

where \(OH\) is on-hand stock, \(IT\) is confirmed inbound stock, \(R\) is reserved stock and \(B\) is stock committed to backorders. All quantities must be measured in the same unit and for the same SKU–location combination. Inbound stock should be included only when its status and expected receipt are sufficiently reliable.

A data model should distinguish physical stock, book stock, sellable stock and financial inventory. For example, damaged units may be physically present but unavailable for sale; inventory at cost may differ from inventory at retail selling price. Failure to make these distinctions produces misleading dashboards.

2. From Data to Decision: The Analytical Architecture

A robust inventory analytics process has six layers:

1. Capture: point-of-sale, warehouse management, enterprise resource planning, e-commerce and counting systems.
2. Validate: check duplicates, missing master data, impossible dates, negative quantities and unit-of-measure conversions.
3. Integrate: create common SKU, location, calendar and supplier keys.
4. Measure: calculate operational, financial and service indicators.
5. Diagnose: identify causes, not merely symptoms.
6. Decide and monitor: assign actions, owners, deadlines and expected benefits.

A dashboard should answer a decision question. “Inventory value” is descriptive; “which locations contain high-value, low-service stock requiring transfer?” is managerial. Dashboards should therefore combine a headline measure, diagnostic segmentation and an action queue.

Dashboard design

A senior dashboard may contain:

Visual design should avoid false precision. A rate based on ten observations should not be presented with the same confidence as one based on one million transactions. Filters should permit drill-down from enterprise to region, location, category, SKU and transaction.

3. Core Measures and Their Interpretation

3.1 Inventory variance and record accuracy

For a count event, quantity variance is:

A positive value means more units were physically found than recorded; a negative value means fewer were found. The value variance at unit cost \(c_i\) is:

A commonly used absolute accuracy measure is:

This formula is suitable when book quantity is positive and is capped at zero if the absolute difference exceeds book quantity. For zero-book situations, accuracy must be defined separately—for example, as “matched” when both physical and book quantities are zero, and “exception” otherwise. Organisations should not silently divide by zero or exclude zero-book exceptions.

Illustration. Book stock is 240 units, physical stock is 228, and unit cost is ₹350. Quantity variance is −12 units and value variance is −₹4,200. Accuracy is \(1 - 12/240 = 95\%\). The result indicates a control issue, but not its cause. Possible causes include unrecorded sales, receiving errors, theft, mis-picks, returns not processed or counting mistakes.

3.2 Inventory turnover and days of supply

Inventory turnover is normally defined as cost of goods sold divided by average inventory at cost:

For a period of \(T\) days, days of supply (DOS) is:

These measures assume that the denominator and numerator cover the same period, valuation basis and scope. A high turnover is not automatically desirable if it results from stockouts and lost sales; a low turnover may be justified for seasonal, strategic or long-lead-time products.

3.3 Service measures

The cycle service level is the proportion of replenishment cycles with no stockout. The fill rate is the proportion of demand units supplied immediately:

These measures are not interchangeable. A cycle can experience one stockout but still have a high fill rate if the shortage is small. Conversely, a single large stockout can reduce fill rate substantially while affecting only one cycle.

Lost sales are difficult to observe because the customer may leave without recording a transaction. Estimates should use evidence such as shelf gaps, online search abandonment, substitution, traffic, historical demand and comparable locations. Report estimated lost sales separately from observed sales.

3.4 Forecast error and bias

For actual demand \(A_t\) and forecast \(F_t\), forecast error is:

Mean absolute percentage error (MAPE) is:

MAPE is unsuitable when actual demand is zero or close to zero. Weighted absolute percentage error or scale-dependent measures such as mean absolute error may be more appropriate. Bias can be monitored through mean error:

Positive bias indicates systematic under-forecasting under this sign convention; negative bias indicates over-forecasting.

4. Ageing, Dead Stock and Obsolescence

Ageing analysis classifies inventory according to the time since receipt, last sale, last movement or expiry date. The correct date depends on the decision. Receipt age is relevant to perishability and capital lock-up; last-sale age is useful for detecting demand inactivity.

Typical buckets might be 0–30, 31–60, 61–90, 91–180 and over 180 days. Buckets should reflect product life cycles rather than be copied mechanically across categories. Fashion products may become obsolete in weeks, while spare parts may remain valuable for years.

Dead stock is inventory with no expected economic movement within a defined policy horizon. A defensible rule combines several conditions:

• no sales or consumption for a specified period;
• no confirmed demand, open order or service obligation;
• no strategic or regulatory reason to retain it;
• positive quantity or value after excluding already reserved units.

A dead-stock flag is a decision aid, not proof that disposal is correct. Before disposal, managers should check data errors, channel transfers, repair demand, product substitution, supplier return rights and legal retention requirements.

Ageing value at cost is:

where \(b\) is an age bucket. For goods with expiry dates, the remaining shelf-life percentage is:

This measure assumes reliable manufacture and expiry dates and should not override product-specific safety rules.

5. Service–Cost Analysis

Inventory decisions balance service benefits against investment and risk. Relevant cost components include purchase cost, ordering cost, holding cost, shortage cost, markdown loss, disposal cost, transportation and handling.

A simplified annual total-cost model is:

where \(D\) is annual demand in units, \(C\) purchase cost per unit, \(Q\) order quantity, \(S\) ordering cost per order and \(H\) annual holding cost per unit. The final two terms represent shortage and obsolescence costs and are often uncertain. The basic economic order quantity is derived only when those terms are ignored and demand, lead time and costs are sufficiently stable:

EOQ is not a complete replenishment policy. It does not determine safety stock, allocation, capacity feasibility or promotional response.

For continuous-review replenishment, a commonly used reorder point is:

where \(μ_L\) is expected demand during lead time, \(σ_L\) is the standard deviation of lead-time demand and \(z\) is the standard-normal factor associated with the chosen cycle service level. If daily demand is independent with mean \(μ_d\) and standard deviation \(σ_d\), and lead time is constant at \(L

times days, then \(μ_L=Lμ_d\) and \(σ_L=√Lσ_d\). If both demand and lead time vary, the variance must include both sources; the simple expression should not be used without qualification.

Worked example. Daily demand has mean 40 units and standard deviation 10 units. Lead time is constant at 5 days. For a 95% cycle service level, use \(z≈1.645\).

Expected lead-time demand is \(5×40=200\) units. Lead-time standard deviation is \(√5×10≈22.36\) units. Safety stock is \(1.645×22.36≈36.8\), or 37 units. Therefore, reorder point is approximately 237 units. This result assumes normally approximated independent daily demand, stable lead time and a service objective defined as cycle service level.

6. Optimisation Under Constraints

Retail optimisation is usually constrained. Inventory cannot exceed budget, shelf space, warehouse cube, store capacity, supplier minimums or labour capability. A simplified allocation model is:

subject to:

Here, \(x_{ij}\) is allocated quantity, \(m_{ij}\) is expected contribution margin or risk-adjusted benefit per unit, \(c_i\) is unit cost, \(B\) is budget, \(v_i\) is space volume and \(K_j\) is location capacity. In a practical integer model, \(x_{ij}\) may be restricted to whole units, case packs or minimum presentation quantities.

The objective should not be chosen casually. Maximising margin may reduce availability for essential products; maximising sales may overvalue low-margin items. Multi-objective models can include service penalties, working-capital costs and strategic weights. Constraints should reflect policy, not merely what happens to be available in the database.

Scenario analysis

Scenario analysis changes assumptions and observes consequences. Useful scenarios include:

• demand increase or decrease by category;
• supplier lead-time disruption;
• budget reduction;
• additional warehouse capacity;
• increased service target;
• markdown or liquidation programme;
• alternative allocation between stores and online fulfilment.

A scenario table should show assumptions, outputs, risks and decision triggers. Sensitivity analysis is especially important for uncertain parameters. A recommendation that is optimal only under one precise forecast is not robust.

7. Translating Analytics into Managerial Action

Analytics becomes valuable when every exception has a response logic. For example:

A recommendation should state the baseline, proposed action, expected benefit, cost, risk, owner, timing and monitoring metric. “Reduce excess inventory” is not an executable decision; “transfer 600 units from three low-demand stores to two high-demand stores, subject to shelf capacity, then review sell-through after 14 days” is.

8. Illustrative Case: Meridian Urban Retail

Background

Meridian Urban Retail operates 80 convenience stores and one regional distribution centre. Its dashboard reports overall inventory accuracy of 96%, fill rate of 94% and inventory worth ₹48 million at cost. Management believes working capital is excessive, but store managers report frequent stockouts of fast-moving products.

Problem

An aggregate measure hides opposing conditions: some stores have excess slow-moving stock, while high-demand stores experience shortages. Finance also reports rising write-downs in chilled and seasonal categories.

Analysis

The analytics team creates a SKU–location dataset and segments items by demand rate, margin, age, service criticality and physical variance. It finds:

• 12% of inventory value has had no sale for more than 90 days;
• 4% of units are in locations different from the planned assortment;
• 70% of value variance is concentrated in 9% of SKU–location pairs;
• fast-moving beverages show high lost-sales indicators in 18 stores;
• several apparent stockouts are caused by unprocessed receiving and negative book balances;
• chilled products aged beyond the markdown threshold are being transferred too late.

Approach

Meridian introduces four interventions. First, it performs targeted recounts on high-value, high-variance pairs rather than recounting all locations equally. Second, it transfers saleable excess stock to stores with demonstrated demand and available capacity. Third, it applies age-based markdown rules for chilled products, with escalation to supplier return or controlled disposal. Fourth, it raises replenishment parameters for critical beverages while reducing orders for low-velocity items.

A constrained allocation model is used for the transfer plan. The model cannot exceed store capacity, excludes damaged units and preserves minimum presentation quantities. A weekly exception dashboard assigns actions to store, supply-chain and finance owners.

Outcome

After eight weeks, the illustrative pilot reports a reduction in aged inventory value, improved availability for the selected beverage group and fewer unexplained adjustments. The result is not attributed solely to the model: process corrections, targeted counting and better ownership also contributed. Management therefore retains a control group of comparable stores to distinguish analytical impact from seasonal effects.

Learning points

1. Aggregate inventory value conceals location-level imbalance.
2. A stockout may be physical, informational or process-related.
3. Dead-stock action must consider returns, substitution and strategic demand.
4. Optimisation is meaningful only when constraints and implementation ownership are explicit.
5. Outcomes require measurement against a baseline and, where possible, a comparison group.

9. Technology, Governance and Ethical Issues

Cloud data platforms, application programming interfaces, radio-frequency identification, computer vision and machine-learning forecasts can improve timeliness and scale. However, automation does not remove the need for controls. A computer-vision count may misclassify products; a forecast model may learn historical stockouts as low demand; an optimisation model may favour profitable customers and disadvantage smaller communities.

Governance should include:

• documented data definitions and ownership;
• version-controlled business rules;
• audit trails for adjustments and model outputs;
• access controls and segregation of duties;
• exception thresholds and human approval;
• monitoring for drift, bias and deterioration;
• retention and privacy controls for customer-linked data.

Managers should distinguish correlation from causation. A location with low sales may have weak demand, poor display, frequent stockouts, incorrect price, inadequate staffing or local competition. Automated markdowns and allocation decisions should therefore include override policies, reason codes and post-decision review.

10. Limitations and Best Practices

Inventory analytics is limited by inaccurate master data, delayed transactions, unobserved lost sales, uncertain lead times, changing assortment and behavioural responses to decisions. A mathematically optimal plan may be operationally infeasible if store staff cannot execute transfers or suppliers cannot meet order patterns.

Best practice is to:

1. Define measures before building visualisations.
2. Reconcile physical, operational and financial quantities.
3. Segment by product life cycle and service criticality.
4. Use value and risk-based exception management.
5. Validate assumptions and quantify uncertainty.
6. Test recommendations through pilots or controlled comparisons.
7. Assign action owners and due dates.
8. Review model performance and override patterns.
9. Communicate trade-offs rather than a single “optimal” number.
10. Integrate analytics with audit, planning, merchandising and finance.

Chapter Summary

Inventory analytics converts SKU–location data into operational and financial decisions. Effective analysis distinguishes book, physical, sellable and available inventory; measures variance, accuracy, turnover, service, forecast error and ageing; and identifies dead stock through explicit policy rules. Service and cost must be evaluated together because high availability can require additional working capital, while excessive cost reduction can create stockouts and lost sales.

Optimisation models help allocate inventory under budget, capacity and service constraints, but their value depends on valid data, realistic assumptions and executable policies. Scenario and sensitivity analysis reveal whether recommendations are robust. Dashboards should move from headline indicators to diagnosis and assigned action. Governance, audit trails, human oversight and ethical review are essential when technology and AI influence replenishment, allocation or markdown decisions.

Key Terms

SKU–location: The combination of a product identifier and a physical or virtual inventory location.

Available-to-promise: Inventory that can reliably be committed to a customer or order.

Inventory variance: Difference between physical quantity and book quantity.

Record accuracy: Degree to which system inventory agrees with physical inventory.

Inventory turnover: Cost of goods sold divided by average inventory at cost.

Days of supply: Inventory expressed as the number of demand days it can cover.

Fill rate: Proportion of demand units supplied immediately.

Cycle service level: Proportion of replenishment cycles completed without a stockout.

Forecast bias: Systematic tendency to over-forecast or under-forecast.

Ageing analysis: Classification of inventory by time since receipt, movement or another defined event.

Dead stock: Inventory with no expected economically justified movement within a policy horizon.

Obsolescence: Loss of economic value because inventory is outdated, expired or no longer saleable.

Safety stock: Additional inventory held to protect against demand or supply uncertainty.

Reorder point: Inventory position at which replenishment is triggered.

Economic order quantity: Order quantity minimising simplified ordering and holding costs.

Constraint: A limit, such as budget, capacity, minimum order or service requirement.

Scenario analysis: Evaluation of outcomes under alternative assumptions.

Exception management: Prioritisation and resolution of abnormal or high-risk conditions.

Inventory governance: Policies and controls governing data, decisions, approvals and accountability.

Review Questions

Short Answer Questions

1. Why is the SKU–location pair a useful unit of inventory analysis?
2. Distinguish fill rate from cycle service level.
3. What is the difference between book stock and sellable stock?
4. State two limitations of MAPE.
5. Why should dead-stock rules include strategic and contractual checks?

Long Answer Questions

1. Explain how an inventory dashboard can progress from descriptive reporting to managerial action.
2. Discuss the assumptions and limitations of EOQ and reorder-point calculations.
3. Explain how ageing and dead-stock analysis can support working-capital reduction without damaging service.
4. Describe a constrained inventory allocation model and the managerial meaning of its constraints.
5. Evaluate the governance risks of using machine-learning forecasts and automated markdowns.

Analytical / Application Questions

1. A store has 500 book units, 470 physical units and a unit cost of ₹240. Calculate quantity variance, value variance and record accuracy. Interpret the result.
2. Daily demand has a mean of 60 units and standard deviation of 12 units. Lead time is constant at 4 days and the desired cycle service level is 95% with \(z=1.645\). Calculate the approximate reorder point and state the assumptions.
3. A retailer has a fixed inventory budget and limited shelf capacity. Propose an objective function, three constraints and a dashboard-based implementation plan for allocating a new seasonal product across stores.

Multiple Choice Questions with Answers

Question 1

Which measure directly compares physical inventory with book inventory?

A. Inventory turnover

B. Record accuracy

C. Fill rate

D. Gross margin return

Correct Answer: B. Record accuracy

Question 2

A book quantity is 240 units and the physical count is 228 units. What is the quantity variance?

A. 12 units

B. −12 units

C. 228 units

D. 240 units

Correct Answer: B. −12 units

Question 3

Which statement best distinguishes fill rate from cycle service level?

A. Fill rate measures value, while cycle service level measures cost

B. Fill rate measures demand units supplied immediately, while cycle service level measures stockout-free cycles

C. Fill rate measures forecast error, while cycle service level measures lead time

D. They are always identical measures

Correct Answer: B. Fill rate measures demand units supplied immediately, while cycle service level measures stockout-free cycles

Question 4

In the simplified EOQ model, which two costs are balanced?

A. Shortage and markdown costs

B. Ordering and holding costs

C. Purchase and tax costs

D. Transport and labour costs

Correct Answer: B. Ordering and holding costs

Question 5

If daily mean demand is 40 units and lead time is 5 constant days, expected lead-time demand is:

A. 8 units

B. 45 units

C. 200 units

D. 400 units

Correct Answer: C. 200 units

Question 6

Which condition most appropriately supports a dead-stock flag?

A. High sales and confirmed demand

B. No movement within policy horizon and no justified future requirement

C. Any product with a high selling price

D. Any product held in a distribution centre

Correct Answer: B. No movement within policy horizon and no justified future requirement

Question 7

What does a positive forecast bias indicate under the convention Bias = average(actual − forecast)?

A. Systematic under-forecasting

B. Systematic over-forecasting

C. Perfect forecast accuracy

D. Zero demand

Correct Answer: A. Systematic under-forecasting

Question 8

Which is an example of a capacity constraint in an allocation model?

A. Total allocated value must be within budget

B. Allocated volume must not exceed store space

C. Forecast error must equal zero

D. All SKUs must have identical demand

Correct Answer: B. Allocated volume must not exceed store space

Question 9

Why should an aggregate inventory dashboard be supplemented by SKU–location analysis?

A. Aggregates always contain duplicate transactions

B. Aggregate values can conceal excess in one location and shortages in another

C. SKU–location analysis eliminates all uncertainty

D. Aggregates cannot measure financial value

Correct Answer: B. Aggregate values can conceal excess in one location and shortages in another

Question 10

Which governance practice is most appropriate for automated inventory decisions?

A. Remove all human overrides

B. Use undocumented rules to preserve flexibility

C. Maintain audit trails, approval rules and performance monitoring

D. Optimise only for sales volume

Correct Answer: C. Maintain audit trails, approval rules and performance monitoring