Woche 11 | Session 3: GFA Hands-On — Excel Solver Formeln & AnyLogistix (Multi-SKU)
Kurs: Supply Chain Digitalisierung — Modul 4: Digitale Infrastruktur
Session-Agenda
Abschnitt betitelt „Session-Agenda“1. Fallstudienerinnerung
Abschnitt betitelt „1. Fallstudienerinnerung“| Parameter | Wert |
|---|---|
| 4 Märkte | Pune (M1), Mumbai (M2), Ahmedabad (M3), Surat (M4) |
| Bekannt | Jahresbedarf + Breiten-/Längengrad pro Markt |
| Unbekannt | xDC (Breitengrad), yDC (Längengrad) des neuen DCs |
| Ziel | Minimiere Z = Σ (Nachfrage_i × Distanz_i × 1$/km/Einheit) |
| Ausgangs-DC | Breitengrad = 20, Längengrad = 72 → Z = 12,44,89,119 (nicht optimal) |
2. Excel — Formelaufbau (Sphärische Distanz)
Abschnitt betitelt „2. Excel — Formelaufbau (Sphärische Distanz)“
Die Distanzformel erfordert 5 Zwischenberechnungen bevor die km-Distanz erreicht wird.
Schlüsselzellreferenzen: B7 = xDC, B8 = yDC, D2 = xᵢ (Breitengrad Markt i), E2 = yᵢ (Längengrad Markt i).
| Schritt | Zelle / Variable | Excel-Formel | Erklärung |
|---|---|---|---|
| 1 | d_long | =BOGENMASS(B8 − E2) | Längengradunterschied in Bogenmaß |
| 2 | d_lat | =BOGENMASS(B7 − D2) | Breitengradunterschied in Bogenmaß |
| 3 | A | =SIN(d_lat/2)^2 + COS(BOGENMASS(D2)) * COS(BOGENMASS(B7)) * SIN(d_long/2)^2 | Haversine-Zwischenterm |
| 4 | C | =2 * ARCTAN2(WURZEL(1−A), WURZEL(A)) | Zentralwinkel in Bogenmaß |
| 5 | Distanz | =6371 * C | Distanz in km = Erdradius (6371 km) × Zentralwinkel |
| 6 | Gesamtkosten | =SUMPRODUKT(Distanzen, Nachfragen, Kosten) | Eine Formel gibt Gesamtkosten über alle 4 Märkte |
Schritt 1 — d_long = BOGENMASS(B8 − E2)Schritt 2 — d_lat = BOGENMASS(B7 − D2)Schritt 3 — A = SIN(d_lat/2)^2 + COS(BOGENMASS(D2)) * COS(BOGENMASS(B7)) * SIN(d_long/2)^2Schritt 4 — C = 2 * ARCTAN2(WURZEL(1−A), WURZEL(A))Schritt 5 — Distanz = 6371 * C [km, berücksichtigt Erdkrümmung]Schritt 6 — Gesamtkosten = SUMPRODUKT(Distanzen, Nachfragen, Kosten)B7 und B8 bleiben feste Referenzen — sie sind die Entscheidungsvariablen.
3. Excel Solver — Einstellungen & Ausgabe
Abschnitt betitelt „3. Excel Solver — Einstellungen & Ausgabe“
| Solver-Feld | Wert / Einstellung |
|---|---|
| Zielzelle setzen | Zelle B9 (Gesamtkosten) |
| Auf | Min (Minimieren) |
| Veränderbare Variablenzellen | B7:B8 (Breiten- und Längengrad des DCs) |
| Nebenbedingungen | Keine — DC kann überall platziert werden |
| Lösungsmethode | GRG Nichtlinear — sphärische Formel ist nichtlinear |
| Optimale Ausgabe | B7 = 19.09, B8 = 72.87 → Mumbai |
| Minimierte Kosten | ₹9,73,92,135 (vs ₹12,44,89,119 bei Startwert 20, 72) |
4. Erweitertes Szenario — Mehrere SKUs & Tagesbedarf
Abschnitt betitelt „4. Erweitertes Szenario — Mehrere SKUs & Tagesbedarf“| Szenario | Bedarfseinträge |
|---|---|
| Session 2 (Basis) | 1 Produkt × 4 Märkte = 4 Einträge (jährlich) |
| Session 3 (Erweitert) | 4 SKUs × 4 Märkte = 16 Einträge (Tagesbedarf) |
| Echte Unternehmen | 20–100 SKUs × tausende Märkte → Excel nicht geeignet |
Excel-Beschränkung: kann LP/NLP-Modelle mit mehr als ca. 200 Entscheidungsvariablen nicht effizient lösen. Lösung: AnyLogistix verwenden.
5. AnyLogistix — GFA Schritt für Schritt (Hands-On)
Abschnitt betitelt „5. AnyLogistix — GFA Schritt für Schritt (Hands-On)“
- Herunterladen & Installieren: anylogic.com → Academic-Tab → ALX Educational Toolkit → AnyLogistix Personal Learning Edition (PLE) herunterladen — kostenlos für Studenten.
- GFA-Modul öffnen: AnyLogistix starten → ‘Green Field Analysis’ Modul auswählen.
- Kundendaten eingeben: 4 Kunden hinzufügen: Pune, Mumbai, Ahmedabad, Surat. Breiten-/Längengrad manuell eingeben ODER Stadtname eintippen → automatisches Ausfüllen der Koordinaten aus Datenbank.
- Bedarfsdaten eingeben: Für jeden Kunden Tagesbedarf pro SKU eingeben. 4 SKUs × 4 Märkte = 16 Einträge (z.B. Pune SKU1 = 130 Einheiten/Tag).
- Produktdaten eingeben: Produkte definieren: SKU1, SKU2, SKU3, SKU4 mit Einheitentyp.
- GFA-Experiment ausführen: Auf Ausführen klicken → Optimierer führt das sphärisch-gewichtete Nachfragemodell automatisch aus → Ergebnis in Sekunden.
- Ausgabe lesen: Optimaler DC: Breitengrad 19.096, Längengrad 72.877 (Mumbai). Karte zeigt DC-Standort mit Verbindungen zu allen 4 Kunden.
- Flüsse erkunden: Flusstabelle ansehen: DC → Ahmedabad, SKU1: 43.800 Einheiten, 437.988 km Distanz, Flusskosten. Alle 16 SKU-Markt-Flüsse sichtbar.
6. Excel vs. AnyLogistix — Ausgabevergleich
Abschnitt betitelt „6. Excel vs. AnyLogistix — Ausgabevergleich“
| Ausgabe / Funktion | Excel Solver | AnyLogistix |
|---|---|---|
| Optimaler Breitengrad | 19.09 | 19.096 |
| Optimaler Längengrad | 72.87 | 72.877 |
| Standort | Mumbai | Mumbai |
| Kartenvisualisierung | Nein (nur Zellwerte) | Ja — auf geografischer Karte |
| Flussaufschlüsselung | Nein | Ja — pro SKU pro Route |
| Multi-SKU-Unterstützung | Begrenzt | Ja — 4+ SKUs |
| Skalierung möglich | ~4 Märkte | Tausende von Märkten |
Zusammenfassung der Session
Abschnitt betitelt „Zusammenfassung der Session“- Excel-Formelkette: d_long → d_lat → A → C → Distanz (= 6371 × C) → SUMPRODUKT für Gesamtkosten.
- Schlüsselzellen: B7 = xDC, B8 = yDC (Entscheidungsvariablen). B9 = Gesamtkosten (Ziel).
- Solver: B9 minimieren, B7:B8 ändern, GRG Nichtlinear, keine Nebenbedingungen → optimal: 19.09, 72.87 (Mumbai).
- Erweiterter Fall: 4 SKUs × 4 Märkte = 16 Bedarfseinträge, Tagesbedarf — Excel zu begrenzt → AnyLogistix benötigt.
- Beide Ausgaben übereinstimmen: DC optimaler Standort = Breitengrad 19.096, Längengrad 72.877 = Mumbai.