Χρήση της Επίλυσης για τον προϋπολογισμό κεφαλαίου

Οι περισσότερες εταιρείες θέλουν να αναλάβουν έργα που συνεισφέρουν τη μεγαλύτερη καθαρή παρούσα αξία (NPV), με την επιφύλαξη περιορισμένων πόρων (συνήθως κεφαλαίου και εργασίας). Ας υποθέσουμε ότι μια εταιρεία ανάπτυξης λογισμικού προσπαθεί να προσδιορίσει ποιο από τα 20 έργα λογισμικού θα πρέπει να αναλάβει. Το NPV (σε εκατομμύρια δολάρια) που συνεισέφερε κάθε έργο, καθώς και το κεφάλαιο (σε εκατομμύρια δολάρια) και ο αριθμός των προγραμματιστών που απαιτούνται για κάθε ένα από τα επόμενα τρία έτη δίνεται στο φύλλο εργασίας Βασικό μοντέλο στο αρχείο Capbudget.xlsx, το οποίο εμφανίζεται στο Σχήμα 30-1 στην επόμενη σελίδα. Για παράδειγμα, το Project 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, δεν κάνουμε το έργο. Μπορείτε να ρυθμίσετε την Επίλυση ώστε να χρησιμοποιεί μια περιοχή κελιών δυαδικής αλλαγής προσθέτοντας έναν περιορισμό— επιλέξτε τα κελιά αλλαγής που θέλετε να χρησιμοποιήσετε και, στη συνέχεια, επιλέξτε Θέση αποθήκευσης από τη λίστα στο παράθυρο διαλόγου Προσθήκη περιορισμού.

Με αυτό το παρασκήνιο, είμαστε έτοιμοι να επιλύσουμε το πρόβλημα επιλογής έργου λογισμικού. Όπως πάντα με ένα μοντέλο της Επίλυσης, ξεκινάμε προσδιορίζοντας το κελί προορισμού μας, τα μεταβαλλόμενα κελιά και τους περιορισμούς.

• Κελί προορισμού. Μεγιστοποιούμε το NPV που δημιουργείται από επιλεγμένα έργα.

• Αλλαγή κελιών. Αναζητούμε ένα δυαδικό κελί αλλαγής 0 ή 1 για κάθε έργο. Έχω εντοπίσει αυτά τα κελιά στην περιοχή A6:A25 (και ονόμασα την περιοχή doit). Για παράδειγμα, το 1 στο κελί A6 υποδεικνύει ότι αναλαμβάνουμε το Έργο 1. το 0 στο κελί C6 υποδεικνύει ότι δεν αναλαμβάνουμε το Έργο 1.

• Περιορισμούς. Πρέπει να διασφαλίσουμε ότι για κάθε έτος t (t=1, 2, 3), το κεφάλαιο του έτους 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 εξασφαλίζει ότι κατά τη διάρκεια κάθε έτους το κεφάλαιο και η εργασία που χρησιμοποιείται είναι λιγότερο από ή ίσα με το κεφάλαιο και την εργασία που είναι διαθέσιμα. Για να προσθέσω τον περιορισμό που κάνει τα μεταβαλλόμενα κελιά δυαδικά, κάντε κλικ στην επιλογή Προσθήκη στο παράθυρο διαλόγου Παράμετροι Επίλυσης και, στη συνέχεια, επιλέξτε Θέση αποθήκευσης από τη λίστα στη μέση του παραθύρου διαλόγου. Το παράθυρο διαλόγου Προσθήκη περιορισμού θα πρέπει να εμφανίζεται όπως φαίνεται στην Εικόνα 30-3.

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

Με συμπληρωμένο το παράθυρο διαλόγου Παράμετροι Επίλυσης, κάντε κλικ στην επιλογή Επίλυση και έχουμε τα αποτελέσματα που εμφανίζονται νωρίτερα στην Εικόνα 30-1. Η εταιρεία μπορεί να αποκτήσει μέγιστη NPV 9.293 εκατομμυρίων δολαρίων (9,293 δισεκατομμύρια δολάρια) επιλέγοντας Έργα 2, 3, 6–10, 14–16, 19 και 20.

Ορισμένες φορές τα μοντέλα επιλογής έργου έχουν άλλους περιορισμούς. Για παράδειγμα, ας υποθέσουμε ότι εάν επιλέξουμε το Project 3, πρέπει επίσης να επιλέξουμε το Project 4. Επειδή η τρέχουσα βέλτιστη λύση μας επιλέγει το Project 3 αλλά όχι το Project 4, γνωρίζουμε ότι η τρέχουσα λύση μας δεν μπορεί να παραμείνει η βέλτιστη. Για να επιλύσετε αυτό το πρόβλημα, απλώς προσθέστε τον περιορισμό ότι το κελί δυαδικής αλλαγής για το Project 3 είναι μικρότερο ή ίσο με το δυαδικό κελί αλλαγής για το Project 4.

