Dos dados brutos à informação útil: como a automação em Excel criou um dashboard de KPIs para varejo de duas marcas de vestuário

Dashboard de KPIs para varejo era o que faltava a duas marcas de vestuário que queriam comparar o desempenho financeiro e operacional das suas lojas. Os números existiam, mas estavam espalhados entre folha de pagamento, sistema de ponto e planilhas de vendas que nunca se cruzavam. A Safira Consultoria uniu essas fontes em um único modelo no Excel 365, com Power Query e VBA. O resultado poupa mais de 50 horas de trabalho manual por mês.

O modelo acompanha receita, fluxo de clientes e eficiência do custo de pessoal por loja e por período. Ele também nasceu pronto para migrar para o Power BI quando o volume pedir. A seguir, mostramos o problema, a construção e um passo a passo para aplicar o mesmo método nas suas lojas.

Principais conclusões

  • As lojas de duas marcas de vestuário guardavam folha de pagamento, ponto e vendas em arquivos separados, que nunca se conectavam.
  • A Safira uniu essas fontes em um dashboard de KPIs para varejo no Excel 365, construído com Power Query e VBA.
  • O painel mostra receita, custo de pessoal como percentual da receita e fluxo de clientes para qualquer loja e qualquer intervalo de datas, e compara as duas marcas lado a lado.
  • A solução poupa mais de 50 horas de trabalho manual por mês entre Financeiro, RH e Operações.
Gerente regional em um estoque de loja de roupas analisa no monitor um dashboard de KPIs para varejo em Excel com receita, custo de pessoal e fluxo de clientes

Resumo do projeto

Item Detalhe
Cliente Guess e Hering
Setor Varejo de vestuário
Solução Sistema Integrado de Análise de Desempenho Operacional e Financeiro
Ferramentas Excel 365, VBA e Power Query (pronto para Power BI)
Prazo Construção inicial e, depois, uma fase com rotatividade e absenteísmo
Departamentos Financeiro, RH, Operações e Diretoria
Impacto Mais de 50 horas de trabalho manual poupadas por mês

Cada marca tinha a sua própria rede de lojas. Por isso, qualquer comparação entre elas exigia montar tudo duas vezes antes de chegar à diretoria. O projeto trocou esse retrabalho por um modelo único, com a mesma estrutura para as duas.

Navegue neste artigo
  1. Resumo do projeto
  2. Por que três fontes de dados não conversavam entre si?
  3. Como montamos o dashboard de KPIs para varejo no Excel
  4. Quais ganhos o novo painel trouxe para as lojas?
  5. Como criar um dashboard de KPIs para varejo: passo a passo
  6. A lição: desenhe em torno de proporções, não de totais
  7. Quer um dashboard de KPIs para varejo pronto para a sua rede?
  8. Perguntas frequentes

Por que três fontes de dados não conversavam entre si?

A folha de pagamento trazia cada funcionário ativo, com cargo, loja e custo mensal total. O sistema de ponto exportava um resumo diário por pessoa: quem trabalhou, quando e por quanto tempo. Já cada loja mantinha a própria planilha de vendas, com receita e fluxo de clientes do dia, lado a lado.

Cada arquivo fazia sentido sozinho. Juntos, porém, quase não respondiam nada. Por exemplo, ninguém conseguia dizer qual loja gastou a maior fatia da receita com pessoal na semana anterior sem abrir várias pastas de trabalho e montar a comparação na mão. E a tarefa se repetia: a cada fechamento, alguém refazia as mesmas tabelas do zero.

O pedido por escrito listava três entregas: receita por loja, fluxo de clientes por loja e custo de pessoal em períodos diferentes, como dia, semana e mês. Tratamos essa lista como ponto de partida, e não como especificação. Depois de mapear os arquivos, ficou clara uma quarta necessidade. Receita e custo de pessoal só fazem sentido juntos.

Uma loja que vende bem ainda pode ser fraca se a folha consome boa parte dessa receita. Da mesma forma, fluxo sem receita não diz nada sobre conversão. Quadro de pessoal sem fluxo também não mostra se um turno estava com gente demais ou de menos. Assim, desenhamos o painel em torno de proporções, e não só de totais.

Havia ainda uma restrição. O varejo não funciona em meses fechados. Promoções começam no meio do mês, e um gerente regional que compara duas campanhas precisa filtrar as datas exatas de cada uma. Então, todo o modelo tinha de aceitar intervalos de datas livres.

Sua rede também refaz as mesmas tabelas de folha, ponto e vendas a cada fechamento? Conte como é hoje e mostramos como unificar.

Falar com um especialista

Diagrama em que folha de pagamento, ponto e vendas das lojas se unem em um único dashboard de KPIs para varejo feito no Excel

Como montamos o dashboard de KPIs para varejo no Excel

Começamos pela base, e não pelo painel. O cliente quase nunca vê essa camada, mas é ela que decide se a ferramenta sobrevive ao primeiro ano.

Primeiro, criamos uma camada estrutural com os dados fixos de cada loja: nome, categoria, faixa de tamanho, receita anual de referência e vagas de quadro autorizadas. Essa camada funciona como um dicionário. Com isso, cada loja entra uma única vez e vale para o modelo inteiro.

Depois vieram três abas de importação, cada uma feita para receber uma fonte sem edição manual: ponto, folha e quadro de pessoal, e receita e fluxo diários das lojas. O Power Query cuida do tratamento, enquanto rotinas em VBA cuidam da coleta e da sequência de atualização.

A grade diária pediu atenção, porque é ali que muita tentativa interna quebra. Em vez de mesclar células em blocos bonitos, demos a cada dia do mês um par de colunas, receita e fluxo, do dia 1 ao dia 31. Célula mesclada parece organizada, mas calcula mal. Uma grade simples, com um valor por célula, calcula sem erro.

No uso, a rotina é curta. Na segunda-feira, o gerente regional abre a pasta de trabalho, escolhe a loja em uma lista suspensa e digita a data de início e a de fim. O painel devolve a receita do período, o custo de pessoal do período e o percentual entre os dois. Logo abaixo ficam três curvas diárias: receita, fluxo de clientes e funcionários ativos. Um pico de fluxo em dia de equipe enxuta é venda perdida, enquanto um pico de equipe em dia fraco é margem perdida.

Embaixo, o arquivo lista cada funcionário da loja, com cargo, custo total e escala. O RH usa essa visão para questionar escalas, e o Financeiro usa os indicadores para questionar custos. Para a diretoria, as duas marcas aparecem lado a lado, sem reformatar nada.

Quais ganhos o novo painel trouxe para as lojas?

O resultado medido foi de mais de 50 horas de trabalho manual eliminadas por mês entre Financeiro, RH e Operações. Antes, essas horas iam para exportar, colar, conciliar e formatar dados.

O ganho menos visível pesa tanto quanto. Quatro áreas agora discutem decisões, e não de quem é o número certo. Quando Operações questiona uma escala e o Financeiro questiona uma proporção de custo, os dois abrem a mesma pasta de trabalho. Também ficou simples comparar duas campanhas: basta digitar duas datas.

Um bom modelo costuma gerar novos pedidos, e este gerou. Em uma fase seguinte, o cliente pediu dois indicadores de RH: rotatividade (turnover) e absenteísmo. Os dois vêm de arquivos por loja. Por isso, ampliamos a rotina de importação em VBA para coletar as novas bases e aplicamos o mesmo filtro de datas livres. Hoje, o cliente lê a rotatividade ao lado do custo de pessoal e da receita da mesma loja, no mesmo período.

Nada precisou ser refeito. Como a base já estava estruturada e a atualização já era automática, os dois novos indicadores entraram no painel sem mudar o visual que a equipe conhecia.

A escolha das ferramentas também olhou para a frente. Excel 365, VBA e Power Query deram ao cliente uma solução que a equipe usou na hora, em programas que a empresa já licenciava. Ao mesmo tempo, o modelo de dados está limpo o bastante para ir para o Power BI quando o volume exigir.

Ao montar um dashboard de KPIs para varejo no Excel em torno de proporções como o custo de pessoal sobre a receita, a Safira Consultoria poupou a duas marcas de vestuário mais de 50 horas de trabalho manual por mês.

Como criar um dashboard de KPIs para varejo: passo a passo

Dá para aplicar o mesmo método com as ferramentas que você já tem. Os passos seguem a ordem que usamos no projeto.

  1. Liste as fontes e o que cada uma traz. Anote de onde vêm receita, fluxo de clientes, folha e ponto, e em que formato cada arquivo chega.
  2. Crie um dicionário de lojas. Em uma aba, dê a cada loja uma linha com os dados fixos, como nome, categoria e tamanho. As outras abas devem citar a loja sempre por esse nome ou código.
  3. Reserve uma aba de importação para cada fonte. Guarde as exportações brutas em abas separadas e nunca digite por cima delas. Faça os cálculos em outro lugar.
  4. Use o Power Query para carregar e limpar os dados. No Excel, vá em Dados, Obter Dados, conecte cada exportação e deixe a consulta repetir a mesma limpeza toda vez. Se as exportações têm as mesmas colunas e ficam em uma pasta, combine tudo em uma consulta; se cada fonte tem um layout diferente, crie uma consulta por fonte.
  5. Evite células mescladas. Mantenha um valor por célula para que fórmulas e filtros funcionem em todos os dias do mês.
  6. Calcule proporções, não só totais. Inclua o custo de pessoal como percentual da receita de cada loja. O total mostra tamanho, enquanto a proporção mostra eficiência.
  7. Adicione um seletor de loja e duas células de data. Use uma lista suspensa do Excel para a loja e duas células para início e fim. Depois, faça cada indicador seguir esse intervalo, e não um mês fixo.
  8. Coloque as curvas diárias juntas. Mostre receita, fluxo e equipe em serviço por dia, uma ao lado da outra, para enxergar picos e buracos.
  9. Decida como será a atualização. Se uma pessoa atualiza o arquivo uma vez por semana, Dados, Atualizar Tudo costuma bastar. Se várias fontes chegam em horários diferentes ou precisam carregar em ordem fixa, uma rotina curta em VBA roda a sequência inteira com um botão.

A lição: desenhe em torno de proporções, não de totais

A Microsoft descreve o Power Query como a tecnologia de conectividade e preparação de dados que permite importar e remodelar dados em produtos como Excel e Power BI. Foi exatamente por isso que o usamos aqui. As mesmas consultas que alimentam o modelo em Excel hoje podem alimentar um relatório no Power BI amanhã.

Nossa experiência acrescenta um ponto que a documentação não traz. Dado limpo é necessário, mas dado limpo ainda não é a resposta. No varejo com várias lojas, a pergunta útil raramente é “quanto?”. Na prática, é “quanto, em relação a quê?”. Custo de pessoal só faz sentido diante da receita, e escala só faz sentido diante do fluxo.

Esse princípio vale para duas marcas ou para vinte lojas. Desenhe primeiro as proporções e os intervalos de datas livres, e deixe os gráficos para o fim. Para os cálculos, as funções de Excel para análise de dados empresariais cobrem quase tudo de que um painel assim precisa. Se inverter a ordem, você terá um painel que parece completo, mas não responde à pergunta do gerente na segunda de manhã. Quando o modelo crescer, vale comparar Power BI ou Excel para relatórios gerenciais antes de migrar. E, se rotatividade e absenteísmo entrarem na conta, as planilhas personalizadas para RH mostram como organizar esses indicadores.

Quer um dashboard de KPIs para varejo pronto para a sua rede?

O passo a passo funciona bem para poucas lojas e fontes limpas. No entanto, quando é preciso juntar folha, ponto e vendas de várias marcas, a construção leva tempo e cada erro sai caro. Uma solução profissional encurta esse caminho.

Nossos especialistas em Excel constroem planilhas personalizadas e modelos de relatório automatizados como este, prontos para ir ao Power BI quando você precisar. Se você prefere o resultado ao projeto, conheça o serviço de especialista em Excel da Safira e veja como transformamos arquivos separados em um único dashboard de KPIs para varejo.

Quer ver receita, custo de pessoal e fluxo de todas as lojas em um só painel? Vamos conversar sobre a sua rede.

Solicitar diagnóstico gratuito

Perguntas frequentes

Quais KPIs usar em um dashboard de varejo?
Neste case, os principais foram receita por loja, fluxo de clientes e custo de pessoal como percentual da receita. Depois, entraram rotatividade e absenteísmo. A proporção entre custo de pessoal e receita costuma dizer mais sobre a eficiência de uma loja do que qualquer total isolado.
Dá para fazer um dashboard de varejo no Excel sem Power BI?
Sim. Este projeto usou Excel 365, Power Query e VBA, ferramentas que o cliente já licenciava. O cuidado é montar um modelo de dados limpo, porque assim a migração para o Power BI fica simples quando o volume crescer.
Como calcular o custo de pessoal sobre a receita?
Some o custo total da folha da loja no período e divida pela receita da mesma loja no mesmo período. O resultado, em percentual, mostra quanto de cada venda vai para a equipe. Compare lojas e períodos sempre pelo mesmo intervalo de datas.
Como comparar lojas de marcas diferentes na mesma planilha?
Coloque as duas marcas na mesma estrutura: o mesmo dicionário de lojas, as mesmas abas de importação e os mesmos indicadores. Assim, o painel mostra uma ao lado da outra sem reformatação. Sem essa estrutura comum, cada comparação vira um trabalho manual novo.
Por que evitar células mescladas em planilhas de indicadores?
Células mescladas atrapalham fórmulas, filtros e classificações. Uma grade simples, com um valor por célula, deixa o cálculo confiável em todos os dias do mês. Neste case, cada dia ganhou um par de colunas próprio, de receita e de fluxo.

Este case foi escrito pela Safira Consultoria, a partir da experiência com mais de 500 clientes e 2.000 projetos em Excel, Power Query e Power BI.

Seja mais um cliente
feliz da Safira

Douglas dos Reis

Douglas dos Reis Silva Junior Especialista em Negócios & Tecnologia Passei mais de 15.000 horas resolvendo o mesmo problema: empresas que pagam caro por ferramentas que usam mal. Sou Douglas dos Reis Silva Junior. Formação em Mecatrônica, Administração de Empresas e Ciências Econômicas pela Faculdade Metodista. Especialista certificado em Desenvolvimento Tecnológico no Ambiente Microsoft (2015) e na metodologia Six Sigma / DMAIC (2020). Atuo no ecossistema Microsoft 365 e Google Workspace desde 2015. Comecei em 2012, dentro das áreas de qualidade e processos da General Electric, Vestas Energia, Aliansce Sonae, Festo e G4S. Foi ali que entendi a regra que carrego até hoje: o problema raramente é a ferramenta, é o desenho do processo em volta dela. Quando migrei para controladoria e finanças, levei essa leitura comigo. E foi ali que comecei a desenvolver soluções que pagam o próprio investimento ainda no primeiro trimestre. Hoje, na Sapphire Business Technology, gerencio projetos de diversos tamanhos e escopos variados, com uma promessa simples: a planilha, relatório/dashboard ou sistema em que você perde horas em trabalhos manuais não precisa continuar sendo um problema. Isso pode virar a solução do seu fluxo de trabalho e poupar 90% das suas horas com um bom time técnico. LinkedIn Falar com especialista

Postagens Interessantes

Quer um brinde? Adquira abaixo

São diversas ferramentas da Loja Safira para organizar finanças, pessoas, vendas e operação. Passe para o lado e conheça cada uma.

Tela da ferramenta Projetos
Projetos

Projetos

Acompanhe prazos, responsáveis e status de cada entrega do projeto.

Ver na Loja
Tela da ferramenta RH
Pessoas

RH

Controle cadastros, horas extras, férias e avaliações da equipe.

Ver na Loja
Tela da ferramenta Higienizador de Planilhas
Produtividade

Higienizador de Planilhas

Acerta CPF, telefone, e-mail e nomes e tira os duplicados, sem mexer no original.

Ver na Loja
Tela da ferramenta Vendas com Parcelas
Comercial

Vendas com Parcelas

Registre vendas, calcule comissões e acompanhe metas por vendedor.

Ver na Loja
Tela da ferramenta Playbook Administrativo
Playbook grátis

Playbook Administrativo

O administrativo inteiro, processo por processo, de graça.

Ver na Loja
Tela da ferramenta Gestão de Eventos
Operações

Gestão de Eventos

Organize convidados, fornecedores, prazos e custos do seu evento.

Ver na Loja
Tela da ferramenta Compras e Aluguel
Operações

Compras e Aluguel

Controle imóveis alugados, comprados e vendidos: contratos, pagamentos, inadimplência, reformas e o retorno de cada imóvel.

Ver na Loja
Tela da ferramenta Orçamentos, Propostas e OS
Comercial

Orçamentos, Propostas e OS

Orçamento e OS com numeração única, QR Code PIX e aceite na tela.

Ver na Loja
Tela da ferramenta Contas e Cotações
Financeiro

Contas e Cotações

Organize contas a pagar e receber e compare cotações antes de fechar.

Ver na Loja
Tela da ferramenta Contratos e Renovação
Jurídico

Contratos e Renovação

Acompanhe vigências, reajustes e alertas de renovação de contratos.

Ver na Loja
Tela da ferramenta Estudos
Educação

Estudos

Planeje matérias, revisões e cronograma de estudos sem se perder.

Ver na Loja
Tela da ferramenta Playbook Financeiro
Playbook grátis

Playbook Financeiro

Do boleto ao balanço, com antifraude e calendário legal.

Ver na Loja
Tela da ferramenta Controle Financeiro PF/PJ
Financeiro

Controle Financeiro PF/PJ

Contas a pagar e a receber da casa e da empresa separadas, com alertas.

Ver na Loja
Tela da ferramenta Precificação
Financeiro

Precificação

Calcule custo, margem e preço de venda ideal para cada produto.

Ver na Loja
Tela da ferramenta Processos
Operações

Processos

Mapeie etapas, responsáveis e gargalos dos processos da empresa.

Ver na Loja
Tela da ferramenta Produtividade
Produtividade

Produtividade

Organize tarefas, prioridades e rotina semanal em um só lugar.

Ver na Loja
Tela da ferramenta Gerador de Documentos
Produtividade

Gerador de Documentos

Contratos e declarações preenchidos sozinhos, um a um ou em lote.

Ver na Loja
Tela da ferramenta Propostas e Orçamentos
Comercial

Propostas e Orçamentos

Monte propostas e orçamentos padronizados e acompanhe o retorno.

Ver na Loja
Tela da ferramenta Gestão Empresarial
Estratégia

Gestão Empresarial

Painel geral com indicadores de vendas, custos e resultado do mês.

Ver na Loja
Tela da ferramenta Playbook Comercial
Playbook grátis

Playbook Comercial

Vendas, pós-venda e marketing organizados, com cuidado de LGPD.

Ver na Loja
Tela da ferramenta Vendas e comissão
Comercial

Vendas e comissão

Faça controle de suas vendas e comissões de forma organizada e personalizada.

Ver na Loja
Tela da ferramenta Treinamento
Operações

Treinamento

Faça controle dos seus treinamentos para cada time e especificações de forma personalizada.

Ver na Loja
Tela da ferramenta Precificação e Reajuste
Financeiro

Precificação e Reajuste

Descubra o preço certo e reajuste a tabela inteira sem medo de errar.

Ver na Loja
Tela da ferramenta Matriz SWOT
Operações

Matriz SWOT

Analise seus processos e entenda a matriz do seu negócio para melhoria de processos.

Ver na Loja
Tela da ferramenta Gestão de Chamados
Produtividade

Gestão de Chamados

Tenha um acompanhamento consolidado e com dados reais dos chamados abertos e seu SLA.

Ver na Loja
Tela da ferramenta Acidentes de Trabalho
Pessoas

Acidentes de Trabalho

Registre acidentes, investigue causas e acompanhe afastamentos, ações corretivas e indicadores de segurança.

Ver na Loja
Tela da ferramenta Dashboard Instantâneo
Estratégia

Dashboard Instantâneo

Arraste a planilha e o painel com cards, gráficos e metas aparece.

Ver na Loja
Tela da ferramenta Playbook RH e DP
Playbook grátis

Playbook RH e DP

Do ponto ao eSocial, sem colocar nenhum dado de empregado no arquivo.

Ver na Loja
Tela da ferramenta Cargos e Salários
Pessoas

Cargos e Salários

Organize cargos, faixas salariais, histórico de reajustes e simule impactos na folha.

Ver na Loja
Tela da ferramenta Cobranças
Financeiro

Cobranças

Controle títulos vencidos e a vencer, tentativas de cobrança, negociações e recebimentos.

Ver na Loja
Tela da ferramenta Conciliação Bancária
Financeiro

Conciliação Bancária

Confere o extrato com o seu sistema e fecha o mês com relatório.

Ver na Loja
Tela da ferramenta CRM
Comercial

CRM

Acompanhe leads por fonte, funil de vendas, fechamentos e o custo comercial de cada venda.

Ver na Loja
Tela da ferramenta Mini-CRM e Agenda
Comercial

Mini-CRM e Agenda

Clientes, agenda e mensagens prontas num arquivo só.

Ver na Loja
Tela da ferramenta Dieta Nutricional
Fora do trabalho

Dieta Nutricional

Monte planos alimentares, registre refeições e acompanhe calorias, macronutrientes e medidas.

Ver na Loja
Tela da ferramenta Escala de Trabalho
Pessoas

Escala de Trabalho

Gere escalas, controle folgas, turnos, horas trabalhadas e ausências da equipe.

Ver na Loja
Tela da ferramenta Playbook Operações
Playbook grátis

Playbook Operações

Do papel e do quadro branco ao app no celular da equipe.

Ver na Loja
Tela da ferramenta Estoque
Operações

Estoque

Controle entradas, saídas, perdas, validade e saldo do estoque por produto.

Ver na Loja
Tela da ferramenta Analisador de Campanhas
Comercial

Analisador de Campanhas

Relatórios de anúncio entram, diagnóstico do que fazer sai.

Ver na Loja
Tela da ferramenta Financeiro Empresarial
Financeiro

Financeiro Empresarial

Acompanhe fluxo de caixa, DRE, clientes, fornecedores e saldo por conta bancária.

Ver na Loja
Tela da ferramenta Saúde e Segurança do Trabalho
Pessoas

Saúde e Segurança do Trabalho

Controle treinamentos, exames, EPIs, inspeções, ocorrências e planos de ação de SST.

Ver na Loja