Εισαγωγή στην προσομοίωση του Monte Carlo στο Excel

• Ποιος χρησιμοποιεί προσομοίωση του Μόντε Κάρλο;

• Τι συμβαίνει όταν πληκτρολογείτε =RAND() σε ένα κελί;

• Πώς μπορείτε να προσομοιώσετε τιμές μιας διακριτής τυχαίας μεταβλητής;

• Πώς μπορείτε να προσομοιώσετε τιμές μιας κανονικής τυχαίας μεταβλητής;

• Πώς μπορεί μια εταιρεία ευχετήριων καρτών να καθορίσει πόσες κάρτες θα παράγει;

Θα θέλαμε να εκτιμήσουμε με ακρίβεια τις πιθανότητες αβέβαιων γεγονότων. Για παράδειγμα, ποια είναι η πιθανότητα οι ταμειακές ροές ενός νέου προϊόντος να έχουν θετική καθαρή παρούσα αξία (NPV); Ποιος είναι ο παράγοντας κινδύνου του επενδυτικού χαρτοφυλακίου μας; Η προσομοίωση του Μόντε Κάρλο μας δίνει τη δυνατότητα να μοντελαπομορφώνουμε καταστάσεις που παρουσιάζουν αβεβαιότητα και στη συνέχεια να τις αναπαράγουμε σε έναν υπολογιστή χιλιάδες φορές.

Στα επόμενα πέντε κεφάλαια, θα δείτε παραδείγματα για το πώς μπορείτε να χρησιμοποιήσετε το Excel για να εκτελέσετε προσομοιώσεις του Monte Carlo.

Πολλές εταιρείες χρησιμοποιούν την προσομοίωση του Monte Carlo ως σημαντικό μέρος της διαδικασίας λήψης αποφάσεων. Ακολουθούν ορισμένα παραδείγματα.

• Η General Motors, η Proctor και η Gamble, η Pfizer, η Bristol-Myers Squibb και ο Eli Lilly χρησιμοποιούν προσομοίωση για να εκτιμήσουν τόσο τη μέση απόδοση όσο και τον παράγοντα κινδύνου νέων προϊόντων. Στην GM, αυτές οι πληροφορίες χρησιμοποιούνται από τον Διευθύνοντα Σύμβουλο για να καθορίσουν ποια προϊόντα διατίθενται στην αγορά.

• Η GM χρησιμοποιεί προσομοίωση για δραστηριότητες όπως η πρόβλεψη καθαρών εσόδων για την εταιρεία, η πρόβλεψη του διαρθρωτικού κόστους και του κόστους αγοράς και ο προσδιορισμός της ευαισθησίας της σε διαφορετικά είδη κινδύνου (όπως οι αλλαγές των επιτοκίων και οι διακυμάνσεις των συναλλαγματικών ισοτιμιών).

• Η Lilly χρησιμοποιεί προσομοίωση για να καθορίσει τη βέλτιστη χωρητικότητα φυτών για κάθε φάρμακο.

• Η Proctor και η Gamble χρησιμοποιούν την προσομοίωση για τη δημιουργία μοντέλων και τη βέλτιστη αντιστάθμιση του συναλλαγματικού κινδύνου.

• Η Sears χρησιμοποιεί προσομοίωση για να καθορίσει πόσες μονάδες κάθε γραμμής προϊόντος θα πρέπει να παραγγελθούν από προμηθευτές — για παράδειγμα, τον αριθμό των ζευγαριών παντελονιών Dockers που θα πρέπει να παραγγελθούν φέτος.

• Οι πετρελαϊκές και φαρμακευτικές εταιρείες χρησιμοποιούν προσομοίωση για να εκτ γιατί εκτιμούν τις "πραγματικές επιλογές", όπως την αξία μιας επιλογής επέκτασης, σύμπτυξης ή αναβολής ενός έργου.

• Οι οικονομικοί σχεδιαστές χρησιμοποιούν την προσομοίωση του Monte Carlo για να καθορίσουν βέλτιστες επενδυτικές στρατηγικές για τη συνταξιοδότηση των πελατών τους.

