Nessa publicação vou te mostrar como utilizar a Tabela de Dados no Excel. Poucas pessoas conhecem e pode poupar muito tempo!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
Existe uma ferramenta da guia Dados chamada teste de Hipótese, se você tem uma tabela com as datas e uma coluna com a quantidade vendida em cada data, você pode usar a ferramenta para fazer uma previsão futura de como estarão as vendas.
Para fazer isso selecione toda a tabela clicando em qualquer célula da tabela e depois usando o atalho CONTROL + T ou selecione a primeira célula e dê control + shift + seta para baixo e depois para o lado.
Feito isso clique na guia Dados e selecione a opção Planilha de Previsão.
Assim que você clicar em planilha de previsão já vai aparecer um gráfico como o da imagem abaixo:
A parte azul do gráfico é a parte que corresponde ao histórico e a parte em laranja corresponde a projeção feita pela ferramenta.
Nós podemos escolher qual será a periodicidade dessa previsão, selecione no calendário abaixo do gráfico a maior data que a ferramenta permite fazer a previsão, note a mudança no gráfico:
Ainda temos mais opções de formatações interessantes, clique em Opções abaixo do gráfico.
Note que temos o intervalo de confiança, esse intervalo representa as duas linhas laranja que aparecem acima e abaixo da linha principal da previsão, ela indica que a previsão irá ser feita dentro daqueles parâmetros que são as faixas de erro. Você pode diminuir ou aumentar a margem de erro.
Podemos substituir o gráfico de linhas para um gráfico de colunas, é só clicar no símbolo do gráfico de coluna na parte superior direita do gráfico.
Feito essas mudança clique no botão Criar, observe que será criada uma nova planilha com o gráfico e uma tabela formatada.
Para explicar como utilizar o gerenciador de cenários vamos utilizar outra tabela, esta tabela possui informações como preço unitário, custo unitário, vendas etc.
Nosso objetivo agora é usar uma ferramenta que vai variar o preço unitário e o custo unitário para nos dar três cenários:
Para criar esses cenários de forma automática selecione toda a tabela e clique na guia Dados -> Teste de Hipótese -> Gerenciador de senário.
Feito isso, você será direcionado para uma janela, nesta janela clique em Adicionar e você será direcionado para outra janela:
Em Nome do cenário coloque “Realista” e em seguida em Células variáveis você deve selecionar as células que contem o valor do preço unitário e do custo unitário (no nosso caso são as duas células da planilha que estão pintadas de amarelo).
Feito isso você deve informar qual o valor do preço unitário em um cenário realista e também o valor do custo unitário no cenário realista, essas duas informações você irá colocar na próxima janela que se abrir como na imagem abaixo.
Faça isso para cada cenário, realista, otimista e pessimista.
Resultado
Feito isso, com os três senários criados você pode clicar sobre o cenário e em seguida clique em Mostrar, note que as duas células que contém o preço unitário e o custo unitário vão se modificar conforme o resultado que você programou e consequentemente as demais célula da tabela serão recalculadas.
Vou te dar outra dica de como você pode chegar nesse resultado e impressionar no seu trabalho, no Excel clique em:
-> Guia Arquivo
-> Opções
-> Barra de ferramentas de Acesso Rápido
-> Procure por “Escolher comandos em”
-> Clique na caixinha de múltipla escolha e selecione
-> Comandos Fora da Caixa de Opções
-> Feito isso vão aparecer vários comandos, procure por -> Cenário
-> Clique no botão Adicionar que fica entre os dois blocos e dê ok
Feito isso, irá aparecer no canto superior esquerdo da tela um círculo, clique neste círculo e você vai ter acesso aos cenários criados anteriormente de uma forma muito mais prática!
Obs. Caso apareça uma caixinha com a frase “Definir Cenário…” é só clicar em Não e as alterações vão seguir conforme o planejado.
A tabela de dados tem um comportamento similar ao Gerenciador de Cenários, no entanto, com essa ferramenta podemos construir uma tabela com os valores variáveis da nossa tabela para podermos ter uma análise mais completa entre esses dois valores. Neste caso, vamos utilizar a seguinte tabela.
Temos que os dois valores em laranja são variáveis, ou seja, eles podem ser modificados. E à medida que esses valores são modificados temos uma alteração no faturamento e lucro bruto dos nossos produtos.
Vamos utilizar justamente para verificar diferentes combinações dos nossos dados variáveis e com isso poderemos verificar como o lucro bruto e o faturamento se comportam em cada uma dessas combinações.
Inicialmente, vamos preencher uma tabela manualmente com esses dados e em seguida utilizamos a ferramenta Tabela de Dados para automatizar esse processo.
Note que os valores em azul desta tabela correspondem aos valores de:
Faturamento = Vendas * Preço unitário
Lucro bruto = (Preço unitário – Custo unitário) * Vendas
Esses valores estão sendo puxados da primeira tabela de forma automática.
Inicialmente, podemos pensar que, para obter os resultados de faturamento e lucro bruto precisaríamos apenas alterar na primeira tabela e copiar os resultados para cada uma das colunas.
Isso pode ser feito, no entanto, temos uma forma para automatizar esse processo e torná-lo mais rápido.
O primeiro passo para preenchermos essa tabela menor é selecionar os dados tanto de preço unitário quanto de faturamento e lucro bruto.
Vale ressaltar que o valor das células F4 e F5 são apenas cópias dos valores das células C5 e C6, pois são nessas células que temos as fórmulas para calcular o faturamento (C4*C2) e o lucro bruto ((C2-C3)*C4). Portanto, o valor da célula F4 é =C5 e o valor da célula F5 é =C6.
Feito isso, basta ir até a guia Dados, em seguida selecionar a opção Teste de Hipóteses -> Tabela de Dados.
Ao clicar em Tabela de Dados uma nova janela será aberta, nesta janela você tem dois campos, para escolher entre eles responda a seguinte pergunta:
Os dados que queremos calcular a variação (que no nosso caso é 130, 140, 150, 160 e 170), esses dados estão em linha ou em coluna?
Note que esses dados na nossa tabela estão dispostos em uma linha, então escolha a opção -> Célula de entrada de Linha.
Feito isso no campo ao lado você deve indicar a célula onde ele deve fazer as simulações, no nosso caso será feito na célula C2.
Após colocar a célula C2 dentro do campo de entrada da linha podemos pressionar OK e então toda a tabela será preenchida com as informações de lucro bruto e faturamento para todos os valores que desejamos, que foram colocados na parte superior da tabela.
Desta forma, o usuário não tem que preencher coluna por coluna e dado por dado até completar toda a tabela, essa ferramenta permite automatizar esse processo e facilitá-lo, principalmente quando temos uma quantidade grande de valores para testar.
Neste exemplo, só fizemos a variação de preço unitário, no entanto, podemos explorar um pouco mais essa ferramenta e utilizar duas variações simultâneas, ou seja, podemos fazer tanto uma variação de preço unitário quanto de custo unitário.
Para fazermos essa variação dupla só podemos calcular uma variável de cada vez, ou seja, vamos calcular apenas o lucro bruto. Desta forma, vamos obter o lucro bruto para diferentes valores de preço e custo unitário.
O procedimento é o mesmo, vamos iniciar selecionando os dados variáveis e a nossa fórmula.
Em seguida, vamos até a ferramenta Tabela de Dados.
Na janela que será aberta vamos preencher tanto a entrada de linha quanto a entrada de coluna, pois agora teremos os dois valores variando para encontrarmos os diferentes valores de lucro bruto para essas combinações.
Na entrada de linha selecionamos a célula C3, pois em nossa tabela podemos ver que o preço unitário está variando em linha enquanto o custo unitário está variando em coluna, que se encontra na célula C2. Por fim, basta pressionar OK para que todo o cálculo seja feito.
É possível observar que toda a tabela foi preenchida com pequenos passos. Agora é possível analisar os diferentes dados das diferentes combinações de preço unitário com custo unitário para encontrar o lucro bruto.
Desta forma, o usuário não precisa ficar testando uma infinidade de valores até encontrar um valor próximo ao que procura. Pode simplesmente utilizar essa ferramenta para facilitar sua análise.
Vale lembrar que o mesmo pode ser feito para o faturamento, basta copiar a tabela e trocar a fórmula dentro da célula azul para a fórmula do faturamento.
Para mais conteúdos sobre Teste de Hipóteses, acesse:
https://www.hashtagtreinamentos.com/gerenciador-de-cenarios-no-excel/
https://www.hashtagtreinamentos.com/planilha-de-previsao-no-excel/
Você já teve que calcular diferentes cenários de lucro e valores para seus produtos? Preparei esta aula para te ensinar 3 ferramentas muito úteis se você possui um negócio ou trabalha com vendas.
Com esses recursos você vai tornar sua tabela muito mais dinâmica, automática e prática, diminuindo a chance de erros em cálculos também porque uma vez feitos e conferidos os cálculos de diferentes cenários de preço e lucro são calculados automaticamente!
Eu espero que tenham gostado! Eu fico por aqui. Um abraço,
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador