Aprenda criar uma análise completa e passo a passo de títulos pagos e a receber no Power BI. Construa seu próprio fluxo de caixa e analise as inadimplências.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Na aula de hoje eu quero te mostrar um exemplo completo de análise de títulos pagos e a receber no Power BI.
Vou te ensinar passo a passo, detalhando cada etapa do tratamento de dados e da construção dessa análise de fluxo de caixa.
Isso permitirá que você acompanhe o que deveria receber em relação ao que realmente está recebendo, possibilitando o cálculo da inadimplência dos seus clientes.
Além disso, veremos como apresentar essa análise por meio de um gráfico de área no Power BI, facilitando muito a compreensão visual.
Portanto, baixe o material disponível e vamos aprender como criar nossa análise de títulos pagos e a receber no Power BI.
Nós temos uma base de dados contendo o histórico de vendas da empresa. A partir desses dados, faremos uma projeção do quanto deveríamos receber a cada mês e compararemos com os valores efetivamente recebidos.
Essa análise nos permitirá calcular a inadimplência dos nossos clientes e, assim, avaliar nosso fluxo de caixa.
Nossa base de dados contém informações sobre o cliente, método de pagamento, número de parcelas, data da compra, valor de cada parcela, produto adquirido e o total de parcelas, sendo 0 correspondente ao pagamento à vista e 12 o máximo de parcelamento.
Para visualizar, vamos começar filtrando, por exemplo, o cliente13017.
Podemos observar que esse cliente já concluiu o pagamento de todas as 12 parcelas referentes ao Produto B que adquiriu.
No entanto, devido às informações disponíveis apenas até junho de 2022, nosso gráfico de pagamentos realizados (Valor Pago) será gerado somente até essa data.
Agora que compreendemos a estrutura da nossa base de dados e o escopo do projeto, podemos passar para a prática e construir nosso arquivo a partir do zero.
A primeira etapa do nosso projeto será trazer a base de dados para dentro do Power BI. Para começar, clique em Pasta de Trabalho do Excel e selecione o arquivo Vendas.xlsx.
Na janela que se abrirá, escolha a planilha com a qual trabalharemos e clique em Transformar Dados para carregá-la no Power Query. Isso nos permitirá verificar os dados e realizar edições, caso seja necessário.
Dentro do Power Query, vamos renomear a planilha para Parcelamentos.
Feito isso, podemos agora avançar para a criação da lógica do nosso projeto.
Nosso objetivo é criar um gráfico composto por duas partes. A primeira parte corresponde aos pagamentos já efetuados, ou seja, aqueles que já foram recebidos. A outra parte do gráfico representa nossa projeção, um cenário ideal no qual todos os pagamentos de parcelas seriam feitos corretamente.
Para conseguir essas duas informações, precisaremos dos seguintes dados: informações sobre os pagamentos realizados até o momento e as expectativas de pagamento. Até agora, nossa base de vendas já nos fornece informações sobre o que foi pago até junho de 2022.
Para criar a projeção do que esperamos receber, iremos criar uma cópia da tabela de dados existente e gerar uma projeção com base nessas informações.
Para ilustrar, vamos utilizar o cliente 777 como exemplo:
Esse cliente optou por um parcelamento em 12 vezes, e até o momento, ele realizou o pagamento de 10 parcelas, todas dentro do prazo. No entanto, há a possibilidade de que ele efetue ou deixe de efetuar os pagamentos das próximas duas parcelas.
Portanto, para criar nossa projeção, consideraremos que todos os clientes, independentemente do número de parcelas que escolheram, farão os pagamentos pontualmente todos os meses. Para calcular isso, precisamos apenas da informação referente à primeira parcela do cliente.
A partir da primeira parcela de um cliente, conseguimos determinar em quantas vezes a compra foi parcelada. Com essa informação, podemos replicar a linha correspondente ao pagamento da primeira parcela pelo número total de parcelas (por exemplo, 12 vezes, sendo a primeira linha a original e as outras 11 repetidas).
No exemplo acima do cliente777 mencionado anteriormente, teremos 12 linhas de parcelamento completas até o dia 23/08/2022, considerando que ele efetuará todos os pagamentos. Essa mesma lógica será aplicada a todos os clientes.
Como vimos, precisaremos de duas tabelas: uma contendo os pagamentos reais, que já está pronta, e outra refletindo o cenário ideal daquilo que esperamos receber.
Para fazer isso, duplicaremos a nossa tabela Parcelamentos dentro do Power Query. Isso pode ser feito facilmente clicando com o botão direito do mouse sobre a consulta Parcelamentos e selecionando a opção Duplicar.
Posteriormente, iremos renomear a tabela original para Parcelamentos (Pago) e a tabela recém-criada para Parcelamentos (A Pagar).
Dado que nosso foco na análise é em relação aos títulos pagos e aqueles a receber, a fim de calcular a inadimplência, não estamos interessados em clientes que já efetuaram o pagamento à vista, pois isso não afetará a análise. Portanto, podemos remover da tabela aqueles que já pagaram à vista.
Vamos aplicar um filtro nas duas tabelas, a partir da coluna Total Parcelas, para selecionar apenas os clientes com parcelamentos superiores a 1. Afinal, se o número de parcelas for igual a 1, significa que o cliente pagou à vista. Para realizar isso, basta selecionar a coluna e desmarcar o valor 1.
Além disso, na nossa tabela Parcelamentos (A Pagar), podemos eliminar as linhas que não correspondem à primeira parcela. Porque a partir da primeira parcela, projetaremos o número total de linhas para cada cliente, considerando que ele fará todos os pagamentos.
Manter as linhas relacionadas aos pagamentos das parcelas 2, 3 e assim por diante só complicaria o processo de geração da nossa projeção, em vez de ajudar.
Portanto, na nossa tabela Parcelamentos (A Pagar), teremos apenas as linhas correspondentes à primeira parcela. Cada cliente e cada produto comprado aparecerão apenas uma vez.
A partir dessa tabela, para cada cliente e produto comprado, precisamos replicar a primeira linha da parcela pelo número de parcelas em que o cliente dividiu a compra.
Em outras palavras, desejamos pegar cada linha da nossa tabela e repeti-la n vezes, onde n é a quantidade de parcelas que o cliente escolheu para dividir a compra. Por exemplo, para um cliente que dividiu a compra em 12 vezes, teremos a primeira linha e mais 11 linhas correspondentes às parcelas restantes.
Para realizar todo o processo de pegar as linhas da tabela e replicá-las de acordo com o número de parcelas escolhido pelos clientes, utilizaremos uma função chamada Table.Repeat.
Vamos adicionar uma nova coluna a nossa tabela Parcelamentos (A Pagar). Para isso, clique em Adicionar Coluna e depois em Coluna Personalizada.
Essa opção nos permite passar uma fórmula personalizada, na qual utilizaremos a função Table.Repeat:
=Table.Repeat(Table.FromRecords({[Col1=1]}),[Total Parcelas])Essa fórmula está criando uma lista de valores (uma tabela) para cada linha da nossa tabela original e essa lista é armazenada na coluna Personalizar.
Mas o que são essas tabelas? Essas tabelas são, na verdade, listas de valores com um número de linhas igual ao número de parcelas especificado em Total Parcelas. Ou seja, para o primeiro cliente, teremos uma tabela com 12 linhas.
Uma linha para cada parcela que o cliente possui.
Para expandir essa tabela e, assim, termos um número de linhas igual ao total de parcelas para cada cliente, clique no ícone das duas setas (uma para cada lado) na coluna Personalizar e selecione OK.
Dessa forma, teremos a linha correspondente à primeira parcela de cada cliente replicada pelo número total de parcelas.
Agora que já expandimos nossa tabela, podemos excluir a coluna Personalizar.
Agora que já replicamos as linhas, é hora de criar uma coluna de índice que nos ajudará a controlar e organizar as informações na nossa tabela Parcelas (A Pagar).
Criar essa coluna é um procedimento simples. Basta seguir até a guia Adicionar Coluna > Coluna de Índice > De 1 para que a coluna de índice comece com o número 1.
Para evitar problemas ao criar nossas fórmulas no Power BI, vamos renomear essa coluna para Indice, sem acento mesmo.
Com isso, finalizamos nossas edições e tratamento de dados no Power Query. É importante notar que a coluna Data Compra ainda está sendo repetida, ou seja, temos todos os pagamentos no mesmo dia e mês para todas as parcelas.
Essa etapa será ajustada dentro do Power BI. Basta clicar em Fechar e Aplicar na guia Página Inicial para levar nossa base de dados para o Power BI.
Dentro do Power BI, na guia de dados, já podemos visualizar nossas duas tabelas: Parcelamentos (Pago) e Parcelamentos (A Pagar).
No entanto, ainda precisamos encontrar uma maneira de ajustar a coluna Data Compra. Para fazer isso, é importante criar uma coluna que nos informe qual é o número da parcela associado a cada linha da tabela.
Isso nos permitirá desenvolver uma fórmula para deslocar a data da compra de acordo com o número da parcela, menos 1 (número da parcela – 1). Por exemplo, na quinta parcela, o mês na coluna Data da Compra deve ser o mês da primeira parcela mais 4 meses.
Para isso, precisamos criar a nossa coluna Parcela, que indicará o número da parcela para cada linha.
A lógica para essa coluna será a seguinte: vamos aplicar uma fórmula que verificará o cliente e o produto. Se for a primeira vez que o cliente aparece para aquele produto, o número da parcela será 1; na segunda vez, será 2, e assim por diante.
Portanto, clique com o botão direito na tabela e selecione Nova Coluna. Vamos nomear essa coluna como Parcela.
Para calcular o número da parcela, utilizaremos a função CALCULATE do Power BI, pois faremos uma contagem com base em uma condição. Contaremos quantas parcelas existem com base em duas condições: o cliente e o produto. Nossa fórmula será a seguinte:
Parcela = CALCULATE(
COUNTROWS('Parcelamentos (A Pagar)'),
FILTER(
'Parcelamentos (A Pagar)',
'Parcelamentos (A Pagar)'[Cliente] = EARLIER('Parcelamentos (A Pagar)'[Cliente])&&
'Parcelamentos (A Pagar)'[Produto] = EARLIER('Parcelamentos (A Pagar)'[Produto])&&
'Parcelamentos (A Pagar)'[Indice]<=EARLIER('Parcelamentos (A Pagar)'[Indice])
)
)Estamos contando as linhas da nossa tabela, por isso usamos a função COUNTROWS , passando a nossa tabela Parcelamentos (A Pagar) como argumento.
Também aplicamos alguns filtros para essa contagem. Como estamos trabalhando com um filtro mais complexo, utilizando outras funções para filtrar as informações, utilizamos a função FILTER.
Nosso filtro ficará da seguinte forma:
Vamos filtrar os valores da tabela Parcelamentos (A Pagar)
Só queremos que ele filtre apenas o cliente que está aparecendo naquela linha. Para fazer isso, utilizamos a função EARLIER e passamos a coluna exata que contém o valor que queremos capturar naquele momento:
‘Parcelamentos (A Pagar) ‘ [Cliente] = EARLIER(‘Parcelamentos (A Pagar) ‘ [Cliente])
Além disso, como desejamos aplicar mais de um filtro, utilizamos os símbolos && para indicar que queremos combinar mais de uma condição e passamos nosso segundo critério, que é que o produto seja igual ao produto atual:
‘Parcelamentos (A Pagar) ‘ [Produto] = EARLIER(‘Parcelamentos (A Pagar) ‘ [Produto])&&
Por fim, adicionamos uma última condição que verifica quantas vezes aquela pessoa apareceu até aquele momento, usando o índice para verificar do índice atual para trás.
‘Parcelamentos (A Pagar) ‘ [Indice]<=EARLIER(VendasParceladas[Indice])
Após esses passos, nossa coluna Parcela estará criada e indicará corretamente o número correspondente da parcela.
Agora que temos as parcelas, precisamos ajustar os meses das datas de pagamento, realizando o deslocamento dos meses. Vamos criar uma coluna chamada Data Prevista Pagamento e compor uma data. A fórmula para isso será a seguinte:
Data Prevista Pagamento =
DATE(
YEAR('Parcelamentos (A Pagar)'[Data Compra]),
MONTH('Parcelamentos (A Pagar)'[Data Compra]) + 'Parcelamentos (A Pagar)'[Parcela] - 1,
DAY('Parcelamentos (A Pagar)'[Data Compra])
)Nessa fórmula, utilizamos a função DATE para compor a nossa data, pegando o ano (YEAR) da coluna Data da Compra, o dia (DAY) da coluna Data da Compra e o mês que precisávamos calcular. Para calcular o mês, somamos o valor da coluna Parcela ao mês da coluna Data da Compra, subtraindo 1.
Dessa forma, obtemos a data exata em que deveríamos receber cada parcela.
Como a coluna está atualmente definida como Data/hora, podemos alterá-la para ser somente a data, pois não temos interesse na informação de hora.
Agora, temos a data prevista para cada pagamento na nossa tabela Parcelamentos (A Pagar), enquanto na outra tabela Parcelamentos (Pago), temos as datas em que cada parcelamento foi efetivamente pago.
Para construir nosso gráfico utilizando as informações de duas tabelas, é preciso antes determinar um relacionamento entre as tabelas. No entanto, como se tratam de duas tabelas de fatos, não podemos fazer isso diretamente.
Para resolver isso, vamos criar uma tabela calendário. Vamos criar uma de forma simples usando a função CALENDARAUTO().
Calendario = CALENDARAUTO()Além disso, vamos alterar o Tipo de Dados da coluna para ser apenas Data e definir o Formato como data abreviada.
Com nossa tabela de calendário pronta, podemos criar os relacionamentos entre as tabelas. Vamos relacionar a coluna Datas da tabela Calendario com a coluna Data Prevista Pagamento da tabela Parcelamento (A Pagar) e com a coluna Data Compra da tabela Parcelamentos (Pago).
Com nossas tabelas prontas e relacionadas, podemos construir nosso gráfico para realizar a análise ao longo do tempo e verificar a inadimplência de nossos clientes.
No eixo Y, colocaremos o Valo Parcela de ambas as tabelas Parcelamentos (A Pagar) e Parcelamentos (Pago). No eixo X, usaremos as datas da tabela Calendario, exibindo apenas informações de ano e mês.
Agora, podemos realizar algumas formatações para melhorar a visualização do nosso gráfico. Podemos alterar a cor das linhas na seção Visual.
Também selecionaremos as colunas Valor da Parcela na nossa guia de Dados e definiremos para que os valores sejam exibidos em reais.
Mais uma vez, em Formatar Visual, configuraremos os valores para serem exibidos nos Rótulos de Dados e formatados para mostrar as unidades em milhares..
Para calcular e exibir a inadimplência em nosso gráfico, primeiro criaremos uma medida chamada Total Pago na nossa tabela Parcelamentos (Pago), que será definida como:
Total Pago = SUM('Parcelamentos (Pago)'[Valor Parcela])Na tabela Parcelamentos (A Pagar), criaremos a medida Valor a Pagar:
Valor a Pagar = SUM('Parcelamentos (A Pagar)'[Valor Parcela])Agora podemos criar a medida Inadimplência em qualquer uma das tabelas da seguinte forma:
Inadimplência = ([Valor a Pagar] - [Total Pago]) / [Valor a Pagar]Selecionaremos a formatação dessa medida como percentual e a adicionaremos como Dicas de Ferramentas no nosso gráfico.
Dessa forma, ao passarmos o mouse sobre um determinado período em nosso gráfico, ele exibirá o valor que deveríamos receber, o valor recebido e a porcentagem de inadimplência.
Para melhorar a visualização do nosso gráfico, renomearemos Valor Parcela para Valor Pago e Valor Parcela para Valor Projetado. O Valor Projetado refere-se à tabela Parcelas (A Pagar) e o Valor Pago à tabela Parcela (Pago).
Isso permitirá que você realize uma análise completa de títulos a pagar, títulos a receber e a porcentagem de inadimplência.
Na aula de hoje, eu te ensinei como realizar uma análise de títulos pagos e a receber no Power BI de maneira completa. Dessa forma, você pode ter um controle eficaz sobre o valor que deveria receber em cada mês e o montante que realmente está recebendo, além de conseguir calcular o percentual de inadimplência facilmente.
Durante toda essa aula, você teve a oportunidade de praticar diversas fórmulas no Power BI, aprender sobre o tratamento de dados no Power Query e criar um gráfico de área que permite uma análise clara e concisa.
Esse foi um case real do mercado de trabalho, e com certeza você pode evoluir muito se continuar praticando e tentando desenvolver e aprimorar o projeto por conta própria posteriormente.
Você pode usar esse projeto como base para construir o fluxo de caixa da sua empresa, do seu negócio, ou adaptá-lo para se adequar à área que melhor se encaixe na sua realidade.
Para acessar outras publicações de Power BI, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador