استفاده از تکنیک What-if Analysis در تصمیم گیری

شاید شما هم در شرایطی قرار گرفته اید که نیاز به تصمیم گریی سریع و درست داشته باشید و شاید برای رسیدن به نتیجه بارها از خود پرسیده اید، چه می شود، اگرمن در شرایطم تغییراتی را ایجادکنم و برای تصمیم گیری دچار سردرگمی شده اید؛ در اینجا شما با صرف زمان و انرژی بسیار زیادی اسیر بازی آزمون و خطا می شوید.

نگران نباشید ماکروسافت اکسل با کمک یکی از بیشمار ابزار قدرتمند خود به نام What if analysis این بازی زمانبر را برای شما انجام می دهد.

What-if Analysis شامل سه ابزار 

1- Data Table
2- Scenario Manager
3- Goal Seek
می باشد که در ادامه با یک مثال کاربردی به شرح هریک از آنها خواهیم پرداخت.

1- Data Table
فرض کنید با توجه به نیاز مالی خود خواهان دریافت وامی به مبلغ 100،0000،000 ریال هستیم. اکنون باید تصمیم بگیریم با توجه به شرایط خود از بین انبوهی از بانک ها، موسسات مالی و اعتباری و موسسات قرض الحسنه باشرایط متفاوت کدام یک را انتخاب کنیم در اینجا به یکباره با همان سردرگمی که به آن اشاره شد مواجه می شویم و بدلیل کمبود زمان نیاز به تصمیم گیری سریع پیدا خواهیم کرد و Data Table کوتاه ترین مسیر برای تصمیم گیری ما خواهد بود.
بسیار خوب شروع به حل مسئله می کنیم. برای حل این مسئلهexcel تابع کلیدی PMTرا پیشنهاد می دهد که باتوجه به مبلغ وام، نرخ بهره و مدت بازپرداخت، مبلغ قسط ماهیانه را برای ما محاسبه می کند.

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

حال نوبت به استفاده از تکنیک What-if Analysis می باشد. قبل از اینکار از ابتدای بازه نرخ بهره تا انتهای بازه دوره بازپرداخت را درگ می کنیم.  سپس از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Data Table را انتخاب می کنیم.

در پنجره Data Table در قسمت Row input cell آدرس سلول نرخ بهره سالیانه (E4) و در قسمت Column input cell آدرس سلول دوره بازپرداخت (E5) را داده و بر روی  OK کلیک می کنیم.             

در ادامه  آموزش اکسل تکنیک  قدرتمند What-if Analysis  امروز به دو ابزار   Scenario Manager و Goal Seek خواهیم پرداخت.

  Scenario Manager:

یکی از محدویت هایی که در ماکروسافت اکسل وجود دارد دو بعدی بودن این نرم افزار است بصورتی که هنگام استفاده از ابزار Data Table تنها می توان تغییرات را بروی دو متغیر تابع اعمال کرد.

 فرض کنید علاوه بر نرخ بهره و دوره باز پرداخت ما می خواهیم بدانیم چه می شود اگر مبلغ وام نیز کم و زیاد شود و تاثیر آن در جواب مسئله یعنی مبلغ قسط ماهانه چه خواهد شد. در این حالت نیز شرکت ماکروسافت پیش بینی های لازم را انجام داده و  ابزار سودمند Scenario Manager را معرفی کرده است.

برای حل مسئله ابتدا از سربرگ DATA قسمت DATA Tools قسمت What-if Analysis گزینه Scenario Manager را انتخاب می کنیم.

در پنجره باز شده ناحیه ای به نام Scenarios برای ایجاد سناریو با حالت های مختلف وجود دارد و تعدادی ابزار نیز در سمت راست پنجره برای مدیریت سناریوها قرار داده شده است. برای اضافه کردن سناریو گزینه Add… را انتخاب می کنیم.

 در پنجره Add Scenario بعد از انتخاب نام برای سناریو در قسمت Changing cells آدرس سلول هایی که برای تغییر مد نظر داریم را وارد می کنیم در این مثال ما آدرس سلول های E3 , E4 , E5 که در تابع ما بترتیب به متغیرهای نرخ بهره سالانه، دوره بازپرداخت و مبلغ وام اشاره دارد را وارد کرده ایم.

 بعد از آدرس دهی سلول های مورد نظر و تایید، در پنجره بعد نوبت به دادن مقادیر دلخواه به متغیر های انتخاب شده می رسد.

 بعد از تایید مقادیر، اولین سناریو را ایجاد شده است.به همین ترتیبی که گفته شد می توانید به هر مقداری که می خواهید سناریو اضافه کنید.

در Scenario Manager مانند Data Table محدودیت تعداد متغیر وجود ندارد ولی در عوض گزارشی بصورت ماتریسی نیز دریافت نخواهید کرد.

برای گزارش گیری از پنجره Scenario Manager بر روی گزینه Summery…  کلیک می کنیم در اینجا حق انتخاب دو نوع گزارش Scenario summery  و Scenario PivotTable Report  را داریم که اکسل هر گزارش را در شیتی جداگانه به ما می دهد.

در قسمت Result cells آدرس سلول پاسخ یعنی مبلغ قسط ماهانه را می دهیم.

 در نهایت فرمت گزارش ها بصورت زیر خواهد بودو شما می توانید براحتی بین حالت های مختلف وام گیری یک مورد را انتخاب کنید.

 Scenario Summery:

 Scenario PivotTable :

 Goal Seek :

اگر شما بخواهید با توجه به توان بازپرداخت خود وام بگیرید مثلا ماهیانه 3,000,000 ریال وبخواهید که متغیرها طوری تغییر کنند که دقیقا این مبلغ را هرماه بپردازید ماکروسافت اکسل ابزار Goal Seek را به شما پیشنهاد می کند.

شما کافیست مبلغ هدف را مشخص کرده و یک متغیر را اعلام کنید و Goal Seek متغیر را تا جایی که شما به مقدار هدفتان برسید تغییر می دهد. (ابزار Goal Seek تغییرات را تنها بر روی یک متغیر می تواند اعمال کند.) ما دراینجا مبلغ وام را به عنوان متغیر تعریف کرده ایم یعنی چه مقدار وام بگیریم تا هر ماه تنها مبلغ 3,000,000 ریال قسط بپردازیم.

سلول هدف (مبلغ قسط ماهانه) را در قسمت  Set cell، مقدار هدف را در قسمت  To value و متغیری که قرار است تغییر کند (مبلغ وام) را در قسمت By changing cell وارد می کنیم و با زدن Ok نتیجه دلخواه،