Análise de Sensibilidade no Excel (Folha de Dados de Amostra)

Os processos na área de finanças estão sempre interligados – um fator depende do outro e muda com ele. Acompanhe essas mudanças e entenda o que esperar no futuro, talvez usando funções do Excel e métodos de planilha.

Obtendo vários resultados com uma tabela de dados

Os recursos de folha de dados são elementos de análise hipotética, geralmente feita por meio do Microsoft Excel. Este é o segundo nome para análise de sensibilidade.

Visão geral

Uma tabela de dados é um tipo de intervalo de células que pode ser usado para resolver problemas alterando os valores em algumas células. Ele é criado quando é necessário acompanhar as alterações nos componentes da fórmula e receber atualizações dos resultados, de acordo com essas alterações. Vamos descobrir como usar tabelas de dados em pesquisas e quais são os tipos.

Noções básicas sobre tabelas de dados

Existem dois tipos de tabelas de dados, elas diferem no número de componentes. Você precisa compilar uma tabela com foco no número de valores uXNUMXbuXNUMXbque você precisa verificar com ela.

Os estatísticos usam uma tabela de variável única quando há apenas uma variável em uma ou mais expressões que podem alterar seu resultado. Por exemplo, é frequentemente usado em conjunto com a função PMT. A fórmula é projetada para calcular o valor do pagamento regular e leva em consideração a taxa de juros especificada no contrato. Nesses cálculos, as variáveis ​​são escritas em uma coluna e os resultados dos cálculos em outra. Um exemplo de placa de dados com 1 variável:

Em seguida, considere as placas com 2 variáveis. Eles são usados ​​nos casos em que dois fatores influenciam a mudança em qualquer indicador. As duas variáveis ​​podem acabar em outra tabela associada ao empréstimo, que pode ser usada para determinar o prazo ideal de pagamento e o valor da mensalidade. Neste cálculo, você também precisa usar a função PMT. Um exemplo de uma tabela com 2 variáveis:

Criando uma tabela de dados com uma variável

Considere o método de análise usando o exemplo de uma pequena livraria com apenas 100 livros em estoque. Alguns deles podem ser vendidos mais caros (US$ 50), o restante custará menos aos compradores (US$ 20). A receita total da venda de todas as mercadorias é calculada – o proprietário decidiu que venderia 60% dos livros a um preço alto. Você precisa descobrir como a receita aumentará se você aumentar o preço de um volume maior de mercadorias – 70% e assim por diante.

Preste atenção! A receita total deve ser calculada usando uma fórmula, caso contrário não será possível compilar uma tabela de dados.

1. Selecione uma célula livre longe da borda da planilha e escreva a fórmula nela: =Célula de receita total. Por exemplo, se a renda estiver escrita na célula C14 (a designação aleatória é indicada), você precisa escrever isso: =S14.
2. Escrevemos a porcentagem do volume de mercadorias na coluna à esquerda desta célula – não abaixo dela, isso é muito importante.
3. Selecionamos o intervalo de células onde estão localizadas a coluna de porcentagem e o link para a receita total.

4. Encontramos na guia “Dados” o item “E se análise” e clicamos nele – no menu que se abre, selecione a opção “Tabela de dados”.

5. Uma pequena janela será aberta onde você precisa especificar uma célula com a porcentagem de livros vendidos inicialmente a um preço alto na coluna “Substituir valores por linhas em …”. Esta etapa é feita para recalcular a receita total levando em consideração o percentual crescente.

Após clicar no botão “OK” na janela onde foram inseridos os dados para a compilação da tabela, os resultados dos cálculos aparecerão nas linhas.

Adicionando uma Fórmula a uma Tabela de Dados de Variável Única

A partir de uma tabela que ajudou a calcular uma ação com apenas uma variável, você pode fazer uma ferramenta de análise sofisticada adicionando uma fórmula adicional. Ele deve ser inserido ao lado de uma fórmula já existente – por exemplo, se a tabela for orientada por linhas, inserimos a expressão na célula à direita da existente. Quando a orientação da coluna é definida, escrevemos a nova fórmula sob a antiga. Em seguida, siga o algoritmo:

1. Selecione o intervalo de células novamente, mas agora ele deve incluir a nova fórmula.
2. Abra o menu de análise “what if” e selecione “Datasheet”.
3. Adicionamos uma nova fórmula ao campo correspondente em linhas ou colunas, dependendo da orientação da placa.

Crie uma tabela de dados com duas variáveis

