شنبه , ۳ تیر ۱۳۹۶
صفحه نخست / توسعه مهارتهای اکسل / Data Tables , Simulations & Solver / ِData table و Simulation مونت کارلو در اکسل

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

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

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

Data table-Excel

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

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

Data table-Excel کاربرد Data table در اکسل

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

Data table-Excel کاربرد Data table در اکسل۲

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

Data table-Excel کاربرد Data table در اکسل۳

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

Data table-Excel کاربرد Data table در اکسل۴

 

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

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

Data table-Excel کاربرد Data table در اکسل۵

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

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

 

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

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

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

Data table-Excel کاربرد Data table در اکسل۵-بصورت درصد

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

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

Data table-Excel کاربرد Data table در اکسل- جدول دو متغیره

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

Data table-Excel کاربرد Data table در اکسل- جدول دو متغیره۲

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

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

 

 

 

 

۱ دیدگاه

  1. اقا دمتون گرم من که از سایتتون خوشم اومد . در زمینه برنامه ریزی تولید هم فایل بزارید اکه مطلب یا داده یا مسئله ای خواسبید کمکتون می کنم . با تشکر

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

چهارده − 11 =

Time limit is exhausted. Please reload CAPTCHA.