كيف تُجري تحليل التعادل في Excel؟

افترض أنك تخطط لإطلاق منتج جديد، وتعرف التكلفة المتغيرة لكل وحدة والمصروفات الثابتة الإجمالية. الآن، تريد التنبؤ بأحجام المبيعات المحتملة وتحديد سعر المنتج بناءً على تلك التوقعات.

1. أنشئ جدولًا بسيطًا وأدخل البيانات المُعطاة فيه. راجع لقطة الشاشة أدناه:

2. أدخل الصيغ المناسبة لحساب الإيرادات والتكاليف المتغيرة والربح. راجع لقطة الشاشة أعلاه:
الإيرادات = سعر الوحدة × عدد الوحدات المباعة
التكاليف المتغيرة = التكلفة لكل وحدة × عدد الوحدات المباعة
الربح = الإيرادات – التكاليف المتغيرة – التكاليف الثابتة

3. انقر علىبيانات > تحليل ماذا لو > البحث عن الهدف.

4. في مربع حوار البحث عن الهدف الذي يظهر، قم بما يلي (انظر لقطة الشاشة أعلاه):
(1) اضبط خليةالتعيينلتكون خلية الربح، وهي في حالتنا الخلية B7؛
(2) عيّن القيمةالمستهدفةإلى0؛
(3) حدّد الخليةالتي سيتم تغييرهالتكون خلية سعر الوحدة، وهي في حالتنا الخلية B1؛
(4) انقر على زرموافق.

5. بعد ذلك، يظهر مربع حوار حالة البحث عن الهدف. يُرجى النقر على زرموافقلتطبيقه.

لقد انخفض سعر الوحدة الآن من 40 إلى 31.579، وانخفض صافي الربح إلى الصفر. لذا، إذا كنت تتوقع أن يبلغ حجم المبيعات 50 وحدة، فلا يمكن أن يكون سعر الوحدة أقل من 31.579، وإلا فسوف تتكبّد خسارة.

تنزيل قالب تحليل التعادل المعقد

http://office.microsoft.com/en-us/templates/breakeven-analysis-TC001116512.aspx
http://office.microsoft.com/en-us/templates/break-even-analysis-TC001017515.aspx

بالمقارنة بميزة «البحث عن الهدف»، يمكننا أيضًا استخدام الصيغة لإجراء تحليل التعادل بسهولة في Excel.

1. أنشئ جدولًا بسيطًا وأدخل البيانات المُعطاة فيه. في هذه الطريقة، نفترض أن الربح يساوي صفرًا، وقد قمنا مسبقًا بالتنبؤ بعدد الوحدات المباعة والتكلفة لكل وحدة والتكاليف الثابتة. انظر لقطة الشاشة أدناه:

2. في الجدول، أدخل الصيغة=B6/B2+B4 في الخلية B1 لحساب سعر الوحدة، والصيغة=B1*B2 في الخلية B3 لحساب الإيرادات، والصيغة=B2*B4 في الخلية B5 للتكاليف المتغيرة. انظر لقطة الشاشة أدناه:

وبعد ذلك، كلما غيّرت أيًّا من القيم المتوقعة—مثل عدد الوحدات المباعة أو التكلفة لكل وحدة أو التكاليف الثابتة—سيتم تحديث سعر الوحدة تلقائيًّا. انظر لقطة الشاشة أعلاه:

تلميح: احفظ نطاقًا كإدخال نص تلقائي (مع الاحتفاظ بتنسيق الخلية والصيغ) لإعادة استخدامه لاحقًا
من المؤكد أن الرجوع إلى الخلايا وتطبيق صيغ متعددة لتحليل التعادل في كل مرة أمرٌ مرهقٌ للغاية. يوفّر لك Kutools لـ Excel حلاً ذكيًّا عبر أداةالنص التلقائيلحفظ النطاق كإدخال نص تلقائي، بحيث يحتفظ بتنسيق الخلية والصيغ الموجودة فيه. وبعد ذلك، يمكنك إعادة استخدام هذا النطاق بنقرة واحدة فقط في أي مصنف!

احصل الآن على نسخة تجريبية مجانية لمدة 30 يومًا بكامل الميزات!

إذا كنت قد سجّلت بيانات المبيعات بالفعل، فيمكنك أيضًا إجراء تحليل التعادل باستخدام مخطط في Excel. ستوجّهك هذه الطريقة لإنشاء مخطط تحليل التعادل بسهولة.

1. أعد جدول مبيعات كما في لقطة الشاشة أدناه. في حالتنا، نفترض أن عدد الوحدات المباعة وتكلفة كل وحدة والتكاليف الثابتة ثابتة، ونحتاج إلى إجراء تحليل التعادل بناءً على سعر الوحدة.

2. أكمل الجدول كما هو موضح أدناه:
(1) في الخلية E2، أدخل الصيغة=D2*$B$1، ثم اسحب مقبض الملء لأسفل حتى النطاق E2:E13؛
(2) في الخلية F2، أدخل الصيغة=D2*$B$1+$B$3، ثم اسحب مقبض الملء لأسفل حتى النطاق F2:F13؛
(3) في الخلية G2، أدخل الصيغة=E2-F2، ثم اسحب مقبض الملء لأسفل حتى النطاق G2:G13.

بذلك تكون قد أكملت البيانات الأصلية لمخطط تحليل التعادل الذي سننشئه لاحقًا. راجع لقطة الشاشة أدناه:

3. في الجدول، حدد أعمدة الإيرادات والتكاليف والربح معًا دفعة واحدة، ثم انقر علىإدراج > إدراج مخطط خطي أو مساحي > خط. انظر لقطة الشاشة:

4. الآن تم إنشاء مخطط خطي. يُرجى النقر بزر الماوس الأيمن على المخطط، ثم اختيار «تحديد البيانات» من القائمة السياقية. انظر لقطة الشاشة أدناه:

5. في مربع حوار تحديد البيانات المصدر، يُرجى:
(1) في قسمنطاق اسم السلسلة، تحديد إحدى السلاسل حسب الحاجة. في مثالي، اخترت سلسلةالإيرادات؛
(2) النقر على زرتعديلفي قسمتسميات المحور الأفقي (الفئة)؛
(3) في مربع الحوار المنبثق «نطاق تسميات المحور»، تحديد عمود سعر الوحدة (باستثناء اسم العمود) كنطاق لتسميات المحور؛
(4) النقر علىموافقثمموافقلحفظ التغييرات.

الآن في مخطط تحليل التعادل، سترى أن نقطة التعادل تحدث عندما يساوي السعر 36 كما في لقطة الشاشة أدناه:

بالمثل، يمكنك أيضًا إنشاء مخطط تحليل التعادل لتحليل نقطة التعادل حسب عدد الوحدات المباعة كما في لقطة الشاشة أدناه:

صدّر/احفظ التحديد بسهولة كملف PDF/CSV/TEXT/ملف HTML منفصل في Excel

على سبيل المثال، بعد الانتهاء من تحليل التعادل في Excel، قد ترغب في إعداد عرض تقديمي يتضمّن هذا التحليل. في هذه الحالة، يمكنك استخدام أداة Kutools لـ Excel تصدير النطاق إلى ملفلتصدير التحديد بسرعة كملف منفصل بصيغة PDF أو HTML أو CSV أو ملف نصي، وغير ذلك.