Demand, Inventory, and Capacity Tracking Models

Introduction

Supply chain decisions depend on converting operational records into timely, interpretable information. A spreadsheet can support this conversion by linking demand history, inventory status, replenishment parameters, and capacity data. However, a spreadsheet is not merely a calculator. It is a model: a structured representation of a process that embeds assumptions, definitions, data-quality rules, and decision logic.

This chapter develops practical models for demand tracking, ABC/XYZ classification, safety stock, reorder points, economic order quantity (EOQ), stock aging, capacity utilization, constraint identification, and exception management. The emphasis is on managerial use of spreadsheets: identifying what should be calculated, understanding when a formula is appropriate, validating outputs, and translating results into decisions.

A sound tracking model should be:

• Traceable: every output can be linked to a source record and formula.
• Unit-consistent: units, time periods, currency, and calendar conventions are explicit.
• Exception-oriented: attention is directed toward material deviations rather than all records equally.
• Scenario-capable: users can test changes in demand, lead time, capacity, and service targets.
• Governed: ownership, version control, access rights, and review procedures are defined.

Learning Objectives

By the end of this chapter, students should be able to:

1. Prepare demand and inventory data for spreadsheet analysis.
2. Select suitable demand summaries and distinguish trend, seasonality, variability, and intermittency.
3. Apply ABC and XYZ classification and interpret the combined matrix.
4. Calculate safety stock, reorder points, and EOQ under stated assumptions.
5. Build stock-aging and capacity-utilization trackers.
6. Diagnose constraints and design useful exception rules.
7. Evaluate spreadsheet models critically, including their data, assumptions, controls, and managerial implications.

1. Model Architecture and Data Preparation

A robust workbook separates four layers:

A basic item-period table may contain item identifier, date, demand quantity, receipts, closing inventory, unit cost, supplier lead time, and available capacity. The time grain must be declared. Daily demand cannot be compared directly with a monthly lead time without conversion.

Useful data-quality checks include duplicate transaction IDs, missing dates, negative quantities where not permitted, inconsistent item codes, unexplained inventory balances, and lead times equal to zero or implausibly large values. A reconciliation for inventory is:

Here, all quantities must use the same unit and period; t identifies the period. The equation is an accounting identity for the recorded system, not proof that the records are correct. A reconciliation difference should be investigated rather than hidden with a spreadsheet adjustment.

2. Demand Tracking and Pattern Diagnosis

Demand tracking begins with a time series D_t, where D_t is observed demand in period t. The arithmetic mean over n periods is:

The mean is useful for stable demand but can be misleading when demand is seasonal, trending, intermittent, or affected by promotions and stockouts. A period with zero sales may represent zero customer demand, or it may represent lost sales caused by no inventory. Analysts should distinguish observed demand from censored demand.

A simple moving average over the most recent k periods is:

where F_t+1 is the forecast for the next period. This method assumes recent observations are informative and gives equal weight to the selected periods. An exponentially weighted forecast gives more weight to recent observations:

where 0 < α ≤ 1 is the smoothing constant. Higher α responds more quickly to changes but is more sensitive to random variation.

Forecast error for period t may be defined as:

Common measures include mean absolute deviation:

and mean absolute percentage error:

MAPE is undefined when actual demand is zero and unstable when demand is close to zero. For intermittent demand, absolute measures, scaled errors, or specialized methods are generally more informative.

Trend may be examined through a time plot or a fitted line, while seasonality is assessed by comparing corresponding periods across cycles. A spreadsheet should not label every fluctuation as a trend. A managerial interpretation should ask whether the pattern is caused by market demand, pricing, promotions, supply shortages, calendar effects, or data capture.

Worked Example: Moving-Average Demand

Observed weekly demand is 120, 150, 135, and 165 units. A three-week moving-average forecast for week 5 is:

If actual demand in week 5 is 180 units, the forecast error is 30 units and the absolute error is 30 units. The result does not establish that demand will continue rising; it only describes performance against this forecast.

3. ABC and XYZ Classification

ABC analysis prioritizes items by annual consumption value. For item i:

If annual demand is 12,000 units and unit cost is $4.50, annual consumption value is $54,000. Items are sorted from highest to lowest value, and cumulative percentages are calculated. Organizations often use approximate thresholds such as A = the highest 70–80% of cumulative value, B = the next 15–25%, and C = the remainder. These are managerial conventions, not universal laws. Thresholds should reflect risk, substitutability, margin, and supply consequences.

XYZ analysis classifies demand predictability. A common indicator is the coefficient of variation:

where σ_D is the standard deviation of demand and D̄ is mean demand, measured over the same periods. A low CV indicates relatively stable demand; a high CV indicates greater relative variation. Exact X, Y, and Z cutoffs must be calibrated to the business. A common illustrative scheme is X ≤ 0.50, 0.50 < Y ≤ 1.00, and Z > 1.00, but these values are not universally valid.

The combined matrix supports differentiated control:

ABC is a value-prioritization method, not a service-level policy by itself. A low-value safety-critical component may deserve tighter control than its cost ranking suggests.

4. Safety Stock and Reorder Points

A reorder point is the inventory position at which replenishment should be initiated. Under a simple continuous-review model:

where μ_L is expected demand during lead time and SS is safety stock. If average demand per period is d̄ and lead time is L periods:

This assumes the demand rate and lead-time units are compatible.

If demand during lead time is approximately normally distributed and lead time is treated as fixed, safety stock is:

where σ_d is the standard deviation of demand per period and z is the standard-normal quantile associated with the target cycle-service probability. For example, a 95% one-sided target commonly uses z ≈ 1.645, subject to the model’s assumptions.

If both demand and lead time vary independently, a commonly used approximation is:

and:

Here, L̄ is mean lead time, σ_L is lead-time standard deviation, d̄ is mean demand per period, and σ_d is demand standard deviation per period. The notation σ_L is used both for lead-time variability in the second equation and, in the first equation, σ_L for demand-during-lead-time standard deviation; spreadsheets should use distinct names such as σ_LT and σ_lead to prevent confusion. The approximation assumes independence and is less suitable when demand and lead time are correlated, distributions are strongly non-normal, or lead time is highly intermittent.

Worked Example: Reorder Point

Average daily demand is 100 units, daily demand standard deviation is 20 units, and fixed lead time is 4 days. For a 95% one-sided target:

Expected lead-time demand is:

Therefore:

The inventory position—not merely on-hand inventory—should be compared with the reorder point:

This prevents duplicate ordering when replenishment is already in transit.

5. Economic Order Quantity and Replenishment Decisions

The classical EOQ model balances annual ordering and holding costs:

where D is annual demand in units per year, S is ordering or setup cost per order, and H is annual holding cost per unit per year. The units are consistent because DS has currency per year and H has currency per unit per year, producing units squared under the square root.

The model assumes relatively constant demand, known parameters, instantaneous replenishment, no quantity discounts, and holding cost proportional to average cycle stock. Under these assumptions, average cycle inventory is approximately Q ÷ 2, and annual relevant cost is:

Differentiating this expression with respect to Q and setting the derivative to zero gives the EOQ formula. In practice, EOQ is a benchmark, not an automatic order quantity. Minimum order quantities, truck utilization, supplier calendars, shelf life, cash constraints, and production changeovers may justify another quantity.

Worked Example: EOQ

Annual demand is 10,000 units, ordering cost is $80 per order, and annual holding cost is $2 per unit. Then:

The implied number of orders per year is approximately 10,000 ÷ 894 = 11.2. If a supplier requires a minimum order of 1,000 units, the analyst should compare the feasible alternatives and document the constraint rather than silently replacing EOQ.

6. Stock Aging and Inventory Health

Stock aging identifies inventory by elapsed time since receipt, production, or last movement. A typical aging table contains item, lot, receipt date, quantity remaining, unit cost, expiry date, and age bucket. Age in days on a reporting date is:

The subtraction is valid only when both fields are actual dates in the same calendar system. Useful buckets might be 0–30, 31–60, 61–90, and over 90 days, but the boundaries should reflect shelf life and product policy.

Aging should follow physical-flow rules such as first-expiry-first-out (FEFO) for dated products or first-in-first-out (FIFO) where appropriate. Aged inventory may indicate slow demand, forecast bias, excessive order quantities, quality holds, incorrect allocation, or master-data errors. The financial exposure of a bucket can be estimated as:

The model should distinguish usable, quarantined, damaged, obsolete, and reserved stock. Combining these categories creates misleading availability.

7. Capacity Utilization and Constraints

Capacity tracking compares required load with available productive capacity. For a work center:

Required load may be calculated as:

Available capacity should account for the calendar, planned downtime, breaks, maintenance, and an appropriate efficiency factor. If a work center has 480 scheduled minutes, 60 minutes of planned downtime, and 360 minutes of required load, utilization against net available time is 360 ÷ 420 = 85.7%.

Utilization is not identical to productivity or effectiveness. A work center can show high utilization while producing defects, excessive waiting, or low-value output. Capacity models should therefore track throughput, queue time, changeovers, downtime, yield, and schedule adherence where relevant.

A constraint is a resource or policy limitation that restricts achievable flow. Capacity may be constrained by labor, equipment, materials, supplier availability, storage, transportation, cash, or regulatory limits. The bottleneck is usually the resource with the most consequential shortage, not necessarily the resource with the highest percentage utilization in every period. Managers should examine time-phased demand and dependencies before declaring a bottleneck.

8. Exception Management

A tracking model becomes operationally useful when it converts measurements into prioritized exceptions. Example rules include:

A good exception rule specifies owner, severity, due date, evidence, and closure condition. Thresholds should be reviewed for false positives and false negatives. A rule that produces hundreds of alerts every day is not necessarily effective; it may simply transfer noise to planners.

Prioritization can combine impact and urgency. For example, a shortage of a high-value, long-lead-time item with a customer order due tomorrow should receive more attention than a low-value item with abundant substitutes. Any scoring formula should be documented and should not obscure professional judgment.

9. Integrated Spreadsheet Design

A practical workbook might contain the following sheets:

1. ReadMe and assumptions: purpose, definitions, units, owners, refresh date, and parameter sources.
2. Transactions: immutable or protected source records.
3. Item master: cost, supplier, lead time, unit of measure, shelf life, and classification attributes.
4. Demand model: period totals, forecast, error, and variability measures.
5. Inventory policy: safety stock, reorder point, EOQ, minimum and maximum quantities.
6. Aging and capacity: lot status, age buckets, work-center load, and available capacity.
7. Exceptions: filtered action queue with status and ownership.
8. Dashboard: concise indicators with drill-through to records.

Use named ranges or structured table references, validation lists for status fields, protected formula cells, and conditional formatting that reinforces rather than replaces numerical labels. Avoid hard-coded constants inside formulas. Store service factors, thresholds, and calendar assumptions in an assumptions table.

A model should be tested with known cases: zero demand, no lead time, a fully consumed lot, a capacity overload, and a missing transaction. Boundary tests are particularly important for ABC/XYZ cutoffs and age buckets. Independent recalculation of a sample of outputs is a basic control.

10. Illustrative Case: Northstar Components

This is a fictional illustrative case.

Background

Northstar Components supplies repair parts to regional maintenance contractors. Planners reported simultaneous stockouts of critical parts and rising obsolete inventory. The existing workbook contained separate demand, purchasing, and warehouse tabs with inconsistent item codes and no distinction between on-hand and inventory position.

Problem

Management needed to identify which items required urgent intervention, estimate appropriate replenishment parameters, and determine whether warehouse labor could support the proposed policy. Historical demand also included periods in which items were unavailable, making sales an incomplete measure of customer demand.

Analysis

The team first standardized item identifiers and reconciled opening inventory, receipts, issues, and adjustments. Items were classified using annual consumption value and demand CV. Criticality was added as a separate attribute so that a low-value safety-critical component would not automatically receive a low service priority.

For one item, average daily demand was 100 units, standard deviation was 20 units, and lead time was four days. With a 95% target, safety stock was approximately 66 units and the reorder point was 466 units. On-hand inventory was 300 units, on-order inventory was 250 units, and backorders were 40 units. Thus:

