Optimale Programmplanung mit dem Solver in Excel

Gewinnmaximierung gehört neben anderen Zielen wie z.B. Kostensenkung immer zu den Zielen eines Unternehmens.

Verhalten sich die zu betrachtenden Kennzahlen linear, werden auch Methoden der linearen Optimierung angewendet werden, um dem Ziel näher zu kommen.

Dieser Beitrag zeigt, wie in Excel mit dem Solver solche Berechnungen durchgeführt werden können.

Was ist der Solver?

Wer könnte das besser erklären als Microsoft selbst. So ist auf einer Seite von support.office [1] zu lesen:

„Solver ist ein Add-In-Programm für Microsoft Excel, das Sie für Was-wäre-wenn-Analyse verwenden können.
Mithilfe von Solver können Sie den optimalen (maximalen oder minimalen) Wert für eine Formel in einer als Zielzelle bezeichneten Tabellenzelle ermitteln, die Nebenbedingungen, oder Einschränkungen, der Werte anderer Formelzellen auf einem Arbeitsblatt unterliegt. In Solver wird mit einer Gruppe von Zellen gearbeitet, die als „veränderbare Zellen“ oder einfach „Variablenzellen“ bezeichnet werden und zur Berechnung der Formeln in den Zielzellen und Nebenbedingungszellen verwendet werden. Solver passt die Werte in den Variablenzellen an, sodass sie den Einschränkungen für Nebenbedingungszellen entsprechen und das für die Zielzelle gewünschte Ergebnis erzeugt wird.“

Während bei der Zielwertsuche immer nur eine Zelle veränderbar definiert werden konnte, können mit dem Solver mehrere Zellen verändert werden.

Weiterhin ist es hier möglich, mehrere Nebenbedingungen festzusetzen, die bei der Berechnung des Optimums zu berücksichtigen sind.

Ein optimales Produktionsprogramm bestimmen

Im Beispiel soll es um ein kleines Unternehmen gehen, dass drei Produkte herstellen kann.

Für die Produkte fallen unterschiedliche variable, mengenabhängige, Kosten in der Herstellung an. Mit den Produkten lassen sich unterschiedliche Preise am Markt erzielen.

Die gesamten Erlöse je Produkt abzüglich der gesamten variablen Kosten je Produkt ergeben den sogenannten Deckungsbeitrag.

Die Summe der Deckungsbeiträge muss auskömmlich sein, um die fixen Kosten des Unternehmens sowie die Gewinnerwartungen des Unternehmens zu decken.

Die nachfolgende Tabelle zeigt, worum es geht:

SolverProg1

Als Menge wurden in der Ausgangstabelle zunächst jeweils 1000 Stück eingetragen. Zu sehen ist bereits, dass Produkt B einen negativen Deckungsbeitrag abwirft. Es sollte deshalb gar nicht produziert werden. Aber es gibt nach Vertragslage eine Mindestmenge, die produziert werden muss.

Als Nebenbedingungen werden definiert:

SolverProg2

Die Mindestmengen werden mit bereits vereinbarten Lieferungen begründet. Die Maximalmengen ergeben sich aus den Kapazitätsgrenzen der Produktionsanlagen.

Einsatz des Solvers

Nun geht es an die Optimierung des Produktionsprogramms. Dazu ist zuerst das Ziel zu bestimmen. Ziel soll die Maximierung des Gesamtdeckungsbeitrages in Zelle G8 sein.

Veränderbar sollen die Zellen mit den Mengenangaben sein, also B5:B7.

Als Nebenbedingungen müssen definiert werden:

  • von Produkt A müssen mindestens 14.000 aber höchstens 17.000 Stück produziert werden
  • von Produkt B müssen mindestens 42.000 produziert werden, wegen des negativen Deckungsbeitrages soll dies aber auch die Höchstmenge sein
  • von Produkt C müssen mindestens 36.000, höchstens aber 50.000 Stück produziert werden

Um nun den Solver aufzurufen, gehst Du im Menü Daten auf Analyse und klickst Solver an. Hast Du ihn dort nicht vorliegen, aktiviere das Add-In über Datei/Optionen/Add-Ins.

Fülle die Maske nun entsprechend den Vorgaben aus:

SolverProg3

Klicke auf „Lösen“. Excel teilt Dir jetzt mit, dass der Solver eine Lösung gefunden hat. Du kannst diese akzeptieren oder die ursprünglichen Werte zurück haben wollen. Treffe Deine Entscheidung und klicke „OK“.

SolverProg4

Die Tabelle sieht, nachdem Du die Lösung akzeptiert hast, so aus:

SolverProg5

Die Lösung besagt, dass von den Produkten A und C die maximale Menge produziert werden soll.

Von Produkt B wird nur die bereits vertraglich gebundene Mindestmenge produziert, wie Du als Nebenbedingung bestimmt hast.

Somit ist in diesem überschaubaren Beispiel das optimale Produktionsprogramm mit dem Solver bestimmt worden.

Wenn es für Dich interessant ist, findest Du hier zwei weitere Beiträge zum Thema Solver:

So findest Du mit dem Solver eine optimale Lösung

Mit dem Solver die Transportleistung optimieren

[1] https://support.office.com/de-de/article/Erkennen-und-Lösen-eines-Problems-mithilfe-von-Solver-5D1A388F-079D-43AC-A7EB-F63E45925040

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s