Verwenden von Solver zum Bestimmen des optimalen Produktmixes - Microsoft Support

Mit Solver den optimalen Produktmix ermitteln

Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr... Weniger

Dieser Artikel beschreibt die Verwendung von Solver, einem Microsoft Excel-Add-In-Programm, das Sie für Was-wäre-wenn-Analysen verwenden können, um eine optimale Produktmischung zu bestimmen.

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

Unternehmen müssen oft die monatlich zu produzierende Menge jedes Produkts bestimmen. In seiner einfachsten Form geht es beim Produktmixproblem darum, die Menge jedes Produkts zu bestimmen, die während eines Monats produziert werden sollte, um den Gewinn zu maximieren. Der Produktmix muss normalerweise die folgenden Einschränkungen einhalten:

• Der Produktmix kann nicht mehr Ressourcen verbrauchen, als verfügbar sind.

• Für jedes Produkt gibt es eine begrenzte Nachfrage. Wir können in einem Monat nicht mehr von einem Produkt produzieren, als die Nachfrage diktiert, weil die überschüssige Produktion verschwendet wird (z. B. ein verderbliches Medikament).

Lassen Sie uns nun das folgende Beispiel des Produktmixproblems lösen. Die Lösung für dieses Problem finden Sie in der Datei Prodmix.xlsx, die in Abbildung 27-1 gezeigt wird.

Nehmen wir an, wir arbeiten für ein Pharmaunternehmen, das in seinem Werk sechs verschiedene Produkte herstellt. Die Herstellung jedes Produkts erfordert Arbeit und Rohstoffe. Zeile 4 in Abbildung 27-1 zeigt die Arbeitsstunden, die benötigt werden, um ein Pfund jedes Produkts herzustellen, und Zeile 5 zeigt die Pfund Rohmaterial, die benötigt werden, um ein Pfund jedes Produkts herzustellen. Beispielsweise erfordert die Herstellung eines Pfunds von Produkt 1 sechs Arbeitsstunden und 3,2 Pfund Rohmaterial. Für jedes Medikament ist in Zeile 6 der Preis pro Pfund angegeben, in Zeile 7 die Stückkosten pro Pfund und in Zeile 9 der Gewinnbeitrag pro Pfund Stückkosten von 5,70 $ pro Pfund und trägt 5,30 $ Gewinn pro Pfund bei. Die Monatsnachfrage für jedes Medikament ist in Zeile 8 angegeben. Beispielsweise beträgt die Nachfrage nach Produkt 3 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 wüssten, würden wir dieses Problem angehen, indem wir ein Arbeitsblatt erstellen, um den Gewinn und die Ressourcennutzung im Zusammenhang mit dem Produktmix zu verfolgen. Dann würden wir Versuch und Irrtum anwenden, um den Produktmix zu variieren, um den Gewinn zu optimieren, ohne mehr Arbeit oder Rohmaterial einzusetzen, als verfügbar ist, und ohne ein Medikament zu produzieren, das die Nachfrage übersteigt. Wir verwenden Solver in diesem Prozess nur in der Trial-and-Error-Phase. Im Wesentlichen ist Solver eine Optimierungsmaschine, die die Trial-and-Error-Suche fehlerfrei durchführt.

Ein Schlüssel zur Lösung des Produktmixproblems ist die effiziente Berechnung der Ressourcennutzung und des Gewinns, die mit einem bestimmten Produktmix verbunden sind. Ein wichtiges Werkzeug, das wir für diese Berechnung verwenden können, ist die SUMPRODUCT-Funktion. Die SUMPRODUCT-Funktion multipliziert entsprechende Werte in Zellbereichen und gibt die Summe dieser Werte zurück. Jeder Zellbereich, der in einer SUMMENPRODUKT-Evaluierung verwendet wird, muss dieselben Dimensionen haben, was bedeutet, dass Sie SUMMENPRODUKT mit zwei Zeilen oder zwei Spalten verwenden können, aber nicht mit einer Spalte und einer Zeile.

Als Beispiel dafür, wie wir die SUMPRODUCT-Funktion in unserem Produktmix-Beispiel verwenden können, versuchen wir, unsere Ressourcennutzung zu berechnen. Unser Arbeitseinsatz wird berechnet nach