Although on-hand inventory was below the reorder point, inventory position exceeded it. The model therefore flagged the item for monitoring rather than automatically placing another order. This distinction prevented duplicate replenishment.

The aging analysis found that 18% of inventory value was more than 180 days old, concentrated in low-demand Z items. The capacity model showed that receiving labor would be overloaded if all overdue purchase orders arrived in the same week. The constraint was therefore not only inventory quantity but also receiving capacity.

Approach and Solution

Northstar introduced a protected transaction table, a controlled item master, and an exception queue. Criticality, shelf life, supplier reliability, and substitute availability were included alongside ABC/XYZ results. Purchase orders were time-phased against receiving capacity. Old stock was reviewed for transfer, return, rework, or disposal before new purchases were approved.

Outcome

The organization reduced duplicate orders, improved visibility of aged stock, and focused planner attention on a smaller number of material exceptions. The model did not eliminate uncertainty; instead, it made uncertainty visible and assigned actions to responsible owners.

Learning Points

1. Inventory position is more decision-relevant than on-hand inventory alone.
2. ABC/XYZ classification should be supplemented by criticality and shelf-life information.
3. A replenishment decision can create a capacity problem elsewhere in the chain.
4. Data reconciliation is a prerequisite for credible analytics.
5. Spreadsheet outputs require governance, not just formulas.

11. Limitations, Governance, and Contemporary Practice

Spreadsheet models are vulnerable to broken links, overwritten formulas, hidden rows, uncontrolled copies, stale data, and ambiguous definitions. A sophisticated formula cannot compensate for inaccurate transactions or an inappropriate policy assumption. Critical models should have an owner, documented change history, locked calculation areas, regular backup, access controls, and periodic independent review.

Ethically, inventory and capacity reports can affect customer allocation, employee workload, supplier evaluation, and disposal decisions. Classification rules should not conceal unfavorable performance or systematically disadvantage particular customers without a defensible policy. Data containing customer, supplier, or employee information should be protected according to organizational and legal requirements.

Modern planning systems increasingly combine spreadsheets with enterprise-resource-planning data, business-intelligence dashboards, and machine-learning forecasts. AI can help detect anomalies, suggest parameters, or classify patterns, but it should not be treated as an unquestionable source of truth. Users should retain lineage from recommendation to source data, monitor model drift, test for bias, and require human approval for consequential actions. A transparent baseline spreadsheet is often valuable even when advanced tools are introduced because it provides an independently understandable benchmark.

Chapter Summary

Demand, inventory, and capacity tracking models translate operational data into replenishment and resource decisions. Demand analysis must distinguish stable, trending, seasonal, intermittent, and censored demand. ABC analysis prioritizes annual consumption value, while XYZ analysis evaluates relative demand variability; neither should be used without considering criticality and business context.

Safety stock protects against uncertainty, and reorder points combine expected lead-time demand with that protection. EOQ provides a cost-balancing benchmark under restrictive assumptions. Stock-aging models reveal slow, obsolete, quarantined, or expiring inventory. Capacity utilization compares required load with net available capacity, but bottleneck analysis must also consider flow, yield, queues, and dependencies. Finally, exception management makes the model actionable by assigning thresholds, owners, priorities, and closure conditions. Spreadsheet quality depends as much on governance, reconciliation, and transparent assumptions as on arithmetic accuracy.

Key Terms

Demand history: Recorded quantity requested, sold, issued, or otherwise consumed during a period.

Censored demand: Demand that cannot be fully observed because inventory or another constraint prevented fulfillment.

Moving average: A forecast calculated from the average of a specified number of recent observations.

Exponential smoothing: A forecasting method that weights recent observations more heavily through a smoothing constant.

Forecast error: The difference between actual demand and forecast demand.

ABC analysis: Classification based on cumulative annual consumption value.

XYZ analysis: Classification based on demand predictability or variability.

Coefficient of variation: Standard deviation divided by the mean.

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

Reorder point: Inventory-position threshold that triggers replenishment.

Inventory position: On-hand plus on-order inventory minus backorders.

EOQ: Economic order quantity that balances ordering and holding costs under stated assumptions.

Stock aging: Analysis of inventory by elapsed time since receipt, production, or movement.

FEFO: First-expiry-first-out inventory issuing policy.

Capacity utilization: Required load expressed as a percentage of available capacity.

Constraint: A limitation that restricts achievable supply-chain flow or output.

Exception management: Identification, prioritization, assignment, and resolution of material deviations.

Inventory reconciliation: Comparison of recorded inventory movements with the resulting balance.

Model governance: Controls governing ownership, assumptions, access, changes, validation, and use of a model.

Review Questions

Short Answer Questions

1. Why must a spreadsheet declare its time grain and units?
2. What is the difference between on-hand inventory and inventory position?
3. Why can MAPE be unsuitable for intermittent demand?
4. What does the coefficient of variation measure in XYZ analysis?
5. Name two causes of aged inventory.

Long Answer Questions

1. Explain how ABC and XYZ classifications can be combined to support differentiated inventory policies. Include limitations of the method.
2. Derive the logic of the reorder-point model and discuss the assumptions behind a normal-demand safety-stock calculation.
3. Compare EOQ with real-world replenishment decisions involving minimum order quantities, shelf life, and transportation constraints.
4. Design a spreadsheet architecture for integrated demand, inventory, and capacity tracking. Explain the role of controls.
5. Discuss why high capacity utilization does not necessarily indicate effective operations.

Analytical / Application Questions

1. Daily demand averages 80 units with a standard deviation of 15 units. Lead time is fixed at 9 days and the desired one-sided service probability is 95%, using z = 1.645. Calculate safety stock and reorder point.
2. An item has annual demand of 24,000 units, ordering cost of $50, and annual holding cost of $4 per unit. Calculate EOQ and explain one reason the recommended order quantity might differ in practice.
3. A warehouse has 600 minutes of net available labor capacity. Scheduled work requires 540 minutes, but 90 minutes of that work is expected to be reworked. Calculate the initial utilization and discuss whether the model should report the rework separately.

Multiple Choice Questions with Answers

Question 1

Which quantity should normally be compared with a reorder point?

A. On-hand inventory only

B. Inventory position

C. Annual demand

D. Unit cost

Correct Answer: B. Inventory position

Question 2

If average daily demand is 100 units and fixed lead time is 4 days, what is expected lead-time demand?

A. 25 units

B. 104 units

C. 400 units

D. 4,000 units

Correct Answer: C. 400 units

Question 3

What does ABC analysis primarily classify?

A. Supplier reliability

B. Demand variability

C. Annual consumption value

D. Warehouse age

Correct Answer: C. Annual consumption value

Question 4

A coefficient of variation is calculated as:

A. Mean divided by standard deviation

B. Standard deviation divided by mean

C. Annual demand divided by cost

D. Forecast divided by actual demand

Correct Answer: B. Standard deviation divided by mean

Question 5

Under the classical EOQ assumptions, increasing annual holding cost generally causes EOQ to:

A. Increase

B. Decrease

C. Remain exactly unchanged

D. Become equal to reorder point

Correct Answer: B. Decrease

Question 6

A work center requires 360 minutes and has 420 minutes of net available capacity. Its utilization is approximately:

A. 65.7%

B. 75.0%

C. 85.7%

D. 116.7%

Correct Answer: C. 85.7%

Question 7

Which demand situation can make MAPE problematic?

A. Constant positive demand

B. Demand with no zero values

C. Demand containing zero or near-zero actual values

D. Demand measured in consistent units

Correct Answer: C. Demand containing zero or near-zero actual values

Question 8

For dated products, which issuing policy prioritizes the earliest expiry?

A. LIFO

B. FEFO

C. Random issue

D. Highest-cost-first

Correct Answer: B. FEFO

Question 9

If on-hand inventory is 300 units, on-order inventory is 250 units, and backorders are 40 units, inventory position is:

A. 90 units

B. 510 units

C. 550 units

D. 590 units

Correct Answer: B. 510 units

Question 10

Which is the strongest reason to protect and document spreadsheet formulas?

A. To make the workbook visually colorful

B. To prevent traceability and review

C. To support auditability and reduce uncontrolled changes

D. To eliminate the need for source data

Correct Answer: C. To support auditability and reduce uncontrolled changes