Verwenden von Solver zur Bestimmung der optimalen Produktmischung

Wie kann ich den monatlichen Produktmix bestimmen, der die Rentabilität maximiert?

Unternehmen müssen häufig die Menge der einzelnen Produkte bestimmen, die monatlich produziert werden sollen. In seiner einfachsten Form umfasst das Produktmischungsproblem , wie die Menge jedes Produkts bestimmt wird, die während eines Monats produziert werden sollte, um den Gewinn zu maximieren. Die Produktmischung muss in der Regel die folgenden Einschränkungen einhalten:

• Die Produktmischung kann nicht mehr Ressourcen verwenden, als verfügbar sind.

• Die Nachfrage nach jedem Produkt ist begrenzt. Wir können während eines Monats nicht mehr von einem Produkt produzieren, als es die Nachfrage vorschreibt, da die überschüssige Produktion verschwendet wird (z. B. eine verderbliche Droge).

Lassen Sie uns nun das folgende Beispiel für das Produktmischungsproblem lösen. Die Lösung für dieses Problem finden Sie in der Datei Prodmix.xlsx, wie in Abbildung 27-1 dargestellt.

Angenommen, wir arbeiten für ein Arzneimittelunternehmen, das sechs verschiedene Produkte in seinem Werk produziert. Die Herstellung jedes Produkts erfordert Arbeit und Rohstoffe. Zeile 4 in Abbildung 27-1 zeigt die Arbeitsstunden, die erforderlich sind, um ein Pfund jedes Produkts zu produzieren, und Zeile 5 zeigt die Pfund an Rohmaterial an, die für die Herstellung eines Pfunds jedes Produkts erforderlich sind. Zum Beispiel erfordert die Herstellung eines Pfunds Produkt 1 sechs Stunden Arbeit und 3,2 Pfund Rohmaterial. Für jedes Medikament wird der Preis pro Pfund in Zeile 6 angegeben, die Stückkosten pro Pfund wird in Zeile 7 angegeben, und der Gewinnbeitrag pro Pfund wird in Zeile 9 angegeben. Produkt 2 verkauft sich beispielsweise für 11,00 US-Dollar pro Pfund, verursacht Stückkosten von 5,70 US-Dollar pro Pfund und trägt einen Gewinn von 5,30 US-Dollar pro Pfund bei. Die Monatsnachfrage für jedes Medikament wird in Zeile 8 angegeben. Die Nachfrage nach Produkt 3 beträgt beispielsweise 1041 Pfund. In diesem Monat stehen 4500 Arbeitsstunden und 1600 Pfund Rohmaterial zur Verfügung. Wie kann dieses Unternehmen seinen monatlichen Gewinn maximieren?

Wenn wir nichts über Excel Solver wissen, würden wir dieses Problem angreifen, indem wir ein Arbeitsblatt erstellen, um den Gewinn und die Ressourcennutzung im Zusammenhang mit dem Produktmix nachzuverfolgen. Dann würden wir Versuchen und Irrtum verwenden, um die Produktmischung zu variieren, um den Gewinn zu optimieren, ohne mehr Arbeit oder Rohmaterial zu verbrauchen, als verfügbar ist, und ohne ein Medikament zu produzieren, das die Nachfrage übersteigen. Wir verwenden Solver in diesem Prozess nur in der Test-and-Error-Phase. Im Wesentlichen ist Solver eine Optimierungs-Engine, die die Trial-and-Error-Suche fehlerfrei ausführt.

Ein Schlüssel zur Lösung des Produktmixproblems ist die effiziente Berechnung der Ressourcennutzung und des Gewinns, die mit einer bestimmten Produktmischung verbunden sind. Ein wichtiges Tool, das wir für diese Berechnung verwenden können, ist die SUMMENPRODUKT-Funktion. Die funktion SUMMENPRODUKT multipliziert entsprechende Werte in Zellbereichen und gibt die Summe dieser Werte zurück. Jeder Zellbereich, der in einer SUMMENPRODUKT Auswertung verwendet wird, muss die gleichen Dimensionen aufweisen. Dies bedeutet, dass Sie SUMMENPRODUKT mit zwei Zeilen oder zwei Spalten verwenden können, jedoch nicht mit einer Spalte und einer Zeile.

Als Beispiel für die Verwendung der SUMMENPRODUKT-Funktion in unserem Produktmischungsbeispiel versuchen wir, die Ressourcennutzung zu berechnen. Unser Arbeitseinsatz wird berechnet durch

