Excel Solver: Was ist das und wann sollten Sie es verwenden?

Excel-Tabellenkalkulationstools wie Goal Seek, die eine einzelne Variable ändern, sind nützlich, aber leider sind die meisten Probleme in Unternehmen nicht so einfach. Sie werden normalerweise mit Formeln mit mindestens zwei und manchmal Dutzenden von Variablen konfrontiert. Häufig gibt es für ein Problem mehr als eine Lösung, und Ihre Herausforderung besteht darin, Excel zu verwenden, um die optimale Lösung zu finden (d. h. diejenige, die den Gewinn maximiert oder die Kosten minimiert oder andere Kriterien erfüllt). Für diese größeren Herausforderungen benötigen Sie ein muskulöseres Werkzeug. Excel hat genau die Antwort: Solver. Excel Solver ist ein ausgeklügeltes Optimierungsprogramm, mit dem Sie Lösungen für komplexe Probleme finden können, die ansonsten eine mathematische Analyse auf hohem Niveau erfordern würden.

Excel-Solver verstehen

Solver verwendet wie Goal Seek eine iterative Methode, um seine Berechnungen durchzuführen. Die Verwendung von Iteration bedeutet, dass Excel Solver eine Lösung ausprobiert, die Ergebnisse analysiert, eine andere Lösung ausprobiert usw. Diese zyklische Iteration ist jedoch nicht nur Vermutungen von Solvers Seite. Das wäre albern. Nein, Excel Solver untersucht, wie sich die Ergebnisse mit jeder neuen Iteration ändern und kann durch einige ausgeklügelte mathematische Prozesse (die glücklicherweise im Hintergrund ablaufen und ignoriert werden können) normalerweise sagen, in welche Richtung es die Lösung angehen soll.

Die Vorteile des Excel Solvers

Ja, Goal Seek und Solver sind beide iterativ, aber das macht sie nicht gleich. Tatsächlich bringt Excel Solver eine Reihe von Vorteilen mit sich:

• Mit Excel Solver können Sie mehrere anpassbare Zellen angeben. Sie können insgesamt bis zu 200 einstellbare Zellen verwenden.
• Mit Excel Solver können Sie Einschränkungen für die anpassbaren Zellen einrichten. Sie können Solver beispielsweise anweisen, eine Lösung zu finden, die nicht nur den Gewinn maximiert, sondern auch bestimmte Bedingungen erfüllt, z. Diese Bedingungen werden als Beschränkungen der Lösung bezeichnet.
• Excel Solver sucht nicht nur ein gewünschtes Ergebnis (das „Ziel“ in Goal Seek), sondern auch das optimale. Wenn Sie beispielsweise nach einem optimalen Ergebnis suchen, können Sie eine Lösung finden, die maximal oder minimal möglich ist.
• Für komplexe Probleme kann Solver mehrere Lösungen generieren . Sie können diese verschiedenen Lösungen dann in verschiedenen Szenarien speichern.

Wann sollten Sie Excel Solver verwenden?

Okay, fangen wir gleich hier an: Excel Solver ist ein leistungsstarkes Tool, das die meisten Excel-Benutzer nicht benötigen. Es wäre beispielsweise übertrieben, Solver zu verwenden, um den Nettogewinn bei festen Umsatz- und Kostenzahlen zu berechnen. Viele Probleme erfordern jedoch nichts weniger als den Solver-Ansatz. Diese Probleme decken viele verschiedene Bereiche und Situationen ab, haben jedoch alle die folgenden Merkmale gemeinsam:

• Sie haben eine einzelne Zielzelle (auch die genannte Zielzelle ) , die eine Formel enthält , die Sie maximieren möchten, minimieren oder auf einen bestimmten Wert. Diese Formel könnte eine Berechnung wie die Gesamttransportkosten oder der Nettogewinn sein.
• Die Zielzellenformel enthält Verweise auf eine oder mehrere variable Zellen (auch als Unbekannte oder sich ändernde Zellen bezeichnet ). Solver passt diese Zellen an, um die optimale Lösung für die Zielzellenformel zu finden. Diese variablen Zellen können Elemente wie verkaufte Einheiten, Versandkosten oder Werbeausgaben enthalten.
• Optional gibt es eine oder mehrere Einschränkungszellen , die bestimmte Kriterien erfüllen müssen. Sie können beispielsweise verlangen, dass Werbung weniger als 10 Prozent der Gesamtausgaben ausmacht oder dass der Rabatt für Kunden zwischen 40 und 60 Prozent beträgt.

