Χρήση της Επίλυσης για τον προσδιορισμό του βέλτιστου μείγματος προϊόντων

Πώς μπορώ να προσδιορίσω το μηνιαίο μείγμα προϊόντων που μεγιστοποιεί την κερδοφορία;

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

• Το μείγμα προϊόντων δεν μπορεί να χρησιμοποιήσει περισσότερους πόρους από όσους είναι διαθέσιμοι.

• Υπάρχει περιορισμένη ζήτηση για κάθε προϊόν. Δεν μπορούμε να παράγουμε περισσότερο προϊόν κατά τη διάρκεια ενός μήνα από ό, τι υπαγορεύει η ζήτηση, επειδή η πλεονάζουσα παραγωγή σπαταλάται (για παράδειγμα, ένα ευπαθές φάρμακο).

Ας λύσουμε τώρα το παρακάτω παράδειγμα του προβλήματος με το μείγμα προϊόντος. Μπορείτε να βρείτε τη λύση σε αυτό το πρόβλημα στο Prodmix.xlsx αρχείου, που εμφανίζεται στην Εικόνα 27-1.

Ας υποθέσουμε ότι εργαζόμαστε για μια φαρμακευτική εταιρεία που παράγει έξι διαφορετικά προϊόντα στο εργοστάσιό τους. Η παραγωγή κάθε προϊόντος απαιτεί εργασία και πρώτη ύλη. Η γραμμή 4 στο σχήμα 27-1 δείχνει τις ώρες εργασίας που απαιτούνται για την παραγωγή ενός κιλό κάθε προϊόντος και η γραμμή 5 δείχνει τις λίβρες της πρώτης ύλης που απαιτούνται για την παραγωγή ενός κιλό κάθε προϊόντος. Για παράδειγμα, η παραγωγή μιας λίβρας Προϊόντος 1 απαιτεί έξι ώρες εργασίας και 3,2 λίβρες πρώτης ύλης. Για κάθε φάρμακο, η τιμή ανά λίβρα δίνεται στη γραμμή 6, το κόστος μονάδας ανά λίβρα δίνεται στη γραμμή 7 και η συνεισφορά κέρδους ανά λίβρα δίνεται στη γραμμή 9. Για παράδειγμα, το Προϊόν 2 πωλεί για 11,00 $ ανά λίβρα, επιφέρει κόστος μονάδας 5,70 $ ανά λίβρα και συνεισφέρει κέρδος 5,30 $ ανά λίβρα. Η ζήτηση του μήνα για κάθε φάρμακο δίνεται στη γραμμή 8. Για παράδειγμα, η ζήτηση για το Προϊόν 3 είναι 1041 λίρες. Αυτό το μήνα, είναι διαθέσιμες 4500 ώρες εργασίας και 1600 λίβρες πρώτης ύλης. Πώς μπορεί αυτή η εταιρεία να μεγιστοποιήσει το μηνιαίο κέρδος της;

Εάν δεν γνωρίζαμε τίποτα για την Επίλυση του Excel, θα επιτευχθούμε σε αυτό το πρόβλημα δημιουργώντας ένα φύλλο εργασίας για την παρακολούθηση των κερδών και της χρήσης πόρων που σχετίζονται με το μείγμα προϊόντων. Στη συνέχεια, θα χρησιμοποιούσαμε δοκιμή και σφάλμα για να διαφοροποιήσουμε το μείγμα προϊόντων για να βελτιστοποιήσουμε το κέρδος χωρίς να χρησιμοποιήσουμε περισσότερη εργασία ή πρώτη ύλη από ό, τι είναι διαθέσιμο και χωρίς να παράγουμε κανένα φάρμακο που υπερβαίνει τη ζήτηση. Χρησιμοποιούμε την Επίλυση σε αυτήν τη διαδικασία μόνο στο στάδιο της δοκιμαστικής έκδοσης και του σφάλματος. Ουσιαστικά, η Επίλυση είναι ένας μηχανισμός βελτιστοποίησης που εκτελεί άψογα την αναζήτηση δοκιμαστικής έκδοσης και σφάλματος.

