تحلیل فرضی در اکسل (what-if-analysis)

تحلیل فرضی (What-If Analysis) در Excel ابزاری است که به شما اجازه می‌دهد تاثیر تغییرات احتمالی در ورودی‌های یک فرمول یا مدل را بررسی کنید. این ابزار به شما کمک می‌کند تا نتایج متفاوتی که ممکن است در سناریوهای مختلف رخ دهد را پیش‌بینی کنید

سه نوع ابزار تحلیل فرضی وجود دارد :

• سناریوها (Scenarios): با استفاده از این ابزار می‌توانید چندین سناریو مختلف را ایجاد کنید و داده‌های متفاوتی را برای هر سناریو اعمال کنید تا تاثیر تغییرات را ببینید. مثلاً اگر بخواهید بفهمید تغییرات قیمت فروش چه تاثیری بر سود خواهد داشت، می‌توانید سناریوهای مختلفی برای قیمت‌های مختلف ایجاد کنید.
• داده‌های قابل تغییر (Data Tables): این ابزار به شما اجازه می‌دهد تاثیر تغییر یک یا دو متغیر را بر یک فرمول مشاهده کنید. مثلاً می‌توانید ببینید تغییر نرخ بهره چه تاثیری بر پرداخت‌های وام خواهد داشت.
• جستجوی هدف (Goal Seek): اگر می‌خواهید بدانید چه مقدار ورودی‌ای برای دستیابی به یک نتیجه مشخص نیاز است، از این ابزار استفاده می‌کنید. مثلاً اگر هدف شما سود مشخصی است، می‌توانید از جستجوی هدف استفاده کنید تا ببینید باید چه قیمتی را تنظیم کنید.

 

فرض کنید شما صاحب یک کتاب‌فروشی هستید و 100 کتاب در انبار دارید. شما درصد مشخصی از کتاب‌ها را به بالاترین قیمت 50 دلار و درصد دیگری را به قیمت پایین‌تر 20 دلار می‌فروشید. این وضعیت یک سناریوی ساده است که نشان می‌دهد چگونه تحلیل فرضی می‌تواند تأثیر تغییرات قیمت و میزان فروش را بر کل درآمد بررسی کند.

در این مثال، از Excel برای محاسبه سود استفاده می‌شود. اگر 60 درصد از کتاب‌ها را به قیمت 50 دلار و 40 درصد را به قیمت 20 دلار بفروشید، سود کل به‌صورت ترکیبی از دو قیمت محاسبه می‌شود. Excel با استفاده از فرمولی که در سلول D10 قرار دارد، این محاسبه را انجام می‌دهد و نتیجه نهایی 3800 دلار سود خواهد بود.

سناریوهای مختلف ایجاد کنید

اما اگر 70% از کتاب‌ها را به بالاترین قیمت بفروشید چه می‌شود؟ و اگر 80% را به بالاترین قیمت بفروشید چه؟ یا 90%، یا حتی 100%؟ هر درصد متفاوت یک سناریوی متفاوت است. شما می‌توانید از ابزار “مدیر سناریو” (Scenario Manager) برای ایجاد این سناریوها استفاده کنید.
در اینجا، منظور از سناریوهای مختلف، تنظیمات مختلف فروش است که بر اساس درصد کتاب‌های فروخته‌شده به قیمت‌های متفاوت تنظیم می‌شوند. برای مثال، اگر درصد بیشتری از کتاب‌ها را به قیمت بالاتر بفروشید، سود شما نیز تغییر می‌کند. Excel از طریق ابزار “مدیر سناریو” به شما امکان می‌دهد این سناریوها را به‌راحتی تنظیم کرده و تأثیر آن‌ها بر کل سود را مشاهده کنید.

می‌توانید به‌سادگی درصد متفاوتی را در سلول C4 وارد کنید تا نتیجه سناریوی مربوطه را در سلول D10 ببینید. با این حال، تحلیل فرضی به شما این امکان را می‌دهد که نتایج سناریوهای مختلف را به‌راحتی با هم مقایسه کنید.
اگرچه می‌توانید به‌صورت دستی درصدها را تغییر دهید و نتایج را بررسی کنید، ابزار تحلیل فرضی Excel به شما این امکان را می‌دهد که چندین سناریو را ایجاد و نتایج هر یک را به‌طور هم‌زمان مقایسه کنید. این قابلیت مقایسه سریع و مؤثر را ممکن می‌سازد و به شما کمک می‌کند تصمیمات بهتری بر اساس داده‌ها بگیرید.

1. در تب Data، در گروه Forecast، روی What-If Analysis کلیک کنید.

2. روی Scenario Manager کلیک کنید.

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

3. با کلیک بر روی Add یک سناریو اضافه کنید.

