Dave vereinfachte die Frage von Mikey, sodass sie nicht so offen ist. Lass es mich wiederholen. Wir wissen, wann ein Vertrag beginnt und endet. Es gibt Regeln für die Entscheidung, wann die Umsatzphase beginnt und endet. Wir wollen zwei Dinge finden:
- Wie viele Monate gibt es in der Einnahmenphase und
- Wie viel Umsatz wird 2016 erzielt?
Ich werde die Einnahmen ermitteln, die in demselben Jahr wie dem Start anfallen, egal in welchem Jahr.
Ich habe Namen für die folgenden Zellen definiert. Contract.valueist als 1000 definiert.
Start $C$3 Stop $D$3 Months $E$3 Startund Stopwerden aus den Anfangs- und Enddaten des Vertrags berechnet. Wir brauchen die Anzahl der Monate von Startbis Stop, um die "gleichphasige Umsatzphasen" zu finden. Es ist wirklich leicht. Verwenden Sie DATEDIF, oder aktivieren Sie das Kontrollkästchen Berechnen der Anzahl der Monate zwischen zwei Daten in Excel für eine alternative Methode. Hier ist die Formel für die Zelle E3.
=DATEDIF(Start,Stop,"M")+1 Das +1macht die Anzahl von Monaten einschließlich des ersten und letzten Monats. Im Beispiel gibt es neun Einnahmenmonate von Juli 2016 bis März 2017.
So finden Sie den Umsatz in 2016, dem Jahr, in dem die Umsatzphase beginnt.
C2Ermitteln Sie zunächst die Anzahl der Monate im Jahr 2016. Cell berechnet es:
=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1 Berechnen Sie die Anzahl der Monate und den Umsatz für einen Monat in der Umsatzphase. Zelle D2macht es:
=C2*Contract.value/Months Die Kombination von C2und D2in einer einzigen Formel zeigt das gleiche Ergebnis in der Zelle E2.
=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months Dieser Ansatz hat einige Nachteile. Was ist, wenn Sie den Umsatz 2017 finden möchten? Die Formel für 2017 folgt dem gleichen Muster wie C2, aber es ist nicht dasselbe. Die Dinge werden unordentlich, wenn sich die Ertragsphase über zwei Jahre erstreckt.
Das Problem kann verallgemeinert werden, indem eine vereinfachte Amortisierungstabelle erstellt und PivotTable verwendet wird, um es zusammenzufassen. Start, StopUnd Monthssind alles, was erforderlich, um eine Liste der Monate und Umsatz zu machen. Das PivotTable ermittelt den Umsatz für jedes Jahr in der Umsatzphase.
Row 2ist etwas Besonderes.
Hier sind die Formeln für A2:C2.
=Start =YEAR(A2) =Contract.value/Months Verwenden Sie die Formeln für die A3:C3auf Fill Downdie Liste zu füllen. Füllen Sie die Anzahl der Zeilen, um die größtmöglichen Monate in der Ertragsphase zu berücksichtigen. Es ist einfach mehr hinzuzufügen.
=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"") =IF(A3<>"",YEAR(A3),"") =IF(A3<>"",Contract.value/Months,"") Der "Trick" ist in A3. Wenn das Datum in der Spalte Afür die vorherige Zeile nachher liegt Stop, ist die Zelle in der Spalte Aleer und jede nachfolgende Zeile ist leer. Daher wird in der Umsatzphase für jeden Monat eine Zeile angezeigt - nicht mehr und nicht weniger. Der Trick ist nützlich, wenn Sie eine Liste erstellen möchten, die Anzahl der Zeilen jedoch nicht im Voraus kennen.
Wählen Sie zum Erstellen der Pivot-Tabelle die gesamte Liste einschließlich leerer Zeilen aus. Wenn Sie die gesamte Liste auswählen, funktioniert die Pivot-Tabelle auch dann, wenn Sie die Vertragsdaten ändern. Verwenden Sie das YearFeld für die Pivot-Tabellenzeile Sum of Revenueund Count of Yearfür die Zusammenfassungsfelder.