Aging List Excel

Aging List Excel

Objetivo: Demonstrar como criar um relatório de Aging List de contas a receber.

Este artigo foi uma ideia do amigo Rodrigo Bernardo a quem eu agradeço


O Aging List é uma análise da carteira de clientes com um agrupamento de datas de 30 em 30 dias, permitindo desta forma demonstrar os títulos vencidos ou a vencer para que seja realizada uma análise de um dos principais ativos da empresa.

Para a criação da sua análise de carteira de clientes, é necessário que você tenha no mínimo os campos em uma tabela:

  1. Vencimento: Vencimento da parcela;
  2. Valor: Valor total do vencimento sem multa e juros.

Na nossa planilha exemplo, colocamos ainda outros campos:

  1. Razão social: Razão social do cliente;
  2. Representante: O equivalente ao vendedor que efetuou a venda ao cliente;
  3. Vencimento: Vencimento da parcela;
  4. Valor: Valor total a ser recebido sem multa e juros;
  5. Inferior: Agrupamento inferior das parcelas;
  6. Superior: Agrupamento superior das parcelas.

Os campos 5 e 6 servem para a análise das informações do contas a receber. Vemos que é muito difícil chegar a alguma análise com os dados brutos, por este motivo, criamos um agrupamento destas informações separando estes vencimentos em grupos de 30 em 30 dias.

Para o limite superior foi utilizada a função Excel:

=SE(C2=HOJE();30;ARREDONDAR.PARA.CIMA((C2-HOJE())/30;0)*30)

Aonde temos por partes:

  • =SE(C2=HOJE();30; -> Se a data for igual a hoje então 30 senão…
  • ARREDONDAR.PARA.CIMA((C2-HOJE())/30;0)*30) -> Esta função realiza o cálculo das diferenças de data através de C2-HOJE() e depois divide este valor por 30, o valor é arredondado para cima e depois é multiplicado novamente por 30, gerando desta forma um múltiplo de 30.

Na função inferior temos:

  • =SE(F2=30;0;SE(F2<0;F2+29;F2-29)) -> Esta função identifica se o valor do limite superior é igual a 30, se for o valor do limite inferior é setado para 0, senão é realizada uma outra verificação para identificar se o valor é menor que 0, se for soma-se 29, senão subtrai-se 29 do número. Isto é necessário para que quando os grupos forem negativos o limite inferior fique setado corretamente.
Com esta base pronta é possível utilizar tabelas dinâmicas para criar análises ricas como as que seguem como exemplos nas imagens:
Veja também como criar tabelas dinâmicas e formatações condicionais nos artigos com vídeos: http://guiadoexcel.com.br/tabelas-dinamicas ou http://guiadoexcel.com.br/formatacao-condicional-excel-2007-e-2010.

Baixe a planilha

Abraço

Marcos Rieper