4.یک نام تایپ کنید (برای مثال: “60% بالاترین قیمت”)، سلول C4 (درصد فروش برای بالاترین قیمت) را برای سلول‌های متغیر انتخاب کنید و بر روی OK کلیک کنید.
ر این مرحله، شما باید نامی برای سناریوی خود انتخاب کنید که نشان‌دهنده شرایطی باشد که می‌خواهید تحلیل کنید، مانند “60% بالاترین قیمت”. سپس سلول C4 که حاوی درصد فروش کتاب‌ها به قیمت بالاتر است را به عنوان سلول متغیر انتخاب می‌کنید. این سلول همان متغیری است که شما در سناریوهای مختلف تغییر می‌دهید تا تأثیر آن بر سود نهایی را مشاهده کنید. با کلیک بر روی OK، این سناریو در ابزار “مدیر سناریو” ذخیره می‌شود و شما می‌توانید به راحتی نتایج آن را با سایر سناریوها مقایسه کنید.

5. مقدار مربوطه 0.6 را وارد کرده و دوباره بر روی OK کلیک کنید.

6. در مرحله بعد، 4 سناریو دیگر (70٪، 80٪، 90٪ و 100٪) را اضافه کنید.

در نهایت، مدیر سناریو شما باید با تصویر زیر مطابقت داشته باشد:

توجه: برای دیدن نتیجه یک سناریو، سناریو را انتخاب کرده و روی دکمه Show کلیک کنید. اکسل مقدار سلول C4 را بر این اساس تغییر می‌دهد تا شما نتیجه مربوطه را در برگه مشاهده کنید.

خلاصه سناریو

خلاصه سناریو (Scenario Summary) ابزاری است که به شما اجازه می‌دهد نتایج سناریوهای مختلف را به‌صورت یک جدول خلاصه مشاهده کنید. این ابزار برای مقایسه سریع و ساده سناریوها بسیار مفید است و به کاربران این امکان را می‌دهد تا تأثیر تغییرات مختلف بر روی یک مدل را در کنار هم ببینند. در ادامه، مراحلی که برای ایجاد و مشاهده این خلاصه باید انجام دهید توضیح داده خواهد شد تا بتوانید به راحتی نتایج سناریوهای خود را مقایسه کنید.

برای مقایسه آسان نتایج این سناریوها، مراحل زیر را انجام دهید.

1. روی دکمه Summary در مدیریت سناریو کلیک کنید.

2. سپس سلول D10 (سود کل) را برای سلول نتیجه انتخاب کنید و روی OK کلیک کنید.

نتیجه:

اگر 70% از کتاب‌ها را به بالاترین قیمت بفروشید، مجموع سود شما 4100 دلار خواهد بود. اگر 80% از کتاب‌ها را به بالاترین قیمت بفروشید، مجموع سود شما 4400 دلار خواهد بود و به همین ترتیب. این نشان می‌دهد که تحلیل فرضی در Excel چقدر می‌تواند ساده و کاربردی باشد.
در این نتیجه‌گیری، مشاهده می‌کنید که با تغییر درصد فروش کتاب‌ها به بالاترین قیمت، سود کل شما چگونه تغییر می‌کند. این مثال به‌خوبی نشان می‌دهد که چگونه با استفاده از تحلیل فرضی می‌توانید به‌سرعت تأثیر تغییرات در ورودی‌ها (مانند درصد فروش) را بر نتایج (مانند سود کل) ببینید. Excel با ارائه ابزارهای تحلیل فرضی به شما کمک می‌کند تا بدون نیاز به محاسبات دستی، نتایج مختلف سناریوها را بررسی کرده و بهترین تصمیم را بر اساس داده‌های موجود بگیرید.

 

جستجوی هدف

اگر بخواهید بدانید که برای رسیدن به سود کل دقیقاً 4700 دلار، چند کتاب باید به بالاترین قیمت بفروشید، چه باید کرد؟ شما می‌توانید از ویژگی جستجوی هدف (Goal Seek) در Excel استفاده کنید تا پاسخ این سوال را پیدا کنید.
ابزار جستجوی هدف در Excel به شما کمک می‌کند تا یک مقدار ورودی مشخص را پیدا کنید که باعث ایجاد یک نتیجه مورد نظر در فرمول می‌شود. در این مثال، شما می‌خواهید بدانید چه تعداد کتاب باید به قیمت بالاتر بفروشید تا دقیقاً به سود 4700 دلار برسید. با استفاده از Goal Seek، Excel به‌طور خودکار محاسبات لازم را انجام داده و به شما نشان می‌دهد که چه تعداد کتاب باید فروخته شود تا به هدف سود مشخص‌شده برسید. این ابزار برای حل معادلات و سناریوهای مختلف، بسیار مفید و کاربردی است.

1. در تب Data، در گروه Forecast، روی What-If Analysis کلیک کنید.

2. روی Goal Seek کلیک کنید.

کادر گفتگوی Goal Seek ظاهر می‌شود.

3. سلول D10 را انتخاب کنید.

4. در کادر “To value” کلیک کنید و 4700 را تایپ کنید.

5. در کادر “By change cell” کلیک کنید و سلول C4 را انتخاب کنید.

6. روی OK کلیک کنید.

نتیجه. برای به دست آوردن سود کل دقیقاً 4700 دلار، باید 90٪ از کتاب‌ها را به بالاترین قیمت بفروشید.