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

احتمالا برای ایجاد لیست‌ها و مرتب‌سازی داده‌ها از اکسل استفاده کرده‌اید. هر چند در اکثر مواقع، اکسل را برای انجام فرمول‌نویسی و محاسبات سطری و ستونی به کار می‌بریم، ولی ابزارهای مناسب و البته کاربردی در اکسل وجود دارد که بخصوص برای آنالیز حساسیت و ایجاد سناریوهای مختلف برای حل یک مسئله به کار گرفته می‌شوند. هدف از ارائه این مطلب آشنایی با روش آنالیز حساسیت در اکسل به کمک سه روش مختلف است. در ابتدا ابزار «جستجوی هدف» (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) استفاده کنید. در این حالت اغلب با مسئله یا فرمولی (سلول وابسته) مواجه هستیم که برای محاسبه آن از یک یا دو متغیر (سلول‌های مستقل) استفاده می‌شود که ممکن است مقادیر مختلفی بگیرند.