معرفی ابزار کاربردی 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 در اکسل از جنس آرایه است.
امیدواریم که این مثال توانسته باشد نحوه استفاده از این ابزار کاربردی را برای شما روشن سازد.
درباره الناز پورمحمد
دانش آموخته اقتصاد دانشگاه شهید بهشتی، علاقمند به مطالعات امکان سنجی، تحقیقات بازار و مدلسازی مالی
نوشته های بیشتر از الناز پورمحمدمطالب زیر را حتما مطالعه کنید
آموزش ابزار solver در اکسل
یکی از ابزارهای بسیار کاربردی در نرم افزار اکسل، افزونه solver است. با کمک این ابزار می توان به راه حل های بهینه در حل مسائل و فرآیند تصمیم گیری دست یافت. رسیدن به بهینه ترین راه حل می تواند شامل حداکثر سود ، حداقل کردن هزینه یا استفاده ازبهترین تخصیص منابع در فرآیند تولید باشد.
نرخ هزینه فرصت اسمی و موثر در اکسل
در این مطلب به نحوه محاسبه نرخ هزینه فرصت اسمی و موثر سالانه به کمک دستورات مالی در نرم افزار اکسل خواهیم پرداخت. به طور خلاصه نرخ هزینه فرصت موثر سالانه، نشان دهنده بازده حقیقی یک سرمایه گذاری است که دربرگیرنده اثرات تجمعی سرمایه گذاری در طول زمان است. بالعکس نرخ هزینه فرصت اسمی سالانه نرخ ساده ای است که بدون در نظر گرفتن اثرات تجمعی سرمایه، در محاسبات مورد استفاده قرار می گیرد.
خالص ارزش حال (NPV) و نرخ بازده داخلی (IRR) در اکسل
خالص ارزش حال(NPV) و نرخ بازده داخلی(IRR) دو معیار مهم در مقایسه و الویت بندی طرح های سرمایه گذاری هستند. در این مطلب، ابتدا به تفصیل به مفاهیم این دو معیار خواهیم پرداخت و در ادامه به دستورهای مربوط به آنها در نرم افزار اکسل اشاره خواهیم کرد.
تحلیل سناریو در مدلسازی مالی
همواره عناصر بالقوه مطلوب و نامطلوبی وجود دارد که ممکن است بر عملکرد مالی کسب و کار ما تاثیر بگذارد. از اینرو در مدلسازی مالی، انجام تحلیل سناریو در مدل مالی برای سنجش شرایط نامعلوم آینده ضرورتی بسیار مهم است.
چگونه یک مدل مالی می سازیم؟
در مدلسازی مالی طراحی و ساختاربندی، اهمیتی هم تراز با محتوا دارد. این اهمیت از آن جهت است که یک طراحی مناسب، کمک می کند که یک مدل مالی را آسان درک کنیم و تجزیه و تحلیل مالی برای کاربر نهایی، عاری از هرگونه پیچیدگی و ابهام باشد.
معرفی انواع مختلف مدل های مالی
انواع مختلفی از مدل های مالی وجود دارد که یک مدلساز مالی با توجه به مشکلی که سعی در حل آن دارد، می تواند از آنها استفاده کند. ما در این مطلب، به رایج ترین مدل های مالی اشاره کرده ایم.
سلام و وقت بخیر خدمت شما
ممنون از آموزش شما فقط تا محاسبات (ضرب و تفریق)انجام نشود این آنالیز کار نمیکند و دیگر مثال شما ایراد دارد
سلام و عرض ادب ممنونم از شما. اگر درست متوجه شده باشم می فرمائید متغیر های مستقل که فروش و قیمت هر واحد است در تابع هدف که سود عملیاتی هست اثزگذاری ندارند در این مثال؟ اگر در تصاویر دقت کرده باشین در مثال، درآمد ۲۰۰ میلیون ریالی ما از ضرب ۲۰۰ واحد در قیمت هر واحد به ارزش ۱ میلیون ریال بدست آمد و در مراحل بعدی پس از کسر هزینه متغیر و ثابت به سود عملیاتی می رسیم. بنابراین در سود عملیاتی اثر دو متغیر وجود دارد. مسلما تابع هدف باید بر اساس متغیرهای مستقل تعریف شود تا… ادامه...