آنالیز حساسیت در اکسل — تصویری و گام به گام

احتمالا برای ایجاد لیست‌ها و مرتب‌سازی داده‌ها از اکسل استفاده کرده‌اید. هر چند در اکثر مواقع، اکسل را برای انجام فرمول‌نویسی و محاسبات سطری و ستونی به کار می‌بریم، ولی ابزارهای مناسب و البته کاربردی در اکسل وجود دارد که بخصوص برای آنالیز حساسیت و ایجاد سناریوهای مختلف برای حل یک مسئله به کار گرفته می‌شوند. هدف از ارائه این مطلب آشنایی با روش آنالیز حساسیت در اکسل به کمک سه روش مختلف است. در ابتدا ابزار «جستجوی هدف» (Goal Seek) را مرور کرده و سپس از ساختار «جدول داده» (Data Tables) برای تحلیل یا آنالیز حساسیت در اکسل استفاده می‌کنیم. در انتها نیز با «سناریو» (Scenario) آشنا شده و یک مسئله واقعی را حل خواهیم کرد.

فرض بر این است که با اکسل تا حدودی آشنایی دارید. ولی برای کسب اطلاعات بیشتر پیشنهاد می‌شود،‌ مطالب مرتبط با این متن مانند ایجاد و رسم جدول در اکسل — به زبان ساده و آموزش اکسل — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی را مطالعه کنید. همچنین خواندن مطالب سیمپلکس در اکسل — راهنمای کاربردی و جدول محوری (Pivot Table) در اکسل ۲۰۱۹ نیز خالی از لطف نیست.

آنالیز حساسیت در اکسل

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

فیلم آموزش مدل سازی مالی با اکسل – تحلیل پروژه های اقتصادی و سبد سهام و سرمایه گذاری در فرادرس

کلیک کنید

تجزیه و تحلیل یا «آنالیز حساسیت» (Sensitivity Analysis) تعیین می‌کند که چگونه مقادیر مختلف یک متغیر مستقل بر یک متغیر وابسته تحت مجموعه‌ای از مفروضات تأثیر می‌گذارد. به عبارت دیگر، تجزیه و تحلیل حساسیت روشی برای بررسی و مطالعه چگونه تاثیر منابع مختلف (در محیط مطلق یا عدم اطمینان) در یک مدل ریاضی است. این تکنیک در حوزه‌هایی استفاده می‌شود که با یک یا چند متغیر ورودی سرکار داشته و بخواهیم رفتار یک تابع یا رابطه با براساس آن‌ها بسنجیم.

این روش معمولاً توسط تحلیل گران مالی و اقتصاددانان مورد بهره‌برداری قرار گرفته و به گاهی به آن (What-if Analysis) نیز گفته می‌شود. آنالیز حساسیت در اکسل در برگه Data و بخش Forecast با نماد What-if Analysis قابل دسترس است. زمینه‌های کاربردی تحلیل حساسیت را در فهرست زیر مشخص کرده‌ایم.

• تجزیه و تحلیل حساسیت تعیین می‌کند که چگونه مقادیر مختلف یک متغیر مستقل بر یک متغیر وابسته خاص تاثیر گذار است. این تحلیل با توجه به قیدهایی که برای هر یک از متغیرهای مستقل یا وابسته فرض کرده‌ایم، صورت می‌گیرد و پاسخ باید در این محدودیت‌ها صدق کند.
• از این مدل به عنوان تجزیه و تحلیل پارامترهای مدل‌های آماری یا اقتصادی، استفاده می‌کنند که توسط شبیه‌سازی صورت می‌گیرند.
• از تجزیه و تحلیل حساسیت می‌توان برای پیش بینی قیمت سهام شرکت‌های سهامی عام در بورس یا چگونگی تأثیر نرخ بهره بر قیمت اوراق بهادار استفاده کرد.
• تجزیه و تحلیل حساسیت امکان پیش بینی با استفاده از داده‌های زمانی (مانند سری زمانی) و واقعی را فراهم می‌کند.

در تصویر زیر، مسیر دسترسی به ابزارهای گروه What-if Analysis را مشاهده می‌کنید.

