Controle de cartões de crédito/débito em Excel

Controle de cartões de crédito e débito numa loja. Acontece o tempo todo e é uma necessidade super frequente. Como fazer para controlar o que tem a receber baseado no registro das vendas ? Essa é a pergunta mais completa. Imagine a seguinte situação. Uma pequena loja vende vários produtos com diversos preços diferentes e com modalidades de pagamento diferentes. Vende a vista, em dinheiro, cheques para o dia, cheques pré-datados, duplicatas bancárias, cartões de crédito, cartões de débito, cartões de crédito com parcelamento pela própria loja e com parcelamento pela administradora do cartão. Várias bandeiras de cartão e as vezes até a mesma bandeira em máquinas de POS diferentes.

Cada uma dessas modalidades de pagamento tem um conjunto de características determinas e diversas entre si, tais como :
1 – Prazo para disponibilização do dinheiro. A venda a dinheiro a disponibilização é imediata. O cheque para o dia na verdade levará um ou dois dias para ter o dinheiro disponível. O cheque para 30 dias, o cartão de crédito tem o seu prazo que varia de acordo com a bandeira e as vezes na operadora do POS (Redecard, Cielo, etc). O cartão de débito tem outro prazo (normalmente o dia seguinte) e por aí via.

2 – Taxa de desconto. É um valor ou percentual que é “subtraido” do valor nominal da venda. No exemplo da venda a dinheiro, que não desconta, o cheque pré-datado que será trocado na factoring sofrerá um desconto, o cartão de crédito que cobrar um percentual (taxa de administração) ou o de débito que cobra um valor por transação.

3 – A bandeira/marca da modalidade. Visa, Mastercard, Maestro, VisaEletron, cheque pré, dinheiro, cheque a vista, duplicata. Serve para identificar o tipo de pagamento da transação.

Para atender essa demanda fiz uma planilha em que se registra as vendas, a data, o valor da venda e o tipo de pagamento e uma macro elabora o fluxo de caixa, ou seja, a previsão de recebimentos associados as vendas. A ideia é demonstrar quanto dinheiro irá entrar ao longo dos próximos dias no futuro. A planilha é bem simples. Basta lançar as vendas e depois rodar a macro que fará as contas e os lançamentos. Essa planilha tem alguns itens que precisam ser melhorados e a ideia de postá-la aqui é justamente expô-la a comentários e sugestões. Ao mesmo tempo, vou dar esclarecimentos sobre as dúvidas que aparecem para quem tentar usá-la.

Como funciona a planilha

A planilha tem várias abas. Como dito, lança-se apenas na aba de OPERAÇÔES, que seriam as vendas. Com alguma imaginação é possível lançar compras também. Deixo o desafio aqui.

Na aba de operações, a planilha tem as seguintes colunas
Número da venda -> serve para identificar a transação. pode ser o número da nota fiscal, do cupom fiscal ou do pedido. É preciso ter um número para poder identificar o recebimento e associá-lo a venda.
Bandeira -> é a o tipo de cartão de crédito que será utilizado. Esse tipo é pré-cadastrado na aba de cartões que veremos adiante. Para cada tipo de pagamento, se cria uma bandeira. Pode-se criar quantas bandeiras quiser. As variantes de bandeira associada a operadoras implicam em criação de uma bandeira. Por exemplo, Visa quando recebido pela redecard tem uma taxa de 4%. Já quando Visa é recebido pela Cielo tem taxa de 3,8%, por exemplo. Se isso acontecer, é preciso criar duas “bandeiras” tais como Visa Rede e Visa Cielo.
Data -> é a data em que a venda aconteceu e a partir da qual será calculado o prazo (ou prazos) para recebimento.
Valor -> é fácil de .. alguém advinha o que significa ?
Taxa -> é a taxa de “desconto” que será aplicada a venda. Essa coluna é preenchida automaticamente pela fórmula da planilha. Não a modifique-a
Prazo -> é o prazo previsto para recebimento do valor da venda. Também é calculado automaticamente
Número de parcelas -> é informado para cada venda. Vendas a vista ou no cartão de débito/crédito simples tem uma parcela só.

A aba de cartões contém os parâmetros que serão usados para o preenchimento automático das colunas TAXA e PRAZO da aba anterior. Essa tabela só precisa ser informada uma vez no início do uso. Informe todas as modalidades de pagamento que você tiver. Use a imaginação. Nessa tabela você terá as seguintes colunas :

Bandeira -> é o “código” do tipo de pagamento que o identifica. Não pode ter dois iguais
Taxa -> é o % de desconto aplicado sobre as vendas. Use a imaginação aqui quando for para pagamentos. desafio
Prazo -> é o prazo padrão para recebimento associado as vendas daquela modalidade , em dias

As planilhas de FLUXO e de Resumo são calculadas automaticamente pela macro acionada pelo botão “gera fluxo” na aba operações. Você lança os dados em operações, aperta o botão e pronto, as abas fluxo e resumo são criadas.

