تحليل الحساسية في Excel | جدول بيانات متغير واحد واثنين
يساعدنا تحليل الحساسية في Excel على دراسة عدم اليقين في إخراج النموذج مع التغييرات في متغيرات الإدخال. إنه يقوم في المقام الأول باختبار الضغط لافتراضاتنا النموذجية ويؤدي إلى رؤى ذات قيمة مضافة
في سياق تقييم التدفقات النقدية المخصومة ، يعتبر تحليل الحساسية في إكسل مفيدًا بشكل خاص في التمويل لنمذجة سعر السهم أو حساسية التقييم لافتراضات مثل معدلات النمو أو تكلفة رأس المال.
في هذه المقالة ، نلقي نظرة على تحليل الحساسية التالي في Excel لنمذجة DCF بشكل احترافي.
الأهم - تنزيل تحليل الحساسية في قالب Excel
تعلم متغير واحد واثنين من أنواع جداول البيانات المتغيرة في Excel
تحليل الحساسية في Excel
# 1 - تحليل حساسية جدول بيانات متغير واحد في Excel
دعنا نأخذ مثال التمويل (نموذج خصم الأرباح) أدناه لفهم هذا المثال بالتفصيل.
النمو المستمر يمنحنا DDM القيمة العادلة للسهم كقيمة حالية لتدفق لا نهائي من أرباح الأسهم التي تنمو بمعدل ثابت.
صيغة Gordon Growth كما هو موضح أدناه -
أين:
- D1 = قيمة توزيعات الأرباح التي سيتم استلامها العام المقبل
- D0 = قيمة توزيعات الأرباح المستلمة هذا العام
- g = معدل نمو الأرباح
- Ke = معدل الخصم
الآن ، لنفترض أننا نريد أن نفهم مدى حساسية سعر السهم فيما يتعلق بالعائد المتوقع (ke). هناك طريقتان للقيام بذلك -
- طريق الحمار :-)
- ماذا في التحليل
# 1 - طريق الحمار
يعد تحليل الحساسية في Excel باستخدام طريقة Donkey واضحًا جدًا ، ولكن يصعب تنفيذه عند تضمين الكثير من المتغيرات.
هل تريد الاستمرار في القيام بذلك في ظل وجود 1000 افتراض؟ من الواضح أنه لا!
تعرف على تحليل الحساسية التالي في تقنية excel لإنقاذ أنفسكم من المشاكل.
# 2 - استخدام جدول بيانات متغير واحد
أفضل طريقة للقيام بتحليل الحساسية في Excel هي استخدام جداول البيانات. توفر جداول البيانات اختصارًا لحساب إصدارات متعددة في عملية واحدة وطريقة لعرض ومقارنة نتائج جميع الأشكال المختلفة معًا في ورقة العمل الخاصة بك. فيما يلي الخطوات التي يمكنك اتباعها لتنفيذ تحليل الحساسية أحادي البعد في Excel.
الخطوة 1 - قم بإنشاء الجدول بتنسيق قياسي
في العمود الأول ، لديك افتراضات الإدخال. في مثالنا ، المدخلات هي معدل العائد المتوقع (ke). يُرجى أيضًا ملاحظة وجود صف فارغ (ملون باللون الأزرق في هذا التمرين) أسفل عنوان الجدول. يخدم هذا الصف الفارغ غرضًا مهمًا لجدول البيانات أحادي البعد الذي ستراه في الخطوة 2.
الخطوة 2 - اربط المدخلات والمخرجات المرجعية على النحو الوارد في اللقطة أدناه.
يتم الآن استخدام المساحة التي يوفرها الصف الفارغ لتوفير الإدخال (العائد المتوقع Ke) وصيغة الإخراج. لماذا يتم مثل هذا؟
سنستخدم "ماذا لو التحليل" ، فهذه طريقة لإرشاد Excel إلى أنه بالنسبة للإدخال (ke) ، يجب استخدام الصيغة المقابلة المتوفرة على الجانب الأيمن لإعادة حساب جميع المدخلات الأخرى.
الخطوة 3 - حدد أداة تحليل ماذا لو لإجراء تحليل الحساسية في Excel
من المهم ملاحظة أن هذا ينقسم إلى خطوتين
- حدد نطاق الجدول بدءًا من الجانب الأيسر بدءًا من 10٪ حتى الركن الأيمن السفلي من الجدول.
- انقر فوق البيانات -> ماذا لو التحليل -> جداول البيانات
الخطوة 4 - يفتح مربع حوار جدول البيانات.
يبحث مربع الحوار عن مدخلين - إدخال الصف وإدخال العمود. نظرًا لوجود مدخل واحد فقط Ke قيد الدراسة ، فسنقدم إدخال عمود واحد.
الخطوة 5 - ربط إدخال العمود
في حالتنا ، يتم توفير جميع المدخلات في عمود ، وبالتالي ، سنقوم بالربط بمدخل العمود. إدخال العمود مرتبط بالعائد المتوقع (Ke). يرجى ملاحظة أنه يجب ربط الإدخال من المصدر الأصلي وليس من المصدر الموجود داخل الجدول
الخطوة 6 - استمتع بالإخراج
# 2 - تحليل حساسية جدول البيانات ثنائي المتغير في Excel
تعد جداول البيانات مفيدة جدًا لتحليل الحساسية في Excel خاصة في حالة DCF. بمجرد تحديد الحالة الأساسية ، يجب دائمًا اختبار تحليل التدفقات النقدية المخصومة DCF في ظل سيناريوهات الحساسية المختلفة يتضمن الاختبار فحص التأثير المتزايد للتغييرات المختلفة في الافتراضات (تكلفة رأس المال ، ومعدلات النمو النهائي ، ونمو الإيرادات المنخفض ، ومتطلبات رأس المال الأعلى ، إلخ) على القيمة العادلة للمخزون.
دعونا نأخذ تحليل الحساسية في التفوق مع مثال مالي لتحليل التدفقات النقدية المخصومة من علي بابا.
مع الافتراضات الأساسية لتكلفة رأس المال بنسبة 9٪ ومعدل نمو ثابت عند 3٪ ، توصلنا إلى التقييم العادل البالغ 191.45 مليار دولار.
لنفترض الآن أنك لا توافق تمامًا على تكلفة افتراضات رأس المال أو افتراضات معدل النمو التي اتخذتها في تقييم الاكتتاب العام على بابا. قد ترغب في تغيير الافتراضات والوصول إلى التأثير على التقييمات.
إحدى الطرق هي تغيير الافتراضات يدويًا والتحقق من نتائج كل تغيير. (كلمة السر - طريقة الحمار!)
ومع ذلك ، نحن هنا لمناقشة طريقة أفضل وفعالة لحساب التقييم باستخدام تحليل الحساسية في Excel الذي لا يوفر الوقت فحسب ، بل يوفر لنا أيضًا طريقة لتصور جميع تفاصيل المخرجات بتنسيق فعال.
إذا قمنا بإجراء تحليل What-if بطريقة احترافية على البيانات المذكورة أعلاه ، فإننا نحصل على الناتج التالي.
- هنا ، تتكون مدخلات الصف من التغييرات في تكلفة رأس المال أو WACC (7٪ إلى 11٪)
- تتكون مدخلات العمود من التغييرات في معدلات النمو (1٪ إلى 6٪)
- نقطة التقاطع هي Alibaba Valuation. على سبيل المثال. باستخدام حالتنا الأساسية وهي 9٪ WACC ومعدلات نمو 3٪ ، نحصل على التقييم على أنه 191.45 مليار دولار.
مع هذه الخلفية ، دعونا الآن نلقي نظرة على كيفية إعداد تحليل الحساسية هذا في التفوق باستخدام جداول البيانات ثنائية الأبعاد.
الخطوة 1 - قم بإنشاء هيكل الجدول كما هو موضح أدناه
- نظرًا لأن لدينا مجموعتين من الافتراضات - تكلفة رأس المال (WACC) ومعدلات النمو (g) ، فأنت بحاجة إلى إعداد جدول معروض أدناه.
- أنت حر في تبديل مدخلات الصفوف والعمود. بدلاً من WACC ، قد يكون لديك معدلات نمو والعكس صحيح.
الخطوة 2 - اربط نقطة التقاطع بخلية الإخراج.
يجب استخدام نقطة تقاطع المدخلين لربط الإخراج المطلوب. في هذه الحالة ، نريد أن نرى تأثير هذين المتغيرين (WACC ومعدل النمو) على قيمة حقوق الملكية. ومن ثم ، فقد قمنا بربط الخلية المتقاطعة بالمخرجات.
الخطوة 3 - افتح جدول بيانات ثنائي الأبعاد
- حدد الجدول الذي قمت بإنشائه
- ثم انقر فوق البيانات -> ماذا لو التحليل -> جداول البيانات
الخطوة 4 - قم بتوفير مدخلات الصف ومدخلات العمود.
- إدخال الصف هو تكلفة رأس المال أو Ke.
- إدخال العمود هو معدل النمو.
- يرجى تذكر ربط هذه المدخلات من مصدر الافتراض الأصلي وليس من أي مكان داخل الجدول
الخطوة 5 - استمتع بالإخراج.
- تقع معظم قيم الإنتاج المتشائمة في الزاوية العلوية اليمنى حيث تبلغ تكلفة رأس المال 11٪ ومعدل النمو 1٪ فقط
- أكثر قيمة للاكتتاب العام تفاؤلاً على بابا هي عندما يكون Ke 7٪ و g هو 6٪
- الحالة الأساسية التي حسبناها لمعدلات نمو 9٪ ke و 3٪ تقع في الوسط.
- يوفر تحليل الحساسية ثنائي الأبعاد في جدول Excel للعملاء تحليل سيناريو سهل يوفر الكثير من الوقت.
# 3 - البحث عن الهدف لتحليل الحساسية في Excel
- يتم استخدام أمر Goal Seek لإحضار صيغة واحدة إلى قيمة معينة
- يقوم بذلك عن طريق تغيير إحدى الخلايا التي تشير إليها الصيغة
- يطلب Goal Seek مرجع خلية يحتوي على صيغة (خلية التعيين). يطلب أيضًا قيمة ، وهو الرقم الذي تريد أن تساويه الخلية
- أخيرًا ، يطلب Goal Seek تغيير خلية من أجل نقل خلية التعيين إلى القيمة المطلوبة
دعونا نلقي نظرة على DCF لتثمين الاكتتاب العام على بابا.
كما نعلم من التدفقات النقدية المخصومة DCF أن معدلات النمو والتقييم مرتبطان بشكل مباشر. زيادة معدلات النمو تزيد من سعر السهم للسهم.
لنفترض أننا نريد التحقق من معدل النمو الذي سيصل سعر السهم إلى 80 دولارًا؟
كما هو الحال دائمًا ، يمكننا القيام بذلك يدويًا عن طريق تغيير معدلات النمو للاستمرار في رؤية التأثير على سعر السهم. ستكون هذه مرة أخرى عملية شاقة ، قد نضطر إلى إدخال معدلات النمو عدة مرات للتأكد من أن سعر السهم يطابق 80 دولارًا في حالتنا.
ومع ذلك ، يمكننا استخدام دالة مثل Goal Seek في التفوق لحل هذه المشكلة بخطوات سهلة.
الخطوة 1 - انقر فوق الخلية التي ترغب في تعيين قيمتها. (يجب أن تحتوي خلية التعيين على صيغة)
الخطوة 2 - اختر أدوات ، Goal Seek من القائمة ، وسيظهر مربع الحوار التالي:
- يقترح أمر Goal Seek الخلية النشطة تلقائيًا على أنها خلية Set.
- يمكن الكتابة فوق ذلك بمرجع خلية جديد أو يمكنك النقر فوق الخلية المناسبة في جدول البيانات.
- أدخل الآن القيمة المطلوبة التي يجب أن تصل إليها هذه الصيغة.
- انقر داخل مربع "To Value" واكتب القيمة التي تريد أن تساويها الصيغة المحددة
- أخيرًا ، انقر داخل مربع "عن طريق تغيير الخلية" واكتب أو انقر فوق الخلية التي يمكن تغيير قيمتها لتحقيق النتيجة المرجوة
- انقر فوق الزر "موافق" وسيقوم جدول البيانات بتغيير الخلية إلى قيمة كافية حتى تصل الصيغة إلى هدفك.
الخطوة 3 - استمتع بالإخراج.
يُعلمك Goal Seek أيضًا أن الهدف قد تحقق
استنتاج
يزيد تحليل الحساسية في برنامج Excel من فهمك للسلوك المالي والتشغيلي للشركة. كما تعلمنا من المناهج الثلاثة - جداول البيانات ذات البعد الواحد ، وجداول البيانات ثنائية الأبعاد ، والسعي إلى الهدف ، فإن تحليل الحساسية مفيد للغاية في مجال التمويل خاصة في سياق التقييمات - DCF أو DDM.
ومع ذلك ، يمكنك أيضًا الحصول على فهم على المستوى الكلي للشركة والصناعة بشكل عام. يمكنك تطوير الحالات لتعكس حساسية التقييم للتغيرات في أسعار الفائدة ، والركود ، والتضخم ، والناتج المحلي الإجمالي ، وما إلى ذلك في التقييم. يجب توظيف الفكر والمنطق في تطوير حالات حساسية معقولة ومفيدة.
ماذا بعد؟
إذا تعلمت شيئًا عن تحليل الحساسية في Excel ، فالرجاء ترك تعليق أدناه. اسمحوا لي أن أعرف ما هو رأيك. شكرا جزيلا واعتن بنفسك. تعلم سعيد!
يمكنك أيضًا إلقاء نظرة على هذه المقالات أدناه لمعرفة المزيد حول التقييمات وتمويل الشركات -
- صيغة لحساسية السعر
- تحليل المخاطر - الطرق
- Excel تحليل التعادل
- تحليل Excel Pareto <