تجزیه و تحلیل حساسیت بر اساس متغیرهایی ورودی صورت می‌گیرد که روی متغیرهای خروجی تأثیر می‌گذارند. برای مثال، در یک مدل مالی می‌توان به کمک ابزارهای تحلیل حساسیت، براساس قیمت سهام و مقدار EPS، به شکلی سود یا میزان اختلاف سرمایه‌گذاری از برداشت یا دریافتی را نشان داد. تجزیه و تحلیل حساسیت این متغیرها را جدا کرده و سپس دامنه نتایج یا متغیرهای خروجی را ثبت می‌کند. از طرف دیگر، با استفاده از تجزیه و تحلیل سناریو، تحلیلگر بازار می‌تواند رفتارهای مختلف مانند سقوط بازار سهام یا تغییر در سودآوری صنایع را تعیین کند. به این منظور کافی است، متغیرهای درون مدل را تغییر داده تا نتیجه در سناریوها همسو با این تغییرات، محاسبه شود. علاوه بر همه این‌ها، تحلیلگرها به کمک ابزارهای تصویری اکسل می‌توانند نمودارهای مختلفی را هم تهیه کرده و به کمک آن‌ها، مصورسازی داده‌ها را به همراه تحلیل حساسیت نشان دهند. به این ترتیب از هر جنبه‌ای، اشراف کامل به داده‌ها و وضعیت مسئله برای مقادیر مختلف متغیرها بوجود می‌آید.

چگونه آنالیز حساسیت در اکسل را اجرا کنیم؟

ابزارهای What-If Analysis شامل فرآیندهای تغییر مقادیر در سلول‌ها برای دیدن تأثیر این تغییرات بر نتیجه فرمول‌های کاربرگ‌ها است. سه نوع ابزار برای تجزیه و تحلیل فرمول‌ها و محاسبات، در گروه ابزارهای What-If Analysis در اکسل وجود دارد.

• جستجوی هدف (Goal Seek)
• جدول داده‌ها (Data Table)
• سناریوها (Scenarios)

فیلم آموزش تحلیل داده های کسب و کار با اکسل Excel در فرادرس

کلیک کنید

توجه داشته باشید که جستجوی هدف، جدول داده‌ها و سناریوها، مجموعه‌ای از مقادیر ورودی را تعیین می‌کنند و نتایج مربوط به متغیر وابسته را به کمک آن‌ها مشخص و محاسبه می‌نمایند. جدول داده فقط با یک یا دو متغیر ورودی کار می‌کند، اما می‌تواند مقادیر مختلفی را برای آن‌ها بپذیرد. یک سناریو می‌تواند چندین متغیر داشته باشد، اما حداکثر 32 مقدار ورودی را در خود جای می‌دهد. ولی Goal Seek متفاوت از سناریوها و جدول داده‌ها است زیرا به عنوان ورودی یک متغیر را می‌گیرد و مقدار یک متغیر خروجی را تعیین می‌کند بنابراین بسیار ساده‌تر بوده و فراگیری آن آسان‌تر است.

علاوه بر این سه ابزار، می‌توانید افزونه‌هایی مانند افزونه Solver را نصب کنید که به شما در انجام تجزیه و تحلیل چند متغیره کمک می‌کند. افزونه Solver مشابه Goal Seek است، اما می‌تواند متغیرهای بیشتری را در خود جای دهد و برای آن‌ها نیز محدودیت قائل شود. همچنین می‌توانید با استفاده از برگه Forecast و دستورات مختلفی که در اکسل تعبیه شده، پیش‌بینی‌ها را برای داده‌های زمانی ایجاد کنید.

در ادامه به این سه تکنیک پرداخته و به کمک آن‌ها آنالیز حساسیت در اکسل را به همراه مثال‌هایی، آموزش خواهیم داد.

آنالیز حساسیت در اکسل به کمک ابزار Goal Seek

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

فرض کنید یک فرمول (سلول وابسته) دارید و می‌خواهید با تغییر سلول پیش‌نیاز، مقدار آن را به عددی که در نظر دارید برسانید. شاید تغییر دادن مکرر مقدار سلول پیش‌نیاز  یک راه حل باشد ولی انتخاب مقدارهای مناسب و جایگذاری در سلول پیش‌نیاز و امتحان کردن نتیجه در سلول وابسته، زمان‌بر و طولانی است. ابزار Goal Seek این کار را برای شما انجام می‌دهد. در حقیقت Goal Seek‌ با مقداردهی متوالی به سلول پیش‌نیاز، سعی می‌کند تا سلول وابسته را به مقداری که شما تعیین کرده‌اید برساند. به یک مثال در این زمینه دقت کنید.

استفاده از Goal Seek‌ برای تعیین درصد سود از فروش

فرض کنید که شما یک فروشنده هستید و می‌خواهید کالای خود را با ۱۰٪ سود بفروش رسانید. از آنجایی که مالیات براساس مبالغ فروش محاسبه می‌شود، باید قیمت‌گذاری را به شکلی انجام دهید که سود حاصل از فروش کالا برابر با ۱۰٪ باشد. شاید از خود بپرسید که اگر به مبلغ خرید، ۱۰٪ اضافه کنیم، به مبلغ فروش خواهیم رسید. ولی به کمک ابزار Goal Seek می‌خواهیم این کار را انجام دهیم و درصد مناسب برای ضرب در قیمت خرید را بدست آوریم.

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

کاربرگی که در تصویر زیر مشاهده می‌کنید به این منظور تهیه شده است. البته برای راحتی خوانندگان، فایل اصلی این کاربرگ اطلاعاتی به صورت یک فایل فشرده از اینجا قابل دریافت است. کافی است پس از خارج کردن آن از حالت فشرده، فایل را در اکسل بارگذاری کرده و به کار گیرید. در این کارپوشه، پنج کاربرگ (Worksheet) وجود دارد که سه کاربرگ اول آن مربوط به ابزارهای Goal Seek، Data Table و سناریو بوده و دو کاربرگ آخر، خروجی‌های مربوط به ابزار سناریو را نشان می‌دهند.

فرض کنید مبلغ خرید کالا در سلول B2 نوشته شده و درصد سود مورد انتظارتان نیز از فروش در سلول‌های B3 محاسبه شده است. به تصویر زیر که اطلاعات کاربرگ اکسل را برای این منظور نشان می‌دهد، توجه کنید.

همانطور که در تصویر مشاهده می‌کنید، در ستون سوم، فرمول‌های مربوط به هر یک از سلول‌های محاسباتی نوشته شده است. برای محاسبه قیمت فروش (یک سلول وابسته) از فرمول زیر کمک گرفته‌ایم.

(B3 + 1) * B2 =

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

‌B5 / B4 =

همانطور که مشاهده می‌کنید درصد سود بدست آمده تقریبا برابر با ۹٪ است که با درصدی که انتظار داشتیم یعنی ۱۰٪، تفاوت زیادی (حدود یک درصد) دارد. می‌خواهیم با استفاده از ابزار Goal Seek، «درصد افزایش» را به شکلی تغییر دهیم که مقدار «درصد سود»‌برابر با مقدار مورد انتظارمان یعنی ۱۰٪ شود.

از مسیری که در قبل به آن اشاره کردیم، پنجره Goal Seek را ظاهر می‌کنیم. البته بهتر است ابتدا سلولی را که به عنوان سلول وابسته می‌شناسید، انتخاب کنید. توجه داشته باشید که این سلول همان فرمول است که می‌خواهید مقدار آن را تعیین کنید. در مسئله ما این سلول همان «درصد سود»‌ یا سلول B6 است.

همانطور که در تصویر بالا می‌بینید، در پنجره Goal Seek، سه پارامتر وجود دارد. در بخش اول پارامتر Set cell، به آدرسی از کاربرگ اشاره دارد که قرار است مقدار آن به عنوان سلول هدف، تعیین شود. باید توجه داشت که این سلول، باید حاوی فرمول بوده و وابسته به سلولی باشد که در بخش By changing cell معرفی می‌کنید. پارامتر دوم یعنی To value، مقداری است که باید مقدار سلول تعیین شده در Set cell به آن برسد. همچنین بخش انتهایی هم به آدرسی اختصاص دارد که به عنوان پیش‌نیاز سلول تعیین شده در Set cell نقش داشته است. مشخص است که این آدرس به صورت مرجع مطلق در پنجره Goal Seek درج شده است.

با فشردن دکمه OK، مقدار دهی از مجموعه اعداد برای پارامتر آخر در اکسل صورت گرفته و این کار تا زمانی که سلول هدف (در اینجا B6) به مقدار مربوط به To value (‌در اینجا ۰٫۱ یا ۱۰٪) برسد، ادامه می‌یابد.

پس از طی شدن مراحل محاسباتی، پاسخ بدست آمده که برابر با ۱۱٪ است. پس به این ترتیب متوجه می‌شویم که برای رسیدن به درصد سودی برابر با ۱۰٪، باید مقدار «درصد افزایش» برابر با ۱۱٪ باشد و با توجه به این درصد، به قیمت خرید باید ۱۱ تومان اضافه کنیم و قیمت فروش برابر با ۱۱۱٫۱ محاسبه می‌شود. نتیجه نهایی را در تصویر زیر مشاهده می‌کنید. در صورتی که دکمه OK‌ را کلیک کنید، پاسخ بدست آمده جایگزین سلول پیش‌نیاز می‌شود. در این حالت می‌گویم که پاسخ مسئله همگرا شده است. ولی با فشردن دکمه Cancel، همه مقادیر به حالت قبل از اجرای Goal Seek بر می‌گردند.

در صورتی که رسیدن به پاسخ مناسب، طولانی باشد، دکمه‌های Step و Pause نیز روشن خواهند شد. با انتخاب دکمه Pause، عملیات مقدار دهی، متوقف شده و با دکمه Step، گام به گام سلول پیش‌نیاز مقدار خواهد گرفت و نتیجه سلول وابسته تغییر خواهد کرد.

نکته: ممکن است که محاسبات صورت گرفته برای تعیین سلول پیش‌نیاز، همگرا نباشد و Goal Seek قادر نباشد سلول یا فرمول هدف را به درستی تعیین کند. در این حالت با پیغامی به شکل زیر مواجه خواهید شد. برای اینکه چنین حالتی را بوجود آورید، می‌توانید مقدار یک سلول را به توان ۲ برسانید و از اکسل بخواهید که با تغییر مقدار پیش‌نیاز، حاصل مربع آن را یک مقدار منفی کند. در تصویر زیر این مقدار ۵- در نظر گرفته شده و محاسبات همگرا نشده‌اند. از کوچکترین تا بزرگترین مقدار ممکن در اکسل برای سلول پیش‌نیاز در نظر گرفته شده ولی نتیجه‌ای حاصل نشده است. عبارت May not have found a solution این موضوع را نشان می‌دهد.

جدول داده و اجرای آنالیز حساسیت در اکسل

برای دیدن تأثیرات یک یا دو متغیر روی فرمول، از جدول داده (Data Table) استفاده کنید. در این حالت اغلب با مسئله یا فرمولی (سلول وابسته) مواجه هستیم که برای محاسبه آن از یک یا دو متغیر (سلول‌های مستقل) استفاده می‌شود که ممکن است مقادیر مختلفی بگیرند.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

برای ارزیابی یا محاسبه نتایج مختلف به ازاء مقادیر متفاوت این دو متغیر از «جدول داده» استفاده می‌شود. این کار به نوع تحلیل یا آنالیز حساسیت در اکسل نیز محسوب می‌شود. به این ترتیب همه محاسبات را در یک ساختار جدولی مشاهده کرده و هم نسبت به بهترین موقعیت مقادیر دو متغیر تصمیم‌گیری می‌کنیم.

استفاده از جداول داده، بررسی طیف وسیعی از محاسبات را در یک نگاه آسان می‌کند. از آنجا که فقط روی یک یا دو متغیر تمرکز دارید، نتایج به راحتی خوانده می‌شوند و به صورت جدول ظاهر و به نمایش گذاشته خواهند شد. اگر پیش‌نیازهای فرمول یا مقدارهای سطر یا ستون متغیر وابسته را تغییر دهید، به طور خودکار محاسبات به روز شده و نتیجه در سلول‌های وابسته نمایش داده می‌شود. برای مثال به یک مسئله پرداخت وام می‌پردازیم. در این مثال، به ازاء پارامترهای متفاوت تعیین اقساط بازپرداخت وام، بهترین وضعیت را انتخاب و به کار می‌گیریم.

تحلیل و آنالیز حساسیت در اکسل برای یک وام

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

روال کار برای استفاده از Data Table در این حالت، به صورت زیر است.

• ابتدا مسئله را برای یک حالت خاص حل می‌کنیم. این کار را در سلول B11 و براساس سلول‌های پیش‌نیاز که در B8 و B9 نوشته شده‌اند، انجام داده‌ایم. فرمولی که براساس تابع PMT نوشتیم،‌ به صورت زیر است. توجه داشته باشید، چون مبلغ قسط را باید برای هر ماه محاسبه کنیم، سود سالانه را بر ۱۲ تقسیم کرده‌ایم. از طرفی مبلغ وام نیز در سلول B10 نوشته شده که البته در فرمول به کار رفته ولی در جدول داده، مورد نظرمان نیست.

=PMT ( B8/12, B9, B10)

• ناحیه‌ای از کاربرگ را به صورت یک جدول می‌سازیم که در سطر اولش مقادیر مختلف و مورد نیازمان برای یکی از سلول‌های پیش‌نیاز (ستون B-درصد سود بانکی) قرار گرفته و در ستون‌ اول آن (سطر دوم- دوره بازپرداخت) نیز برای سلول پیش‌نیاز بعدی مقدار دهی انجام داده‌ایم.
• فرمول مربوط به محاسبه اصلی را به کمک یک تساوی، در محل تقاطع سطر و ستون‌های این جدول، ثبت می‌کنیم. پس با توجه به مسئله ما، سلول B2 هدف قرار گرفته و به عنوان فرمول یدکی (در کنار سلول «مقدار جاری»)، در آن فرمولی به صورت زیر می‌نویسیم.

= B11

• کل ناحیه جدول شامل سطر و ستون‌های مقادیر پیش‌نیاز برای جدول داده را انتخاب کنید. به خاطر داشته باشید که سلول B2 که فرمول یدکی در آن نوشته شده نیز باید انتخاب شده باشد. این کار در تصویر زیر صورت گرفته است. تمامی سلول‌های B2 تا E6 به این ترتیب انتخاب شده‌اند.

• از برگه Data و قسمت Forecast دکمه What-if Analysis را انتخاب کرده و دستور Data Table را انتخاب می‌کنیم.
• تنظیمات پنجره ظاهر شده را مطابق با تصویر زیر انجام می‌دهیم. توجه داشته باشید که پارامتر Row Input Cell به سلولی اشاره می‌کند که پیش‌نیاز آن در فرمول اصلی براساس سطر جدول داده (Data Table) مقدار دهی می‌شود. همچنین پارامتر Column input cell نیز مربوط به سلولی از فرمول اصلی (B11) است که با مقادیر ستون جدول داده پرخواهند شد.
  

  

• با فشردن دکمه OK، تمامی سلول‌های درون جدول داده، با مقادیری متناسب با فرمول مربوط به سلول B2 (یا در حقیقت B11) پر خواهند شد و پارامترهای محاسباتی به صورت تقاطعی از سطر و ستون بدست می‌آیند.

توجه داشته باشید که با فشردن دکمه Cancel ایجاد جدول داده، لغو می‌شود. علامت ? در نوار عنوان پنجره Data Table نیز راهنمای کار با این ابزار را در اکسل نمایش می‌دهد. نتایج مربوط به این عملیات در تصویر زیر قابل مشاهده است. به فرمولی که به صورت برداری در سلول‌های جدول داده ثبت شده، توجه کنید. تابع Table به همراه علامت‌های { , } نشانگر یک تابع برداری است. چنین حالتی باعث می‌شود که کل ناحیه C3 تا E6 یک بردار (یا ماتریس) شده و به صورت یک شئی واحد در نظر گرفته شود.

