Πώς μπορεί μια εταιρεία να χρησιμοποιήσει την Επίλυση για να καθορίσει ποια έργα θα πρέπει να αναλάβει;
Κάθε χρόνο, μια εταιρεία όπως η Eli Lilly πρέπει να καθορίσει ποια φάρμακα θα αναπτυχθούν. μια εταιρεία όπως η Microsoft, ποια προγράμματα λογισμικού να αναπτύξει. μια εταιρεία όπως η Proctor & Gamble, ποια νέα καταναλωτικά προϊόντα να αναπτύξει. Η δυνατότητα "Επίλυση" στο Excel μπορεί να βοηθήσει μια εταιρεία στη λήψη αυτών των αποφάσεων.
Πώς μπορεί μια εταιρεία να χρησιμοποιήσει την Επίλυση για να καθορίσει ποια έργα θα πρέπει να αναλάβει;
Οι περισσότερες εταιρείες θέλουν να αναλάβουν έργα που συνεισφέρουν τη μεγαλύτερη καθαρή παρούσα αξία (NPV), με την επιφύλαξη περιορισμένων πόρων (συνήθως κεφαλαίου και εργασίας). Ας υποθέσουμε ότι μια εταιρεία ανάπτυξης λογισμικού προσπαθεί να καθορίσει ποιο από τα 20 έργα λογισμικού πρέπει να αναλάβει. Το NPV (σε εκατομμύρια δολάρια) που συνεισφέρει κάθε έργο, καθώς και το κεφάλαιο (σε εκατομμύρια δολάρια) και ο αριθμός των προγραμματιστών που απαιτούνται κατά τη διάρκεια καθενός από τα επόμενα τρία χρόνια δίνονται στο φύλλο εργασίας του Βασικού Μοντέλου στο αρχείο Capbudget.xlsx, το οποίο φαίνεται στο Σχήμα 30-1 στην επόμενη σελίδα. Για παράδειγμα, το Έργο 2 αποδίδει 908 εκατομμύρια δολάρια. Απαιτεί 151 εκατομμύρια δολάρια κατά τη διάρκεια του έτους 1, 269 εκατομμύρια δολάρια κατά το έτος 2 και 248 εκατομμύρια δολάρια κατά το έτος 3. Το Project 2 απαιτεί 139 προγραμματιστές κατά το έτος 1, 86 προγραμματιστές κατά το έτος 2 και 83 προγραμματιστές κατά το έτος 3. Τα κελιά E4:G4 εμφανίζουν το κεφάλαιο (σε εκατομμύρια δολάρια) που είναι διαθέσιμο σε κάθε ένα από τα τρία έτη και τα κελιά H4:J4 υποδεικνύουν τον αριθμό των προγραμματιστών που είναι διαθέσιμοι. Για παράδειγμα, κατά τη διάρκεια του έτους 1 έως και 2,5 δισεκατομμύρια δολάρια σε κεφάλαιο και 900 προγραμματιστές είναι διαθέσιμοι.
Η εταιρεία πρέπει να αποφασίσει εάν πρέπει να αναλάβει κάθε έργο. Ας υποθέσουμε ότι δεν μπορούμε να αναλάβουμε ένα κλάσμα ενός έργου λογισμικού. Εάν διαθέσουμε 0,5 από τους απαραίτητους πόρους, για παράδειγμα, θα έχουμε ένα μη λειτουργικό πρόγραμμα που θα μας αποφέρει έσοδα 0 $!
Το κόλπο σε καταστάσεις μοντελοποίησης στις οποίες είτε κάνετε είτε δεν κάνετε κάτι είναι να χρησιμοποιήσετε δυαδικά μεταβαλλόμενα κελιά. Ένα δυαδικό κελί που αλλάζει έχει πάντα το αποτέλεσμα 0 ή 1. Όταν ένα δυαδικό κελί αλλαγής που αντιστοιχεί σε ένα έργο ισούται με 1, εμείς εκτελούμε το έργο. Εάν ένα δυαδικό κελί που αλλάζει και αντιστοιχεί σε ένα έργο ισούται με 0, δεν εκτελούμε το έργο. Μπορείτε να ρυθμίσετε την επίλυση ώστε να χρησιμοποιεί μια περιοχή δυαδικών μεταβαλλόμενων κελιών προσθέτοντας έναν περιορισμό—επιλέξτε τα μεταβαλλόμενα κελιά που θέλετε να χρησιμοποιήσετε και, στη συνέχεια, επιλέξτε Bin από τη λίστα στο παράθυρο διαλόγου Προσθήκη περιορισμού.
Με αυτό το υπόβαθρο, είμαστε έτοιμοι να λύσουμε το πρόβλημα επιλογής έργων λογισμικού. Όπως πάντα με ένα μοντέλο Επίλυσης, ξεκινάμε προσδιορίζοντας το κελί προορισμού, τα μεταβαλλόμενα κελιά και τους περιορισμούς.
- Κελί προορισμού. Μεγιστοποιούμε την NPV που δημιουργείται από επιλεγμένα έργα.
- Μεταβαλλόμενα κελιά. Αναζητούμε ένα δυαδικό κελί αλλαγής 0 ή 1 για κάθε έργο. Έχω εντοπίσει αυτά τα κελιά στην περιοχή A6: A25 (και ονόμασα το εύρος doit). Για παράδειγμα, το 1 στο κελί A6 υποδεικνύει ότι αναλαμβάνουμε το Έργο 1. Το 0 στο κελί C6 υποδεικνύει ότι δεν αναλαμβάνουμε το Έργο 1.
- Περιορισμοί. Πρέπει να διασφαλίσουμε ότι για κάθε έτος t (t = 1, 2, 3), το έτος t κεφαλαίου που χρησιμοποιείται είναι μικρότερο ή ίσο με το διαθέσιμο κεφάλαιο t έτους και το έτος t εργασίας που χρησιμοποιείται είναι μικρότερο ή ίσο με το διαθέσιμο εργατικό δυναμικό t έτους.
Όπως μπορείτε να δείτε, το φύλλο εργασίας μας πρέπει να υπολογίζει για οποιαδήποτε επιλογή έργων το NPV, το κεφάλαιο που χρησιμοποιείται ετησίως και τους προγραμματιστές που χρησιμοποιούνται κάθε χρόνο. Στο κελί B2, χρησιμοποιώ τον τύπο SUMPRODUCT(doit,NPV) για τον υπολογισμό του συνόλου NPV που δημιουργείται από επιλεγμένα έργα. (Το όνομα της περιοχής NPV αναφέρεται στην περιοχή C6:C25.) Για κάθε έργο με 1 στη στήλη A, αυτός ο τύπος παίρνει την NPV του έργου, ενώ για κάθε έργο με 0 στη στήλη A, αυτός ο τύπος δεν λαμβάνει την NPV του έργου. Επομένως, μπορούμε να υπολογίσουμε το NPV όλων των έργων και το κελί στόχος μας είναι γραμμικό, επειδή υπολογίζεται αθροίζοντας όρους που ακολουθούν τη μορφή (μεταβαλλόμενο κελί)*(σταθερά). Με παρόμοιο τρόπο, υπολογίζω το κεφάλαιο που χρησιμοποιείται κάθε χρόνο και την εργασία που χρησιμοποιείται κάθε χρόνο αντιγράφοντας από το E2 στο F2: J2 τον τύπο SUMPRODUCT(doit, E6: E25).
Τώρα συμπληρώνω το παράθυρο διαλόγου Παράμετροι Επίλυσης όπως φαίνεται στην Εικόνα 30-2.
Στόχος μας είναι η μεγιστοποίηση του NPV των επιλεγμένων έργων (κελί B2). Τα μεταβαλλόμενα κελιά μας (η περιοχή που ονομάζεται doit) είναι τα δυαδικά μεταβαλλόμενα κελιά για κάθε έργο. Ο περιορισμός E2:J2<=E4:J4 εξασφαλίζει ότι κατά τη διάρκεια κάθε έτους το κεφάλαιο και η εργασία που χρησιμοποιούνται είναι μικρότερα ή ίσα με το διαθέσιμο κεφάλαιο και εργασία. Για να προσθέσετε τον περιορισμό που κάνει τα μεταβαλλόμενα κελιά δυαδικά, κάνω κλικ στο κουμπί Προσθήκη στο παράθυρο διαλόγου Παράμετροι επίλυσης και, στη συνέχεια, επιλέγω Bin από τη λίστα στο μέσο του παραθύρου διαλόγου. Το παράθυρο διαλόγου Προσθήκη περιορισμού θα πρέπει να εμφανίζεται όπως φαίνεται στην εικόνα 30-3.
Το μοντέλο μας είναι γραμμικό επειδή το κελί προορισμού υπολογίζεται ως το άθροισμα των όρων που έχουν τη μορφή (μεταβαλλόμενο κελί)*(σταθερά) και επειδή οι περιορισμοί χρήσης πόρων υπολογίζονται συγκρίνοντας το άθροισμα των (μεταβαλλόμενα κελιά)*(σταθερές) με μια σταθερά.
Με συμπληρωμένο το παράθυρο διαλόγου Παράμετροι Επίλυσης, κάντε κλικ στην επιλογή Επίλυση και έχουμε τα αποτελέσματα που εμφανίζονται νωρίτερα στο Σχήμα 30-1. Η εταιρεία μπορεί να αποκτήσει μέγιστο NPV 9.293 εκατομμυρίων δολαρίων (9,293 δισεκατομμύρια δολάρια) επιλέγοντας τα έργα 2, 3, 6-10, 14-16, 19 και 20.
Χειρισμός άλλων περιορισμών
Ορισμένες φορές τα μοντέλα επιλογής έργων έχουν άλλους περιορισμούς. Για παράδειγμα, ας υποθέσουμε ότι αν επιλέξουμε το Έργο 3, πρέπει επίσης να επιλέξουμε το Έργο 4. Επειδή η τρέχουσα βέλτιστη λύση επιλέγει το Έργο 3 αλλά όχι το Έργο 4, γνωρίζουμε ότι η τρέχουσα λύση δεν είναι δυνατό να παραμείνει η βέλτιστη. Για να επιλύσετε αυτό το πρόβλημα, απλώς προσθέστε τον περιορισμό ότι το δυαδικό κελί αλλαγής για το Project 3 είναι μικρότερο ή ίσο με το δυαδικό κελί αλλαγής για το Project 4.
Μπορείτε να βρείτε αυτό το παράδειγμα στο φύλλο εργασίας If 3 then 4 στο αρχείο Capbudget.xlsx, το οποίο φαίνεται στην εικόνα 30-4. Το κελί L9 αναφέρεται στη δυαδική τιμή που σχετίζεται με το Έργο 3 και το κελί L12 στη δυαδική τιμή που σχετίζεται με το Έργο 4. Προσθέτοντας τον περιορισμό L9<=L12, αν επιλέξουμε το Έργο 3, το L9 ισούται με 1 και ο περιορισμός μας αναγκάζει το L12 (το δυαδικό αρχείο του Έργου 4) να ισούται με 1. Ο περιορισμός μας πρέπει επίσης να αφήνει τη δυαδική τιμή στο μεταβαλλόμενο κελί του Έργου 4 απεριόριστη, εάν δεν επιλέξουμε το Έργο 3. Εάν δεν επιλέξουμε το Έργο 3, το L9 ισούται με 0 και ο περιορισμός μας επιτρέπει στο δυαδικό αρχείο του Έργου 4 να ισούται με 0 ή 1, πράγμα που θέλουμε. Η νέα βέλτιστη λύση φαίνεται στο σχήμα 30-4.
Μια νέα βέλτιστη λύση υπολογίζεται εάν η επιλογή του Έργου 3 σημαίνει ότι πρέπει να επιλέξουμε και το Έργο 4. Ας υποθέσουμε ότι μπορούμε να εκτελέσουμε μόνο τέσσερα έργα από τα Έργα 1 έως 10. (Δείτε το φύλλο εργασίας At Most 4 Of P1–P10, που φαίνεται στην εικόνα 30-5.) Στο κελί L8, υπολογίζουμε το άθροισμα των δυαδικών τιμών που σχετίζονται με τα έργα 1 έως 10 με τον τύπο SUM(A6:A15). Στη συνέχεια προσθέτουμε τον περιορισμό L8<=L10, ο οποίος εξασφαλίζει ότι, το πολύ, επιλέγονται 4 από τα πρώτα 10 έργα. Η νέα βέλτιστη λύση φαίνεται στο σχήμα 30-5. Το NPV μειώθηκε στα 9,014 δισεκατομμύρια δολάρια.
Επίλυση Προβλημάτων Δυαδικού και Ακέραιου Προγραμματισμού
Τα μοντέλα γραμμικής επίλυσης στα οποία ορισμένα ή όλα τα μεταβαλλόμενα κελιά απαιτείται να είναι δυαδικά ή ακέραια είναι συνήθως δυσκολότερο να επιλυθούν από τα γραμμικά μοντέλα στα οποία όλα τα μεταβαλλόμενα κελιά επιτρέπεται να είναι κλάσματα. Για το λόγο αυτό, συχνά είμαστε ικανοποιημένοι με μια σχεδόν βέλτιστη λύση σε ένα πρόβλημα δυαδικού ή ακέραιου προγραμματισμού. Εάν το μοντέλο Επίλυσης λειτουργεί για μεγάλο χρονικό διάστημα, εξετάστε το ενδεχόμενο προσαρμογής της ρύθμισης ανοχής στο παράθυρο διαλόγου Επιλογές Επίλυσης. (Βλέπε σχήμα 30-6.) Για παράδειγμα, μια ρύθμιση ανοχής 0,5% σημαίνει ότι η Επίλυση θα σταματήσει την πρώτη φορά που θα βρει μια εφικτή λύση εντός 0,5 τοις εκατό της θεωρητικά βέλτιστης τιμής του κελιού προορισμού (η θεωρητικά βέλτιστη τιμή του κελιού προορισμού είναι η βέλτιστη τιμή-στόχος που βρίσκεται όταν παραλείπονται οι δυαδικοί και ακέραιοι περιορισμοί). Συχνά βρισκόμαστε αντιμέτωποι με μια επιλογή μεταξύ της εύρεσης μιας απάντησης εντός του 10 τοις εκατό του βέλτιστου σε 10 λεπτά ή της εύρεσης μιας βέλτιστης λύσης σε δύο εβδομάδες χρόνου υπολογιστή! Η προεπιλεγμένη τιμή ανοχής είναι 0,05%, που σημαίνει ότι η Επίλυση σταματά όταν βρει μια τιμή κελιού προορισμού εντός 0,05 τοις εκατό της θεωρητικά βέλτιστης τιμής κελιού προορισμού.
Προβλήματα
- Μια εταιρεία έχει εννέα έργα υπό εξέταση. Η ΚΠΑ που προστίθεται από κάθε έργο και το κεφάλαιο που απαιτείται από κάθε έργο κατά τη διάρκεια των επόμενων δύο ετών παρουσιάζεται στον ακόλουθο πίνακα. (Όλοι οι αριθμοί είναι σε εκατομμύρια.) Για παράδειγμα, το Έργο 1 θα προσθέσει 14 εκατομμύρια δολάρια σε NPV και θα απαιτήσει δαπάνες 12 εκατομμυρίων δολαρίων κατά το Έτος 1 και 3 εκατομμυρίων δολαρίων κατά το Έτος 2. Κατά τη διάρκεια του έτους 1, 50 εκατομμύρια δολάρια σε κεφάλαιο είναι διαθέσιμα για έργα και 20 εκατομμύρια δολάρια είναι διαθέσιμα κατά τη διάρκεια του έτους 2.
| NPV | Δαπάνες του έτους 1 | Δαπάνες του έτους 2 | |
|---|---|---|---|
| Έργο 1 | 14 | 12 | 3 |
| Έργο 2 | 17 | 54 | 7 |
| Έργο 3 | 17 | 6 | 6 |
| Έργο 4 | 15 | 6 | 2 |
| Έργο 5 | 40 | 30 | 35 |
| Έργο 6 | 12 | 6 | 6 |
| Έργο 7 | 14 | 48 | 4 |
| Έργο 8 | 10 | 36 | 3 |
| Έργο 9 | 12 | 18 | 3 |
- Εάν δεν μπορούμε να αναλάβουμε ένα κλάσμα ενός έργου, αλλά πρέπει να αναλάβουμε είτε το σύνολο είτε κανένα έργο, πώς μπορούμε να μεγιστοποιήσουμε το NPV;
- Ας υποθέσουμε ότι αν αναληφθεί το Έργο 4, πρέπει να αναληφθεί το Έργο 5. Πώς μπορούμε να μεγιστοποιήσουμε το NPV;
Μια εκδοτική εταιρεία προσπαθεί να καθορίσει ποιο από τα 36 βιβλία θα πρέπει να εκδώσει φέτος. Το αρχείο Pressdata.xlsx παρέχει τις ακόλουθες πληροφορίες για κάθε βιβλίο:
- Προβλεπόμενο κόστος εσόδων και ανάπτυξης (σε χιλιάδες δολάρια)
- Σελίδες σε κάθε βιβλίο
- Εάν το βιβλίο απευθύνεται σε ένα κοινό προγραμματιστών λογισμικού (υποδεικνύεται από ένα 1 στη στήλη E)
Μια εκδοτική εταιρεία μπορεί να εκδώσει βιβλία συνολικού ύψους έως 8500 σελίδων φέτος και πρέπει να εκδώσει τουλάχιστον τέσσερα βιβλία που απευθύνονται σε προγραμματιστές λογισμικού. Πώς μπορεί η εταιρεία να μεγιστοποιήσει το κέρδος της;
Σχετικά με το άρθρο
Αυτό το άρθρο έχει προσαρμοστεί από το Microsoft Office Excel 2007 Data Analysis and Business Modeling του Wayne L. Winston.
Αυτό το βιβλίο σε στυλ τάξης αναπτύχθηκε από μια σειρά παρουσιάσεων από τον Wayne Winston, γνωστό στατιστικολόγο και καθηγητή επιχειρήσεων που ειδικεύεται σε δημιουργικές, πρακτικές εφαρμογές του Excel.