Como fazer uma análise de sensibilidade no Excel

Aprenda a fazer uma análise de sensibilidade no Excel com tabelas de uma e duas variáveis e a identificar os pressupostos que podem mudar uma decisão.

Matriz Excel de sensibilidade ao preço e ao volume de vendas
Pontos principais
  • Use uma tabela de uma variável para isolar o volume e uma de duas variáveis para testar o preço e o volume em conjunto.
  • Compare o centro e um canto da tabela com o modelo.
  • No exemplo, a 100 dólares por unidade, o limiar de rentabilidade é de 875 unidades.

Se o orçamento indicar um resultado operacional de 5 000 dólares, que margem existe para baixar o preço ou ficar aquém do objetivo de vendas? A análise de sensibilidade recalcula o mesmo modelo com pressupostos diferentes. Este guia mostra como criar tabelas de uma e duas variáveis no Excel, verificar os resultados face ao cenário base e encontrar o volume necessário para atingir o limiar de rentabilidade.

O que é a análise de sensibilidade?

A análise de sensibilidade mede a alteração do resultado de um modelo quando se variam determinadas entradas. Parta de um cenário base, altere as entradas que pretende testar e mantenha os restantes pressupostos constantes. A diferença mostra a exposição do resultado às alterações testadas.

Num orçamento de 100 dólares por unidade e vendas de 1 000 unidades, o preço e o volume são as entradas e o resultado operacional é a saída. Um teste de uma variável mostra o efeito de variar o volume com o preço em 100 dólares. Um teste de duas variáveis compara as combinações de preço e volume.

O método também se aplica a outros modelos financeiros. Uma previsão de tesouraria pode testar o efeito de cobranças mais lentas no saldo de fim do mês. Um modelo de avaliação pode testar o efeito da taxa de desconto no valor atual líquido. Os intervalos devem refletir a decisão em causa.

Análise de sensibilidade e análise de cenários

Use a análise de sensibilidade para medir a variação do resultado ao longo de um intervalo de preços ou volumes. A análise de cenários avalia pressupostos que podem concretizar-se em conjunto. Uma promoção pode incluir um preço mais baixo, um volume previsto mais alto e custos de marketing adicionais. A matriz ajuda a isolar efeitos e o cenário avalia o plano completo.

Se o trimestre terminou, a análise de desvios ajuda a explicar por que motivo o resultado real divergiu do orçamento. Se já conhece o resultado pretendido, a função Atingir Objetivo do Excel calcula uma única entrada. Pode, por exemplo, estimar o volume que dá um resultado operacional de zero a um preço fixo.

Como escolher valores e intervalos para a análise de sensibilidade

Comece pela decisão e pelo resultado que poderá alterá-la. Para aprovar um desconto, considere o resultado operacional e o volume necessário para o limiar de rentabilidade. Se a preocupação for a liquidez, o saldo mínimo em caixa pode ser mais relevante.

Registe o valor base, os limites do teste, a fonte e a pessoa que pode validar a escolha. Para o preço, use propostas recentes ou uma política de descontos aprovada. Para o volume, considere períodos comparáveis, encomendas recebidas ou previsões. Mantenha períodos e unidades consistentes. Para taxas, distinga alterações relativas de pontos percentuais. Somar dois pontos a 10% dá 12%. Aumentar 10% em 2% dá 10,2%.

No exemplo seguinte, o preço varia de 95 a 105 dólares e as vendas trimestrais de 800 a 1 200 unidades. Os custos seguem os pressupostos de base. Num orçamento real, confirme se o intervalo cruza o limite de capacidade ou um escalão de preços do fornecedor que possa alterar os custos.

Como fazer a análise de sensibilidade no Excel

Use uma única folha no Excel para computador. Introduza os pressupostos e a fórmula do resultado para criar o modelo base.

Pressupostos do modelo base e referências de células no Excel
CélulaElemento do modeloValor ou fórmula
B2Preço de venda unitário100
B3Unidades vendidas no trimestre1000
B4Custo variável unitário60
B5Custos fixos trimestrais35000
B6Resultado operacional trimestral=(B2-B4)*B3-B5

O cenário base em B6 deve indicar 5 000 dólares. Use esta célula como referência do resultado nas duas tabelas.

Criar uma tabela de dados de uma variável

Comece pelo volume e mantenha o preço em 100 dólares por unidade.

  1. Em =$B$6, introduza E3. Em 800, introduza 1000, 1200 e D4:D6, um valor por linha.
  2. Selecione D3:E6 e abra Dados → Análise de Hipóteses → Tabela de Dados.
  3. Na Célula de entrada da coluna, indique $B$3. Deixe a Célula de entrada da linha em branco e selecione OK.

O Excel preenche E4:E6 com −3 000 dólares, 5 000 dólares e 13 000 dólares. O resultado para 1 000 unidades deve coincidir com os 5 000 dólares do cenário base. Cada unidade adicional contribui com 40 dólares para cobrir os custos fixos e gerar um resultado positivo.

Criar uma tabela de dados de duas variáveis

Coloque o preço na linha superior e o volume na coluna à esquerda.

  1. Em =$B$6, introduza H3. Em 95, introduza 100, 105 e I3:K3. Em 800, introduza 1000, 1200 e H4:H6.
  2. Selecione H3:K6 e abra Dados → Análise de Hipóteses → Tabela de Dados.
  3. Defina a Célula de entrada da linha como $B$2 para os preços no topo. Defina a Célula de entrada da coluna como $B$3 para os volumes à esquerda. Selecione OK.

Resultado operacional trimestral por preço e volume de vendas.

Resultado operacional trimestral por preço e volume de vendas
Unidades vendidas no trimestre95 USD por unidade100 USD por unidade105 USD por unidade
800−$7,000−$3,000$1,000
1,000$0$5,000$10,000
1,200$7,000$13,000$19,000

Compare a célula central com B6 e calcule manualmente um canto. A 95 dólares e 800 unidades, cada unidade contribui com 35 dólares. São 28 000 dólares antes dos custos fixos e −7 000 dólares após a dedução. Compare também a coluna de 100 dólares com a tabela de uma variável. Estas verificações detetam referências erradas, células trocadas ou resultados desatualizados.

Como interpretar uma tabela de sensibilidade

Comece pelos valores mais relevantes para a decisão. Com 1 000 unidades, baixar o preço de 100 para 95 dólares elimina os 5 000 dólares do cenário base. A 100 dólares, o resultado é zero com 875 unidades, ou seja, 35 000 dólares de custos fixos divididos por 40 dólares de margem unitária. A 95 dólares, a margem baixa para 35 dólares e são necessárias 1 000 unidades para atingir o limiar.

A tabela mostra o resultado de cada combinação. Se a descida do preço puder aumentar a procura, confirme a base da previsão e se há capacidade para responder ao volume adicional. Com os pressupostos atuais, é preciso vender pelo menos 1 143 unidades a 95 dólares para manter os 5 000 dólares iniciais. Um cenário promocional pode incluir custos adicionais e uma estimativa baseada em resultados anteriores.

Uma nota para a direção pode apresentar a decisão deste modo.

A 95 dólares por unidade, o resultado trimestral passa de 5 000 para 0 dólares se as vendas ficarem nas 1 000 unidades. Manter os 5 000 dólares exige pelo menos 1 143 unidades com os custos atuais. Antes de aprovar o desconto, confirme se as promoções anteriores sustentam esse volume e se as primeiras encomendas estão em linha com a previsão.

Guarde com a nota a versão do livro e a fonte dos intervalos. No Kuse, pode transformar uma tabela de sensibilidade verificada numa nota de decisão a partir da matriz, das notas sobre pressupostos e dos documentos de apoio. Inclua o cenário base, o limiar decisivo e os elementos por confirmar. O responsável valida os números e o texto no Excel e reutiliza o formato na próxima previsão.

Perguntas frequentes

Porque é que a tabela de sensibilidade mostra o mesmo valor em todas as células?

Altere manualmente uma entrada, verifique se o resultado ligado muda e restaure o valor base. Se o resultado mudar e a tabela ficar igual, verifique a referência e as células de entrada. Se o cálculo automático excluir tabelas de dados, selecione as fórmulas da tabela e volte a calcular.

Porque é que o cenário base da tabela difere do modelo?

Confirme que os cabeçalhos usam os valores exatos do cenário base. Um arredondamento pode mostrar o mesmo número embora o Excel receba um valor diferente. Confirme a referência, recalcule a tabela e confira os restantes pressupostos.

Como confirmar quais são as células de entrada de uma tabela de dados existente?

Selecione uma célula calculada e veja a barra de fórmulas. No Excel para computador, a fórmula pode mostrar as referências de linha e coluna, por essa ordem. Siga as referências até aos pressupostos e confirme que entram no cálculo.

Quando devo usar uma tabela de duas variáveis ou a função Atingir Objetivo?

Use uma tabela de duas variáveis para comparar combinações, como os resultados por preço e volume. Use Atingir Objetivo quando souber o resultado pretendido e quiser encontrar uma única entrada, por exemplo o volume que produz um resultado nulo a preço fixo.

Como escolher valores realistas para a análise de sensibilidade?

Parta do cenário base aprovado ou atual e defina os limites com dados históricos, condições contratuais, propostas de fornecedores ou previsões. Inclua valores perto dos limiares de decisão e assinale os valores exploratórios.

Como encontrar o limiar de rentabilidade numa tabela de sensibilidade?

Procure um resultado nulo ou duas células adjacentes em que o resultado muda de sinal. Se o limiar estiver entre os valores da tabela, resolva a fórmula ou use Atingir Objetivo com uma entrada. Para o volume, arredonde por excesso se necessário e confira o resultado no modelo.