Die optimierten Kabelkosten

Die Kopfnuss für Verlegekosten

Ein Städtchen liegt an einem Fluss, der einen Kilometer breit ist. Der Gemeinderat beschliesst, auf dem zwei Kilometer flussabwärts auf der anderen Fluss-Seite gelegenen Sportgelände eine Flutlichtanlage zu installieren. Die Umspannstation, von der aus das notwendige Starkstrom-Kabel durch den Fluss und bis zum Sportgelände gezogen werden soll, liegt in der Stadt direkt am Fluss.

Die Kabelverlegung auf dem Lande kostet 1'100 € pro Meter, diejenige unter Wasser 1'300 € pro Meter. Der Stadtrat beauftragt seinen Kämmerer, eine kostenminimale Verlegungsvariante zu erarbeiten.

Der Kämmerer ist pfiffig und sucht verständlicherweise die kürzeste Strecke, indem er den alten Pythagoras zu Rate zieht:
a2 + b2 = c2 und daraus die Quadratwurzel ergibt die kürzeste Strecke. Stolz legt er dem Stadtrat bei der nächsten Sitzung folgende Rechnung vor:

"1 km x 1 km + 2 km x 2 km = 5 km und daraus die Qadratwurzel ergibt 2,2361 km. Das Kabel wird also nur im Wasser verlegt und kostet somit 2'906'930 €."

Ein Mitglied des Stadtrates behauptet jedoch, es gäbe eine kostengünstigere Lösung, die er mit dem Solver in Excel gefunden habe.

Hat er recht und wenn ja, wie sieht diese Lösung aus?

Das Mitglied des Stadtrates hat recht. Er hat eine Skizze gemacht, damit auch jeder sich von seiner Genialität überzeugen kann.

Mit x hat er die Strecke bezeichnet, bei der auf Land kein Kabel verlegt wird. Dann ist die Strecke S1, auf der auf Land das Kabel verlegt wird 2000 - x. Nun lässt sich mit Hilfe von Pythagoras die Strecke S2 berechnen, die unter Wasser liegt, und zwar wie folgt:

S2 = Quadratwurzel(x2 + 10002) (zur Erinnerung: 1000 ist die Breite des Flusses in m)

Die insgesamt zu verlegende Kabelstrecke S beträgt also S1 + S2
S = (2000 - x) + Quadratwurzel(x2 + 10002)

Die Strecken S1 und S2 sind mit den jeweiligen Verlegekosten zu multiplizieren, um die Gesamtkosten K zu erhalten:

K = (2000 - x)*1100 + ( Quadratwurzel(x2 + 10002))*1300.

Die Gesamtkosten K sind zu minimieren, und zwar mit dem in Excel enthaltenen Optimierungstool Solver.

Dies sieht in Excel so aus:

 Ausgefüllt mit Zahlen sieht das Werk jetzt so aus:

Damit der Solver rechnen kann, benötigt er folgende Angaben:

Die Zielzelle, in der er das Ergebnis berechnet. In unserem Fall ist das die Zelle B4, deren Wert zu minimieren ist, denn der Stadtrat möchte eine kostenminimale Lösung haben. Der Wert in dieser Zelle ergibt sich aus der dahinterliegenden Formel und den bereits eingegebenen Basisdaten.
Auserdem muss der Solver wissen, welche Zelle er verändern darf, um auf das gesuchte Ergebnis zu kommen. Das ist in unserem Falle die Zelle B3.
Zusätzlich muss die Nebenbedingung 'Gesuchte Strecke >= 0' eingegeben werden, da der Solver sonst mit negativen Werten arbeiten könnte.

In den Solver eingegeben sieht das so aus:

Die Zelle B3 wurde in Excel mit einem Namen versehen (Ges_Strecke = gesuchte Strecke).

Jetzt braucht nur noch der Knopf Lösen angeklickt zu werden und in wenigen Sekunden präsentiert der Solver das gesuchte Ergebnis:

Kosten-/ Nutzensbetrachtung

Auf 'hochnotpeinliches' Befragen durch den Stadtdirektor gibt der clevere Stadratrat zu, dass er sich bei einem guten Bekannten am Institut für angewandte Optimierung Rat geholt hat, und dass das ganze Modell für 500 € zu haben war. Der Nutzen seiner Variante (immerhin 14'106,68 €) überstieg seine Ausgaben um das stolze Achtundzwanzigfache. Der Stadtdirektor gratuliert im Namen des gesamten Stadtrates.

Wenn Sie sich die Arbeit des Eingebens sparen wollen, können Sie sich die Datei Fluss.exe (selbstentpackend, 24 k) herunterladen.

Wenn Sie Fragen zur praktischen Anwendung des Solvers haben, helfen wir Ihnen gerne KOSTENLOS weiter. Nehmen Sie Kontakt mit uns auf und klicken Sie dazu bitte hier.