Inventory analysis: methods and business decisions

Inventory analysis turns stock, demand and movement exports into evidence for a specific business decision. Reconcile the stock first, then choose a measure that answers the question: capital tied up, inactive goods, expensive units, demand uncertainty or customer service. A single inventory score cannot tell a buyer which problem to fix.

Match the question to a method

Start the review with a decision and an owner. Lower stock can release capital but also leave customer requests unfilled. More stock can protect service while accumulating goods that no longer have supported demand. Evaluate those outcomes together.

For detailed calculations, use the owners of inventory turnover, days inventory outstanding and inventory aging by site. This review selects and reconciles the views before someone acts on them.

Build a consistent stock and movement table

Choose a period, a valuation basis and a boundary: one company, site or product group. Keep item IDs, location, unit of measure, ownership and stock status. Goods held for another owner need separate treatment from goods the company owns.

A snapshot is a balance at a date; receipts and shipments are movements during a period. The Census Bureau's manufacturing definitions similarly distinguish end-of-month inventory from shipments during the reporting period and identify raw materials, work in process and finished goods separately. Use the company's own approved accounting basis when reconciling its ledger.

An illustrative US distributor reviews September 2026. It has no returns, transfers or adjustments in this simplified example, and each item's unit cost is unchanged. Columns A:G are SKU, opening units, receipts, shipped units, closing units, unit cost and closing value. Rows 2:4 hold the items.

Calculate the expected closing quantity in a check column, then subtract the reported closing quantity:

=B2+C2-D2
=(B2+C2-D2)-E2

Every row's difference must be zero. Closing stock value in G2 is:

=E2*F2

Fill down and sum G2:G4. The $13,000 total must tie to the inventory valuation report for the same date and scope. Aggregate unit counts help check completeness here, but unlike products do not become interchangeable because each is counted in units.

Prove value before interpreting turnover

Use the first table to derive opening value, receipt cost and cost of goods sold. This simple distributor has no additional cost adjustments:

Opening value is opening units × unit cost; receipt cost and COGS use their corresponding units. Average stock value here is the mean of opening and closing values. The Census manufacturing and trade definitions describe the basic merchandise cost reconciliation as beginning inventory plus purchases less ending inventory to obtain COGS.

The value check is $14,000 + $11,000 − $12,000 = $13,000. In a real export, incorporate transfers across the chosen boundary, returns and approved adjustments as separate movements rather than hiding them in receipts.

Using the first table's columns A:G, calculate monthly turnover in Excel with SUMPRODUCT, which Microsoft documents as summing the products of corresponding ranges:

=SUMPRODUCT(D2:D4,F2:F4)/((SUMPRODUCT(B2:B4,F2:F4)+SUM(G2:G4))/2)

It is 12,000 / 13,500 = 0.89 turns for September, rounded to two decimals. It is not an annual turnover figure. If stock fluctuates substantially within the month, use consistently timed daily or weekly balances for the average instead of assuming two endpoints represent the whole period.

Put aging beside service

Next, identify closing units with no movement for more than 180 days, using the company's approved movement definition. Separately match customer requests due during September to quantities delivered on time. Transfers and count adjustments should not restart an inactivity clock unless the policy explicitly treats them as qualifying movements.

Inactive value is inactive units × the unit cost in the first table. Its share is 4,100 / 13,000 = 31.54%, rounded to two decimals. This is a review population, not an automatic write-off.

For a separate worksheet with these columns in A:E, calculate the defined on-time unit fulfillment rate as:

=SUM(E2:E4)/SUM(D2:D4)

The result is 430 / 470 = 91.49%. This is a quantity measure, not the percentage of complete orders. Requests due in September can differ from units shipped in September because early or late fulfillment crosses the period boundary; reconcile those timings in the underlying order lines.

SKU C combines $3,500 of inactive stock with only 10 / 20 = 50.00% on-time fulfillment. Investigate lot condition, location, reservation and customer specifications before concluding the company merely needs less stock. The aggregate balance alone cannot explain that combination.

Add classification or demand analysis only when needed

Use ABC analysis to understand the cumulative-value ranking method, then define annual inventory usage consistently for the stock application. Use HML inventory analysis when the question concerns unit-cost bands rather than annual consumption value.

For a relationship between demand and planning inputs, regression analysis for inventory management adds chronological validation and baseline comparisons. It does not replace this stock reconciliation or establish the cause of a shortage.

End the review with an exception, an owner and evidence required. Warehouse operations can verify SKU C's usable lots; planning can check future demand; finance can confirm the cost and any accounting treatment. Record the next review against the same definitions.

Where it goes wrong

• Snapshots are added across dates. Summing month-end stock reports counts the same goods repeatedly. Select a date or calculate a defined average.
• Sales dollars meet inventory at cost. Keep numerator and denominator on a consistent basis when evaluating turnover.
• Old stock is labeled obsolete. Investigate supported future use, condition and replacement needs before recommending disposition.
• Service definitions drift. Unit fulfillment, complete orders and on-time delivery answer different questions; label the denominator.
• Company totals hide site exceptions. Reconcile and review SKU-location detail before a consolidated measure informs purchasing.

Covirage is not affiliated with Microsoft.

Analyze your own inventory file in Covirage

Bring dated stock balances, movement detail, unit costs and the demand or service export behind your question. Covirage maps the columns, and its tools compute and check the selected views; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for supply-chain teams for the supporting workflow.