Resolução de Case

A Empresa X possuí produtos comercializados para revendas do setor do agronegócio em todas as cidades do Brasil e algumas do exterior. O diretor de marketing gostaria de saber qual o potencial de vendas para algumas regiões, o potencial de vendas para cada revenda que fez pelo menos uma transação em 2020, entre outras informações.
Para calcular o potencial da cidade é necessário multiplicar a área cultivável de cada cultura pela quantidade de litros de cada um dos quatro produtos necessários para atender aquela cultura pelo preço do litro de cada um dos produtos naquela região.
O potencial da cidade será um valor em reais que mostrará que a cidade XPTO tem potencial de comprar N reais da Empresa X, caso ela tenha 100% de market share de 100% da área cultivável.
Cada cliente (revenda) possuí diversos códigos SAP cadastrados, que é a primary key desse banco de dados. Cada código SAP é uma filial da loja.
Todas as filiais têm peso igual, não há relação de matriz e filiais. Os valores das compras de cada produto e a data da última transação estão vinculados à chave do banco de dados. Além disso, a nota de crédito está vinculada ao mesmo código SAP, assim como os territórios que cada um deles atende.
O potencial de compra da revenda será a soma dos potenciais das cidades atendidas.
Além de mostrar as informações necessárias para os cálculos acima, as tabelas mostram a população das cidades do Brasil e a margem em porcentagem com a venda de cada produto em cada região. Exemplo: se um produto é vendido por R$ 100,00 e a margem é de 3%, então, a margem da Empresa X é de R$ 3,00 com aquela venda.

Todas as tabelas do Excel disponível para este estudo são usadas como fonte de dados para outras tabelas da rede da Empresa X, estando vinculadas às mesmas por fórmulas. Alguns dados podem estar faltando ou com erros grotescos que mesmo pessoas que não são do setor podem notar.
O diretor de marketing precisa de um dashboard mostrando informações relevantes aos dados disponíveis incluindo no mínimo:
- O potencial de vendas em reais de cada país.

- O potencial de vendas em reais de cada estado brasileiro.
- As três capitais estaduais brasileiras com as menores margens em reais somando os 4 produtos caso as vendas alcancem 100% de market share em 100% da área cultivável.
- Qual o top 20 municípios não capitais entre 500 mil e 1,25 milhão de habitantes com as maiores margens em reais na soma dos 4 produtos caso as vendas alcancem 100% de market share em 100% da área cultivável.

- Qual a média de faturamento em reais por cidade do produto IV no estado de São Paulo caso as vendas alcancem 100% de market share em 100% da área cultivável.
Além do dashboard, o diretor precisa de planilhas em Excel que mostrem:
- Qual o potencial de compras de cada revenda que fez pelo menos uma transação em 2020 e a nota de crédito média dessa revenda.
- A quantidade de produtos comprados até 80% do valor do faturamento de cada revenda.

Na explicação da solução será necessário mostrar quais foram os passos executados e as fórmulas usadas. Suas fontes de dados são o Excel anexado e fontes externas da Internet, que devem ser referenciadas e justificadas na apresentação no case.