Όταν πληκτρολογείτε τον τύπο =RAND() σε ένα κελί, λαμβάνετε έναν αριθμό που είναι εξίσου πιθανό να αναλάβει οποιαδήποτε τιμή μεταξύ 0 και 1. Έτσι, περίπου το 25 τοις εκατό του χρόνου, θα πρέπει να πάρετε έναν αριθμό μικρότερο ή ίσο με 0,25. περίπου το 10 τοις εκατό του χρόνου θα πρέπει να πάρετε έναν αριθμό που είναι τουλάχιστον 0,90 και ούτω καθεξής. Για να δείξετε πώς λειτουργεί η συνάρτηση RAND, ρίξτε μια ματιά στο αρχείο Randdemo.xlsx, που εμφανίζεται στην Εικόνα 60-1.

Πρώτα, αντιγράψτε από το κελί 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), δεν μας λέει τίποτα για τις τιμές των άλλων τυχαίων αριθμών που δημιουργούνται.

Ας υποθέσουμε ότι η απαίτηση για ένα ημερολόγιο διέπεται από την παρακάτω διακριτή τυχαία μεταβλητή:

Ζήτηση	Πιθανότητα
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

Για να επιδείξετε την προσομοίωση της ζήτησης, δείτε τις Discretesim.xlsx αρχείων, που εμφανίζονται στην Εικόνα 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 και σίγμα τυπικής απόκλισης. Αυτή η διαδικασία απεικονίζεται στο αρχείο Normalsim.xlsx, που φαίνεται στην Εικόνα 60-3.

Ας υποθέσουμε ότι θέλουμε να προσομοιώσουμε 400 δοκιμές, ή επαναλήψεις, για μια κανονική τυχαία μεταβλητή με μέσο 40.000 και τυπική απόκλιση 10.000. (Μπορείτε να πληκτρολογήσετε αυτές τις τιμές στα κελιά E1 και E2 και να ονομάσετε αυτά τα κελιά ως μέση τιμή και σίγμα αντίστοιχα.) Η αντιγραφή του τύπου =RAND() από το C4 στο C5:C403 δημιουργεί 400 διαφορετικούς τυχαίους αριθμούς. Η αντιγραφή από το B4 στο B5:B403 του τύπου NORMINV(C4;μέση_τιμή;σίγμα) παράγει 400 διαφορετικές δοκιμαστικές τιμές από μια κανονική τυχαία μεταβλητή με μέση τιμή 40.000 και τυπική απόκλιση 10.000. Όταν πατήσουμε το πλήκτρο F9 για να επαναλάβουμε τον υπολογισμό των τυχαίων αριθμών, ο μέσος όρος παραμένει κοντά στο 40.000 και η τυπική απόκλιση κοντά στο 10.000.

Ουσιαστικά, για έναν τυχαίο αριθμό x, ο τύπος NORMINV(p,mu,sigma) δημιουργεί το pεκατοστημόριο μιας κανονικής τυχαίας μεταβλητής με μέση τιμή mu και σίγμα τυπικής απόκλισης. Για παράδειγμα, ο τυχαίος αριθμός 0,77 στο κελί C4 (βλ. Εικόνα 60-3) δημιουργεί στο κελί B4 περίπου το 77ο εκατοστημόριο μιας κανονικής τυχαίας μεταβλητής με μέσο όρο 40.000 και τυπική απόκλιση 10.000.

Σε αυτή την ενότητα, θα δείτε πώς μπορεί να χρησιμοποιηθεί η προσομοίωση του Monte Carlo ως εργαλείο λήψης αποφάσεων. Ας υποθέσουμε ότι η απαίτηση για κάρτα του Αγίου Βαλεντίνου διέπεται από την ακόλουθη διακριτή τυχαία μεταβλητή:

Ζήτηση	Πιθανότητα
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.

Μπορείτε να εισαγάγετε μια ποσότητα δοκιμαστικής παραγωγής (40.000 σε αυτό το παράδειγμα) στο κελί C1. Στη συνέχεια, δημιουργήστε έναν τυχαίο αριθμό στο κελί C2 με τον τύπο =RAND(). Όπως περιγράφεται προηγουμένως, προσομοιώνετε την απαίτηση για την κάρτα στο κελί C3 με τον τύπο VLOOKUP(rand;αναζήτηση;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 πολλές φορές (για παράδειγμα, 1.000) για κάθε ποσότητα παραγωγής και να υπολογίζουμε το αναμενόμενο κέρδος μας για κάθε ποσότητα. Αυτή είναι μια κατάσταση στην οποία ένας αμφίδρομος πίνακας δεδομένων έρχεται να μας σώσει. (Ανατρέξτε στο Κεφάλαιο 15, "Ανάλυση ευαισθησίας με πίνακες δεδομένων", για λεπτομέρειες σχετικά με τους πίνακες δεδομένων.) Ο πίνακας δεδομένων που χρησιμοποιείται σε αυτό το παράδειγμα εμφανίζεται στην Εικόνα 60-5.

Στην περιοχή κελιών A16:A1015, εισαγάγετε τους αριθμούς 1–1000 (που αντιστοιχούν στις 1000 δοκιμές μας). Ένας εύκολος τρόπος για να δημιουργήσετε αυτές τις τιμές είναι να ξεκινήσετε πληκτρολογώντας 1 στο κελί A16. Επιλέξτε το κελί και, στη συνέχεια, στην Κεντρική καρτέλα της ομάδας Επεξεργασία , κάντε κλικ στην επιλογή Γέμισμα και επιλέξτε Σειρά για να εμφανιστεί το παράθυρο διαλόγου Σειρά . Στο παράθυρο διαλόγου Σειρά , που εμφανίζεται στην Εικόνα 60-6, πληκτρολογήστε τιμή βήματος 1 και τιμή διακοπής 1000. Στην περιοχή Σειρά σε , επιλέξτε Στήλες και, στη συνέχεια, κάντε κλικ στο κουμπί OK. Οι αριθμοί 1–1000 θα εισαχθούν στη στήλη A ξεκινώντας από το κελί A16.

Στη συνέχεια εισάγαμε τις πιθανές ποσότητες παραγωγής (10.000, 20.000, 40.000, 60.000) στα κελιά B15:E15. Θέλουμε να υπολογίσουμε το κέρδος για κάθε αριθμό δοκιμής (1 έως 1000) και κάθε ποσότητα παραγωγής. Αναφερόμαστε στον τύπο κέρδους (υπολογίζεται στο κελί C11) στο επάνω αριστερό κελί του πίνακα δεδομένων μας (A15) πληκτρολογώντας =C11.

Τώρα είμαστε έτοιμοι να εξαπατήσουμε το Excel ώστε να προσομοιώσει 1000 επαναλήψεις ζήτησης για κάθε ποσότητα παραγωγής. Επιλέξτε την περιοχή πίνακα (A15:E1014) και, στη συνέχεια, στην ομάδα Εργαλεία δεδομένων στην καρτέλα Δεδομένα, κάντε κλικ στην επιλογή Ανάλυση what if και, στη συνέχεια, επιλέξτε Πίνακας δεδομένων. Για να ρυθμίσετε έναν αμφίδρομο πίνακα δεδομένων, επιλέξτε την ποσότητα παραγωγής (κελί C1) ως κελί εισαγωγής γραμμής και επιλέξτε οποιοδήποτε κενό κελί (επιλέξαμε το κελί I14) ως κελί εισαγωγής στήλης. Αφού κάνετε κλικ στο κουμπί OK, το Excel προσομοιώνει 1000 τιμές ζήτησης για κάθε ποσότητα παραγγελίας.

Για να κατανοήσετε γιατί λειτουργεί αυτό, εξετάστε τις τιμές που τοποθετούνται από τον πίνακα δεδομένων στην περιοχή κελιών C16:C1015. Για κάθε ένα από αυτά τα κελιά, το Excel θα χρησιμοποιήσει την τιμή 20.000 στο κελί C1. Στο κελί C16, η τιμή του κελιού εισαγωγής στήλης 1 τοποθετείται σε ένα κενό κελί και ο τυχαίος αριθμός στο κελί C2 επαναλαμβάνεται. Το αντίστοιχο κέρδος καταγράφεται στη συνέχεια στο κελί C16. Στη συνέχεια, η τιμή εισαγωγής του κελιού στήλης 2 τοποθετείται σε ένα κενό κελί και ο τυχαίος αριθμός στο κελί C2 επανυπολογίζεται. Το αντίστοιχο κέρδος καταχωρείται στο κελί C17.

Αντιγράφοντας από το κελί B13 στο C13:E13 τον τύπο AVERAGE(B16:B1015), υπολογίζουμε τον μέσο όρο προσομοιωμένων κερδών για κάθε ποσότητα παραγωγής. Αντιγράφοντας από το κελί B14 στο C14:E14 τον τύπο STDEV(B16:B1015), υπολογίζουμε την τυπική απόκλιση των προσομοιωμένων κερδών μας για κάθε ποσότητα παραγγελίας. Κάθε φορά που πατάτε F9, 1000 διαδοχικές επαναλήψεις της ζήτησης προσομοιώνονται για κάθε ποσότητα παραγγελίας. Η παραγωγή 40.000 καρτών αποδίδει πάντα το μεγαλύτερο αναμενόμενο κέρδος. Ως εκ τούτου, φαίνεται ότι η παραγωγή 40.000 καρτών είναι η σωστή απόφαση.

Ο αντίκτυπος του κινδύνου στην απόφασή μας      Εάν παράγουμε 20.000 αντί για 40.000 κάρτες, το αναμενόμενο κέρδος μας μειώνεται περίπου κατά 22 τοις εκατό, αλλά ο κίνδυνος (όπως μετράται από την τυπική απόκλιση του κέρδους) μειώνεται σχεδόν κατά 73 τοις εκατό. Επομένως, αν είμαστε εξαιρετικά αντίθετοι στον κίνδυνο, η παραγωγή 20.000 καρτών μπορεί να είναι η σωστή απόφαση. Παρεμπιπτόντως, η παραγωγή 10.000 καρτών έχει πάντα μια τυπική απόκλιση 0 καρτών, επειδή αν παράγουμε 10.000 κάρτες, θα τα πουλάμε πάντα όλα χωρίς περισσεύματα.

Διάστημα εμπιστοσύνης για το μέσο κέρδος      Ένα φυσικό ερώτημα που πρέπει να θέσουμε σε αυτή την κατάσταση είναι, σε ποιο χρονικό διάστημα είμαστε 95 τοις εκατό βέβαιοι ότι το πραγματικό μέσο κέρδος θα μειωθεί; Αυτό το διάστημα ονομάζεται διάστημα εμπιστοσύνης 95 τοις εκατό για το μέσο κέρδος. Ένα διάστημα εμπιστοσύνης 95 τοις εκατό για τον μέσο οποιασδήποτε εξόδου προσομοίωσης υπολογίζεται από τον ακόλουθο τύπο:

Στο κελί J11, υπολογίζετε το χαμηλότερο όριο για το διάστημα εμπιστοσύνης 95 τοις εκατό για το μέσο κέρδος όταν παράγονται 40.000 ημερολόγια με τον τύπο D13–1,96*D14/SQRT(1000). Στο κελί J12, υπολογίζετε το ανώτερο όριο για το διάστημα εμπιστοσύνης 95 τοις εκατό με τον τύπο D13+1,96*D14/SQRT(1000). Οι υπολογισμοί αυτοί εμφανίζονται στην Εικόνα 60-7.

Είμαστε 95 τοις εκατό βέβαιοι ότι το μέσο κέρδος μας όταν παραγγέλνονται 40.000 ημερολόγια είναι μεταξύ $ 56.687 και $ 62.589.

1. Ένας έμπορος της GMC πιστεύει ότι η ζήτηση για απεσταλμένους του 2005 θα διανεμηθεί κανονικά με μέσο όρο 200 και τυπική απόκλιση 30. Το κόστος για τη λήψη απεσταλμένου είναι 25.000 δολάρια, και πουλάει έναν Απεσταλμένο για 40.000 δολάρια. Οι μισοί απεσταλμένοι που δεν πωλούνται σε πλήρη τιμή μπορούν να πωληθούν για 30.000 δολάρια. Σκέφτεται να διατάξει 200, 220, 240, 260, 280 ή 300 απεσταλμένους. Πόσα πρέπει να παραγγείλει;

2. Ένα μικρό σούπερ μάρκετ προσπαθεί να καθορίσει πόσα αντίγραφα του περιοδικού Άτομα πρέπει να παραγγέλνουν κάθε εβδομάδα. Πιστεύουν ότι η απαίτησή τους για Άτομα διέπεται από την ακόλουθη διακριτή τυχαία μεταβλητή:

   Ζήτηση	Πιθανότητα
   15	0,10
   20	0.20
   25	0.30
   30	0,25
   35	0,15

3. Το σούπερ μάρκετ πληρώνει $ 1.00 για κάθε αντίγραφο του Άτομα και το πωλεί για $ 1.95. Κάθε απούλητο αντίγραφο μπορεί να επιστραφεί για 0,50 $. Πόσα αντίγραφα του Άτομα θα πρέπει να αποθηκεύουν την παραγγελία;