(Arbeit pro Pfund Drogen 1)*(Medikament 1 Pfund produziert)+
(Arbeit pro Pfund des Medikaments 2)*(Medikament 2 Pfund produziert) + ...
(Arbeitsaufwand pro Pfund Medikament 6)*(Medikament 6 Pfund produziert)

Wir könnten die Arbeitsauslastung als D2*D4+E2*E4+F2*F4+G2*G4+G2*G4+H2*H4+I2*I4 berechnen. Ebenso kann die Rohstoffnutzung als D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 berechnet werden. Die Eingabe dieser Formeln in ein Arbeitsblatt für sechs Produkte ist jedoch zeitaufwändig. Stellen Sie sich vor, wie lange es dauern würde, wenn Sie mit einem Unternehmen zusammenarbeiten würden, das beispielsweise 50 Produkte in ihrem Werk produzierte. Eine viel einfachere Möglichkeit zum Berechnen der Arbeits- und Rohstoffnutzung besteht darin, die Formel SUMMENPRODUKT($D$2:$I$2,D4:I4) von D14 nach D15 zu kopieren. Diese Formel berechnet D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (was unsere Arbeitsauslastung ist), ist aber viel einfacher zu eingeben! Beachten Sie, dass ich das $-Zeichen mit dem Bereich D2:I2 verwende, damit ich beim Kopieren der Formel immer noch die Produktmischung aus Zeile 2 erfasse. Die Formel in Zelle D15 berechnet den Rohstoffverbrauch.

In ähnlicher Weise wird unser Gewinn bestimmt durch

(Medikament 1 Gewinn pro Pfund)*(Medikament 1 Pfund produziert) +
(Medikament 2 Gewinn pro Pfund)*(Medikament 2 Pfund produziert) + ...
(Medikament 6 Gewinn pro Pfund)*(Medikament 6 Pfund produziert)

Der Gewinn wird in Zelle D12 mit der Formel SUMMENPRODUKT(D9:I9,$D$2:$I$2)) leicht berechnet.

Wir können nun die drei Komponenten unseres Solver-Modells für produktmixen identifizieren.

• Zielzelle. Unser Ziel ist es, den Gewinn zu maximieren (berechnet in Zelle D12).

• Ändern von Zellen. Die Anzahl der produzierten Pfund jedes Produkts (aufgeführt im Zellbereich D2:I2)

• Zwänge. Es gelten die folgenden Einschränkungen:

  • Verwenden Sie nicht mehr Arbeit oder Rohmaterial, als verfügbar ist. Das heißt, die Werte in den Zellen D14:D15 (die verwendeten Ressourcen) müssen kleiner oder gleich den Werten in den Zellen F14:F15 (die verfügbaren Ressourcen) sein.

  • Produzieren Sie nicht mehr von einem Medikament, als gefragt ist. Das heißt, die Werte in den Zellen D2:I2 (Pfund, die von jedem Medikament produziert werden) müssen kleiner oder gleich dem Bedarf für jedes Medikament sein (aufgeführt in den Zellen D8:I8).

  • Wir können keine negative Menge an Medikamenten produzieren.

Ich zeige Ihnen, wie Sie die Zielzelle eingeben, Zellen und Einschränkungen in Solver ändern. Dann müssen Sie nur noch auf die Schaltfläche "Lösen" klicken, um einen gewinnmaximierenden Produktmix zu finden!

Klicken Sie zunächst auf die Registerkarte Daten, und klicken Sie in der Gruppe Analyse auf Solver.

Das Dialogfeld Solver-Parameter wird angezeigt, wie in Abbildung 27-2 dargestellt.

Klicken Sie auf das Feld Zielzelle festlegen, und wählen Sie dann unsere Gewinnzelle (Zelle D12) aus. Klicken Sie auf das Feld Durch Zellen ändern, und zeigen Sie dann auf den Bereich D2:I2, der die von den einzelnen Medikamenten produzierten Pfund enthält. Das Dialogfeld sollte nun in Abbildung 27-3 angezeigt werden.

Wir sind nun bereit, dem Modell Einschränkungen hinzuzufügen. Klicken Sie auf die Schaltfläche Hinzufügen. In Abbildung 27-4 wird das Dialogfeld Einschränkung hinzufügen angezeigt.

Klicken Sie zum Hinzufügen der Ressourcennutzungseinschränkungen auf das Feld Zellverweis, und wählen Sie dann den Bereich D14:D15 aus. Wählen Sie in der mittleren Liste <= aus. Klicken Sie auf das Feld Einschränkung, und wählen Sie dann den Zellbereich F14:F15 aus. Das Dialogfeld Einschränkung hinzufügen sollte nun wie in Abbildung 27-5 aussehen.

Wir haben nun sichergestellt, dass, wenn Solver verschiedene Werte für die sich ändernden Zellen versucht, nur Kombinationen berücksichtigt werden, die sowohl D14<=F14 (die verwendete Arbeit ist kleiner oder gleich der verfügbaren Arbeit) als auch D15<=F15 (das verwendete Rohmaterial ist kleiner oder gleich dem verfügbaren Rohmaterial) erfüllen. Klicken Sie auf Hinzufügen, um die Bedarfseinschränkungen einzugeben. Füllen Sie das Dialogfeld Einschränkung hinzufügen aus, wie in Abbildung 27-6 dargestellt.

Durch Das Hinzufügen dieser Einschränkungen wird sichergestellt, dass nur Kombinationen berücksichtigt werden, die die folgenden Parameter erfüllen, wenn Solver verschiedene Kombinationen für die sich ändernden Zellwerte versucht:

• D2<=D8 (die Menge, die von Medikament 1 produziert wird, ist kleiner oder gleich dem Bedarf an Medikament 1)

• E2<=E8 (die Menge der produzierten Arzneimittel 2 ist kleiner oder gleich der Nachfrage nach Rauschgift 2)

• F2<=F8 (die produzierte Menge von Medikament 3 ist kleiner oder gleich der Nachfrage nach Medikament 3)

• G2<=G8 (die produzierte Menge von Medikament 4 ist kleiner oder gleich der Nachfrage nach Medikament 4)

• H2<=H8 (die produzierte Menge von Medikament 5 ist kleiner oder gleich der Nachfrage nach Medikament 5)

• I2<=I8 (die produzierte Menge von Medikament 6 ist kleiner oder gleich der Nachfrage nach Medikament 6)

Klicken Sie im Dialogfeld Einschränkung hinzufügen auf OK. Das Solver-Fenster sollte wie in Abbildung 27-7 aussehen.

Wir geben im Dialogfeld Solver-Optionen die Einschränkung ein, dass sich ändernde Zellen nicht negativ sein müssen. Klicken Sie im Dialogfeld Solver-Parameter auf die Schaltfläche Optionen. Aktivieren Sie das Kontrollkästchen Lineares Modell annehmen und das Kontrollkästchen Nicht negativ annehmen, wie in Abbildung 27-8 auf der nächsten Seite dargestellt. Klicken Sie auf "OK".

Wenn Sie das Feld Nicht negativ annehmen aktivieren, wird sichergestellt, dass Solver nur Kombinationen von sich ändernden Zellen berücksichtigt, in denen jede sich ändernde Zelle einen nicht negativen Wert annimmt. Wir haben das Feld Lineares Modell annehmen aktiviert, da das Produktmischungsproblem eine spezielle Art von Solver-Problem ist, das als lineares Modell bezeichnet wird. Im Wesentlichen ist ein Solver-Modell unter den folgenden Bedingungen linear:

• Die Zielzelle wird berechnet, indem die Begriffe der Form (ändernde Zelle)*(konstante) addiert werden.

• Jede Einschränkung erfüllt die Anforderung "lineares Modell". Dies bedeutet, dass jede Einschränkung ausgewertet wird, indem die Begriffe des Formulars addiert werden (Zelle ändern)*(konstant) und die Summen mit einer Konstante verglichen werden.

Warum ist dieses Solver-Problem linear? Unsere Zielzelle (Profit) wird berechnet als

(Medikament 1 Gewinn pro Pfund)*(Medikament 1 Pfund produziert) +
(Medikament 2 Gewinn pro Pfund)*(Medikament 2 Pfund produziert) + ...
(Medikament 6 Gewinn pro Pfund)*(Medikament 6 Pfund produziert)

Diese Berechnung folgt einem Muster, in dem der Wert der Zielzelle durch Addieren von Begriffen der Form (ändernde Zelle)*(konstante) abgeleitet wird.

Unsere Arbeitseinschränkung wird durch Den Vergleich des Werts ausgewertet, der von (Labor pro Pfund des Medikaments 1)*(Medikament 1 Pfund produziert) + (Arbeit pro Pfund des Medikaments 2)*(Medikament 2 Pfund produziert)+ ... (Arbeiten Sie unsed pro Pfund der Droge 6)*(Medikament 6 Pfund produziert) zur Arbeit zur Verfügung.

Daher wird die Arbeitseinschränkung ausgewertet, indem die Begriffe der Form (ändernde Zelle)*(konstante) addiert und die Summen mit einer Konstante verglichen werden. Sowohl die Arbeitseinschränkung als auch die Rohstoffeinschränkung erfüllen die Anforderung des linearen Modells.

Unsere Nachfrageeinschränkungen nehmen die Form an

(Drug 1 produced)<=(Drug 1 Demand)
(Drug 2 produced)<=(Drug 2 Demand)
§
(Drug 6 produced)<=(Drug 6 Demand)

Jede Anforderungseinschränkung erfüllt auch die Anforderung des linearen Modells, da jede durch Addieren der Begriffe des Formulars (ändernde Zelle)*(konstante) und vergleicht die Summen mit einer Konstante.

Nachdem wir gezeigt haben, dass unser Produktmixmodell ein lineares Modell ist, warum sollten wir uns darum kümmern?

• Wenn ein Solver-Modell linear ist und wir Lineares Modell annehmen auswählen, findet Solver garantiert die optimale Lösung für das Solver-Modell. Wenn ein Solver-Modell nicht linear ist, findet Solver möglicherweise die optimale Lösung.

• Wenn ein Solver-Modell linear ist und wir Lineares Modell annehmen auswählen, verwendet Solver einen sehr effizienten Algorithmus (die simplex-Methode), um die optimale Lösung für das Modell zu finden. Wenn ein Solver-Modell linear ist und wir nicht Lineares Modell annehmen auswählen, verwendet Solver einen sehr ineffizienten Algorithmus (die GRG2-Methode) und kann Schwierigkeiten haben, die optimale Lösung für das Modell zu finden.

Nachdem Sie im Dialogfeld Solver-Optionen auf OK geklickt haben, kehren Sie zum Dialogfeld Standard Solver zurück, wie weiter oben in Abbildung 27-7 dargestellt. Wenn wir auf Solve klicken, berechnet Solver eine optimale Lösung (sofern vorhanden) für unser Produktmixmodell. Wie ich in Kapitel 26 sagte, wäre eine optimale Lösung für das Produktmischungsmodell eine Reihe von sich ändernden Zellwerten (Pfund produziert von jedem Medikament), die den Gewinn gegenüber allen möglichen Lösungen maximieren. Auch hier ist eine mögliche Lösung eine Reihe von zellverändernden Werten, die alle Einschränkungen erfüllen. Die sich ändernden Zellwerte in Abbildung 27-9 sind eine praktikable Lösung, da alle Produktionsstufen nicht negativ sind, die Produktionsebenen den Bedarf nicht überschreiten und die Ressourcennutzung die verfügbaren Ressourcen nicht überschreitet.

Die sich ändernden Zellwerte in Abbildung 27-10 auf der nächsten Seite stellen aus den folgenden Gründen eine nicht umsetzbare Lösung dar:

• Wir produzieren mehr von Drug 5 als die Nachfrage dafür.

• Wir verbrauchen mehr Arbeit, als verfügbar ist.

• Wir verwenden mehr Rohmaterial, als verfügbar ist.

Nachdem Sie auf Solve geklickt haben, findet Solver schnell die optimale Lösung, die in Abbildung 27-11 dargestellt ist. Sie müssen Solver-Lösung beibehalten auswählen, um die optimalen Lösungswerte im Arbeitsblatt beizubehalten.

Unser Pharmaunternehmen kann seinen monatlichen Gewinn auf einem Niveau von 6.625,20 $ maximieren, indem es 596,67 Pfund Drug 4, 1084 Pfund Drug 5 und keines der anderen Medikamente produziert! Wir können nicht ermitteln, ob wir den maximalen Gewinn von 6.625,20 US-Dollar auf andere Weise erreichen können. Wir können uns nur sicher sein, dass es mit unseren begrenzten Ressourcen und unserer Nachfrage in diesem Monat keine Möglichkeit gibt, mehr als 6.627,20 US-Dollar zu verdienen.