همانطور که می‌بینید، هر چه تعداد دوره‌های بازپرداخت بیشتر و سود کمتر باشد، مبلغ پرداخت ماهانه قسط کمتر است. همچنین با افزایش سود بانکی، وامی به صرفه است که تعداد اقساط کمتری داشته باشد. برای مثال یک وام، با سود ۲۰٪ و اقساط ۱۲ ماهه، به مبلغ یک میلیون ریال، بازگشتی با مبلغ حدود ۱٫۱ میلیون ریال خواهد داشت در حالیکه همین وام با تعداد اقساط ۳۶ ماهه و سود ۲۰٪، مبلغی حدود ۳٫۳ میلیون ریال را به عنوان مبلغ بازگشت وام معرفی می‌کند. برای محاسبه هر یک از این اعداد، کافی است مبلغ قسط ماهانه را در دوره‌های بازپرداخت ضرب کنید.

92,634 × 12 ≅ 1,11۱,614

37,163 × 36 ≅ 3,334,842

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

نکته: از آنجایی که جدول داده، یک هویت واحد را مشخص می‌کند، قادر به پاک کردن یک سلول به تنهایی نیستند. برای حذف این جدول،‌باید همه سلول‌های آن را انتخاب کرده سپس آن را حذف کنید.

سناریو و آنالیز حساسیت در اکسل

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

به عنوان مثال، فرض کنید دو سناریو بودجه دارید. بدترین حالت و بهترین حالت. می توانید با استفاده از ابزار مدیریت سناریو (Scenario Management) هر دو سناریو را در یک کاربرگ اکسل ایجاد کنید و سپس بین آن‌ها جابجا شوید.

برای هر سناریو، سلول‌های پیش‌نیاز فرمول یا سلول وابسته، تغییر یافته و مقادیری را که باید برای این سناریو مورد نیاز است را تعیین کنید. وقتی بین سناریوها جابجا می‌شوید، سلول وابسته یا همان فرمول، تغییر می‌کند تا وابستگی بین سلول پیش‌نیاز و وابسته را مشخص کند. برای روشن شدن موضوع یک مسئله آنالیز حساسیت با اکسل را با این ابزار حل می‌کنیم.

حل مسئله میزان سرمایه‌گذاری در بورس

در بورس اوراق بهادار تهران، سهام شرکت‌های زیادی معامله می‌شود. بهتر است قبل از خرید یا فروش سهام، یک تحلیل یا آنالیز حساسیت در اکسل اجرا کنیم و بین مثلا دو یا چند وضعیت خرید سهم، دست به انتخاب بزنیم. البته مشخص است که این مثال، ساختگی بوده و مبنای کاملی برای تصمیم‌گیری نیست. به منظور تعیین پارامترها و همچنین میزان درآمد هر سهم و مقایسه آن‌ها از سناریو در اکسل استفاده می‌کنیم.

یک نماد بورسی به نام‌ a وجود دارند که می‌خواهیم سهام آن‌ را خریداری کنیم. از طرفی به EPS یا میزان سود پرداختی هر سهم این شرکت هم توجه داریم و با توجه به هزینه خرید و تعداد سهام با مقادیر مختلف، می‌خواهیم سود حاصل از خریداری سهام هر یک از موقعیت‌ها را با توجه به سود دریافتی در پایان سال مالی، مقایسه کنیم. تعداد سهم و همچنین حداکثر قیمت خریداری سهم، متغیرهایی هستند که می‌خواهیم با تغییر آن‌ها، نسبت به انتخاب موقعیت خریداری مناسب، اقدام کنیم. کاربرگ اکسلی که در تصویر زیر مشاهده می‌کنید، به این منظور تهیه شده است.

