استفاده از مدیریت سناریو (Scenario Manager) در تحلیل حساسیت

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل که در فصل گذشته به بررسی فرمان Goal Seek پرداختیم، در این فصل به استفاده از مدیریت سناریو (Scenario Manager) در تحلیل حساسیت می پردازیم.

می‌توانید از ابزار مدیریت سناریو (Scenario Manager) برای اجرای تحلیل حساسیت با تغییر تا 32 سلول ورودی استفاده نمایید. با ابزار مدیریت سناریو، شما ابتدا مجموعه‌ای از سلول‌های ورودی را که می‌خواهید تغییر دهید معین می‌کنید. پس از آن سناریوی خود را نام‌گذاری نموده و برای هر سناریو مقدار هر سلول ورودی را وارد می‌کنید. در نهایت سلول‌های خروجی (که سلول‌های نتایج نیز نامیده می‌شوند) را که می‌خواهید دنبال نمایید را انتخاب می‌کنید. آنگاه ابزار مدیریت سناریو گزارش بسیار ارزشمندی حاوی ورودی‌ها و مقادیر خروجی سلول‌ها برای هر سناریو را ایجاد می‌نماید.

سؤالاتی که در این فصل پاسخ داده می‌شوند:

• تمایل دارم که بهترین، بدترین و محتمل‌ترین سناریوها را برای فروش یک اتومبیل با تغییر دادن مقادیر فروش سال اول، میزان رشد فروش سالیانه و قیمت فروش سال اول ایجاد نمایم. جدول داده‌ها برای تحلیل حساسیت به من اجازه می‌دهد که تنها یک یا دو ورودی را تغییر دهم بنابراین نمی‌توانم از جدول داده استفاده کنم. آیا اکسل ابزاری دارد که بتوان با استفاده از آن در تحلیل حساسیت بیش از دو ورودی را تغییر داد؟

پاسخ به سؤالات این فصل:

تمایل دارم که بهترین، بدترین و محتمل‌ترین سناریوها را برای فروش یک اتومبیل با تغییر دادن مقادیر فروش سال اول، میزان رشد فروش سالیانه و قیمت فروش سال اول ایجاد نمایم. جدول داده‌ها برای تحلیل حساسیت به من اجازه می‌دهد که تنها یک یا دو ورودی را تغییر دهم بنابراین نمی‌توانم از جدول داده استفاده کنم. آیا اکسل ابزاری دارد که بتوان با استفاده از آن در تحلیل حساسیت بیش از دو ورودی را تغییر داد؟

فرض کنید می‌خواهید با استفاده از مثال فصل 16 بنام ابزار حسابرسی و افزونه بررسی، این سه سناریوی زیر را در ارتباط باارزش خالص فعلی (NPV) یک ماشین ایجاد نمایید.

برای هریک از سناریوها می‌خواهید به ارزش خالص فعلی و سود پس از مالیات شرکت نگاهی بیندازید. کار انجام شده در این مورد در فایلی بنام NPVauditscenario.xlsx قرار دارد. تصویر 1-19 کاربرگ مدل (در کاربرگ Original Model موجود است) و تصویر 2-19 گزارش سناریو (که در کاربرگ Scenario Summary موجود است) را نشان می‌دهند.

تصویر 1-19 داده‌هایی که سناریوها بر اساس آنها ایجاد شده‌اند.

تصویر 2-19 گزارش موجزی از سناریوها

 برای اینکه تعریف کردن بهترین سناریوی ممکن را آغاز کنیم، در کاربرگ Original Model تبData را نمایان کرده و بعد در گروه Forcast در منوی What-If Analysis روی گزینه Scenacrio Manager یا مدیریت سناریو کلیک کنید. پس از آن روی دکمه Add‌ کلیک کرده و کادر محاوره‌ای Add Scenario را همان‌طور که در تصویر 3-19 نشان‌داده‌شده پرکنید.

تصویر 3-19 داده‌های ورودی برای بهترین سناریو

در قسمت نام سناریو عبارت Best را وارد کرده و سلول‌های C2:C4 را به‌عنوان سلول‌های متغیر انتخاب یا وارد کنید، این سلول‌ها حاوی مقادیری که سناریو را تعریف می‌کنند می‌باشند. پس از اینکه در کادر محاوره‌ای Add Scenario روی دکمه Ok‌ کلیک کردید، در کادر محاوره‌ای ظاهر شده Scenario Values مقادیر ورودی که بهترین سناریوی ممکن را تعیین می‌کنند را همان‌طور که در تصویر 4-19 نشان‌داده‌شده وارد کنید.

تصویر 4-19 تعیین مقادیر ورودی جهت بهترین سناریوی ممکن

 برای بهترین سناریوی ممکن عدد 2000 را به‌عنوان مقدار فروش سال یکم، عدد0.2 را به‌عنوان میزان رشد فروش و عدد 10 را به‌عنوان قیمت کالا در سال یکم وارد کرده‌ایم. با کلیک بر دکمه Add در کادر محاوره‌ای Scenario Values (که وقتی‌که سناریویی را ویرایش می‌کنید در دسترس نمی‌باشد) می‌توانید داده‌ها را برای محتمل‌ترین و بدترین سناریوها نیز وارد کنید. بعد از کلیک روی دکمه Add و واردکردن داده‌ها برای هر سه سناریو (بهترین، محتمل‌ترین و بدترین) در کادر محاوره‌ای Scenario Values روی دکمه Ok  کلیک می‌کنیم. حالا ابزار مدیریت سناریو همان‌طور که در تصویر 5-19 نشان‌داده‌شده سناریوهایی را که ایجاد کرده‌ایم را فهرست می‌کند.

وقتی‌که روی دکمه خلاصه در کادر محاوره‌ای Scenario Manager کلیک کنید می‌توانید در کادر Result Cells سلول‌هایی را که قرار است در گزارش سناریو نشان داده شوند را وارد نمایید. تصویر 6-19 به شما نشان می‌دهد چگونه در کادر محاوره‌ایScenario Summary مشخص کردم که می‌خواهم گزارش موجز سناریو سود پس از مالیات هرسال (سلول‌های B17:F17) به همراه درآمد خالص فعلی کل (سلولB19) را دنبال نماید.

تصویر 5-19 کادر محاوره‌ای Scenario Manader هر سناریویی را که شما تعیین کنید را نمایش می‌دهد.

تصویر 6-19 استفاده از کادر محاوره‌ای Scenario Summary برای انتخاب سلول‌های نتیجه برای گزارش خلاصه.

 از آنجا که سلول‌های نتیجه از بیش از دو محدوده ناشی می‌شود، محدوده‌های B17:F17 و B10 را با یک علامت ویرگول از هم جدا می‌کنیم. (می‌توان از کلید Ctrl برای انتخاب و واردکردن محدوده‌های متعدد استفاده کنیم) پس از اینکه گزینه Scenario Summary را (به‌جای گزینه PivotTable) انتخاب و روی دکمه Ok کلیک نمودید، اکسل خلاصه گزارش سناریوی بسیار زیبایی به وجود می‌آورد که در تصویر قبلی 2-19 آن را مشاهده نمودید.

در کاربرگ Scenario Summary توجه کنید که اکسل ستونی را برای مقادیری که از ابتدا در کاربرگ قرارداده شده بود به نام Current Values (مقادیر حاضر) اضافه می‌کند. بدترین سناریو ضرر می‌دهد (ضرری برابر با 13.345.75 دلار) در حالی که بهترین سناریو سودافزا است (سودی برابر با 226.892.67 دلار). ازآنجایی‌که قیمت واحد بدترین سناریو کمتر از هزینه‌های متغیر است، بنابراین بدترین سناریو هرسال دچار ضرر مالی می‌شود.

یادآوری‌ها

• گزینه Scenario PivoTable Report در کادر محاوره‌ای Scenario Summary نتایج سناریو را به فرمت جداول پیوت (جداول محوری) نمایش می‌دهد.
• فرض کنید در کادر محاوره‌ای Scenario Manager سناریویی انتخاب نموده و روی دکمه Show یا نمایش کلیک کرده‌اید. مقادیر سلول‌های ورودی برای سناریوی انتخابی در کاربرگ نمایان می‌شوند و اکسل تمامی فرمول‌ها را باز محاسبه می‌نماید. این ابزار برای ارائه یک نمایش اسلاید از سناریوی شما بسیار کاربردی است.
• ایجاد سناریوهای فراوان با ابزار مدیریت سناریو کمی مشکل است چراکه نیاز است مقادیر هر سناریوی جداگانه در آن وارد شود. شبیه‌ سازی مونت‌کارلو (فصل 77 به نام مقدمه‌ای بر متد شبیه‌سازی مونت‌کارلو) کار را برای ایجاد سناریوهای فراوان راحت می‌کند. با استفاده از متد شبیه‌سازی مونت‌کارلو می‌توانید اطلاعاتی مثل احتمال اینکه ارزش فعلی خالص جریان نقدی پروژه‌ای غیر منفی باشد را پیدا کنید، این سنجش بسیار مهم است چراکه در واقع احتمالی است که پروژه به شرکت ارزش اضافه نماید.
• کلیک کردن بر نشان منها (سمت چپ ردیف اعداد) در ردیف 5 از خلاصه گزارش سناریو باعث ناپدید شدن سلول‌های گمانه شده و تنها نتایج را نشان می‌دهد. کلیک برنشان جمع باعث برگشت گزارش به حالت اولیه می‌شود.
• فرض کنید فایلی را برای عده زیادی افراد می‌فرستید و هر فرد سناریوی خود را به آن اضافه می‌کند. بعد از اینکه هر فرد فایل حاوی سناریوها را به شما برگرداند، می‌توانید تمام سناریوها را در یک کاربرگ ادغام کنید. برای انجام این کار کاربرگ هریک از اشخاص را باز کنید و در کاربرگ اولیه بر روی دکمه Merge (ادغام) در کادر محاوره‌ای Scenario Manager کلیک کنید. بعد کاربرگ‌های انتخاب شده حاوی سناریوهایی را که می‌خواهید در هم ادغام کنید را انتخاب نمایید. اکسل تمامی سناریوهای موجود را در کاربرگ اولیه ادغام می‌نماید.

مسئله‌های این فصل:

سناریوی بهترین حالت موجود را حذف کرده و سناریوی دیگری را اجرا نمایید.

سناریویی به نام High price اضافه کنید که در طی سال اول قیمت‌ها برابر با 15 دلار و دو ورودی دیگر در بهترین مقادیر ممکن باشند.

برای مثال دکه لیموناد فروشی در فصل 17 بنام تحلیل حساسیت با جداول داده از ابزار مدیریت سناریو استفاده کنید تا گزارشی حاوی سود سناریوهای زیر را نمایش دهد:

برای مثال پرداخت قسط وام در فصل 17 از ابزار مدیریت سناریو استفاده کنید تا گزارشی تهیه کنید که پرداخت‌های ماهیانه برای سناریوهای ذیل را در جدولی نمایش دهد.