Model prognozy programu Excel dla subskrypcji otrzymanych z wyprzedzeniem na kolejne 2 lata

Pracuję nad modelem finansowym, który zawiera:

• Miesiące w kolumnach (styczeń-2020 do grudnia 2025)
• Liczba subskrybentów na miesiąc (kolumna) na pakiet w rzędach
• Cena każdej paczki w rzędach

Wykonywanie przepływów pieniężnych jest łatwe, ponieważ [abonenci miesięczni (kolumna) x na pakiet (wiersz) x cena pakietu (wiersz), jednak utknąłem w rachunku zysków i strat, ponieważ przychody z subskrypcji powinny być rozłożone na okres trwania pakietu (który wynosi 2 lata) .

W tym przykładzie umowa rozpoczynająca się w styczniu powinna zostać podzielona na 24 miesiące, a następnie uzupełniona o umowę w lutym i tak dalej ... powinno to trwać do momentu, gdy umowa zawarta w styczniu osiągnie 24 miesiąc.

Wydaje się, że obliczanie przychodu dla każdego miesiąca, a pakiet jest możliwy tylko przy użyciu funkcji tablicowej, ale nie mogę tego pojąć.

Dziękuję Ci.

@ Tee, na wypadek, gdybyś nadal był w pobliżu, zamieszczam odpowiedź na twoje pytanie. Długo trwało zrozumienie problemu, z którym się borykasz, i nadal nie jestem pewien, czy mam dokładnie taką odpowiedź.

Pozwólcie, że przedstawię problem, który rozwiązałem, i mam nadzieję, że udzielę wystarczających informacji, aby zmodyfikować rozwiązanie, jeśli moje rozumienie problemu będzie nieprawidłowe.

Problem: Chcesz obliczyć bieżącą sumę 35 / 36. każdej liczby, która zaczyna się w G17 arkusza kalkulacyjnego i kontynuuje poza AP17. Trudna część polega na tym, że gdy w sumie będzie 35 wyrażeń, początek zakresu musi przejść w prawo (tj. H17, I17 itd.), Ponieważ formuła jest wypełniona po prawej stronie.

Poniższa dyskusja pokaże, jak obliczyć sumę, a ostateczna formuła zostanie pomnożona przez 35 i podzielona przez 36.

Rozwiązanie: Aby obliczyć sumę, wymagana jest następująca formuła:

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

Forma „odwołania” INDEKSU () może być użyta do zwrócenia odwołania komórki, a tutaj pierwszy INDEKS () oblicza początek zakresu do zsumowania, a drugi INDEKS () oblicza koniec zakresu.

Suma zaczyna się od G17 (kolumna 7), dla wszystkich kolumn mniejszych niż kolumna AP (kolumna 42). Począwszy od kolumny AP komórka początkowa przesuwa jedną kolumnę w prawo, gdy formuła jest wypełniana w prawo. Zatem pierwszy INDEX () to:

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

Na przykład w kolumnie AP zakres sumy rozpoczyna się od H17. Kolumna 42–34 = 8 = kolumna H.

Koniec zakresu do zsumowania to tylko bieżąca kolumna. Drugi INDEX () to:

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

Teraz suma wynosi:

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

Ostateczna formuła to:

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

Część arkusza kalkulacyjnego z obliczeniami pokazano na poniższym obrazku. Proszę o komentarz, jeśli nadal tu odwiedzasz. Z poważaniem.

Możesz to zrobić w dwóch krokach:

1. Najpierw obliczasz Nowy miesięczny dochód dla każdej gotówki otrzymanej w twoim przykładzie, dzieląc ją na 24. Na przykład:

   za. Wpisz = D14 / 24 w D19 (nowy miesięczny dochód ze sprzedaży pakietu 1 od stycznia)

   b. Skopiuj tę formułę do D20: D21 (pakiety 2 i 3), a następnie do wszystkich innych kolumn od E19: E21

2. Po drugie, sumujesz wszystkie Nowe miesięczne dochody, ale do 24 miesięcy wstecz.

   za. Wpisz = SUMA (D19 $: D19) w D24

   b. Skopiuj tę formułę do E24: AA24 (od 2 do 24 miesiąca)

   do. W AA24 usuń znak $, aby uzyskać formułę: = SUMA (D19: AA19), a następnie skopiuj go do AB24. Teraz ustalono sumę z ostatnich 24 miesięcy.

   re. Skopiuj wiersz 24 do wierszy 25 i 26 (aby uzyskać kwoty dla paczek 2 i 3)

Oto zdjęcia tego rozwiązania: