نحوه استفاده از Goal Seek در اکسل برای تحلیل حساسیت (What-If Analysis)

در این آموزش نحوه استفاده از Goal Seek در اکسل ۲۰۱۹، ۲۰۱۶، ۲۰۱۳ و ۲۰۱۰ توضیح داده شده‌است تا با تغییر یک مقدار ورودی، نتیجه‌ی فرمول مورد نظر خود را به دست آورید.

تحلیل حساسیت (What-If Analysis) یکی از قدرتمندترین ویژگی‌های اکسل و یکی از کم درک‌شده‌ترین آن‌هاست. به طور کلی، تحلیل حساسیت به شما امکان می‌دهد سناریوهای مختلف را آزمایش کرده و بازه‌ای از نتایج احتمالی را تعیین کنید. به عبارت دیگر، این امکان را به شما می‌دهد تا بدون تغییر داده‌های واقعی، تأثیر ایجاد یک تغییر خاص را ببینید. در این آموزش خاص، ما به یکی از ابزارهای تحلیل حساسیت اکسل – Goal Seek می‌پردازیم.

• Goal Seek در اکسل چیست؟
• نحوه‌ی استفاده‌ی از Goal Seek در اکسل
• نمونه‌هایی از استفاده از Goal Seek در اکسل
• Goal Seek کار نمی‌کند

Goal Seek در اکسل چیست؟

Goal Seek ابزار تحلیل حساسیت داخلی اکسل است که نشان می‌دهد چگونه یک مقدار در یک فرمول بر دیگری تأثیر می‌گذارد. به طور دقیق‌تر، تعیین می‌کند چه مقدار باید در یک سلول ورودی وارد شود تا نتیجه‌ی مطلوب را در یک سلول فرمول به دست آورید.

بهترین چیز در مورد Goal Seek اکسل این است که تمام محاسبات را در پشت صحنه انجام می‌دهد و فقط از شما خواسته می‌شود این سه پارامتر را مشخص کنید:

• سلول دارای فرمول
• هدف/مقدار مورد نظر
• سلول تغییر به منظور دستیابی به هدف

ابزار Goal Seek مخصوصاً برای انجام تجزیه و تحلیل حساسیت در مدل‌سازی مالی بسیار مفید است و توسط بزرگان مدیریت و صاحبان مشاغل بسیار مورد استفاده قرار می‌گیرد. اما موارد استفاده بسیاری نیز وجود دارد که ممکن است برای شما مفید باشد.

به عنوان مثال، Goal Seek می‌تواند به شما بگوید میزان فروش شما در یک دوره معین برای رسیدن به $100,000 دلار سود خالص سالانه (مثال 1) چیست. یا این‌که برای آخرین امتحان خود برای دریافت نمره قبولی کلی 70% باید چه نمره ای کسب کنید (مثال 2). یا این‌که برای پیروزی در انتخابات باید چند رای کسب کنید (مثال 3).

در کل، هر زمان که بخواهید یک فرمول یک نتیجه‌ی خاص را برگرداند اما مطمئن نیستید که چه مقدار ورودی را در فرمول برای برای به دست آوردن نتیجه وارد کنید، حدس زدن را متوقف کرده و از عملکرد Goal Seek اکسل استفاده کنید!

توجه: Goal Seek می‌تواند فقط یک مقدار ورودی را همزمان پردازش کند. اگر در حال کار روی یک مدل تجاری پیشرفته با مقادیر ورودی چندگانه هستید، از افزونه‌ی Solver برای یافتن راه حل بهینه استفاده کنید.

نحوه‌ی استفاده‌ی از Goal Seek در اکسل

هدف از این بخش این است که شما را در چگونگی استفاده از عملکرد Goal Seek یاری کنیم؛ بنابراین، ما با بر روی یک مجموعه داده‌ی بسیار ساده کار خواهیم کرد:

