تجزیه و تحلیل حساسیت در اکسل (نمونه دیتاشیت)

فرآیندها در حوزه مالی همیشه به هم مرتبط هستند - یک عامل به دیگری بستگی دارد و با آن تغییر می کند. این تغییرات را دنبال کنید و درک کنید که در آینده چه انتظاراتی دارید، شاید با استفاده از توابع Excel و روش های صفحه گسترده.

بدست آوردن چندین نتیجه با جدول داده

قابلیت‌های صفحه داده عناصری از تجزیه و تحلیل what-if هستند که اغلب از طریق Microsoft Excel انجام می‌شوند. این دومین نام برای تجزیه و تحلیل حساسیت است.

بررسی اجمالی

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

مبانی جداول داده

دو نوع جداول داده وجود دارد که از نظر تعداد اجزا متفاوت هستند. شما باید جدولی را با تمرکز بر تعداد مقادیر uXNUMXbuXNUMXb که باید با آن بررسی کنید، جمع آوری کنید.

زمانی که تنها یک متغیر در یک یا چند عبارت وجود دارد که می تواند نتیجه آنها را تغییر دهد، آماردانان از یک جدول متغیر استفاده می کنند. به عنوان مثال، اغلب همراه با تابع PMT استفاده می شود. این فرمول برای محاسبه مبلغ پرداخت منظم طراحی شده است و نرخ بهره مشخص شده در توافقنامه را در نظر می گیرد. در این گونه محاسبات، متغیرها در یک ستون و نتایج محاسبات در ستونی دیگر نوشته می شوند. نمونه ای از صفحه داده با 1 متغیر:

بعد، صفحات با 2 متغیر را در نظر بگیرید. آنها در مواردی استفاده می شوند که دو عامل بر تغییر در هر شاخص تأثیر می گذارد. این دو متغیر ممکن است به جدول دیگری مرتبط با وام ختم شوند که می توان از آن برای تعیین دوره بهینه بازپرداخت و میزان پرداخت ماهانه استفاده کرد. در این محاسبه باید از تابع PMT نیز استفاده کنید. نمونه ای از جدول با 2 متغیر:

ایجاد جدول داده با یک متغیر

روش تجزیه و تحلیل را با استفاده از مثال یک کتابفروشی کوچک با تنها 100 کتاب در انبار در نظر بگیرید. برخی از آنها را می توان گران تر (50 دلار) فروخت، بقیه برای خریداران هزینه کمتری (20 دلار) خواهد داشت. کل درآمد حاصل از فروش همه کالاها محاسبه می شود - مالک تصمیم گرفت که 60٪ از کتاب ها را به قیمت بالا بفروشد. شما باید دریابید که اگر قیمت حجم بیشتری از کالاها را افزایش دهید - 70٪ و غیره، درآمد چگونه افزایش می یابد.

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

1. یک سلول آزاد دور از لبه برگه انتخاب کنید و فرمول را در آن بنویسید: = سلول درآمد کل. به عنوان مثال، اگر درآمد در سلول C14 نوشته شده باشد (تعیین تصادفی نشان داده شده است)، باید این را بنویسید: =S14.
2. درصد حجم کالاها را در ستون سمت چپ این سلول می نویسیم - نه زیر آن، این بسیار مهم است.
3. محدوده سلول هایی را انتخاب می کنیم که ستون درصد و پیوند به کل درآمد در آن قرار دارد.

4. در برگه "داده ها" مورد "What if analysis" را پیدا می کنیم و روی آن کلیک می کنیم - در منوی باز شده گزینه "Data table" را انتخاب کنید.

5. پنجره کوچکی باز می‌شود که در آن باید سلولی را با درصد کتاب‌هایی که در ابتدا با قیمت بالا فروخته می‌شوند، در ستون «مقادیر جایگزین با ردیف‌ها در…» مشخص کنید. این مرحله به منظور محاسبه مجدد کل درآمد با در نظر گرفتن درصد افزایشی انجام می شود.

پس از کلیک بر روی دکمه "OK" در پنجره ای که داده ها برای جمع آوری جدول وارد شده است، نتایج محاسبات در خطوط ظاهر می شود.

افزودن یک فرمول به یک جدول داده های متغیر منفرد

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

1. محدوده سلول ها را دوباره انتخاب کنید، اما اکنون باید فرمول جدید را شامل شود.
2. منوی تجزیه و تحلیل "What if" را باز کرده و "Datasheet" را انتخاب کنید.
3. بسته به جهت صفحه، فرمول جدیدی را در ردیف یا ستون به فیلد مربوطه اضافه می کنیم.

یک جدول داده با دو متغیر ایجاد کنید

شروع چنین جدولی کمی متفاوت است - باید پیوندی به کل درآمد بالاتر از مقادیر درصد قرار دهید. در مرحله بعد این مراحل را انجام می دهیم:

1. گزینه های قیمت را در یک خط با پیوندی به درآمد بنویسید - یک سلول برای هر قیمت.
2. محدوده ای از سلول ها را انتخاب کنید.

3. پنجره جدول داده ها را باز کنید، مانند زمانی که یک جدول با یک متغیر کامپایل می کنید - از طریق زبانه «داده» در نوار ابزار.
4. در ستون «جایگزین مقادیر با ستون‌ها در…» سلولی با قیمت اولیه بالا جایگزین کنید.
5. سلولی با درصد اولیه فروش کتاب های گران قیمت را به ستون «جایگزینی مقادیر بر اساس ردیف در …» اضافه کنید و روی «OK» کلیک کنید.

در نتیجه کل جدول پر از مبالغ درآمد احتمالی با شرایط متفاوت برای فروش کالا می شود.

افزایش سرعت محاسبات برای کاربرگ های حاوی جداول داده

اگر به محاسبات سریع در یک جدول داده نیاز دارید که باعث محاسبه مجدد کل کتاب کار نمی شود، چند کار وجود دارد که می توانید برای سرعت بخشیدن به فرآیند انجام دهید.

1. پنجره گزینه ها را باز کنید، مورد "Formulas" را در منوی سمت راست انتخاب کنید.
2. مورد "خودکار، به جز جداول داده" را در بخش "محاسبات در کتاب کار" انتخاب کنید.

3. بیایید نتایج جدول را به صورت دستی دوباره محاسبه کنیم. برای این کار فرمول ها را انتخاب کرده و کلید F را فشار دهید.

ابزارهای دیگر برای انجام تجزیه و تحلیل حساسیت

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

1. تابع "انتخاب پارامتر" در صورتی مناسب است که نتیجه مورد نظر مشخص باشد و برای به دست آوردن چنین نتیجه ای باید مقدار ورودی متغیر را بدانید..
2. "جستجوی راه حل" افزونه ای برای حل مشکلات است. باید حد و حدودی تعیین کرد و به آنها اشاره کرد و پس از آن سیستم پاسخ را پیدا خواهد کرد. راه حل با تغییر مقادیر مشخص می شود.
3. تجزیه و تحلیل حساسیت را می توان با استفاده از مدیر سناریو انجام داد. این ابزار در منوی what-if تجزیه و تحلیل در زیر تب Data یافت می شود. این مقادیر را در چندین سلول جایگزین می کند - تعداد آنها می تواند به 32 برسد. توزیع کننده این مقادیر را مقایسه می کند تا کاربر مجبور نباشد آنها را به صورت دستی تغییر دهد. مثالی از استفاده از اسکریپت منیجر:

تجزیه و تحلیل حساسیت یک پروژه سرمایه گذاری در اکسل

تجزیه و تحلیل چه می‌شود به‌ویژه در شرایطی که نیاز به پیش‌بینی است، مانند سرمایه‌گذاری، مفید است. تحلیلگران از این روش استفاده می کنند تا دریابند ارزش سهام یک شرکت در نتیجه تغییر برخی عوامل چگونه تغییر خواهد کرد.

روش تحلیل حساسیت سرمایه گذاری

هنگام تجزیه و تحلیل "چه می شود اگر" از شمارش استفاده کنید - دستی یا خودکار. محدوده مقادیر مشخص است و آنها یک به یک در فرمول جایگزین می شوند. نتیجه مجموعه ای از مقادیر است. از بین آنها عدد مناسب را انتخاب کنید. بیایید چهار شاخص را در نظر بگیریم که تجزیه و تحلیل حساسیت برای آنها در حوزه مالی انجام می شود:

1. ارزش فعلی خالص - با کم کردن مقدار سرمایه گذاری از مقدار درآمد محاسبه می شود.
2. نرخ بازده داخلی / سود - نشان می دهد که چه مقدار سود باید از یک سرمایه گذاری در یک سال دریافت شود.
3. نسبت بازپرداخت نسبت تمام سودها به سرمایه گذاری اولیه است.
4. شاخص سود تنزیل شده - نشان دهنده اثربخشی سرمایه گذاری است.

فرمول

حساسیت جاسازی را می توان با استفاده از این فرمول محاسبه کرد: تغییر در پارامتر خروجی در % / تغییر در پارامتر ورودی در %

پارامترهای خروجی و ورودی می توانند مقادیری باشند که قبلاً توضیح داده شد.

1. شما باید نتیجه را در شرایط استاندارد بدانید.
2. یکی از متغیرها را جایگزین می کنیم و تغییرات حاصل را رصد می کنیم.
3. درصد تغییر هر دو پارامتر را نسبت به شرایط تعیین شده محاسبه می کنیم.
4. درصدهای بدست آمده را وارد فرمول کرده و حساسیت را تعیین می کنیم.

نمونه ای از تحلیل حساسیت یک پروژه سرمایه گذاری در اکسل

برای درک بهتر روش تحلیل، به یک مثال نیاز است. بیایید پروژه را با داده های شناخته شده زیر تجزیه و تحلیل کنیم:

1. برای تجزیه و تحلیل پروژه روی آن جدول را پر کنید.

2. ما جریان نقدی را با استفاده از تابع OFFSET محاسبه می کنیم. در مرحله اولیه، جریان برابر با سرمایه گذاری است. سپس فرمول را اعمال می کنیم: =IF(OFFSET(Number,1;)=2;SUM(Inflow 1: Outflow 1); SUM(Inflow 1: Outflow 1)+$B$ 5)
   بسته به طرح جدول، نام سلول ها در فرمول ممکن است متفاوت باشد. در پایان، مقدار داده های اولیه اضافه می شود - ارزش نجات.

3. ما دوره ای را تعیین می کنیم که پروژه برای آن سود خواهد کرد. برای دوره اولیه از این فرمول استفاده می کنیم: =به طور خلاصه(G7: جی17;»<0″). محدوده سلولی ستون جریان نقدی است. برای دوره های بعدی، این فرمول را اعمال می کنیم: =دوره اولیه+IF(First e.stream>0; First e.stream;0). این پروژه در 4 سال به نقطه سربه سر می رسد.

4. ما یک ستون برای اعداد آن دوره هایی که پروژه نتیجه می دهد ایجاد می کنیم.

5. ما بازگشت سرمایه را محاسبه می کنیم. لازم است بیانی در جایی بیان شود که سود در یک دوره زمانی خاص بر سرمایه گذاری اولیه تقسیم شود.

6. ما ضریب تخفیف را با استفاده از این فرمول تعیین می کنیم: =1/(1+دیسک.%) ^ شماره.

7. ما ارزش فعلی را با استفاده از ضرب محاسبه می کنیم - جریان نقدی در ضریب تخفیف ضرب می شود.

8. بیایید PI (شاخص سودآوری) را محاسبه کنیم. ارزش فعلی در طول زمان بر سرمایه گذاری در شروع پروژه تقسیم می شود.

