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

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

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

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

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

Ας λύσουμε τώρα το παρακάτω παράδειγμα του προβλήματος με τη μείξη προϊόντων. Μπορείτε να βρείτε τη λύση για αυτό το πρόβλημα στο αρχείο 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 * 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 λίβρες που παράγονται) +... (Labor USEd ανά λίβρα του φαρμάκου 6) * (φάρμακο 6 λίβρες που παράγονται) στην εργασία διαθέσιμη.

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

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

(Φάρμακο 1 που παράγεται) < = (φάρμακο 1 ζήτηση)
(φάρμακο 2 που παράγεται) < = (φάρμακο 2 απαίτηση)
§
(φάρμακο 6 που παράγεται) < = (φάρμακο 6 απαίτηση)

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

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

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

• Εάν ένα μοντέλο επίλυσης είναι γραμμικό και επιλέγουμε το στοιχείο γραμμικό μοντέλο, η επίλυση χρησιμοποιεί έναν πολύ αποτελεσματικό αλγόριθμο (τη μέθοδο του απλού) για να βρει τη βέλτιστη λύση του μοντέλου. Εάν ένα μοντέλο επίλυσης είναι γραμμικό και δεν επιλέξουμε υποΘέτουμε γραμμικό μοντέλο, η επίλυση χρησιμοποιεί έναν αλγόριθμο πολύ αναποτελεσματικό (τη μέθοδο 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 αυτόν τον μήνα.