دو قیمت برای خرید این سهام در نظر است. در یک حالت، کف قیمت را در نظر گرفته‌ایم که 50۰ ریال است. از طرفی حداکثر قیمت نیز ۱۰۰۰ ریال است. با توجه به بودجه‌ای که در نظر گرفته‌ایم، یعنی ۵۰۰۰۰ ریال، می‌خواهیم بهترین موقعیت خرید را مشخص کنیم. البته می‌دانیم که EPS یا سود اختصاصی به هر سهم برای این شرکت نیز ۱۵ ریال بوده.

فرمولی که در قسمت «سود نهایی» نوشته شده، براساس قیمت خرید و سود هر سهم است. در اینجا سود نهایی را به شکلی براساس تفاضل سود هر سهم (EPS) از قیمت خرید محاسبه کرده‌ایم. البته این کار را به وسیله‌های فرمول‌های دیگری نیز می‌توان انجام داد. ولی ما برای درک راحت‌تر و فراگیری نحوه استفاده از سناریو، از این فرمول ساده استفاده کرده‌ایم.

= B2* C2 - D2*B2

اگر تعداد سهم خریداری شده را در قیمت هر سهم ضرب کرده و از حاصل ضرب سود (EPS) در تعداد سهم کم کنیم، سود نهایی حاصل می‌شود. می‌خواهیم به ازاء مقدار مناسب برای قیمت خرید حداقلی (یعنی 500) و خرید تعداد 100 سهم و از طرفی خرید با حداکثر قیمت (یعنی 1000) و تعداد سهام ۵۰ تایی، سناریوها را بسازیم. به این منظور گام‌های زیر را برمی‌داریم.

• با انتخاب سلول E2 به عنوان سلول وابسته، آغاز می‌کنیم.
• از مسیر مربوط به تحلیل حساسیت به دستور Scenario Manager می‌رسیم و آن را اجرا می‌کنیم.
• در پنجره‌ای که به شکل زیر ظاهر می‌شود، دکمه Add را کلیک می‌کنیم.

• یک نام برای سناریو اول (مثلا در اینجا خرید حداقلی) در بخش Scenario Name انتخاب می‌کنیم.
• سلول‌های پیش‌نیاز برای این سناریو را در بخش Changing Cells تعیین کرده و دکمه OK را می‌زنیم. یک سناریو می‌تواند حداکثر 32 مقدار مختلف داشته باشد، اما شما می‌توانید بیش از یک سناریو هم برای مسئله خود بسازید.

• مقدارهای مربوط به سلول‌های پیش‌نیاز را که در بخش قبلی در کادر Changing cells مشخص کرده‌ بودیم را در کادرهای مربوطه در پنجره Scenario Values وارد می‌کنیم. از آنجایی که برای هر یک از این سلول‌ها، عمل نام‌گذاری را انجام داده‌ایم، این آدرس‌ها به همراه نامشان مشخص شده است. در پایان دکمه OK را کلیک می‌کنیم.

• دوباره دکمه Add را کلیک کرده و تنظیمات مربوط به سناریو دوم (مثلا با نام خرید حداکثری) را مطابق با تصویر زیر ایجاد می‌کنیم.

• با فشردن دکمه OK و بازگشت به پنجره اصلی سناریو، کار ایجاد سناریوها پایان می‌یابد.

حالی کافی است هر یک از سناریوها را انتخاب کنید و دکمه Show را کلیک کنید. مقادیر مربوط به سلول‌های پیش‌نیاز که در آن سناریو، تعیین کرده‌اید، جایگزین شده و مقدار سلول وابسته محاسبه می‌شود. برای مثال اگر سناریو «خرید حداقلی» را انتخاب کنید، نتیجه محاسبات سلول E2 در کاربرگ هم تغییر کرده و با توجه به مقادیر جایگزین شده برای سلول‌های B2, C2، محاسبات صورت می‌گیرد. با انتخاب سناریو «خرید حداکثری» نیز کاربرگ به روز شده و مقادیر جدید جایگزین می‌شوند.

