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

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

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

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

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

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

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

في الفصول الخمسة التالية، سترى أمثلة على كيفية استخدام Excel لإجراء عمليات محاكاة مونتي كارلو.

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

• تستخدم جنرال موتورز وبروكتور وغامبل وPfizer Bristol-Myers Squibb وEli Lilly المحاكاة لتقدير متوسط العائد وعامل الخطر للمنتجات الجديدة. في جنرال موتورز، يتم استخدام هذه المعلومات من قبل الرئيس التنفيذي لتحديد المنتجات التي تأتي إلى السوق.

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

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

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

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

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

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

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

أولا، انسخ الصيغة =RAND()من الخلية C3 إلى C4:C402. ثم يمكنك تسمية النطاق C3:C402 Data. بعد ذلك، في العمود 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 بطلب محتمل للتقويمات. يضمن التعيين التالي حدوث طلب قدره 10000 بنسبة 10 بالمائة من الوقت، وما إلى ذلك.

الطلب	تم تعيين رقم عشوائي
10,000	أقل من 0.10
20,000	أكبر من أو يساوي 0.10، وأقل من 0.45
40,000	أكبر من أو يساوي 0.45، وأقل من 0.75
60,000	أكبر من أو يساوي 0.75

لإظهار محاكاة الطلب، انظر إلى الملف Discretesim.xlsx، الموضح في الشكل 60-2 في الصفحة التالية.

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

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

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

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

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

الطلب	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 تكرار. يمكنك العثور على بيانات هذا القسم في Valentine.xlsx الملف، الموضحة في الشكل 60-4. يمكنك تعيين أسماء النطاقات في الخلايا B1:B11 إلى الخلايا C1:C11. يتم تعيين نطاق الخلية G3:H6 للبحث عن الاسم. يتم إدخال معلمات سعر المبيعات والتكلفة في الخلايا C4:C6.

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

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

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

نود أن تكون هناك طريقة فعالة للضغط على F9 عدة مرات (على سبيل المثال، 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 قيمة 20000 في الخلية C1. في C16، يتم وضع قيمة خلية إدخال العمود 1 في خلية فارغة ويعيد الرقم العشوائي في الخلية C2 الحساب. ثم يتم تسجيل الربح المقابل في الخلية C16. ثم يتم وضع قيمة إدخال خلية العمود 2 في خلية فارغة، ويعيد الرقم العشوائي في C2 الحساب مرة أخرى. يتم إدخال الربح المقابل في الخلية C17.

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

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

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

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

نحن متأكدون بنسبة 95 في المائة من أن متوسط الربح عند طلب 40000 تقويم يتراوح بين 56687 دولارا و62589 دولارا.

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

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

   الطلب	Probability
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0.15

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