Lieferoptimierung

Formulierung des Problems

Angenommen, das Unternehmen, in dem Sie arbeiten, hat drei Lager, von wo aus die Waren zu fünf Ihrer in ganz Moskau verstreuten Geschäfte gehen.

Jedes Geschäft ist in der Lage, eine bestimmte uns bekannte Menge an Waren zu verkaufen. Jedes der Lager hat eine begrenzte Kapazität. Die Aufgabe besteht darin, rational auszuwählen, von welchem ​​Lager zu welchen Filialen die Waren geliefert werden sollen, um die Gesamttransportkosten zu minimieren.

Bevor Sie mit der Optimierung beginnen, müssen Sie auf einem Excel-Blatt eine einfache Tabelle erstellen – unser mathematisches Modell, das die Situation beschreibt:

Es versteht sich, dass:

• Die hellgelbe Tabelle (C4:G6) beschreibt die Kosten für den Versand eines Artikels von jedem Lager zu jedem Geschäft.
• Violette Zellen (C15:G14) beschreiben die Menge an Waren, die jedes Geschäft zum Verkauf benötigt.
• Rote Zellen (J10:J13) zeigen die Kapazität jedes Lagers an – die maximale Menge an Waren, die das Lager aufnehmen kann.
• Gelbe (C13:G13) und blaue (H10:H13) Zellen sind die Zeilen- bzw. Spaltensummen für grüne Zellen.
• Die Gesamtversandkosten (J18) errechnen sich aus der Summe der Produkte der Warenanzahl und deren entsprechenden Versandkosten – zur Berechnung wird hier die Funktion verwendet SUMMENPRODUKT (SUMMENPRODUKT).
  

Somit reduziert sich unsere Aufgabe auf die Auswahl optimaler Werte grüner Zellen. Und damit der Gesamtbetrag für die Linie (blaue Zellen) die Kapazität des Lagers (rote Zellen) nicht überschreitet und gleichzeitig jedes Geschäft die Menge an Waren erhält, die es verkaufen muss (die Menge für jedes Geschäft in der gelbe Zellen sollten so nah wie möglich an den Anforderungen liegen – violette Zellen).

Lösung

In der Mathematik werden solche Probleme der Wahl der optimalen Ressourcenverteilung seit langem formuliert und beschrieben. Und natürlich werden seit langem Wege zu ihrer Lösung entwickelt, nicht durch stumpfe Aufzählung (die sehr lang ist), sondern in einer sehr kleinen Anzahl von Iterationen. Excel stellt dem Benutzer diese Funktionalität über ein Add-In zur Verfügung. Suchlösungen (Löser) aus der Registerkarte Datum (Datum):

Wenn auf der Registerkarte Datum Ihr Excel hat keinen solchen Befehl – ​​es ist in Ordnung – es bedeutet, dass das Add-In einfach noch nicht verbunden ist. Zum Aktivieren öffnen Reichen Sie dasUnd wählen Sie dann Parameter - Add-ons - Über uns (Optionen – Add-Ins – Gehe zu). Aktivieren Sie in dem sich öffnenden Fenster das Kontrollkästchen neben der Zeile, die wir benötigen Suchlösungen (Löser).

Lassen Sie uns das Add-On ausführen:

In diesem Fenster müssen Sie die folgenden Parameter einstellen:

• Zielfunktion optimieren (Setze TGeld Zelle) – Hier ist es notwendig, das endgültige Hauptziel unserer Optimierung anzugeben, dh das rosa Kästchen mit den Gesamtversandkosten (J18). Die Zielzelle kann minimiert (wenn es sich um Ausgaben handelt, wie in unserem Fall), maximiert (wenn es sich beispielsweise um Gewinn handelt) oder versuchen, sie auf einen bestimmten Wert zu bringen (z. B. genau in das zugewiesene Budget passen).
• Variablenzellen ändern (By Ändern Zellen) – hier geben wir die grünen Zellen (C10: G12) an, indem wir deren Werte variieren, wollen wir unser Ergebnis erzielen – die Mindestlieferkosten.
• Im Einklang mit Einschränkungen (Betreff zu Einschränkungen) – eine Liste von Restriktionen, die bei der Optimierung berücksichtigt werden müssen. Um Einschränkungen zur Liste hinzuzufügen, klicken Sie auf die Schaltfläche Speichern (Hinzufügen) und geben Sie die Bedingung in das erscheinende Fenster ein. In unserem Fall ist dies die Nachfragebeschränkung:

   

  und Begrenzung des maximalen Lagervolumens:

Zusätzlich zu den offensichtlichen Einschränkungen, die mit physikalischen Faktoren verbunden sind (Kapazität von Lagern und Transportmitteln, Budget- und Zeitbeschränkungen usw.), ist es manchmal notwendig, Einschränkungen „speziell für Excel“ hinzuzufügen. So kann Excel zum Beispiel ganz einfach eine „Optimierung“ der Lieferkosten für Sie arrangieren, indem es anbietet, Waren von den Filialen zurück ins Lager zu transportieren – die Kosten werden negativ, dh wir machen Gewinn! 🙂

