Análise de sensibilidade no tutorial do Excel: Visão geral, tipos e práticas recomendadas

Ao projetar um modelo em uma planilha, podemos estar interessados em saber como um resultado pode mudar ao alterar o valor de uma das variáveis envolvidas. Esse processo pode ser realizado no Excel, executando uma análise de sensibilidade

Saber como realizar uma análise de sensibilidade é fundamental se você for um profissional de dados que trabalha com modelagem financeira ou processos de decisão. 

Neste artigo, exploraremos a análise de sensibilidade em profundidade. Abordaremos os fundamentos dessa técnica útil dentro da família de análise "what-if" e como conduzir e interpretar os resultados da análise de sensibilidade básica e avançada no Excel. Por fim, discutiremos o papel da IA e do machine learning no avanço de uma análise de sensibilidade mais poderosa, bem como as práticas recomendadas e as armadilhas comuns. 

Se você deseja obter alguma experiência prática, confira nosso curso Análise financeira no Excel, que aborda detalhadamente a análise hipotética. 

O que é análise de sensibilidade no Excel?

A abordagem mais comum na análise de sensibilidade é examinar como as alterações em uma variável de cada vez afetam o resultado final, mantendo todos os outros fatores constantes. Isso é repetido para todas as variáveis em consideração. Ele ajuda a identificar os fatores mais influentes em um modelo, permitindo que as empresas se concentrem nos principais fatores que afetam seu desempenho. 

A análise de sensibilidade ajuda as empresas a priorizar os principais riscos e oportunidades, identificando quais fatores têm o maior impacto sobre o desempenho. Devido à sua natureza, a análise de sensibilidade é comum em finanças, negócios e gerenciamento de projetos, por exemplo, para calcular como as taxas de juros podem mudar ao longo do tempo ou para entender diferentes tendências de crescimento. 

Além disso, a sensibilidade é frequentemente combinada com a análise de cenários, que normalmente analisa a combinação de variáveis ao mesmo tempo. Essas duas análises hipotéticas estudam como uma variável dependente reagirá a determinados inputs. No entanto, a análise de cenário é específica para um determinado "cenário", enquanto a análise de sensibilidade é mais aberta porque fornece uma gama de entradas e valores. 

O objetivo da análise de sensibilidade é entender como a variável dependente reage a um intervalo de valores de entrada, conhecidos como variáveis independentes.

Ao incorporar essa técnica à tomada de decisões, as organizações podem ajustar as estratégias de forma proativa e aumentar a resistência à incerteza. 

Para obter uma explicação mais detalhada da teoria por trás da análise de sensibilidade, recomendamos enfaticamente o site Advanced Probability: Incerteza no curso de dados.

Muitas vezes, a análise de sensibilidade é apresentada em uma tabela de sensibilidade com formatação condicional que destaca os valores do maior para o menor. 

Abaixo está um exemplo da sensibilidade da oferta e da demanda ao preço. A oferta e a demanda são as variáveis independentes rotuladas nos eixos, e o preço é a variável dependente, que contém os valores na tabela.

Nas próximas seções, trabalharemos com tabelas de sensibilidade semelhantes no Excel.

Tabela de sensibilidade. Fonte: DataCamp

Primeiros passos com a análise de sensibilidade no Excel

Vamos ver como você pode realizar uma análise de sensibilidade no Excel, seguindo cada etapa do processo. 

Ferramentas e recursos do Excel

O Excel é uma ferramenta útil para criar modelos matemáticos, inclusive análises hipotéticas. A análise de variações hipotéticas é essencialmente a criação de fórmulas que permitem que as questões sejam exploradas, como uma análise de sensibilidade.

As versões mais recentes do Excel vêm com a guiaDados, que oferece uma infinidade de ferramentas e possibilidades para você trabalhar com dados. Isso inclui um botão What-if Analysis na faixa de opções. Ele inclui três ferramentas. Obotão Data Table foi projetado especificamente para que você crie tabelas de sensibilidade.

Para realizar a análise de cenário, basta clicar no Gerenciador de cenários para que você possa analisar o cenário.

Também usaremos o Solverum suplemento que você pode ativar no Desenvolvedor Guia Desenvolvedor.

O Solver é usado em análises hipotéticas para encontrar um valor ideal (máximo ou mínimo) para uma fórmula em uma célula - chamada de célula objetivo - sujeito a restrições ou limites nos valores de outras células de fórmula em uma planilha.

