كيفية إجراء تحليل الحساسية في إكسل: خطوة بخطوة

لإجراء تحليل الحساسية في Excel، ضع كل مدخل من مدخلات نموذجك في خلية خاصة به. ثم قم بتغيير تلك المدخلات وسجل كيف تتغير النتيجة. استخدم جدول بيانات بمتغير واحد لمدخل واحد، وجدول بيانات بمتغيرين لمدخلين، و Scenario Manager للحالات الكاملة. اختم بمخطط الإعصار (tornado chart) الذي يرتب المدخلات حسب التأثير.

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

ما هو تحليل الحساسية

يُظهر تحليل الحساسية مقدار تغير النتيجة عند تغير افتراض واحد. فهو يجيب على أسئلة مثل "ماذا يحدث للربح إذا انخفض السعر بنسبة 10%؟"

تعتمد معظم نماذج الأعمال على التخمينات. فحجم المبيعات والسعر والتكاليف ومعدلات النمو كلها تقديرات. يختبر تحليل الحساسية أي من هذه التخمينات هو الأكثر أهمية.

يجمع Excel هذه الأدوات تحت مسمى What-If Analysis. وتعرفه Microsoft بأنه "عملية تغيير القيم في الخلايا" لرؤية كيف تتغير نتائج الصيغ.

تدرج Microsoft ثلاث أدوات مدمجة: "Scenarios، و Goal Seek، و Data Tables". ولكل منها وظيفة مختلفة:

• جداول البيانات (Data tables) تعرض نتيجة واحدة عبر قيم متعددة لمدخل واحد أو اثنين.
• إدارة السيناريوهات (Scenario Manager) تحفظ مجموعات كاملة من المدخلات، مثل الحالة الأسوأ والحالة الأفضل.
• البحث عن الهدف (Goal Seek) تعمل بشكل عكسي؛ حيث تجد المدخل المطلوب للوصول إلى نتيجة مستهدفة.

عادةً ما يستخدم تحليل الحساسية الأداتين الأوليين. بينما يساعد Goal Seek في الإجابة على سؤال ذي صلة، مثل سعر نقطة التعادل.

إعداد النموذج قبل البدء

لا تعمل أدوات Excel إلا إذا تم بناء النموذج بشكل نظيف. تحقق من هذه النقاط أولاً.

ضع كل مدخل في خلية خاصة به. تحتاج الوحدات والسعر والتكاليف والمعدلات كل منها إلى خلية. لا تكتب الأرقام داخل الصيغ.

اربط النتيجة بالمدخلات. يجب أن تكون خلية النتيجة، مثل الربح، صيغة تشير إلى خلايا المدخلات. إذا لم تكن كذلك، فلن يتغير شيء عندما يقوم Excel بتبديل القيم الجديدة.

حافظ على تجميع المدخلات معاً. اجمعها في كتلة واحدة في الجزء العلوي من الورقة. ضع تسمية لكل منها، ولون خلايا المدخلات لتبرز.

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

دون حالتك الأساسية. اكتب القيم الأولية. تتم مقارنة كل نتيجة في التحليل بهذه القاعدة الأساسية.

إليك نموذج ربح بسيط للمتابعة. إنه لمتجر دراجات خيالي، Northgate Cycles:

الخلية	التسمية	القيمة
B1	الوحدات المباعة	2,400
B2	سعر الوحدة	$850
B3	التكلفة المتغيرة للوحدة	$510
B4	التكاليف الثابتة	$520,000
B5	الربح	=B1*(B2-B3)-B4

عند هذه القيم الأساسية، يكون الربح $296,000.

كيفية إجراء تحليل حساسية بمتغير واحد باستخدام جدول بيانات

يختبر جدول البيانات ذو المتغير الواحد نطاقاً من القيم لمدخل واحد. تقول Microsoft إنه يوضح "كيف ستغير القيم المختلفة لمتغير واحد في صيغة واحدة أو أكثر النتائج".

إليك كيفية اختبار السعر في نموذج المثال:

1. في الخلايا من D2 إلى D6، اكتب الأسعار المراد اختبارها: $800، $825، $850، $875، و $900.
2. في الخلية E1، صف واحد فوق وأول عمود إلى يمين السعر الأول، اكتب =B5.
3. حدد النطاق D1:E6.
4. في علامة التبويب Data، اختر What-If Analysis > Data Table.
5. في Column input cell، أدخل B2، وهي خلية السعر. اترك Row input cell فارغة.
6. انقر فوق OK.