Na aba de fluxo vai aparecer os valores a receber em ordem cronológica, mencionando a venda original. Observe que se uma venda foi parcelada em 3 vezes, ela aparecerá 3 vezes, em datas diferentes, conforme os parâmetros de prazo da aba de cartões.

A aba de resumo é uma tabela dinãmica que usa os dados de fluxo. nela você pode fazer sumários por tipo de cartão, por prazo, por data simplesmente arrastando os campos conforme sua necessidade.

50 comentários sobre “Controle de cartões de crédito/débito em Excel

  1. prescizo de uma planilha simples para cadastrar os caixas diários com recebimentos e retiras como almoço, combustível, mercadoria, pag funcionários , vale de funcionários etc …e que as sobras gerem receitas para que se eu for fazer uma compra no atacado possa usar da receita acumulada dia a dia sem prescizar lançar no caixa de um determinado dia tbem como aluguel, contabilidade, impostos, das, pró-labore etc…nos caixas diários tem cartões de créditos recebidos pelos vendedores tbem tinha que colocar no calculo para gerar receitas descontando a taxa da adm dos cartões? sera que essa serve

    • Já ouviu falar do Omie ? http://www.omie.com.br/

      O que você quer é uma planilha bem complexa para registrar operações do dia a dia. É factível, é possível e muita gente usa assim. Só que isso fica muito complicado e frágil numa planilha Excel. Para fazer todas as verificações vai ficar tão complexa que dentro de pouco tempo ela vira um frankenstein que só quem a fez consegue alimentar.

  2. Olá João vi muitos comentários sobre essa planilha. Estou precisando de uma parecida, mas como esse link não está abrindo não sei se a sua atende as minhas necessidades. Haveria a possibilidade de você enviá-la no meu e-mail? evandro_pacheco@yahoo.com.br Desde ja agradeço sua atenção

  3. Boa tarde!
    Poderia me disponibilizar a planilha pelo e-mail sandrapteodoro@gmail.com, pois o link não está funcionando.

    Desde de já lhe agradeço.

    Sandra

  4. Não consigo baixar, clico no link e dá erro.

  5. Olá,
    A planilha ficou perfeita, mas apareceu um ploblema erro tempo execução 13, veja:
    Sub cria_fluxo()

    Dim Area As Range
    Dim destino As Range
    Call Limpa_fluxo
    Set Area = Worksheets(“Operações”).Range(“a2:f2000”)
    Set destino = Worksheets(“Fluxo”).Range(“a2:f6000”)
    j = 1
    i = 1
    While Area.Cells(i, 1).Value “”
    ‘ copia os dados da transação original para a tabela de fluxo, as 6 primeiras colunas são iguais

    For k = 1 To 6
    destino.Cells(j, k).Value = Area.Cells(i, k).Value
    Next k
    ‘ agora vamos calcular as parcelas . o número de parcelas, a data, a taxa e o valor da parcela são os mesmos
    num_parcelas = Area.Cells(i, 7).Value
    data_opera = Area.Cells(i, 3).Value
    wtaxa = Area.Cells(i, 5).Value
    wvalor = Area.Cells(i, 4).Value / num_parcelas
    destino.Cells(j, 8).Value = wvalor
    destino.Cells(j, 9).Value = data_opera + Area.Cells(i, 6).Value
    destino.Cells(j, 10).Value = wvalor * (1 – wtaxa)
    destino.Cells(j, 7).Value = 1
    For k = 2 To num_parcelas
    j = j + 1
    For z = 1 To 6
    destino.Cells(j, z).Value = Area.Cells(i, z).Value

    Linha em destaque: destino.Cells(j, 9).Value = data_opera + Area.Cells(i, 6).Value

    Como devo proceder?

    Por favor, ajude.

    Obrigada

  6. oi boa tarde!!
    usei a tabela para um teste, fiz tudo com explicado na “instrução”, mas trabalho com duas contas diferentes, separei-as na planilha, lancei os dados mas ou “gerar fluxo” só mostra uma parte:/
    gostaria de saber se possível fazer isso que tentei ou se terei que fazer uma planilha pra cada conta.
    e uma curiosidade: o que seria a faixa amarela na aba *fluxo*???
    obrigada!!

    • A planilha não foi concebida para ter duas contas mas pode ser modificada para tal. Quanto a faixa amarela, a ideia é que as coisas que estão ACIMA da faixa amarela serão consideradas. A faixa amarela é apenas um delimitador. O que ficar abaixo da faixa será ignorado.

  7. quando eu clico em gerar fluxo da uma caixa de dialogo nao é possivel gerar macro, o que eu faço

  8. Olá João , gostei muito de sua planilha,fiz um teste com alguns dados e apresentou o seguinte erro:
    Erro em tempo de execução ‘6’: Estouro
    E no deputador:
    wvalor = Area.Cells(i, 4).Value / num_parcelas
    Gostaria muito de utilizar essa planilha.

  9. Olá João, sua planilha é muito interessante, só tenho um problema, na hora do prazo/previsão de recebimento, alguns cartões tem datas de corte(regras), você conseguiria incluir isso na planilha? exemplo o Sodexo ele tem data de corte 7 dias(sexta feira) e paga no próximo dia útil.

    • parece interessante. Isso significa que na aba de bandeiras tem que ter uma marca dizendo qual é o dia de corte e mudar a lógica da planilha para suportar essa facilidade.
      Vou dar uma olhada.

  10. Olá João, como posso descobrir o prazo para cada bandeira? Vi que tu preencheu na aba Cartões diferentes prazos para cada bandeira ou situação de parcelamento. Existe alguma fonte dessa informação? Ou é na experiência mesmo?

    • Valdir, cada bandeira vai negociar com o lojista o prazo padrão. É possível inclusive ter a mesma bandeira com prazos diferentes, se o lojista contratar com “máquinhinhas” diferentes. Cielo, Rede, Pay & Go, cada uma delas vai dar um prazo diferente. Quando o lojista contratar, ele vai ter essa informação.

  11. Bom dia, preciso elaborar uma planilha que mostre os seguintes tópicos: Faturamento, Cartões de Débitos e Créditos, e Dinheiro, tudo que eu concilio no dia dia, terei que colocar nessa planilha.

    Grato.

  12. Como faço para baixar a planilha

  13. Boa tarde!
    Não estou conseguindo baixar a planilha… O link está com problema?

  14. oi boa tarde. quando clico no botao gerar fluxo não acontece nada.
    Oque posso estar fazendo errado ??

  15. Boa tarde, nessa macro, é possível fazer a contagem da Previsão utilizando apenas os dias úteis ? Pois a Cielo não deposita em finais de semana.

    Planilha muito boa, obrigado!

    • A modificação é simples. Primeiro é preciso criar uma tabela de feriados que não são óbvios. Feriados bancários, digamos assim. Pode ser que os feriados variem de cidade para cidade. Em seguida, modifico a macro. COmo já dizia aquele grande programador português, Pero Vaz de Caminha, em se programando, tudo dá.

  16. Fiquei com uma dúvida sobre a planilha:
    o campo “bandeira” não deveria carregar da tabela “cartões”?
    Fica aqui uma sugestão para, ao invés de digitar esse campo pudesse ser lincado.
    Obrigado

  17. bom dia,
    não consigo baixar a planilha você poderia me enviar por email?

    agradeço desde já a atenção.

  18. Bom dia, não estou conseguindo baixar a planilha, você poderia me enviar por email? Obrigado!

  19. Olá! Sua planilha já e ajudou muito, porém precisaria de uma ajuda para incluir na tabela um valor de repasse (comissão). Já que trabalho em uma clínica e repasso 30% do valor recebido integral (sem contar as taxas do cartão). Se puder me ajudar, agradeço.

  20. Olá João,
    Sua planilha foi muito útil, mas encontrei a mesma dificuldade da Priscila. Gostaria de poder baixar a planilha revisada também.

    Obrigada desde já!

  21. Oi João,
    baixei a planilha que estava no link desta página e deu certo!
    Obrigada pela atenção!
    Abraço

  22. Oi João,
    baixei a planilha que estava neste link, e deu certo!😉
    Obrigada pela atenção!
    Abraço!!

  23. Boa tarde João,
    estou usando sua planilha, ela é ótima, mas estou com uma dúvida quanto a taxa do cartão que está sendo descontada ao mês, resultando assim num desconto em cima do outro e uma parcela decrescente. Mas não é assim que recebo da operadora.
    Tem como modificar isso?
    Fico no aguardo.
    Obrigada.

    • Pelo jeito a planilha está descontando errado porque eu entendi errado como desconto seria efetuado. Se você me explicar as regras dos descontos eu posso facilmente modificar a planilha para ela funcionar corretamente. Obrigado pela valiosa informação e pela oportunidade de ajudá-la.

      • Oi João,
        o desconto é feito uma vez só. Ex.:
        uma compra de r$308, em 3x. A taxa do cartão para essa operação é 3,1% e é feito uma vez só, recebo em 3x, mas parcelas iguais de R$ 98,484.
        Aguardo um retorno e muito obrigada pela atenção.

      • A situação da Priscila é a mesma que verifiquei. Se você fizer uma venda de R$100,00 dividida em 10 parcelas e a taxa for 5%, teremos 10 parcelas de R$9,50.
        Se possível, amigo, após a algteração, encaminhe-a ao meu e-mail (lucasbaiao@msn.com).
        Parabéns pela iniciativa, é demasiadamente útil!

Comente aqui ou faça uma pergunta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s