Summary of ABC Analysis : Step-by-Step Tutorial in Excel with 500 products

Summary of "ABC Analysis: Step-by-Step Tutorial in Excel with 500 products"

Main Ideas and Concepts:

• Introduction to ABC Analysis:
  • ABC Analysis is a method based on the Pareto Principle (80/20 rule), which states that roughly 80% of results come from 20% of causes.
  • This principle applies broadly in business and life, such as sales, purchases, time management, and crime statistics.
  • In supply chain and inventory management, ABC Analysis helps prioritize products, customers, suppliers, projects, or problems based on their impact on revenue or volume.
• Purpose of ABC Analysis in Supply Chain:
  • Helps focus time, energy, and cash on the most impactful items (A items), while managing less critical ones (B and C items) accordingly.
  • Improves service levels, inventory management, and communication by concentrating on what matters most.
  • Avoids wasting resources on low-impact products or issues.
• ABC Classification Explained:
  • A items: ~5% of products generating ~40% of volume/revenue (highest priority).
  • B items: Combined with A, make up ~20% of products generating ~80% of volume/revenue.
  • C items: The remaining products with minimal impact on revenue or volume.
  • Different service levels should be targeted for each category (e.g., 99.5% for A, 90% for B, 70-80% for C).
• Example for Better Understanding:
  • Fashion industry example with black shirts (A), orange shirts (B), and fancy shirts (C).
  • Prioritize stocking and service levels according to product category importance.

---

Methodology: Step-by-Step ABC Analysis in Excel

1. Prepare Data:
   • Collect complete product data including active, discontinued, and new products.
   • Use both sales history and sales forecast for a comprehensive view.
   • Decide on valuation metric: recommend using value (revenue or cost) rather than quantity to reflect business priorities.
2. Select Time Period:
   • Typically use the last 12 months of data.
   • For seasonal businesses, use relevant shorter periods (e.g., per fashion collection).
3. Combine Sales History and Forecast:
   • Mix recent sales history with forecast data to include new products.
   • Example: 3 months sales history + 9 months forecast.
4. Create a Table in Excel:
   • Convert raw data into an Excel Table for dynamic range management.
5. Create a Pivot Table:
   • Insert a Pivot Table based on the table.
   • Add fields: item codes, names, and 12-month sales value.
   • Sort items descending by sales value.
6. Rank Products:
   • Use Excel’s RANK function to rank products by sales value.
   • Extend the rank formula for all products.
7. Calculate Totals:
   • Calculate total revenue and total number of items using SUM and COUNT functions.
8. Calculate Percentages:
   • Calculate percentage of items (rank/total items).
   • Calculate percentage of revenue per item.
   • Calculate cumulative revenue percentage to determine ABC thresholds.
9. Classify ABC Categories:
   • Use IF formulas based on cumulative revenue percentage:
     • If ≤ 40%, classify as A.
     • If > 40% and ≤ 80%, classify as B.
     • Else classify as C.
   • Adjust formulas to handle blanks or missing data.
10. Count Items per Category:
    • Use COUNTIF to tally the number of A, B, and C items.
11. Visualize Results:
    • Create charts to visualize distribution of items and revenue.
    • Use conditional formatting to color-code ABC categories for clarity.
12. Maintain and Update:
    • To update, simply paste new sales data and refresh pivot tables.
    • The classification will automatically update based on new data.
    • Avoid modifying the file structure; only update raw data.

---

Additional Insights:

ABC Analysis is foundational but not sufficient alone.

Recommended to integrate with XYZ Analysis to account for sales variability and uncertainty.

ABC helps define safety stock levels and risk management strategies per product.

Embedding ABC Analysis into dashboards and ERP systems automates focus on critical products/customers/suppliers.

---

Speaker / Source:

• Eduard — Supply chain professional with 15 years of experience, founder of ABC Supply Chain, and the sole speaker/presenter in the video.

Notable Quotes

— 00:35 — « 80 percent of your results come from 20 percent of your causes. »

— 07:28 — « I always recommend to focus on value because value is quantity times price. You can use sales price if you're a retail company or FMCG, and cost price if you work for an industry. »

— 08:34 — « Most of the companies on the planet don't have any forecast per item today, so it's okay if you don't have one. »

— 10:30 — « The pivot table is really important if you want to automate this process and don't do it every week or every month in a manual way. »

— 20:53 — « When we look at the behavior of sales, one product is very stable and predictable and the other one is not stable at all. You definitely don't need the same safety stock for both. »

Category

Educational

Video