فرمان Goal Seek

04 دی 1400

دقیقه

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

آخرین به‌روزرسانی: 27 دی 1401

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

سؤالاتی که در این فصل پاسخ داده می‌شوند:

  • یک دکه لیموناد فروشی با در نظر گرفتن قیمتی ثابت باید سالیانه چند لیوان لیموناد بفروشد تا به نقطه سر به سر برسد؟
  • می‌خواهیم وامی گرفته و طی مدت 15 سال اقساط آن را پرداخت کنیم. نرخ بهره سالیانه 6 درصد است. بانک گفته است که ماهیانه توان پرداخت  2000 دلار را داریم. با این شرایط چقدر می‌توانیم وام بگیریم؟
  • همیشه در جبر دبیرستان با مسئله‌های داستانی (مسئله‌هایی که به‌جای زبان ریاضی در آن از زبان عادی استفاده می‌شود) مشکل داشتم. آیا اکسل می‌تواند حل مسئله‌های داستانی را برایم راحت‌تر کند؟

ابزار Goal Seek در مایکروسافت اکسل 2019 شما را قادر می‌سازد تا با تغییر مقدار ورودی کاربرگی، محاسبه کنید  که حاصل فرمول معینی ، به مقدار مشخصی  که در نظر دارید برسد. به‌عنوان‌مثال از مورد دکه لیموناد فروشی در فصل 17 بنام “تحلیل حساسیت با جدول داده” استفاده می‌کنیم، فرض کنید که مقادیر ثابتی از هزینه‌های بالاسری، هزینه‌های هر واحد و قیمت فروش دارید. با این اطلاعات می‌توانید از ابزار Goal Seek استفاده کنید تا تعداد لیوان‌های لیمونادی که می‌بایست به فروش برسانید تا به نقطه سر به سر  برسید را محاسبه کند. اساساً ابزار Goal Seek یک حل کننده معادله بسیار قوی در کاربرگ شما تعبیه می‌کند. برای استفاده از ابزارGoal seek می‌بایست این سه داده لازم را ( در کادرمحاوره ای مخصوص آن) به اکسل ارائه کنید:

  • ورودیSet Cell ، سلول حاوی فرمول محاسبه اطلاعاتی را که به دنبالش هستید مشخص می‌کند. در مثال مربوط به لیمونادها، Set Cell حاوی فرمول محاسبه سود می‌باشد.
  • ورودیTo Value مقدار عددی هدفی که قرار است ورودی Set Cell به آن برسد را مشخص می‌کند. در مثال لیموناد، ازآنجاکه می‌خواهیم میزان فروشی را که نشان‌دهنده حالت نقطه سر به سر  است، مشخص کنیم مقدار To Value برابر با صفر خواهد بود.
  • ورودی Changing Cell‌ By، سلول ورودی است که اکسل با تغییر آن تا زمانی که فرمول مشخص شده در Set Cell به مقدار To Value‌ برسد، را تعیین می کند. در مثال مربوط به لیمونادها  By Changing Cellحاوی فروش سالیانه لیمونادها خواهد بود.

پاسخ به سؤالات این فصل:

یک لیموناد فروشی با درنظرگرفتن قیمتی ثابت باید سالیانه چند لیوان لیموناد بفروشد تا به نقطه سر به سر  برسد؟

کارهای انجام شده درمورد این مسئله در فایل Lemonadegs.xlsx که در تصویر 1-18 نشان‌داده ‌شده می‌باشد. همان‌طور که در فصل 17 خاطرنشان کردیم، برای این مورد هزینه ثابت سالیانه 45000 و هزینه متغیر واحد 0.45 را در نظر گرفته‌ایم. بیایید قیمتی 3 دلاری را در نظر بگیریم و همان‌طور که گفته شد سؤال این است که باید چند لیوان لیموناد بفروشیم تا به نقطه سر به سر  برسیم؟

فرمان Goal Seek

تصویر 1-18 استفاده از داده‌ها برای تنظیم Goal Seek جهت اجرای تحلیل موقعیت نقطه سر به سر(آموزش Goal Seek)

برای شروع، هر عددی که نشان‌دهنده میزان درخواست کالا است را در سلولD2 قرار دهید. در تب Data از گروه گزینه‌های Forcast روی گزینه What-If Anaylsis کلیک کرده و سپس گزینه Goal Seek‌را انتخاب کنید. حالا کادر محاوره‌ای Goal Seek را همان‌طور که در تصویر 2-18 نشان‌داده‌شده پرکنید.

آموزش اکسل به زبان ساده

تصویر 2-18 کادر محاوره‌ای Goal Seek که ورودی‌های آن برای تحلیل وضعیت نقطه سر به سر  پر شده‌اند.

این کادر محاوره‌ای نشان می‌دهد که می‌خواهیم مقدار سلول D2(میزان درخواست سالیانه، یا فروش) را آن‌قدر تغییر دهیم تا سلول D7(سود) به مقدار صفر برسد. بعد از اینکه روی گزینه OK کلیک کردیم نتیجه‌ای را که در تصویر 1-18 نشان‌داده‌شده را به دست می‌آوریم.

اگر سالیانه تقریباً 17647 لیوان لیموناد (یا روزانه 48 لیوان) بفروشیم به نقطه سر به سر  می‌رسیم. اکسل برای یافتن مقداری که در جستجوی آن هستیم مقدار تقاضا در سلول D2 را تغییر می‌دهد (بین مقادیر کم یا زیاد) تا مقداری پیدا کند که سود را برابر با صفر دلار بنماید. اگر این مسئله بیش از یک راه‌حل داشته باشد قابلیت Goal Seek تنها یک جواب را نشان می‌دهد.

می‌خواهیم وامی 15 ساله بگیریم. نرخ بهره سالیانه 6 درصد است. بانک گفته است توان پرداخت ماهیانه تا 2000 دلار را داریم. چه میزان می‌توانیم وام بگیریم؟

پاسخ‌دادن به این سؤال را با ایجاد یک کاربرگ برای محاسبه پرداخت‌های ماهیانه وام 15 ساله (بیایید فرض کنیم که پرداخت‌ها در انتهای ماه انجام می‌شوند) به‌عنوان تابعی از نرخ بهره ماهیانه و میزان وامی آزمایشی انجام می دهیم. می‌توانید کاری انجام شده در مورد این مسئله را در فایل Paymentgs.xlsx  در تصویر 3-18 ببینید.

تصویر 3-18 استفاده از داده‌ها به همراه ویژگی Goal Seek برای مشخص‌کردن میزان وامی که می توانید بر اساس مجموعه‌ای از پرداخت‌های ماهیانه بگیرید.

فرمول =–PMT(annual_int_rate/12,years,amt._borrowed) در سلولE6 پرداخت ماهیانه مرتبط با میزان وام را محاسبه کرده که در سلول E5  درج شده است.

با پرکردن کادر محاوره‌ای Goal Seek به شکلی که در تصویر 4-18 نشان‌داده‌شده میزان وام را با پرداخت‌های ماهیانه‌ای 2000 دلاری ، محاسبه می‌کند. بدین صورت با حداقل 2000 دلار پرداخت ماهیانه، می‌توان تا 237.007.30 ‌دلار وام گرفت.

آموزش اکسل به زبان

تصویر 4-18 کادر محاوره ای Goal Seek که برای محاسبه میزان وام تنظیم شده است.

همیشه در درس جبر دبیرستان با مسئله‌های داستانی (مسئله‌هایی که به‌جای زبان ریاضی در آن از زبان عادی استفاده می‌شود) مشکل داشتم. آیا اکسل می‌تواند مسئله‌های داستانی را برایم راحت‌تر کند؟

اگر به زمان آموزش جبر در دبیرستان فکر کنید، می‌بینید که بیشتر مسئله‌های داستانی از شما می‌خواهند که متغیری (که معمولاً X نامیده می‌شود) را انتخاب کنید تا معادله خاصی را حل نمایید. ابزارGoal Seek‌یک ابزار حل معادله است، بنابراین کاملاً برای حل مسائل داستانی مناسب است. در اینجا یک مسئله داستانی معمولی جبر دوران دبیرستان را مشاهده می‌کنید:

ماریا و ادموند در حین گذراندن ماه عسل در سیاتل با یکدیگر دعوا کردند. ماریا با عصبانیت به سمت اتومبیل مزدا میاتا دوید و با سرعت 64 مایل در ساعت به سمت خانه مادرش در لوس آنجلس رانندگی کرد. دو ساعت بعد از ترک کردن ماریا، ادموند برای دنبال‌کردنش به درون بی ام وی خود پرید و او را با سرعت 80 مایل در ساعت دنبال کرد. وقتی ادموند به ماریا برسد هریک از آن دو نفر چند مایل رانندگی کرده‌اند؟

می‌توانید راه‌حل این مسئله را در فایل Maria.xlsx که در تصویر 5-18 نشان‌داده‌شده مشاهده کنید.

آموزش اکسل

تصویر 5-18 ابزار Goal Seek می‌تواند به شما در حل مسئله‌های داستانی کمک کند.(اکسل مالی)

 در بخش ورودیSet Cell تفاوت بین فاصله طی شده توسط ماریا و ادموند را وارد خواهیم کرد. می‌توانید مقدار این بخش را با تغییر ساعت‌های رانندگی ماریا به مقدار صفر تغییر دهید. البته به یاد داشته باشید ادموند دو ساعت کمتر از ماریا رانندگی می‌کند.

در این جا چند مقدار آزمایشی از ساعت‌هایی که ماریا رانندگی می‌کند را در سلول D2 وارد کرده‌ایم. سپس نام‌های محدوده را از محدوده سلول‌های C2:C8 در محدوده سلول‌های D2:D8 تخصیص داده‌ایم. ازآنجایی‌که ادموند دو ساعت کمتر از ماریا رانندگی کرده است در سلولD4 فرمول =Time_Maria_drives–2 را وارد می‌کنیم. در سلول D7 از این واقعیت که distance=speed*time (فاصله برابر است با سرعت ضربدر زمان) استفاده شده تا کل فاصله‌ای را که ماریا و ادموند پیموده‌اند را محاسبه کنیم. تفاوت بین فاصله‌های پیموده شده توسط ادموند و ماریا در سلول D8 با فرمول =Maria_distance–Edmund_distance‌محاسبه شده است. اکنون می‌توان کادر محاوره‌ای Goal Seek را همان‌طور که در تصویر 6-18 نشان داده شده تکمیل کرد

اکسل به زبان ساده

تصویر 6-18 کادر محاوره‌ای Goal Seek تکمیل شده برای حل یک مسئله داستانی جبر

 در اینجا آن‌قدر تعداد ساعت‌های رانندگی ماریا (سلول D2) را تغییر داده‌ایم تا تفاوت بین مایل‌های پیموده شده توسط ماریا و ادموند (سلولD8) برابر با صفر شد. همان‌طور که می‌بینید، بعد از اینکه ماریا 10 ساعت و ادموند 8 ساعت رانندگی کنند، فاصله‌ای که هر دو پیموده‌اند به 640 مایل می‌رسد.

مسئله‌های این فصل: (آموزش آسان اکسل)

در مسئله شماره 1 در فصل 17، مشخص کنید چند عدد کتاب با جلد سخت باید فروخته شود تا به وضعیت نقطه سر به سر  برسیم؟

از مثال ارزش فعلی خالص (NPV) اتومبیل در فصل 16 بنام ابزار Auditing (وارسی) و افزونهInquire(بررسی) مشخص کنید فروش سالیانه به چه میزان باید افزایش پیدا کند تا NPV برابر با 1 میلیون دلار شود؟

چه مقداری از قیمت واحد در سال اول میزان NPV را در مثال اتومبیل فصل 16 به 1 میلیون دلار افزایش می‌دهد؟

در مثال مربوط به وام مسکن، فرض کنید می‌بایست 200000 دلار برای 15 سال وام بگیریم. اگر حداکثر پرداخت‌ها به 2000 دلار در ماه محدود شده باشد، حداکثر نرخ بهره‌ای که می‌توان با آن از پس پرداخت وام برآمد چقدر است؟

چگونه می‌توان از ابزارGoal Seek‌ برای تعیین نرخ بازده داخلی (IRR) پروژه‌ای استفاده کرد؟

قصد دارم در 40 سال آینده در انتهای هر سال 20000 دلار در حساب بازنشستگی خود پس‌انداز کنم. در این سرمایه‌گذاری خود چه نرخ بازده ای نیاز دارم تا بتوانم در این مدت 2 میلیون دلار پس‌انداز کرده باشم؟

انتظار دارم که از سرمایه‌گذاری پس‌انداز بازنشستگی سالیانه ده درصد سود کسب کنم. می‌خواهم در طی چهل سال آینده در انتهای هرسال مقداری پول به‌حساب بازنشستگی خود اضافه کنم. اگر بخواهم هنگام بازنشستگی 2 میلیون دلار در حسابم داشته باشم سالیانه چقدر پول می‌بایست به‌حساب واریز کنم؟

دو پروژه را با جریان نقدی زیر در نظر بگیرید:

پروژه 1 با چه میزان نرخ بهره NPV بیشتری خواهد داشت؟ اشاره: نرخ بهره‌ای که هر دو پروژه را به یک میزانNPV‌خواهد رساند را پیدا کنید.