Ένας αριθμός-κλειδί για την επίλυση του προβλήματος μεικτής προϊόντος είναι ο αποτελεσματικός υπολογισμός της χρήσης πόρων και του κέρδους που σχετίζονται με οποιοδήποτε δεδομένο μείγμα προϊόντων. Ένα σημαντικό εργαλείο που μπορούμε να χρησιμοποιήσουμε για να κάνουμε αυτόν τον υπολογισμό είναι η συνάρτηση SUMPRODUCT. Η συνάρτηση SUMPRODUCT πολλαπλασιάζει τις αντίστοιχες τιμές σε περιοχές κελιών και επιστρέφει το άθροισμα αυτών των τιμών. Κάθε περιοχή κελιών που χρησιμοποιείται σε έναν SUMPRODUCT υπολογισμό πρέπει να έχει τις ίδιες διαστάσεις, γεγονός που σημαίνει ότι μπορείτε να χρησιμοποιήσετε SUMPRODUCT με δύο γραμμές ή δύο στήλες, αλλά όχι με μία στήλη και μία γραμμή.

Ως παράδειγμα του τρόπου με τον οποίο μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT στο παράδειγμα μείγματος προϊόντων, ας προσπαθήσουμε να υπολογίσουμε τη χρήση των πόρων μας. Η χρήση της εργασίας μας υπολογίζεται από

(Εργασία που χρησιμοποιείται ανά λίβρα του φαρμάκου 1)*(Φάρμακο 1 λίβρες που παράγονται)+
(Εργασία που χρησιμοποιείται ανά λίβρα του φαρμάκου 2)*(Φάρμακο 2 λίβρες που παράγονται) + ...
(Εργασία που χρησιμοποιείται ανά λίβρα του φαρμάκου 6)*(Φάρμακο 6 λίβρες που παράγονται)

Θα μπορούσαμε να υπολογίσουμε τη χρήση εργασίας με πιο κουρασμένο τρόπο ως D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I2*I4. Ομοίως, η χρήση των πρώτων υλών θα μπορούσε να υπολογιστεί ως D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Ωστόσο, η εισαγωγή αυτών των τύπων σε ένα φύλλο εργασίας για έξι προϊόντα είναι χρονοβόρα. Φανταστείτε πόσος χρόνος θα έπαιρνε αν συνεργάζεστε με μια εταιρεία που παρήγαγε, για παράδειγμα, 50 προϊόντα στο εργοστάσιό τους. Ένας πολύ πιο εύκολος τρόπος για να υπολογίσετε τη χρήση εργασίας και πρώτης ύλης είναι να αντιγράψετε από το D14 στο D15 τον τύπο SUMPRODUCT($D $2:$I$2,D4:I4). Αυτός ο τύπος υπολογίζει το D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (που είναι η χρήση εργασίας μας), αλλά είναι πολύ πιο εύκολο να εισαχθεί! Παρατηρήστε ότι χρησιμοποιώ το σύμβολο $ με την περιοχή D2:I2, έτσι ώστε όταν αντιγράφω τον τύπο να αποτυπώνω το μείγμα προϊόντων από τη γραμμή 2. Ο τύπος στο κελί D15 υπολογίζει τη χρήση των πρώτων υλών.

Με παρόμοιο τρόπο, το κέρδος μας καθορίζεται από

(Φάρμακο 1 κέρδος ανά λίβρα)*(Φάρμακο 1 λίβρες που παράγονται) +
(Φάρμακο 2 κέρδος ανά λίβρα)*(Φάρμακο 2 λίβρες που παράγονται) + ...
(Φάρμακο 6 κέρδος ανά λίβρα)*(Φάρμακο 6 λίβρες που παράγονται)

Το κέρδος υπολογίζεται εύκολα στο κελί D12 με τον τύπο SUMPRODUCT(D9:I9$D$2:$I$2).

Τώρα μπορούμε να προσδιορίσουμε τα τρία στοιχεία του μοντέλου Επίλυσης μεικτών προϊόντων.

• Κελί προορισμού. Στόχος μας είναι η μεγιστοποίηση του κέρδους (υπολογίζεται στο κελί D12).

• Αλλαγή κελιών. Ο αριθμός των κιλών που παράγονται από κάθε προϊόν (παρατίθεται στην περιοχή κελιών D2:I2)

• Περιορισμούς. Έχουμε τους ακόλουθους περιορισμούς:

  • Μην χρησιμοποιείτε περισσότερη εργασία ή πρώτη ύλη από ό, τι είναι διαθέσιμο. Δηλαδή, οι τιμές στα κελιά D14:D15 (οι πόροι που χρησιμοποιούνται) πρέπει να είναι μικρότερες ή ίσες με τις τιμές στα κελιά F14:F15 (οι διαθέσιμοι πόροι).

  • Μην παράγετε περισσότερο από ένα φάρμακο από ό, τι είναι σε ζήτηση. Δηλαδή, οι τιμές στα κύτταρα D2:I2 (λίβρες που παράγονται από κάθε φάρμακο) πρέπει να είναι μικρότερες ή ίσες με τη ζήτηση για κάθε φάρμακο (παρατίθεται στα κύτταρα D8:I8).

  • Δεν μπορούμε να παράγουμε αρνητική ποσότητα ναρκωτικού.

Θα σας δείξω πώς μπορείτε να εισαγάγετε το κελί προορισμού, να αλλάξετε κελιά και περιορισμούς στην Επίλυση. Στη συνέχεια, το μόνο που χρειάζεται να κάνετε είναι να κάνετε κλικ στο κουμπί Επίλυση για να βρείτε ένα μείγμα προϊόντων μεγιστοποίηση κέρδους!

Για να ξεκινήσετε, κάντε κλικ στην καρτέλα Δεδομένα και, στην ομάδα Ανάλυση, κάντε κλικ στην επιλογή Επίλυση.

Θα εμφανιστεί το παράθυρο διαλόγου Παράμετροι Επίλυσης, όπως φαίνεται στην Εικόνα 27-2.

Κάντε κλικ στο πλαίσιο Ορισμός κελιού προορισμού και, στη συνέχεια, επιλέξτε το κελί κέρδους μας (κελί D12). Κάντε κλικ στο πλαίσιο Με την αλλαγή κελιών και, στη συνέχεια, τοποθετήστε το δείκτη στην περιοχή D2:I2, η οποία περιέχει τις λίβρες που παράγονται από κάθε φάρμακο. Το παράθυρο διαλόγου θα πρέπει τώρα να έχει την εμφάνιση της Εικόνας 27-3.

Είμαστε πλέον έτοιμοι να προσθέσουμε περιορισμούς στο μοντέλο. Κάντε κλικ στο κουμπί Προσθήκη. Θα δείτε το παράθυρο διαλόγου Προσθήκη περιορισμού, που εμφανίζεται στην Εικόνα 27-4.

Για να προσθέσετε τους περιορισμούς χρήσης πόρων, κάντε κλικ στο πλαίσιο Αναφορά κελιού και, στη συνέχεια, επιλέξτε την περιοχή D14:D15. Επιλέξτε <= από τη μεσαία λίστα. Κάντε κλικ στο πλαίσιο Περιορισμός και, στη συνέχεια, επιλέξτε την περιοχή κελιών F14:F15. Το παράθυρο διαλόγου Προσθήκη περιορισμού θα πρέπει τώρα να μοιάζει με εικόνα 27-5.

Έχουμε πλέον εξασφαλίσει ότι όταν η Επίλυση δοκιμάζει διαφορετικές τιμές για τα μεταβαλλόμενα κελιά, θα λαμβάνονται υπόψη μόνο οι συνδυασμοί που ικανοποιούν τόσο το D14<το =F14 (η εργασία που χρησιμοποιείται είναι μικρότερη ή ίση με την εργασία) όσο και η D15<=F15 (η πρώτη ύλη που χρησιμοποιείται είναι μικρότερη ή ίση με την πρώτη ύλη που διατίθεται). Κάντε κλικ στο κουμπί Προσθήκη για να εισαγάγετε τους περιορισμούς απαίτησης. Συμπληρώστε το παράθυρο διαλόγου Προσθήκη περιορισμού, όπως φαίνεται στην Εικόνα 27-6.

Η προσθήκη αυτών των περιορισμών εξασφαλίζει ότι όταν η Επίλυση δοκιμάζει διαφορετικούς συνδυασμούς για τις μεταβαλλόμενες τιμές κελιών, θα λαμβάνονται υπόψη μόνο οι συνδυασμοί που ικανοποιούν τις παρακάτω παραμέτρους:

• D2<=D8 (η ποσότητα που παράγεται από το φάρμακο 1 είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 1)

• E2<=E8 (η ποσότητα που παράγεται από το φάρμακο 2 είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 2)

