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

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

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

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

см пример листа

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

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

Спасибо.

Источник

2 ответа

Решение

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

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

Проблема: вы хотите рассчитать промежуточную сумму 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

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

Источник

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

  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)

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

Источник
Другие вопросы по тегам