نرم افزار تحلیل حساسیت چند متغیره در اکسل

در مسائل تحلیل حساسیت می بایست حد پایین و حد بالای هر متغیر ورودی را نیز داشته باشیم برای مثال شرکت با توجه به ظرفیت هایی که در فرودگاه مهیا است نهایتا 1000 ساعت پروازی می تواند داشته باشد بنابراین چون منابع، بودجه و امکانات شرکت نامحدود نیست برای هر متغیر ورودی، حداقل و حداکثری را می توان در نظر گرفت.

اگر از ما سوال بپرسند کدام متغیر ورودی در شرکت هواپیمایی تاثیر بیشتری بر سودآوری می گذارد می بینیم پاسخ به این سوال ساده نیست چون روابط بین سودآوری و متغیرهای ورودی پیچیده است. در این شرایط باید تحلیل حساسیت چند متغیره انجام دهیم تا میزان تاثیرگذاری هر متغیر ورودی بر سودآوری مشخص و قابل مقایسه شود.

برای اینکه بتوانید از نرم افزار تحلیل حساسیت چند متغیره استفاده کنید:

• حتما می بایست مسئله را مانند تصویر بالا فرمت دهی کنید یعنی نام متغیرهای ورودی همگی در یک ستون و مقدار متغیرهای ورودی نیز همگی در یک ستون، مقابل آن نوشته شود.
• در سلول مقدار متغیرهای ورودی نباید هیچ فرمولی وجود داشته باشد
• در سلول مقدار متغیر خروجی حتما باید فرمولی وجود داشته باشد که از طریق فرمول مشخص باشد هر متغیر ورودی چگونه با متغیر خروجی در ارتباط است.

بعد از اینکه نرم افزار تحلیل حساسیت چند متغیره را خریداری و نصب کردید نرم افزار به منوی add-ins‌ بالای اکسل اضافه می شود

بر روی Sensitivity Analysis کلیک کنید تا منو باز شود سپس بر روی گزینه «چند ورودی یک خروجی» کلیک کنید تا پنجره زیر باز شود.

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

برای اینکه تغییر در متغیرهای ورودی انجام گیرد نرم افزار میزان حداقل، میزان تدریجی تغییر(گام) و حداکثر مقدار را بر اساس درصد از ما می گیرد. برای مثال وقتی مقدار شروع را 50 درصد قرار می دهیم نرم افزار مقدار شروع را 50 درصد مقدار پایه به ازای هر متغیر در نظر می گیرد و در گام بعدی 10 درصد به مقدار اولیه هر ورودی اضافه می کند تا نهایتا در گام اخر مقدار متغیر ورودی به 150 درصد مقدار پایه برسد. سپس مقدار متغیر خروجی را در هر گام به ازای هر متغیر ورودی محاسبه می کند و این مراحل را برای هر متغیر ورودی تکرار می کند تا نتایج تحلیل حساسیت بدست آید.

نحوه تفسیر نتایج تحلیل حساسیت چند متغیره

بعد از کلیک بر روی دکمه تایید، خروجی های تحلیل حساسیت در یک شیت جدید ایجاد می شود که شامل چند جدول و نمودار می باشد. در جدول اول نتایج، مقادیر مختلف ورودی ها (از 50 درصد تا 150 درصد) محاسبه شده است.

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

در جدول بالا مقادیری که با رنگ قرمز مشخص شده است نشان دهنده سود منفی(ضرر) است برای مثال اگر ظرفیت پروازهای برنامه ریزی شده کمتر از 90 درصد ظرفیت پایه باشد سود سالیانه شرکت منفی می شود و یا اینکه اگر هزینه های عملیاتی 10 درصد بیشتر از مقدار پایه باشد سود سالیانه شرکت منفی می شود(ضرر می کند).

در جدول بعدی میزان تغییر در متغیر خروجی(سودآوری) بر حسب درصد محاسبه شده است.

نتایج جدول بالا نشان می دهد سلول هایی که به رنگ سبز پر رنگ هستند سودآوری افزایش قابل ملاحظه ای داشته است و سلول هایی که به رنگ قرمز پر رنگ مشخص شده اند سودآوری منفی شده است و سلول های زرد رنگ نشان دهنده تغییرات جزئی در میزان سودآوری شرکت است. با نگاه اجمالی به جدول مشخص می شود که متغیر هزینه های عملیاتی در هر ساعت با کاهش یا افزایش جزئی می تواند سودآوری شرکت را با تغییرات زیادی مواجه کند.

در خروجی بعدی همین جداول در قالب نمودار ترسیم شده اند تا امکان مقایسه بین متغیرهای ورودی، راحت تر انجام گیرد.

در نمودارهای تحلیل حساسیت هر متغیر ورودی که شیب بیشتری داشته باشد یعنی تاثیر بیشتری بر متغیر خروجی دارد. اگر به نمودارهای بالا توجه کنید بیشترین حساسیت خروجی مربوط به متغیر « هزینه های عملیاتی در هر ساعت » است. بعد از آن متغیر «قیمت چارتر در هر ساعت» تاثیرگذاری بیشتری بر متغیر خروجی می گذارد به همین ترتیب می توان تاثیرات همه متغیرهای ورودی را با یکدیگر مقایسه کرد و از نتایج بدست آمده برای اخذ تصمیمات صحیح و مناسب جهت افزایش سودآوری شرکت استفاده نمود.

مثال تحلیل حساسیت یک متغیره در اکسل

اگر فقط بخواهیم تاثیر یکی از متغیرهای ورودی مثلا ساعت پروازی را بر سودآوری شرکت بررسی کنیم نیاز است تا تحلیل حساسیت یک متغیره انجام شود.  در منوی نرم افزار بر روی  گزینه «یک ورودی یک خروجی» کلیک کنید تا پنجره زیر باز شود.

مطابق تصویر بالا متغیر ساعت پروازی را به عنوان متغیر ورودی و متغیر سودآوری را به عنوان متغیر خروجی تعیین کنید. در قسمت میزان تغییر در متغیر ورودی می توانید حد پایین و حد بالای متغیر که 500 و 1000 ساعت پروازی هست در نظر بگیرید میزان گام را 50 در نظر می گیریم که متغیر ورودی بصورت 500، 550، 600، 650 ، … تا 1000 تغییر می کند. اگر می خواهید جزیات بیشتر شود مقدار گام را عدد کوچکتر مشخص کنید تا داده های بیشتری ایجاد شود. پس از کلیک بر روی دکمه «تایید» نتیجه تحلیل حساسیت یک متغیره بصورت زیر حاصل می شود.

نتایج تحلیل حساسیت نشان می دهد میزان سودآوری با ساعت پروازی ارتباط مثبتی دارد یعنی هر چه ساعت پروازی بیشتر شود سوداوری افزایش می یابد. نکته قابل ملاحظه این است اگر ساعات پروازی از 600 ساعت کمتر شود سودآوری شرکت هواپیمایی منفی می شود.

برای مثال های مهندسی که می خواهیم تاثیر پارامترهای ورودی را بر مقدار تابع اندازه گیری کنیم می توان از نرم افزار تحلیل حساسیت استفاده کرد.

برای دانلود فایل اکسل مثال انجام شده، بر روی دکمه زیر کلیک کنید.