Modelo de previsão do Excel para assinaturas recebidas antecipadamente pelos próximos 2 anos

Question

Estou trabalhando em um modelo financeiro que contém:

• Meses em colunas (jan-2020 a dez-2025)
• Número de assinantes por mês (coluna) por pacote em linhas
• Preço de cada pacote em linhas

É fácil fazer o fluxo de caixa como [assinantes mensais (coluna) x por pacote (linha) x preço do pacote (linha), no entanto, estou preso ao fazer declaração de renda porque a receita da assinatura deve ser distribuída pela posse do pacote (que é 2 anos).

Neste exemplo, o contrato a partir de janeiro deve ser dividido por 24 meses, depois complementado com o contrato em fevereiro e assim por diante ... isso deve continuar até que o contrato a partir de janeiro atinja o 24º mês.

Parece que o cálculo da receita para cada mês e pacote só é possível usando uma função de matriz, mas não consigo entender como.

Obrigado.

    

Answer 1

@Tee, caso você ainda esteja por perto, estou postando uma resposta para sua pergunta. Demorou um bom tempo para entender claramente o problema que você está enfrentando e ainda não tenho certeza se estou exatamente correto.

Então, deixe-me declarar o problema que resolvi e espero fornecer a você informações suficientes para modificar a solução se meu entendimento do problema estiver incorreto.

Problema: você deseja calcular uma soma de 35/36 de cada um dos números que começam no G17 da sua planilha e continuam além do AP17. A parte complicada é que, uma vez que há 35 termos em sua soma, o início do intervalo deve se mover para a direita (ou seja, H17, I17 etc.), conforme a fórmula é preenchida à direita.

A discussão a seguir mostrará como calcular a soma e a fórmula final será multiplicada por 35 e dividida por 36.

Solução: Para calcular a soma, é necessária uma fórmula como esta:

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

O formulário "reference" do INDEX () pode ser usado para retornar uma referência de célula, e aqui, o primeiro INDEX () calcula o início do intervalo a ser somado, enquanto o segundo INDEX () calcula o fim do intervalo intervalo.

A soma começa com G17 (coluna 7), para todas as colunas menores que a coluna AP (coluna 42). Começando com a coluna AP, a célula inicial move uma coluna para a direita à medida que a fórmula é preenchida à direita. Então o primeiro INDEX () é:

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

Como exemplo, na coluna AP, o intervalo de soma começa com H17. Coluna 42-34 = 8 = coluna H.

O final do intervalo a ser somado é apenas a coluna atual. Então o segundo INDEX () é:

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

Agora, a soma é:

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

E a fórmula final é:

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

Uma parte da sua planilha com o cálculo é mostrada na figura abaixo. Por favor, comente se você ainda está visitando aqui. Atenciosamente.

    

Answer 2

Você pode fazer isso em duas etapas:

1. Primeiro você calcula Nova renda mensal para cada Dinheiro recebido em seu exemplo com uma divisão simples com 24. Por exemplo:

   a. Coloque = D14 / 24 em D19 (nova renda mensal para as vendas do Pacote 1 de janeiro)

   b. Copie esta fórmula para D20: D21 (pacotes 2 e 3) e depois para todas as outras colunas de E19: E21 em diante

2. Em segundo lugar, você soma todos os Novos rendimentos mensais mas até 24 meses atrás.

   a. Coloque = SUM ($ D19: D19) no D24

   b. Copie esta fórmula para E24: AA24 (do 2º ao 24º mês)

   c. Em AA24, remova o sinal $ para obter a fórmula: = SUM (D19: AA19) e, em seguida, copie-o para AB24 em diante. Agora é fixado para somar os últimos 24 meses.

   d. Copie a linha 24 para as linhas 25 e 26 (para obter as somas dos pacotes 2 e 3)

Aqui estão fotos desta solução: