مقدمه حول محاكاة مؤنتي كارلو في Excel

ملاحظة: إننا نرغب في تزويدك بأحدث محتوى تعليمات لدينا بأسرع ما يمكن، وبلغتك الخاصة. وقد تمت ترجمة هذه الصفحة آلياً، وقد تحتوي على أخطاء نحوية أو أخطاء تتعلق بالدقة. إن هدفنا هو أن يكون هذا المحتوى مفيداً لك. هل يمكنك إخبارنا إذا ما كانت المعلومات مفيدة لك في أسفل هذه الصفحة؟ فيما يلي المقالة باللغة الإنجليزية لتكون مرجعاً سهلاً لك.

تم تكييف هذه المقالة من تحليل بيانات Microsoft Excel وبناء الاعمال بواسطة وأين وينستون.

  • من الذي يستخدم محاكاة مؤنتي كارلو ؟

  • ماذا يحدث عندما تكتب = RAND () في خليه ؟

  • كيف يمكنك محاكاة قيم متغير عشوائي منفصل ؟

  • كيف يمكنك محاكاة القيم الخاصة بمتغير عشوائي عادي ؟

  • كيف يمكن لشركه بطاقات الترحيب تحديد عدد البطاقات التي يجب إنتاجها ؟

نرغب في تقدير احتمالات الاحداث غير المؤكدة. علي سبيل المثال ، ما احتماليه ان تحتوي التدفقات النقدية لمنتج جديد علي القيمة الصافية الموجبة (NPV) ؟ ما هو عامل المخاطر في قائمه الاستثمارات الخاصة بنا ؟ تمكننا محاكاة كارلو مؤنتي من التعرف علي المواقف التي تتسبب في الشك فيها ومن ثم تشغيلها علي جهاز كمبيوتر آلاف الأوقات.

ملاحظة:  ياتي مؤنتي كارلو الخاص بالاسم من عمليات محاكاة الكمبيوتر التي يتم تنفيذها اثناء ال1930s و 1940s لتقدير احتماليه ان السلسلة اللازمة ل atom ريكتيون لبومب ستعمل بنجاح. الفيسيسيستس المشتركة في هذا العمل كانت مراوح كبيره في المقامرة ، بحيث تعطيوا محاكاة اسم الرمز مؤنتي كارلو.

في الفصول الخمسة التالية ، ستري أمثله حول كيفيه استخدام Excel لتنفيذ عمليات محاكاة مؤنتي كارلو.

تستخدم العديد من الشركات محاكاة مؤنتي كارلو كجزء هام من عمليه إنشاء القرارات الخاصة بهم. اليك بعض الامثله.

  • موتورس عامه و بروكتور و جامبل و فيزير و بريستول و مييرس سكويب و إلى ليلي استخدام المحاكاة لتقدير كل من العائد المتوسط وعامل المخاطر الخاص بالمنتجات الجديدة. في جم ، يتم استخدام هذه المعلومات بواسطة المدير التنفيذي لتحديد المنتجات التي تاتي إلى السوق.

  • يستخدم جم المحاكاة للانشطه مثل التنبؤ بالإيرادات الصافية للشركة والتوقع الهيكلية وتكاليف الشراء وتحديد السوسسيبتيبيليتي إلى أنواع مختلفه من المخاطر (مثل تغييرات أسعار الفائدة وتقلبات أسعار الصرف).

  • يستخدم ليلي المحاكاة لتحديد سعة النبات المثالية لكل العقاقير.

  • تستخدم البروكتور والجامبل المحاكاة لإنشاء نموذج والمعروفة بشكل أمثل لمخاطر exchange الخارجية.

  • يستخدم سيرس المحاكاة لتحديد عدد الوحدات التي يجب طلبها من كل سطر منتج من الموردين ، علي سبيل المثال ، عدد أزواج دوكيرس تروسيرس التي يجب طلبها هذه السنه.

  • تستخدم شركات البترول والعقاقير المحاكاة للقيمة "الخيارات الحقيقية" مثل قيمه خيار لتوسيع مشروع أو عقده أو تاجيله.

  • المخططات المالية استخدم محاكاة مؤنتي كارلو لتحديد استراتيجيات الاستثمار المثالية لتقاعد العملاء.

عند كتابه الصيغة = RAND () في خليه ، ستحصل علي رقم من المحتمل ان يفترض اي قيمه بين 0 و 1. التالي ، يمكنك الحصول علي رقم اقل من 0.25 أو مساويا له ؛ حول 10 بالمائة من الوقت يجب الحصول علي رقم يكون علي الأقل 0.90 وما إلى ذلك. لتوضيح كيفيه عمل الدالة RAND ، ألق نظره علي الملف رانديمو ، الذي يظهر في الشكل 60-1.

صورة الكتاب

ملاحظة:  عند فتح الملف رانديمو ، لن تتمكن من رؤية الأرقام العشوائية نفسها المعروضة في الشكل 60-1. تقوم الدالة RAND دائما باعاده حساب الأرقام التي تنشا عند فتح ورقه عمل أو عند إدخال معلومات جديده في ورقه العمل.

أولا ، انسخ من الخلية C3 إلى C4: C402 الصيغة = RAND (). ثم تقوم بتسميه النطاق C3: C402 البيانات. بعد ذلك ، في العمود F ، يمكنك تعقب متوسط الأرقام العشوائية في 400 (الخلية F2) واستخدام الدالة COUNTIF لتحديد الكسور التي تقع بين 0 و 0.25 و 0.25 و 0.50 و 0.50 و 0.75 و 0.75 و و 1. عند الضغط علي المفتاح F9 ، يتم أعاده حساب الأرقام العشوائية. لاحظ ان متوسط أرقام 400 دوما هو 0.5 تقريبا ، والنسبة المئوية التي تبلغ 25 بالمائة من النتائج هي الفاصلة الزمنيه في 0.25. تتوافق هذه النتائج مع تعريف رقم عشوائي. لاحظ أيضا ان القيم التي تم إنشاؤها بواسطة RAND في خلايا مختلفه مستقله. علي سبيل المثال ، إذا كان الرقم العشوائي الذي تم إنشاؤه في الخلية C3 عبارة عن رقم كبير (علي سبيل المثال ، 0.99) ، فانه يعلمنا بعدم العثور علي قيم الأرقام العشوائية الأخرى التي تم إنشاؤها.

افترض ان الطلب لتقويم يخضع للمتغير العشوائي المنفصل التالي:

متزايد

Probability

10,000

0,10

20,000

0.35

40,000

0,3

60,000

0,25

كيف يمكننا تشغيل Excel ، أو محاكاة هذا الطلب للتقويمات عده مرات ؟ تتمثل الجولة في اقران كل قيمه محتمله للدالة RAND بالطلب المحتمل للتقويمات. يؤكد الواجب التالي انه ستتم مطالبه 10,000 بنسبه 10% من الوقت ، وهكذا.

متزايد

تعيين رقم عشوائي

10,000

اقل من 0.10

20,000

أكبر من أو يساوي 0.10 ، واقل من 0.45

40,000

أكبر من أو يساوي 0.45 ، واقل من 0.75

60,000

أكبر من أو يساوي 0.75

لشرح محاكاة الطلب ، انظر إلى الملف ديسكريتيسيم ، الذي يظهر في الشكل 60-2 علي الصفحة التالية.

صورة الكتاب

المفتاح لعمليه المحاكاة الخاصة بنا هو استخدام رقم عشوائي لبدء بحث من نطاق الجدول F2: G5 (البحث المسمي). ستعطي الأرقام العشوائية التي تكون أكبر من أو تساوي 0 واقل من 0.10 اي طلب 10,000 ؛ ستعطي الأرقام العشوائية التي تكون أكبر من أو تساوي 0.10 واقل من 0.45 الحاجة إلى 20,000 ؛ ستعطي الأرقام العشوائية التي تكون أكبر من أو تساوي 0.45 واقل من 0.75 الحاجة إلى 40,000 ؛ ستعطي الأرقام العشوائية التي تكون أكبر من أو تساوي 0.75 طلب 60,000. تقوم بإنشاء أرقام عشوائية 400 بالنسخ من C3 إلى C4: C402 الصيغة RAND (). يمكنك بعد ذلك إنشاء تجارب 400 أو التكرارات الخاصة بطلب التقويم عن طريق النسخ من B3 إلى B4: B402 الدالة VLOOKUP (C3 ، بحث ، 2). تضمن هذه الصيغة ان اي رقم عشوائي اقل من 0.10 ينشئ طلب 10,000 ، اي رقم عشوائي بين 0.10 و 0.45 ينشئ طلب 20,000 ، وهكذا. في نطاق الخلايا F8: F11 ، استخدم الدالة COUNTIF لتحديد كسر تكرار 400 ييلدينج كل طلب. عند الضغط علي F9 لأعاده حساب الأرقام العشوائية ، يتم إغلاق الاحتمالات التي تمت محاكاتها إلى احتمالات الطلب المفترضة.

إذا قمت بالكتابة في اي خليه ، ستنشئ الصيغة NORMINV (rand () ، mu ، sigma)، ستقوم بإنشاء قيمه محاكاة لمتغير عشوائي عادي بحيث يكون متوسط mu والانحراف المعياري القياسي. يتم توضيح هذا الاجراء في الملف نورمالسيم ، الذي يظهر في الشكل 60-3.

صورة الكتاب

فلنفترض اننا نريد محاكاة تجربه 400 أو التكرارات ، لمتغير عشوائي عادي بوسط 40,000 وانحراف معياري ل 10,000. (يمكنك كتابه هذه القيم في الخلايا E1 و E2 ، وتسميه هذه الخلايا بأنها تعني و sigma، علي التوالي.) نسخ الصيغة = RAND () من C4 إلى C5: C403 ينشئ 400 أرقام عشوائية مختلفه. النسخ من B4 إلى B5: B403 الصيغة NORMINV (C4, mean, sigma) بإنشاء 400 قيمه تجريبية مختلفه من متغير عشوائي عادي بوسط 40,000 وانحراف معياري ل 10,000. عند الضغط علي المفتاح F9 لأعاده حساب الأرقام العشوائية ، يظل الوسط قريبا من 40,000 سيتم إغلاق الانحراف المعياري إلى 10,000.

بشكل أساسي ، بالنسبة إلى الرقم العشوائي x، تنشئ الصيغة NORMINV (p, mu, sigma) النسبة المئوية pلمتغير عشوائي عادي مع وسط mu وانحراف معياري sigma. علي سبيل المثال ، يتم إنشاء الرقم العشوائي 0.77 في الخلية C4 (راجع الشكل 60-3) في الخلية B4 تقريبا النسبة المئوية ل77th متغير عشوائي عادي بوسط 40,000 وانحراف معياري 10,000.

في هذا القسم ، ستشاهد كيف يمكن استخدام محاكاة مؤنتي كارلو كاداه صنع قرار. افترض ان الطلب الخاص ببطاقة عيد الحب يخضع للمتغير العشوائي المنفصل التالي:

متزايد

Probability

10,000

0,10

20,000

0.35

40,000

0,3

60,000

0,25

تبيع بطاقة الترحيب ل $4.00 ، والتكلفة المتغيرة لإنتاج كل بطاقة هو $1.50. يجب التخلص من بطاقات ليفتوفير بتكلفه $0.20 لكل بطاقة. ما عدد البطاقات المطلوب طباعتها ؟

بشكل أساسي ، نقوم بمحاكاة كل كميه من كميات الإنتاج المحتملة (10,000 أو 20,000 أو 40,000 أو 60,000) عده مرات (علي سبيل المثال ، تكرارات 1000). ثم نقوم بتحديد كميه الطلبات التي تعطي الحد الأقصى لمتوسط الربح في 1000 تكرارات. يمكنك العثور علي البيانات الخاصة بهذا المقطع في الملف الذي تم عرضه في الشكل 60-4. يمكنك تعيين أسماء النطاقات في الخلايا B1: B11 إلى الخلايا من C1: C11. يتم تعيين قيمه البحث عنالاسم G3: H6. يتم إدخال سعر المبيعات ومعلمات التكلفة في الخلايا C4: C6.

صورة الكتاب

يمكنك إدخال كميه الإنتاج التجريبية (40,000 في هذا المثال) في الخلية C1. بعد ذلك ، أنشئ رقما عشوائيا في الخلية C2 باستخدام الصيغة = RAND (). كما هو موضح سابقا ، فانك تحاكي طلب البطاقة في الخلية C3 باستخدام الدالة VLOOKUP (rand ، lookup ، 2). (في صيغه VLOOKUP ، rand هو اسم الخلية المعين إلى الخلية C3 ، وليس الدالة rand.)

عدد الوحدات المباعة هو الأصغر من كميه الإنتاج والطلب. في الخلية C8 ، يمكنك حساب العائد الخاص بنا باستخدام الصيغة الأدنى (الناتجة ، الطلب) * unit_price. في الخلية C9 ، يمكنك حساب إجمالي تكلفه الإنتاج بالصيغة الناتجة * unit_prod_cost.

