المحتويات
دائمًا ما تكون العمليات في مجال التمويل مترابطة - يعتمد عامل واحد على الآخر ويتغير معه. تتبع هذه التغييرات وفهم ما يمكن توقعه في المستقبل ، ربما باستخدام وظائف Excel وطرق جداول البيانات.
الحصول على نتائج متعددة باستخدام جدول البيانات
إمكانيات ورقة البيانات هي عناصر لتحليل ماذا لو - يتم إجراؤه غالبًا من خلال Microsoft Excel. هذا هو الاسم الثاني لتحليل الحساسية.
نبذة
جدول البيانات هو نوع من نطاق الخلايا التي يمكن استخدامها لحل المشكلات عن طريق تغيير القيم في بعض الخلايا. يتم إنشاؤه عندما يكون من الضروري تتبع التغييرات في مكونات الصيغة وتلقي تحديثات للنتائج ، وفقًا لهذه التغييرات. دعنا نتعرف على كيفية استخدام جداول البيانات في البحث وأنواعها.
أساسيات حول جداول البيانات
هناك نوعان من جداول البيانات ، يختلفان في عدد المكونات. تحتاج إلى تجميع جدول مع التركيز على عدد القيم uXNUMXbuXNUMXb التي تحتاج إلى التحقق منها.
يستخدم الإحصائيون جدول متغير واحد عندما يكون هناك متغير واحد فقط في تعبير واحد أو أكثر يمكنه تغيير النتيجة. على سبيل المثال ، غالبًا ما يتم استخدامه مع وظيفة PMT. تم تصميم الصيغة لحساب مبلغ الدفع المنتظم وتأخذ في الاعتبار معدل الفائدة المحدد في الاتفاقية. في مثل هذه الحسابات ، تتم كتابة المتغيرات في عمود ، ونتائج الحسابات في عمود آخر. مثال على لوحة بيانات ذات متغير واحد:
بعد ذلك ، ضع في اعتبارك اللوحات ذات المتغيرين. يتم استخدامها في الحالات التي يؤثر فيها عاملان على التغيير في أي مؤشر. قد ينتهي المتغيرين في جدول آخر مرتبط بالقرض ، والذي يمكن استخدامه لتحديد فترة السداد المثلى ومقدار الدفعة الشهرية. في هذا الحساب ، تحتاج أيضًا إلى استخدام وظيفة PMT. مثال على جدول به متغيرين:
إنشاء جدول بيانات بمتغير واحد
ضع في اعتبارك طريقة التحليل باستخدام مثال مكتبة صغيرة بها 100 كتاب فقط في المخزون. يمكن بيع بعضها بسعر أعلى (50 دولارًا) ، وسيكلف الباقي المشترين أقل (20 دولارًا). يتم احتساب إجمالي الدخل من بيع جميع السلع - قرر المالك أنه سيبيع 60٪ من الكتب بسعر مرتفع. تحتاج إلى معرفة كيفية زيادة الإيرادات إذا قمت بزيادة سعر حجم أكبر من السلع - 70٪ ، وما إلى ذلك.
انتبه! يجب حساب إجمالي الإيرادات باستخدام صيغة ، وإلا فلن يكون من الممكن تجميع جدول بيانات.
- حدد خلية حرة بعيدًا عن حافة الورقة واكتب الصيغة فيها: = خلية إجمالي الإيرادات. على سبيل المثال ، إذا كان الدخل مكتوبًا في الخلية C14 (يشار إلى التعيين العشوائي) ، فستحتاج إلى كتابة هذا: = S14.
- نكتب النسبة المئوية لحجم البضائع في العمود الموجود على يسار هذه الخلية - وليس أسفلها ، هذا مهم جدًا.
- نختار نطاق الخلايا حيث يوجد عمود النسبة المئوية والرابط إلى إجمالي الدخل.
- نجد في علامة التبويب "البيانات" العنصر "ماذا لو التحليل" وانقر فوقه - في القائمة التي تفتح ، حدد الخيار "جدول البيانات".
- سيتم فتح نافذة صغيرة حيث تحتاج إلى تحديد خلية مع النسبة المئوية للكتب التي تم بيعها في البداية بسعر مرتفع في العمود "استبدال القيم بالصفوف في ...". تتم هذه الخطوة لإعادة حساب إجمالي الإيرادات مع الأخذ في الاعتبار النسبة المئوية المتزايدة.
بعد النقر فوق الزر "موافق" في النافذة حيث تم إدخال البيانات لتجميع الجدول ، ستظهر نتائج الحسابات في السطور.
إضافة صيغة إلى جدول بيانات متغير واحد
من الجدول الذي ساعد في حساب إجراء باستخدام متغير واحد فقط ، يمكنك إنشاء أداة تحليل متطورة عن طريق إضافة صيغة إضافية. يجب إدخاله بجوار صيغة موجودة بالفعل - على سبيل المثال ، إذا كان الجدول موجهًا للصفوف ، فإننا ندخل التعبير في الخلية الموجودة على يمين القائمة الموجودة. عندما يتم تعيين اتجاه العمود ، نكتب الصيغة الجديدة تحت القديم. بعد ذلك ، اتبع الخوارزمية:
- حدد نطاق الخلايا مرة أخرى ، ولكن الآن يجب أن يتضمن الصيغة الجديدة.
- افتح قائمة التحليل "ماذا لو" وحدد "ورقة البيانات".
- نضيف صيغة جديدة إلى الحقل المقابل في صفوف أو أعمدة ، اعتمادًا على اتجاه اللوحة.
قم بإنشاء جدول بيانات بمتغيرين
تختلف بداية هذا الجدول قليلاً - تحتاج إلى وضع رابط لإجمالي الإيرادات أعلى من قيم النسبة المئوية. بعد ذلك ، نقوم بتنفيذ الخطوات التالية:
- اكتب خيارات الأسعار في سطر واحد مع ارتباط بالدخل - خلية واحدة لكل سعر.
- حدد نطاق من الخلايا.
- افتح نافذة جدول البيانات ، كما هو الحال عند تجميع جدول بمتغير واحد - من خلال علامة التبويب "البيانات" في شريط الأدوات.
- عوض في العمود "استبدل القيم بالأعمدة في ..." خلية بسعر أولي مرتفع.
- أضف خلية بها النسبة المئوية الأولية لمبيعات الكتب باهظة الثمن إلى العمود "استبدال القيم بالصفوف في ..." ثم انقر فوق "موافق".
نتيجة لذلك ، يتم ملء الجدول بأكمله بمبالغ الدخل المحتمل بشروط مختلفة لبيع البضائع.
تسريع العمليات الحسابية لأوراق العمل التي تحتوي على جداول البيانات
إذا كنت بحاجة إلى عمليات حسابية سريعة في جدول بيانات لا تؤدي إلى إعادة حساب المصنف بأكمله ، فهناك بعض الأشياء التي يمكنك القيام بها لتسريع العملية.
- افتح نافذة الخيارات ، حدد العنصر "الصيغ" في القائمة على اليمين.
- حدد العنصر "تلقائي ، باستثناء جداول البيانات" في قسم "الحسابات في المصنف".
- دعنا نعيد حساب النتائج في الجدول يدويًا. للقيام بذلك ، حدد الصيغ واضغط على المفتاح F.
أدوات أخرى لإجراء تحليل الحساسية
هناك أدوات أخرى في البرنامج لمساعدتك في إجراء تحليل الحساسية. يقومون بأتمتة بعض الإجراءات التي كان يجب القيام بها يدويًا.
- تكون وظيفة "تحديد المعلمة" مناسبة إذا كانت النتيجة المرجوة معروفة ، وتحتاج إلى معرفة قيمة إدخال المتغير للحصول على هذه النتيجة.
- "البحث عن حل" هو إضافة لحل المشاكل. من الضروري وضع حدود والإشارة إليها ، وبعد ذلك سيجد النظام الإجابة. يتم تحديد الحل بتغيير القيم.
- يمكن إجراء تحليل الحساسية باستخدام مدير السيناريو. توجد هذه الأداة في قائمة تحليل ماذا لو ضمن علامة التبويب البيانات. يستبدل القيم في عدة خلايا - يمكن أن يصل الرقم إلى 32. يقارن المرسل هذه القيم بحيث لا يضطر المستخدم إلى تغييرها يدويًا. مثال على استخدام مدير البرنامج النصي:
تحليل الحساسية لمشروع استثماري في Excel
تحليل ماذا لو مفيد بشكل خاص في المواقف التي تتطلب التنبؤ ، مثل الاستثمار. يستخدم المحللون هذه الطريقة لمعرفة كيف ستتغير قيمة سهم الشركة نتيجة للتغيرات في بعض العوامل.
طريقة تحليل حساسية الاستثمار
عند تحليل "ماذا لو" استخدم التعداد - يدوي أو آلي. نطاق القيم معروف ، ويتم استبدالها بالصيغة واحدة تلو الأخرى. والنتيجة هي مجموعة من القيم. اختر الرقم المناسب منهم. دعونا ننظر في أربعة مؤشرات يتم من أجلها تحليل الحساسية في مجال التمويل:
- صافي القيمة الحالية - محسوبة بطرح مبلغ الاستثمار من مبلغ الدخل.
- معدل العائد / الربح الداخلي - يشير إلى مقدار الربح المطلوب استلامه من الاستثمار في السنة.
- نسبة الاسترداد هي نسبة جميع الأرباح إلى الاستثمار الأولي.
- مؤشر الربح المخصوم - يشير إلى فعالية الاستثمار.
المعادلة
يمكن حساب حساسية التضمين باستخدام هذه الصيغة: التغيير في معلمة الإخراج في٪ / تغيير في معامل الإدخال في٪.
يمكن أن تكون معلمات الإخراج والإدخال هي القيم الموضحة سابقًا.
- تحتاج إلى معرفة النتيجة في ظل الظروف القياسية.
- نستبدل أحد المتغيرات ونراقب التغييرات في النتيجة.
- نحسب النسبة المئوية للتغير في كلا المعلمتين بالنسبة للشروط المحددة.
- نقوم بإدخال النسب المئوية التي تم الحصول عليها في الصيغة وتحديد الحساسية.
مثال على تحليل الحساسية لمشروع استثماري في Excel
من أجل فهم أفضل لمنهجية التحليل ، هناك حاجة إلى مثال. دعنا نحلل المشروع بالبيانات المعروفة التالية:
- املأ الجدول لتحليل المشروع عليه.
- نحسب التدفق النقدي باستخدام وظيفة OFFSET. في المرحلة الأولية ، التدفق يساوي الاستثمارات. بعد ذلك ، نطبق الصيغة: = IF (OFFSET (Number، 1؛) = 2؛ SUM (التدفق 1: التدفق 1)؛ SUM (التدفق 1: التدفق 1) + $B$ 5)قد تختلف تسميات الخلايا في الصيغة ، بناءً على تخطيط الجدول. في النهاية ، تضاف القيمة من البيانات الأولية - قيمة الإنقاذ.
- نحدد الفترة التي سيؤتي ثمارها المشروع. للفترة الأولية ، نستخدم هذه الصيغة: = في بعض الأحيان (G7: ز17 ؛ »<0). نطاق الخلايا هو عمود التدفق النقدي. لفترات أخرى ، نطبق هذه الصيغة: = الفترة الأولية + IF (التيار الإلكتروني الأول> 0 ؛ التيار الإلكتروني الأول ؛ 0). وصل المشروع إلى نقطة التعادل خلال 4 سنوات.
- نقوم بإنشاء عمود لأرقام تلك الفترات التي يدفع فيها المشروع.
- نحسب عائد الاستثمار. من الضروري عمل تعبير حيث يتم تقسيم الربح في فترة زمنية معينة على الاستثمار الأولي.
- نحدد عامل الخصم باستخدام هذه الصيغة: = 1 / (1 + قرص٪) ^ رقم.
- نحسب القيمة الحالية باستخدام الضرب - يتم ضرب التدفق النقدي في عامل الخصم.
- دعنا نحسب PI (مؤشر الربحية). القيمة الحالية بمرور الوقت مقسومة على الاستثمار في بداية المشروع.
- دعنا نحدد معدل العائد الداخلي باستخدام وظيفة IRR: = IRR (نطاق التدفق النقدي).
تحليل حساسية الاستثمار باستخدام ورقة البيانات
لتحليل المشاريع في مجال الاستثمار ، هناك طرق أخرى أكثر ملاءمة من جدول البيانات. يعاني العديد من المستخدمين من الارتباك عند تجميع الصيغة. لمعرفة اعتماد عامل واحد على التغييرات في الآخرين ، تحتاج إلى تحديد الخلايا الصحيحة لإدخال الحسابات وقراءة البيانات.
تحليل العوامل والتشتت في Excel مع أتمتة الحساب
تصنيف آخر لتحليل الحساسية هو تحليل العوامل وتحليل التباين. يحدد النوع الأول العلاقة بين الأرقام ، ويكشف النوع الثاني عن اعتماد متغير واحد على متغير آخر.
أنوفا في Excel
الغرض من هذا التحليل هو تقسيم تباين القيمة إلى ثلاثة مكونات:
- التباين نتيجة لتأثير القيم الأخرى.
- التغييرات بسبب علاقة القيم التي تؤثر عليه.
- تغييرات عشوائية.
لنقم بتحليل التباين من خلال "تحليل البيانات" في Excel. إذا لم يتم تمكينه ، فيمكن تمكينه في الإعدادات.
يجب أن يتبع الجدول الأولي قاعدتين: يوجد عمود واحد لكل قيمة ، ويتم ترتيب البيانات الموجودة فيه بترتيب تصاعدي أو تنازلي. من الضروري التحقق من تأثير مستوى التعليم على السلوك في الصراع.
- ابحث عن أداة تحليل البيانات في علامة التبويب البيانات وافتح نافذتها. في القائمة ، تحتاج إلى تحديد تحليل التباين أحادي الاتجاه.
- املأ سطور مربع الحوار. الفاصل الزمني للإدخال هو جميع الخلايا ، باستثناء الرؤوس والأرقام. تجميع حسب الأعمدة. نعرض النتائج على ورقة جديدة.
نظرًا لأن القيمة في الخلية الصفراء أكبر من واحد ، يمكن اعتبار الافتراض غير صحيح - لا توجد علاقة بين التعليم والسلوك المتعارض.
تحليل العامل في Excel: مثال
دعنا نحلل علاقة البيانات في مجال المبيعات - من الضروري تحديد المنتجات المشهورة والتي لا تحظى بشعبية. المعلومات الأولية:
- نحتاج إلى معرفة أي طلب على السلع زاد أكثر خلال الشهر الثاني. نقوم بتجميع جدول جديد لتحديد النمو والانخفاض في الطلب. يتم حساب النمو باستخدام هذه الصيغة: = IF ((الطلب 2 - الطلب 1)> 0 ؛ الطلب 2 - الطلب 1 ؛ 0). إنقاص الصيغة: = إذا (النمو = 0 ؛ الطلب 1- الطلب 2 ؛ 0).
- احسب نمو الطلب على السلع كنسبة مئوية: = إذا (النمو / النتيجة 2 = 0 ؛ النقص / النتيجة 2 ؛ النمو / النتيجة 2).
- دعنا نصنع مخططًا للوضوح - حدد نطاقًا من الخلايا وقم بإنشاء مدرج تكراري من خلال علامة التبويب "إدراج". في الإعدادات ، تحتاج إلى إزالة التعبئة ، ويمكن القيام بذلك من خلال أداة تنسيق سلسلة البيانات.
تحليل التباين ثنائي الاتجاه في Excel
يتم إجراء تحليل التباين باستخدام عدة متغيرات. ضع في اعتبارك هذا بمثال: تحتاج إلى معرفة مدى سرعة ظهور رد الفعل على صوت ذي حجم مختلف في الرجال والنساء.
- نفتح "تحليل البيانات" ، في القائمة التي تحتاج إلى إيجاد تحليل ثنائي الاتجاه للتباين دون التكرار.
- فاصل الإدخال - الخلايا التي تحتوي على بيانات (بدون رأس). نعرض النتائج على ورقة جديدة ونضغط على "موافق".
قيمة F أكبر من قيمة F ، مما يعني أن الأرضية تؤثر على سرعة رد الفعل على الصوت.
وفي الختام
في هذه المقالة ، تمت مناقشة تحليل الحساسية في جدول بيانات Excel بالتفصيل ، حتى يتمكن كل مستخدم من فهم طرق تطبيقه.