• F2<=F8 (η ποσότητα που παράγεται από το φάρμακο 3 που παράγεται είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 3)

• G2<=G8 (η ποσότητα που παράγεται από το φάρμακο 4 που παράγεται είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 4)

• H2<=H8 (η ποσότητα που παράγεται από το φάρμακο 5 που παράγεται είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 5)

• I2<=I8 (η ποσότητα που παράγεται από το φάρμακο 6 που παράγεται είναι μικρότερη ή ίση με τη ζήτηση για το φάρμακο 6)

Κάντε κλικ στο κουμπί OK στο παράθυρο διαλόγου Προσθήκη περιορισμού. Το παράθυρο επίλυσης θα πρέπει να μοιάζει με εικόνα 27-7.

Καταχωρούμε τον περιορισμό ότι η αλλαγή κελιών πρέπει να είναι μη αρνητική στο παράθυρο διαλόγου Επιλογές Επίλυσης. Κάντε κλικ στο κουμπί Επιλογές στο παράθυρο διαλόγου Παράμετροι Επίλυσης. Επιλέξτε το πλαίσιο Προϋποθέτει γραμμικό μοντέλο και το πλαίσιο Ας υποθέσουμε ότι δεν είναι αρνητικό, όπως φαίνεται στην Εικόνα 27-8 στην επόμενη σελίδα. Επιλέξτε OK.

Εάν επιλέξετε το πλαίσιο Προϋποθέτει μη αρνητικό, διασφαλίζεται ότι η Επίλυση λαμβάνει υπόψη μόνο συνδυασμούς μεταβαλλόμενων κελιών στα οποία κάθε μεταβαλλόμενο κελί θεωρεί μη αρνητική τιμή. Ελέγξαμε το πλαίσιο Υπόθεση γραμμικού μοντέλου, επειδή το πρόβλημα με το μείγμα προϊόντος είναι ένας ειδικός τύπος προβλήματος επίλυσης που ονομάζεται γραμμικό μοντέλο. Ουσιαστικά, ένα μοντέλο της Επίλυσης είναι γραμμικό υπό τις ακόλουθες συνθήκες:

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

• Κάθε περιορισμός ικανοποιεί την "απαίτηση γραμμικού μοντέλου". Αυτό σημαίνει ότι κάθε περιορισμός υπολογίζεται προσθέτοντας μαζί τους όρους της φόρμας (μεταβαλλόμενο κελί)*(σταθερά) και συγκρίνοντας τα αθροίσματα με μια σταθερά.

Γιατί αυτό το πρόβλημα της Επίλυσης είναι γραμμικό; Το κελί-στόχος μας (κέρδος) υπολογίζεται ως

(Φάρμακο 1 κέρδος ανά λίβρα)*(Φάρμακο 1 λίβρες που παράγονται) +
(Φάρμακο 2 κέρδος ανά λίβρα)*(Φάρμακο 2 λίβρες που παράγονται) + ...
(Φάρμακο 6 κέρδος ανά λίβρα)*(Φάρμακο 6 λίβρες που παράγονται)

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

Ο περιορισμός της εργασίας μας αξιολογείται συγκρίνοντας την τιμή που προέρχεται από (Η εργασία χρησιμοποιείται ανά λίβρα του φαρμάκου 1)*(Φάρμακο 1 λίβρες που παράγονται) + (Εργασία που χρησιμοποιείται ανά λίβρα του φαρμάκου 2)*(Φάρμακο 2 λίβρες που παράγονται)+ ... (Εργατικοίed ανά λίβρα του φαρμάκου 6)*(Φάρμακο 6 λίβρες που παράγονται) στην εργασία διαθέσιμη.

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

Οι περιορισμοί απαίτησής μας παίρνουν τη μορφή

(Φάρμακο 1 που παράγεται)<=(Φάρμακο 1 Ζήτηση)
(Φάρμακο 2 που παράγεται)<=(Φάρμακο 2 Ζήτηση)
¢
(Φάρμακο 6 που παράγεται)<=(Απαίτηση φαρμάκων 6)

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

Έχοντας δείξει ότι το μοντέλο μείγματος προϊόντων μας είναι ένα γραμμικό μοντέλο, γιατί θα πρέπει να νοιαζόμαστε;

