What-If Analysis: Goal Seek، Data Table و Scenario Manager

مدل بسازید، سؤال بپرسید

یک مدل مالی خوب در اکسل از سه بخش جدا ساخته می‌شود: ورودی‌ها (قیمت، نرخ، مقدار)، محاسبات و خروجی (سود، قسط، نقطه‌ی سربه‌سر). وقتی ورودی‌ها در سلول‌های جدا و نام‌گذاری‌شده باشند، ابزارهای What-If (Data ← What-If Analysis) می‌توانند سؤال‌های «اگر… آن‌گاه…» را پاسخ دهند.

مدل نمونه: وام خرید دستگاه بافندگی

B1  مبلغ وام (تومان)        2,000,000,000
B2  نرخ سود سالانه           23%
B3  تعداد اقساط ماهانه       36
B4  قسط ماهانه               =PMT(B2/12, B3, -B1)
B5  کل بازپرداخت             =B4*B3
B6  کل سود پرداختی           =B5-B1

Goal Seek؛ حل معکوس یک مجهول

«حداکثر قسطی که می‌توانیم بدهیم ۷۰ میلیون است؛ چه مبلغ وامی بگیریم؟» Data ← What-If Analysis ← Goal Seek: Set cell = B4، To value = 70000000، By changing cell = B1. اکسل با آزمون و خطا مقدار B1 را پیدا و در سلول می‌نویسد. Goal Seek فقط یک متغیر را تغییر می‌دهد و فقط یک جواب پیدا می‌کند.

Data Table؛ جدول حساسیت

«قسط در نرخ‌های ۱۸ تا ۳۰ درصد و دوره‌های ۱۲ تا ۶۰ ماه چقدر است؟» یک جدول دوبعدی بسازید:

D10:  =B4                      ← گوشه‌ی بالا-چپ: ارجاع به خروجی
E10:I10:  12  24  36  48  60   ← مقادیر تعداد اقساط (Row input → B3)
D11:D17:  18% 20% … 30%        ← مقادیر نرخ (Column input → B2)

انتخاب D10:I17 → Data → What-If Analysis → Data Table
   Row input cell:    $B$3
   Column input cell: $B$2

اکسل در خانه‌ها می‌نویسد:  {=TABLE(B3,B2)}

برای Data Table یک‌بعدی فقط یکی از دو کادر را پر کنید و فرمول‌های خروجی (قسط، کل سود) را در ردیف اول کنار هم بگذارید.

Scenario Manager؛ مقایسه‌ی چند حالت کامل

وقتی چند ورودی با هم تغییر می‌کنند (سناریوی خوش‌بینانه: قیمت بالا، هزینه‌ی نخ پایین؛ بدبینانه: برعکس)، Data ← What-If Analysis ← Scenario Manager ← Add: برای هر سناریو نام و مقادیر سلول‌های متغیر را ثبت کنید. دکمه‌ی Summary یک شیت گزارش با مقایسه‌ی همه‌ی سناریوها روی سلول‌های نتیجه می‌سازد.

نکته‌هایی که کمتر کسی می‌داند

• سلول‌های ورودی Data Table باید در همان شیتی باشند که جدول در آن است؛ اگر مدل در شیت دیگر است، ورودی را در شیت جدول بگذارید و مدل را به آن ارجاع دهید.
• Data Table با هر تغییری در فایل از نو محاسبه می‌شود و فایل را کند می‌کند؛ Formulas ← Calculation Options ← Automatic except for Data Tables و هر وقت لازم شد F9.
• خانه‌های خروجی Data Table را نمی‌توان تک‌تک ویرایش یا حذف کرد؛ باید کل محدوده‌ی TABLE را انتخاب و Delete کنید.
• در گزارش Scenario Summary به‌جای نام سلول‌ها (مثل $B$4) نشانی نمایش داده می‌شود مگر آن سلول‌ها Named Range داشته باشند؛ قبل از Summary نام‌گذاری کنید.
• Goal Seek با دقت پیش‌فرض ۰٫۰۰۱ متوقف می‌شود؛ برای مبالغ میلیاردی کافی است، اما برای نرخ‌ها Maximum Change را در File ← Options ← Formulas کوچک‌تر کنید.