إذا قمنا بتقديم المزيد من البطاقات التي تمت في الطلب ، سيكون عدد الوحدات المتبقية أكبر من الحاجة إلى طرح المنتج بخلاف ذلك لا توجد وحدات متبقية. نقوم بحساب تكلفه التخلص الخاصة بنا في الخلية C10 مع الصيغة unit_disp_cost * IF (produced_gt_demand ، التي تم إنتاجها – الطلب ، 0). وأخيرا ، في الخلية C11 ، نقوم بحساب أرباحنا كايرادات -total_var_cost-total_disposing_cost.

نحن نريد طريقه فعاله للضغط علي العديد من المرات (علي سبيل المثال ، 1000) لكل كميه إنتاج وتالي الأرباح المتوقعة لكل كميه. هذا الموقف هو الذي ياتي فيه جدول بيانات ثنائي الاتجاه لإنقاذنا. (راجع الفصل 15 ، "تحليل الحساسية مع جداول البيانات" ، للحصول علي تفاصيل حول جداول البيانات.) يتم عرض جدول البيانات المستخدم في هذا المثال في الشكل 60-5.

صورة الكتاب

في نطاق الخلايا A16: A1015 ، ادخل الأرقام 1 – 1000 (المناظرة لتجاربنا في 1000). تتمثل أحدي الطرق السهلة لإنشاء هذه القيم في البدء بإدخال 1 في الخلية A16. حدد الخلية ، ثم علي علامة التبويب الصفحة الرئيسية في المجموعة تحرير ، انقر فوق تعبئة، ثم حدد سلسله لعرض مربع الحوار سلسله . في مربع الحوار سلسله ، الذي يظهر في الشكل 60-6 ، ادخل قيمه الخطوة 1 وقيمه التوقف 1000. في المنطقة سلسله في ، حدد الخيار أعمده ، ثم انقر فوق موافق. سيتم إدخال الأرقام من 1 – 1000 في العمود A بدءا من الخلية A16.

صورة الكتاب

بعد ذلك ، قم بإدخال كميات الإنتاج المحتملة (10,000 ، 20,000 ، 40,000 ، 60,000) في الخلايا B15: E15. نحن نريد حساب ربح كل رقم تجريبي (من 1 إلى 1000) وكل كميه إنتاج. اننا نقوم بالاشاره إلى المعادلة للربح (محسوبة في الخلية C11) في الخلية العلوية اليمني من جدول البيانات الخاص بنا (A15) بإدخال = C11.

أنت الآن جاهز لجولة في Excel في محاكاة 1000 تكرارات الطلب لكل كميه إنتاج. حدد نطاق الجدول (A15: E1014) ، ثم في المجموعة أدوات البيانات علي علامة التبويب بيانات ، انقر فوق ما إذا كان التحليل ، ثم حدد جدول البيانات. لاعداد جدول بيانات ثنائي الاتجاه ، اختر كميه الإنتاج الخاصة بنا (الخلية C1) كخليه إدخال الصف وحدد اي خليه فارغه (اخترت الخلية I14) كخليه إدخال العمود. بعد النقر فوق موافق ، يحاكي Excel قيم الطلب 1000 لكل كميه من الطلبات.

لفهم سبب هذا العمل ، ضع في اعتبارك القيم الموضوعة بواسطة جدول البيانات في نطاق الخلايا C16: C1015. بالنسبة إلى كل من هذه الخلايا ، سيستخدم Excel قيمه 20,000 في الخلية C1. في C16 ، يتم وضع قيمه خليه إدخال العمود 1 في خليه فارغه والرقم العشوائي في الخلية C2 لأعاده الحساب. يتم بعد ذلك تسجيل الأرباح المقابلة في الخلية C16. ثم يتم وضع قيمه إدخال خليه العمود 2 في خليه فارغه ، ويعيد الرقم العشوائي في C2 مره أخرى. يتم إدخال الأرباح المقابلة في الخلية C17.

بالنسخ من الخلية B13 إلى C13: E13 متوسط الصيغة (B16: B1015)، قم بحساب متوسط الربح الذي تمت محاكاته لكل كميه إنتاج. بالنسخ من الخلية B14 إلى C14: E14 الصيغة STDEV (B16: B1015)، فاننا نقوم بحساب الانحراف المعياري لأرباحنا التي تمت محاكاتها لكل كميه طلب. في كل مره تقوم فيها بالضغط علي F9 ، يتم محاكاة 1000 تكرارات الطلب لكل كميه طلب. يؤدي إنتاج بطاقات 40,000 دائما إلى اقصي ربح متوقع. ولذلك ، يبدو ان إنتاج بطاقات 40,000 هو القرار المناسب.