Um dies zu verhindern, lassen Sie das Kontrollkästchen am besten aktiviert. Unbegrenzte Variablen nicht-negativ machen oder solche Momente sogar manchmal explizit in der Liste der Einschränkungen registrieren.

Nachdem Sie alle erforderlichen Parameter eingestellt haben, sollte das Fenster wie folgt aussehen:

In der Dropdown-Liste Wählen Sie eine Lösungsmethode aus, müssen Sie zusätzlich die geeignete mathematische Methode zum Lösen einer Auswahl von drei Optionen auswählen:

• Simplex-Methode ist eine einfache und schnelle Methode zur Lösung linearer Probleme, dh Probleme, bei denen die Ausgabe linear von der Eingabe abhängt.
• Allgemeine Herabgestufte-Gradienten-Methode (OGG) – für nichtlineare Probleme, bei denen komplexe nichtlineare Abhängigkeiten zwischen Eingabe- und Ausgabedaten bestehen (z. B. die Abhängigkeit des Umsatzes von den Werbekosten).
• Evolutionäre Suche nach einer Lösung – eine relativ neue Optimierungsmethode, die auf den Prinzipien der biologischen Evolution basiert (hallo Darwin). Diese Methode arbeitet um ein Vielfaches länger als die ersten beiden, kann aber fast jedes Problem lösen (nichtlinear, diskret).
  

Unsere Aufgabe ist eindeutig linear: 1 Stück geliefert – 40 Rubel ausgegeben, 2 Stück geliefert – 80 Rubel ausgegeben. usw., daher ist die Simplex-Methode die beste Wahl.

Nachdem Sie nun die Daten für die Berechnung eingegeben haben, drücken Sie die Taste Finde eine Lösung (Lösen)um die Optimierung zu starten. In schweren Fällen mit vielen sich ändernden Zellen und Einschränkungen kann das Finden einer Lösung lange dauern (insbesondere mit der evolutionären Methode), aber unsere Aufgabe für Excel wird kein Problem sein – in wenigen Augenblicken werden wir die folgenden Ergebnisse erhalten :

Achten Sie darauf, wie interessant die Liefermengen auf die Filialen verteilt wurden, ohne die Kapazität unserer Lager zu überschreiten und alle Wünsche nach der erforderlichen Anzahl von Waren für jede Filiale zu erfüllen.

Wenn die gefundene Lösung zu uns passt, können wir sie speichern, oder auf die ursprünglichen Werte zurücksetzen und es mit anderen Parametern erneut versuchen. Sie können die ausgewählte Kombination von Parametern auch speichern als Szenario. Auf Wunsch des Benutzers kann Excel drei Typen erstellen Meldungen zur zu lösenden Aufgabe auf separaten Blättern: einen Bericht über die Ergebnisse, einen Bericht über die mathematische Stabilität der Lösung und einen Bericht über die Grenzen (Einschränkungen) der Lösung, die jedoch in den meisten Fällen nur für Spezialisten von Interesse sind .

Es gibt jedoch Situationen, in denen Excel keine passende Lösung findet. Es ist möglich, einen solchen Fall zu simulieren, wenn wir in unserem Beispiel den Bedarf der Lager in einer Höhe angeben, die größer ist als die Gesamtkapazität der Lager. Wenn Sie dann eine Optimierung durchführen, versucht Excel, der Lösung so nahe wie möglich zu kommen, und zeigt dann eine Meldung an, dass die Lösung nicht gefunden werden kann. Trotzdem haben wir auch in diesem Fall viele nützliche Informationen – insbesondere können wir die „Schwachstellen“ unserer Geschäftsprozesse erkennen und die Bereiche mit Verbesserungspotenzial erkennen.

Das betrachtete Beispiel ist natürlich relativ einfach, lässt sich aber leicht skalieren, um viel komplexere Probleme zu lösen. Zum Beispiel:

• Optimierung der Verteilung der Finanzmittel nach Ausgabenposten im Geschäftsplan oder Budget des Projekts. Die Einschränkungen sind in diesem Fall die Höhe der Finanzierung und der Zeitpunkt des Projekts, und das Ziel der Optimierung besteht darin, den Gewinn zu maximieren und die Projektkosten zu minimieren.
• Optimierung der Mitarbeitereinsatzplanung um den Lohnfonds des Unternehmens zu minimieren. Einschränkungen sind in diesem Fall die Wünsche jedes Mitarbeiters gemäß dem Beschäftigungsplan und den Anforderungen der Besetzungstabelle.
• Optimierung von Investitionen Investitionen – die Notwendigkeit, Mittel korrekt auf mehrere Banken, Wertpapiere oder Aktien von Unternehmen zu verteilen, um wiederum Gewinne zu maximieren oder (falls wichtiger) Risiken zu minimieren.

Auf jeden Fall Nachschub Suchlösungen (Löser) ist ein sehr leistungsfähiges und schönes Excel-Tool und verdient Ihre Aufmerksamkeit, da es in vielen schwierigen Situationen helfen kann, denen Sie in der modernen Geschäftswelt begegnen müssen.