Microsoft Excel

Herbers Excel/VBA-Archiv

Mittelabflussplanung

Betrifft: Mittelabflussplanung von: Ricarda
Geschrieben am: 05.11.2020 16:27:42

Hallo zusammen,

Anbei die Excel zu folgendem Problem: https://www.herber.de/bbs/user/141341.xlsx.

Ich stehe vor einem für mich unlösbarem Problem. Ich habe eine Liste, die einen Zeitraum mit Beginn und Ende enthält. Dazu gehört eine gewissen Geldsumme, die über die Zeit entsteht.

Ich würde diese Summe gerne auf 10 bzw. 11 Sequenzen aufteilen. Wenn sich der Beginn oder das Ende verändert, müssen sich die Sequenzen automatisch (zeitlich) mitanpassen. Diesen Sequenzen sind Prozentsätze zugeordnet, die immer gleich bleiben. Das heißt in einer Sequenz wird ein bestimmter Satz der Gesamtsumme verbraucht. Gleichzeitig möchte ich diese Sequenzen mit dem Zeitraum verbinden.
Das ist deswegen wichtig, weil ich es gerne so hätte, dass das Endresultat ist, dass nur den Jahreszahlen also 2021,2022 ... die Geldsumme zu geordnet werden, die in diesem Jahr anfallen. Diese resultieren direkt aus den Sequenzen und deren Prozentsätze.
Weiterhin bilden 10 Sequenzen, innerhalb des Zeitraumes nur 80.% der Summe ab. Nach dem Ende verteilt sich auf ein Jahr (nicht 20XX sondern 365 Tage) die Restlichen 20%.

Man nennt es glaube ich Mittelabflussplanung, zumindest soll es am Ende als solche funktionieren.

Ich arbeite seit Wochen daran und weiß nicht, wie ich als Anfänger sowas lösen kann. Es kommt mir wirklich sehr kompliziert vor und geht über mein Können hinaus.
Um ehrlich zu sein, habe ich auch keinen richtigen Ansatz, der das Problem tatsächlich angeht.

Ich wäre so dankbar für Hilfe!!

Ricarda

Betrifft: AW: hierzu ...
von: neopa C
Geschrieben am: 05.11.2020 18:00:38

Hallo Ricarda,

... Du schreibst: "Man nennt es glaube ich Mittelabflussplanung, zumindest soll es am Ende als solche funktionieren"

Wer mit einer derartigen Summe wie in Deiner Datei umgehen kann bzw. muss, sollte die entsprechende Ausbildung entweder schon besitzen oder sich qualifizieren lassen/können. In einem völlig kostenfreien wie spezifischen Forum wie diesem, kannst Du diese weder erwarten und kaum erlangen.

Wenn Du excelspezifische Fragen hast, dann bist Du hier richtig, diese kannst Du hier so konkret wie möglich einstellen und kannst meist auch auf Hilfe entsprechende Hilfe rechnen. Wenn Dir dann Hilfe erwiesen werden sollte, solltest Du Dir auch etwas Zeit für ein entsprechendes Feedback nicht nehmen lassen.

Gruß Werner
.. , - ...

Betrifft: AW: hierzu ...
von: Ricarda
Geschrieben am: 05.11.2020 18:22:36

Hallo Werner,

mein Fehler. Ich weiß was du meinst, entschuldige.

Gruß,
Ricarda

Betrifft: AW: Mittelabflussplanung
von: Niclaus Wurthberg
Geschrieben am: 05.11.2020 21:05:51

Hallo Ricarda

Ob Du das Mittelabflussplanung nennst oder 2. Teil der Liquiditätsplanung oder der Cashflow-Rechnung spielt keine Rolle. Aus beruflicher Erfahrung weiss ich, dass das ein ganz spannendes Excel-Projekt sein kann.

Was mir nicht klar ist, sind Deine Sequenzen.
Verteilen sich diese auf die drei Jahre 2020 – 2022? Eine Sequenz würde dann 109.5 Tage dauern.
Oder sind es pro Jahr 10 Sequenzen? Insgesamt also 30 Sequenzen mit je 36.5 Tagen?
Oder sind die Sequenzen die Monate Januar bis Oktober?
Im November und Dezember keine Abflüsse?

Ich kenne in der Mittelabflussplanung nur die monatliche Aufteilung.

Grüsse Niclaus

Betrifft: AW: Mittelabflussplanung
von: Ricarda
Geschrieben am: 06.11.2020 08:10:18

Hallo Niclaus,

die 10 Sequenzen teilen sich auf den entsprechenden, angegebenen Zeitraum auf, der sich aber in der Liste verändern wird. Das kann sein, dass es 10 Monate sind oder eben 3 Jahre. Und da liegt mein Problem, dass der Zeitraum eben variabel ist.
Die 11 Sequenz ist fest nach dem Ende des Vorgangs bzw. des Zeitraums.

Ich habe mir jetzt kleinschrittig etwas zusammen gebastelt, allerdings stehe ich jetzt vor der Frage, ob Excel bei einer Zuordnung von Kosten pro Jahr versteht, dass ein Jahreswechsel vorliegt und deswegen die Kosten anteilig auf das nächste Jahr rechnet?

Grüße,
Ricarda

Betrifft: AW: Mittelabflussplanung
von: Niclaus Wurthberg
Geschrieben am: 06.11.2020 18:08:51

Hallo Ricarda

Ich habe rumgebastelt und lade Dir die Datei hoch. Vielleicht hilft es Dir.
https://www.herber.de/bbs/user/141371.xlsx

Es gibt einige Bedingungen dazu:

Es muss für jede Sequenz das Start-Datum festgelegt werden: Zeile 4 ab Spalte J.

Wenn eine Sequenz sich über einen Jahreswechsel hinaus erstreckt, muss sie aufgeteilt werden.
In meinem Beispiel die Sequenz 4. Sie dauert vom 26.11.2020 bis zum 15.03.2021.
Diese Sequenz muss aufgesplittet werden in die Sequenzen 4.1 und 4.2. Diese Sequenzen müssen in Zeile 3 zwingend so benannt werden. Die Sequenz 4.2 beginnt mit dem 1. Januar des neuen Jahres 2021. Die Prozent-Werte für 4.1 und 4.2 sind identisch. – Aehnliches gilt für die Sequenz 7.

Unter diesen Bedingungen wird mit Hilfszeilen berechnet, wie hoch die Abflüsse und Restbeträge pro Jahr sind. Diese Zeilen (10 – 12) kannst Du ausblenden.

Grüsse Niclaus

Betrifft: AW: Mittelabflussplanung
von: Ricarda
Geschrieben am: 09.11.2020 08:07:55

Wow vielen Dank für deine Mühen!

Eine Frage habe ich dazu. Du hast das Ende also den 31.12.2022 festgelegt. Wenn sich das Ende in 2023 verschiebt, müsste ich die letzte Sequenz dann aber händisch aufteilen, richtig? Mein größtes Problem ist diese Tabelle/Mittelabflussplanung "dynamisch zu machen", also das sich die Daten, das Datum der Sequenz-Beginne sowie die Aufteilung auf die Jahre automatisch anpassen bzw. verschieben.

Weil am Ende besteht die Tabelle eben nicht nur aus einer Einheit, sondern aus 200 und dann wird die händische Einarbeitung unübersichtlich.

Verstehst du mein Problem?



Betrifft: AW: Mittelabflussplanung
von: Niclaus Wurthberg
Geschrieben am: 09.11.2020 20:50:30

Hallo Ricarda

Weil am Ende besteht die Tabelle eben nicht nur aus einer Einheit, sondern aus 200 …
Eine Frage dazu: Was meinst Du mit Einheiten? Sind das Sequenzen? – Das wären ja dann fast mehr als beim BER ;-)

diese Tabelle/Mittelabflussplanung "dynamisch zu machen"
Ich bin noch einmal dahinter und habe vor allem die Formeln verwendet, die Dir in Deinem andern Forumsbeitrag geboten wurden ("Formel-Fehler für Zuordnung Tage zu Jahr").

https://www.herber.de/bbs/user/141442.xlsx

Schau Dir Tabelle3 an in der hochgeladenen Datei. Das Aufsplitten der Sequenzen, die über ein Jahresende hinaus dauern, ist nicht mehr nötig. Du kannst die Sequenzen über beliebig viele Jahre verteilen. Im unteren Teil der Tabelle musst Du für jedes Jahr 4 Zeilen kopieren – immer die ganzen Zeilen, denn von I12 bis AH35 ist alles mit Formeln belegt, auch wenn Du keine Ergebnisse siehst. Und immer eine Zeile leer lassen zwischen den Jahren!

Du kannst auch weitere Spalten für noch mehr Sequenzen einfügen. Und da aus einer "alten" Spalte alle Formeln reinkopieren. Das ist etwas fehler-anfällig.

Unbedingt beachten musst Du: Das letzte Datum in Zeile 4 ("Beginn der Sequenz") muss um 1 (und nur um 1) grösser sein als das Ende-Datum in H4. – In der Zelle darunter darf natürlich kein %-Wert mehr eingegeben werden! – In meiner Tabelle V4:V5.

Die Tabelle3a ist eine Kopie der Tabelle3. Ich habe dort einige Zeilen und Spalten ausgeblendet, damit das Ganze etwas übersichtlicher wird.

Vielleicht hilft Dir das. Grüsse Niclaus