جدول فوق نشان می‌دهد که اگر 100 مورد را با قیمت 5 دلار و منهای 10% کمیسیون بفروشید، $450 دلار درآمد خواهید داشت. سؤال این است: برای به دست آوردن $1,000، چند کالا باید بفروشید؟

بیایید ببینیم چگونه می‌توان جواب را با Goal Seek پیدا کرد:

1. داده‌های خود را طوری تنظیم کنید که یک سلول دارای فرمول و یک سلول در حال تغییر وابسته به سلول فرمول داشته باشید.
2. به تب Data > گروه Forecast بروید، روی دکمه‌ی What if Analysis کلیک کنید و Goal Seek… را انتخاب کنید
   
3. در کادر محاوره‌ای Goal Seek، سلول‌ها/مقادیر را برای تست تعریف کرده و OK را کلیک کنید:
   • Set cell – ارجاع به سلول حاوی فرمول (B5).
   • To value – نتیجه‌ی فرمولی که می‌خواهید به آن برسید (1000).
   • By changing cell – ارجاع به سلول ورودی که می‌خواهید تنظیم کنید (B3).

   

4. کادر گفتگوی Goal Seek Status ظاهر می‌شود و به شما اطلاع می‌دهد اگر راه حلی پیدا بشود یا خیر. در صورت موفقیت، مقدار “changing cell” با مقداری جدید جایگزین می‌شود. OK را کلیک کنید تا مقدار جدید را حفظ کنید یا Cancel را بزنید تا مقدار اصلی بازیابی شود.
   در این مثال، Goal Seek دریافته است که برای دستیابی به درآمد $1,000، نیاز به فروش 223 مورد (گردشده به عدد صحیح بعدی) دارید.

اگر مطمئن نیستید که قادر باشید آن تعداد از کالاها را بفروشید، پس شاید با تغییر قیمت کالا بتوانید به درآمد مورد نظر خود برسید؟ برای آزمایش این سناریو، دقیقاً همان‌طور که در بالا توضیح داده شد، تجزیه و تحلیل Goal Seek را انجام دهید، به جز این که شما یک سلول در حال تغییر (Changing cell) دیگر (B2) را تعیین می‌کنید:

در نتیجه می‌فهمید که اگر قیمت واحد را به $11 افزایش دهید، می‌توانید با فروش فقط 100 کالا به درآمد $1,000 برسید:

نمونه‌هایی از استفاده از Goal Seek در اکسل

در زیر چند نمونه‌ی دیگر از استفاده از عملکرد Goal Seek را در اکسل مشاهده خواهید کرد. پیچیدگی مدل کسب و کار شما تا زمانی که فرمول شما در Set cell بستگی به مقدار Changing cell دارد، به طور مستقیم یا از طریق فرمول‌های میانی در سلول‌های دیگر، اهمیتی ندارد.

مثال 1: رسیدن به سود هدف

مسأله: این یک وضعیت تجاری معمول است – شما برای سه فصل اول ارقام فروش دارید و می‌خواهید بدانید که در فصل آخر چه میزان فروش باید انجام دهید تا به سود خالص هدف سال، یعنی $100,000 برسید.

راه حل: با داده های منبع سازماندهی‌شده مانند تصویر بالا ، پارامترهای زیر را برای عملکرد Goal Seek تنظیم کنید:

• Set cell – فرمولی که کل سود خالص (D6) را محاسبه می‌کند.
• To value – نتیجه‌ی فرمولی که به دنبال آن هستید ($100,000).
• By changing cell – سلول دارای درآمد ناخالص برای فصل چهارم (B5).

نتیجه: تجزیه و تحلیل Goal Seek نشان می‌دهد که برای به دست آوردن سود خالص سالانه‌ی $100,000، درآمد سه ماهه‌ی چهارم شما باید $185,714 باشد.

مثال 2: تعیین نمره‌ی قبولی در امتحان

مسأله: در پایان دوره، دانش آموز 3 امتحان می‌دهد. نمره‌ی قبولی 70% است. تمام امتحانات دارای ضریب یکسان هستند، بنابراین نمره‌ی متوسط با میانگین گرفتن 3 نمره محاسبه می‌شود. دانشجو قبلاً 2 تا از 3 امتحان را داده است. سؤال این است: دانشجو برای امتحان سوم برای گذراندن کل دوره، باید چه نمره‌ای کسب کند؟

راه حل: بیایید Goal Seek را برای تعیین حداقل امتیاز در آزمون 3 انجام دهیم:

• Set cell – فرمولی که میانگین نمرات 3 امتحان (B5) باشد.
• To value – نمره‌ی پاس کردن (70%).
• By changing cell – نمره‌ی امتحان سوم (B4).

نتیجه: برای کسب نمره‌ی کلی مطلوب، دانش آموز باید در آخرین امتحان حداقل 67% را کسب کند:

مثال 3: تحلیل حساسیت انتخابات

مسأله: شما در حال انتخاب شدن برای برخی از مقامات منتخب هستید که اکثریت دو سوم (66.67% آرا) برای پیروزی در انتخابات لازم است. با فرض این‌که 200 عضو رأی‌دهنده در کل وجود داشته باشد، چند رأی برای انتخاب شدن نیاز دارید؟

در حال حاضر، شما ۹۸ رأی دارید، که بسیار خوب است اما کافی نیست زیرا فقط 49% از کل رأی‌دهندگان را تشکیل می‌دهد:

راه حل: برای پیدا کردن مینیمم تعداد آرای “بله” مورد نیاز برای اخذ از Goal Seek استفاده کنید:

• Set cell – فرمولی که درصد آرای فعلی “بله” را محاسبه می‌کند (C2).
• To value – درصد لازم آرای “بله”.
• By changing cell -تعداد آرای “بله” (B2).

نتیجه: تحلیل حساسیت با Goal Seek نشان می‌دهد که برای دستیابی به دو سوم آرا یا 66.67%، به 133 رأی “بله” نیاز دارید:

Goal Seek اکسل کار نمی‌کند

گاهی اوقات Goal Seek قادر به یافتن راه حل نیست فقط به دلیل عدم وجود راه حل. در چنین شرایطی، اکسل نزدیک‌ترین مقدار را می‌دهد و به شما اطلاع می‌دهد که ممکن است Goal Seeking نتوانسته باشد راه حلی پیدا کند:

اگر مطمئن هستید که راه حلی برای فرمول مورد نظر وجود دارد، نکات عیب‌یابی زیر را بررسی کنید.

1- دوباره پارامترهای Goal Seek را بررسی کنید

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

2- تنظیمات تکرار را اصلاح کنید

در اکسل خود، روی File> Options> Formulas کلیک کنید و این گزینه‌ها را تغییر دهید:

• Maximum Iterations – اگر می‌خواهید اکسل راه حل‌های ممکن بیشتری را آزمایش کند، این تعداد را افزایش دهید.
• Maximum Change – اگر فرمول شما به دقت بیشتری نیاز دارد، این تعداد را کاهش دهید. به عنوان مثال، اگر شما در حال آزمایش یک فرمول با یک سلول ورودی برابر با 0 هستید اما Goal Seek در 0.001 متوقف می‌شود، تنظیم Maximum Change به 0.0001 باید مشکل را برطرف کند.

تصویر زیر تنظیمات تکراری پیش‌فرض را نشان می‌دهد:

3- هیچ مرجع دایره‌واری نباشد

برای این‌که Goal Seek (یا هر فرمول اکسل) به درستی کار کند، فرمولهای درگیر نباید وابسته به یکدیگر باشند، یعنی هیچ مرجع دایره‌واری نباشد.

به این ترتیب شما می‌توانید با استفاده از ابزار Goal Seek تحلیل حساسیت (What-If analysis) را در اکسل انجام می‌دهید. از شما متشکریم که این مطلب را خواندید و امیدوارم دوباره شما را در وب‌سایت‌مان ببینیم!