FSN analysis in Excel: classify inventory by movement

FSN analysis in Excel classifies stock by observed movement, using thresholds you define before inspecting the result. Count movement for each SKU and site, apply the same observation window, and reconcile the resulting stock values to a dated inventory snapshot. Keep movement frequency separate from receipt age.

Set the policy and assemble the exports

This US distributor reviews inventory at September 30, 2026. The observation window runs from July 1 through September 30, or 92 calendar days. Its illustrative policy is fast for at least two qualifying issue days, slow for one, and nonmoving for none. These are workbook demonstration thresholds, not recommended industry standards.

A qualifying day has positive issues to customers or production. Exclude transfers, cycle-count adjustments and receipts. Keep returns identifiable rather than allowing a return to erase evidence of a shipment. State whether the business needs a gross-issue or net-demand measure; the example uses gross issue activity.

Use a stock snapshot with site, SKU, on-hand quantity and approved unit cost. Add issue transactions with site, SKU, posting date, movement type, quantity and a unique document-line identifier. Verify the export covers the whole window. Missing transaction coverage is an exception, not evidence of no movement.

Frequency and value answer different questions. Inventory turnover relates cost of sales to inventory investment; do not substitute that ratio for this movement classification.

Reduce issues to one row per active day

Make a working sheet containing only approved movement types and positive issue quantities. Put Site, SKU, PostingDate and IssueQty in A:D, starting in row 2. Add IssueDay in E, FirstDay in F and DailyQty in G:

=INT(C2)
=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$E$2:E2,E2)=1
=SUMIFS($D$2:$D$9999,$A$2:$A$9999,A2,$B$2:$B$9999,B2,$E$2:$E$9999,E2)

Fill down through the working rows; extend all fixed ranges together for larger files. FirstDay is TRUE only on the first occurrence of each SKU-site-date combination. Copy only those TRUE rows' Site, SKU, IssueDay and DailyQty as values into a table named DailyIssues; name its quantity column IssueQty. Each combination now occurs once, even when several lines shipped that day. SUMIFS and COUNTIFS are documented below.

The complete daily issue input for this example is:

The total is 56 issued units. Preserve a check between the daily total and the filtered transaction total. Counting the original transaction lines would measure transaction frequency and could change classifications when a shipment has multiple lines.

Calculate the SKU-site classes

Create a table named Stock from the snapshot, one row per Site and SKU. Put the observation start in P1 as =DATE(2026,7,1), the exclusive end in P2 as =DATE(2026,10,1), and the fast threshold in P3 as numeric 2. Add IssueDays, FSN and StockValue columns:

=COUNTIFS(DailyIssues[Site],[@Site],DailyIssues[SKU],[@SKU],DailyIssues[IssueDay],">="&$P$1,DailyIssues[IssueDay],"<"&$P$2,DailyIssues[IssueQty],">0")
=IF([@IssueDays]>=$P$3,"F",IF([@IssueDays]>0,"S","N"))
=[@OnHand]*[@UnitCost]

Microsoft documents COUNTIFS as counting records that meet multiple criteria. Here the prior daily aggregation makes the count an issue-day count. The same SKU at different sites remains separate.

North A's three days and South A's two days both meet the fast threshold. The six issue days across SKU-site combinations tie to the six daily input rows. Do not interpret six as six distinct company-wide calendar days unless that is separately checked.

Summarize value and prove the totals

Build a Summary table with Site, FSN and Value. For each site-class combination use:

=SUMIFS(Stock[StockValue],Stock[Site],[@Site],Stock[FSN],[@FSN])

Microsoft documents SUMIFS as adding values meeting multiple criteria. Covirage is not affiliated with Microsoft.

Fast stock is 1,600 / 3,200 = 50.0% of value; slow stock is 12.5%; nonmoving stock is 37.5%. Percentages are rounded to one decimal place. The classes sum to $3,200, matching the snapshot, and both site totals reconcile independently.

North C was first received September 29. It remains N under the stated observed-movement rule but needs a new-item exception flag before review. South D requires its own demand and availability review. Neither status establishes obsolescence. Pair this result with inventory aging by site, which answers how long inventory has been held. For changes between snapshots, use a checked waterfall bridge.

Where it goes wrong

• Counting lines as days. Deduplicate the SKU-site-date grain before measuring frequency.
• Ignoring site. Movement elsewhere can conceal a local nonmoving balance.
• Changing the policy silently. A different window or threshold changes the classification without any new demand.
• Treating recent receipts as old excess. Review introduction dates, seasonality and critical spares before taking action.

Review your inventory files in Covirage

Bring the inventory snapshot and issue file with the stated window and movement policy; Covirage maps the columns, and its tools compute and check stock-value and movement comparisons. The external AI model explains the results 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 operating context.