التبديل بين مجموعات مختلفة من القيم باستخدام السيناريوهات

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

إذا كان لدى العديد من الأشخاص معلومات معينة تريد استخدامها في السيناريوهات، يمكنك جمع المعلومات في مصنفات منفصلة، ثم دمج السيناريوهات من المصنفات المختلفة في مصنف واحد.

بعد الحصول على كل السيناريوهات التي تحتاج إليها، يمكنك إنشاء تقرير ملخص السيناريو الذي يتضمن معلومات من جميع السيناريوهات.

تتم إدارة السيناريوهات باستخدام معالج "إدارة السيناريوهات" من مجموعة "تحليل ماذا لو" ضمن علامة التبويب "بيانات".

هناك ثلاثة أنواع من أدوات تحليل ماذا لو تأتي مع Excel: السيناريوهات وجداول البيانات والبحث عن الأهداف. تأخذ السيناريوهات وجداول البيانات مجموعات من قيم الإدخال والمشروع للأمام لتحديد النتائج المحتملة. يختلف "البحث عن الهدف" عن "السيناريوهات" و"جداول البيانات" حيث يستغرق الأمر نتيجة ومشاريع للخلف لتحديد قيم الإدخال المحتملة التي تنتج تلك النتيجة.

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

بالإضافة إلى هذه الأدوات الثلاثة، يمكنك تثبيت الوظائف الإضافية التي تساعدك على إجراء تحليل ماذا لو، مثل الوظيفة الإضافية Solver. تعتبر "الوظيفة الإضافية Solver" مماثلة لـ "البحث عن الأهداف"، ولكنها قد تشمل مزيداً من المتغيرات. يمكنك أيضاً إنشاء التنبؤات باستخدام مقبض التعبئة والأوامر المتعددة المضمنة في Excel. للحصول على نماذج أكثر تقدماً، يمكنك استخدام الوظيفة الإضافية Analysis ToolPak‏.

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

على سبيل المثال، افترض أن أسوأ سيناريو لميزانية الحالة هو إجمالي الإيرادات بقيمة 50000 دولار أمريكي وتكلفة السلع المباعة بقيمة 13200 دولار أمريكي، مما يترك 36800 دولار أمريكي في إجمالي الربح. لتعريف هذه المجموعة من القيم كسيناريو، أدخل القيم أولا في ورقة عمل، كما هو موضح في الرسم التوضيحي التالي:

السيناريو - إعداد سيناريو باستخدام خلايا التغيير والنتيجة

تحتوي الخلايا المتغيرة على قيم تكتبها، بينما تحتوي خلية النتيجة على صيغة تستند إلى الخلايا المتغيرة (في خلية الرسم التوضيحي هذه B4 تحتوي على الصيغة =B2-B3 ).

يمكنك بعد ذلك استخدام مربع الحوار "إدارة السيناريو" لحفظ هذه القيم كسيناريو. انتقل إلى علامة التبويب "بيانات" > "تحليل ماذا لو" > "إدارة السيناريوهات" > إضافة.

الوصول إلى Scenario Manager من Data > Forecast؟ تحليل What-If

معالج Scenario Manger

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

إعداد سيناريو أسوأ الحالات

ملاحظة: على الرغم من أن هذا المثال يحتوي على خليتين متغيرتين فقط (B2 وB3)، يمكن أن يحتوي السيناريو على ما يصل إلى 32 خلية.

الحماية – يمكنك أيضا حماية السيناريوهات، لذلك في قسم الحماية، تحقق من الخيارات التي تريدها، أو قم بإلغاء تحديدها إذا كنت لا تريد أي حماية.

حدد "منع التغييرات" لمنع تحرير السيناريو عندما تكون ورقة العمل محمية.
حدد "مخفي" لمنع عرض السيناريو عندما تكون ورقة العمل محمية.

ملاحظة: تنطبق هذه الخيارات على أوراق العمل المحمية فقط. لمزيد من المعلومات حول أوراق العمل المحمية، راجع حماية ورقة عمل

افترض الآن أن سيناريو موازنة أفضل حالة هو إجمالي الإيرادات البالغ 150000 دولار أمريكي وتكلفة السلع التي تم بيعها بقيمة 26000 دولار أمريكي، مما يترك 124000 دولار أمريكي في إجمالي الربح. لتعريف هذه المجموعة من القيم كسيناريو، يمكنك إنشاء سيناريو آخر، وتسمية "أفضل حالة"، وتوفير قيم مختلفة للخلية B2 (150000) والخلية B3 (26000). لأن إجمالي الربح (الخلية B4) عبارة عن صيغة - الفرق بين الإيراد (B2) والتكلفة (B3) - لا تغير الخلية B4 لسيناريو أفضل حالة.

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

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

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

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

عند تجميع سيناريوهات مختلفة من مصادر مختلفة، يجب استخدام نفس بنية الخلية في كل مصنف من المصنفات. على سبيل المثال، قد تنتقل الإيرادات دائما في الخلية B2 وقد تنتقل النفقات دائما في الخلية B3. إذا كنت تستخدم بنيات مختلفة للسيناريوهات من مصادر مختلفة، فقد يكون من الصعب دمج النتائج.

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

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

قد يبدو تقرير ملخص السيناريو استنادا إلى سيناريو المثالين السابقين مماثلا لما يلي:

ستلاحظ أن Excel أضاف مستويات تجميع تلقائيا لك، مما سيؤدي إلى توسيع طريقة العرض وطيها عند النقر فوق المحددات المختلفة.

تظهر ملاحظة في نهاية تقرير الملخص توضح أن عمود "القيم الحالية" يمثل قيم الخلايا المتغيرة في وقت إنشاء "تقرير ملخص السيناريو"، وأن الخلايا التي تم تغييرها لكل سيناريو يتم تمييزها باللون الرمادي.

ملاحظات:

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