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

شما میتوانید برای آشنایی با تجزیه و تحلیل what if در اکسل مطلب “تحلیل what if در اکسل | نحوه استفاده از scenario manager” را مطالعه فرمایید.

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

Goal Seek یکی از ابزارهای تحلیل what if در اکسل است که نشان میدهد یک مقدار در یک فرمول چگونه بر روی یک مقدار دیگر تاثیر گذار است. به عبارت دیگر با استفاده از خاصیت Goal Seek میتوانید بدانید، برای بدست آوردن یک نتیجه(خروجی) خاص چه مقداری را بعنوان ورودی می بایست وارد کنید.

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

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

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

برای مثال ، ابزار Goal Seek در اکسل میتواند به شما بگوید که می بایست چه مقدار فروش در هر دوره داشته باشید تا 100 میلیون تومان سود خالص سالیانه داشته باشید. یا اینکه برای بدست آوردن معدل 19 در پایان دوره دانشگاه در این ترم می بایست چه نمره ای کسب کنید یا اینکه چه تعداد رای لازم دارید تا برنده یک انتخابات باشید.

به صورت کلی هر وقت هدف خود را می دانستید ولی نمیدانید در حال حاضر چه عملکردی می بایست داشته باشید تا به هدف نهایی دست پیدا کنید، از goal seek در اکسل استفاده کنید.

نکته:

Goal Seek در اکسل فقط میتواند یک مفدار را بعنوان ورودی بپذیرد. در صورتی که با مدلی روبرو هستید که پیچیده است و دارای چند متغیر است میتوانید از افزونه هایی که در این زمینه وجود دارد مثل solver add-in استفاده کنید. برای آشنایی با افزونه Solver در اکسل مطلب ” ابزار Solver در اکسل” را مطالعه کنید.

نحوه استفاده از goal seek در اکسل

در این قسمت میخواهیم شما را صرفا با نحوه کار با Goal Seek در اکسل آشنا کنیم. بنابراین از یک مجموعه داده ساده استفاده میکنیم.

جدول بالا نشان میدهد که اگر شما 100 آیتم را به قیمت 5 دلار به فروش برسانید، منهای 10 درصد کمیسیون، شما 450 دلار بدست خواهید آورد. حال، سوال این است که برای بدست آوردن 1000 دلار چه تعداد کالا می بایست به فروش برسانید؟

برای پاسخ به این سوال از طریق Goal Seek به شکل زیر عمل کنید:

1. داده های خود را سازماندهی کنید به نحوی که یک سلول فرمول(formula cell) و یک سلول متغیر(changing cell) داشته باشید که به سلول فرمول وابسته باشد.
2. در تب data، گزینه what-if Analysis و سپس Goal Seek را انتخاب کنید.
3. در دیالوگ باکس Goal Seek شبیه به شکل زیر سلول ها و مقادیر را انتخاب کنید و گزینه ok را بزنید.

• Set cell: آدرس سلولی که شامل فرمول است را در این باکس قرار دهید.
• To value: نتیجه مورد نظر یا هدفی که میخواهید به آن دست یابید را در این سلول وارد کنید.
• By changing cell: آدرس سلول ورودی که میخواهید مقدار آن را تغییر دهید را در این سلول وارد کنید.

4. یک دیالوگ باکس به نام Goal seek status باز میشود و به شما خبر میدهد که تحلیل های مورد نیاز انجام شده است و مقدار سلول متغیر(changing cell) با یک مقدار جدید جایگزین میشود.

این مقدار همان مقداری است که شما برای رسیدن به مقدار هدف(1000دلار ) به آن احتیاج دارید. در این مثال، Goal Seek در اکسل دریافت که شما برای بدست آوردن 1000 دلار می بایست 223 آیتم را به فرش برسانید.

5. حال در صورتی که میخواهید مقدار سلول متغیر همان 223 بماند، ok را کلیک کنید و در غیر اینصورت cancel را انتخاب کنید.

در صورتی که فکر میکنید نمیتوانید این مقدار را به فروش برسانید، میتوانید با تغییر قیمت هر کالا(5دلار) به هدف خود دست پیدا کنید. برای بررسی این سناریو دقیقا مراحل گفته شده در بالا را انجام دهید با این تفاوت که مقدار سلول متغیر(changing cell) را به B2 تغییر دهید.

در نتیجه، همانطور که مشاهده میکنید، اگر قیمت هر واحد کالا را به 11 دلار تغییر دهید، میتوانید با همین مقدار فروش به 1000 دلار دست پیدا کنید.

نکته:

• Goal Seek در اکسل فرمول را تغییر نمیدهد، فقط مقدار ورودی را تغییر میدهد تا شما با همان فرمول قبل به نتایج دلخواه خود دست پیدا کنید.
• در صورتی که Goal Seek قادر به محاسبه مقدار مورد نظر نباشد، نزدیکترین مقدار را بعنوان نتیجه برای شما برمیگرداند.
• در صورتی که از نتیجه بدست آمده راضی نبودید، میتوانید با فشار دادن کلید های Ctrl+Z یا دستور Undo مقدار قبل را بازیابی کنید.

مثال هایی از کاربرد Goal Seek در اکسل

