كيفية إجراء تحليل نقطة التعادل في Excel؟

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

1. قم بإنشاء جدول بسيط واملأ العناصر بالبيانات المعطاة في الجدول. انظر إلى لقطة الشاشة أدناه:

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

3. اضغط على البيانات > تحليل السيناريوهات > Goal Seek.

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

5. ثم سيظهر مربع حوار حالة Goal Seek. يرجى الضغط على زر موافق لتطبيقها.

الآن يتغير سعر الوحدة من 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

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

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

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

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

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

احصل على نسخة تجريبية مجانية كاملة الميزات لمدة 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) في Legend Entries (Series) القسم، حدد أحد السلاسل حسب الحاجة. في مثالنا، حددت الإيرادات السلسلة؛
(2) اضغط على زر تحرير في Horizontal (Category) Axis Labels القسم؛
(3) في مربع الحوار Axis Labels المنبثق، حدد عمود سعر الوحدة (باستثناء اسم العمود) كنطاق تسميات المحور؛
(4) اضغط على موافق > موافق لحفظ التغييرات.

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

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

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

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