Модель Excel Forecast для подписок, полученных заранее на ближайшие 2 года

Question

Я работаю над финансовой моделью, которая содержит:

• Месяцы в столбцах (январь-2020 - декабрь-2025)
• Количество подписчиков в месяц (столбец) на пакет в строках
• Цена каждой упаковки в строках

Делать денежный поток легко, как [ежемесячные подписчики (столбец) x за пакет (строку) x цена пакета (строка), однако, я застрял в отчете о доходах, потому что доход от подписки должен быть распределен на срок владения пакетом (который составляет 2 года) ,

В этом примере контракт, начинающийся в январе, должен быть разделен на 24 месяца, затем дополнен контрактом в феврале и так далее ... это должно продолжаться до тех пор, пока контракт, начинающийся в январе, не достигнет 24-го месяца.

Кажется, расчет дохода за каждый месяц и пакет возможен только с использованием функции массива, но я не могу понять, как это сделать.

Спасибо.

Answer 1

@ Ти, если ты все еще рядом, я отправляю ответ на твой вопрос. Потребовалось много времени, чтобы четко понять проблему, с которой вы сталкиваетесь, и я до сих пор не уверен, что все правильно понял.

Итак, позвольте мне изложить проблему, которую я решил, и я надеюсь дать вам достаточно информации, чтобы изменить решение, если мое понимание вашей проблемы неверно.

Проблема: Вы хотите вычислить промежуточную сумму 35/36 от каждого из чисел, которые начинаются в G17 вашей электронной таблицы и продолжаются после AP17. Сложность в том, что если в вашей сумме 35 терминов, начало диапазона должно сдвинуться вправо (то есть H17, I17 и т.д.), Так как формула заполнена вправо.

Следующее обсуждение покажет, как рассчитать сумму, а окончательная формула будет умножена на 35 и разделена на 36.

Решение: для расчета суммы требуется следующая формула:

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

"Ссылочная" форма INDEX() может использоваться для возврата ссылки на ячейку, и здесь первый INDEX() вычисляет начало диапазона, который будет суммироваться, а второй INDEX() вычисляет конец диапазона.

Сумма начинается с G17 (столбец 7), для всех столбцов меньше, чем столбец AP (столбец 42). Начиная со столбца AP, начальная ячейка перемещается на один столбец вправо, а формула заполняется вправо. Итак, первый INDEX () это:

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

Например, в столбце AP диапазон суммы начинается с H17. Столбец 42-34 = 8 = столбец H.

Конец диапазона, подлежащего суммированию, является просто текущим столбцом. Итак, второй INDEX() это:

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

Теперь сумма составляет:

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

И последняя формула:

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

Часть вашей электронной таблицы с расчетом показана на рисунке ниже. Пожалуйста, прокомментируйте, если вы все еще посещаете здесь. С наилучшими пожеланиями.

Answer 2

Вы можете сделать это в два этапа:

1. Сначала вы рассчитываете Новый ежемесячный доход для каждой Денежной наличности, полученной в вашем примере, с простым делением на 24. Например:

   а. Put = D14/24 в D19 (новый ежемесячный доход для продаж пакета 1 с января)

   б. Скопируйте эту формулу в D20:D21 (пакеты 2 и 3), а затем во все остальные столбцы, начиная с E19:E21 и далее.

2. Во-вторых, вы суммируете все новые ежемесячные доходы, но до 24 месяцев назад.

   а. Положите = СУММА ($ D19:D19) в D24

   б. Скопируйте эту формулу в E24:AA24 (со 2-го по 24-й месяц)

   с. В AA24 удалите знак $, чтобы получить формулу: = SUM (D19:AA19), а затем скопируйте его в AB24 и далее. Теперь он зафиксирован на сумму за последние 24 месяца.

   д. Скопируйте строку 24 в строки 25 и 26 (чтобы получить суммы для пакетов 2 и 3)

Вот фотографии этого решения: