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

در این مقاله به شما نحوه تنظیم جدول های حساسیت در مدل ‌های مالی و همین طور پیش نیاز های مهم برای ورودی ‌و خروجی‌ ها و مراحل مورد نیاز را یاد خواهیم داد و ایجاد این جدول ها را با مدل ارزشگ ذاری Walmart تمرین خواهیم کرد.

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

آنالیز حساسیت در اکسل کمک تان می کند تا مفروضات یا پارامتر های یک مدل را تغییر دهید و به طیف وسیعی از خروجی ها برسید.

تمام پروژه های سرمایه‌ گذاری با احتمال انجام می شوند؛ زیرا نمی ‌توانید دقیقا پیش ‌بینی کنید که در 5، 10 یا 15 سال آینده چه اتفاقی می ‌افتد اما می‌ توانید حالت های مختلفی که احتمال دارد؛ اتفاق بیفتد را بررسی نمایید.

دانلود کنید: دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

مثلا یکی از سناریو هایی که می توانید در نظر بگیرید، این است که شرکت مورد آنالیز تان از حد انتظار شما فراتر رود و به جای 5 تا 10 درصد، 15 درصد در سال رشد کند.

حتی می تواند مطابق با انتظارات تان رشد کند و یا نرخ رشد منفی داشته باشد و سناریوی سومی را رقم بزند!

شما می‌ توانید از آنالیز حساسیت برای بررسی چگونگی تغییر ارزش ‌گذاری این شرکت با در نظر گرفتن هر سه حالت، استفاده کنید.

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

در اکسل، آنالیز حساسیت به ‌عنوان “Data Table ” شناخته می‌ شود و می ‌توانید در منوی نواری زیر زبانه داده و “What – If Analysis” به آن ‌ها دسترسی داشته باشید:

در اکسل ویندوز، کلید های میانبر Alt+ D+ Tیا Alt + A +W + هستند (در اکسل مک میانبری وجود ندارد).

یک جدول حساسیت که به درستی تنظیم و قالب ‌بندی شده باشد به این شکل است (این جدول مربوط به روش ارزش گذاری Walmart DCF است که در آن نرخ تنزیل یا Discount Rate و نرخ رشد نهایی یا Terminal Growth Rate را برای ارزیابی ارزش ضمنی شرکت تغییر می‌ دهیم):

برای ایجاد این جدول مراحل زیر را انجام بدهید:

آموزش کدنویسی در اکسل از صفر تا صد با 9 درس رایگان

جدول های حساسیت، قسمت 1: ساخت یک مدل مالی مناسب

قبل از انجام هر کاری، باید یک مدل مالی یا آنالیز دیگری داشته باشید که در آن چندین ورودی یا پارامتر های کلیدی مستقیما روی خروجی تأثیر بگذارند.

مثلا در مورد مدل Walmart DCF، به راحتی می توان فهمید که پارامتر هایی مثل نرخ تنزیل و مضرب نهایی (Terminal Multiple) بر قیمت ضمنی سهام شرکت تأثیر می گذارد.

مضرب نهایی بر ارزش نهایی یا Terminal Value تأثیر می ‌گذارد، بعد نرخ تنزیل بر ارزش فعلی (PV) ارزش نهایی و PV جریان ‌های نقدی آزاد روی دوره پیش ‌بینی تأثیر می‌ گذارد، و تاثیرشان به ارزش سرمایه گذاری ضمنی و سپس ارزش ویژه ضمنی و قیمت سهام هم سرایت می کند:

اگر چنین تنظیماتی نداشته باشید تا چند سلول کلیدی بر خروجی مدل تان تأثیر بگذارد، آنالیز حساسیت فایده نخواهد داشت.

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

جدول حساسیت، قسمت 2: قالب بندی

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

جدول حساسیت ، قسمت 3: اعداد سطر و ستونی که می خواهید حساسیت روی آن ها باشد را وارد کنید

این مرحله بسیار مهم است. وقتی اعدادی را در سطر و ستون جدول وارد می ‌کنید که می‌ خواهید حساسیت روی شان باشد، نمی‌ توانید آن ها را مستقیما به چیزی در مدل مرتبط کنید!

بنابراین، شما باید تمام این اعداد را در اکسل به صورت Hard Code یا استاتیک بنویسید، یا سطر و ستون را با یک عدد غیر قابل تغییر شروع کنید و بعد در هر ستون یا سطر جمع و تفریق کنید.

در مورد Walmart به این صورت است:

با این آموزش اکسل صفر تا صد اکسل، رو توی کمترین زمان ممکن یاد بگیر.بهترین پک آموزش اکسل در ایران همین الان خرید و دانلود کنید!

کاربردی: نمودار در اکسل: ترسیم انواع نمودار و ویرایش آنها