O início de tal tabela é um pouco diferente – você precisa colocar um link para a receita total acima dos valores percentuais. Em seguida, realizamos estas etapas:

1. Escreva as opções de preço em uma linha com um link para renda – uma célula para cada preço.
2. Selecione um intervalo de células.

3. Abra a janela da tabela de dados, como ao compilar uma tabela com uma variável – através da guia “Dados” na barra de ferramentas.
4. Substitua na coluna “Substituir valores por colunas em…” uma célula com um preço inicial alto.
5. Adicione uma célula com o percentual inicial de vendas de livros caros na coluna “Substituir valores por linhas em…” e clique em “OK”.

Como resultado, toda a tabela é preenchida com valores de receitas possíveis com diferentes condições para a venda de mercadorias.

Acelere os cálculos para planilhas contendo tabelas de dados

Se você precisar de cálculos rápidos em uma tabela de dados que não acionem um recálculo de toda a pasta de trabalho, há algumas coisas que você pode fazer para acelerar o processo.

1. Abra a janela de opções, selecione o item “Fórmulas” no menu à direita.
2. Selecione o item “Automático, exceto para tabelas de dados” na seção “Cálculos na pasta de trabalho”.

3. Vamos recalcular os resultados na tabela manualmente. Para fazer isso, selecione as fórmulas e pressione a tecla F.

Outras Ferramentas para Realizar Análise de Sensibilidade

Existem outras ferramentas no programa para ajudá-lo a realizar a análise de sensibilidade. Eles automatizam algumas ações que, de outra forma, teriam que ser feitas manualmente.

1. A função “Seleção de parâmetro” é adequada se o resultado desejado for conhecido e você precisar conhecer o valor de entrada da variável para obter tal resultado.
2. “Search for a solution” é um add-on para resolver problemas. É necessário estabelecer limites e apontar para eles, após o que o sistema encontrará a resposta. A solução é determinada alterando os valores.
3. A análise de sensibilidade pode ser feita usando o Gerenciador de Cenários. Essa ferramenta é encontrada no menu de análise hipotética na guia Dados. Substitui valores em várias células – o número pode chegar a 32. O despachante compara esses valores para que o usuário não precise alterá-los manualmente. Um exemplo de uso do gerenciador de scripts:

Análise de sensibilidade de um projeto de investimento em Excel

A análise de hipóteses é especialmente útil em situações em que a previsão é necessária, como investimento. Os analistas usam esse método para descobrir como o valor das ações de uma empresa mudará como resultado de mudanças em alguns fatores.

Método de Análise de Sensibilidade de Investimento

Ao analisar “e se” use enumeração – manual ou automática. O intervalo de valores é conhecido e eles são substituídos na fórmula um por um. O resultado é um conjunto de valores. Escolha o número apropriado deles. Vamos considerar quatro indicadores para os quais a análise de sensibilidade é realizada no campo das finanças:

1. Valor Presente Líquido – Calculado subtraindo o valor do investimento do valor da receita.
2. Taxa interna de retorno/lucro – indica quanto lucro deve ser recebido de um investimento em um ano.
3. A taxa de retorno é a relação de todos os lucros para o investimento inicial.
4. Índice de lucro descontado – indica a eficácia do investimento.

Fórmula

A sensibilidade de incorporação pode ser calculada usando esta fórmula: Alteração no parâmetro de saída em % / Alteração no parâmetro de entrada em %.

Os parâmetros de saída e entrada podem ser os valores descritos anteriormente.

1. Você precisa saber o resultado em condições padrão.
2. Substituímos uma das variáveis ​​e monitoramos as mudanças no resultado.
3. Calculamos a variação percentual de ambos os parâmetros em relação às condições estabelecidas.
4. Inserimos as porcentagens obtidas na fórmula e determinamos a sensibilidade.

Um exemplo de análise de sensibilidade de um projeto de investimento em Excel

Para uma melhor compreensão da metodologia de análise, é necessário um exemplo. Vamos analisar o projeto com os seguintes dados conhecidos:

1. Preencha a tabela para analisar o projeto nela.

2. Calculamos o fluxo de caixa usando a função OFFSET. Na fase inicial, o fluxo é igual aos investimentos. Em seguida, aplicamos a fórmula: =SE(OFFSET(Número,1;)=2;SOMA(Entrada 1:Saída 1); SOMA(Entrada 1:Saída 1)+$B$ 5)
   As designações de células na fórmula podem ser diferentes, dependendo do layout da tabela. No final, o valor dos dados iniciais é adicionado – o valor residual.

