تحلیل داده ها با ابزار Goal Seek اکسل

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

فرض کنید که شرکتی 300 کارمند داره و به هر کدوم از اونها ماهیانه 5 میلیون ریال حقوق می دهد. هزینه مواد اولیه به ازای هر کالا برابر 60 هزار ریال هست و قیمت فروش محصول در بازار هم 80 هزار ریال هست. تعداد تولید  ماهانه محصول شرکت در حال حاضر 100 هزار واحد در هر ماه هست و تمام محصولات تولید شده در بازار فروخته خواهند شد. حالا فرض کنیم که هزینه تمام شده هر واحد کالا و سود شرکت از روابط ساده زیر بدست میاد:

 قیمت تمام شده هر کالا = هزینه مواد اولیه به ازای هر کالا + ((تعداد پرسنل *  حقوق ماهانه) / تعداد تولید ماهانه)

سود ماهانه شرکت = تعداد تولید ماهانه * (قیمت فروش هر کالا – قیمت تمام شده هر کالا)

حالا اگر بنا به دلایلی حقوق پرداختی پرسنل افزایش پیدا کند، طبیعتا سود ماهانه شرکت کاهش پیدا خواهد کرد. فرض کنید در سال جدید حقوق کارکنان از 5 میلیون ریال به 6 میلیون و 200 هزار ریال افزایش پیدا کرده باشه و بقیه متغیرها ثابت باشد.

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

حالا در excelپنجره Goal Seek باز میشه و ما میتونیم مساله خودمون رو در اون تعریف کنیم. گزینه های این پنجره شامل موارد زیر هستند:

Set cell:  در این بخش متغیر وابسته خود راکه به دنبال رسیدن به مقدار مشخصی برای آن هستیم  تعریف می کنیم. در مثال ما این بخش سلول B7 هست که سود ماهانه شرکت رو نشون میده.

To value: در این بخش مقدار مورد نظر رو برای متغیر وابسته تعریف می کنیم. از آنجایی که هدف ما رسیدن به سود ماهانه دوره قبل یعنی 500 میلیون ریال است مقدار این بخش را برابر 500 میلیون قرار میدهیم.

By changing cell: در این بخش متغیری که میخواهیم با تغییر آنن مقدار متغیر وابسته را به عدد موردنظر برسانیم تعریف می کنیم. در این مثال چون ما میخواهیم با تغییر میزان تولید ماهانه سود ماهانه را افزایش دهیم مقدار این گزینه رو برابر سلول B6 قرار میدهیم.

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

اگر حقوق پرسنل افزایش پیدا کنه و مابقی متغیرها ثابت باشه، شرکت می تواند با افزایش تولید و فروش ماهانه از 100 هزار کالا به 118 هزار  کالا، سودی معادل دوره قبل یعنی 500 میلیون ریال  داشته باشد.                  امیدواریم ازاین آموزش اکسل بهره برد باشید