Formel-Hilfe (Logik)

[Azkoyen]

allo Zusammen, 

normalerweise bin ich in Excel "Top-Fit", doch warsch. sehe ich gerade den Wald vor lauter Bäumen nicht mehr. 
Ob es nun ein Formel-Fehler ist, oder ein "Denk-Fehler" im erwarteten Ergebniss kann ich auch nicht genau sagen :)

Falls mal wer Zeit & Lust hat sich das anzuschauen, wäre das super. 

Hintergrund: 
- Ich bin selbstständig und möchte eine Kleine "Umsatz-Planung" machen.
- Diese sollte dynamisch auf Veränderungen reagieren, sprich: Wenn ich in einem Monat Urlaub plane, soll dieser Monat eine "niedrigere" Umsatzerwartung haben, entsprechend die Folge-Monate eine "höhere". 

Entsprechend habe ich erstmal folgendes Layout erzeugt, sollte eig. selbsterklärend sein. (Der einfachheit halber mal 12.000 Euro Wunschumsatz, nat. nicht realisitisch)

Das "Soll" berechnet sich erstmal aus "Monatstage/Gesamt-Tage" was ja erstmal grob stimmt, in nem längeren Monat sollte etwas mehr gehen. 

Soweit passt mein "korrigiertes Soll" auch noch: Monate mit Urlaubstagen eben etwas weniger, dafür die anderen etwas mehr als "nach Kalendertagen": 

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]


Das Problem: Wenn ich nun einen Umsatz eintrage, z.b. im Januar 2000 Euro (obwohl ja eig. "korr. Soll = 0"), stimmt es "irgendwie", aber auch irgendwie nicht ::P

- Das korrigierte Soll für die "Folge-Monate" wird nun korrekt berechnet: Eben nur noch über "10.000" Euro / Arbeitstage pro Monat.
- Mich stört: Sollte die Summe meines "korrigierten Solls" nicht jederzeit 12.000 Euro bleiben?
- Irgendwie stimmt es ja, dass auf diese Tage nun nur noch "10.000" Euro entfallen, entsprechend alles etwas "kleiner". 
- Aber in Summe müsste das korrigierte Soll doch bei 12.000 bleiben  

Es ist wohl nur ein Darstellungsproblem, aber ich kann mein Hirn gerade nicht darum verbiegen, wie ich das "Elegant" lösen kann...


[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
] 

Auch z.b. die Überlegung, dass "Soll korrigiert" + "IST" = "SOLL" sein müsste stimmt nicht:

Erbgringe ich z.b. 10.000 Umsatz in den ersten 6 Monaten, verteilen sich die verbleibenden 2.000 KORREKT auf die verbleibenden 6 Monate - die Summe des Korrigierten Solls
sagt dann aber etwas völlig falsches ::(

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]
Ich habe das Excel-Sheet mal angehängt, falls jemand eine Schnuffige Idee zur Korrektur oder "Darstellung" hat ::)

ps.: Aktuell verwende ich die "308" Tage als referenz, das bewirkt Rückwirkende Anpassungen des "Korr. Solls", wenn ich z.b. im Aug. einen September-Urlaub eintrage. 
Das wäre verschmerzbar, auch wenn es nat. besser wäre, statt der 308 z.b. SUM(DX:$D$Y) zu verwenden - da die Ergebnisse aber dann noch falscher sind, habe ich das erstmal "ignoriert".

[Azkoyen]

Sorry, Blödsinn angehangen ::)

Ohne Referenz auf die Verbleibenden Tage stimmt nat. auch jegliche korrektur nicht.    

So passt es natürlich: Würde kein weiterer Umsatz statt finden, wäre das korrigierte Soll im Dez. 2000 Euro.

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Bleibt nat. die Ur-Frage: Wie schaffe ich es, das korrigierte Soll so darzustellen, dass es stimmt und unterm Strich (Sum(Jan:dez)) auch 12.000 Euro ergibt?

[neopa]

Hallo,

zunächst solltest Du in Spalte A auch die Daten des aktuellen Jahres erfassen.
Dann (zunächst mal nur auf den Monat betrachtet, kann man analog natürlich auch auf Tag genau umsetzen, wobei man dann auch noch mit NETTOARBEITSTAGE() rechnen sollte) in J2 z.B. folgende Formel:

=WENN(MONAT(HEUTE())>MONAT(A5);"";D5/SUMME(INDEX(D:D;VERGLEICH(HEUTE();A:A)):D$16)*K5)

und diese nach unten kopieren.

[Azkoyen]

Hallo,

zunächst solltest Du in Spalte A auch die Daten des aktuellen Jahres erfassen.
Dann (zunächst mal nur auf den Monat betrachtet, kann man analog natürlich auch auf Tag genau umsetzen, wobei man dann auch noch mit NETTOARBEITSTAGE() rechnen sollte) in J2 z.B. folgende Formel:

=WENN(MONAT(HEUTE())>MONAT(A5);"";D5/SUMME(INDEX(D:D;VERGLEICH(HEUTE();A:A)):D$16)*K5)

