Criar cenários no Excel, analisar e automatizar

Estamos em um mundo sujeito a muitas mudanças, e mudanças rápidas, e é importante que estejamos preparados para elas. Neste artigo você verá como podemos criar cenários no Excel com a finalidade de projetar e comparar cenários conforme valores aplicados á fórmulas pré-estabelecidas e permita a análise e comparação destas.

Neste artigo, iremos apresentar como criar cenários, analisar os dados dos cenários e automatizar a comparação entre os diversos cenários criados.

Problema prático

Imaginemos uma empresa na qual precisamos projetar cenários de vendas para o ano seguinte aonde analisaremos o Faturamento previsto e a comissão prevista, com base em três cenários possíveis. Pessimista, Seguro e Otimista.

No cenário Pessimista temos previsto um crescimento da venda de 1%, uma inflação de 8% e um aumento da comissão em torno de 5%.

No cenário Seguro temos previsto um crescimento da venda de 5%, uma inflação de 7% e um aumento da comissão em torno de 6%.

No cenário Otimista temos previsto um crescimento da venda de 10%, uma inflação de 7% e um aumento da comissão em torno de 6,5%.

Como podemos gerar sobre uma mesma base de dados três cenários alterando rapidamente e de forma segura estes valores para comparar e analisar?

Preparando a tabela

A primeira etapa é criar uma tabela com os nossos dados do ano anterior, sobre o qual os cálculos de crescimento e comissão serão realizados.

Em seguida criamos uma pequena tabela que servirá para armazenar os índices que serão aplicados aos cálculos.

Este índice serão os valores que serão reajustados conforme o cenário escolhido.

Agora na linha Prev. faturamento 2016 mil da tabela de previsão, inclua a seguinte função:

=ARRED(ARRED(E7*(1+$B$1);0)*(1+$B$2);0)

Esta função realiza a multiplicação do valor do ano anterior pelo percentual de aumento da venda e este valor é multiplicado pela inflação do período, gerando assim o faturamento previsto.

Da mesma forma foi inclusa no campo Comissão prevista 2016 mil o seguinte cálculo:

=ARRED(E8*$B$3;0)

Que é uma simples multiplicação do faturamento previsto pela comissão prevista para o cenário definido.

Criando o cenário

Após a criação da tabela que utilizaremos vamos partir para a criação dos cenários no Excel para as nossas previsões de valores.

1. Clique na opção em guia Dados->Teste de Hipóteses->Gerenciador de cenários
2. Clique em Adicionar e no nome do cenário digite “Pessimista”, e em células variáveis selecione B1:B3, que são as células que possuem os valores variáveis para a geração das previsões de faturamento e comissão.

3. Na tela seguinte inclua o percentual de crescimento de venda, percentual da inflação prevista e a comissão prevista para o cenário. Colocamos os valores 1%, 8% e 5%, lembre que o valor deve ser dividido por 100 quando trabalhamos com percentual, por exemplo 1% é igual á 1/100 = 0,01.

4. Clique em Ok e depois no botão Adicionar da mesma forma o cenário Seguro, com os valores 5%, 7% e 6% respectivamente.

5. Repita a operação do passo 4 e inclua o cenário Otimista, com os valores 10%, 7,5% e 6,5%.

Analisar os cenários

Perceba que não ocorreu nada a partir dos dados anteriores, mas clique em Gerenciador de cenários, selecione um dos cenários e clique em Mostrar ou clique duas vezes sobre, Pessimista, Seguro ou Otimista. Os valores serão alterados pelo gerenciador de cenários e a planilha atualizada.

Agora clique em Resumir e selecione os dados da linha Prev. faturamento 2016 mil. O Excel irá gerar um resumo comparando todos os resultados a partir de cada cenário.

Análise gerada do cenário:

Automatização dos cenários

Nesta etapa vamos automatizar a troca de cenários no Excel a partir de botões que criaremos.

1. Clique em Inserir->Ilustrações e insira três imagens conforme você preferir:

2. Agora será necessário criarmos os códigos em VBA para automatizar a troca entre os cenários ao clicar dos botões.

3. Habilite a guia desenvolvedor caso não esteja habilitada, veja neste artigo como habilitar: http://guiadoexcel.com.br/habilitando-a-guia-desenvolvedor-e-copiando-procedimentos-vba-sub-da-internet

4. No VBA clique em Inserir->Módulo e no módulo criado cole o código abaixo.

Private Sub lsCenario(ByVal lCenario As String)
    ActiveSheet.Scenarios(lCenario).Show
End Sub

Public Sub lsSeguro()
    lsCenario "Seguro"
End Sub

Public Sub lsOtimista()
    lsCenario "Otimista"
End Sub

Public Sub lsPessimista()
    lsCenario "Pessimista"
End Sub

5. Volte para o Excel clicando em Fechar o VBA ou alterando entre as janelas.

6. Clique com o botão direito sobre o botão Pessimista, e selecione a opção Atribuir macro. Atribua a macro lsPessimista. Clique e veja que o cenário mudou automaticamente para o Pessimista.

7. Clique com o botão direito sobre o botão Pessimista, e selecione a opção Atribuir macro. Atribua a macro lsSeguro. Clique e veja que o cenário mudou automaticamente para o Seguro.

8. Clique com o botão direito sobre o botão Pessimista, e selecione a opção Atribuir macro. Atribua a macro lsOtimista. Clique e veja que o cenário mudou automaticamente para o Otimista.

Caso queira veja também o nosso vídeo com a explicação e a construção do nosso exemplo.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: