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

تحلیل حساسیت در اکسل به ما کمک می‌کند تا عدم قطعیت در خروجی مدل را با تغییرات متغیرهای ورودی بررسی کنیم. این در درجه اول سنجش حساسیت فرضیات مدل شده ما را انجام می‌دهد و به بینش‌های ارزش افزوده منجر می‌شود. در زمینه ارزش گذاری DCF، تجزیه و تحلیل حساسیت در اکسل به ویژه برای امور مالی برای مدل سازی قیمت سهام یا حساسیت ارزش گذاری به مفروضاتی مانند نرخ رشد یا هزینه سرمایه مفید است.

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

 

 

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

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

۱ – تجزیه و تحلیل حساسیت جدول داده‌های تک متغیری در اکسل

اجازه دهید یک مثال مالی را در نظر بگیریم. مدل تخفیف سود سهارم را در نظر بگیرید. در زیر برای درک جزئیات این مورد باید بگوییم که رشد ثابت DDM ارزش منصفانه یک سهام را به عنوان ارزش فعلی یک جریان نامتناهی از سود سهام در حال رشد با نرخ ثابت به ما می‌دهد. فرمول رشد «گوردون» به شکل زیر است:

مقادیر به شرح زیر هستند:

•  = D1 ارزش سود سهام در سال آینده دریافت می‌شود
• = D0 ارزش سود سهام دریافتی در سال جاری
• = g نرخ رشد سود سهام
• = Ke نرخ تخفیف

حال، بیایید فرض کنیم که می‌خواهیم بفهمیم، قیمت سهام نسبت به بازده مورد انتظار (ke) چقدر حساس است. دو راه برای انجام این کار وجود دارد.

• روش Donkey
• استفاده از یک جدول داده متغیر

  ۱ – روش Donkey

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

آیا می‌خواهید این ۱۰۰۰ فرض را ادامه دهید؟ بدیهی است که خیر! تجزیه و تحلیل حساسیت در اکسل را بیاموزید تا خود را از دردسر نجات دهید.

 ۲ – استفاده از یک جدول داده متغیر

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

 

 

• جدول را در قالب استاندارد ایجاد کنید.

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

• ورودی و خروجی مرجع را همانطور که در عکس زیر نشان داده شده است، پیوند دهید.

فضای ارائه شده توسط ردیف خالی اکنون برای ارائه ورودی و فرمول خروجی استفاده می‌شود.

• ابزار What-if Analysis را برای انجام تجزیه و تحلیل حساسیت در اکسل انتخاب کنید.

لازم به ذکر است که این امر به دو مرحله تقسیم می‌شود و عبارتند از:

• محدوده جدول را از سمت چپ، از ۱۰% تا گوشه پایین سمت راست جدول، انتخاب کنید.
• روی Data –> What if Analysis > Data Tables کلیک کنید.

• جعبه گفتگوی جدول داده‌ها باز می‌شود.

کادر محاوره‌ای به دنبال دو ورودی است. یکی ورودی ردیف و دیگیری ورودی ستون. از آنجایی که تنها یک ورودی Ke در نظر گرفته شده است، ما یک ورودی تک ستونی ارائه خواهیم داد.

• پیوند دادن ورودی ستون

در مورد مثال ما، تمام ورودی‌ها در یک ستون ارائه می‌شود و از این رو، ما به ورودی ستون پیوند می‌دهیم. ورودی ستون به بازده مورد انتظار (Ke) مرتبط است. توجه داشته باشید که ورودی باید از منبع اصلی و نه از منبعی که در داخل جدول است پیوند داده شود.

• از خروجی لذت ببرید

روش ۲: تجزیه و تحلیل حساسیت جدول داده دو متغیره در اکسل

جداول داده برای تجزیه و تحلیل حساسیت در اکسل بسیار مفید هستند، به خصوص در مورد DCF هنگامی که یک مورد پایه ایجاد شده باشد. تجزیه و تحلیل DCF همیشه باید تحت سناریوهای حساسیت مختلف آزمایش شود. این سنجش شامل بررسی اثر افزایشی تغییرات مختلف در مفروضات (هزینه سرمایه، نرخ رشد نهایی، رشد درآمد کمتر، سرمایه مورد نیاز بیشتر و غیره) بر ارزش منصفانه سهام است.

 

 

اجازه دهید تجزیه و تحلیل حساسیت در اکسل را با یک مثال مالی از تجزیه و تحلیل جریان نقدی تخفیف خورده علی بابا در نظر بگیریم.

با مفروضات پایه هزینه سرمایه ۹ درصد و نرخ رشد ثابت ۳ درصد، به ارزش عادلانه ۱۹۱٫۴۵ میلیارد دلار رسیدیم.

اجازه دهید فرض کنیم که شما به طور کامل با مفروضات هزینه سرمایه یا مفروضات نرخ رشدی که من در ارزیابی IPO Alibaba اتخاذ کرده‌ایم موافق نیستید. ممکن است بخواهید مفروضات را تغییر دهید و به تأثیر آن بر ارزش گذاری‌ها دسترسی پیدا کنید. یک راه این است که مفروضات را به صورت دستی تغییر دهید و نتایج هر تغییر را بررسی کنید.

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

اگر ما تجزیه و تحلیل What-if در اکسل را انجام دهیم، به صورت حرفه‌ای روی داده‌های فوق، خروجی زیر را دریافت می‌کنیم.

• در اینجا، ورودی‌های ردیف شامل تغییرات در هزینه سرمایه یا میانگین وزنی هزینه‌های سرمایه‌ای (wacc) از  ۷٪ تا ۱۱٪ است.
• ورودی‌های ستون شامل تغییرات در نرخ رشد (۱٪ تا ۶٪) است.
• نقطه تلاقی Alibaba Valuation است. به عنوان مثال، با استفاده از مورد پایه ما از ۹٪ WACC و ۳٪ نرخ رشد، ما ارزش ۱۹۱٫۴۵ میلیارد دلار را دریافت می‌کنیم.

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

 

 

مرحله ۱ – ساختار جدول را مطابق زیر ایجاد کنید.

• از آنجایی که ما دو مجموعه فرض داریم – هزینه سرمایه (WACC) و نرخ رشد (g)، باید جدولی را تهیه کنید که در زیر آورده شده است.
• شما آزاد هستید که ورودی‌های ردیف و ستون را تغییر دهید. به جای WACC، ممکن است نرخ رشد داشته باشید و بالعکس.

مرحله ۲ – نقطه تقاطع را به سلول خروجی پیوند دهید.

برای اتصال خروجی مورد نظر باید از نقطه تلاقی دو ورودی استفاده شود. در این صورت می‌خواهیم تأثیر این دو متغیر (WACC) و نرخ رشد را بر روی آن ببینیم. از این رو، ما سلول متقاطع را به خروجی مرتبط کرده‌ایم.

مرحله ۳ – جدول داده‌های دو بعدی را باز کنید.

• جدولی را که ایجاد کرده‌اید انتخاب کنید.
• سپس روی Data -> What if Analysis -> Data Tables کلیک کنید.

 مرحله ۴ – ورودی‌های ردیف و ورودی‌های ستون را ارائه دهید.

• ورودی ردیف هزینه سرمایه یا Ke است.
• ورودی ستون نرخ رشد است.
• به یاد داشته باشید که این ورودی‌ها را از منبع فرضی اصلی و نه از جای دیگری در جدول پیوند دهید.

مرحله ۵ – از خروجی لذت ببرید.

• بیشتر مقادیر بدبینانه خروجی در گوشه سمت راست بالا قرار دارد که در آن هزینه سرمایه ۱۱ درصد است و نرخ رشد تنها ۱ درصد است.
• خوش بینانه ترین ارزش عرضه اولیه سهام Alibaba زمانی است که Ke 7% و g 6% باشد.
• مورد پایه‌ای که ما برای نرخ‌های ۹% ke و ۳% محاسبه کردیم در وسط قرار دارد.
• این تحلیل حساسیت دو بعدی در جدول اکسل، تحلیل سناریو آسانی را در اختیار مشتریان قرار می‌دهد که باعث صرفه جویی در زمان می‌شود.

 شماره ۳ – جستجوی هدف برای تجزیه و تحلیل حساسیت در اکسل

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

همانطور که می دانید در DCF که نرخ رشد و ارزش گذاری با هم ارتباط مستقیم دارند، افزایش نرخ رشد باعث افزایش قیمت سهام می‌شود.

 بیایید فرض کنیم که می‌خواهیم بررسی کنیم که قیمت سهام با چه نرخ رشدی به ۸۰ دلار می‌رسد؟

می‌توانیم این کار را به صورت دستی با تغییر نرخ‌های رشد انجام دهیم تا همچنان تأثیر آن را بر قیمت سهم ببینیم. اما این یک روند خسته کننده خواهد بود. همچنین ممکن است مجبور شویم بارها نرخ رشد را وارد کنیم تا اطمینان حاصل کنیم که قیمت سهام در مورد ما با ۸۰ دلار مطابقت دارد. با این حال، می‌توانیم از تابعی مانند تابع جستجوی هدف در اکسل استفاده کنیم تا حل این مشکل آسان شود.

مرحله ۱ – روی سلولی که می‌خواهید مقدار آن را تنظیم کنید کلیک کنید. (سلول Set باید دارای فرمول باشد).

مرحله ۲ – از ابزارها کمک بگیرید. جستجوی هدف را از منو انتخاب کنید تا کادر محاوره‌ای زیر ظاهر شود:

• دستور Goal Seek به طور خودکار سلول فعال را به عنوان سلول Set پیشنهاد می‌کند.
• این را می‌توان با یک مرجع سلول جدید بیش از حد تایپ کرد، یا می‌توانید روی سلول مناسب در صفحه گسترده کلیک کنید.
• حالا مقدار مورد نظر را که این فرمول باید به آن برسد را وارد کنید.
• داخل کادر “To Value” کلیک کنید و مقداری را که می‌خواهید فرمول انتخابی شما برابر باشد تایپ کنید.
• در نهایت داخل کادر “By Changing Cell” کلیک کنید و سلولی را که مقدار آن قابل تغییر است را تایپ کرده یا روی آن کلیک کنید تا به نتیجه دلخواه برسید.
• روی دکمه OK کلیک کنید، اکسل سلول را به مقدار کافی برای رسیدن فرمول به هدف شما تغییر می‌دهد.

 مرحله ۳ – از خروجی لذت ببرید.

Goal Seek همچنین به شما اطلاع می‌دهد که هدف حاصل شده است.

 سخن آخر

تجزیه و تحلیل حساسیت در اکسل درک شما را از رفتار مالی و عملیاتی کسب و کارتان افزایش می‌دهد. همانطور که از سه رویکرد، جداول داده‌های یک بعدی، جداول داده‌های دو بعدی و جستجوی هدف یادگرفتید، تجزیه و تحلیل حساسیت در زمینه مالی بسیار مفید است، به ویژه در زمینه ارزش گذاری‌ها DCF یا DDM.

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