جدول حساسیت ، قسمت 4: لینک دادن به متغیر خروجی در گوشه بالا سمت چپ

حالا به گوشه سمت چپ بالای جدول یعنی سلول D102 بروید و یک لینک مستقیم به متغیر خروجی که می‌ خواهید در جدول نمایش داده شود، قرار بدهید.

در این جا، این لینک Implied Share Price است:

این تنها بخشی از جدول حساسیت است که باید به چیزی در مدل لینک داده شود.

جدول حساسیت، قسمت 5: کل محدوده را انتخاب و جدول را ایجاد کنید

حالا کل محدوده را انتخاب کنید و کلید های میانبر Alt+ D + Tرا بزنید یا بهData ، What-If Analysis و Data Tableبروید و سلول های ورودی سطر و ستون را وارد کنید (در این مثال این ورودی ها، Terminal Growth Rate و Discount Rate هستند):

اگر جدول فورا آپدیت نشد، F9 را فشار دهید تا اکسل را وادار به آپدیت صفحه گسترده کنید و نتایج را ببینید.

به کار میاد: رسم نمودار سه بعدی در اکسل +همه نکات کاربردی

جدول حساسیت، قسمت 6: کارتان را بررسی کنید

نتایج باید به این صورت باشد:

شما باید کارتان را با باز نگری جدول و توجه به نکات زیر چک کنید:

• آیا اعداد در هر سلول تغییر می کنند؟ اگر نه، یک جا اشتباه کرده اید. شما نباید سطر ها یا ستون هایی با خروجی یکسان داشته باشید.
• در جدول های ارزش گذاری، آیا ارزش ضمنی شرکت با افزایش درآمد، رشد درآمد یا حاشیه سود افزایش پیدا می کند؟ اعداد بالاتر به معنای ارزش گذاری بالاتر هستند.
• با افزایش نرخ تنزیل، ارزش ضمنی شرکت باید کاهش پیدا کند.

علاوه بر این ها مطمئن شوید که متوجه شده‌اید هر پارامتری باید چه تاثیری روی خروجی بگذارد و بعد ببینید که آیا جدول شما این طور هست یا خیر.

ویدیوی آموزشی (اختیاری)

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

تجزیه و تحلیل حساسیت اکسل: پیش نیاز های لازم برای تنظیم جدول

به عنوان جمع بندی این مقاله، به پیش نیاز های کلیدی برای آنالیز حساسیت در اکسل می پردازیم:

1-متغیر های ورودی و خروجی باید در همان صفحه گسترده جدول باشند. در این جدول نمی توانید از پارامتر ها یا شاخص های برگه های دیگر مثل مدل 3-statement استفاده کنید.

2- اعداد در ردیف و ستون ورودی را نمی توان به هر چیزی که در مدل است مرتبط کرد و بالعکس. هر سطر یا ستون ورودی را با یک عدد غیرقابل تغییر پر کنید و بعد بقیه را به صورت Hard code وارد کنید یا آن ها را با درصد یا اعداد ساده تغییر بدهید.

3- ورودی سطر و ستون و خروجی باید به نحوی به هم مرتبط باشند. اگر ورودی ها بر خروجی تأثیر نگذارند، با تغییر اعداد، جدول هیچ تغییری را نشان نخواهد داد.

ساده ترین روش ایجاد یک نمودار کلوچه ای در اکسل

4- در قسمتِ Options یا Preferences، Workbook Calculation را رویAutomatic expect for data tables تنظیم کنید، که در غیر این صورت سرعت صفحه‌ گسترده ‌تان، مخصوصا به خاطر بسیاری از جدول ها کاهش می ‌یابد. بعدا می توانید F9 را برای Refresh یا آپدیت جدول ها فشار بدهید.

5- برای حساس کردن یک سری اعداد یک لینک مستقیم به خروجی مورد نظر در گوشه سمت چپ بالای جدول قرار بدهید . و بعد همه چیز را انتخاب کنید و کار های گفته شده در قسمت های قبلی را انجام بدهید. ” Row Input Cell ” باید یک لینک مستقیم برای ورودی در ردیف بالا باشد، و “Column Input Cell ” باید یک لینک مستقیم برای ورودی در ستون سمت چپ باشد.

6- شما نمی توانید سلول های تکی جدول را بعد از ایجاد تغییر دهید. اگر می خواهید چیزی را تغییر دهید یا ورودی ها یا خروجی های مختلف را انتخاب کنید ، باید کل جدول را حذف و دوباره رسم کنید.

تقریبا در هر مدل مالی چه با پیچیدگی متوسط ​​و چه فراتر از آن، با جدول های حساسیت رو به رو خواهید شد، بنابراین باید بدانید که چه طور از آن ها استفاده کنید.