(Aufgewendete Arbeit pro Pfund Droge 1)*(Medikament 1 Pfund produziert)+
(Aufgewendete Arbeit pro Pfund Droge 2)*(Medikament 2 Pfund produziert) + ...
(Aufgewendete Arbeit pro Pfund Droge 6)*(Medikament 6 Pfund produziert)

Wir könnten den Arbeitsverbrauch auf mühsamere Weise als D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 berechnen. In ähnlicher Weise könnte der Rohstoffverbrauch als D2*D5+E2* E5+F2*F5+G2*G5+H2*H5+I2*I5 berechnet werden. Das Eingeben 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 seinem Werk herstellt. Eine viel einfachere Möglichkeit, den Arbeits- und Rohstoffverbrauch zu berechnen, besteht darin, die Formel SUMPRODUCT($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 unser Arbeitsaufwand ist), ist aber viel einfacher einzugeben! Beachten Sie, dass ich das $-Zeichen mit dem Bereich D2:I2 verwende, sodass ich beim Kopieren der Formel immer noch den Produktmix 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 lässt sich einfach in Zelle D12 mit der Formel SUMPRODUCT(D9:I9,$D$2:$I$2) berechnen.

Wir können jetzt die drei Komponenten unseres Produktmix-Solver-Modells identifizieren.

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

• Zellen wechseln. Die Anzahl der von jedem Produkt produzierten Pfund (aufgelistet im Zellbereich D2:I2)

• Einschränkungen. Wir haben folgende Einschränkungen:

  • Setzen Sie nicht mehr Arbeit oder Rohmaterial ein, 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 nachgefragt wird. Das heißt, die Werte in den Zellen D2:I2 (von jedem Medikament produzierte Pfund) müssen kleiner oder gleich der Nachfrage nach jedem Medikament sein (aufgelistet in den Zellen D8:I8).

  • Wir können keine negative Menge irgendeiner Droge produzieren.

Ich zeige Ihnen, wie Sie in Solver die Zielzelle eingeben, Zellen ändern und Einschränkungen vornehmen. Dann brauchen Sie nur noch auf den Solve-Button zu klicken, um einen gewinnmaximierenden Produktmix zu finden!

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

Hinweis: Wie in Kapitel 26, „Eine Einführung in die Optimierung mit Excel Solver", erläutert, wird Solver installiert, indem Sie auf die Microsoft Office-Schaltfläche, dann auf Excel-Optionen und dann auf Add-Ins klicken. Klicken Sie in der Liste Verwalten auf Excel-Add-Ins, aktivieren Sie das Kontrollkästchen Solver-Add-In und klicken Sie dann auf OK.

Das Dialogfeld „Solver-Parameter" wird angezeigt (siehe Abbildung 27-2).

Klicken Sie auf das Feld Zielzelle festlegen und wählen Sie dann unsere Gewinnzelle (Zelle D12). Klicken Sie auf das Feld By Changing Cells und zeigen Sie dann auf den Bereich D2:I2, der die von jedem Medikament produzierten Pfund enthält. Das Dialogfeld sollte jetzt wie in Abbildung 27-3 aussehen.

Wir können jetzt dem Modell Einschränkungen hinzufügen. Klicken Sie auf die Schaltfläche Hinzufügen. Sie sehen das Dialogfeld „Einschränkung hinzufügen", das in Abbildung 27-4 gezeigt wird.

Klicken Sie zum Hinzufügen der Ressourcennutzungseinschränkungen auf das Feld Zellbezug und wählen Sie dann den Bereich D14:D15 aus. Wählen Sie <= aus 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 jetzt wie in Abbildung 27-5 aussehen.

Wir haben jetzt sichergestellt, dass, wenn Solver verschiedene Werte für die sich ändernden Zellen ausprobiert, nur Kombinationen angezeigt werden, die sowohl D14<=F14 (aufgewendete Arbeit ist kleiner oder gleich der verfügbaren Arbeit) als auch D15<=F15 (verwendetes Rohmaterial ist kleiner oder gleich) erfüllen auf verfügbares Rohmaterial) werden berücksichtigt. Klicken Sie auf Hinzufügen, um die Bedarfsbedingungen einzugeben. Füllen Sie das Dialogfeld Bedingung hinzufügen aus, wie in Abbildung 27-6 gezeigt.