يملأ Excel الخلايا E2:E6 بالربح لكل سعر. مكان الصيغة مهم؛ حيث تقول Microsoft بوجوب كتابتها "صفاً واحداً فوق وخلية واحدة إلى يمين عمود القيم".

النتائج لـ Northgate Cycles:

السعر	الربح
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

كل تغيير بمقدار $25 في السعر يحرك الربح بمقدار $60,000. هذه هي حساسية الربح تجاه السعر.

كيفية إجراء تحليل حساسية بمتغيرين

يختبر جدول البيانات ذو المتغيرين مدخلين في وقت واحد. تقول Microsoft إنه يوضح "كيف ستغير القيم المختلفة لمتغيرين في صيغة واحدة نتائج تلك الصيغة".

إليك كيفية اختبار السعر والوحدات معاً:

1. في خلية فارغة، مثل D10، اكتب =B5.
2. تحتها، في الخلايا من D11 إلى D13، اكتب أحجام الوحدات: 2,000، 2,400، و 2,800.
3. إلى يمينها، في الخلايا من E10 إلى G10، اكتب الأسعار: $800، $850، و $900.
4. حدد النطاق D10:G13.
5. اختر What-If Analysis > Data Table.
6. في Row input cell، أدخل B2. وفي Column input cell، أدخل B1.
7. انقر فوق OK.

النتيجة هي شبكة من الأرباح:

الوحدات \ السعر	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

تُظهر الشبكة أن المتجر يظل رابحاً في كل مجموعة تم اختبارها. أضعف زاوية، وهي 2,000 وحدة بسعر $800، لا تزال تحقق ربحاً قدره $60,000.

هناك حد صارم واحد؛ حيث تقول Microsoft "لا يمكن لجدول البيانات استيعاب أكثر من متغيرين". لمزيد من المدخلات، استخدم Scenario Manager.

كما أن جداول البيانات تعيد الحساب كلما تمت إعادة حساب الورقة. في دفاتر العمل الكبيرة، يمكن أن يؤدي ذلك إلى إبطاء Excel. تقترح Microsoft ضبط الحساب بحيث لا تعيد جداول البيانات الحساب إلا عند الضغط على F9.

كيفية مقارنة الحالات الكاملة باستخدام Scenario Manager

يحفظ Scenario Manager مجموعات كاملة من المدخلات ويقوم بتبديلها. تصف Microsoft السيناريو بأنه "مجموعة من القيم التي يحفظها Excel ويمكنه استبدالها تلقائياً في ورقة العمل الخاصة بك".

إليك كيفية إعداد ثلاث حالات:

1. في علامة التبويب Data، اختر What-If Analysis > Scenario Manager.
2. انقر فوق Add، وسمِّ السيناريو Base، وأدخل B1:B4 كخلايا متغيرة. احتفظ بالقيم الحالية.
3. أضف حالة Worst case: 2,000 وحدة، سعر $825، تكلفة متغيرة $530، وتكاليف ثابتة $540,000.
4. أضف حالة Best case: 2,800 وحدة، سعر $875، تكلفة متغيرة $495، وتكاليف ثابتة $510,000.
5. اختر سيناريو وانقر فوق Show لتحميل قيمه.
6. انقر فوق Summary واختر B5 كخلية نتيجة لإنشاء تقرير.

بالنسبة لـ Northgate Cycles، يظهر الملخص ثلاثة أرباح. تحقق الحالة الأسوأ $50,000، والحالة الأساسية $296,000، والحالة الأفضل $554,000.

تشير Microsoft إلى بعض القيود. يمكن لكل سيناريو أن يحتوي على ما يصل إلى 32 قيمة متغيرة. تقرير الملخص ليس تلقائياً: "لا يتم إعادة حساب تقارير السيناريو تلقائياً". إذا قمت بتغيير سيناريو، فأنشئ ملخصاً جديداً.

كيفية العثور على نقطة التعادل باستخدام Goal Seek

يُظهر تحليل الحساسية مدى تغير النتيجة. أما Goal Seek فيجيب على السؤال العكسي: ما هي قيمة المدخلات التي تعطي نتيجة مستهدفة؟

تقول Microsoft إن Goal Seek "يأخذ نتيجة ويحدد قيم المدخلات الممكنة التي تنتج تلك النتيجة". مثالها يجد معدل الفائدة المطلوب لسداد قرض.

إليك كيفية العثور على سعر التعادل لـ Northgate Cycles:

1. في علامة التبويب Data، اختر What-If Analysis > Goal Seek.
2. في Set cell، أدخل B5، وهي خلية الربح.
3. في To value، أدخل 0.
4. في By changing cell، أدخل B2، وهي خلية السعر.
5. انقر فوق OK.

يقوم Excel بتغيير السعر حتى يصل الربح إلى الصفر. عند 2,400 وحدة والتكاليف الأساسية، يكون سعر التعادل حوالي $726.67. وهذا أقل بنحو 15% من السعر الحالي البالغ $850.

أداة Goal Seek لها قيد واحد؛ حيث تقول Microsoft إنها "تعمل مع قيمة مدخلات متغيرة واحدة فقط". لأكثر من مدخل واحد، تشير Microsoft إلى وظيفة Solver الإضافية.

كيفية بناء مخطط الإعصار (tornado chart)

يرتب مخطط الإعصار المدخلات حسب مقدار تأثيرها على النتيجة. إنها أوضح طريقة لتقديم تحليل الحساسية.

إليك كيفية بناء واحد:

1. اختر نطاق اختبار، مثل تقليل كل مدخل بنسبة 10% وزيادته بنسبة 10%.
2. قم بتغيير مدخل واحد في كل مرة، وسجل النتيجة المنخفضة والمرتفعة. حافظ على المدخلات الأخرى عند مستواها الأساسي.
3. ضع النتائج في جدول مع صف واحد لكل مدخل.
4. رتب الصفوف بحيث يكون المدخل ذو النطاق الأوسع في الأعلى.
5. أدرج مخططاً شريطياً للنتائج المنخفضة والمرتفعة.

بالنسبة لـ Northgate Cycles، مع تحريك كل مدخل بنسبة 10% صعوداً وهبوطاً:

المدخل	الربح عند −10%	الربح عند +10%	النطاق
السعر	$92,000	$500,000	$408,000
التكلفة المتغيرة	$418,400	$173,600	$244,800
الوحدات المباعة	$214,400	$377,600	$163,200
التكاليف الثابتة	$348,000	$244,000	$104,000

السعر هو الأكثر أهمية بفارق كبير. فانخفاض السعر بنسبة 10% يقلل الربح من $296,000 إلى $92,000. التكاليف الثابتة هي الأقل أهمية. يخبر هذا المالك بالمكان الذي يجب أن يقضي فيه وقته في التحقق من الافتراضات.

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

كيفية إجراء تحليل الحساسية باستخدام AI

يستغرق بناء جداول البيانات والسيناريوهات ومخطط الإعصار لنموذج كبير وقتاً طويلاً. يمكن لأداة AI إجراء نفس الاختبارات من دفتر عملك.

إليك الطريقة في ثلاث خطوات، باستخدام Powerdrill Bloom.

الخطوة 1: رفع نموذجك

ابدأ بجدول البيانات الذي يحتوي على نموذجك. يطلب منك AI Sensitivity Analysis Generator رفع "DCF، أو حالة عمل، أو خطة تشغيل أو أي نموذج جدول بيانات".

تأكد من تسمية المدخلات وخلية النتيجة بوضوح. تحتاج الأداة إلى معرفة الأرقام التي تمثل الافتراضات.

الخطوة 2: اختر عرض الحساسية بلغة طبيعية

صف الاختبار الذي تريده. على سبيل المثال: "أظهر مخطط إعصار للربح مع تحريك كل مدخل بنسبة 10% صعوداً وهبوطاً. أضف جدولاً ثنائياً للسعر والوحدات المباعة".

تسرد صفحة الأداة أربعة عروض: "إعصار، ثنائي الاتجاه، Monte Carlo أو نقطة التعادل". وتصف مخرج الإعصار بأنه مخطط "يرتب المدخلات التي تحرك الإجابة أكثر من غيرها". كما تقدم "جدول حساسية عبر الافتراضين الأكثر أهمية".

الخطوة 3: تحقق من النتائج وتصدير التحليل

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

ثم قم بالتصدير. تسرد صفحة الأداة "دفتر عمل Excel، أو PDF يحمل علامتك التجارية أو رابط كانفاس". بالنسبة للعروض التقديمية، تقوم أداة Excel to PPT tool بتحويل نفس الملف إلى شرائح. وتغطي صفحة AI Financial Modeling بناء النموذج نفسه.

كيفية قراءة وتقديم النتائج

لا يكون تحليل الحساسية مفيداً إلا إذا اتخذ الناس إجراءات بناءً عليه. تساعد هذه العادات في ذلك.

ابدأ بالترتيب. افتح بمخطط الإعصار. فهو يوضح في صورة واحدة أي الافتراضات هي التي تقود النتيجة.

ترجم النطاقات إلى قرارات. "ينخفض الربح إلى $92,000 إذا انخفض السعر بنسبة 10%" هي حقيقة. "يجب أن نختبر السعر قبل توقيع عقد الإيجار" هو قرار.

