كيفية إجراء تحليل ABC في Excel: 5 خطوات سهلة
يصنف تحليل ABC عناصر المخزون إلى ثلاث فئات بناءً على قيمة استخدامها السنوي. عناصر الفئة A هي العناصر القليلة التي تستحوذ على معظم الأموال. وعناصر الفئة C هي العناصر الكثيرة التي تستحوذ على القليل منها، بينما تقع الفئة B بينهما. في Excel، يمكنك القيام بذلك باستخدام جدول واحد: القيمة السنوية، والحصة من الإجمالي، والإجمالي التراكمي، وصيغة تحدد كل فئة.
يشرح هذا الدليل معنى هذه الفئات، وخطوات Excel الخمس، ومثالاً عملياً، وكيفية رسم النتائج بيانياً. كما يغطي أيضاً كيفية اختيار نقاط التقسيم الخاصة بك وما يجب فعله بكل فئة بمجرد الانتهاء من التحليل.
ما هو تحليل ABC
تحليل ABC هو وسيلة لتحديد العناصر التي تستحق أكبر قدر من الاهتمام. وهو يعتمد على نمط بسيط: حصة صغيرة من العناصر تستحوذ على حصة كبيرة من الإنفاق.
يصف فصل صدر عام 2012 حول تحليل ومراقبة النفقات الدوائية، من منظمة علوم الإدارة من أجل الصحة (MSH)، هذا الأمر بوضوح. ويشير إلى أن "عدداً صغيراً نسبياً من العناصر يستحوذ على معظم قيمة الاستهلاك السنوي". ويضيف: "يُعرف تحليل هذه الظاهرة باسم تحليل Pareto أو، الأكثر شيوعاً، تحليل ABC".
يوضح الفصل نفسه أنه "يمكن تصنيف العناصر إلى ثلاث فئات (A و B و C) بناءً على قيمة استخدامها السنوي". الطريقة هي نفسها سواء كنت تقوم بتخزين الأدوية، أو قطع الغيار، أو منتجات التجزئة.
هناك نقطة يسهل إغفالها. الفئات ليست تصنيفات دائمة. تشير MSH إلى أنه "إذا تغيرت أنماط الاستخدام، فقد يقع العنصر في فئة مختلفة في المرة القادمة التي يتم فيها إجراء تحليل ABC". لذلك، يعمل تحليل ABC بشكل أفضل كفحص روتيني، وليس كمشروع يُنفذ لمرة واحدة فقط.
ماذا تعني الفئات A و B و C
يقدم فصل MSH نطاقات نموذجية لكل فئة:
| الفئة | حصة العناصر | حصة القيمة السنوية | ماذا تعني عادةً |
|---|---|---|---|
| A | 10 إلى 20 بالمئة | 75 إلى 80 بالمئة | عناصر قليلة، معظم الأموال |
| B | 10 إلى 20 بالمئة | 15 إلى 20 بالمئة | مجموعة متوسطة |
| C | 60 إلى 80 بالمئة | 5 إلى 10 بالمئة | عناصر كثيرة، القليل من الأموال |
هذه نطاقات نموذجية وليست قواعد. تقول MSH "هذه الحدود مرنة إلى حد ما". ويحدد مثالها الفئة A عند العناصر التي تشكل 70 بالمئة من الأموال بدلاً من ذلك.
القيمة التي تحدد الفئات هي قيمة الاستهلاك السنوي: الوحدات المستخدمة في السنة مضروبة في تكلفة الوحدة. يمكن أن يقع عنصر رخيص يُستخدم بكميات ضخمة في الفئة A. بينما يمكن أن يقع عنصر باهظ الثمن يُستخدم مرة واحدة في السنة في الفئة C.
تشكك مقالة نُشرت عام 2014 في American Journal of Business Education في استخدام القيمة وحدها. وتجادل بأن الكتب المدرسية "تركز على حجم الدولار كمعيار وحيد" وتوصي بإضافة معايير أخرى. كخطوة أولى، القيمة هي الطريقة التي يستخدمها فصل MSH.
ما تحتاجه قبل البدء
يتطلب تحليل ABC في Excel بضعة أعمدة فقط لكل عنصر:
- اسم العنصر أو SKU. صف واحد لكل عنصر.
- الوحدات السنوية المستخدمة أو المشتراة. استخدم نفس فترة الـ 12 شهراً لكل عنصر.
- تكلفة الوحدة. تكلفة الوحدة الواحدة، بنفس الوحدة التي تعد بها.
تؤكد MSH على مطابقة الفترة: "تأكد من استخدام نفس فترة المراجعة لجميع العناصر لتجنب المقارنات غير الصالحة". وتنصح أيضاً باستخدام نفس الوحدة الأساسية للتكلفة والكمية، مثل قرص دواء أو صندوق واحد، بدلاً من خلط أحجام العبوات.
إذا كانت بياناتك تأتي من نظام مخزون أو مشتريات، فقم بتصديرها كملف CSV أو Excel. قم بإزالة العناصر التي ليس لها أي نشاط في تلك الفترة، أو احتفظ بها وتوقع أن تقع في الفئة C.
كيفية إجراء تحليل ABC في Excel
تتبع الخطوات الخمس أدناه الطريقة الموضحة في فصل MSH، والمكيفة مع صيغ Excel. يضع المثال عنواناً في الصف 1، ورؤوس الأعمدة في الصف 2، و 10 عناصر في الصفوف من 3 إلى 12. وتحتوي الأعمدة A و B و C على اسم العنصر، والوحدات السنوية، وتكلفة الوحدة.
الخطوة 1: سرد العناصر والوحدات وتكلفة الوحدة
أدخل أو الصق صفاً واحداً لكل عنصر مع اسمه، ووحداته السنوية، وتكلفة وحدته. أضف رؤوس الأعمدة في الصف 2 بحيث يسهل فرز الجدول لاحقاً.
تحقق من البيانات قبل المتابعة. ابحث عن التكاليف الفارغة، والكميات السالبة، ورموز SKU المكررة، لأن كل منها سيشوه الإجماليات. عادةً ما يعثر عليها عامل تصفية سريع في كل عمود.
إذا تم إجراء عمليات شراء متعددة لنفس العنصر بأسعار مختلفة، فاستخدم تكلفة واحدة متسقة. تشير MSH إلى أن "المتوسط المرجح أو متوسط FIFO" هما البديلان الأكثر دقة عندما يصعب تتبع تكلفة الوحدة الفعلية.
الخطوة 2: حساب القيمة السنوية وحصتها من الإجمالي
في العمود D، اضرب الوحدات في التكلفة للحصول على القيمة السنوية لكل عنصر. في الخلية D3، أدخل =B3*C3 واسحب الصيغة للأسفل لتطبيقها.
في العمود E، اقسم كل قيمة على إجمالي القيم للحصول على حصتها. في الخلية E3، أدخل =D3/SUM($D$3:$D$12) واسحب للأسفل. تحافظ علامات الدولار على ثبات نطاق الإجمالي أثناء نسخ الصيغة. قم بتنسيق العمود E كنسبة مئوية بخانتين عشريتين.
توصي MSH بهذه الدقة لسبب ما. وبعبارتها: "قد تكون عدة عناصر متقاربة في القيمة وقد يمثل العديد منها أقل من 1 بالمئة من القيمة الإجمالية".
الخطوة 3: فرز العناصر حسب القيمة، من الأكبر إلى الأصغر
حدد الجدول بأكمله، بما في ذلك رؤوس الأعمدة، وقم بالفرز حسب العمود D من الأكبر إلى الأصغر. في Excel، يتم ذلك عبر "البيانات" (Data)، ثم "فرز" (Sort)، مع تحديد العمود D وتعيين الترتيب من الأكبر إلى الأصغر (Largest to Smallest).
إذا كنت تفضل استخدام صيغة، فإن دالة SORT ترجع نسخة مفرزة. صيغة Microsoft هي =SORT(array,[sort_index],[sort_order],[by_col])، حيث يعني ترتيب الفرز -1 الترتيب التنازلي. بالنسبة لهذا الجدول، تقوم الصيغة =SORT(A3:E12,4,-1) بالفرز حسب العمود الرابع، القيمة الأعلى أولاً.
بعد هذه الخطوة، يستقر العنصر ذو القيمة السنوية الأعلى في الأعلى. هذا الترتيب هو ما يجعل الإجمالي التراكمي في الخطوة التالية ذا معنى.
الخطوة 4: إضافة النسبة المئوية التراكمية
في العمود F، أضف إجمالياً تراكمياً للحصص. في الخلية F3، أدخل =SUM($E$3:E3) واسحب للأسفل. يظل الجزء الأول من النطاق ثابتاً، بينما ينمو الجزء الثاني بمقدار صف واحد في كل مرة.
يجب أن يظهر الصف الأخير 100 بالمئة. إذا لم يكن الأمر كذلك، فتحقق من وجود خلايا فارغة أو قيم نصية في العمودين D و E.
هذا العمود هو جوهر تحليل ABC. فهو يوضح مقدار القيمة الإجمالية التي تمثلها العناصر الموجودة فوق كل صف معاً.
الخطوة 5: تعيين الفئات A و B و C
في العمود G، استخدم صيغة لتصنيف كل عنصر. مع نقاط تقسيم تبلغ 80 و 95 بالمئة، أدخل هذا في الخلية G3 واسحب للأسفل:
=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")
تتحقق دالة IFS من كل شرط بالترتيب وترجع أول تطابق. يستخدم مثال Microsoft نفسه نفس النمط، مع وجود TRUE كخيار نهائي شامل. تصبح العناصر التي تصل نسبتها التراكمية إلى 80 بالمئة من الفئة A، وتصبح العناصر التي تصل إلى 95 بالمئة من الفئة B، والباقي يصبح من الفئة C.
أخيراً، قم بعدّ عناصر كل فئة باستخدام =COUNTIF(G3:G12,"A") والشيء نفسه بالنسبة لـ B و C. قارن الأعداد بالنطاقات النموذجية أعلاه. اضبط نقاط التقسيم إذا كانت الفئة A كبيرة جداً أو صغيرة جداً بحيث لا يستطيع فريقك إدارتها.
مثال عملي
فيما يلي جدول توضيحي لـ 10 عناصر، تم فرزها بالفعل حسب القيمة السنوية. الأرقام هي مجرد أمثلة، وليست بيانات من شركة حقيقية.
| العنصر | الوحدات السنوية | تكلفة الوحدة | القيمة السنوية | الحصة | التراكمي | الفئة |
|---|---|---|---|---|---|---|
| SKU-01 | 1,200 | $45.00 | $54,000 | 36.00% | 36.00% | A |
| SKU-02 | 3,000 | $12.00 | $36,000 | 24.00% | 60.00% | A |
| SKU-03 | 500 | $40.00 | $20,000 | 13.33% | 73.33% | A |
| SKU-04 | 8,000 | $1.50 | $12,000 | 8.00% | 81.33% | B |
| SKU-05 | 2,000 | $4.00 | $8,000 | 5.33% | 86.67% | B |
| SKU-06 | 600 | $10.00 | $6,000 | 4.00% | 90.67% | B |
| SKU-07 | 1,000 | $5.00 | $5,000 | 3.33% | 94.00% | B |
| SKU-08 | 1,500 | $3.00 | $4,500 | 3.00% | 97.00% | C |
| SKU-09 | 700 | $5.00 | $3,500 | 2.33% | 99.33% | C |
| SKU-10 | 400 | $2.50 | $1,000 | 0.67% | 100.00% | C |
تبلغ القيمة السنوية الإجمالية $150,000. تشكل ثلاثة عناصر، أي 30 بالمئة من القائمة، 73.33 بالمئة من القيمة وتصنف في الفئة A. وتقع أربعة عناصر في الفئة B، بينما تقع العناصر الثلاثة الأخيرة، التي تمثل 6 بالمئة من القيمة، في الفئة C.
تبرز تفصيلتان هنا. يحتوي SKU-04 على أكبر عدد من الوحدات بفارق كبير، لكن تكلفته المنخفضة تضعه في الفئة B. ومع وجود 10 عناصر فقط، لن تتطابق حصص الفئات مع النطاقات النموذجية، وهو أمر طبيعي بالنسبة لقائمة قصيرة.
كيفية رسم النتائج بيانياً
يسهل الرسم البياني إظهار النمط في الاجتماع. تقترح MSH رسم النسبة المئوية التراكمية مقابل رقم العنصر، مما يعطي منحنى ABC المألوف.
يحتوي Excel على مخطط مدمج لهذا الغرض. تصف Microsoft مخطط Pareto بأنه مخطط "يحتوي على أعمدة مرتبة ترتيباً تنازلياً وخط يمثل النسبة المئوية الإجمالية التراكمية". لإنشاء مخطط، حدد أسماء العناصر والقيم السنوية، ثم اختر "إدراج" (Insert)، ثم "إدراج مخطط إحصائي" (Insert Statistic Chart)، ثم Pareto.
أضف خطين أفقيين أو تسميتين عند نقاط التقسيم الخاصة بك، مثل 80 و 95 بالمئة، حتى يتمكن المشاهدون من رؤية أين تبدأ كل فئة. يغطي دليلنا حول كيفية إنشاء مخطط Pareto باستخدام الذكاء الاصطناعي المخطط نفسه بمزيد من التفصيل.
اختيار نقاط التقسيم الخاصة بك
لا توجد نقطة تقسيم صحيحة واحدة. توضح MSH أن الاختيار "يعتمد على كيفية توزيع الحجم والقيمة بين العناصر المدرجة في القائمة". كما يعتمد أيضاً على "كيفية استخدام نتائج تحليل ABC".
القدرة الإدارية هي الحد العملي. وتصيغها MSH مباشرة: "يجب أن يعتمد تخصيص العناصر للفئة A على القدرة الإدارية". إذا كان بإمكان فريقك مراجعة 50 عنصراً بدقة كل شهر، فإن وجود 300 عنصر في الفئة A يفقد العملية الغرض منها.
بعض الأساليب الشائعة:
- نقاط تقسيم القيمة. الفئة A حتى 80 بالمئة من القيمة، والفئة B حتى 95 بالمئة، والفئة C للباقي. هذه هي الطريقة المستخدمة أعلاه.
- نقاط تقسيم عدد العناصر. تصبح أعلى 20 بالمئة من العناصر من حيث القيمة من الفئة A، والـ 30 بالمئة التالية من الفئة B، والباقي من الفئة C.
- القوائم الثابتة. تحدد بعض الفرق الفئة A كأعلى 25 أو 50 عنصراً، بغض النظر عن حصتها من القيمة.
أياً كان ما تختاره، قم بتدوينه واستخدمه في كل مرة. إن مقارنة فئات هذا الربع بفئات الربع الماضي لا تنجح إلا إذا ظلت نقاط التقسيم كما هي.
ماذا تفعل بكل فئة
الغرض من تحليل ABC هو بذل الجهد حيث توجد الأموال. يسرد فصل MSH عدة طرق لاستخدام النتائج:
- طلب عناصر الفئة A بشكل متكرر. تقول MSH إن طلب عناصر الفئة A "بشكل متكرر وبكميات أصغر يجب أن يؤدي إلى خفض تكاليف الاحتفاظ بالمخزون".
- التفاوض على أسعار الفئة A أولاً. "يمكن أن تؤدي التخفيضات في أسعار العناصر المصنفة كمنتجات من الفئة A في التحليل إلى توفير كبير"، وفقاً للفصل.
- جرد مخزون الفئة A بشكل متكرر. تشير MSH إلى أنه "يجب أن يسترشد جرد المخزون الدوري بتحليل ABC، مع إجراء جرد أكثر تكراراً لعناصر الفئة A".
- مراقبة حالة طلبات الفئة A. يمكن أن يؤدي النقص غير المتوقع في عنصر من الفئة A إلى عمليات شراء طارئة مكلفة.
يمكن أن تحصل عناصر الفئة C على قواعد أبسط، مثل طلبات أكبر وأقل تكراراً وجرد أقل. وتقع الفئة B بينهما. إذا كانت العناصر بطيئة الحركة تثير قلقك، فإن دليلنا حول كيفية اكتشاف المخزون بطيء الحركة يتناسب جيداً مع هذا التحليل.
القيام بذلك بشكل أسرع باستخدام الذكاء الاصطناعي
تستغرق خطوات Excel بضع دقائق بمجرد تنظيف البيانات. لكن تنظيف البيانات المصدرة وتكرار العمل كل ربع سنة يستغرق وقتاً أطول.
يمكن لمساحة عمل تعمل بالذكاء الاصطناعي (AI) إجراء العمليات الحسابية والفرز في طلب واحد. قم بتحميل ملف المخزون أو المشتريات المصدر إلى Powerdrill Bloom واطلب بلغة طبيعية إجراء تحليل ABC بنقاط التقسيم الخاصة بك. اطلب القيمة السنوية، والحصة، والنسبة المئوية التراكمية، والفئة لكل عنصر، بالإضافة إلى مخطط Pareto.
ثم تحقق منه كأي جدول بيانات آخر. تأكد من إجمالي القيمة السنوية مقارنة بمجموعك الخاص، وافحص عشوائياً عنصرين في كل فئة. تغطي صفحة مساعد Excel AI لدينا هذا النوع من العمل على جداول البيانات بمزيد من التفصيل. للحصول على نظرة أوسع على أدوات التنبؤ، راجع هذه المجموعة من أدوات الذكاء الاصطناعي (AI) للتنبؤ بالمخزون والطلب.
أخطاء شائعة يجب تجنبها
- خلط الفترات الزمنية. إن استخدام اثني عشر شهراً لعنصر واحد وستة أشهر لعنصر آخر يجعل الحصص بلا معنى.
- استخدام الوحدات بدلاً من القيمة. تعتمد الفئات على الوحدات مضروبة في التكلفة، وليس على الوحدات وحدها.
- نسيان الفرز قبل حساب الإجمالي التراكمي. إن حساب النسبة المئوية التراكمية في قائمة غير مفرزة يضع العناصر في الفئة الخاطئة.
- التعامل مع الفئات على أنها دائمة. أعد تشغيل التحليل كل ربع سنة أو سنة، لأن العناصر تنتقل بين الفئات.
- نقاط التقسيم التي تتجاهل القدرة الاستيعابية. إن وجود قائمة فئة A طويلة جداً بحيث يصعب إدارتها بدقة لن يحظى باهتمام أكبر من الفئة B.
- تجاهل العناصر الرخيصة الحيوية. يمكن أن يؤدي نفاد عنصر منخفض القيمة إلى توقف العمل. يقرن فصل MSH تحليل ABC بتقييم منفصل للعناصر الحيوية والأساسية وغير الأساسية.
عندما تأتي قائمة العناصر الخاصة بك من ملف مصدر غير منظم، يمكنك تجربة Powerdrill Bloom لإنشاء أول جدول ومخطط ABC.
الأسئلة الشائعة
ما هو تحليل ABC في إدارة المخزون؟
يصنف تحليل ABC العناصر إلى ثلاث فئات بناءً على قيمة الاستهلاك السنوي. عناصر الفئة A هي العناصر القليلة التي تستحوذ على معظم القيمة. وعناصر الفئة C هي العناصر الكثيرة التي تستحوذ على القليل منها، بينما تقع الفئة B بينهما. وهو يساعد الفرق على تركيز جهود الرقابة حيث توجد الأموال.
كيف تقوم بحساب تحليل ABC في Excel؟
اضرب الوحدات السنوية في تكلفة الوحدة لكل عنصر، ثم اقسم الناتج على الإجمالي للحصول على حصة كل عنصر. قم بالفرز حسب القيمة من الأكبر إلى الأصغر، وأضف إجمالياً تراكمياً للحصص، وعيّن الفئات باستخدام صيغة مثل IFS. تقع نقاط التقسيم البالغة 80 و 95 بالمئة ضمن النطاقات النموذجية في فصل MSH.
ما هي النسب المئوية لتحليل ABC؟
المبدأ التوجيهي الشائع هو أن الفئة A تضم من 10 إلى 20 بالمئة من العناصر و 75 إلى 80 بالمئة من القيمة. وتضم الفئة B من 10 إلى 20 بالمئة أخرى من العناصر و 15 إلى 20 بالمئة من القيمة. وتضم الفئة C من 60 إلى 80 بالمئة من العناصر و 5 إلى 10 بالمئة من القيمة.
ما هي صيغة تصنيف ABC في Excel؟
مع وجود النسبة المئوية التراكمية في العمود F وبدء البيانات من الصف 3، استخدم =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). قم بتغيير 0.8 و 0.95 لتتوافق مع نقاط التقسيم الخاصة بك. يمكن لصيغ IF المتداخلة القيام بنفس المهمة.
لماذا يعد تحليل ABC مهماً؟
يوضح هذا التحليل أين تذهب معظم أموال المخزون، بحيث يمكن للفرق إدارة هذه العناصر بدقة أكبر. وتشمل الاستخدامات النموذجية طلب عناصر الفئة A بشكل متكرر، والتفاوض على أسعارها أولاً، وجردها بشكل أكثر تكراراً. كما أنه ينبه إلى الإنفاق الذي لا يتطابق مع الخطط.
المصادر: منظمة علوم الإدارة من أجل الصحة، MDS-3 الفصل 40: تحليل ومراقبة النفقات الدوائية · Ravinder and Misra, ABC Analysis for Inventory Management (2014) · دعم Microsoft، دالة SORT · دعم Microsoft، دالة IFS · دعم Microsoft، إنشاء مخطط Pareto.