Μπορείτε να βρείτε αυτό το παράδειγμα στο φύλλο εργασίας Εάν 3, στη συνέχεια, 4 στο Capbudget.xlsx αρχείου, το οποίο εμφανίζεται στην Εικόνα 30-4. Το κελί L9 αναφέρεται στη δυαδική τιμή που σχετίζεται με το Project 3 και στο κελί L12 στη δυαδική τιμή που σχετίζεται με το Project 4. Προσθέτοντας τον περιορισμό L9<=L12, εάν επιλέξουμε το Project 3, το L9 ισούται με 1 και ο περιορισμός μας επιβάλλει στο L12 (δυαδικό έργο 4) να ισούται με 1. Ο περιορισμός πρέπει επίσης να αφήσει απεριόριστη τη δυαδική τιμή στο μεταβαλλόμενο κελί του Project 4, εάν δεν επιλέξουμε το Project 3. Εάν δεν επιλέξουμε το Project 3, το L9 ισούται με 0 και ο περιορισμός μας επιτρέπει στο Project 4 δυαδικό να ισούται με 0 ή 1, το οποίο είναι αυτό που θέλουμε. Η νέα βέλτιστη λύση εμφανίζεται στην Εικόνα 30-4.

Μια νέα βέλτιστη λύση υπολογίζεται εάν επιλέξετε το Project 3 σημαίνει ότι πρέπει επίσης να επιλέξετε το Project 4. Ας υποθέσουμε τώρα ότι μπορούμε να κάνουμε μόνο τέσσερα έργα μεταξύ των έργων 1 έως 10. (Ανατρέξτε στο φύλλο εργασίας Το πολύ 4 του φύλλου εργασίας 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. Μια εταιρεία έχει εννέα έργα υπό εξέταση. Η NPV που προστίθεται από κάθε έργο και το κεφάλαιο που απαιτείται από κάθε έργο κατά τη διάρκεια των επόμενων δύο ετών εμφανίζεται στον παρακάτω πίνακα. (Όλοι οι αριθμοί είναι σε εκατομμύρια).) Για παράδειγμα, το Project 1 θα προσθέσει 14 εκατομμύρια δολάρια σε NPV και θα απαιτήσει δαπάνες 12 εκατομμυρίων δολαρίων κατά το Έτος 1 και 3 εκατομμυρίων δολαρίων κατά τη διάρκεια του Έτους 2. Κατά τη διάρκεια του 1ου έτους, διατίθενται 50 εκατομμύρια δολάρια σε κεφάλαιο για έργα και 20 εκατομμύρια δολάρια είναι διαθέσιμα κατά τη διάρκεια του 2ου έτους.

	NPV	Δαπάνες για το έτος 1	Δαπάνες για το έτος 2
Project 1	14	12	3
Project 2	17	54	7
Project 3	17	6	6
Project 4	15	6	2
Project 5	40	30	35
Project 6	12	6	6
Project 7	14	48	4
Project 8	10	36	3
Project 9	12	18	3

• Εάν δεν μπορούμε να αναλάβουμε ένα κλάσμα ενός έργου, αλλά πρέπει να αναλάβουμε είτε όλα είτε κανένα από ένα έργο, πώς μπορούμε να μεγιστοποιήσουμε το NPV;

• Ας υποθέσουμε ότι εάν πραγματοποιηθεί το Έργο 4, πρέπει να πραγματοποιηθεί το Έργο 5. Πώς μπορούμε να μεγιστοποιήσουμε το NPV;

• Μια εκδοτική εταιρεία προσπαθεί να καθορίσει ποιο από τα 36 βιβλία θα δημοσιεύσει αυτό το έτος. Ο Pressdata.xlsx αρχείων παρέχει τις ακόλουθες πληροφορίες σχετικά με κάθε βιβλίο:

  • Προβλεπόμενα έσοδα και έξοδα ανάπτυξης (σε χιλιάδες δολάρια)

  • Σελίδες σε κάθε βιβλίο

  • Εάν το βιβλίο απευθύνεται σε ένα ακροατήριο προγραμματιστών λογισμικού (υποδεικνύεται από το 1 στη στήλη E)

    Μια εκδοτική εταιρεία μπορεί να δημοσιεύσει βιβλία συνολικού ύψους έως 8500 σελίδων φέτος και πρέπει να δημοσιεύσει τουλάχιστον τέσσερα βιβλία που απευθύνονται σε προγραμματιστές λογισμικού. Πώς μπορεί η εταιρεία να μεγιστοποιήσει το κέρδος της;

Αυτό το άρθρο προσαρμόστηκε από την Ανάλυση δεδομένων και τη Μοντελοποίηση επιχειρήσεων του Microsoft Office Excel 2007 από τον Wayne L. Winston.

Αυτό το βιβλίο σε στυλ τάξης αναπτύχθηκε από μια σειρά παρουσιάσεων από τον Wayne Winston, έναν γνωστό στατιστικολόγο και καθηγητή επιχειρήσεων που ειδικεύεται στις δημιουργικές, πρακτικές εφαρμογές του Excel.