Alternar entre vários conjuntos de valores usando cenários

Há três tipos de ferramentas de análise de What-If que vêm com o Excel: cenários, tabelas de dados e busca de metas. Cenários e Tabelas de Dados levam conjuntos de valores de entrada e projeto para determinar possíveis resultados. O Goal Seek difere de Cenários e Tabelas de Dados, pois ele leva um resultado e projeta para trás para determinar possíveis valores de entrada que produzem esse resultado.

Cada cenário pode acomodar até 32 valores variáveis. Se você quiser analisar mais de 32 valores e os valores representarem apenas uma ou duas variáveis, você poderá usar Tabelas de Dados. Embora seja limitado a apenas uma ou duas variáveis (uma para a célula de entrada de linha e outra para a célula de entrada da coluna), uma Tabela de Dados pode incluir tantos valores variáveis diferentes quanto você quiser. Um cenário pode ter no máximo 32 valores diferentes, mas você pode criar quantos cenários desejar.

Além dessas três ferramentas, você pode instalar suplementos que ajudam você a executar What-If Analysis, como o suplemento Solver. O suplemento Solver é semelhante a Atingir Meta, mas pode acomodar mais variáveis. Você também pode criar previsões usando a alça de preenchimento e vários comandos que são integrados ao Excel. Para modelos mais avançados, use o suplemento Ferramentas de Análise.

Suponha que você queira criar um orçamento, mas não tenha certeza de sua receita. Usando cenários, você pode definir diferentes valores possíveis para a receita e alternar entre cenários para executar análises de e se.

Por exemplo, suponha que seu pior cenário orçamentário seja a Receita Bruta de US$ 50.000 e os Custos de Bens Vendidos de US$ 13.200, deixando US$ 36.800 em Lucro Bruto. Para definir esse conjunto de valores como um cenário, primeiro insira os valores em uma planilha, conforme mostrado na ilustração a seguir:

As células de alteração têm valores que você digita, enquanto a célula Result contém uma fórmula baseada nas células de alteração (nesta célula de ilustração B4 tem a fórmula =B2-B3).

Em seguida, use a caixa de diálogo Gerenciador de Cenários para salvar esses valores como um cenário. Goto a guia Dados > What-If Analysis > Scenario Manager > Add.

Na caixa de diálogo Nome do cenário , nomeie o cenário Pior Caso e especifique que as células B2 e B3 são os valores que mudam entre cenários. Se você selecionar a alteração de células em sua planilha antes de adicionar um Cenário, o Gerenciador de Cenários inserirá automaticamente as células para você, caso contrário, você poderá digitá-las manualmente ou usar a caixa de diálogo seleção de células à direita da caixa de diálogo Alterar células.

Proteção – você também pode proteger seus cenários, portanto, na seção Proteção marcar as opções desejadas ou desmarque-os se não desejar nenhuma proteção.

• Selecione Impedir alterações para impedir a edição do cenário quando a planilha estiver protegida.

• Selecione Oculto para impedir a exibição do cenário quando a planilha estiver protegida.

Agora suponha que seu cenário de orçamento melhor caso seja receita bruta de US $ 150.000 e custos de bens vendidos de US $ 26.000, deixando US $ 124.000 em lucro bruto. Para definir esse conjunto de valores como um cenário, você cria outro cenário, nomeie-o Melhor Caso e forneça valores diferentes para a célula B2 (150.000) e a célula B3 (26.000). Como o Lucro Bruto (célula B4) é uma fórmula - a diferença entre Receita (B2) e Custos (B3) - você não altera a célula B4 para o cenário Best Case.

Depois de salvar um cenário, ele fica disponível na lista de cenários que você pode usar em suas análises de e-if. Dado os valores na ilustração anterior, se você optar por exibir o cenário Best Case, os valores na planilha serão alterados para se assemelhar à seguinte ilustração:

Pode haver momentos em que você tem todas as informações em uma planilha ou pasta de trabalho necessárias para criar todos os cenários que você deseja considerar. No entanto, talvez você queira coletar informações de cenário de outras fontes. Por exemplo, suponha que você esteja tentando criar um orçamento da empresa. Você pode coletar cenários de diferentes departamentos, como Vendas, Folha de Pagamento, Produção, Marketing e Jurídico, pois cada uma dessas fontes tem informações diferentes para usar na criação do orçamento.

Você pode reunir esses cenários em uma planilha usando o comando Merge . Cada fonte pode fornecer quantos valores de células ou poucas alterações desejar. Por exemplo, você pode querer que cada departamento forneça projeções de despesas, mas só precisa de projeções de receita de alguns.

Quando você optar por mesclar, o Gerenciador de Cenários carregará um assistente de Cenário de Mesclagem, que listará todas as planilhas na pasta de trabalho ativa, bem como listará quaisquer outras pastas de trabalho que você possa ter aberto no momento. O assistente informará quantos cenários você tem em cada planilha de origem selecionada.

Ao coletar cenários diferentes de várias fontes, você deve usar a mesma estrutura celular em cada uma das pastas de trabalho. Por exemplo, a Receita pode sempre ir na célula B2 e as despesas podem sempre ir na célula B3. Se você usar estruturas diferentes para os cenários de várias fontes, pode ser difícil mesclar os resultados.

Para comparar vários cenários, você pode criar um relatório que os resume na mesma página. O relatório pode listar os cenários lado a lado ou apresentá-los em um relatório de Tabela Dinâmica.

Um relatório de resumo de cenário com base nos dois cenários de exemplo anteriores seria semelhante ao seguinte:

Você observará que o Excel adicionou automaticamente níveis de agrupamento para você, o que expandirá e ressoará a exibição ao clicar nos diferentes seletores.

Uma nota é exibida no final do relatório de resumo explicando que a coluna Valores Atuais representa os valores de alteração de células no momento em que o Relatório de Resumo de Cenário foi criado e que as células alteradas para cada cenário são realçadas em cinza.