مثال 1. رسیدن به مقدار سود دلخواه

مسئله:

فرض کنید مقدار فروش مربوط به سه دوره سه ماهه را در اختیار دارید و حال میخواهید بدانید در سه ماهه چهارم چه مقدار فروش داشته باشید تا به مقدار سود 100000 دلار در آن سال دست پیدا کنید.

حل مسئله:

اگر داده های شما به شکل بالا سازماندهی شده باشد، پارامترهای زیر را برای ابزار Goal Seek وارد کنید:

• Set cell: فرمولی که مقدار سود خالص را محاسبه میکند( D6)
• To value: نتیجه مورد انتظار (100000دلار)
• By changing cell: سلولی که فروش ناخالص را برای سه ماهه چهارم نشان میدهد(B5)

تحلیل Goal Seek نشان میدهد که به منظور بدست آوردن سود خالص 100000 دلاری در انتهای سال می بایست در سه ماهه چهارم فروشی به مقدار 185714 دلار داشته باشید.

مثال 2. نمره امتحانی

در انتهای یک ترم تحصیلی از یک دانشجو سه امتحان گرفته میشود. برای قبولی در آن ترم دانشجو می بایست 70 درصد نمرات کل امتحاناتت را کسب کرده باشد. اگر تمام امتحانات از ضریب یکسانی برخوردار بوده باشند، میتوان نمره کلی را با میانگین گرفتن از نمره سه امتحان بدست آورد. اگر دانشجو تاکنون 2 امتحان را انجام داده باشد، در امتحان سوم می بایست چه نمره ای کسب کند تا 70 درصد از کل نمرات و در نتیجه قبولی در آن ترم بدست آید؟ نمرات دانشجو در دو آزمون اول به شکل زیر است:

حل مسئله:

• Set cell: سلول B5
• To value : 70%
• By changing cell: نمره آزمون سوم(سلول B4)

نتیجه تحلیل Goal Seek در اکسل نشان میدهد که دانشجو می بایست در آزمون سوم 67% از نمره را کسب کند تا در آن ترم نمره قبولی را بدست آورد.

مثال 3. تحلیل what if  انتخابات

فرض کنید در یک انتخابات شرکت کرده اید و برای پیروز شدن در آن انتخابات به دوسوم(66.67درصد) از آراء احتیاج دارید. با فرض اینکه در کل 200 رای وجود دارد، شما به چه تعداد رای برای پیرو شدن احتیاج دارید؟

فرض کنید در حال حاضر 98 رای را در اختیار دارید، که فقط 49 درصد از کل آراء را تشکیل میدهد و برای پیروز شدن کافی نیست:

حل مسئله:

• Set cell: فرمولی که درصد رای های موافق را محاسبه میکند( سلول C2)
• To value: تعداد رای های موافق مورد نیاز( 67%)
• By changing cell: تعداد رای های موافق (سلول B2)

تحلیل what if در اکسل نشان میدهد که برای پیروز شدن در انتخابات شما نیاز به 133 رای دارید.

زمانی هایی که Goal Seek در اکسل به خوبی کار نمیکند

در بعضی از موارد Goal Seek در اکسل قادر به بافتن پاسخ مورد انتظار نیست، صرفا به این دلیل که این پاسخ وجود ندارد. در این شرایط، اکسل نزدیکترین مقدار را به شما ارائه میکند و در یک پیغام شما را از این موضوع مطلع میکند.

اما اگر مطمئن هستید که پاسخ سوال شما وجود دارد ولی Goal Seek آن را برنمیگرداند، موارد زیر را بررسی کنید:

1. یک بار دیگر پارامترهای Goal seek را بررسی کنید

در ابتدا حتما اطمینان حاصل کنید که سلول set cell حاوی یک فرمول است. سپس بررسی کنید که حتما سلول فرمول به صورت مستقیم یا غیر مستقیم به سلول متغیر وابسته باشد.

2. تنظیمات iteration را تغییر دهید.

برای انجام این کار، به File > Options > Formulas مراجعه کنید و موارد زیر را تغییر دهید:

• Maximum Iteration: در صورتی که میخواهید اکسل راه حل های دیگری را نیز بررسی کند این عدد را افزایش دهید.
• Maximum change: در صورتی که فرمول شما به مقدار دقت بیشتری نیاز دارد، این مقدار را کاهش دهید. برای مثال، اگر یک فرمول را با مقدار ورودی 0 بررسی میکنید ولی Goal Seek در 0.001 متوقف میشود، با تغییر Maximum change به 0.0001 مشکل رفع خواهد شد.

تصویر زیر تنظیمات پیش فرض iteration  را نشان میدهد.

3. آدرس دهی حلقه ای

برای اینکه Goal seek به درستی کار کند، فرمول هایی که استفاده میشود نباید به صورت حلقه ای به همدیگر وابسته باشند بنابراین از آدرس دهی حلقه ای نباید استفاده شود.

در این مطلب تلاش کردیم یکی از قسمت های تحلیل What if در اکسل را به صورت کامل بررسی کنیم. در مطالب آینده به قسمت های دیگر what if analysis در اکسل خواهیم پرداخت.