und diese nach unten kopieren.

Daran hatte ich auch schon gedacht (wenn ich deinen Vorschlag richtig interpretierte):

Du schlägst quasi vor, alle "abgelaufenen" Monate zu ignorieren (""), sodass die Folge-Monate eben nur noch den Offenen Betrag auf die Verbleibenden Tage anzeigen?

Aber ich wüsste am "Jahresende" halte auch gerne, wieviel das "Korrigierte Soll" pro Monat gewesen wäre, und um wieviel ich es jeweils über- bzw. Unterschritten habe. 
(Für entsprechende Planung des nächsten Jahres) 

Was ich meine: Egal in welcher Form ich das "Soll" korrigiere - Am Ende sollten da in Summe 12.000 stehen. Denn sonst war die Berechnung des korrigierten Solls ja offensichtlich
"falsch"?

[Azkoyen]

Weiterer Versuch: Ich markiere Monate als "erledigt", oder eben nicht. 

Die Berechnung des "korrigierten Solls" stimmt wieder insofern, dass in diesem Beispiel im Dezember 500€ zu erbringen wären.
Die Summe über die korrigierten Solls hingegen ist wieder fernab des "Erwartungswerts"....

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Vielleicht noch ganz kurz, was ich mir eigentlich erwarte:

- Das "korrigierte Soll" soll mir den noch zu erbringenden Umsatz auflisten.
- Es soll berücksichtigen, wieviel Umsatz bereits erbracht wurde.
- Es soll berücksichtigen, wie "arbeitsintensiv" (Urlaub / Krank) einzelne Monate sind.
- Im Idealfall: Eintragungen von Urlaub im "Oktober" sollen die Vormonate nicht beeinflussen, sondern sich lediglich auf Oktober, November, Dezember auswirken.
- In Summe sollte das "Korrigierte Soll" dem "Soll" entsprechen - in genantem Beispiel 12.000 Euro.

(Aber vielleicht beißen sich die Anforderungen ja schon "rein logisch" selbst.)

Mathematisch ist das Problem nat. Lösbar - vielleicht hapert es einfach nur daran, dass Excel (in solch einer Anordnung) keine "Statefull" Zellen kennt?

[snb]

In L5

PHP-Code:

=$K$4*$D5/$D$17 


Hinunterziehen bis L16

[Azkoyen]

In L5

PHP-Code:

=$K$4*$D5/$D$17 


Hinunterziehen bis L16

Das Ergibt in Summe 12.000 - Ist Formelmäßig logisch, doch es reagiert in "keinster" weise auf den erbrachten Umsatz.

Wenn ich im Januar 12.000 Euro Umsatz hätte (=Jahreserwartung), ändert sich am Ergebnis "nichts"...
Das Sheet würde trotzdem für jeden Monat einen Anteiligen Umsatz "wünschen":

Das "korrigierte Soll", sollte in diesem Fall aber für Feb-Dez = "0.00" sein...
(Währen es nur 6.000 im Januar sollten die verbleibenden 6.000 entsprechend auf Feb-Dez anteilig verteilt werden)

[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

[Azkoyen]

In L5

PHP-Code:

=$K$4*$D5/$D$17 


Hinunterziehen bis L16

Dennoch vielen Dank für deinen Beitrag - er hat mir eine Sache klar gemacht: 

Das Problem ist nicht die (mögliche) Umsetzung, sondern meine Erwartungshaltung: 

- Einerseits soll der Januar ein "korrigiertes Soll" von 0 € zeigen, da keine Arbeitstage vorliegen.
- Andererseits sollen die restlichen Monate ebenfalls (in Summe) 0€ zeigen, falls der notwendige Umsatz im Januar erbracht wurde.
- Und die Summe darüber soll 12.000 Euro sein (also 12x0 = 12.000 ??!!) - das ist nicht logisch und daher auch nicht mathematisch "abbildbar", zumindest nicht in einer "Spalte".

[shift-del]

Hallo

Einen Vorschlag hätte ich noch.

Code:

=MAX(0;WENN(H5;H5;($K$4-SUMME(H$4:H4))/SUMMENPRODUKT((H$5:H$16=0)*(D$5:D$16))*D5))

[Azkoyen]

Aktueller Stand: 

- Berechnung des "korrigierten Solls" bottom-up.
- Eingabe des ISTS von oben nach unten - ergibt unterm Strich genau die Erwartung, allerdings mit dem 
Effekt, dass sich die Werte von Januar/Februar ändern, wenn ich etwas im September/Dezember eintrage...

Beispiel 1: Immer 2000:
[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

[u]Beispiel 2: Immer das "Korrigierte Soll" (Es verändert sich nach Eintragung rückwirkend, stimmt unterm Strich aber):

[u][img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]


-> Feb 1129,87
[u][img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

-> Mar 896,10

[u][img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

-> Viele mehr: Am Ende: 12.000.

[u][img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]