Die Abbildung unten zeigt beispielsweise ein Arbeitsblatt-Datenmodell, das für Excel Solver eingerichtet ist. Das Modell zeigt Umsatz (Preis mal verkaufte Einheiten) und Kosten für zwei Produkte, den von jedem Produkt erzeugten Gewinn und den Gesamtgewinn. Die hier zu beantwortende Frage lautet: Wie viele Einheiten jedes Produkts müssen verkauft werden, um einen Gesamtgewinn von 0 € zu erzielen? Dies wird in der Wirtschaft als Break-Even-Analyse bezeichnet.

Das Ziel dieses Datenmodells besteht darin, den Break-Even-Punkt zu finden (wo der Gesamtgewinn 0 USD beträgt).

Das klingt nach einer einfachen Goal Seek-Aufgabe, aber dieses Modell hat einen kniffligen Aspekt: ​​die variablen Kosten. Normalerweise sind die variablen Kosten eines Produkts seine Stückkosten mal der Anzahl der verkauften Einheiten. Wenn die Herstellung von Produkt A 10 US-Dollar kostet und Sie 10.000 Einheiten verkaufen, betragen die variablen Kosten für dieses Produkt 100.000 US-Dollar. In der Praxis werden solche Kosten jedoch häufig auf mehrere Produkte verteilt. Führen Sie beispielsweise eine gemeinsame Werbekampagne für zwei Produkte durch, werden diese Kosten von beiden Produkten getragen. Daher wird bei diesem Modell davon ausgegangen, dass die Kosten eines Produkts im Verhältnis zu den verkauften Einheiten des anderen stehen. Hier zum Beispiel die Formel zur Berechnung der Kosten der aufblasbaren Dartscheibe (Zelle B8):

=B7 * B4 – C4

Mit anderen Worten, die variablen Kosten für das Aufblasbare Dartboard reduzieren sich um einen Dollar für jede verkaufte Einheit des Dog Polisher. Die variablen Kosten des letzteren verwenden eine ähnliche Formel (in Zelle C8):

=C7 * C4 – B4

Da die variablen Kosten für mehrere Produkte gelten, ist dieses Datenmodell außerhalb dessen, was Goal Seek leisten kann, aber Solver ist der Herausforderung gewachsen. Hier sind die speziellen Zellen im Modell, die Solver verwendet:

• Die Zielzelle ist C14; der Gesamtgewinn und die Ziellösung für diese Formel ist 0 (d. h. der Break-Even-Point).
• Die Wechselzellen sind B4 und C4, die die Anzahl der verkauften Einheiten für jedes Produkt enthalten.
• Für Einschränkungen möchten Sie vielleicht hinzufügen, dass beide Produktgewinnzellen (B12 und C12) ebenfalls 0 sein sollten.

Laden des Excel Solver-Add-Ins

Ein Add-In ist eine Software, die Excel eine oder mehrere Funktionen hinzufügt. Durch die Installation von Add-Ins erhalten Sie zusätzliche Excel-Funktionen, die standardmäßig nicht im Menüband verfügbar sind. Die gebündelte Add-In-Software ist in Excel enthalten, wird jedoch nicht automatisch installiert, wenn Sie Excel installieren . Mehrere Add-Ins werden standardmäßig mit Excel geliefert, einschließlich Solver, mit dem Sie Optimierungsprobleme lösen können.

Sie installieren die gebündelten Add-Ins mithilfe des Dialogfelds Excel-Optionen; Sie finden sie im Abschnitt Add-Ins. Nach der Installation sind Add-Ins sofort verfügbar. Sie erscheinen normalerweise auf einer Registerkarte, die sich auf ihre Funktion bezieht. Solver wird beispielsweise auf der Registerkarte Daten angezeigt.

Gehen Sie wie folgt vor, um das Excel Solver-Add-In zu laden:

Wählen Sie Datei→ Optionen.
Das Dialogfeld Excel-Optionen wird angezeigt.

Wählen Sie Add-Ins.

Wählen Sie in der Liste Verwalten die Option Excel-Add-Ins und dann Los aus.
Excel zeigt das Dialogfeld Add-Ins an.

Aktivieren Sie das Kontrollkästchen Solver-Add-In

OK klicken.
Excel fügt der Gruppe Analysieren der Registerkarte Daten eine Solver-Schaltfläche hinzu.

Sehen Sie sich an, wie Sie den Excel-Solver verwenden .