Utilizando o Gerenciador de Cenários no Excel

Nessa publicação vamos mostrar como utilizar a ferramenta de Gerenciador de Cenários do Excel, que fica dentro da guia Dados em Teste de Hipóteses.

Caso prefira esse conteĂşdo no formato de vĂ­deo-aula, assista ao vĂ­deo abaixo!

O que vocĂŞ aprende neste vĂ­deo

Resposta rápida: A aula mostra três ferramentas da guia Dados do Excel: a Planilha de Previsão, que projeta o segundo semestre a partir das vendas do primeiro, com intervalo de confiança; o Gerenciador de Cenários, com cenários realista, otimista e pessimista para preço e custo; e a Tabela de Dados, que calcula faturamento e lucro para vários preços e, depois, para preço e custo ao mesmo tempo.

Neste vĂ­deo (31 min):

  • 1:44 - A Planilha de PrevisĂŁo, na guia Dados, no grupo PrevisĂŁo
  • 2:31 - O histĂłrico: vendas diárias do primeiro semestre de 2019
  • 3:52 - O gráfico: azul para o histĂłrico, laranja para a previsĂŁo
  • 4:36 - A data final da previsĂŁo, que o calendário deixa ir atĂ© 28 de dezembro
  • 5:34 - Mexer no intervalo de confiança
  • 8:30 - Criar: a previsĂŁo numa aba nova, com os limites superior e inferior
  • 10:11 - Gerenciador de Cenários: preço e custo unitário mudam o faturamento e o lucro bruto
  • 14:22 - Adicionar os cenários realista, otimista e pessimista sobre as cĂ©lulas C2 e C3
  • 16:38 - Mostrar cada cenário e ver os resultados mudarem
  • 18:24 - O comando Cenário na barra de acesso rápido
  • 25:05 - Tabela de Dados com uma linha de preços de 130 a 170
  • 28:25 - Tabela de Dados com o preço na linha e o custo na coluna

Trechos do vĂ­deo:

  • O intervalo de confiança mostra a faixa em que as vendas devem ficar: quanto maior a confiança pedida, mais larga a faixa, e o padrĂŁo do Excel Ă© 95%. A faixa tambĂ©m se abre Ă  medida que a previsĂŁo se afasta do fim do histĂłrico.
  • O Gerenciador de Cenários guarda conjuntos de valores para as mesmas cĂ©lulas variáveis. Ao mostrar um cenário, o Excel troca esses valores e recalcula as fĂłrmulas que dependem deles.
  • Na Tabela de Dados, a cĂ©lula de entrada Ă© a que o Excel substitui por cada valor da linha ou da coluna: com o preço na linha, a cĂ©lula do preço vai na entrada da linha; com o custo na coluna, a do custo vai na entrada da coluna.
  • Lucro bruto Ă© o lucro sem considerar impostos: preço unitário menos custo unitário, vezes o nĂşmero de vendas.

Para baixar a planilha utilizada nessa aula clique aqui!

ĂŤcone ExcelExcel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Black Friday Â· Lote Especial Â· Lote 1 Â· Ăšltimo Lote

De R$ 1.497 56% OFF54% OFF53% OFF

por 12x R$ 6467,5069

Preço do Lote Especial por tempo limitadoPreço do Lote Especial só até amanhã, 13/11, às 23h59Preço do Lote Especial até 13/11, às 23h59Último dia do Lote Especial: o preço vale até hoje, às 23h59Preço do Lote 1 por tempo limitadoPreço do Lote 1 só até amanhã, 20/11, às 23h59Preço do Lote 1 até 20/11, às 23h59Último dia do Lote 1: o preço vale até hoje, às 23h59Preço do Último Lote por tempo limitadoPreço do Último Lote só até amanhã, 27/11, às 23h59Preço do Último Lote até 27/11, às 23h59Último dia do Último Lote: o preço vale até hoje, às 23h59

Começar agoraQuero a oferta de Black FridaySeta para a direita
Fundo ExcelTelas Excel
Luz Excel

O que é o Gerenciador de Cenários?

É uma ferramenta que possibilita ao usuário fazer simulações de cenários já criados para um problema. Neste caso, vamos utilizar uma tabela de venda com alguns dados que são fixos e outros dados que dependem desses valores, ou seja, ao alterar esses dados fixos nossas fórmulas de faturamento e de lucro bruto serão modificadas.

