How to Do ABC Analysis of Inventory in Excel

Knowing which inventory items to focus on can feel like a guessing game, but it doesn't have to be. ABC analysis is a straightforward method to classify your products based on their value, helping you manage stock more intelligently. This guide will walk you through step-by-step on how to perform an ABC analysis on your own inventory data using Microsoft Excel.

What is ABC Analysis for Inventory?

ABC analysis is a way to sort your inventory into three categories - A, B, and C - based on their impact on your overall revenue. It applies the Pareto Principle, also known as the 80/20 rule, which suggests that about 80% of your revenue comes from just 20% of your products. By identifying that critical 20%, you can manage your inventory more efficiently, reduce carrying costs, and avoid stockouts on your most important items.

Here’s how the categories typically break down:

• Category A: These are your most valuable products. They represent a small fraction of your total inventory items (around 10-20%) but account for the largest share of an item's value (around 70-80%). These are your star players - the items you can't afford to run out of. They require your closest attention, frequent reordering, and careful sales forecasting.

• Category B: These products fall in the middle. They make up a larger portion of your inventory items (around 30%) but represent a smaller slice of your overall value (around 15-25%). While important, they don't have the same dollar-for-dollar impact as your Category A items. Management here can be more relaxed.

• Category C: These are your low-value, high-quantity items. They typically account for the majority of the items you sell (maybe 50% or more) but represent a tiny fraction of your overall value (less than 5%). Managing these items should be as simple as possible, with minimal time spent on tracking and forecasting.

The core benefit of this system is focus. Instead of treating every product the same, you can dedicate your time, energy, and capital to the items that actually drive your business forward. This leads to smarter purchasing decisions, optimized warehouse space, and healthier cash flow.

Getting Your Data Ready for Analysis

Before jumping into Excel, you need to gather the right information. The good news is, you only need three key data points for each of your products or SKUs. You can typically export this information from your e-commerce platform (like Shopify, BigCommerce), accounting software, or ERP system.

Here's what you'll need to pull together in an Excel or Google Sheet:

1. Product Identifier: The name of the product or its SKU (Stock Keeping Unit).

2. Unit Cost: How much you pay for a single unit of that product.

3. Annual Demand: The total number of units sold over a specific period, usually a year. If you don't have a full year's data, use the longest time frame available (like a quarter or six months) for a meaningful analysis.

Your raw data, once gathered, should look something like this in a spreadsheet:

Step-by-Step Guide: Performing ABC Analysis in Excel

With your data organized, you're ready to start the analysis. We'll add a few new columns to calculate each item’s value and assign it to its proper category.

Step 1: Calculate the Annual Consumption Value for Each Product

The "Annual Consumption Value" (or just "Annual Value") tells you the total monetary value each product contributed over the year. It's the key metric we'll use to rank your products.

Create a new column titled "Annual Value" (let's say it's column D) and multiply the Annual Demand by the Unit Cost for each product.

The formula for cell D2 would be:

Click on the bottom-right corner of cell D2 (the fill handle) and drag it down to apply the formula to all of your products.

Step 2: Calculate the Total Annual Value

Next, you need to sum the "Annual Value" for all your products. This total will be used to determine what percentage each individual product contributes.

Scroll to the bottom of your "Annual Value" column (Column D) and use the SUM formula to get the total. If you have products listed down to row 21, the formula would be:

Take note of this total value, as you'll use it in the next steps.

Step 3: Sort Your Products by Annual Value

Now, you need to reorder your product list from most valuable to least valuable. This makes it much easier to calculate the cumulative values later on.

• Select your entire data set, including all the header columns.

• Go to the Data tab in Excel's main ribbon.

• Click on the Sort button.

• In the Sort dialog box, set "Sort by" to "Annual Value" and "Order" to "Largest to Smallest."

• Click OK. Your list should now be sorted with your highest-value products at the top.

Step 4: Calculate the Percentage of Total Value

This column will show each product’s share of the total annual consumption value.

Create a new column named "% of Total Value" (Column E). To calculate this, divide each product's individual annual value by the total sum you computed in Step 2. Use an absolute cell reference (with dollar signs, e.g., $D$22) for the total to ensure that it doesn't change when you drag the formula down.

If your total annual value is in cell D22, the formula for cell E2 would be:

Drag this formula down for every product. To make it easier to read, format this column as a percentage by selecting it and clicking the % button on the Home tab.

Step 5: Calculate the Cumulative Percentage

The cumulative percentage is the running total that will help us group the items into categories A, B, and C. It shows the combined value percentage as you move down your sorted list of products.

Create a new column called "Cumulative %" (Column F). The formula here is a little different:

• For the first product (cell F2), the cumulative percentage is simply its own percentage from Column E. The formula is:

• For the second product (cell F3) and every one after, you'll add its percentage to the cumulative total from the row above:

Drag the formula from cell F3 down to the bottom of your list. The last cell in this column should equal 100%.

Step 6: Assign ABC Categories

You're at the final step! Based on the cumulative percentage, you can now classify each product.

Create one last column named "ABC Category". Here's a common set of thresholds you can use:

• Category A: Cumulative percentage is less than or equal to 80%.

• Category B: Cumulative percentage is between 80% and 95%.

• Category C: Cumulative percentage is between 95% and 100%.

You can use a nested IF formula in Excel to automatically assign these categories. In cell G2, type the following formula:

Here's a breakdown of that formula:

• IF(F2<=0.8, "A", ... ): If the cumulative value is 80% or less, label it "A".

• IF(F2<=0.95, "B", "C"): Otherwise, if it's 95% or less, label it "B". For everything else, label it "C".

Drag this formula all the way down. You've officially completed your ABC Analysis!

How to Use Your ABC Analysis Results

Finishing the analysis is just the beginning. The real value comes from using these categories to change how you manage your inventory.

Managing Category A Items

These are your cash cows. Protect them at all costs. Stockouts here directly impact revenue and customer trust. You should have very tight inventory control, conduct frequent cycle counts, and invest in more accurate forecasting. Demand-pull policies like just-in-time (JIT) are often a good fit here, and you should always maintain safety stock to prevent shortages.

Managing Category B Items

These items require a more moderate level of control. They are important, but you don't need to watch them as closely as Category A items. Review their stock levels monthly or quarterly instead of weekly. You might order these items less frequently but in slightly larger quantities to minimize administrative overhead.

Managing Category C Items

Your goal with C items is to spend as little time as possible managing them. Because they contribute so little to your revenue, the cost of closely monitoring them far outweighs the benefit. You can order these in very large quantities (bulk purchasing) infrequently to save on shipping costs and management time. A simple "two-bin" system can work well here - when the first bin is empty, you place an order.

Final Thoughts

By investing a bit of time in an ABC analysis in Excel, you gain a powerful framework for prioritizing your inventory management efforts. This simple classification helps you focus on what really matters, enabling you to reduce carrying costs and improve cash flow without getting lost in the weeds.

While Excel is great for one-off analyses, creating and updating these reports manually can become tedious. With Graphed, we connect directly to your data sources like Shopify, Salesforce, and Google Analytics so you don't have to deal with endless CSV exports. You can simply ask plain-English questions like "Show me my top products by revenue this year" and have a live, auto-updating dashboard built in seconds, saving you from the busywork of manual reporting.