Dave simplificou a pergunta de Mikey, por isso não é tão aberto. Deixe-me reafirmar. Nós sabemos quando um contrato começa e termina. Existem regras para decidir quando a fase de receita começa e termina. Queremos encontrar duas coisas:
- Quantos meses há na fase de receita e
- Quanta receita ocorre em 2016.
Eu vou encontrar a receita que ocorre no mesmo ano do início, qualquer ano que seja.
Eu já defini nomes para as seguintes células. Contract.value é definido como 1000.
Start $C$3
Stop $D$3
Months $E$3
Start e Stop são calculados a partir das datas de início e término do contrato. Precisamos do número de meses de Start a Stop para que possamos encontrar a "igualdade de receita em fases". É muito fácil. Use DATEDIF ou verifique Como calcular o número de meses entre duas datas no Excel para obter um método alternativo. Aqui está a fórmula para a célula E3 .
=DATEDIF(Start,Stop,"M")+1
O +1 faz o número de meses inclusive do primeiro e do último mês. No exemplo, há 9 meses de receita de julho de 2016 a março de 2017.
Veja como encontrar a receita em 2016, o ano em que a fase de receita começa.
Primeiro, encontre o número de meses em 2016. Cell C2 calcula:
=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1
Calcule o número de meses multiplicado pela receita de um mês na Fase de receita. Cell D2 faz isso:
=C2*Contract.value/Months
A combinação de C2 e D2 em uma única fórmula mostra o mesmo resultado na célula E2 .
=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months
Existem algumas desvantagens nessa abordagem. E se você quiser encontrar a receita em 2017? A fórmula para 2017 segue o mesmo padrão de C2 , mas não é o mesmo. As coisas ficam confusas se a Fase de Receita se estender por mais de dois anos.
O problema pode ser generalizado fazendo uma tabela de amortização simplificada e usando Tabela Dinâmica para resumi-la. Start , Stop e Months são tudo o que é necessário para criar uma lista de meses e receita. A tabela dinâmica localiza a receita para cada ano na fase de receita.
A linha 2 é especial.
Aqui estão as fórmulas para A2:C2 .
=Start
=YEAR(A2)
=Contract.value/Months
Use as fórmulas para A3:C3 to Fill Down para preencher a lista. Preencha o número de linhas para permitir os maiores meses possíveis na fase de receita. É fácil adicionar mais.
=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")
O "truque" está em A3 . Quando a data na coluna A da linha anterior é posterior a Stop , a célula na coluna A está em branco e todas as linhas seguintes estão em branco, portanto, haverá uma linha para cada mês na Fase de receita - não mais, não menos. O truque é útil quando você deseja fazer uma lista, mas não sabe o número de linhas antecipadamente.
Para criar a tabela dinâmica, selecione a lista inteira, incluindo as linhas em branco. Ao selecionar a lista inteira, a tabela dinâmica ainda funcionará se você alterar as datas do contrato. Use o campo Year para a linha da tabela dinâmica e Sum of Revenue e Count of Year para os campos de resumo.