Como criar cenários suspensos em seu modelo financeiro - manequins

O método mais comum de construção de 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 liga os nomes dos cenários a uma caixa suspensa de célula de entrada. As entradas do modelo estão ligadas à tabela de cenários. Se o modelo foi construído corretamente com todas as entradas que fluem para as saídas, os resultados do modelo serão alterados à medida que o usuário selecionar opções diferentes na caixa suspensa.

As caixas suspensas de validação de dados são usadas para uma série de propósitos diferentes na modelagem financeira, incluindo a análise de cenários.

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

Baixar arquivo 0801. xlsx. Abra-o e selecione a guia com o nome 8-1-start.

A forma como este foi modelado, as entradas estão alinhadas na coluna B. Você pode executar a análise de sensibilidade simplesmente mudando uma das entradas - por exemplo, altere os clientes por operador de chamada na célula B3 de 40 para 45 e Você verá todos os números dependentes mudarem. Esta seria uma análise de sensibilidade, porque você está modificando apenas uma variável. Em vez disso, você vai mudar várias variáveis ​​ao mesmo tempo neste exercício de análise de cenário completo, então você precisará fazer mais do que ajustar alguns números manualmente.

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

1. Pegue o modelo baixado e corte 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 o intervalo ativo que impulsiona o modelo e permanecerá assim. No entanto, eles precisam se tornar fórmulas que mudam dependendo da caixa drop-down que você criará.

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

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

3. Na linha 2, insira os títulos Best Case , Base Case , e Pior caso.

   

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

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

4. Edite as entradas abaixo de cada cenário.

   Você pode colocar o que você pensa que é provável, mas para combinar os números com aqueles neste exemplo, insira os valores. Ignore a coluna B por enquanto.

   

   Entradas para análise de cenários.

   Agora você precisa adicionar a caixa drop-down no topo, que vai dirigir seus cenários. Na verdade, não importa onde exatamente você coloca a caixa suspensa, mas deve estar em um local fácil de encontrar, geralmente no topo da página.

5. Na célula E1, digite o título Cenário .
6. Selecione a célula F1 e altere a formatação para a entrada para que o usuário veja que essa célula pode ser editada.

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

   1. Clique em uma das células que já estão formatadas como uma entrada, como a célula E3.
   2. Pressione o ícone Formatar pintor na seção Área de transferência no lado esquerdo da guia Início. Seu cursor mudará para um pincel.
   3. Selecione a célula F1 para colar a formatação.

      Format Painter é normalmente para uso único. Depois de selecionar a célula, o pincel desaparecerá do cursor. Se você deseja que o Painel de formatação se torne "pegajoso" e aplique para várias células, clique duas vezes no ícone quando você seleciona-o na guia Início.

7. Agora, na célula F1, selecione Data Validation na seção Data Tools da guia Data.

   Aparece a caixa de diálogo Validação de dados.

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

   

   Criando cenários de suspensão de validação de dados.
9. Clique na caixa suspensa, que agora aparece ao lado da célula F1, e selecione um dos cenários (por exemplo, Base Case).

Aplicando fórmulas para cenários

As células da coluna B ainda estão dirigindo o modelo, e estas 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 estes passos:

1. Selecione células B3: B8 e selecione a Cor de preenchimento no grupo Fonte na guia Início.
2. Mude 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 desse intervalo contém fórmulas e não devem ser substituídas.

Agora você precisa substituir os valores codificados na coluna B com fórmulas que serão alteradas à medida que a caixa drop-down for alterada. Você pode fazer isso usando várias funções diferentes; um HLOOKUP, uma declaração IF aninhada, um IFS e um SUMIF farão o truque. Adicione as fórmulas seguindo estas etapas:

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

   Aqui está o que a fórmula estará sob as diferentes opções:

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

     Observe que com esta solução, você precisa mudar o número de índice de linha de 2 a 3 e assim por diante enquanto você copia a fórmula para baixo. 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)

   • = IF ($ F $ 1 = $ C $ 2, C3, IF ($ 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 diferentes para escolher e a melhor solução é aquela que é o mais simples e fácil de entender.Qualquer uma dessas funções produzirá exatamente o mesmo resultado, mas ter que mudar o número de índice de linha no HLOOKUP não é robusto, e adicionar o ROW pode ser confuso para um usuário. A declaração de IF aninhada é complicada de construir e seguir e, embora a nova função IFS tenha sido projetada para tornar uma função IF aninhada mais simples, ainda é bastante difícil. O SUMIF é bastante simples de construir e seguir, e é fácil expandir se você precisar adicionar cenários adicionais 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ê usa essa função e alguém abre esse modelo em uma versão anterior do Excel, ela pode visualizar a fórmula, mas ela não poderá editá-la.

2. Copie a fórmula na célula B3 abaixo da coluna.

   

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

   Ao usar uma cópia e cole comum, você perderá toda a sua formatação. É importante manter a formatação do modelo para que você possa ver de imediato quais entradas estão em valores em dólares, porcentagens ou números de clientes. Use Paste Formulas para reter 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 do mouse e selecionando o ícone Colar fórmulas para colar apenas fórmulas e deixe a formatação intacta.

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

3. Clique na célula F1, altere a caixa suspensa e observe as saídas do modelo alteradas conforme você alterna entre os diferentes cenários.