دکمه‌های دیگری نیز در پنجره Scenario Manager وجود دارد. با انتخاب سناریوهای ساخته شده، دکمه‌های Delete و Edit و حتی Summary فعال می‌شوند. واضح است که Delete، سناریوی انتخابی را حذف و Edit آن را ویرایش می‌کند. همچنین Summary یک کاربرگ جدید ساخته و مقادیر مختلف در سناریوها را به همراه مقدار سلول وابسته نشان می‌دهد. این ویژگی برای مقایسه همزمان چندین موقعیت بسیار مناسب است. همچنین در بخش Comment نیز اطلاعاتی در مورد تاریخ و تغییر سناریو دیده می‌شود.

توجه داشته باشید که گزارش سناریو به طور خودکار مجدداً محاسبه نمی‌شود. اگر مقادیر یک سناریو را تغییر دهید، این تغییرات در گزارش خلاصه قبلی نشان داده نمی‌شوند. در عوض، شما باید یک گزارش خلاصه (Summary Report) جدید ایجاد کنید.

این بار با فشردن دکمه Summary گزارش سناریو را هم ایجاد می‌کنیم. در تصویر زیر پنجره تنظیم‌های این دستور را مشاهده می‌کنید. توجه داشته باشید که در قسمت Result cells، به سلول‌هایی وابسته اشاره کنید. همچنین انتخاب Scenario summary باعث ایجاد کاربرگ نتایج شده در حالیکه Scenario PivotTable Report یک جدول محوری (PivotTable) برای مقایسه سناریوها (با مقادیر مختلف سلول‌های پیش‌نیاز) به همراه مقدار سلول‌های وابسته می‌سازد.

در ادامه با انتخاب گزینه اول یعنی Scenario summary، نتیجه محاسبات و ساخت گزارش در تصویر زیر ارائه شده است. همانطور که می‌بینید، همه مقادیر سلول‌های پیش‌نیاز به همراه مقدار سلول وابسته محاسبه شده و در یک جدول ارائه شده است.

اگر گزینه Scenario PivotTable report را انتخاب می‌کردیم، نتیجه به مانند تصویر زیر ساخته می‌شد. جدول محوری امکان فیلتر سازی و جدا کردن هر یک از سناریوها را به کاربر ارائه می‌نماید.

اینطور به نظر می‌رسد که خرید حداقلی مناسب‌تر از خرید حداکثری است. هر چند سود کمتری دارد ولی اختلاف این دو سود با مقایسه به پولی بیشتری که پرداخت می‌کنیم، محسوس نیست. پس یک سیاست برای خرید می‌توان استفاده از سناریو اول یعنی خرید در پایین‌ترین قیمت باشد که البته تعداد سهام بیشتری هم خواهیم خرید.

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت در فرادرس

کلیک کنید

نکته: در نوشتاری دیگر از مجله فرادرس به معرفی ابزار SOLVER نیز پرداخته‌‌ایم که البته برای حل مسائل آنالیز حساسیت در اکسل با چندین قید و محدودیت قابل استفاده است. در این بین بر تکنیک سیمپلکس تاکیدی بیشتری شده است. همچنین رسم نمودارهای ROC به منظور تحلیل حساسیت یک فرمول به ازاء مقادیر مختلف، یکی دیگر از روش‌های بررسی و انتخاب بهترین گزینه‌ها و یا پارامترهای مدل‌های آماری است که توسط بسیاری از نرم‌افزارهای محاسبات آماری مانند SPSS، قابل ترسیم است.

خلاصه و جمع‌بندی

در این متن سه شیوه مختلف آنالیز حساسیت در اکسل را اجرا کرده و نتایج حاصل از آن‌ها را بخصوص در حوزه مسئله‌های مالی و کسب و کار، تفسیر و توصیف کردیم. همانطور که دیدید، خروجی‌های هر یک از آن‌ها و البته کاربردهایشان در زمینه تحلیل حساسیت، متفاوت است. وجود ابزارهای «جستجوی هدف» (Goal Seek)، «جدول داده» (Data Table) و «سناریو» (Scenario) از مهم‌ترین تکنیک‌های آنالیز حساسیت در اکسل محسوب می‌شوند. البته نباید از Solver که برای حل مسائل برنامه‌ریزی خطی مثلا با روش سیمپلکس به کار می‌رود نیز غافل شد.