9. بیایید نرخ بازده داخلی را با استفاده از تابع IRR تعریف کنیم: =IRR (محدوده جریان نقدی).

تجزیه و تحلیل حساسیت سرمایه گذاری با استفاده از دیتاشیت

برای تجزیه و تحلیل پروژه ها در زمینه سرمایه گذاری، روش های دیگر مناسب تر از جدول داده ها هستند. بسیاری از کاربران هنگام کامپایل فرمول دچار سردرگمی می شوند. برای پی بردن به وابستگی یک عامل به تغییرات عوامل دیگر، باید سلول های صحیح را برای وارد کردن محاسبات و خواندن داده ها انتخاب کنید.

تحلیل عاملی و پراکندگی در اکسل با اتوماسیون محاسباتی

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

ANOVA در اکسل

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

1. تغییرپذیری در نتیجه تأثیر سایر مقادیر.
2. تغییرات به دلیل رابطه مقادیری که بر آن تأثیر می گذارد.
3. تغییرات تصادفی

بیایید تجزیه و تحلیل واریانس را از طریق افزونه اکسل "تحلیل داده" انجام دهیم. در صورت فعال نبودن، می توان آن را در تنظیمات فعال کرد.

جدول اولیه باید از دو قانون پیروی کند: برای هر مقدار یک ستون وجود دارد و داده های موجود در آن به ترتیب صعودی یا نزولی مرتب شده اند. بررسی تأثیر سطح تحصیلات بر رفتار در تعارض ضروری است.

1. ابزار Data Analysis را در تب Data پیدا کنید و پنجره آن را باز کنید. در لیست، باید تحلیل واریانس یک طرفه را انتخاب کنید.

2. خطوط کادر محاوره ای را پر کنید. فاصله ورودی همه سلول ها است، به استثنای سرصفحه ها و اعداد. گروه بندی بر اساس ستون ما نتایج را در یک صفحه جدید نمایش می دهیم.

از آنجایی که مقدار در سلول زرد بزرگتر از یک است، این فرض را می توان نادرست در نظر گرفت - هیچ رابطه ای بین آموزش و رفتار در تعارض وجود ندارد.

تحلیل عاملی در اکسل: یک مثال

بیایید رابطه داده ها را در زمینه فروش تجزیه و تحلیل کنیم - لازم است محصولات محبوب و غیرمحبوب شناسایی شوند. اطلاعات اولیه:

1. ما باید دریابیم که تقاضا برای کدام کالاها در ماه دوم بیشترین افزایش را داشته است. ما در حال تهیه جدول جدیدی برای تعیین رشد و کاهش تقاضا هستیم. رشد با استفاده از این فرمول محاسبه می شود: =IF((Demand 2-Demand 1)>0; Demand 2- Demand 1;0). فرمول کاهش: =IF(رشد=0؛ تقاضا 1- تقاضا 2;0).

2. رشد تقاضا برای کالاها را به صورت درصد محاسبه کنید: =IF(رشد/نتیجه 2 =0؛ کاهش/نتیجه 2؛ رشد/نتیجه 2).

3. بیایید یک نمودار برای وضوح ایجاد کنیم - محدوده ای از سلول ها را انتخاب کنید و یک هیستوگرام از طریق زبانه "Insert" ایجاد کنید. در تنظیمات، باید پر را حذف کنید، این کار را می توان از طریق ابزار Format Data Series انجام داد.

تجزیه و تحلیل واریانس دو طرفه در اکسل

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

1. ما "تحلیل داده" را باز می کنیم، در لیست باید یک تحلیل واریانس دو طرفه بدون تکرار پیدا کنید.
2. فاصله ورودی - سلول هایی که حاوی داده ها هستند (بدون هدر). ما نتایج را در یک صفحه جدید نمایش می دهیم و روی "OK" کلیک می کنیم.

مقدار F بزرگتر از F-critical است، به این معنی که کف بر سرعت واکنش به صدا تأثیر می گذارد.

نتیجه

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