lehrkraefte:blc:informatik:glf4-20:simulation:tabellenkalkulation-ueberbuchung

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
lehrkraefte:blc:informatik:glf4-20:simulation:tabellenkalkulation-ueberbuchung [2021/04/16 10:37]
Ivo Blöchliger [Simulation mehrerer Flüge]
lehrkraefte:blc:informatik:glf4-20:simulation:tabellenkalkulation-ueberbuchung [2021/04/26 20:19] (current)
Ivo Blöchliger [Probleme mit der Simulation / mathematischer Ansatz]
Line 19: Line 19:
   * Diese Formel wird ca. 350 mal nach unten kopiert (für 350 potentielle Passagiere).   * 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 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.
-  * Lassen die die Arbeitsmappe mehrmals durch Drücken der F9-Taste neu berechnen.+  * 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 ===== ===== Simulation mehrerer Flüge =====
-  * Kopieren Sie die 3 Spalte "einige Male" (zwischen 50 und 100) nach rechts.+  * Kopieren Sie die 3Spalte "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).   * 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.   * 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.
Line 34: Line 35:
   * Die Simulation ist recht ungenau, man müsste eher 1000 oder gar mehr Spalten haben, um halbwegs aussagekräftige Resultate zu erhalten.   * 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).   * 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())''+  * 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.   * 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]].
  
  • lehrkraefte/blc/informatik/glf4-20/simulation/tabellenkalkulation-ueberbuchung.1618562268.txt.gz
  • Last modified: 2021/04/16 10:37
  • by Ivo Blöchliger