Alternar entre vários conjuntos de valores através de cenários

Existem três tipos de ferramentas de Análise What-If fornecidas com o Excel: Cenários, Tabelas de Dados e Procura de Objetivos. Cenários e Tabelas de Dados levam conjuntos de valores de entrada e projeto para a frente para determinar possíveis resultados. A opção Procurar Objetivo difere de Cenários e Tabelas de Dados, na medida em que 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 quiser analisar mais de 32 valores e os valores representarem apenas uma ou duas variáveis, pode utilizar Tabelas de Dados. Embora esteja limitada a apenas uma ou duas variáveis (uma para a célula de entrada da linha e outra para a célula de entrada da coluna), uma Tabela de Dados pode incluir os valores de variáveis diferentes que quiser. Um cenário pode ter um máximo de 32 valores diferentes, mas pode criar quantos cenários quiser.

Além destas três ferramentas, pode instalar suplementos que o ajudam a realizar What-If Análise, como o suplemento Solver. O suplemento Solver é semelhante à ferramenta Atingir Objetivo, mas pode conter mais variáveis. O utilizador também pode criar previsões ao utilizar a alça de preenchimento e vários comandos incorporados no Excel. Para obter modelos mais avançados, pode utilizar o suplemento Analysis ToolPak.

Suponha que pretende criar um orçamento, mas não tem a certeza da sua receita. Ao utilizar cenários, pode definir diferentes valores possíveis para a receita e, em seguida, alternar entre cenários para realizar análises de hipóteses.

Por exemplo, suponha que o seu pior cenário orçamental é Receita Bruta de 50 000 $ e Custos de Bens Vendidos de 13 200 $, deixando 36 800 $ em Lucro Bruto. Para definir este conjunto de valores como um cenário, primeiro introduza os valores numa folha de cálculo, conforme mostrado na ilustração seguinte:

A célula Alterar células tem valores que escreve, enquanto a célula Resultado contém uma fórmula baseada em Alterar células (nesta ilustração, a célula B4 tem a fórmula =B2-B3).

Em seguida, utilize a caixa de diálogo Gestor de Cenários para guardar estes valores como um cenário. Aceda ao separador Dados > What-If Analysis > Scenario Manager > Adicionar.

Na caixa de diálogo Nome do cenário , atribua o nome Pior caso ao cenário e especifique que as células B2 e B3 são os valores que mudam entre cenários. Se selecionar a opção Alterar células na sua folha de cálculo antes de adicionar um Cenário, o Gestor de Cenários irá inserir automaticamente as células automaticamente, caso contrário, pode escrevê-las manualmente ou utilizar a caixa de diálogo de seleção de células à direita da caixa de diálogo Alterar células.

Proteção – também pode proteger os seus cenários, por isso, na secção Proteção, verifique as opções pretendidas ou desmarque-as se não quiser qualquer proteção.

• Selecione Impedir Alterações para impedir a edição do cenário quando a folha de cálculo estiver protegida.

• Selecione Oculto para impedir a apresentação do cenário quando a folha de cálculo estiver protegida.

Agora, suponha que o seu cenário orçamental mais adequado é Receita Bruta de 150 000 $ e Custos de Bens Vendidos de 26 000 $, deixando 124 000 $ em Lucro Bruto. Para definir este conjunto de valores como um cenário, crie outro cenário, dê-lhe o nome Melhor Caso e forneça valores diferentes para a célula B2 (150 000) e a célula B3 (26 000). Uma vez que Lucro Bruto (célula B4) é uma fórmula - a diferença entre Receita (B2) e Custos (B3) - não altera a célula B4 para o cenário De Melhores Casos.

Depois de guardar um cenário, este fica disponível na lista de cenários que pode utilizar nas suas análises de hipóteses. Tendo em conta os valores na ilustração anterior, se optar por apresentar o cenário Melhores Casos, os valores na folha de cálculo serão alterados para se assemelharem à seguinte ilustração:

Pode haver alturas em que tem todas as informações numa folha de cálculo ou livro necessárias para criar todos os cenários que pretende considerar. No entanto, poderá querer recolher informações do cenário de outras origens. Por exemplo, suponha que está a tentar criar um orçamento da empresa. Pode recolher cenários de diferentes departamentos, como Vendas, Folha de Pagamentos, Produção, Marketing e Legal, uma vez que cada uma destas origens tem informações diferentes para utilizar na criação do orçamento.

Pode reunir estes cenários numa folha de cálculo com o comando Intercalar . Cada origem pode fornecer os valores de células que quiser ou quantos quiser. Por exemplo, poderá querer que cada departamento forneça projeções de despesas, mas apenas necessite de projeções de receitas de alguns.

Quando opta por intercalar, o Gestor de Cenários carrega um assistente de Cenário de Intercalação, que irá listar todas as folhas de cálculo no livro ativo, bem como listar quaisquer outros livros que possa ter aberto no momento. O assistente irá indicar-lhe quantos cenários tem em cada folha de cálculo de origem que selecionar.

Quando recolhe diferentes cenários de várias origens, deve utilizar a mesma estrutura de células em cada um dos livros. Por exemplo, a Receita pode sempre ir na célula B2 e as Despesas podem sempre ir para a célula B3. Se utilizar estruturas diferentes para os cenários de várias origens, pode ser difícil intercalar os resultados.

Para comparar vários cenários, 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 numa Relatório de tabela dinâmica.

Um relatório de resumo do cenário baseado nos dois cenários de exemplo anteriores teria um aspeto semelhante ao seguinte:

Irá reparar que o Excel adicionou automaticamente níveis de Agrupamento , o que irá expandir e fechar a vista à medida que clica nos diferentes seletores.

É apresentada uma nota no final do relatório de resumo a explicar que a coluna Valores Atuais representa os valores de alteração de células no momento em que o Relatório de Resumo do Cenário foi criado e que as células alteradas para cada cenário estão realçadas a cinzento.