Tabela base
Tabela base

Quando utilizar essa ferramenta?

Essa ferramenta será utilizada quando temos a necessidade de simular alguns cenários diferentes para um problema com o intuito de analisar os resultados. No nosso exemplo, vamos utilizar 3 tipos de cenários: otimista, realista e pessimista.

Cenários analisados
Cenários analisados

Como utilizar o Gerenciador de Cenários?

Primeiramente devemos selecionar todos os nossos dados.

Tabela base selecionada
Tabela base selecionada

Feito isso basta ir até a guia Dados, em seguida na opção Teste de Hipóteses e por fim na opção Gerenciador de Cenários.

Gerenciador de Cenários
Gerenciador de Cenários

Ao selecionar essa opção uma nova janela será aberta.

Opções do Gerenciador de Cenários
Opções do Gerenciador de Cenários

Vamos agora selecionar a opção Adicionar para que possamos adicionar os nosso cenários ao gerenciador. Vamos iniciar pelo cenário Realista.

Cenário Realista
Cenário Realista

A primeira parte a ser preenchida é o nome do cenário e em seguida devemos preencher quais são as células que vão variar para que nosso faturamento e lucro bruto se modifiquem. Essas células são C2 e C3 que contém o valor do preço unitário e custo unitário. Feito isso, podemos clicar em OK. Agora, será aberta outra janela para que possamos modificar os valores dessas células de acordo com o nosso cenário.

Escolha dos valores
Escolha dos valores

Colocamos os valores que tĂ­nhamos na tabela anterior e pressionamos OK para continuar e voltar a tela inicial.

Configurações do Gerenciador de Cenários
Configurações do Gerenciador de Cenários

Nesta janela podemos observar que nosso primeiro cenário foi criado, agora basta repetir o procedimento para os outros dois cenários: otimista e pessimista.

Cenários Otimista e Pessista
Cenários Otimista e Pessista

Tendo os 3 cenários criados podemos agora testar os cenários que foram criados selecionando o cenário desejado e pressionando a opção Mostrar. Fazendo isso os valores que foram colocados em cada um dos cenários serão substituídos nas células C2 e C3 e consequentemente as fórmulas de faturamento de lucro bruto serão recalculadas.

Vamos selecionar o cenário otimista e clicar em mostrar.

Otimista no Gerenciador de Cenários
Otimista no Gerenciador de Cenários

É possível observar que os valores foram alterados para os valores que configuramos esse cenário com preço unitário igual a 200 e custo unitário igual a 30.

Existe uma outra forma de selecionar e trocar a seleção desses cenários de forma muito mais rápida, sem que a janela de gerenciador de cenário fique aberta.

Para isso basta selecionar a guia Arquivo, em seguida Opções.

Guia Arquivo
Guia Arquivo
Opções do Excel
Opções do Excel

Ao selecionar opções uma nova janela será aberta e iremos selecionar a opção Barra de Ferramentas de Acesso Rápido.

Barra de Ferramenta de Acesso Rápido
Barra de Ferramenta de Acesso Rápido

Feito isso, na parte de Escolher comandos em: vamos modificar para Comandos Fora da faixa de Opções.

Cenários
Cenários

Por fim, vamos selecionar a opção Cenário e clicar em Adicionar. Feito isso, basta clicar em OK.

Agora podemos observar que temos um novo ícone no canto superior esquerdo do Excel em formato de círculo e ao clicar nele temos as nossas opções de cenários que foram criadas.

Opções do Gerenciador de Cenários
Opções do Gerenciador de Cenários

Portanto, para alterar entre os cenários basta escolher uma dessas opções que os valores das células C2 e C3 serão modificados para os valores de cada um dos cenários. Essa última opção facilita um pouco na escolha dos cenários sem que o usuário tenha que ficar com uma janela aberta para fazer essas escolhas.

Para mais conteĂşdos da guia Teste de HipĂłteses, acesse:
https://www.hashtagtreinamentos.com/planilha-de-previsao-excel
https://www.hashtagtreinamentos.com/atingir-meta

Hashtag Free Excel Básico

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?