Se você trabalha com vendas parceladas é importante que saiba, para efeito de planejamento, quando vai receber as parcelas daquela sua venda.
Imagine uma venda de R$1.000,00 realizada no dia 13/07/2020 em 2x. Os prováveis dias de recebimento das duas parcelas serão 13/08/2020 e 13/09/2020. A data de Setembro, no entanto, cai em um domingo.
Como prever estes detalhes no Excel? Ou seja, como usar o Excel para chegar na data de “30 dias” (sempre no dia 13 dos próximos meses) e encontrar se o pagamento cairá em um dia útil ou não? É sobre isso que trataremos no artigo de hoje.
Primeira etapa: a base de feriados
Ter uma base consolidada de feriados é fundamental no dia-a-dia do negócio. Já tratamos aqui no site sobre isso, conforme você pode relembrar clicando no link.
Há uma base de feriados da Anbima, que utilizamos como referência. Por mais que ela não tenha feriados estaduais e municipais, ela é uma boa referência. Nada impede, também, de você ter uma base própria de feriados locais e adicionar junto a essa.
A base de feriados da Anbima é facilmente encontrada clicando aqui.
Ao baixar o arquivo, crie uma aba chamada “Feriados” no seu arquivo Excel. Jogue os feriados consolidados lá, copiando e colando a base original. Na frente da base (mais precisamente na coluna D) inclua uma coluna apenas com o número 1. Isso servirá para consultarmos a data nessa base e atribuirmos 1 se foi feriado (e um simples SEERRO dará 0 se não o for).
O modelo conforme sugerido está mostrado na Figura 1.
Segunda etapa: construindo a base principal
Chamaremos de “Base” uma nova aba onde faremos os cálculos para nos entregar a informação que precisamos. A figura 2 traz, de modo antecipado, como ficará o nosso material (alerta de Spoiler):
O que temos aqui:
- Na coluna A, temos a informação de Data: iniciamos com uma data qualquer (que precisa ser antes dos fluxos de caixa que serão pedidos). No caso, começamos por 01/07/2020, já que a data da venda foi 13/07/2020. Essa coluna trará todas as datas do futuro, consultando se o dia é útil ou não
- Na coluna B, temos a informação de Dia.Semana: com fórmulas do Excel, conseguimos saber qual dia da semana cairá aquela data
- Na coluna C, temos a informação de Feriado: procuraremos na base de feriados da Anbima se o dia é feriado ou não
As outras colunas são fórmulas as quais entraremos posteriormente.
O que são as colunas
Sabendo o conteúdo de cada uma das colunas, vamos avançar nos cálculos que estão contidos nelas.
A coluna A, por exemplo, traz todas as datas futuras, a partir de uma determinada data tomada como referência.
O procedimento é: incluímos na célula A2 uma data inicial (conforme explicado, 01/07/2020) e nas células abaixo sempre somamos 1 à célula anterior. Dessa forma, na célula A3 teremos =A2+1 e assim sucessivamente.
A figura 3 ilustra esta etapa.
A coluna B consulta qual o dia da semana daquela data. Há uma função específica para isso, a DIA.DA.SEMANA. Para utilizá-la, basta informarmos uma data no formato do Excel que ela nos trará a informação de qual dia da semana aquela data cai. Os números variam de 1 a 7, sendo 1 o domingo e 7 o sábado (felizmente, os dias da semana serão intuitivos: 2 para segunda, 3 para terça, etc…). É possível mudar essa referência (ou seja, mudar o modelo dos números citado), mas não abordaremos aqui.
A Figura 4 mostra a utilização desta função.
A coluna C nos traz a informação se a data procurada é feriado ou não, trazendo 1 para feriados e 0 para não feriados. Para isso, usaremos a base de feriados que consolidamos na base anterior. Faremos uma PROCV, buscando a data na primeira coluna e retornando o conteúdo da quarta coluna. Se você se atentar à Figura 1, notará que a quarta coluna é o número 1 que colocamos na frente de todas as datas. Isso significa que se uma data for encontrada nessa base de feriados, teremos 1 como resposta. Caso não encontre a data (ou seja, não se trata de um feriado), teremos um erro na fórmula. Esse erro pode ser corrigido com a fórmula SEERRO. No nosso caso, optaremos por, caso a fórmula dê erro, termos como retorno o número 0. Dessa forma, na coluna C teremos valor 1 para feriados e 0 para “não feriados.” Veja a referência da Figura 5.
A coluna D vai resumir tudo isso em um número só. Ela vai credenciar a data a ser usada na nossa análise ou não. As datas que são candidatas a serem “possíveis datas de recebimento do pagamento” são dias úteis (logo, coluna B de Dias da Semana é um valor entre 2 e 6) e datas que não são feriados (logo, coluna C de Feriados é 0). Em outras palavras, a data será desqualificada se for ou sábado (coluna B = 7), ou domingo (coluna B = 1) ou feriado (coluna C = 1). Em qualquer um destes casos, o valor da coluna D deve ser 0 (essa será nossa referência como dia “não útil”).
A figura 6 representa esta fórmula:
A coluna final
O que temos no momento: uma coluna (a “D”) que traz 1 se o dia for útil e 0 se não for útil (ou seja, sábado, domingo ou feriado).
Criaremos uma coluna final, a coluna E, que vai trazer o próximo dia útil. Pensando no modelo do Excel, deveremos procurar o primeiro dia, a partir daquela data, em que a coluna D traz um valor de 1.
Este detalhe é fundamental para termos feito a planilha da forma que fizemos: imagine um feriado prolongado, possivelmente o Carnaval (ou algum feriado local que venha antes de um nacional, ou algo do tipo). Temos um sábado, domingo que são dias não considerados na análise. Temos na sequência uma segunda e terça que são dois feriados (no exemplo). Todas estas datas serão marcadas como “0” na coluna D. É crucial procurarmos o primeiro “1” para fazermos como dia útil, o que seria uma tarefa bem sinuosa se não usássemos recursos mais avançados.
O recurso avançado que usaremos é o famigerado CORRESP com DESLOC.
A fórmula que usaremos na célula E3 é:
=DESLOC(A2;CORRESP(1;D3:D9;0);0)
Pensando por etapas:
- Em CORRESP(1;D3:D9;0), localizamos o número 1 no intervalo de células entre D3 e D9. A referência inicial é sempre a linha daquela célula (no caso, 3), pois podemos encontrar o 1 ali mesmo, caso o dia seja útil. Caso contrário, temos uma chance de encontrar um número 1 nos próximos 6 dias. É improvável que tenhamos final de semana e feriados prolongados por tanto tempo
- A fórmula DESLOC(A2;CORRESP(1;D3:D9;0);0) vem complementar a etapa anterior. Como CORRESP(1;D3:D9;0) nos dá um número de posição (por exemplo, posição 2 significa que o 1 foi encontrado na segunda posição do intervalo D3:D9), esse número será utilizado para deslocar, a partir da célula A2, a exata quantia de dias para chegarmos no primeiro dia útil
A figura 7 representa a fórmula em questão.
Com isso, temos na coluna E a data referente ao próximo dia útil da data que está na coluna A. Note que na maioria dos casos temos a coluna E exatamente igual à coluna A (dias da semana, sem feriado), mas que isso muda se estamos em sábado, domingo ou feriado. O exemplo está na figura 8.
Finalizando o processo
Você deve escolher um local para fazer o procedimento de “saída” das datas. Como visto na Figura 2, optamos por fazer o procedimento na coluna L.
Nessa etapa, você precisa conhecer a função DATAM. A função DATAM é interessantíssima, e quase resolve o nosso problema de forma integral. Ela pega qualquer data e joga para o mês seguinte. Ou seja, se temos uma data cujo dia é 13, por exemplo, ela traz como retorno todos as datas com dia 13 dos meses seguintes. No nosso caso, estando a data 13/07/2020 na célula L3, basta fazermos DATAM(L3;1) para avançarmos 1 mês e irmos na data 13/08/2020, ou fazermos DATAM(L3;2) para avançarmos 2 meses e irmos na data 13/09/2020. De modo idêntico, um DATAM(L3;-3) nos faria regredir 3 meses e iríamos para a data 13/04/2020. Entendeu a lógica?
Por que a fórmula DATAM, sozinha, não resolve o nosso problema? Porque ela não considera apenas dias úteis. Ela simplesmente joga a data para frente (ou para trás) sem nem se interessar se cairá em um sábado, domingo ou feriado. É por isso que ela não resolve sozinha o problema, mas ela nos direciona. Podemos utilizar a fórmula DATAM para chegar na data em que seria naturalmente a data de recebimento, e por meio da nossa tabela auxiliar verificar qual o dia útil seguinte àquela data. E é isso que faremos.
Na célula L6 (data referente à primeira parcela) temos a fórmula:
=PROCV(DATAM($L$3;K6);A:E;5;0)
O que ela faz:
- Calcula qual a data que partirá de 13/07/2020 (L3) e avançará 1 mês (K6)
- Encontrada a data, procura este valor via PROCV na tabela que está em A:E, retornando, quando encontrar o valor na coluna A, o valor da mesma linha na coluna E (representado por 5, no PROCV)
Arrastando a fórmula para baixo, temos a mesma fórmula para as parcelas seguinte. A figura 9 exemplifica a fórmula citada.
Se você sobreviveu até essa etapa, conseguiu compreender como utilizar algumas funções do Excel, de modo bastante inteligente, para saber qual é o próximo dia útil em relação a uma data. Nossa aplicação faz parte da série de Gestão de Negócios, mas a dica pode ser útil para diferentes frentes. De qualquer forma, conhecer qual o próximo dia útil de uma determinada data é fundamental em diversas frentes, e com certeza será assim também no seu Negócio.
Até a próxima!!