Antes de ativar o Solver, você deve acessar a guia Desenvolvedor, que não é exibida por padrão. Para exibir essa guia:

• Ir para Arquivo > Opções
• Selecione Personalizar faixa de opções no painel esquerdo
• Na coluna da direita, em "Guias principaismarque a caixa para Desenvolvedor

Execução de análise de sensibilidade de variável única

Agora que temos nossas ferramentas prontas, vamos começar criando uma análise de sensibilidade simples e de variável única no Excel.

Configurando seu modelo

A etapa inicial para realizar uma análise de sensibilidade no Excel é identificar os inputs e outputs nos quais o modelo se baseará. 

Para este tutorial, usaremos a análise de sensibilidade para estudar como o lucro líquido de uma empresa de venda de guitarras variará se alterarmos o valor de determinadas variáveis de entrada, como o número de guitarras vendidas, o preço da guitarra ou os custos de produção.

Abaixo, você pode encontrar as tabelas com as variáveis de entrada e saída. 

Ao usar tabelas de dados para análise de sensibilidade, é importante que você vincule as células de saída às variáveis de entrada para que o Excel possa entender a relação entre as variáveis do modelo. Esse processo é feito por meio de fórmulas. Em nosso exemplo:

• Receita é calculada pela multiplicação do preço por unidade pelas unidades vendidas (=B2*B4)
• Custo de vendas é calculado pela multiplicação do custo de produção por unidade pelas unidades vendidas (=B3*B4)
• Lucro é calculado subtraindo as receitas do custo de vendas (=B7-B8).

Criando uma tabela de dados unidirecional

Você pode querer saber como o lucro muda se aumentarmos ou diminuirmos o preço por unidade, mantendo inalterados o custo de produção e o número de unidades vendidas. Quando analisamos como uma variável de saída muda quando modificamos o valor de uma variável de entrada, precisamos da chamada tabela de dados unidirecional.

O GIF a seguir mostra como você pode criar uma tabela de dados unidirecional no Excel. Como você pode ver, a tabela recalcula automaticamente o lucro para cada unidade vendida. Naturalmente, o lucro quando vendemos 250 unidades é o mesmo que nos parâmetros originais (ou seja, 50.000 euros).

Você também pode querer estudar como as diferentes variáveis de saída mudam quando modificamos as vendas unitárias. Isso também é bastante fácil, conforme mostrado abaixo. No GIF a seguir, calculamos como as vendas unitárias afetam os lucros e o custo das vendas.

Realização de análises de sensibilidade de duas variáveis

Em vez de analisar como um output é afetado por um input, você também pode criar uma tabela de duas variáveis para analisar o impacto de duas variáveis de input. Digamos que você queira saber como o lucro muda se modificarmos as vendas de guitarras e o preço por guitarra. 

Primeiro, você precisa criar uma tabela bidimensional com os intervalos de preço por unidade nas linhas e as vendas unitárias nas colunas. Em seguida, no canto superior esquerdo da tabela, escolha a variável que você deseja estudar. Por fim, escolha a referência de célula das variáveis independentes na tabela de variáveis de entrada (ou seja, preço por unidade e unidades vendidas).

Como você pode ver, depois de criar a tabela, você pode facilmente alterar a variável de saída no canto superior esquerdo para outra variável, e o Excel recalculará todos os valores

Interpretação dos resultados da análise de sensibilidade do Excel

Agora que você tem suas tabelas de dados, a etapa final é interpretar os resultados para gerar insights sobre como as alterações nos inputs afetam os outputs.

Analisando as saídas das tabelas de dados

Com essa análise de sensibilidade, podemos tomar uma decisão informada sobre o lucro. Aqui estão alguns insights:

• Se aumentarmos os preços das guitarras para 350 euros, poderemos igualar o lucro atual de 50.000 euros com a venda de apenas 200 guitarras.
• Se reduzirmos o preço das guitarras para 200 euros, precisaremos vender 500 unidades para atingir os lucros atuais.
• Se reduzirmos o número de guitarras vendidas para 150, não conseguiremos igualar o lucro atual, a menos que alteremos o preço das guitarras para mais de 400 euros.

A interpretação da análise dependerá das necessidades específicas de sua empresa. Fazer perguntas como quantas guitarras você pode produzir, qual é o nível mínimo de lucros que você precisa para manter a empresa sólida e o que está ao seu alcance para reduzir o custo de produção da guitarra será fundamental para extrair insights significativos da análise de sensibilidade.

Uso do Solver para análise de sensibilidade

