ِData table و Simulation مونت کارلو در اکسل

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

فرض کنید واحد بازاریابی اطلاعات اولیه ای را از میزان تولید اولیه محصول، قیمت گذاری، هزینه و در نتیجه سود اولیه در اختیار تان قرار داده است و شما میخواهید سناریوهای مختلف را برای این محصول جدید بسنجید:

حالا میخواهیم بدانیم اگر قیمت را دست کاری کنیم چه اتفاقی برای سود نهایی محصول می افتد؟ قیمت را تا چه پایین بیاوریم که سودمان منفی نشود و تا چه حد بالا ببریم که سود مورد نظر مدیریت حاصل شود؟

برای اینکار ابتدا لیست قیمتهای مورد نظرتان را زیر هم بنویسید. مانند تصویر زیر:

سپس کل محدوده F3:G24 را انتخاب نموده به تب Data بروید در گزینه What-if Analysis روی گزینه Data table کلیک نمایید.

سپس پنجره ای باز میشود که دارای دو قسمت است. چون جدولتان ستونی است قسمت Row را خالی بگذارید و در دومین قسمت یعنی Column Input Cell سلول مربوط به قیمت یعنی B3 را مشخص کنید:

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

 

همانطور که در تصویر بالا مشاهده مینمایید چنانچه قیمت محصول چیزی بین ۱۱۰۰۰ و ۱۱۵۰۰ باشد به نقطه سر به سر قیمت و سود خواهید رسید. یعنی چنانچه بخواهید با فروش ۱۰۰۰ تن به سود برسید حداقل باید قیمت فراتر از ۱۱۰۰۰ تومان برای محصول در نظر بگیرید. برای اینکه بتوانید قیمت دقیق را محاسبه کنید باید مقادیر بین این دو عدد را در جدول بگذارید، اعداد خودبخود تغییر میکنند و نتیجه مورد نظرتان را خواهید دید.

حالا اگر بخواهید بدانید با همین قیمت ۱۶۸۰۰ تومان باید چه تعداد محصول به فروش برسانید تا به سود برسید می توانید مانند روش بالا محاسبات را انجام دهید.

همانطور که مشاهده می کنید حداقل مقدارفروش برای این محصول باید  ۶۷۰ عدد باشد تا زیان نکنیم. می توانید با تغییر اعداد بصورت دستی در همین جدول نتیجه اعداد مورد نظرتان را مشاهده کنید.

نکته: اگر داده هایتان را بصورت افقی می نویسید در پنجره Data table باید Row Input Cell را تکمیل کنید و دومین گزینه را خالی بگذارید.

 

حالا فرض کنید میخواهید دیتاهایتان بصورت درصد باشد.

یعنی میخواهید بدانید چند درصد باید در مقدار یا قیمت تغییر ایجاد کنید تا سودتان تغییر کند. کاری ندارد:

فرض کنید میخواهیم اینکار را برای سلول مقدار یعنی B2 انجام دهیم. در یک سلول خالی مثلا D2 میزان درصد پیش فرض را صفر میگذاریم و سلول مربوط به عدد مقدار را اصلاح میکنیم و مینویسیم ۱۰۰۰*(۱+D2)

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

فرض کنید میخواهیم بدانیم اگر بصورت همزمان هم هزینه ها و هم قیمت را دست کاری کنیم سود نهایی چه تغییر خواهد کرد؟ برای اینکار نیاز به جدولی با دو سری متغیر داریم یکی بصورت افقی نوشته میشود و متغیر دوم بصورت عمودی. همانطور که در تصویر زیر مشاهده مینمایید قیمتها را در بالای جدول و مقادیر مختلف را در ستون ابتدایی جدول قرار دادیم.

حالا کافیست به تی Data بروید روی گزینه what-If Analysis کلیک نمایید و گزینه Data table را انتخاب نموده و این بار هر دو فیلد Row input cell و Column input cell را تکمیل نمایید. برای قسمت اول عدد مرتبط با قیمت یعنی B3 و برای فیلد دوم سلول B2 را انتخاب نمایید و Ok  کنید. آنگاه تصویر زیر را خواهید دید.

همانطور که مشخص است با نغییرات مختلف در دو متغیر مقدار و قیمت، سود نهایی حاصل شده متفاوت است. مثلا در قیمت ۱۵۰۰۰ تومان و مقدار فروش ۷۵۰ عدد سودمان برابر صفر خواهد بود و ۷۵۱ امین فروش برایمان سود ایجاد خواهد کرد.

فایل اکسل همین موضوع را دانلود نمایید