آموزش تحلیل حساسیت در اکسل (What-If Analysis)_31
جلسه سی و یکم از آموزش اکسل در مورد تحلیل حساسیت در اکسل توضیح میدهد؛ شما با استفاده از ابزار تحلیل حساسیت در اکسل می توانید به بررسی اگر و اماهای درون ذهن خود بپردازید! و به راحتی با مشاهده نتیجه همهی حالت ها آینده نگری و تصمیم گیری کنید
تحلیل حساسیت در اکسل
فرض کنید یک کارخانه دارید، میخواهید مقایسه کنید در سناریو های مختلف میزان سود شما چقدر خواهد بود! سناریو هایی که وابسته به میزان تولید شما، هزینه تولید و .. می باشندو با تغییر هر یک تنیجه ها را میخواهیم مشاهده کنید و مقایسه نیز انجام دهید!
به طور کلی زمانی که یک تابع دارید! و یا یک تابع خودتون تولید کردید! و میخواهید خروجی اون تابع رو به ازای مقادیر مختلف انالیز کنید میتونید از What-If Analysis استفاده کنید.
سه ابزار پیش روی شماست:
- Scenarios : شما سناریو های مختلفی تعیین می کنید که در هر کدام ورودی های محاسباتتان متفاوت است و به ازای هر سناریو خروجی را به صورت جدول و .. میتوانید مشاهده کنید
- Goal Seek : از اکسل کمک میگیرید تا بفهمید به ازای چه مقداری فرایدی که تغریف کرده این به یک عدد دلخواه میرسد!
- Data Tables : در این قسمت شما جدولی رسم می کنید و مانند حالت سناریو (اما بدون نیاز به تعریف سناریو) خروجی تابع را به ازای مقادیر مختلف مشاهده می کنید
یا مثال های ساده تر، مثلا یک کتاب فروشی دارید و ۱۰۰ تا کتاب برای فروش دارید، یک درصد مشخصی از کتاب هاتون کتاب های گرون با ۵۰$ سود هستن و یه درصد مشخصی هم با ۲۰$ سود هستند!
برای شروع فرض میکنیم ۶۰% کتاب ها جز کتاب های گرون یا همان ۵۰$ سود هستن و ۴۰% هم ارزان!
ستون Number Of Book اتوماتیک با درصد داده شده و با توجه به اینکه تعداد کل کتابهامون ۱۰۰ هست پر میشه.
ستون سود هم که دستی وارد شده
ستون سود کل هم با فرمول نویسی سود کل رو مشخص میکنه که برابر هست با :
۶۰ * $۵۰ + ۴۰ * $۲۰ = $۳۸۰۰
چون از ۱۰۰ تا کتاب ۶۰ تاش گرون بود و ۴۰ تاش ارزون!
حالا مطابق انچه گفته شد این سود رو میخواهیم به ازای درصد های مختلف کتاب های گرون و ارزون مشاهده کنیم.
ساخت سناریو تحلیل حساسیت در اکسل
در تب Data روی What-If Analysis کلیک کنید و Scenario Manager رو انتخاب کنید.
حال در پنجره باز شده باید سناریو های خودتون رو اضفه کنید، روی Add کلیک کنید.
یک نام دلخواه برای سناریو خود انتخاب کنید، در قسمت دوم خانه ای که قرار است مقدار آن در این سناریو تغییر کند تا خروجی ها را بر اساس تغییر آن ببینیم را وارد کنید.
با کلیک بر روی ok یک پنجره دیر باز می شود:
چون یک خانه وارد کرده بودید یک ورودی میخواهد! برای این سناریو ورودی خانه معرفی شده چقدر باشد؟!؟ ما میخواهیم در این سناریو ۶۰ درصد از کتاب هایمان گران باشد تا زمانی که این سناریو فعال است در خانه C4 مقدار ۰.۶ قرار گیرد
برای حالت های دیگر هم سناریو تعریف کنید، زمانی که ۷۰ درصد کتاب گران داشته باشیم ، زمانی که ۸۰ و …. حتی زمانی که ۱۰۰% کتابهایمان گران باشد
اگر سناریو دلخواه را انتخاب کنید و Show را کلیک کنید اکسل تنها این سناریو را اجار میکند! سناریو ما پیچیده نیست و فقط خانه C4 را برای ما تغییر میدهد!
اما این کار را که دستی هم میتوانستیم انجام دهیم! درسته اما زمانی که سناریو چندین پارامتر متغیر داشته باشد تغییر آن با دست کمی زمان بر است و اینجا با یک کلیک انجام میشود، علاوه بر آن اینجا میتوانیم کل خروجی خای سناریو های مختلف را در یک جدول ببینیم! چه طور؟
Scenario Summary یا خلاصه سناریو ها
در پنجره ی قبل رو Summary کلیک کنید تا به شکل زیر برسید:
از شما می پرسد که آیا خروجی کار را به صورت جدول محوری Pivot نشان دهد و یا جدول ساده و در قسمت Result cells هم باید خانه ای را انتخاب کنید که مقدار آن را به ازایه سناریو های مختلف میخواهید مشاهده کنید! ما میخواهیم مقدار خانه نارنجی رنگ D10 را به ازای سناریو های مختلف ببینیم
ملاحضه می کنید که چه طور خروجی عملیات یا فرمول دلخواه خود را به اضای ورودی های مختلف (سناریو های مختلف) در یک جدول به نمایش کشیده اید!
ابزار Goal Seek
گاهی اوقات میخواهید یک هدف را دنبال کنید! در واقغ میخواهید تحلیل حساسیت در اکسل بروی موضوع خاصی باشد؛ بطور مثال میخواهید بدونید چقدر کتاب گران نیاز دارید تا سود شما به ۴۷۰۰ برسد! یا هر فرمول دیگری ! مثلا چقدر باید اقساط بانک را کم کنید تا میزان سود آن به ۱ ملیون برسد و یا موارد دیگر
از همان تب Data روی What-If Analysis کلیک کنید و Goal Seek را انتخاب کنید.
پنجره ای باز خواهد شد :
در قسمت اول شما خانه ای برای آن تغیین میکنید!
در قسمت دوم به اکسل میگویید قصد دارید آن خانه به چه مقداری برسد! مثلا ما می گوییم خانه سود ما که D10 بود به مقدار ۴۷۰۰ برسد! حال به اکسل باید بگویید چه پارامتری را تغییر دهید تا به آن عدد برسد! میگوییم خانه C4 را انقدر تغییر بده تا خانه D10 به مقدار ۴۷۰۰ برسد.
نتیجه کار: اکسل مقدار ۹۰ را در خانه c4 قرار داد تا ما به سود مورد نظرمان برسیم!
دیدگاهتان را بنویسید
برای نوشتن دیدگاه باید وارد بشوید.