Hi, ich habe folgendes Problem und hoffe, Ihr könnt mir da einen Denkanstoß geben, wie ich an die Sache ran gehen kann.
Die Dreisatzgeschichten sind ja kein Problem - nur wie bringe ich Excel bei, mit den Prozent zahlen für die einzelnen Monate zu rechnen… so wie in der nachfolgenden Erklärung…
Tut mir Leid, wenn es etwas wüst aussieht - habe es schritt für schritt erklärt…
Achja… ich arbeite mit Excel 2003
____________________________________________________________________
Was habe ich an Werten?
- Ein Anfangswert x zu einem beliebigen Zeitpunkt im Jahr
- Ein Endwert y zu einem anderen beliebigen Zeitpunkt im Jahr
- Einen bekannten Stichtag
- Eine Liste von einer prozentualen Aufteilung pro Monat
Wir haben für:
Januar = 14%
Februar = 10 %
März = 3%
April = 2%
Mai = 1%
Juni = 1%
Juli = 0,5%
August = 3,5%
September = 12%
Oktober = 16%
November = 17%
Dezember = 20%
Bsp:
Gegeben:
Wert 1 = 1
Stand = 17.06.2010
Wert 2 = 522
Stand = 01.10.2010
z1 = 0,962375
z2 = 10 bei Gebiet 1
z2 = 11 bei Gebiet 2
Stichtag ist = 01.02.2011
Berechnet:
a1 = 511
A0 = 5110 (Gebiet 1)
A0 = 5731 (Gebiet 2)
t = 104 Tage
100% = 224 Tage
Was mache ich mit den Werten?
- Aus der Differenz y - x ergibt sich ein Wert = a1
- Aus der Differenz des Datums zum Zeitpunkt wo X und Y erfasst wurden ergibt sich eine Anzahl Tage = t
Jetzt wird die Differenz a1 mit einer zahl z1 multipliziert um auf den Wert = a2 zu kommen
Der Wert a2 muss nochmal mit einer Größe z2 multipliziert werden um auf den Wert = A0 zu kommen.
Wert A0 ist der effektive Verbrauch für den Zeitraum t
Wir gehen davon aus, das t nicht 12 Monate entspricht (365 Tage)
Die Differenz zwischen Stichtag s und x entspricht tx = 100%
Soweit so gut… doch wie gleiche ich jetzt ab, mit der vorliegenden Liste, wie dieser Zeitraum gewichtet wird?
Es muss also berechnet werden, wie viel % der Wert A0 zum Zeitpunkt y entspricht um auf 100% zum Stichtag tx hochrechnen zu können.
Es fallen an: 13 Tage im Juni = (13*100)/30 Entspricht: 43,33%
43,33% von 1% = 0,57%
Es ergibt sich die Situation:
Juni = 0,57%
Juli = 0,5%
August = 3,5%
September = 12%
Oktober = 16%
November = 17%
Dezember = 20%
Januar = 14%
= 83,57%
Es werden also bis zum Stichtag 83,57% der Jahresmenge 365 Tage benötigt.
Jetzt wird ermittelt, wie viel % 224 Tage von 365 Tagen sind.
=(224*100%)/365
= 61,37%
Wir kommen zu dem Ergebnis, dass wir im Zeitraum von 61,37% der Tage einen Bedarf von 83,57% erreichen.
Jetzt ist meine monatliche Vorauszahlung jedoch so ausgelegt, das ich in 100% der Tage meinen Bedarf von 100% decke - dies trifft hier jedoch nicht zu, also muss ich die Differenz von 22,2% in der verbleibenden Zeit bis zum Stichtag ausgleichen, um nicht in eine fette Nachzahlung zu laufen.
Wir gehen davon aus, meine Monatliche Vorauszahlung liegt bei 55,00 Euro, diese wurde immer pünktlich abgebucht. erstmals wurde am 15.07. abgebucht. Bisher wurden also 3x 55,00 Euro = 165,00 Euro voraus gezahlt.
Die Kosten belaufen sich auf auf 5,5 ct je Wert 1,0 bei A0
Mein voraussichtlicher Verbrauch bis zum Stichtag berechnet sich also wie folgt:
104 Tage entsprechen 5110 bzw. 5731 = 16,57%
Wir berechnen:
5110 * 5,5ct = 281,05 Euro
5731 * 5,5ct = 315,21 Euro
Wir stellen fest:
281,05-165 = 116,05 minus
315,21-165 = 147,21 minus
Wir sehen also, unser Verbrauch wird nicht annähernd von der Summe gedeckt, die wir monatlich voraus leisten und müssen die Vorauszahlung für die Restlaufzeit entsprechend nach oben Korrigieren.
Wir wissen:
16,57% = 5110
16,57% = 5731
Wir berechnen also den zu erwartenden Verbrauch:
(83,57*5110)/16,57 = 25.772
(83,57*5731)/16,57 = 28.904
Und daraus die zu erwartenden Kosten:
25.772*5,5ct = 1417,46 Euro
28.904*5,5ct = 1589,72 Euro
Wir ziehen ab, was wir bisher gezahlt haben:
1417,46-165,00 = 1252,46 Euro
1589,72-165,00 = 1424,72 Euro
Nun berechnen wir die Vorauszahlung neu, für die 4 verbleibenden Monate bis zum Stichtag.
1259,72/4 = 313,12 Euro
1424,72/4 = 356,18 Euro
Aus Sicherheitsgründen wird noch leicht aufgerundet:
313,12 auf 315,00 Euro
356,18 auf 358,00 Euro
Wohne ich in Gebiet 1 muss ich für Oktober, November, Dezember und Januar je 315,00 Euro /mtl. Vorauszahlung leisten um nicht in eine Nachzahlung zu rennen.
Wohne ich in Gebiet 2 muss ich für Oktober, November, Dezember und Januar je 358,00 Euro /mtl. Vorauszahlung leisten um nicht in eine Nachzahlung zu rennen.
_________________________________________________________________
Ich hoffe, das ich mich nirgendwo mit den Werten verhauen habe, vom Sinngemäßen sollte es jedoch in jedem Fall nachvollziehbar sein.
Ich habe meine Datei in 3 Blätter unterteilt.
Tabelle1 = Berechnungen
Tabelle2 = Gebiete
Tabelle3 = Prozentwerte
___________________________________________________________________
Danke für eure Hilfe.
