Как рассчитать доход в равной степени на основе даты начала и окончания?

• Ячейка A1: Дата начала контракта
• Ячейка B1: дата окончания контракта

• Ячейка C1: индекс месяца, когда начался контракт:

  =YEAR(A1-15) * 12 + MONTH(A1-15) + 1

• Ячейка D1: индекс месяца по окончании контракта:

  =YEAR(B1-15) * 12 + MONTH(B1-15) + 1

• Ячейка E1: оплата за период при условии, что сумма контракта составляет 1000 долларов США

  =1000/(D1-C1)

• Ячейка F1: количество месяцев в 2016 году

  =24205 - C1

• Ячейка G1: общая оплата за 2016 год

  =F1*E1

Дейв упростил вопрос Майки, чтобы он не был таким открытым. Позвольте мне повторить это. Мы знаем, когда контракт начинается и заканчивается. Существуют правила для принятия решения о том, когда фаза дохода начинается и заканчивается. Мы хотим найти две вещи:

1. Сколько месяцев в фазе дохода, и
2. Сколько выручки происходит в 2016 году.

Я собираюсь найти доход, который происходит в тот же год, что и в начале года, в каком бы году это ни было.

Я определил имена для следующих ячеек. Contract.value определяется как 1000

Start  $C$3
Stop   $D$3
Months $E$3

Start а также Stop рассчитываются из даты начала и окончания контракта. Нам нужно количество месяцев от Start в Stop таким образом, мы можем найти "фазирование равного дохода". Это действительно легко. Используйте DATEDIF или установите флажок Как рассчитать количество месяцев между двумя датами в Excel для альтернативного метода. Вот формула для клетки E3,

=DATEDIF(Start,Stop,"M")+1

+1 делает количество месяцев включительно первого и последнего месяцев. В этом примере есть 9 месяцев дохода с июля 2016 года по март 2017 года.

Вот как найти доход в 2016 году, в котором начинается фаза доходов.

Сначала найдите количество месяцев в 2016 году. Сотовый C2 вычисляет это:

=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1

Вычислите количество месяцев, умноженное на доход за один месяц в фазе дохода. клетка D2 Является ли:

=C2*Contract.value/Months

Объединяя C2 а также D2 в одну формулу показывает тот же результат в ячейке E2,

=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months

У этого подхода есть некоторые недостатки. Что если вы хотите найти доход в 2017 году? Формула для 2017 года следует той же схеме, что и C2, но это не то же самое. Ситуация становится беспорядочной, если этап доходов охватывает более двух лет.

Проблему можно обобщить, составив упрощенную таблицу амортизации и используя сводную таблицу для ее суммирования. Start, Stop а также Months это все, что нужно, чтобы составить список месяцев и доходов. Сводная таблица находит доход за каждый год в фазе дохода.

Строка 2 особенный.

Вот формулы для A2:C2,

=Start
=YEAR(A2)
=Contract.value/Months

Используйте формулы для A3:C3 в Fill Down заполнить список. Заполните количество строк, чтобы учесть как можно больше месяцев в фазе дохода. Легко добавить больше.

=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")

"Трюк" в A3, Когда дата в столбце A для предыдущего ряда после Stop ячейка в столбце A пусто, и каждая следующая строка пуста, поэтому в фазе дохода будет по одной строке для каждого месяца - не больше, не меньше. Трюк полезен, когда вы хотите составить список, но заранее не знаете количество строк.

Чтобы создать сводную таблицу, выберите весь список, включая пустые строки. При выборе всего списка сводная таблица будет работать, даже если вы измените даты контракта. Использовать Year поле для строки сводной таблицы и Sum of Revenue а также Count of Year для сводных полей.