Das Hinzufügen dieser Einschränkungen stellt sicher, dass, wenn Solver verschiedene Kombinationen für die sich ändernden Zellenwerte ausprobiert, nur Kombinationen berücksichtigt werden, die die folgenden Parameter erfüllen:

• D2<=D8 (die produzierte Menge von Medikament 1 ist kleiner oder gleich der Nachfrage nach Medikament 1)

• E2<=E8 (die produzierte Menge von Medikament 2 ist kleiner oder gleich der Nachfrage nach Medikament 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 die Bedingung ein, dass das Ändern von Zellen nicht negativ sein muss, in das Dialogfeld Solver-Optionen. Klicken Sie im Dialogfeld Solver-Parameter auf die Schaltfläche Optionen. Aktivieren Sie die Kästchen Assume Linear Model und Assume Non-Negative, wie in Abbildung 27-8 auf der nächsten Seite gezeigt. OK klicken.

Durch Aktivieren des Kontrollkästchens Nicht-Negativ annehmen stellt sicher, dass Solver nur Kombinationen sich ändernder Zellen berücksichtigt, in denen jede sich ändernde Zelle einen nicht negativen Wert annimmt. Wir haben das Kästchen „Lineares Modell annehmen" aktiviert, weil das Produktmischungsproblem eine spezielle Art von Solver-Problem ist, die als lineares Modell bezeichnet wird. Im Wesentlichen ist ein Solver-Modell unter den folgenden Bedingungen linear:

• Die Zielzelle wird berechnet, indem die Terme der Form (veränderliche Zelle)*(Konstante) addiert werden.

• Jede Einschränkung erfüllt die „lineare Modellanforderung". Das bedeutet, dass jede Einschränkung ausgewertet wird, indem die Terme der Form (veränderliche Zelle)*(Konstante) addiert und die Summen mit einer Konstanten verglichen werden.

Warum ist dieses Solver-Problem linear? Unsere Zielzelle (Gewinn) 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, bei dem der Wert der Zielzelle abgeleitet wird, indem Terme der Form (veränderliche Zelle)*(Konstante) addiert werden.

Unsere Arbeitsbeschränkung wird bewertet, indem der Wert verglichen wird, der sich aus (Arbeitsaufwand pro Pfund Medikament 1)*(Medikament 1 Pfund produziert) + (Arbeitsaufwand pro Pfund Medikament 2)*(Medikament 2 Pfund produziert)+ …(Arbeitsaufwand ed pro Pfund Droge 6)*(Medikament 6 Pfund produziert) zur verfügbaren Arbeitskraft.

Daher wird die Arbeitsbeschränkung bewertet, indem die Terme der Form (veränderliche Zelle)*(Konstante) addiert und die Summen mit einer Konstanten verglichen werden. Sowohl die Arbeitsbeschränkung als auch die Rohstoffbeschränkung erfüllen die Anforderung des linearen Modells.

Unsere Nachfragebeschränkungen nehmen die Form an

(Medikament 1 produziert) <= (Medikament 1 Nachfrage)
(Medikament 2 produziert) <= (Medikament 2 Nachfrage)
§
(Medikament 6 produziert) <= (Medikament 6 Nachfrage)

Jede Bedarfsbeschränkung erfüllt auch die Anforderung des linearen Modells, da jede berechnet wird, indem die Terme der Form (veränderliche Zelle)*(Konstante) addiert und die Summen mit einer Konstante verglichen werden.

Nachdem wir gezeigt haben, dass unser Produktmixmodell ein lineares Modell ist, warum sollte uns das interessieren?

• Wenn ein Solver-Modell linear ist und wir Assume Linear Model auswählen, findet Solver garantiert die optimale Lösung für das Solver-Modell. Wenn ein Solver-Modell nicht linear ist, kann Solver die optimale Lösung finden oder auch nicht.

• Wenn ein Solver-Modell linear ist und wir Assume Linear Model auswählen, verwendet Solver einen sehr effizienten Algorithmus (die Simplex-Methode), um die optimale Lösung des Modells zu finden. Wenn ein Solver-Modell linear ist und wir Assume Linear Model nicht auswählen, verwendet Solver einen sehr ineffizienten Algorithmus (die GRG2-Methode) und hat möglicherweise Schwierigkeiten, die optimale Lösung des Modells zu finden.

Nachdem wir im Dialogfeld „Solver-Optionen" auf „OK" geklickt haben, kehren wir zum Hauptdialogfeld „Solver" zurück, das zuvor in Abbildung 27-7 gezeigt wurde. Wenn wir auf Lösen klicken, berechnet Solver eine optimale Lösung (falls vorhanden) für unser Produktmixmodell. Wie ich in Kapitel 26 festgestellt habe, wäre eine optimale Lösung für das Produktmixmodell ein Satz sich ändernder Zellwerte (von jedem Medikament produzierte Pfund), der den Gewinn über den Satz aller möglichen Lösungen maximiert. Wiederum ist eine praktikable Lösung ein Satz sich ändernder Zellenwerte, die alle Einschränkungen erfüllen. Die in Abbildung 27-9 gezeigten sich ändernden Zellenwerte sind eine praktikable Lösung, da alle Produktionsniveaus nicht negativ sind, die Produktionsniveaus die Nachfrage nicht überschreiten und die Ressourcennutzung die verfügbaren Ressourcen nicht übersteigt.

Die in Abbildung 27-10 auf der nächsten Seite gezeigten sich ändernden Zellenwerte stellen aus folgenden Gründen eine nicht durchführbare Lösung dar:

• Wir produzieren mehr von Droge 5 als die Nachfrage danach.

• Wir verwenden mehr Arbeitskraft als verfügbar ist.

• Wir verbrauchen mehr Rohmaterial als zur Verfügung steht.

Nachdem Sie auf Lösen geklickt haben, findet Solver schnell die optimale Lösung, wie in Abbildung 27-11 gezeigt. 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 Medikament 4, 1084 Pfund Medikament 5 und keines der anderen Medikamente produziert! Wir können nicht feststellen, ob wir den maximalen Gewinn von 6.625,20 $ auf andere Weise erzielen können. Alles, dessen wir uns sicher sein können, ist, dass es mit unseren begrenzten Ressourcen und unserer begrenzten Nachfrage keine Möglichkeit gibt, diesen Monat mehr als 6.627,20 $ zu verdienen.

Angenommen, die Nachfrage nach jedem Produkt muss gedeckt werden. (Siehe das Arbeitsblatt „No Feasible Solution" in der Datei Prodmix.xlsx.) Wir müssen dann unsere Nachfragebeschränkungen von D2:I2<=D8:I8 in D2:I2>=D8:I8 ändern. Öffnen Sie dazu Solver, wählen Sie die Einschränkung D2:I2<=D8:I8 aus, und klicken Sie dann auf Ändern. Das Dialogfeld „Constraint ändern" (siehe Abbildung 27-12) wird angezeigt.

Wählen Sie >= und klicken Sie dann auf OK. Wir haben jetzt sichergestellt, dass Solver nur Zellwerte ändert, die alle Anforderungen erfüllen. Wenn Sie auf „Lösen" klicken, wird die Meldung „Solver konnte keine praktikable Lösung finden" angezeigt. Diese Meldung bedeutet nicht, dass wir in unserem Modell einen Fehler gemacht haben, sondern dass wir mit unseren begrenzten Ressourcen die Nachfrage nicht für alle Produkte decken können. Solver sagt uns einfach, dass wir, wenn wir die Nachfrage nach jedem Produkt befriedigen wollen, mehr Arbeit, mehr Rohstoffe oder mehr von beidem hinzufügen müssen.

Mal sehen, was passiert, wenn wir eine unbegrenzte Nachfrage nach jedem Produkt zulassen und wir zulassen, dass von jedem Medikament negative Mengen produziert werden. (Sie können dieses Solver-Problem auf dem Arbeitsblatt Set Values ​​Do Not Converge in der Datei Prodmix.xlsx sehen.) Um die optimale Lösung für diese Situation zu finden, öffnen Sie Solver, klicken Sie auf die Schaltfläche Optionen und deaktivieren Sie das Kontrollkästchen Assume Non-Negative. Wählen Sie im Dialogfeld Solver-Parameter die Bedarfsbeschränkung D2:I2<=D8:I8 aus, und klicken Sie dann auf Löschen, um die Beschränkung zu entfernen. Wenn Sie auf „Lösen" klicken, gibt Solver die Meldung „Festgelegte Zellenwerte konvergieren nicht" zurück. Diese Meldung bedeutet, dass wenn die Zielzelle maximiert werden soll (wie in unserem Beispiel), es machbare Lösungen mit beliebig großen Zielzellenwerten gibt. (Wenn die Zielzelle minimiert werden soll, bedeutet die Meldung „Festgelegte Zellwerte konvergieren nicht", dass es machbare Lösungen mit willkürlich kleinen Zielzellwerten gibt.) In unserer Situation, indem wir die negative Produktion eines Medikaments zulassen, „schaffen wir tatsächlich „Ressourcen, aus denen beliebig große Mengen anderer Drogen hergestellt werden können. Angesichts unserer unbegrenzten Nachfrage können wir so unbegrenzte Gewinne erzielen. In einer realen Situation können wir nicht unendlich viel Geld verdienen. Kurz gesagt, wenn Sie „Festgelegte Werte konvergieren nicht" sehen, weist Ihr Modell einen Fehler auf.

1. Angenommen, unser Pharmaunternehmen kann bis zu 500 Arbeitsstunden zu einem Preis von 1 USD pro Stunde mehr als die aktuellen Arbeitskosten kaufen. Wie können wir den Gewinn maximieren?

2. In einer Chipfabrik produzieren vier Techniker (A, B, C und D) drei Produkte (Produkte 1, 2 und 3). In diesem Monat kann der Chiphersteller 80 Einheiten von Produkt 1, 50 Einheiten von Produkt 2 und höchstens 50 Einheiten von Produkt 3 verkaufen. Techniker A kann nur die Produkte 1 und 3 herstellen. Techniker B kann nur die Produkte 1 und 2 herstellen. Techniker C kann nur Produkt 3 herstellen. Techniker D kann nur Produkt 2 herstellen. Für jede produzierte Einheit tragen die Produkte zu folgendem Gewinn bei: Produkt 1, 6 $; Produkt 2, 7 $; und Produkt 3, 10 $. Die Zeit (in Stunden), die jeder Techniker benötigt, um ein Produkt herzustellen, ist wie folgt:

   Produkt	Techniker A	Techniker B	Techniker C	Techniker d
   1	2	2.5	Nicht können	Nicht können
   2	Nicht können	3	Nicht können	3.5
   3	3	Nicht können	4	Nicht können

3. Jeder Techniker kann bis zu 120 Stunden pro Monat arbeiten. Wie kann der Chiphersteller seinen monatlichen Gewinn maximieren? Nehmen Sie an, dass eine Bruchzahl von Einheiten produziert werden kann.

4. Eine Computerfabrik stellt Mäuse, Tastaturen und Joysticks für Videospiele her. Der Gewinn pro Einheit, der Arbeitseinsatz pro Einheit, der monatliche Bedarf und die Nutzung der Maschinenzeit pro Einheit sind in der folgenden Tabelle angegeben:

   	Mäuse	Tastaturen	Joysticks
   Gewinn/Einheit	$8	$11	$9
   Arbeitseinsatz/Einheit	.2 Stunden	.3 Stunden	.24 Stunden
   Maschinenzeit/Einheit	0,04 Stunde	0,055 Stunde	0,04 Stunde
   Monatliche Nachfrage	15.000	27.000	11.000

5. Monatlich stehen insgesamt 13.000 Arbeitsstunden und 3.000 Stunden Maschinenzeit zur Verfügung. Wie kann der Hersteller seinen monatlichen Gewinnbeitrag aus der Anlage maximieren?

6. Lösen Sie unser Medikamentenbeispiel unter der Annahme, dass für jedes Medikament ein Mindestbedarf von 200 Einheiten gedeckt werden muss.

7. Jason stellt Diamantarmbänder, Halsketten und Ohrringe her. Er will maximal 160 Stunden im Monat arbeiten. Er hat 800 Unzen Diamanten. Der Gewinn, die Arbeitszeit und die Unzen Diamanten, die zur Herstellung jedes Produkts erforderlich sind, sind unten angegeben. Wenn die Nachfrage nach jedem Produkt unbegrenzt ist, wie kann Jason seinen Gewinn maximieren?

   Produkt	Einheitsgewinn	Arbeitsstunden pro Einheit	Unzen Diamanten pro Einheit
   Armband	$300	.35	1.2
   Halskette	$200	.15	.75
   Ohrringe	$100	.05	.5