ich stehe gerade vor einer Problemstellung, bei der ich nicht weiterkomme: Gegeben sind Monatliche Werte von 2016-2018 zur 1) Budgetbereitstellunng und zur 2) Budgetverwendung. Die Budgetbereitstellung erfolgt einmalig, während die Budgetverwendung über mehrere Monate hinweg dokumentiert sein kann.
Ziel des statistischen Modells ist es, bei einer neuen Budgetfreigabe auf Basis der vorhandenen Daten voraussagen zu können, wie viele Monate es durchschnittlich dauert, bis 25%, 50%, 75% und 95% des Budgets verbraucht sind.
In Excel ist der Datensatz folgendermaßen aufgebaut: In den Zeilen sind die jeweiligen Investitionen (mit ID) und in den Spalten sind alle Monate mit den jeweiligen Werten für 1) Budgetbereitstellung (36 Spalten) und anschließend für die 2) Budgetverwendung (nochmal 36 Spalten) aufgetragen.
Ich weiß, die Problemstellung ist alles andere als Trivial, aber vielleicht haben die Excel-Cracks unter euch ein paar Ideen, wie ich hier ansetzen kann?
ich gehe davon aus das die Bereitstellung im Januar erfolgt.
Dann könntest du den durchschnitt für den Januar der letzten Jahre bilden. Dies sind dann die erwarteten Ausgaben.
Also z.b. wurden in den letzten 2 Jahren im Januar X EUR verwendet.
Dann könnte man sagen das statistisch im Januar X Euro verwendet wurden. Und dann dies in Prozent ausrechnen.
Für die anderen Monate genauso machen und kumuliert berechnen.
Gruß
Die einfachste Art an korrekte Informationen zu gelangen ist, etwas Falsches in ein Usenet-Forum zu posten und auf die Korrekturen zu warten. [i]Matthew Austern – Software-Entwickler bei Google[/i]
eventuell rechnest Du mal die Mittelwerte der Ausgaben - ich habe sie hier mal kumuliert, man könnte sie auch einzeln rechnen und dann die Prozente kumuliert ausgeben.
Dann siehst Du erst mal, wo Du nach jedem Monat stehst. Ob Du die 25, 50 usw. % dann taggenau per Berechnung auf den Punkt bringst - 50% würden bei mir nun im Januar liegen - oder das schätzt, wäre die nächste Frage ...
Arbeitsblatt mit dem Namen 'Tabelle1'
A
B
C
D
E
F
1
2016
2017
2018
Mittelwerte
2
Januar
1
1,5
1,25
1,25
55,56%
3
Februar
0,8
1
1,2
2,25
100,00%
4
5
Summe
1,8
2,5
2,45
2,25
Zelle
Formel
E2
=MITTELWERT(B2:D2)
F2
=E2/$E$5
E3
=MITTELWERT(B3:D3)+E2
F3
=E3/$E$5
B5
=SUMME(B2:B3)
C5
=SUMME(C2:C3)
D5
=SUMME(D2:D3)
E5
=MITTELWERT(B5:D5)
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Bisher bin ich nur so weit gekommen, die Budgetverwendung kumuliert darzustellen - damit lassen sich (manuell) Graphen zur Budgetverwendung erstellen.
Um aber auch die Zeitpunkte der Budgetbereitsstellung im Graph zu integrieren, müsste man alle Spalten der Monate von 2016-2018 zusammenfassen, was ich nicht schaffe.
Außerdem sind die Zeitpunkte der Budgetbereitstellung unterschiedlich, dh. dies ist noch eine zusätzliche Schwierigkeit...
Wenn ihr mir weiterhelfen könntet, ware ich euch sehr dankbar!
29.08.2018, 17:11 (Dieser Beitrag wurde zuletzt bearbeitet: 29.08.2018, 17:11 von schauan.)
Hallo Leon,
warum und wie willst Du die Monate zusammenfassen?
Wenn Du Dir die Formel =MITTELWERT(B2:D2) anschaust, siehst Du, dass ich hier den Mittelwert über die 3 Januare genommen habe. Was hast Du denn an der Stelle vor?
Hast Du es denn nicht mal so versucht, wie ich es beschrieben habe? Wenn es Dir nicht gefällt, dass die Monate untereinander stehen, kannst Du sie auch waagerecht platzieren …
Übrigens sind die Einzelnen Werte doch als Beispiel für für die Lösung nicht relevant, wenn es nur um die Monatssummen geht.
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
1.) in Spalte BV gibst du die Summe der bereitgestellten Mittel an ABER mit welchen Bezug den?
Du beziehst dich auf eine "Nummer" aus der Spalte A. Du willst aber hier die Summe für den Monat 01.2016 und nicht die Summe der Nummer (was auch immer die Nummer angeben soll)
2.) Was für mich komplett unverständlich ist warum man einmal 01.2016 schreibt und dann ab Spalte AL 01.20162? Das Jahr 20162 ist noch weit in der Zukunft und werden wir nicht mehr erleben.
3.) Die Formel für das Kumulieren ist falsch. Aber das eigentliche Problem ist, was willst du im Monat 02.201622 denn kumulieren? Der Monat hat eine Summe und die Summe für den Monat muss nicht kumuliert werden.
Du willst ja für deine Frage aus den Thema die Monate kumulieren. Da müssen die Zahlungen (oder was auch immer) innerhalb des Monats nicht kumuliert werden sonder summiert.
4.) Da du das Datum von links nach Rechts machst, wird meiner Meinung nach, die Tabelle sehr unübersichtlich was ich auch in deinen Formeln sehe.
Daher bitte ich dich erstmal deine Daten ordentlich aufzubereiten und dann diese bei Problemen uns nochmal zur Verfügung zu stellen.
Ich stelle dir mir mal meine Beispieldatei / Überlegung zur Verfügung.
EDIT:
Was noch sehr Wichtig wäre ist die Überlegung ob man hier noch eine Wichtung mit einbaut. Der Januar 2018 sollte mehr Wichtung haben als der Monat Januar 2016.
Aus Betriebswirtschaftlicher Sicht wäre dies für eine Prognose schon sehr Wichtig. Aber ohne ordentliche Datengrundlage ist mir dies zu aufwendig.
Gruß
Die einfachste Art an korrekte Informationen zu gelangen ist, etwas Falsches in ein Usenet-Forum zu posten und auf die Korrekturen zu warten. [i]Matthew Austern – Software-Entwickler bei Google[/i]
Zu 1) Die Nummern stehen für die einzelnen Anschaffungen, wofür Budget bereitgestellt wird. Um zu wissen wie viel Budget insgesamt für die jeweilige Anschaffung bereitgestellt wurde, habe ich die Summe gebildet. Dasselbe habe ich für die Budgetverwendung gemacht. Daraus lässt sich die Differenz berechnen (Differenz = 0 heißt, dass alles Budget aufgebraucht wurde.)
Zu 2) In der Excel Tabelle kann man die Spalten nicht gleich benennen, Excel hängt hier automatisch eine "2" an. Natürlich bezieht sich alles auf die selben Monate/Jahre.
Zu 3) Mein Hintergedanke zum Kumulieren war herauszufinden, wie sich die Budgetverwendung graphisch darstellen lässt. Dies ist nur duch Kumulation sinnvoll.
Zu 4) Kannst du mir den einen Tipp geben, wie ich die Budgetbereitstellung und Verwendung innerhalb einer Tabelle direkt "untereinader" für jede einzelne Anschaffung aufstellen kann? Dann lässt sich auch die Auswertung mit Pivot-Charts leichter machen. (Siehe Bilder im Anhang, diese beiden Graphen möchte ich gerne in einen Graphen zusammenfassen, was aktuell nicht geht...)
Noch eine Ergänzung: Neben den Spalten zur Nummer der Anschaffung existieren noch weitere Spalten z.B. zum Ort/Region der Anschaffung etc. Danach soll bei der Auswertung auch gefiltert werden.
Das schwierige bei der Aufgabe ist es dann noch, den zeitlichen Abstand zwischen der ersten Budgetbereitstellung und der Anschließenden Verwendung automatisch zu ermitteln, denn Budget wird nicht immer im Januar 2016 bereitgestellt sondern zeitlich unabhängig.
Am Ende will ich wissen wie lange es durchschnittlich dauert, bis 25%, 50%, 75% und 95% des Budgets meiner Anschaffung (entspricht "Nummer") aufgebraucht sind.
Ein Vorgehen in Worten beschrieben reicht mir auch schon, du sollst dich auf keinen Fall selbst durch den zugegebenermaßen unübersichtlichen Tabellendschungel kämpfen müssen!
zu 1.) Du bildest die Summe einer Nummer (über alle Monate und Jahre hinweg) was für mich unlogisch ist. Du stellst doch nicht mit der gleich Nummer über 3 Jahre das gleiche Budget zur Verfügung.
zu 2.) Das ist falsch. Natürlich kannst du die Spalten gleich benennen. Warum sollte das denn nicht gehen?
zu 3.) Das ist richtig. Aber schau mal bitte was du kumulierst. Du kumulierst die Nummer einer einzelnen Anschaffung über alle 3 Jahre hinweg. Das ist, meiner Meinung nach, falsch.
zu 4.) Was ich noch nicht verstanden habe ist, was du im Bezug auf dein Ziel mit der Anschaffungsnummer willst.
Mein Tipp:
1.) Erstelle dir einen ordentlichen Datenbestand mit dem man einfach arbeiten kann.
-> Mehrere Tabellenblätter. Das eine Tabellenblatt, nennen wir es mal Datenbestand, sollte so aufgebaut sein:
Vorgangsnummer | Datum | andere weitere Attribute | usw.
2.) Ein Tabellenblatt für die Analyse. Dort packst du wie in meiner Beispieldatei die Monate untereinander und in den Spalten die Jahre.
Du kannst dann mit Summewenn oder Summewenns alles zusammenfassen was du für deine Analyse brauchst ohne im Datenbestand was machen zu müssen.
Zitat:wie ich die Budgetbereitstellung und Verwendung innerhalb einer Tabelle direkt "untereinader" für jede einzelne Anschaffung aufstellen kann?
Warum willst du das machen? Dein Ziel war es doch die Monate zu vergleichen und nicht die einzelnen Anschaffungen.
Nach deinem Muster würde es auch heißen das die Anschaffungsnummer 342369 über 3 Jahre den gleichen Ort, Region hat. Ist das korrekt?
Gruß
Die einfachste Art an korrekte Informationen zu gelangen ist, etwas Falsches in ein Usenet-Forum zu posten und auf die Korrekturen zu warten. [i]Matthew Austern – Software-Entwickler bei Google[/i]
