How to Perform ABC Inventory Analysis in Excel

1. Prepare the source and scope

Import operations.csv into a new worksheet with headers in row 1. Leave the source columns in their original order for the formulas below. Review the data dictionary before choosing the reporting period. Keep identifiers as text and convert numeric columns explicitly. Save a working copy so you can return to the original fixture.

2. Build the calculation

Sort SKUs by annual_cogs descending. Calculate each share of total COGS and a cumulative share. Assign A/B/C classes using documented cutoffs after reviewing the distribution.

=B2/SUM($B$2:$B$7)

3. Reconcile and interpret the output

The expected check is sku a share of annual cogs: 48.9796 percent. SKU A contributes nearly half of annual COGS. The example uses cost consumption as the value measure. If your result differs, inspect the selected rows, data types and date filters before changing the formula. Percentage formulas return decimal ratios; apply Percentage formatting rather than multiplying the cell value twice.

4. Adapt the workflow to a recurring report

Replace the sample with a copy of your own source, retaining the same column meanings and units. Extend bounded ranges to include new rows, refresh PivotTables where used, and compare the result to an independent source total. Record the reporting period and any exclusions beside the output. Do not silently replace cost consumption with revenue or apply universal class thresholds.