راهنمای کامل تحلیل فرضی (What-If Analysis) در Excel برای پیش‌بینی و مدیریت سناریوهای مالی و داده‌ای
تحلیل فرضی (What-If Analysis) در Excel ابزاری است که به شما کمک می‌کند تاثیر تغییرات احتمالی در ورودی‌های یک فرمول یا مدل را بررسی کنید و نتایج مختلف در سناریوهای متفاوت را پیش‌بینی کنید.
سه نوع ابزار اصلی تحلیل فرضی در Excel وجود دارد:
-
سناریوها (Scenarios): با این ابزار می‌توانید چندین سناریو بسازید و داده‌های متفاوت برای هر سناریو اعمال کنید تا تاثیر تغییرات را ببینید. به عنوان مثال، برای بررسی تاثیر تغییر قیمت فروش بر سود، می‌توانید سناریوهای مختلف قیمت را امتحان کنید.
-
جدول داده‌ها (Data Tables): این ابزار به شما اجازه می‌دهد تاثیر تغییر یک یا دو متغیر بر یک فرمول را ببینید. مثلاً می‌توانید بررسی کنید که تغییر نرخ بهره چگونه روی پرداخت‌های وام اثر می‌گذارد.
-
جستجوی هدف (Goal Seek): اگر می‌خواهید بدانید چه مقدار ورودی برای رسیدن به نتیجه خاصی نیاز است، از این ابزار استفاده می‌کنید. برای مثال، اگر هدف شما رسیدن به سود مشخصی است، می‌توانید ببینید چه قیمتی باید تنظیم کنید.
بیشتر بخوانید
نحوه شمارش سلول‌ها در اکسل (Excel)
چگونه یک فایل اکسل (Excel) بزرگ را به چندین فایل تقسیم کنیم
تبدیل فایل اکسل(Excel) به ورد: نحوه تبدیل و کپی کردن داده‌ها
مثال کاربردی:
فرض کنید شما صاحب یک کتاب‌فروشی هستید و 100 کتاب در انبار دارید. بخشی از کتاب‌ها را با قیمت 50 دلار و بخشی دیگر را با قیمت 20 دلار می‌فروشید. این یک سناریوی ساده است که نشان می‌دهد چگونه تحلیل فرضی می‌تواند تاثیر تغییرات قیمت و میزان فروش را روی درآمد کل بررسی کند.
در Excel، با استفاده از فرمول موجود در سلول D10، اگر 60 درصد کتاب‌ها را با قیمت 50 دلار و 40 درصد را با قیمت 20 دلار بفروشید، سود کل شما 3800 دلار خواهد بود.
ایجاد سناریوهای مختلف با ابزار Scenario Manager در Excel
فرض کنید می‌خواهید ببینید فروش کتاب‌ها با درصدهای مختلف چه تاثیری بر سود شما دارد. اگر 70٪ کتاب‌ها را به بالاترین قیمت بفروشید چه می‌شود؟ یا 80٪، 90٪ یا حتی 100٪؟ هر درصد یک سناریوی متفاوت است. ابزار مدیر سناریو (Scenario Manager) در Excel به شما امکان می‌دهد این سناریوها را بسازید و نتایج هر کدام را به‌راحتی مقایسه کنید.
مراحل ایجاد سناریو:
-
در تب Data و گروه Forecast، روی What-If Analysis کلیک کنید.
2. گزینه Scenario Manager را انتخاب کنید.
- کادر محاوره‌ای باز می‌شود و می‌توانید سناریوهای جدید اضافه کنید، ویرایش کنید یا نتایج سناریوهای موجود را مشاهده کنید.
3. وی Add کلیک کنید تا سناریو جدیدی بسازید.
4.نام سناریو را وارد کنید، مثلاً “60٪ بالاترین قیمت”، و سلول متغیر را انتخاب کنید (در این مثال، سلول C4 که درصد فروش کتاب‌ها به قیمت بالا است). سپس روی OK کلیک کنید.
5. مقدار مربوطه را وارد کنید (مثلاً 0.6 برای 60٪) و دوباره روی OK کلیک کنید.
6.مراحل مشابه را برای سناریوهای 70٪، 80٪، 90٪ و 100٪ تکرار کنید.
برای دیدن نتیجه هر سناریو، کافی است آن را انتخاب کرده و روی Show کلیک کنید. Excel سلول C4 را تغییر می‌دهد و نتیجه سود نهایی را در برگه نشان می‌دهد.
با این روش، می‌توانید سناریوهای مختلف را بدون تغییر دستی مداوم مقایسه کرده و تصمیمات بهتری بر اساس داده‌ها بگیرید.
خلاصه سناریو در Excel
خلاصه سناریو (Scenario Summary) ابزاری در Excel است که به شما اجازه می‌دهد نتایج سناریوهای مختلف را در یک جدول مشاهده و مقایسه کنید. این ابزار برای دیدن سریع تأثیر تغییرات بر روی مدل‌ها بسیار مفید است.
مراحل ایجاد خلاصه سناریو:
1. در Scenario Manager روی دکمه Summary کلیک کنید.
2. سلول نتیجه موردنظر خود را انتخاب کنید، برای مثال D10 که شامل سود کل است، و سپس روی OK کلیک کنید.
نتیجه:
مثال:
-
اگر 70٪ کتاب‌ها را به بالاترین قیمت بفروشید، مجموع سود 4100 دلار خواهد بود.
-
اگر 80٪ کتاب‌ها را به بالاترین قیمت بفروشید، مجموع سود 4400 دلار خواهد بود.
این مثال نشان می‌دهد که چگونه تغییر درصد فروش می‌تواند بر سود کل تأثیر بگذارد و ابزار Scenario Summary بررسی نتایج سناریوهای مختلف را ساده و سریع می‌کند. با این روش، می‌توانید بدون محاسبات دستی، بهترین تصمیم‌ها را بر اساس داده‌ها اتخاذ کنید.
جستجوی هدف (Goal Seek) در Excel
اگر می‌خواهید بدانید چه مقدار ورودی لازم است تا به نتیجه‌ای مشخص برسید، می‌توانید از ابزار جستجوی هدف (Goal Seek) در Excel استفاده کنید.
مثال: می‌خواهید بدانید چند کتاب باید به بالاترین قیمت فروخته شود تا سود کل دقیقاً 4700 دلار شود. با استفاده از Goal Seek، Excel این محاسبات را به‌طور خودکار انجام می‌دهد و تعداد کتاب‌های لازم را به شما نشان می‌دهد. این ابزار برای حل مسائل و سناریوهای مختلف بسیار کاربردی است.
مراحل استفاده از Goal Seek:
1. در تب Data و گروه Forecast، روی What-If Analysis کلیک کنید.
2. روی Goal Seek کلیک کنید تا کادر محاوره‌ای ظاهر شود.
3. در کادر Set cell سلول D10 (سود کل) را انتخاب کنید.
4. در کادر To value عدد 4700 را وارد کنید.
5. در کادر By changing cell سلول C4 (درصد فروش کتاب‌ها به بالاترین قیمت) را انتخاب کنید.
6. روی OK کلیک کنید
نتیجه: برای رسیدن به سود کل 4700 دلار، باید 90٪ از کتاب‌ها را به بالاترین قیمت بفروشید.
این ابزار به شما کمک می‌کند بدون انجام محاسبات دستی، به راحتی مقدار ورودی لازم برای رسیدن به هدف مشخص را پیدا کنید.
