Como construir cenários suspensos em seu modelo financeiro

O método mais comumente usado para construir cenários é usar uma combinação de fórmulas e caixas suspensas. No modelo financeiro, você cria uma tabela de cenários possíveis e suas entradas e vincula os nomes dos cenários a uma caixa suspensa de célula de entrada. As entradas do modelo estão vinculadas à tabela de cenários. Se o modelo foi construído corretamente com todas as entradas fluindo para as saídas, então os resultados do modelo mudarão conforme o usuário seleciona diferentes opções na caixa suspensa.

As caixas suspensas de validação de dados são usadas para várias finalidades diferentes em modelagem financeira, incluindo análise de cenário.

Usando validações de dados para modelar cenários de lucratividade

Baixe o arquivo 0801.xlsx . Abra-o e selecione a guia identificada como 8-1-start.

Da forma como isso foi modelado, as entradas estão alinhadas na coluna B. Você pode realizar uma análise de sensibilidade simplesmente alterando uma das entradas - por exemplo, altere os clientes por operadora de chamada na célula B3 de 40 para 45, e você ver todas as mudanças de números dependentes. Isso seria uma análise de sensibilidade, porque você está alterando apenas uma variável. Em vez disso, você vai alterar várias variáveis ​​de uma vez neste exercício de análise de cenário completo, portanto, você precisará fazer mais do que ajustar alguns números manualmente.

Para realizar uma análise de cenário usando caixas suspensas de validação de dados, siga estas etapas:

Pegue o modelo baixado e recorte e cole as descrições da coluna C para a coluna F. Você pode fazer isso destacando as células C6: C8, pressionando Ctrl + X, selecionando a célula F6 e pressionando Enter.

As entradas nas células B3 a B8 são a faixa ativa que impulsiona o modelo e assim permanecerão. No entanto, eles precisam se tornar fórmulas que mudam dependendo da caixa suspensa que você criar.

Copie o intervalo da coluna B para as colunas C, D e E.

Você pode fazer isso destacando B3: B8, pressionando Ctrl + C, selecionando as células C3: E3 e pressionando Enter. Esses valores serão os mesmos para cada cenário até que você os altere.

Na linha 2 insira os títulos melhor caso , Caso Base , e pior caso.

Configurando o modelo para análise de cenário.

Observe que as fórmulas ainda estão vinculadas às entradas na coluna B, como você pode ver selecionando a célula C12 e pressionando a tecla de atalho F2.

Edite as entradas em cada cenário.

Você pode colocar o que achar provável, mas para fazer a correspondência dos números com os deste exemplo, insira os valores. Ignore a coluna B por enquanto.

Entradas para análise de cenário.

Agora você precisa adicionar a caixa suspensa na parte superior, que irá conduzir seus cenários. Não importa exatamente onde você coloca a caixa suspensa, mas deve estar em um local fácil de encontrar, geralmente no topo da página.

Na célula E1, insira o título Cenário .

Selecione a célula F1 e altere a formatação para entrada para que o usuário possa ver que esta célula é editável.

A maneira mais fácil de fazer isso é seguir estas etapas:

Clique em uma das células que já estão formatadas como entrada, como a célula E3.

Pressione o ícone Pincel de formatação na seção Área de transferência no lado esquerdo da guia Início. Seu cursor mudará para um pincel.

Selecione a célula F1 para colar a formatação.

O Pincel de formatação é normalmente para uso único. Depois de selecionar a célula, o pincel desaparecerá do cursor. Se quiser que o Pincel de formatação se torne “aderente” e se aplique a várias células, clique duas vezes no ícone ao selecioná-lo na guia Início.

Agora, na célula F1, selecione Validação de dados na seção Ferramentas de dados da guia Dados.

A caixa de diálogo Validação de dados é exibida.

Na guia Configurações, altere o menu suspenso Permitir para Lista, use o mouse para selecionar o intervalo = $ C $ 2: $ E $ 2 e clique em OK.

Criação de cenários suspensos de validação de dados.

Clique na caixa suspensa, que agora aparece ao lado da célula F1, e selecione um dos cenários (por exemplo, Caso Base).

Aplicação de fórmulas a cenários

As células na coluna B ainda dirigem o modelo e precisam ser substituídas por fórmulas. Antes de adicionar as fórmulas, no entanto, você deve alterar a formatação das células no intervalo para mostrar que elas contêm fórmulas, em vez de números codificados. Siga esses passos:

Selecione as células B3: B8 e selecione a Cor de Preenchimento no grupo Fonte na guia Página Inicial.

Altere a cor de preenchimento para um fundo branco.

É muito importante distinguir entre fórmulas e células de entrada em um modelo. Você precisa deixar claro para qualquer usuário que abra o modelo que as células neste intervalo contêm fórmulas e não devem ser substituídas.

Agora você precisa substituir os valores embutidos em código na coluna B por fórmulas que serão alteradas conforme a caixa suspensa muda. Você pode fazer isso usando várias funções diferentes; um HLOOKUP, uma instrução IF aninhada, um IFS e um SUMIF farão o trabalho. Adicione as fórmulas seguindo estas etapas:

Selecione a célula B3 e adicione uma fórmula que alterará o valor dependendo do que está na célula F1.

Aqui está o que a fórmula será nas diferentes opções:

• = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8,2,0)

  Observe que, com esta solução, você precisa alterar o número do índice da linha de 2 para 3 e assim por diante à medida que copia a fórmula. Em vez disso, você poderia usar uma função ROW no terceiro campo como este: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1,0)

• = SE ($ F $ 1 = $ C $ 2, C3, SE ($ F $ 1 = $ D $ 2, D3, E3))
• = IFS ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = $ E $ 2, E3)
• = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

  Como sempre, existem várias opções à sua escolha e a melhor solução é a mais simples e fácil de compreender. Qualquer uma dessas funções produzirá exatamente o mesmo resultado, mas ter que alterar o número do índice da linha no HLOOKUP não é robusto, e adicionar o ROW pode ser confuso para o usuário. A instrução IF aninhada é complicada de construir e seguir e, embora a nova função IFS seja projetada para tornar uma função IF aninhada mais simples, ainda é um tanto difícil de manejar. O SUMIF é bastante simples de construir e seguir, e é fácil de expandir se você precisar adicionar cenários extras no futuro.

  Observe que IFS é uma nova função que está disponível apenas com Office 365 e Excel 2016 ou posterior instalado. Se você usar esta função e alguém abrir este modelo em uma versão anterior do Excel, ela poderá visualizar a fórmula, mas não poderá editá-la.

Copie a fórmula na célula B3 para baixo na coluna.

A análise de cenário concluída.

Usando uma cópia e colagem comum, você perderá toda a sua formatação. É importante reter a formatação do modelo para que você possa ver rapidamente quais entradas estão em valores em dólares, porcentagens ou números de clientes. Use Colar fórmulas para manter a formatação. Você pode acessá-lo copiando a célula para a área de transferência, destacando o intervalo de destino, clicando com o botão direito e selecionando o ícone Colar fórmulas para colar fórmulas apenas e deixar a formatação intacta.

Agora a parte divertida! É hora de testar a funcionalidade do cenário no modelo.

Clique na célula F1, altere a caixa suspensa e observe a alteração dos resultados do modelo conforme você alterna entre os diferentes cenários.