• Εάν ένα μοντέλο της Επίλυσης είναι γραμμικό και επιλέξουμε "Υπόθεση γραμμικού μοντέλου", η Επίλυση θα βρει εγγυημένα τη βέλτιστη λύση για το μοντέλο της Επίλυσης. Εάν ένα μοντέλο της Επίλυσης δεν είναι γραμμικό, η Επίλυση μπορεί να βρει ή να μην βρει τη βέλτιστη λύση.

• Εάν ένα μοντέλο της Επίλυσης είναι γραμμικό και επιλέξουμε "Υπόθεση γραμμικού μοντέλου", η Επίλυση χρησιμοποιεί έναν πολύ αποτελεσματικό αλγόριθμο (τη μέθοδο simplex) για να βρει τη βέλτιστη λύση του μοντέλου. Εάν ένα μοντέλο της Επίλυσης είναι γραμμικό και δεν επιλέγουμε "Προϋποθέτει γραμμικό μοντέλο", η Επίλυση χρησιμοποιεί έναν πολύ αναποτελεσματικό αλγόριθμο (τη μέθοδο GRG2) και μπορεί να δυσκολευτεί να βρει τη βέλτιστη λύση του μοντέλου.

Αφού κάνετε κλικ στο κουμπί OK στο παράθυρο διαλόγου Επιλογές Επίλυσης, επιστρέφουμε στο κύριο παράθυρο διαλόγου επίλυσης, που εμφανίζεται νωρίτερα στην Εικόνα 27-7. Όταν κάνουμε κλικ στην επιλογή Επίλυση, η Επίλυση υπολογίζει μια βέλτιστη λύση (εάν υπάρχει) για το μοντέλο μείγματος προϊόντων. Όπως δήλωσα στο κεφάλαιο 26, μια βέλτιστη λύση για το μοντέλο μείγματος προϊόντων θα ήταν ένα σύνολο μεταβαλλόμενων τιμών κυττάρων (λίβρες που παράγονται από κάθε φάρμακο) που μεγιστοποιεί το κέρδος σε σχέση με το σύνολο όλων των εφικτών λύσεων. Και πάλι, μια εφικτή λύση είναι ένα σύνολο μεταβαλλόμενων τιμών κελιών που ικανοποιούν όλους τους περιορισμούς. Οι μεταβαλλόμενες τιμές των κελιών που παρουσιάζονται στο σχήμα 27-9 είναι μια εφικτή λύση, επειδή όλα τα επίπεδα παραγωγής είναι μη αρνητικά, τα επίπεδα παραγωγής δεν υπερβαίνουν τη ζήτηση και η χρήση πόρων δεν υπερβαίνει τους διαθέσιμους πόρους.

Οι μεταβαλλόμενες τιμές κελιών που εμφανίζονται στην Εικόνα 27-10 της επόμενης σελίδας αντιπροσωπεύουν μια ανέφικτη λύση για τους ακόλουθους λόγους:

• Παράγουμε περισσότερο φάρμακο 5 από τη ζήτηση για αυτό.

• Χρησιμοποιούμε περισσότερη εργασία από ό, τι είναι διαθέσιμο.

• Χρησιμοποιούμε περισσότερη πρώτη ύλη από ό, τι είναι διαθέσιμο.

Αφού κάνετε κλικ στην επιλογή Επίλυση, η Επίλυση βρίσκει γρήγορα τη βέλτιστη λύση που εμφανίζεται στην Εικόνα 27-11. Πρέπει να επιλέξετε Διατήρηση λύσης Επίλυσης για να διατηρήσετε τις βέλτιστες τιμές λύσεων στο φύλλο εργασίας.

Η φαρμακευτική εταιρεία μας μπορεί να μεγιστοποιήσει το μηνιαίο κέρδος της σε επίπεδο $ 6,625.20 με την παραγωγή 596.67 λίβρες του φαρμάκου 4, 1084 λίβρες του φαρμάκου 5, και κανένα από τα άλλα φάρμακα! Δεν μπορούμε να προσδιορίσουμε αν μπορούμε να επιτύχουμε το μέγιστο κέρδος των 6.625,20 $ με άλλους τρόπους. Το μόνο που μπορούμε να είμαστε σίγουροι είναι ότι με τους περιορισμένους πόρους και τη ζήτηση, δεν υπάρχει τρόπος να βγάλω περισσότερα από 6.627,20 δολάρια αυτό το μήνα.