A análise de sensibilidade no Excel nos ajuda a estudar a incerteza na saída do modelo com as mudanças nas variáveis de entrada. Ele faz principalmente testes de estresse de nossas premissas modeladas e leva a percepções de valor agregado.

No contexto da avaliação DCF, a Análise de Sensibilidade no Excel é especialmente útil em finanças para modelar o preço das ações ou a sensibilidade da avaliação a suposições como taxas de crescimento ou custo de capital.

Neste artigo, examinamos a seguinte Análise de Sensibilidade no Excel para Modelagem DCF profissionalmente.

Mais importante - Baixe a análise de sensibilidade no modelo do Excel

Aprenda uma variável e duas variáveis DATA TABLE Tipos no Excel

Análise de Sensibilidade no Excel

# 1 - Análise de sensibilidade da tabela de dados de uma variável no Excel

Tomemos o exemplo de finanças (modelo de desconto de dividendos) abaixo para entendê-lo em detalhes.

O crescimento constante DDM nos dá o valor justo de uma ação como um valor presente de um fluxo infinito de dividendos que estão crescendo a uma taxa constante.

A fórmula de crescimento de Gordon é a seguinte -

Onde:

D1 = Valor do dividendo a ser recebido no próximo ano
D0 = Valor do dividendo recebido este ano
g = taxa de crescimento do dividendo
Ke = taxa de desconto

Agora, vamos supor que queremos entender o quão sensível é o preço das ações em relação ao Retorno Esperado (ke). Há duas maneiras de fazer isso -

Maneira de burro :-)
Análise E se

# 1 - Donkey Way

A análise de sensibilidade no Excel usando o método do Donkey é muito direta, mas difícil de implementar quando muitas variáveis estão envolvidas.

Você deseja continuar fazendo isso dadas 1000 suposições? Obviamente não!

Aprenda a seguinte análise de sensibilidade na técnica do Excel para evitar problemas.

# 2 - Usando uma tabela de dados variável

A melhor maneira de fazer análises de sensibilidade no Excel é usar tabelas de dados. As tabelas de dados fornecem um atalho para calcular várias versões em uma operação e uma maneira de visualizar e comparar os resultados de todas as variações diferentes juntas em sua planilha. Abaixo estão as etapas que você pode seguir para implementar uma análise de sensibilidade unidimensional no Excel.

Etapa 1 - Crie a tabela em um formato padrão

Na primeira coluna, você tem as suposições de entrada. Em nosso exemplo, as entradas são a taxa de retorno esperada (ke). Além disso, observe que há uma linha em branco (colorida em azul neste exercício) abaixo do título da tabela. Esta linha em branco serve a um propósito importante para esta tabela de dados unidimensional que você verá na Etapa 2.

Etapa 2 - Vincule a entrada e a saída de referência conforme o instantâneo abaixo.

O espaço fornecido pela linha em branco agora é usado para fornecer a entrada (retorno esperado Ke) e a fórmula de saída. Por que isso é feito assim?

Vamos usar a “Análise What if”, esta é uma maneira de instruir o Excel que para o Input (ke), a fórmula correspondente fornecida no lado direito deve ser usada para recalcular todos os outros inputs.

Etapa 3 - Selecione a ferramenta de análise de variações hipotéticas para realizar a análise de sensibilidade no Excel

É importante notar que isso é subdividido em duas etapas

Selecione o intervalo da mesa começando do lado esquerdo, começando de 10%, até o canto inferior direito da mesa.
Clique em Dados -> Análise What if -> Tabelas de Dados

Etapa 4 - A caixa de diálogo Tabela de dados é aberta.

A caixa de diálogo busca duas entradas - Entrada de linha e Entrada de coluna. Como há apenas uma entrada Ke em consideração, forneceremos uma única entrada de coluna.

Etapa 5 - Vincular a entrada da coluna

Em nosso caso, todas as entradas são fornecidas em uma coluna e, portanto, faremos um link para a entrada da coluna. A entrada da coluna está vinculada ao retorno esperado (Ke). Observe que a entrada deve ser vinculada à fonte original e não àquela que está dentro da tabela

Etapa 6 - Aproveite o resultado

# 2 - Análise de sensibilidade da tabela de dados de duas variáveis no Excel

As tabelas de dados são muito úteis para a análise de sensibilidade no Excel, especialmente no caso de DCF. Uma vez que um caso base é estabelecido, a análise de DCF deve sempre ser testada em vários cenários de sensibilidade. O teste envolve o exame do efeito incremental de várias mudanças nas premissas (custo de capital, taxas de crescimento terminal, menor crescimento da receita, maiores requisitos de capital, etc.) sobre o valor justo das ações.

Tomemos a análise de sensibilidade no Excel com um exemplo financeiro da Análise de Fluxo de Caixa Descontado do Alibaba.

Com as premissas básicas do Custo de Capital de 9% e taxa de crescimento constante de 3% , chegamos à avaliação justa de $ 191,45 bilhões.

Vamos agora supor que você não concorda totalmente com as premissas de custo de capital ou as premissas de taxa de crescimento que assumi na Avaliação IPO do Alibaba. Você pode querer alterar as premissas e acessar o impacto nas avaliações.

Uma maneira é alterar as suposições manualmente e verificar os resultados de cada alteração. (palavra-código - método Donkey!)

No entanto, estamos aqui para discutir uma maneira muito melhor e eficiente de calcular a avaliação usando a análise de sensibilidade no Excel que não só economiza tempo, mas também nos fornece uma maneira de visualizar todos os detalhes de saída em um formato eficaz.

Se realizarmos a análise de variações hipotéticas no Excel de maneira profissional nos dados acima, obteremos a seguinte saída.

Aqui, as entradas de linha consistem em mudanças no custo de capital ou WACC (7% a 11%)
As entradas da coluna consistem em mudanças nas taxas de crescimento (1% a 6%)
O ponto de intersecção é a Avaliação Alibaba. Por exemplo. usando nosso caso-base de 9% WACC e taxas de crescimento de 3%, obtemos a avaliação de $ 191,45 bilhões.

Com esse pano de fundo, vamos agora ver como podemos preparar essa análise de sensibilidade no Excel usando tabelas de dados bidimensionais.

Etapa 1 - Crie a estrutura da tabela conforme mostrado abaixo

Como temos dois conjuntos de premissas - Custo de Capital (WACC) e Taxas de crescimento (g), você precisa preparar uma tabela fornecida a seguir.
Você está livre para alternar as entradas de linha e coluna. Em vez de WACC, você pode ter taxas de crescimento e vice-versa.

Etapa 2 - Vincular o ponto de interseção à célula de saída.

O ponto de intersecção das duas entradas deve ser usado para vincular a saída desejada. Neste caso, queremos ver o efeito dessas duas variáveis (WACC e taxa de crescimento) no valor do patrimônio líquido. Portanto, vinculamos a célula de interseção à saída.

Etapa 3 - Abra a tabela bidimensional de dados

Selecione a tabela que você criou
Em seguida, clique em Dados -> Análise What if -> Tabelas de Dados

Etapa 4 - Forneça as entradas de linha e entradas de coluna.

A entrada de linha é o Custo de Capital ou Ke.
A entrada da coluna é a taxa de crescimento.
Lembre-se de vincular essas entradas da fonte de suposição original e não de qualquer lugar dentro da tabela

Etapa 5 - Aproveite a saída.

A maioria dos valores de produção pessimistas estão no canto superior direito, onde o custo de capital é de 11% e a taxa de crescimento é de apenas 1%
O valor mais otimista do Alibaba IPO é quando Ke é 7% eg é 6%
O caso base que calculamos para taxas de crescimento de 9% ke e 3% fica no meio.
Essa análise de sensibilidade bidimensional na tabela do Excel fornece aos clientes uma análise de cenário fácil que economiza muito tempo.

# 3 - Atingir meta para análise de sensibilidade no Excel

O comando Goal Seek é usado para trazer uma fórmula para um valor específico
Ele faz isso alterando uma das células que é referenciada pela fórmula
O Goal Seek solicita uma referência de célula que contém uma fórmula (a célula Set). Ele também pede um valor, que é o valor que você deseja que a célula iguale
Finalmente, o Goal Seek pede que uma célula seja alterada para levar a célula Set ao valor necessário

Vamos dar uma olhada no DCF da Alibaba IPO Valuation.

Como sabemos pelo DCF, as taxas de crescimento e a avaliação estão diretamente relacionadas. Taxas de crescimento crescentes aumentam o preço das ações.

Vamos supor que queremos verificar a que taxa de crescimento o preço das ações atingirá $ 80?

Como sempre, podemos fazer isso manualmente alterando as taxas de crescimento para continuar a ver o impacto no preço das ações. Novamente, este será um processo tedioso, podemos ter que inserir taxas de crescimento muitas vezes para garantir que o preço das ações corresponda a $ 80 em nosso caso.

No entanto, podemos usar uma função como Goal Seek no Excel para resolver isso em etapas fáceis.

Passo 1 - Clique na célula cujo valor você deseja definir. (A célula Set deve conter uma fórmula)

Etapa 2 - Escolha Ferramentas, Atingir Meta no menu e a seguinte caixa de diálogo aparecerá:

O comando Goal Seek sugere automaticamente a célula ativa como a célula Set.
Isso pode ser sobrescrito com uma nova referência de célula ou você pode clicar na célula apropriada na planilha.
Agora insira o valor desejado que esta fórmula deve atingir.
Clique dentro da caixa “Para o valor” e digite o valor que você deseja que a fórmula selecionada iguale
Por fim, clique dentro da caixa “By Changing Cell” e digite ou clique na célula cujo valor pode ser alterado para obter o resultado desejado
Clique no botão OK e a planilha irá alterar a célula para um valor suficiente para que a fórmula alcance seu objetivo.

Etapa 3 - Aproveite a saída.

Goal Seek também informa que a meta foi alcançada

Conclusão

A análise de sensibilidade no Excel aumenta sua compreensão do comportamento financeiro e operacional do negócio. Como aprendemos com as três abordagens - tabelas de dados unidimensionais, tabelas de dados bidimensionais e busca de metas, que a análise de sensibilidade é extremamente útil no campo financeiro, especialmente no contexto de avaliações - DCF ou DDM.

No entanto, você também pode obter uma compreensão de nível macro da empresa e do setor em geral. Você pode desenvolver casos para refletir a sensibilidade da avaliação a mudanças nas taxas de juros, recessão, inflação, PIB, etc. na avaliação. O pensamento e o bom senso devem ser empregados no desenvolvimento de casos de sensibilidade razoáveis e úteis.

Qual o proximo?

Se você aprendeu algo sobre Análise de Sensibilidade no Excel, deixe um comentário abaixo. Diz-me o que pensas. Muito obrigado e tome cuidado. Aprendizagem feliz!

Você também pode dar uma olhada nestes artigos abaixo para aprender mais sobre Avaliações e Finanças Corporativas -