Utilização de Recursos Estatísticos no Excel: Regressão Linear e Previsão

1. Introdução

Muitas vezes, sabemos que determinadas variáveis de um problema estão relacionadas entre si e utilizamos esse conhecimento para fazer simulações. Por exemplo, podemos relacionar os gastos com propaganda com as vendas de um produto e, sabendo que haverá uma variação nos gastos com propaganda, estimar uma variação no volume de vendas.

O objetivo deste estudo é apresentar recursos estatísticos do Excel que auxiliam na determinação de relações entre variáveis, permitindo a criação de equações que expliquem o comportamento dessas variáveis. Com isso, podemos evitar “chutes” e tomar decisões mais embasadas.

Neste material, vamos abordar dois métodos principais: a função PREVISÃO (para regressão linear simples) e a função PROJ.LIN (para regressão linear múltipla). Ambos são ferramentas poderosas para análise de dados e previsões.

2. O que é Regressão Linear?

A regressão linear é uma técnica estatística que busca estabelecer uma relação linear entre uma variável dependente (Y) e uma ou mais variáveis independentes (X). O objetivo é criar uma equação que explique como a variável dependente se comporta em função das variáveis independentes.

Por exemplo, podemos criar uma equação que explique como as vendas de um produto variam em função dos gastos com propaganda. A equação resultante será uma reta, que pode ser usada para fazer previsões futuras.

3. Função PREVISÃO: Regressão Linear Simples

A função PREVISÃO é usada para realizar regressão linear simples, ou seja, quando temos apenas uma variável independente. Ela calcula um valor futuro (Y) com base em valores históricos de X e Y.

Exemplo Prático: Previsão de Vendas com Base nos Gastos com Propaganda

Vamos supor que temos os seguintes dados históricos de gastos com propaganda (X) e vendas (Y) de uma empresa:

Gastos Propaganda (R$)	Vendas (R$)
1.000	10.000
1.500	12.000
2.000	14.000
2.500	16.000
3.000	18.000

Queremos prever as vendas para um gasto com propaganda de R$ 3.500. Para isso, usamos a função PREVISÃO no Excel:

=PREVISÃO(3500; B2:B6; A2:A6)

Onde:

3500 é o valor de X (gasto com propaganda) que queremos prever.
B2:B6 são os valores conhecidos de Y (vendas).
A2:A6 são os valores conhecidos de X (gastos com propaganda).

O resultado será uma previsão das vendas para R$ 3.500 em gastos com propaganda.

Interpretando o Resultado

A equação da reta de regressão linear é dada por:

Y = a.X + b

Onde:

a é o coeficiente angular (inclinação da reta).
b é o coeficiente linear (intercepto da reta).

No nosso exemplo, suponha que a equação seja:

Y = 2,5.X + 7.500

Isso significa que, para cada R$ 1.000 gastos em propaganda, as vendas aumentam em R$ 2.500.

4. Função PROJ.LIN: Regressão Linear Múltipla

A função PROJ.LIN é usada para realizar regressão linear múltipla, ou seja, quando temos mais de uma variável independente. Ela permite analisar a relação entre várias variáveis e uma variável dependente.

Exemplo Prático: Previsão de Vendas com Base em Gastos com Propaganda e Comissão de Vendedores

Vamos supor que temos os seguintes dados históricos:

Comissão (%)	Gastos com Propaganda (R$)	Vendas (R$)
5,0%	1.000	10.000
6,0%	1.500	12.000
7,0%	2.000	14.000
5,5%	1.750	11.500
4,5%	1.250	9.500

Queremos prever as vendas com base na comissão dos vendedores e nos gastos com propaganda. Para isso, usamos a função PROJ.LIN:

=PROJ.LIN(C2:C6; A2:B6; 1; 1)

Onde:

C2:C6 são os valores conhecidos de Y (vendas).
A2:B6 são os valores conhecidos de X (comissão e gastos com propaganda).
O primeiro 1 indica que queremos calcular a constante b.
O segundo 1 indica que queremos calcular estatísticas adicionais.

Interpretando o Resultado

A função PROJ.LIN retorna uma matriz com os coeficientes da equação de regressão. Suponha que o resultado seja:

Y = 3,0.X1 + 2,0.X2 + 5.000

Onde:

X1 é a comissão dos vendedores.
X2 são os gastos com propaganda.

Isso significa que:

Para cada 1% de aumento na comissão, as vendas aumentam em R$ 3.000.
Para cada R$ 1.000 gastos em propaganda, as vendas aumentam em R$ 2.000.

5. Avaliando a Qualidade da Regressão

Para avaliar a qualidade da regressão, usamos o coeficiente de determinação (R²). Ele indica o quanto a variável dependente (Y) é explicada pelas variáveis independentes (X). Quanto mais próximo de 1, melhor.

No exemplo da função PREVISÃO, suponha que o R² seja 0,85. Isso significa que 85% da variação nas vendas é explicada pelos gastos com propaganda.

No exemplo da função PROJ.LIN, suponha que o R² seja 0,90. Isso significa que 90% da variação nas vendas é explicada pela comissão e pelos gastos com propaganda.

6. Conclusão

A regressão linear é uma ferramenta poderosa para análise de dados e previsões. Com as funções PREVISÃO e PROJ.LIN do Excel, podemos criar equações que explicam o comportamento de variáveis e tomar decisões mais embasadas.

No caso da regressão simples, usamos a função PREVISÃO para relacionar uma variável independente (como gastos com propaganda) com uma variável dependente (como vendas). Já na regressão múltipla, usamos a função PROJ.LIN para incluir mais variáveis (como comissão de vendedores) na análise.

Lembre-se de sempre avaliar a qualidade da regressão com o coeficiente R² e ajustar o modelo conforme necessário.

Exemplo Prático: Previsão do PIB com Base no Investimento em Infraestrutura e Taxa de Desemprego

Dados Fictícios do Brasil (2023)

Vamos supor que temos os seguintes dados trimestrais para o ano de 2023:

Trimestre	Investimento em Infraestrutura (R$ bilhões)	Taxa de Desemprego (%)	PIB (R$ bilhões)
1º	50	10,5	1.200
2º	55	10,0	1.250
3º	60	9,8	1.300
4º	65	9,5	1.350

Objetivo

Queremos prever o PIB do próximo trimestre (1º trimestre de 2024) com base em um investimento em infraestrutura de R$ 70 bilhões e uma taxa de desemprego de 9,2%.

Passo a Passo no Excel

Organize os dados na planilha:

Coloque os dados na planilha do Excel, como na tabela acima.

Use a função PROJ.LIN:

Selecione uma célula onde você quer que os resultados da regressão apareçam (por exemplo, célula A6).
Insira a fórmula:

=PROJ.LIN(D2:D5; B2:C5; 1; 1)

D2:D5: Valores conhecidos de Y (PIB).
B2:C5: Valores conhecidos de X (Investimento em Infraestrutura e Taxa de Desemprego).
O primeiro 1: Indica que queremos calcular a constante b.
O segundo 1: Indica que queremos calcular estatísticas adicionais.

Pressione CTRL + SHIFT + ENTER:
Como a função PROJ.LIN é matricial, você precisa pressionar CTRL + SHIFT + ENTER para que o Excel calcule a matriz de resultados.

Resultados da Regressão:
O Excel retornará uma matriz com os coeficientes da equação de regressão e estatísticas adicionais. Suponha que o resultado seja:

Coeficiente	Valor
a1 (Investimento)	5,0
a2 (Desemprego)	-20,0
b (Constante)	1.000

A equação de regressão será:

PIB = 5,0 * Investimento + (-20,0) * Desemprego + 1.000

Interpretação dos Coeficientes:

Investimento em Infraestrutura (a1 = 5,0): Para cada R$ 1 bilhão investido em infraestrutura, o PIB aumenta em R$ 5 bilhões.
Taxa de Desemprego (a2 = -20,0): Para cada 1% de aumento na taxa de desemprego, o PIB diminui em R$ 20 bilhões.
Constante (b = 1.000): Mesmo sem investimento e com desemprego zero, o PIB seria de R$ 1.000 bilhões.

Previsão do PIB para o 1º Trimestre de 2024:
Vamos prever o PIB para um investimento de R$ 70 bilhões e uma taxa de desemprego de 9,2%.
Substitua os valores na equação:

PIB = 5,0 * 70 + (-20,0) * 9,2 + 1.000

PIB = 350 – 184 + 1.000

PIB = 1.166 bilhões

Portanto, o PIB previsto para o 1º trimestre de 2024 é de R$ 1.166 bilhões.

Avaliando a Qualidade da Regressão

Coeficiente de Determinação (R²):

Suponha que o R² calculado seja 0,95. Isso significa que 95% da variação no PIB é explicada pelo investimento em infraestrutura e pela taxa de desemprego. É um valor muito bom, indicando uma forte correlação.

Teste F:
O valor de F calculado pode ser comparado com o valor crítico da tabela F. Se o valor calculado for maior que o crítico, a regressão é estatisticamente significativa.

Teste t:
Verifique a relevância de cada variável. Se o valor de t for maior que o valor crítico, a variável é significativa para o modelo.

Conclusão

Neste exemplo, usamos dados fictícios do Brasil em 2023 para prever o PIB com base no investimento em infraestrutura e na taxa de desemprego. A função PROJ.LIN do Excel nos permitiu criar uma equação de regressão linear múltipla e fazer uma previsão para o próximo trimestre.

Essa abordagem pode ser aplicada a diversos cenários econômicos, como previsão de inflação, consumo, ou até mesmo vendas de empresas. O importante é sempre avaliar a qualidade da regressão e ajustar o modelo conforme necessário.