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

367
Tee

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

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

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

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

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

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

Спасибо.

1
Добро пожаловать в Супер пользователя! У вас есть несколько версий помеченных Excel, какую вы используете? Ура :) bertieb 6 лет назад 0
Я не знаю, что это значит: «контракт, начинающийся в январе, должен быть разделен на 24 месяца, а затем дополнен контрактом в феврале и так далее». Вы пытаетесь получить общий доход для каждого пакета в течение 24 месяцев? 1/24 этого? Пожалуйста, объясните более четко. Bandersnatch 6 лет назад 0
@Bandersnatch: Да, 1/24 от суммы, полученной в конкретном месяце (и это с точки зрения бухгалтерского учета, то есть соответствия концепции). Таким образом, в январе сумма x делится на 24 (контракт на 2 года), затем в феврале [x / 24 + y / 24], где x должен продолжаться до тех пор, пока он не завершится 24-го месяца, и так далее. Я надеюсь, что ясно, иначе, пожалуйста, дайте мне знать. Спасибо! Tee 6 лет назад 0
Таким образом, сумма для пакета 1 будет что? D14 / 24 + E14 / 24 + F14 / 24 ...? Или вы пытаетесь получить D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24) ... Bandersnatch 6 лет назад 0
@Bandersnatch: Еще раз спасибо за то, что со мной. Я пытаюсь сделать последнее [D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24)] и таким образом, что D14 / 24 останавливается, как только 24-й месяц закончился, потому что у меня 5 лет x 12 месяцев = 60 столбцов. Tee 6 лет назад 0
Попался. Дайте мне немного подумать об этом. Bandersnatch 6 лет назад 0
ОК, я бы использовал строку "помощник". Для Программы 1 поместите эту формулу, скажем, в D18 и заполните прямо до AA18: `= SUM (D14: $ AA14)`. Тогда сумма, которую вы ищете, равна `= SUM (D18: AA18) / 24`. Вы можете скрыть вспомогательные строки, если хотите. Бьюсь об заклад, @ScottCraner может сделать это без строки помощника. Может быть, он украсит нас своим присутствием. :-) Bandersnatch 6 лет назад 0
Поскольку у вас есть все цифры (подписка + пакет + наличные) за 24 месяца, что именно вы пытаетесь выяснить? @Bandersnatch предложил вам рассчитать промежуточную сумму как вспомогательную, а затем нашел сумму за 1 месяц из 24. Я хотел бы предложить вам использовать регрессию, доступную в Excel, для прогнозных продаж, доходов и других. Rajesh S 6 лет назад 0
@Bandersnatch ... говорить вне ячейки SUM (...) / 24 вряд ли решение. Причина. Скажем, я получил наличные в январе-20, и контракт заканчивается 21 декабря, в следующем месяце (20 февраля) я снова получу деньги, и контракт заканчивается в 21 января ... Теперь, в 20 января, мне нужен пропорциональный доход с комбинированным эффектом, так как в 20 февраля я получаю доход за два месяца (20 февраля + 20 января) таким образом, чтобы доход, связанный с контрактом, начатым в январе 20, прекращался в декабре. -21. Я надеюсь, что это лучшее объяснение того, что я хочу. Tee 6 лет назад 0
Это все еще не слишком ясно. Давайте поговорим о возможных формулах. Вы сказали, что пытаетесь получить D14 / 24 + (D14 / 24 + E14 / 24) + (D14 / 24 + E14 / 24 + F14 / 24) ... со всеми терминами, заканчивающимися на 24-й месяц. Это то же самое, что (D14 + (D14 + E14) + (D14 + E14 + F14) ...) / 24. Вам нужно что-то отличное от этого? Bandersnatch 6 лет назад 0
Файл был бесполезен. У него были заголовки столбцов, такие как fname и iname, но эти столбцы содержали 3 ряда слов, например:: † ÙˆØ ± Ù ‡. Все остальное было "нулевым", за исключением ссылки на событие, которая имела URL. Bandersnatch 6 лет назад 0
@Bandersnatch, черт возьми, так жаль, что это было глупо с моей стороны. Загрузите правильный файл, примите извинения. https://expirebox.com/download/7fd20ad2d8ec23e37006fe756e57c228.html Tee 6 лет назад 0
Хорошо спасибо. Я думаю, что теперь понимаю. Попробуйте эту формулу в G34 и заполните справа: `= 35 * (SUM ($ G17: G17) / 36)`. Это работает правильно до 31 января 2021 года. Теперь, вам нужно пойти дальше, и увидеть, что общее число незаработанных начинает уменьшаться? Bandersnatch 6 лет назад 0
@Bandersnatch. Спасибо за ваше все время. Хотя ваша формула работает, но она идет не так (как я в итоге использовал свой путь для использования 60 вспомогательных строк). Я буду отмечать вопрос как нерешенный. Действительно ценю все ваши усилия, чтобы помочь. Встретимся снова в другом вопросе, пока. Tee 6 лет назад 0
Я могу ** сделать ** это будет правильным для других столбцов, я просто пытался уточнить, что вам нужно. Bandersnatch 6 лет назад 0
Если вас больше не интересует ваш вопрос, вы можете удалить его. Добавление "брошенных" не способ, которым управлять ими. Máté Juhász 6 лет назад 0

2 ответа на вопрос

1
Bandersnatch

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

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

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

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

enter image description here

** Большое спасибо ** за все усилия. Как я могу купить тебе кофе? __шутки в сторону__ :) Tee 6 лет назад 0
Очень рад помочь. Извините, что так долго. Кофе? Нет, спасибо, но вы можете отправить мне виртуальный бурбон. :-) С наилучшими пожеланиями. Bandersnatch 6 лет назад 0
0
Mario J.

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

  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)

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

Похожие вопросы