تاثير المخاطر علي قرارنا     إذا قمنا بإنشاء 20,000 بدلا من بطاقات 40,000 ، فان الأرباح المتوقعة الخاصة بنا سيسقط تقريبا 22 بالمائة ، ولكن خطره (كما هو موضح بواسطة الانحراف المعياري للربح) يسقط تقريبا النسبة المئوية ل73. ولذلك ، إذا كاننا عبارة averseا جدا للمخاطرة ، فقد يكون إنتاج بطاقات 20,000 القرار المناسب. إينسيدينتالي ، ينتج عن إنتاج بطاقات 10,000 دوما انحراف معياري للبطاقات لأنه إذا كان لديك بطاقة 10,000 ، سنقوم دائما ببيع كل هذه البطاقات بدون اي بقايا.

ملاحظة:  في هذا المصنف ، يتم تعيين الخيار الحساب إلى تلقائي باستثناء الجداول. (استخدم الأمر حساب في المجموعة حساب ضمن علامة التبويب الصيغ.) يضمن هذا الاعداد أعاده حساب جدول البيانات الخاص بنا الا إذا قمنا بالضغط علي F9 ، وهو فكره جيده لان جدول بيانات كبير سيؤدي إلى إبطاء عملك إذا تمت أعاده حسابه في كل مره تقوم فيها بكتابه شيء ما في ورقه العمل الخاصة بك. لاحظ انه في هذا المثال ، عند الضغط علي F9 ، سيتغير الربح المتوسط. يحدث هذا لأنه في كل مره تقوم فيها بالضغط علي F9 ، يتم استخدام تسلسل مختلف من 1000 أرقام عشوائية لإنشاء مطالبات لكل كميه من الطلبات.

فاصل الثقة للحصول علي الأرباح المتوسطة     السؤال الطبيعي الذي يجب ان تطلبه في هذا الوضع هو ، إلى اي فاصل سيتم طرح الربح الصحيح فيه ؟ يسمي هذا الفاصل الزمني النسبة المئوية لثقة 95 بالمائة لربح المتوسط. يتم حساب الفاصل الزمني لثقة 95 بالنسبة المئوية لوسط اي إخراج محاكاة بواسطة الصيغة التالية:

صورة الكتاب

في الخلية J11 ، يمكنك حساب الحد الأدنى لفاصل الثقة في 95 بالمائة علي الأرباح المتوسطة عند إنشاء تقويمات 40,000 باستخدام الصيغة D13 – 1.96 * D14/SQRT (1000). في الخلية J12 ، يمكنك حساب الحد الأعلى للفاصل الزمني الخاص بنا في 95 بالمائة من الصيغة D13 + 1.96 * D14/SQRT (1000). يتم عرض هذه الحسابات في الشكل 60-7.

صورة الكتاب

نحن نوصي بالنسبة المئوية التي نقدمها لأنه الأرباح التي يتم فيها الربح عند طلب التقويمات في 40,000 بين $56,687 و $62,589.

  1. تعتقد جمك الخاصة بالبائع انه سيتم توزيع الطلب ل 2005 انفويس بوسط 200 والانحراف المعياري ل 30. ان تكلفه استلام انفوي هو $25,000 ، وتبيع انفوي ل $40,000. نصف كل انفيس التي لم يتم بيعها بسعر كامل يمكن بيعها في $30,000. من الاعتبار طلب 200 أو 220 أو 240 أو 260 أو 280 أو 300 انفيس. ما هو العدد الذي يجب ان يكون عليه الطلب ؟

  2. يحاول سوبرماركت صغير تحديد عدد نسخ الأشخاص الذين يجب ان يقوموا بطلبها كل أسبوع. انها تعتقد انه قد تم التحكم في طلبات الأشخاص من قبل المتغير العشوائي المنفصل التالي:

    متزايد

    Probability

    15

    0,10

    20

    0.20

    ذا

    0.30

    30

    0,25

    35

    0.15

  3. يدفع سوبرماركت $1.00 لكل نسخه من الأشخاص وتبيعه ل $1.95. يمكن إرجاع كل نسخه من أونسولد ل $0.50. ما عدد نسخ الأشخاص الذين يجب ان يقوموا بطلب المتجر ؟

هل تحتاج إلى مزيد من المساعدة؟

ويمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

تعزيز مهارات Office
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×