Ao realizar uma análise de sensibilidade, você retorna um intervalo de possíveis resultados com base em alterações nas variáveis de entrada de um modelo. No entanto, talvez você queira saber mais detalhes sobre o funcionamento interno do seu modelo.

Felizmente, o Excel vem com uma ferramenta poderosa chamada Solver, que pode ajudar você nessa tarefa. Conforme mencionado, o Solver é usado em análises hipotéticas para encontrar um valor ideal (máximo ou mínimo) para uma fórmula sujeita a restrições ou limites nos valores de outras células de fórmula em uma planilha. 

Isso pode soar como o objetivo da busca de metas, outro tipo de análise de variações hipotéticas que se concentra no ajuste de uma única variável para atingir o resultado desejado. No entanto, o Solver é muito mais do que o Goal Seek. 

Primeiro, o Solver permite que você calcule um resultado ideal com base em mais de uma variável e permite que você inclua restrições nos modelos. 

Além disso, o Solver também vem com um recurso de sensibilidade opcional que lhe dá uma visão de como a solução ideal muda quando você altera os coeficientes do modelo. 

Para ilustrar o poder do Solver, vamos voltar à nossa empresa de guitarras. Imagine que a empresa fabrica dois modelos de violão (modelos A e B), cada um dos quais requer uma determinada combinação de mogno e cedro. Cada modelo tem um preço diferente. 

A empresa quer saber quantas unidades de cada modelo deve produzir para maximizar seus lucros, considerando a disponibilidade atual de mogno e cedro. 

Há três coisas que queremos que o Solver calcule: Lucro, unidades do Modelo A e unidades do Modelo B. A imagem acima mostra os inputs, as fórmulas e as restrições nas quais o modelo se baseará. 

No GIF a seguir, mostramos como usar o Solver para encontrar os valores ideais das unidades para maximizar os benefícios, usando restrições de disponibilidade de madeira e garantindo que as unidades sejam números inteiros. Há várias estratégias que o Solver pode usar para otimizar o modelo, tanto para problemas lineares quanto não lineares. No nosso caso, usaremos o Simple LP. 

Depois de adicionar todos os valores dos parâmetros, o Solver estima que as 48 unidades do modelo A e 9 do modelo B são a melhor combinação para maximizar o lucro (17.591 euros). Você pode clicar em Sensibilidade antes de fechar a caixa do Solver, e o Excel criará uma nova planilha com um relatório de Sensibilidade.

A sensibilidade nos fornece informações valiosas para estimar como os lucros variariam se alterássemos as variáveis de entrada. O Shadow Price indica quanto o lucro aumentaria se você tivesse uma unidade adicional de um recurso restrito. Nesse exemplo, cada metro quadrado adicional de mogno aumenta o lucro em 98 euros, enquanto o cedro aumenta o lucro em 103 euros, destacando o maior potencial de retorno sobre o investimento do cedro

Outro elemento importante no relatório de sensibilidade são os intervalos dentro dos quais você poderia manter os preços dos modelos para manter os resultados do modelo inalterados. 

O lucro é calculado multiplicando-se as unidades de cada modelo por seu preço, que é dado no Valor final e Coeficiente objetivo colunas. Os intervalos são fornecidos nas colunas Aumento permitido e Diminuição permitida.

Por exemplo, o Solver encontraria o mesmo número de unidades a serem fabricadas se você aumentasse o valor do Modelo A em 1140 euros ou o diminuísse em 180 euros.

Visualização de dados com formatação condicional

Se você criar tabelas de sensibilidade unidirecionais e bidirecionais, a formatação dos resultados pode ser um divisor de águas para identificar rapidamente informações relevantes e melhorar a tomada de decisões. A formatação condicional é fundamental para criar limites importantes e mapas de calor atraentes. 

Vamos voltar à nossa tabela bidirecional. Em vez de mostrar a tabela em células brancas, podemos usar a formatação condicional para criar um mapa de calor multicolorido de uma escala de duas cores, estabelecendo um limite personalizado, conforme mostrado no GIF.

Integração de IA e machine learning

Vamos abordar brevemente como a IA e o machine learning podem ajudar a melhorar sua análise de sensibilidade.

Análise de sensibilidade aprimorada por IA

Até agora, vimos como realizar manualmente uma análise de sensibilidade. No entanto, à medida que a complexidade dos seus modelos aumenta, a realização e a interpretação dos resultados da análise de sensibilidade podem consumir muito tempo. 

