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.
- 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.
| Célula | Elemento do modelo | Valor ou fórmula |
|---|---|---|
B2 | Preço de venda unitário | 100 |
B3 | Unidades vendidas no trimestre | 1000 |
B4 | Custo variável unitário | 60 |
B5 | Custos fixos trimestrais | 35000 |
B6 | Resultado 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.
- Em
=$B$6, introduzaE3. Em800, introduza1000,1200eD4:D6, um valor por linha. - Selecione
D3:E6e abra Dados → Análise de Hipóteses → Tabela de Dados. - 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.
- Em
=$B$6, introduzaH3. Em95, introduza100,105eI3:K3. Em800, introduza1000,1200eH4:H6. - Selecione
H3:K6e abra Dados → Análise de Hipóteses → Tabela de Dados. - Defina a Célula de entrada da linha como
$B$2para os preços no topo. Defina a Célula de entrada da coluna como$B$3para os volumes à esquerda. Selecione OK.
Resultado operacional trimestral por preço e volume de vendas.
| Unidades vendidas no trimestre | 95 USD por unidade | 100 USD por unidade | 105 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.