معرفی ابزار کاربردی Data Table در اکسل

یکی ابزارهای بسیار کاربردی برای تحلیل حساسیت در نرم افزار اکسل، Data Table است. این ابزار معمولا در مدلسازی مالی و تجزیه و تحلیل مالی مورد استفاده قرار می گیرند تا در مواجه با عدم اطمینان بتوانیم نتایج مختلف تابع هدف را با توجه به طیف وسیعی از مقادیر یک یا دو متغیر ورودی بسنجیم.

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

نحوه ایجاد Data Table در اکسل

فرض کنید می خواهیم سود عملیاتی یک شرکت را با توجه مقادیر مختلف حجم فروش و قیمت هر واحد محصول ارزیابی کنیم. مدل ساده ما شامل یک هزینه متغیر(بهای تمام شده کالای فروش رفته) و هزینه ثابت (هزینه های عمومی و اداری) است.

همان گونه که در تصویر بالا مشاهده می کنید، در سلول D9 مقدار مربوط به سود عملیاتی (تابع هدف) را محاسبه کردیم. اما به یاد دارید که می خواهیم تغییرات این سود عملیاتی را بر اساس تغییرات دو متغیر مستقل خود یعنی قیمت هر واحد از محصول و تعداد محصول فروش رفته بدست آوریم.

مرحله اول

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

مرحله دوم:

سلول تابع هدف باید در سمت چپ و بالا (اگر صفحه اکسل Left to Right باشد) یا در سمت راست و بالا (اگر صفحه اکسل Right to Left باشد) قرار گیرد. در این مثال سلول F2 باید برابر با مقدار محاسبه شده سود عملیاتی یعنی D9 شود.

مرحله سوم

جهت حل با استفاده از ابزار data table باید کل محدوه جدول شامل F2:K9 را انتخاب کنیم. دقت شود ناحیه انتخابی باید شامل تابع هدف، ردیف مقابل آن و ستون زیر آن باشد.

پس از آن در نوار Data در زیر مجموعه گزینه What-if Analysis، ابزار Data Table را انتخاب می کنیم. کلید میانبر این ابزار Alt A W T است.

مرحله چهارم

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

این مرحله می تواند یکی از سخت ترین مراحل تنظیم Data Table باشد چرا که تحلیلگران مالی اغلب مطمئن نیستند Row Input Cell و Column Input Cell را به کدام سلول ها پیوند دهند. ساده ترین راه این است که بدانیم Row به مفروضات بالای جدول و Column به مفروضات سمت چپ جدول اشاره دارد. بنابراین در این مثال در Row Input Cell باید سلول مرتبط با قیمت هر واحد یعنی D2 را قرار دهیم. چراکه مقادیر مختلف قیمت در ردیف مقابل تابع هدف قرار گرفته اند. در Column Input Cell هم باید سلول مرتبط با حجم فروش یعنی D3 را قرار دهیم. به این علت که مقادیر مختلف حجم فروش در ستون زیر تابع هدف قرار گرفته است.

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

باید دقت کنیم نمی توانیم سلولی را از جدول F2:K9 که شامل مقادیر مختلف تابع هدف است را حذف کنیم و باید کل جدول را یک جا حذف کنیم. این خاصیت به دلیل این است که Data Table در اکسل از جنس آرایه است.

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