É aqui que a IA entra em cena. Com a IA e o machine learning, você pode permitir que as máquinas façam os cálculos sempre que determinadas condições forem atendidas e inserir os resultados em painéis atraentes. 

Depois de conhecer os cálculos que você deseja realizar, a próxima etapa é definir claramente um pipeline de IA para simplificar e automatizar o processo. 

Você está curioso para saber como fazer isso? Confira nosso tutorial de machine learning, pipelines, implantação e MLOps.

Simulações de Monte Carlo

A Simulação de Monte Carlo é uma técnica matemática usada para modelar a probabilidade de diferentes resultados em um processo que não pode ser facilmente previsto devido à intervenção de variáveis aleatórias.

Primeiro, o problema é modelado por uma distribuição de probabilidade para cada variável que tenha incerteza inerente. Em seguida, um grande número de amostras aleatórias é extraído dessas distribuições de probabilidade, e essas amostras são usadas para calcular os resultados. 

Esse processo é repetido várias vezes para criar uma distribuição de resultados possíveis, que podem ser analisados estatisticamente para fornecer previsões sobre como um sistema se comportará. O Monte Carlo ajuda você a analisar a incerteza de forma mais abrangente do que as tabelas de sensibilidade tradicionais.

Você pode aprender tudo sobre essa poderosa técnica em nosso Guia de Simulação de Monte Carlo no Excel.

Dada a natureza da simulação de Monte Carlo, você pode usá-la para turbinar sua análise de sensibilidade e descobrir quais variáveis têm o impacto mais significativo no resultado. 

Em nosso exemplo da guitarra, podemos manter o preço de custo e as unidades vendidas constantes e alterar o preço unitário para entender as alterações nas estimativas. Em seguida, repita o mesmo processo para as duas variáveis restantes, uma a uma. Por fim, essa técnica ajuda a entender em qual variável você deve concentrar esforços para melhorar a precisão.

Práticas recomendadas e armadilhas comuns

Embora o Excel torne a análise de sensibilidade extremamente fácil e acessível, ainda existem algumas armadilhas comuns que você deve ter em mente. Aqui estão algumas coisas que você deve considerar.

Projetando modelos robustos

Como você viu neste tutorial, para que a análise de sensibilidade funcione, o Excel deve conhecer as relações entre as variáveis de entrada e saída. Isso exige que você use as fórmulas com sabedoria, fornecendo referências e intervalos de células claramente definidos. 

Se você quiser adicionar novos valores às tabelas de dados, uma boa prática para evitar possíveis problemas durante o recálculo é usar as tabelas do Excel, pois elas são inerentemente dinâmicas, o que significa que se expandem automaticamente quando novas linhas ou colunas são adicionadas. 

Isso ajudará você a fazer os recálculos corretos da fórmula para manter as informações no formato correto. 

Reduzir erros e garantir a precisão

Mesmo que suas análises de sensibilidade estejam claramente definidas, você precisa verificar os resultados com cuidado. Os testes manuais e os gráficos são ótimas opções para visualizar seus resultados e verificar se há discrepâncias ou anomalias nos resultados.  

No entanto, é importante lembrar que a análise de sensibilidade pode simplesmente não funcionar para o seu caso de uso. Essa análise baseia-se em um conjunto de suposições simples, como a independência das variáveis de entrada, a existência de relações lineares e a natureza estática das variáveis de entrada. 

No entanto, seu modelo pode ser mais complexo e pode ser afetado por fatores externos que a análise de sensibilidade ignora por natureza. Portanto, apesar de seus benefícios, é importante que você tenha uma compreensão abrangente do seu modelo além da sensibilidade para garantir uma análise correta.

Conclusão

Parabéns por você ter chegado até o final deste tutorial. A análise de sensibilidade é uma poderosa análise hipotética que pode mudar o jogo em seu processo de tomada de decisão. Existem apenas análises hipotéticas para substituir seus modelos financeiros, como o Goal Seek ou o Scenario Manager. Felizmente, a DataCamp está aqui para ajudar você. Confira nossos cursos e materiais selecionados sobre o Excel:

• Análise de dados no Excel
• Modelagem financeira no Excel 
• Fundamentos do Excel 
• Manipulação de dados na folha de dicas do Excel 
• Análise de dados no Planilhas Google 
• Funções avançadas do Excel 
• Como calcular intervalos de confiança no Excel 
• Planilha de fórmulas do Excel 
• Como calcular o coeficiente de variação no Excel 
• Simulação de Monte Carlo no Excel: Um guia completo