أظهر نقطة التعادل. اربط التحليل بالقيمة التي تصل عندها النتيجة إلى الصفر. بالنسبة لـ Northgate Cycles، نقطة التعادل هي حوالي 1,530 وحدة بالسعر والتكاليف الأساسية. يغطي دليلنا حول تحليل التعادل في Excel تلك العملية الحسابية.

استخدم نطاقات واقعية. حركة بنسبة 10% تناسب المدخلات المستقرة. أما حجم مبيعات منتج جديد فقد يحتاج إلى نطاق أوسع بكثير.

اذكر ما تم تثبيته. كل اختبار "واحد في كل مرة" يبقي المدخلات الأخرى عند مستواها الأساسي. قل ذلك على الشريحة أو في حاشية سفلية. سيعرف القراء حينها أن تغيير السعر بنسبة 10% يفترض نفس الحجم والتكاليف والإنفاق الثابت. هذا السطر الواحد يمنع معظم الأسئلة اللاحقة حول الطريقة.

راجع التحليل بعد ظهور النتائج. بمجرد وصول الأرقام الفعلية، قارنها بنطاقاتك. يعد تقرير الميزانية مقابل الفعلي متابعة طبيعية.

أخطاء شائعة في تحليل الحساسية

هذه الأخطاء شائعة وسهلة التجنب.

أرقام مكتوبة مباشرة (Hard-coded) في الصيغ. إذا كانت الصيغة تحتوي على 850 بدلاً من B2، فلن يتمكن جدول البيانات من تغييرها. احتفظ بكل مدخل في خلية.

اختبار عدد كبير جداً من المدخلات في وقت واحد. يتعامل جدول البيانات مع مدخل واحد أو اثنين. انتقل إلى Scenario Manager أو مخطط الإعصار للمزيد.

استخدام نفس النطاق لكل مدخل. نادراً ما يحمل السعر والحجم نفس درجة عدم اليقين. اختر نطاقات تعكس ما تعرفه بالفعل.

تجاهل الروابط بين المدخلات. قد يؤدي انخفاض السعر إلى زيادة الحجم. يفتقد اختبار "واحد في كل مرة" ذلك، لذا أضف سيناريو يحرك كليهما.

ملخصات سيناريو قديمة. لا يتم تحديث تقارير السيناريو من تلقاء نفسها. أعد بناءها بعد أي تغيير.

نسيان الحالة الأساسية. يجب أن يظهر كل جدول النتيجة الأساسية، حتى يتمكن القراء من رؤية حجم كل تغيير.

عندما تكون مستعداً لاختبار نموذجك التالي، جرب Powerdrill Bloom مع دفتر العمل الذي تستخدمه للتخطيط.

الأسئلة الشائعة

ما هو تحليل الحساسية في Excel؟

إنه طريقة لاختبار كيف تتغير النتيجة عندما تتغير مدخلاتها. في Excel، تستخدم أدوات What-If Analysis، وبشكل أساسي جداول البيانات و Scenario Manager، لتبديل قيم المدخلات الجديدة وتسجيل النتائج.

كيف أجري تحليل حساسية بمتغير واحد في Excel؟

أدرج قيم المدخلات في عمود. ضع صيغة تشير إلى النتيجة صفاً واحداً فوق وخلية واحدة إلى اليمين. حدد النطاق، واختر What-If Analysis > Data Table، وأدخل خلية المدخلات كـ column input cell.

ما الفرق بين جدول البيانات و Scenario Manager؟

يختبر جدول البيانات متغيراً واحداً أو اثنين عبر قيم عديدة ويعرض جميع النتائج في شبكة واحدة. أما Scenario Manager فيحفظ مجموعات كاملة من المدخلات، تصل إلى 32 قيمة متغيرة لكل منها، ويقوم بتبديلها واحدة تلو الأخرى.

كيف أصنع مخطط إعصار في Excel؟

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

هل يمكن لـ AI إجراء تحليل حساسية؟

نعم. يمكن لأداة AI قراءة نموذج جدول البيانات الخاص بك، وإجراء اختبارات الإعصار والاختبارات ثنائية الاتجاه، وتصدير النتائج كدفتر عمل أو تقرير. ستظل بحاجة إلى التحقق من الحالة الأساسية واختيار نطاقات واقعية.

المصادر: Microsoft Support، مقدمة إلى What-If Analysis · Microsoft Support، حساب نتائج متعددة باستخدام جدول بيانات · Microsoft Support، التبديل بين مجموعات مختلفة من القيم باستخدام السيناريوهات.