در حال برنامه‌ریزی کنفرانسی در دانشگاه برای همکارانم هستیم. هزینه‌های ثابت 15000 دلار است. می‌بایست به هریک از سخنرانان 700 دلار و همچنین 300 دلار برای هریک از شرکت‌کننده‌های کنفرانس جهت غذا و هزینه‌های رفت‌وآمد به اتحادیه دانشگاه پرداخت کرد. از هریک از شرکت‌کننده‌هایی که سخنران نیستند 900 دلار دریافت می‌کنیم که شامل هزینه کنفرانس و غذا و هزینه‌های رفت‌وآمد است. چند نفر می‌بایست ثبت نام کرده و پول پرداخت کنند تا دریافت‌ها و پرداخت‌ها به نقطه سر به سر  برسند؟

می‌خواهم 40 پوند آب‌نبات بخرم. برخی از آب‌نبات‌ها پوندی 10 دلار و برخی دیگر پوندی 6 دلار هستند. چه مقدار آب‌نبات از هر قیمت باید خریداری کنم تا به میانگین هزینه 7 دلار بر هر پوند برسم؟

سه برق‌کار در حال سیم‌کشی خانه هستند. برق‌کار اول به 11 روز کاری برای انجام کارش نیاز دارد، برق‌کار دوم 5 روز برای انجام کارش نیاز دارد. برق‌کار سوم 9 روز برای انجام کارش نیاز دارد. اگر هرسه برق‌کار بر سیم‌کشی خانه کار کنند چقدر طول می‌کشد تا کارشان تمام شود؟

می‌خواهم به یاد سفر اکتشافی لوئیس و کلارک با یک قایق کانو 40 مایل به سمت بالا و 40 مایل به سمت پایین رودخانه سفر کنم. سرعت جریان رودخانه 5 مایل بر ساعت است. اگر کل این سفر 5 ساعت وقت بگیرد، چنانچه رودخانه جریان تندی نداشته باشد با چه سرعتی می‌بایست قایق را برانم؟

در فایلی بنام NPV.xlsx از فصل هشتم: “محاسبه سرمایه‌گذاری با استفاده از معیار ارزش خالص فعلی” دریافتیم که پروژه یک با نرخ بالای بهره دارای NPV بزرگ‌تری است و پروژه دوم با نرخ بهره کمتر دارای NPV بزرگ‌تری است. این دو پروژه با چه نرخ بهره‌ای دارای NPV‌مساوی خواهند شد؟

فرض کنید می‌خواهید 500000 دلار قرض بگیرید و به مدت 20 سال در ابتدای هرسال آن را بازپرداخت کنید. چه نرخ بهره‌ای برای این وضعیت مناسب‌ترین خواهد بود؟

15 آوریل است و تا به این ماه 35000 دلار از محصولات تجاری خود را فروخته‌ایم. هدف آن است که تا آوریل 72500 دلار از این کالاها به فروش برسانیم. اگر ما برای هریک از محصولات قیمت P را لحاظ کنیم، در طول باقی ماه 800-4p واحد از کالاها را به فروش خواهیم رساند. چه قیمتی ما را قادر می‌سازد تا به هدف درآمدی ماه آوریل خود دست پیدا کنیم؟

نرخ بهره در حال حاضر 5 درصد است و می‌خواهید وامی 20 ساله با پرداخت آخر ماه دریافت کنید. اگر بتوانید هر ماه 3000 دلار پرداخت کنید، بیشترین مبلغی که می‌توانید وام بگیرید چقدر است؟

گرگ وینستون ورشکست شده و یک دکه لیموناد فروشی زده است. او 400 دلار برای خرید وسایل لازم ساخت لیموناد سرمایه‌گذاری و قیمت هر لیوان لیموناد را 4 دلار قرار داده است. هزینه تولید هر واحد لیوان لیموناد وی 2.50 دلار است. گرگ باید چند لیوان لیموناد بفروشد تا سودی 300 دلاری به دست آورد؟

درآمد سالیانه‌ام 80000 دلار است. قرار است 35 سال کارکرده و بعد بازنشسته شوم. فرض کنید هرسال درآمدم 5 درصد افزایش داشته باشد و هرسال در ابتدای سال درآمدم را دریافت کنم. فرض می‌کنم پس از دریافت درآمدم x درصد از آن را خرج می‌کنم. سپس تمامی پول نقدم 10درصد بازده کسب می‌کند. چه مقدار از x باعث می‌شود که میزان پول در آخر سال پایانی برابر با 1.5 میلیون دلار باشد؟

 

جدیدترین مقالات آموزشی اکسل را می توانید در وب سایت گروه مالی زاویه مطالعه کنید.

 

فایل ها جانبی:
دانلود فایل نمونه
اشتراک گذاری در شبکه های اجتماعی

مایکروسافت اکسل (Excel)

loader

لطفا شکبیا باشید...