PLANILHA PARA PROJETAR VENDAS USANDO REGRESSÃO LINEAR

PLANILHA PARA PROJETAR VENDAS USANDO REGRESSÃO LINEAR

(Esta planilha pronta está disponível para download através do e-mail albertolima3@gmail.com)

Introdução

Muitas vezes buscamos uma forma mais técnica para projetar a receita de vendas através de modelos estatísticos mais adequados.

Se a empresa entende que exista uma alta correlação entre o aumento de receita das vendas com os gastos com propaganda e também o incentivo através de comissão de vendas, estaremos então diante de uma Regressão Linear Múltipla, pois a Receita de Vendas é influenciada por mais de uma variável "controlada" .

Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quanto já tenho certeza do auto grau de correlação entre as variáveis).

Vejamos então como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado de receita de vendas, que, através da estimativas são determinadas pelosgastos com propaganda e também pela comissão de vendas.

Projeção Utilizando Funções Estatísticas(Projeção com 2 Variáveis)

Utilizando as Funções PROJ.LIN e TENDENCIA 

Informações

A tabela acima demonstra a evolução das receitas de uma empresa ao longo dos últimos 20 meses;

A evolução anual das receitas apresenta uma relação razoável com a evolução dos Gastos com Propaganda e Comissão de Vendas simultaneamente;

Isso pode caracterizar uma alta relação entre as variáveis dependentes (Vendas (Y)) e as variáveis independentes (Gastos com Propaganda (M1) e Comissão de Vendas (M2)) que devemos confirmar através da correlação do r2, que pode ser calculado através da expansão da função matricial do PROJ.LIN e também da função TENDÊNCIA do Excel para determinar esse grau de correlação.

Nesse caso temos um problema que envolve a regressão linear múltipla, pois vendas estão relacionadas com duas variáveis, que nesse caso são Gastos com Propaganda e Comissão de Vendas. Se fosse relacionada a mais apenas uma variável, essa seria considerada como regressão linear simples.

Não aconselhamos que a variável dependente (X1) seja incluída na tabela como percentual, pois nesse caso o valor da fórmula retornará como incorreta. Aconselhamos que transforme em número absoluto, conforme demonstrado na coluna D da tabela.

Nesse caso é possível utilizar tanto a função PROJ.LIN (que pode dar informações estatísticas mais detalhadas) ou mesmo a função TENDÊNCIA (que é utilizada quando já tenho certeza do auto grau de correlação). Importante: Quanto maior for a série histórica, melhor será a qualidade da reta de projetada.

O que temos que fazer? 

Se a empresa precisa projetar a receita de vendas de uma forma estatística mais técnica;

Se a empresa entende que exista uma alta correlação entre o aumento das vendas, os gastos com propaganda e o incentivo através de comissão de vendas;

Qual o objetivo?                         

• Como projetar a receita de vendas para esse mês, tendo como correlação duas variáveis que, a princípio, influenciam no resultado do aumento de receita de vendas, que, segundo estimativas, deveremos determinar os gastos com propaganda nem $95 mil e  comissão de vendas em 2,5%?

ORIENTAÇÕES PARA A MONTAGEM E UTILIZAÇÃO DA PLANILHA

1. Nessa planilha especial você pode calcular o valor de projeção de receita de vendas de duas formas:
1. Utilizando a equação da reta (com o uso da função PROJ.LIN); ou
2. Utilizando a função TENDÊNCIA.
2. Calcule o r² (Grau de Confiança da Reta), M₁, M₂ e a constante B, através da expansão matricial da função PROJ.LIN do Excel, utilizando os seguintes passos:
1. Determine o coeficiente angular (M) através da função PROJ.LIN. O resultado da fórmula será, nesse caso, M2.
2. Mantenha o cursor posicionado no resultado do PROJ.LIN (M₂).
3. Expanda o cursor de uma para três colunas e de uma para quatro linhas, utilizando o seu próprio mouse ou segurando a tecla Shift do teclado e expandido o cursor com as teclas de setas do seu teclado;
4. Solte tudo simultaneamente;
5. Aperte e em seguida solte a tecla F2 no canto superior esquerdo do seu teclado para editar a função PROJ.LIN;
6. Aperte simultaneamente as teclas na seguinte seqüência: Crtl, Shift e Enter;
7. Para finalizar, solte tudo simultaneamente;
8. Você terá como resultado uma pequena tabela como apresentado acima na célula J10.
3. Com a expansão da função PROJ.LIN de forma matricial é possível obter e analisar diversos dados, principalmente para estruturar a sua equação da reta para projeção linear múltipla;
4. Verifique o posicionamento das informações mais relevantes para a estruturação da sua fórmula para calcular a projeção de receita de vendas com o uso do PROJ.LIN;
5. Você deve ter atenção dobrada para estruturar a sua nova equação da reta, onde serão necessárias as seguintes informações:
1. Y = (M1.X1)+(M2.X2)+B
2. Importante: Verifique corretamente o posicionamento de cada variável para estruturar a sua fórmula!
6. Com a estruturação da fórmula e referência as respectivas células você terá como resultado o valor projetado de venda com o uso da função PROJ.LIN e um grau de confiança de correlação dos dados históricos de 62,35%.
7. Agora, para efeito de comparação, calcule o valor de projeção de vendas utilizando a função TENDÊNCIA.
8. Conclusão: considerando a correlação dos dados históricos de Gastos com Propaganda, % de Comissão de Vendas e a Receita de Vendas, para um Gasto com Propaganda de $95 mil reais e a determinação do novo % de comissão de vendas estipulado em 2,5%, teremos um valor de receia de venda de R$1.000,44 mil, com uma probabilidade de acerto de 62,35%, conforme determinado pelo r².

Boa Sorte