چگونه می توان آنالیز حساسیت در اکسل را با صفحه گسترده انجام داد ؟
در این مطلب قصد داریم تا به صورت کامل آنالیز حساسیت در اکسل را با صفحه گسترده آموزش دهیم. تا با داشتن کمی دانش اکسل بتوانید حساسیت را آنالیز نمایید.
در این مقاله ابتدا با مفهوم آنالیز حساسیت آشنا خواهید شد و سپس آنالیز حساسیت در اکسل با یک متغیر و دو متغیر را آموزش خواهید دید، در ادامه مقاله نیز روش های مختلف این مبحث را توضیح داده و ویژگی های هرکدام را توضیح می دهیم تا بتوانید بهترین روش را برای حل مسائل انتخاب کرده و سریع تر به جواب برسید.
آنالیز حساسیت در موارد مختلف آنالیز مالی مورد نیاز بوده و در آمار و احتمالات بسیار مورد توجه قرار می گیرد. در موارد مختلف مهندسی اعم از اندازه گیری دما و حرارت در مبحث فیزیک هم کاربرد دارد. در ادامه آنالیز حساسیت در اکسل را برای این مورد آموزش می دهیم تا در صورت نیاز به راحتی کار با اکسل را برای آنالیز حساسیت یاد بگیرید.
آنالیز حساسیت چیست؟
آنالیز حساسیت ابزاری است که در مدل سازی مالی برای تجزیه و تحلیل چگونگی تأثیر مقادیر مختلف مجموعه ای از متغیر های مستقل بر متغیر وابسته خاص تحت شرایط خاص مورد استفاده قرار می گیرد. به طور کلی، تجزیه و آنالیز حساسیت در طیف گسترده ای از زمینه ها، از زیست شناسی و جغرافیا گرفته تا اقتصاد و مهندسی استفاده می شود. به ویژه در مطالعه و تجزیه و تحلیل “فرایند جعبه سیاه” که در آن خروجی تابعی غیر شفاف از چندین ورودی است مفید است.
تابع یا فرآیند غیر شفاف، تابعی است که به دلایلی قابل مطالعه و تحلیل نیست. به عنوان مثال، مدل های آب و هوایی در جغرافیا معمولاً بسیار پیچیده هستند. در نتیجه، رابطه دقیق بین ورودی ها و خروجی ها به خوبی درک نشده است. اینجاست که آنالیز حساسیت در اکسل اهمیت پیدا می کند.
ما در این آموزش از Microsoft Office Excel 2016 استفاده کردیم که جزو سبکترین ورژن های اکسل به شمار می رود و کار کردن با آن بسیار راحت می باشد. با این حال اگر شما کار با این ورژن از اکسل را یاد ندارید می توانید از آموزش اکسل Microsoft Office Excel 2016 که توسط فرادرس آماده شده است استفاده کنید. در زیر لینک مربوط به این آموزش را قرار می دهیم تا با مبانی و اصطلاحات این ابزار کاربردی آشنا شوید و به راحتی بتوانید از آموزش آنالیز حساسیت در اکسل که ما قرار دادیم استفاده کنید.
آنالیز حساسیت با یک متغیر در اکسل
اولین گام در ایجاد یک دیتاشیت که نیرو را در نتیجه تغییرات دما پیش بینی می کند، ایجاد یک ستون دمای متغیر در جایی از کاربرگ است. ( مطابق تصویر زیر )
در تصویر زیر temp برابر با دما، input: ورودی ها، equation: محاسبات و result: نتیجه می باشد.
در سلول بالا سمت راست، نتیجه را محاسبه کنید، یا به سلولی ارجاع دهید که از قبل حاوی نتیجه باشد.
سپس یک محدوده حاوی یک ستون با ورودی های مختلف و همچنین یک سلول نتیجه را انتخاب کنید.
ابزار Data Table را با انتخاب Data > What If Analysis > Data Tables باز کنید.
از آنجایی که ستون ها حاوی مقادیر دما هستند، سلول ورودی دما را به عنوان “سلول ورودی ستون” انتخاب می کنیم. به این ترتیب دیتاشیت می داند که چگونه مقادیر دما را در ستون دمای متغیر در قدرت محاسبه شده جایگزین کند.
وقتی روی OK کلیک می کنیم، خروجی جدولی از نیرو در مقابل دما است که به ما امکان می دهد حساسیت نیرو به تغییرات دما را مشاهده کنیم.
در آموزش بالا به صورت کامل و گام به گام آنالیز حساسیت با یک متغیر در اکسل را آموزش دادیم که تفاوت چندانی با چند متغیره ندارد و به راحتی می توانید این آموزش را بسط داده و برای چندین متغیره هم آنالیز حساسیت را بدست آورید. با این حال برای سادگی کار، در ادامه آنالیز حساسیت در اکسل با دو متغیر توضیح می دهیم تا این مفهوم را بهتر فرابگیرید.
آنالیز حساسیت در اکسل با دو متغیر
آنالیز حساسیت در اکسل با دو متغییر نیز امکان پذیر است، ممکن است متوجه شده باشید که جدول داده دارای ورودی برای هر ستون و ردیف است. این به ما امکان می دهد جداول داده با دو متغیر ایجاد کنیم. پس بیایید ببینیم با تغییر دما و انبساط حرارتی چه اتفاقی برای نیروی ما می افتد.
می توانیم ستون دما را رها کرده، اما نتایج قدرت موجود را حذف کنیم و نتیجه را به سلول بالای ستون دما منتقل کنیم.
اکنون می توانیم یک سری ضرایب انبساط حرارتی را در کنار سلول نتیجه وارد کنیم:
سپس تمام سطرها و ستون های جدول را انتخاب کرده و ابزار Data Table را باز کنید. از آنجایی که ردیف ها حاوی داده های انبساط حرارتی متغیر هستند، این سلول ورودی را به عنوان “سلول ورودی ردیف” انتخاب کنید. و درست مانند قبل، سلول ورودی دما را به عنوان سلول ورودی ستون انتخاب کنید، زیرا ستون حاوی داده های دما است.
روی OK کلیک کنید تا جدول پر شود و به ما امکان دهد آنالیز حساسیت در اکسل را ببینیم.
از این روش برای حسابداری حقوق و مدیریت منابع انسانی شرکت در اکسل نیز می توان استفاده نمود، اگر شما می خواهید به عنوان یک مدیر یا حسابدار، مدیریت حقوق و منابع انسانی را با اکسل انجام دهید پیشنهاد می کنیم آموزش تکمیلی که فرادرس در این زمینه تهیه کرده است، مشاهده نمایید. این آموزش در لینک زیر قرار گرفته است:
در این آموزش ۴ ساعته که توسط خانم عزیزوند تهیه شده است، تمام موارد مورد نیاز مدیریت منابع انسانی آموزش داده شده است. و فقط با همین آموزش می توانید نیروی انسانی خود را مدیریت نمایید .
آنالیز حساسیت در مقابل آنالیز سناریو
مهم است که آنالیز حساسیت مالی را با آنالیز سناریوهای مالی اشتباه نگیرید. در حالی که آن ها تا حدی شبیه هستند، اما تفاوت های کلیدی دارند.
آنالیز حساسیت در اکسل برای درک تأثیر مجموعه ای از متغیرهای مستقل بر روی برخی از متغیر های وابسته تحت شرایط خاص استفاده می شود. به عنوان مثال، یک تحلیلگر مالی می خواهد تأثیر سرمایه در گردش خالص یک شرکت را بر سود آن دریابد. تجزیه و تحلیل شامل تمام متغیرهایی است که بر حاشیه سود یک شرکت تأثیر می گذارد، مانند هزینه کالاهای فروخته شده، دستمزد کارگران، حقوق مدیران و غیره.
از سوی دیگر، آنالیز سناریو، تحلیلگر مالی را ملزم می کند که یک سناریوی خاص را با جزئیات مطالعه کند. آنالیز سناریو معمولاً برای تجزیه و تحلیل موقعیت های مرتبط با شوک های اقتصادی بزرگ مانند تغییرات بازار جهانی یا تغییرات عمده در ماهیت تجارت انجام می شود.
پس از تعیین جزئیات سناریو، تحلیلگر باید تمام متغیرهای مربوطه را مشخص کند تا با سناریو مطابقت داشته باشند. نتیجه یک تصویر بسیار کامل از آینده است (سناریوی گسسته). تحلیلگر باید طیف کاملی از نتایج را با در نظر گرفتن تمام افراط ها بداند و ایده ای داشته باشد که مجموعه ای از متغیرها با یک سناریوی واقعی خاص به نتایج مختلف داده می شود.
مزایای آنالیز حساسیت مالی
دلایل مهم زیادی برای انجام آنالیز حساسیت وجود دارد:
- آنالیز حساسیت با آزمایش مدل در برابر طیف وسیعی از احتمالات، به هر نوع مدل مالی اعتبار می بخشد.
- آنالیز حساسیت مالی به تحلیلگر اجازه می دهد تا در انتخاب مرزها برای آزمایش حساسیت متغیرهای وابسته به متغیرهای مستقل انعطاف پذیر باشد.
- آنالیز حساسیت به شما کمک می کند تا یک انتخاب آگاهانه داشته باشید. تصمیم گیرندگان از برای درک نحوه پاسخ‌دهی خروجی به تغییرات در متغیرهای خاص، استفاده می کنند. به این ترتیب، تحلیلگر می تواند به ترسیم بینش های ملموس و تسهیل تصمیم گیری بهینه کمک کند.
این دلایل باعث شده آنالیز حساسیت در بحث مالی بسیار مورد توجه قرار بگیرید و در بین حسابداران فراگیر شود. با این حال گرفتن حساسیت مالی نیاز به تجربه و داده های دقیق دارد که باید پیش از شروع آنالیز فراهم شوند.
بهترین روش های آنالیز حساسیت
بدست آوردن آنالیز حساسیت با چندین روش مختلف قابل انجام است، برای اینکه با مهم ترین روش های آن آشنا شوید، در زیر ۳ روش اصلی آنالیز حساسیت را بیان کرده و ویژگی های هرکدام را مورد بررسی قرار می دهیم.
۱- آنالیز حساسیت در اکسل
چیدمان، ساختار و برنامه ریزی برای آنالیز حساسیت خوب در اکسل مهم هستند. اگر مدل بد سازماندهی شود، هم سازنده و هم کاربران مدل سردرگم می شوند و تحلیل مستعد خطا خواهد بود.
مهم ترین نکاتی که باید در آنالیز حساسیت در اکسل در نظر گرفت عبارتند از:
- همه مفروضات را در یک ناحیه از مدل قرار دهید
- همه حدس ها / ورودی ها را با رنگ و فونت منحصر به فرد مجزا کنید تا به راحتی شناسایی شوند.
- به دقت فکر کنید که چه چیزی را آزمایش کنید – فقط مهم ترین فرضیات
- درک رابطه (همبستگی) بین متغیرهای وابسته و مستقل (خطی؟ – غیر خطی؟)
- نمودار یا نمودارهایی ایجاد کنید که به کاربران اجازه می دهد به راحتی داده ها را تجسم کنند
- یک منطقه جداگانه برای تجزیه و تحلیل با استفاده از گروه بندی ایجاد کنید (به مثال زیر مراجعه کنید).
۲- روش های مستقیم و غیر مستقیم
روش مستقیم شامل جایگزینی اعداد مختلف به یک فرض در مدل است.
به عنوان مثال، اگر فرض رشد درآمد در مدل ۱۰٪ سال به سال (سالانه) باشد، فرمول درآمد = (درآمد سال گذشته) ضربدر (۱ + ۱۰٪) خواهد بود. در رویکرد مستقیم، اعداد مختلفی را جایگزین نرخ های رشد می کنیم، مانند ۰، ۵، ۱۵، و ۲۰ درصد و نگاه می کنیم که بازده دلار در نتیجه چیست.
روش غیر مستقیم (همانطور که در زیر نشان داده شده است) به جای تغییر مستقیم مقدار حدس، یک درصد متغییر را در فرمول های مدل وارد می کند.
با استفاده از مثال بالا، اگر فرض رشد درآمد در مدل ۱۰% سال به سال (Y/Y) باشد، فرمول درآمد = (درآمد سال گذشته) ضربدر (۱ + ۱۰%) خواهد بود. به جای تغییر ۱۰% به عدد دیگری، می توانیم فرمول را طوری تغییر دهیم که = (درآمد سال گذشته) ضربدر (۱ + (۱۰% + X)) باشد، که در آن X مقدار موجود در آنالیز حساسیت ناحیه است.
۳- جداول، نمودارها و چارت ها
درک آنالیز حساسیت حتی برای آگاه ترین و باهوش ترین متخصصان مالی ممکن است دشوار باشد، بنابراین مهم است که بتوانیم نتایج را به گونه ای بیان کنیم که درک و استفاده از آن آسان باشد.
جداول داده روشی عالی برای نشان دادن تأثیر روی یک متغیر وابسته با تغییر حداکثر دو متغیر مستقل است. در زیر نمونه ای از یک جدول داده است که به وضوح تأثیر تغییرات در رشد درآمد و چند برابر EV/EBITDA را بر قیمت سهام یک شرکت نشان می دهد.
برای اینکه از نمودار ها و چارت ها به درستی در آنالیز حساسیت استفاده کنید باید طرز صحیح کشیدن نمودار در اکسل را بلد باشید. اگر هنوز این مبحث را یاد نگرفتید، می توانید از آموزش کامل فرادرس با عنوان آموزش رسم نمودارهای پیشرفته در اکسل استفاده کنید.
آموزش بالا که در مورد نمودار ها و طراحی آن ها در اکسل می باشد، جزو بهترین آموزش ها بوده که توسط آقای چراغلو تولید شده است. قالب این آموزش ویدیویی بوده و مدت زمان آن ۲ ساعت می باشد.
محدودیت های آنالیز حساسیت در اکسل
دیتاشیت ها ابزار قدرتمندی هستند که حتی می توانند راه را برای مدل سازی پیشرفته مونت کارلو در اکسل باز کنند. با این حال، آن ها چندین محدودیت دارند:
- جداول داده را نمی توان در کاربرگ دیگری به جز سلول های ورودی شما استفاده کرد. با این حال، راه حل هایی برای این کار وجود دارد.
- جداول داده ها می توانند به میزان قابل توجهی باعث کاهش سرعت محاسبه مجدد یک کاربرگ یا کتاب کار شوند.
آنالیز حساسیت در اکسل یکی از مهم ترین روش های محاسبات آماری به شمار می رود. اگر قصد دارید محاسبات آماری دیگر را نیز با اکسل انجام دهید باید آموزش مربوط به آن را مشاهده کنید. در زیر یکی از بهترین ویدیو های اکسل را که برای آموزش محاسبات آماری در اکسل تولید شده است را قرار می دهیم تا بتوانید از آن استفاده کنید.
