lehrkraefte:blc:informatik:glf4-20:simulation:tabellenkalkulation-ueberbuchung [https://fginfo.ksbg.ch Fachgruppe Informatik der KSBG]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Überbuchung ======
Fluggesellschaften überbuchen Ihre Flüge, d.h. Sie verkaufen möglicherweise mehr Sitzplätze als überhaupt vorhanden. Der Grund ist, dass Flugpassagiere mit einer gewissen Wahrscheinlichkeit aus irgendwelchen Gründen gar nicht zum Flug erscheinen. Sollten trotzdem zu viele Leute erscheinen, werden Freiwillige gesucht (oft auch mit Entschädigungen) die dann auf andere Flüge umbuchen.

==== Annahmen ====
  * Ein Flugpassagier erscheint nicht zum Flug mit Wahrscheinlichkeit $p=0.05$.
  * Wir betrachten ein Flugzeug mit $n=300$ Plätzen.
==== Fragestellung ====
  * Wie viele Tickets sollen verkauft werden, damit mit 99%-iger Wahrscheinlichkeit alle erscheinenden Passagiere auch einen Sitzplatz haben?

===== Vorbereitung und Layout der Arbeitsmappe =====
  * Die Simulation eines Flugs wird in einer Spalte stattfinden (die dann kopiert werden kann).
  * Die grün markierten Zellen enthalten die Parameter und sind entsprechend benannt, damit diese in den Formeln verwendet werden können.

{{:lehrkraefte:blc:informatik:glf4-20:simulation:pasted:20210416-100419.png}}


===== Simulation eines Flugs =====
  * Ob ein Passagier rechtzeitig am Gate erscheint (1) oder nicht (0) kann mit folgender Formel ermittelt werden: ''=IF(RAND()<pshow,1,0)''. Sie müssen diese allerdings noch auf Deutsch übersetzen.
  * Diese Formel wird ca. 350 mal nach unten kopiert (für 350 potentielle Passagiere).
  * Die Anzahl tatsächlich erscheinender Passagiere ist die Summe der ersten ''tickets'' Zellen. Die Summe über einen variablen Bereich kann mit der Formel ''=SUM(OFFSET(C8,0,0,tickets,1))'' berechnet werden. Wieder Übersetzung auf Deutsch nötig. Dokumentieren Sie die Parameter der ''OFFSET''-Funktion.
  * Die Zelle OK soll 1 sein, wenn der alle erscheinenden Passagiere befördert werden können, sonst 0 (wenn Passagiere abgewiesen werden müssen).
  * Lassen die die Arbeitsmappe mehrmals durch Drücken der F9-Taste neu berechnen, "spielen" Sie mit den Parametern.


===== Simulation mehrerer Flüge =====
  * Kopieren Sie die 3. Spalte "einige Male" (zwischen 50 und 100) nach rechts.
  * Berechnen Sie dann den Durchschnitt der 6. Zeile (d.h. wie viel mal sämtliche erscheinende Passagiere befördert werden konnten).
  * Drücken Sie wieder mehrmals die F9-Taste, um die Arbeitsmappe neu zu berechnen. Versuchen Sie damit zu erraten, bei wievielen verkauften Tickets das Ziel von 99% Flügen ohne Abweisung möglich ist.

{{:lehrkraefte:blc:informatik:glf4-20:simulation:pasted:20210416-102056.png}}



===== Probleme mit der Simulation / mathematischer Ansatz =====
  * Die Simulation ist recht ungenau, man müsste eher 1000 oder gar mehr Spalten haben, um halbwegs aussagekräftige Resultate zu erhalten.
  * Die Anzahl erscheinender Passagiere folgt einer **Binomialverteilung** mit den Parametern $n$ (tickets) und $p=0.95$ (pshow).
  * Es ist möglich, solche Zufallszahlen in Excel zu generieren: ''=BINOM.INV(tickets,pshow,RAND())''  (Übersetzung auf Deutsch nötig).
  * Berechnen Sie in der ersten Spalte einige Tausend dieser Zufallswerte und berechnen Sie dann die Wahrscheinlichkeit, dass keine Passagiere abgewiesen werden müssen.


===== Mathematische Lösung =====
  * Die Wahrscheinlichkeit, dass eine binomialverteilte Zufallsvariable einen bestimmten Wert überschreitet kann in Excel berechnet werden: ''=BINOM.DIST(plaetze,tickets,pshow,1)''
  * Anstatt der Zelle ''tickets'' erzeugen Sie eine Spalte mit Werten von 300 bis 330.
  * In der Spalte daneben, berechnen Sie die Wahrscheinlichkeiten, dass der Flug überbucht ist.
  * Plotten Sie am Schluss die Wahrscheinlichkeiten, dass kein Passagier abgewiesen werden muss.

{{:lehrkraefte:blc:informatik:glf4-20:simulation:pasted:20210416-104722.png}}

===== Gewinnmaximierende Lösung (Expert) =====
  * Wir nehmen grosszügig an, die Kosten einen Passagier abzuweisen belaufen sich auf das 10-fache eines Ticket-Preises (darin ist z.B. neben der Entschädigung und Aufwand für die Umbuchung auch der Reputationsverlust eingerechnet).
  * Wie weit kann dann ein Flugzeug mit 300 Plätzen überbucht werden, um die Einnahmen zu maximieren (in Tickets gerechnet).
  * Dazu müssen wir ausrechnen, wie viele Passagiere im Durchschnitt abgewiesen werden, bei einer gegebenen Anzahl verkaufter Tickets.
    * Sei $X ~ \text{Bin}(n,p)$ eine binomialverteilte Zufallvariable. Sei $m$ die Anzahl verfügbarer Plätze, $t$ die Anzahl verkaufter Tickets.
    * Die Zufallsvariable $Y$ entspricht der Anzahl abgewiesener Personen und ist $0$ wenn $X\leq m$ und $X-m$ sonst.
    * $P(Y=0) = P(X\leq m)$ und $P(Y=k) = P(X=k+m)$ für $k\geq 1$.
    * Damit ist der Erwartungswert $$E(Y) = \sum_{k=1}^{t-m} k \cdot P(Y=k) =  \sum_{k=1}^{t-m} k \cdot P(X=m+k).$$
  * Für alle Werte von $t$ (tickets) und alle Werte von $k$ (zu viel erscheinende Passagiere) berechnen Sie $k \cdot P(X=m+k)$ und summieren Sie über $k$, um den Erwartungswert zu erhalten.
  * Berechnen Sie dann die Einnahmen (in Anzahl Tickets) und bestimmen Sie die optimale Anzahl.
  * Verändern Sie dann die Kosten einen Abweisung und betrachten Sie die Wahrscheinlichkeit einer Abweisung beim Optimum. Fällt Ihnen ein Zusammenhang auf? Können Sie diesen erklären?
  * Link zur [[https://fginfo.ksbg.ch/~ivo/videos/informatik/excel/tabellenkalkulation-simulation-ueberbuchung-expert.mp4|Videoanleitung]] und auch auf [[https://web.microsoftstream.com/video/a9073adf-b197-4ecf-b53a-c01474098779|Stream]].