Excel-Prognosemodell für im Voraus erhaltene Abonnements für die nächsten 2 Jahre

541
Tee

Ich arbeite an einem Finanzmodell, das Folgendes enthält:

  • Monate in Spalten (Jan-2020 bis Dez-2025)
  • Anzahl der Abonnenten pro Monat (Spalte) pro Paket in Zeilen
  • Preis für jedes Paket in Reihen

Cashflows zu machen ist einfach, da [monatliche Abonnenten (Spalte) x pro Paket (Zeile) x Paketpreis (Zeile). Ich bleibe jedoch bei der Gewinn- und Verlustrechnung, weil der Abonnementumsatz auf die Laufzeit des Pakets verteilt werden sollte (dh 2 Jahre). .

see example worksheet

In diesem Beispiel sollte der im Januar beginnende Vertrag um 24 Monate geteilt werden, danach im Februar mit dem Vertrag aufgefüllt werden usw. So sollte dies fortgesetzt werden, bis der im Januar beginnende Vertrag den 24. Monat erreicht.

Es scheint, dass die Umsatzberechnung für jeden Monat und das Paket nur mit einer Array-Funktion möglich ist, aber ich kann nicht sagen, wie.

Vielen Dank.

1
Willkommen bei Super User! Sie haben mehrere Versionen von Excel mit Tags versehen, welche verwenden Sie? Prost :) bertieb vor 6 Jahren 0
Ich weiß nicht, was das bedeutet: "Der Vertrag, der in Jan beginnt, sollte durch 24 Monate geteilt und dann im Februar mit dem Vertrag aufgefüllt werden." Versuchen Sie, das Gesamteinkommen für jedes Paket über einen Zeitraum von 24 Monaten zu erhalten? 1/24 davon? Erklären Sie bitte genauer. Bandersnatch vor 6 Jahren 0
@Bandersnatch: Ja, 1/24 des Betrags, der in einem bestimmten Monat eingegangen ist (und zwar aus buchhalterischer Sicht, dh Übereinstimmungskonzept). Also im Januar x Betrag geteilt durch 24 (2 Jahre Vertrag), dann im Februar [x / 24 + y / 24], wobei x so lange fortgesetzt werden sollte, bis der 24. Monat abgeschlossen ist und so weiter. Ich hoffe es wurde klar, ansonsten lass es mich wissen. Vielen Dank! Tee vor 6 Jahren 0
Die Summe für Paket 1 wäre also was? D14 / 24 + E14 / 24 + F14 / 24 ...? Oder versuchen Sie, D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24) zu erhalten ... Bandersnatch vor 6 Jahren 0
@ Bandersnatch: Nochmals vielen Dank, dass Sie bei mir waren. Ich versuche das letztere zu tun, nämlich [D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24)] und so, dass D14 / 24 so bald stoppt Da der 24. Monat vorüber ist, habe ich 5 Jahre x 12 Monate = 60 Spalten. Tee vor 6 Jahren 0
Erwischt. Lass mich ein bisschen darüber nachdenken. Bandersnatch vor 6 Jahren 0
OK, ich würde eine "Helfer" -Reihe verwenden. Geben Sie für Programm 1 diese Formel in D18 ein und füllen Sie AA18 nach: `= SUM (D14: $ AA14)`. Die Summe, nach der Sie suchen, ist `= SUM (D18: AA18) / 24 '. Sie können die Hilfszeilen ausblenden, wenn Sie möchten. Und ich wette, @ScottCraner könnte dies ohne Helferreihe tun. Vielleicht wird er uns mit seiner Anwesenheit zieren. :-) Bandersnatch vor 6 Jahren 0
Da Sie alle Zahlen (Abonnement + Paket + Bargeld) für 24 Monate haben, was genau möchten Sie dann herausfinden? @Bandersnatch hat Ihnen vorgeschlagen, die Laufsumme als Helfer zu berechnen und dann die Summe von 1 Monat von 24 zu ermitteln. Ich möchte Ihnen vorschlagen, die in Excel verfügbare Regression für Prognoseverkäufe, Einnahmen und andere zu verwenden. Rajesh S vor 6 Jahren 0
@Bandersnatch ... außerhalb der Zelle SUM (...) / 24 zu sprechen, ist wahrscheinlich keine Lösung. Grund. Angenommen, ich habe am 20. Januar Bargeld erhalten und der Vertrag endet am 21. Dezember. Nächsten Monat (20. Februar) werde ich wieder Bargeld erhalten und der Vertrag endet am 21. Januar. Jetzt am 20. Januar brauche ich einen anteiligen Umsatz mit einem zusammenfassenden Effekt, dass ich im Februar-20 für zwei Monate (20. Februar + 20. Januar) Einnahmen bekomme, so dass die Einnahmen aus Verträgen, die am 20. Januar begonnen haben, im Dezember enden -21. Ich hoffe, das ist eine bessere Erklärung für das, was ich will. Tee vor 6 Jahren 0
Es ist immer noch nicht zu klar. Sprechen wir über mögliche Formeln. Sie sagten, Sie hätten versucht, D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24) ... zu erhalten, wobei alle Begriffe im 24. Monat enden. Dies ist das Gleiche wie (D14 + (D14 + E14) + (D14 + E14 + F14) ...) / 24. Benötigen Sie etwas anderes? Bandersnatch vor 6 Jahren 0
Die Datei hat keinen Nutzen. Es hatte Spaltenüberschriften wie fname und iname, aber diese Spalten enthielten 3 Reihen von Kauderwelsch wie: Ù † ÙˆØ ± Ù ‡. Alles andere war "null" mit Ausnahme des Ereignislinks mit URLs. Bandersnatch vor 6 Jahren 0
@Bandersnatch gosh so leid es war dumm von mir. Wieder hochgeladene korrekte Datei, bitte entschuldigen Sie sich. https://expirebox.com/download/7fd20ad2d8ec23e37006fe756e57c228.html Tee vor 6 Jahren 0
OK danke. Ich glaube ich verstehe jetzt. Versuchen Sie diese Formel in G34 und füllen Sie sie rechts aus: `= 35 * (SUM ($ G17: G17) / 36)`. Das funktioniert bis zum 31. Januar 2021 einwandfrei. Nun müssen Sie noch weiter gehen und feststellen, dass der Total Unearned abnimmt? Bandersnatch vor 6 Jahren 0
@Bandersnatch. Danke für Ihre ganze Zeit. Ihre Formel funktioniert zwar, geht aber schief (als ich mich schließlich für die Verwendung von 60 Helferreihen entschied). Ich werde die Frage als ungelöst markieren. Ich freue mich über all Ihre Bemühungen, zu helfen. Wiedersehen in einer anderen Frage, tschüss. Tee vor 6 Jahren 0
Ich kann ** es ** für weitere Spalten korrekt machen, ich habe nur versucht zu klären, was Sie brauchen. Bandersnatch vor 6 Jahren 0
Wenn Sie sich nicht mehr für Ihre Frage interessieren, können Sie sie löschen. Hinzufügen von "aufgegeben" ist nicht die Art und Weise, wie sie verwaltet werden. Máté Juhász vor 6 Jahren 0

2 Antworten auf die Frage

1
Bandersnatch

@Tee, falls du noch da bist, werde ich eine Antwort auf deine Frage posten. Es hat eine Weile gedauert, bis Sie das Problem, mit dem Sie konfrontiert sind, klar verstanden haben.

Lassen Sie mich daher das Problem, das ich gelöst habe, darlegen, und ich hoffe, Ihnen genug Informationen zu geben, um die Lösung zu ändern, wenn ich das Problem nicht richtig verstanden habe.

Problem: Sie möchten eine laufende Summe von 35/36 der Zahlen berechnen, die in G17 Ihrer Tabelle beginnen und über AP17 hinaus fortfahren. Der knifflige Teil ist, dass sobald 35 Terme in Ihrer Summe vorhanden sind, der Anfang des Bereichs nach rechts verschoben werden muss (dh H17, I17 usw.), da die Formel nach rechts gefüllt ist.

Die folgende Diskussion zeigt, wie die Summe berechnet wird, und die endgültige Formel wird mit 35 multipliziert und durch 36 dividiert.

Lösung: Um die Summe zu berechnen, ist eine Formel wie folgt erforderlich:

=SUM(INDEX(reference,row_num,[column_num]):INDEX(reference,row_num,[column_num])

Die "Referenz" -Form von INDEX () kann verwendet werden, um eine Zellenreferenz zurückzugeben, und hier berechnet der erste INDEX () den Beginn des zu summierenden Bereichs, während der zweite INDEX () das Ende des Bereichs berechnet.

Die Summe beginnt mit G17 (Spalte 7) für alle Spalten, die kleiner sind als die Spalte AP (Spalte 42). Beginnend mit der Spalte AP wird die Startzelle um eine Spalte nach rechts verschoben, wenn die Formel nach rechts gefüllt wird. Der erste INDEX () ist also:

INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34))

In Spalte AP beginnt der Summenbereich beispielsweise mit H17. Spalte 42-34 = 8 = Spalte H.

Das Ende des zu summierenden Bereichs ist nur die aktuelle Spalte. Der zweite INDEX () ist also:

INDEX($17:$17,1,COLUMN())

Nun ist die Summe:

SUM(INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34)):INDEX($17:$17,1,COLUMN()))

Und die letzte Formel lautet:

=35*(SUM(INDEX($17:$17,1,IF(COLUMN()<42,7,COLUMN()-34)):INDEX($17:$17,1,COLUMN())))/36

Ein Teil Ihrer Kalkulationstabelle mit der Berechnung ist in der folgenden Abbildung dargestellt. Bitte kommentieren Sie, wenn Sie noch hier sind. Freundliche Grüße.

** Vielen Dank ** für alle Bemühungen. Wie kann ich dir einen Kaffee kaufen? __serious__ :) Tee vor 6 Jahren 0
Sehr gerne helfen. Entschuldigung, es hat so lange gedauert. Kaffee? Nein danke, aber Sie können mir einen virtuellen Bourbon schicken. :-) Freundliche Grüße. Bandersnatch vor 6 Jahren 0
0
Mario J.

Sie können dies in zwei Schritten tun:

  1. Zuerst berechnen Sie Neues monatliches Einkommen für jedes in Ihrem Beispiel erhaltene Bargeld, indem Sie es einfach mit 24 teilen. Beispiel:

    ein. Put = D14 / 24 in D19 (neues Monatseinkommen für Paket-1-Verkäufe ab Januar)

    b. Kopieren Sie diese Formel nach D20: D21 (Pakete 2 und 3) und dann in alle anderen Spalten ab E19: E21

  2. Zweitens summieren Sie alles Neue Monatseinkommen, aber bis zu 24 Monate zurück.

    ein. Put = SUMME ($ D19: D19) in D24

    b. Kopieren Sie diese Formel in E24: AA24 (2. bis 24. Monat)

    c. Entfernen Sie in AA24 das Zeichen $, um die Formel zu erhalten: = SUM (D19: AA19), und kopieren Sie es anschließend in AB24. Nun ist es festgelegt, die letzten 24 Monate zu summieren.

    d. Kopieren Sie die Zeile 24 in die Zeilen 25 und 26 (um die Summen für die Pakete 2 und 3 zu erhalten)

Hier sind Bilder dieser Lösung: picture_of_solution Bild2 Bild3