3. Determinamos o período pelo qual o projeto será recompensado. Para o período inicial, usamos esta fórmula: =SUMAMENTE(G7:G17;»<0″). O intervalo de células é a coluna de fluxo de caixa. Para períodos adicionais, aplicamos esta fórmula: =Período inicial+IF(Primeiro e.stream>0; Primeiro e.stream;0). O projeto está no ponto de equilíbrio em 4 anos.

4. Criamos uma coluna para os números desses períodos em que o projeto vale a pena.

5. Calculamos o retorno do investimento. É necessário fazer uma expressão onde o lucro em um determinado período de tempo seja dividido pelo investimento inicial.

6. Determinamos o fator de desconto usando esta fórmula: =1/(1+Disco %) ^ Número.

7. Calculamos o valor presente usando a multiplicação – o fluxo de caixa é multiplicado pelo fator de desconto.

8. Vamos calcular o PI (índice de rentabilidade). O valor presente ao longo do tempo é dividido pelo investimento no início do projeto.

9. Vamos definir a taxa interna de retorno usando a função TIR: =IRR(Intervalo de fluxo de caixa).

Análise de sensibilidade de investimento usando folha de dados

Para a análise de projetos na área de investimento, outros métodos são mais adequados do que uma tabela de dados. Muitos usuários ficam confusos ao compilar uma fórmula. Para descobrir a dependência de um fator em mudanças em outros, você precisa selecionar as células corretas para inserir cálculos e ler dados.

Análise fatorial e de dispersão no Excel com automação de cálculo

Outra tipologia de análise de sensibilidade é a análise fatorial e a análise de variância. O primeiro tipo define a relação entre os números, o segundo revela a dependência de uma variável em relação a outras.

ANOVA no Excel

O objetivo de tal análise é dividir a variabilidade do valor em três componentes:

1. Variabilidade como resultado da influência de outros valores.
2. Alterações devido à relação dos valores que o afetam.
3. Mudanças aleatórias.

Vamos realizar a análise de variância através do suplemento do Excel “Análise de Dados”. Se não estiver ativado, pode ser ativado nas configurações.

A tabela inicial deve seguir duas regras: há uma coluna para cada valor, e os dados nela contidos são organizados em ordem crescente ou decrescente. É necessário verificar a influência do nível de escolaridade sobre o comportamento em conflito.

1. Encontre a ferramenta Análise de Dados na guia Dados e abra sua janela. Na lista, você precisa selecionar a análise de variância unidirecional.

2. Preencha as linhas da caixa de diálogo. O intervalo de entrada é todas as células, excluindo cabeçalhos e números. Agrupar por colunas. Exibimos os resultados em uma nova planilha.

Como o valor na célula amarela é maior que um, a suposição pode ser considerada incorreta – não há relação entre escolaridade e comportamento em conflito.

Análise fatorial no Excel: um exemplo

Vamos analisar a relação de dados na área de vendas – é preciso identificar produtos populares e impopulares. Informações iniciais:

1. Precisamos descobrir para quais bens a demanda aumentou mais durante o segundo mês. Estamos compilando uma nova tabela para determinar o crescimento e declínio da demanda. O crescimento é calculado usando esta fórmula: =SE((Demanda 2- Demanda 1)>0; Demanda 2- Demanda 1;0). Fórmula de diminuição: =SE(Crescimento=0; Demanda 1- Demanda 2;0).

2. Calcule o crescimento da demanda por bens como uma porcentagem: =SE(Crescimento/Resultado 2 =0; Diminuição/Resultado 2; Crescimento/Resultado 2).

3. Vamos fazer um gráfico para maior clareza – selecione um intervalo de células e crie um histograma através da guia “Inserir”. Nas configurações, você precisa remover o preenchimento, isso pode ser feito através da ferramenta Format Data Series.

Análise de variância bidirecional no Excel

A análise de variância é realizada com várias variáveis. Considere isso com um exemplo: você precisa descobrir com que rapidez a reação a um som de volume diferente se manifesta em homens e mulheres.

1. Abrimos “Análise de Dados”, na lista você precisa encontrar uma análise de variância bidirecional sem repetições.
2. Intervalo de entrada – células que contêm dados (sem cabeçalho). Exibimos os resultados em uma nova planilha e clicamos em “OK”.

O valor F é maior que o F-crítico, o que significa que o piso afeta a velocidade de reação ao som.

Conclusão

Neste artigo, foi discutida detalhadamente a análise de sensibilidade em uma planilha Excel, para que cada usuário possa entender os métodos de sua aplicação.