شبیهسازی آینده با What-If Analysis
دادههای تاریخی به ما میگویند چه اتفاقی افتاده است، اما ابزارهای What-If Analysis به ما میگویند چه اتفاقی میتواند بیفتد. این ابزارها به شما اجازه میدهند سناریوهای مختلف را شبیهسازی کنید، «چه میشد اگر» را آزمایش کنید و بر اساس آن تصمیمگیری بهتری داشته باشید. در این پست، با سه ابزار قدرتمند تحلیل سناریو در اکسل آشنا میشوید: Goal Seek (یافتن هدف)، Scenario Manager (مدیریت سناریو) و Data Tables (جداول داده). این ابزارها در حوزههای مالی، مهندسی، بازاریابی و برنامهریزی استراتژیک کاربرد گستردهای دارند.
Goal Seek؛ پیدا کردن ورودی برای رسیدن به خروجی
مطلوب=Goal Seek سادهترین ابزار What-If Analysis است. شما یک نتیجه نهایی (هدف) را مشخص میکنید و اکسل مقدار یک سلول ورودی را تغییر میدهد تا به آن هدف برسد. برای مثال، فرض کنید یک کسبوکار دارید که میداند با فروش ۱۰۰۰ واحد محصول، سود ۵۰ میلیون تومان به دست میآید. اما میخواهید سود را به ۷۰ میلیون برسانید. سوال این است: چند واحد باید بفروشید؟ با Goal Seek، سلول هدف (سود) را روی ۷۰ میلیون تنظیم میکنید و سلول متغیر (تعداد فروش) را به اکسل میدهید تا محاسبه کند. برای دسترسی به Goal Seek، از زبانه Data، گروه Forecast، گزینه What-If Analysis و سپس Goal Seek را انتخاب کنید. پنجرهای باز میشود که سه فیلد دارد: Set Cell (سلول هدف)، To Value (مقدار مورد نظر)، By Changing Cell (سلول متغیر). دقت کنید که بین سلول هدف و سلول متغیر باید یک رابطه فرمولی وجود داشته باشد.
مثال عملی Goal Seek در مسائل مالی
فرض کنید وامی به مبلغ ۱۰۰ میلیون تومان با نرخ بهره سالانه ۱۸٪ و مدت ۵ سال (۶۰ ماه) دریافت کردهاید. قسط ماهانه شما با استفاده از تابع =PMT() حدود ۲٫۵۴ میلیون تومان است. اما شما میخواهید بدانید اگر قسط ماهانه شما ۲ میلیون تومان باشد، مدت بازپرداخت چند ماه میشود. با Goal Seek، سلول قسط ماهانه را روی ۲ میلیون تنظیم کرده و سلول تعداد ماهها را متغیر قرار میدهید. اکسل محاسبه میکند که حدود ۷۷ ماه نیاز است. نکته مهم: اگر چندین متغیر دارید، Goal Seek به تنهایی کافی نیست و باید از ابزارهای دیگر استفاده کنید.
Scenario Manager؛ مقایسه چندین سناریو با همScenario
Manager به شما اجازه میدهد چندین مجموعه از مقادیر ورودی را به عنوان سناریوهای مختلف ذخیره کنید و نتایج آنها را با هم مقایسه کنید. برای مثال، یک مدل مالی برای یک پروژه دارید با متغیرهای «نرخ رشد فروش»، «هزینه تبلیغات» و «نرخ تورم». میخواهید سه سناریو را آزمایش کنید: بهترین حالت (رشد بالا، هزینه کم)، بدترین حالت (رشد پایین، هزینه بالا) و حالت عادی. برای ساخت سناریوها، از زبانه Data، گروه Forecast، What-If Analysis، گزینه Scenario Manager را انتخاب کنید. دکمه Add را بزنید و یک نام و سلولهای متغیر را مشخص کنید. سپس مقادیر هر سناریو را وارد کنید. پس از تعریف چند سناریو، میتوانید با کلیک روی Show، هر سناریو را در کاربرگ مشاهده کنید و تأثیر آن را بر فرمولهای خود ببینید.
خلاصهسازی سناریوها با گزارش Summary
یکی از قویترین قابلیتهای Scenario Manager، ایجاد یک گزارش خلاصه (Summary) است که تمام سناریوها و نتایج آنها را در یک جدول جدید به نمایش میگذارد. برای این کار، در پنجره Scenario Manager روی دکمه Summary کلیک کنید. نوع گزارش را انتخاب کنید (Scenario Summary یا PivotTable) و سلولهای نتیجه (سلولهایی که فرمولهای اصلی شما در آنها هستند) را مشخص کنید. اکسل یک جدول جدید با سطرهای سناریوها، ستونهای متغیرهای ورودی و ستونهای نتایج تولید میکند. این جدول قابل چاپ و ارائه است و به شما کمک میکند تأثیر هر سناریو را به صورت مقایسهای ببینید.
Data Tables؛ تحلیل حساسیت یک یا دو متغیره
Data Tables ابزاری برای نمایش نتایج یک فرمول در برابر تغییر یک یا دو متغیر ورودی هستند. این ابزار در دو نوع یک متغیره و دو متغیره وجود دارد. در جدول یک متغیره، ستون اول مقادیر مختلف یک متغیر را شامل میشود و در کنار آن، نتایج فرمول برای هر مقدار نمایش داده میشود. در جدول دو متغیره، سطر اول مقادیر متغیر اول و ستون اول مقادیر متغیر دوم را نشان میدهد و درون جدول، نتایج تقاطع هر جفت مقدار قرار میگیرد. برای ساخت Data Table، ابتدا یک جدول از مقادیر متغیرها بسازید، فرمول اصلی را در گوشه جدول قرار دهید، سپس محدوده جدول را انتخاب کرده و از زبانه Data، What-If Analysis و Data Table را انتخاب کنید.
مثال عملی Data Table دو متغیر
هفرض کنید میخواهید تأثیر همزمان نرخ بهره و مدت بازپرداخت بر مبلغ قسط ماهانه وام را بررسی کنید. در سطر اول، نرخهای بهره مختلف (۱۵٪، ۱۶٪، ۱۷٪، ۱۸٪) و در ستون اول، مدتهای مختلف (۴۸، ۶۰، ۷۲، ۸۴ ماه) را قرار دهید. در سلول گوشه بالا-چپ، فرمول محاسبه قسط را بنویسید که به سلولهای نرخ و مدت ارجاع میدهد. سپس کل محدوده را انتخاب کرده و Data Table را با سلول ورودی سطر (نرخ بهره) و سلول ورودی ستون (مدت) تنظیم کنید. اکسل تمام ۱۶ حالت ممکن را محاسبه میکند و جدولی از اقساط ماهانه به شما میدهد. این جدول برای تصمیمگیری در مورد شرایط بهینه وام بسیار ارزشمند است.
مقایسه و انتخاب ابزار مناسب
انتخاب ابزار مناسب به پیچیدگی مسئله شما بستگی دارد: اگر فقط یک متغیر ورودی و یک هدف مشخص دارید، Goal Seek بهترین گزینه است. اگر میخواهید چندین سناریوی گسسته را با هم مقایسه کنید (مثلاً ۳-۵ سناریوی محتمل)، Scenario Manager مناسب است. اگر میخواهید تمام حالات ممکن یک یا دو متغیر را در یک بازه پیوسته بررسی کنید، Data Tables بهترین گزینه است. در موارد بسیار پیچیده با دهها متغیر و قیود، از Solver استفاده کنید که در ادامه معرفی میشود.
Solver؛ بهینهسازی پیشرفته
Solver یکی از افزونههای اکسل است که برای بهینهسازی مسائل با چندین متغیر و قیود طراحی شده است. اگر Goal Seek را یک دوربرگردان ساده در نظر بگیریم، Solver یک سیستم ناوبری پیشرفته است. شما یک سلول هدف (که باید بیشینه، کمینه یا دقیقاً یک مقدار مشخص شود)، سلولهای متغیر (که میتوانند تغییر کنند)، و قیود (مثل سلولهای متغیر باید بین ۰ تا ۱۰۰ باشند) را مشخص میکنید. Solver با الگوریتمهای ریاضی، بهترین ترکیب از متغیرها را پیدا میکند. برای فعال کردن Solver، از منوی File، Options، Add-ins، Excel Add-ins بروید و Solver Add-in را تیک بزنید. سپس از زبانه Data، در بخش Analyze، گزینه Solver در دسترس خواهد بود.