INFORMAÇÕES FINANCEIRAS: TREINAMENTO EXCEL AVANÇADO

O que é?

Após ajustar um modelo de regressão para um conjunto de dados, podemos prever o valor médio de uma variável dependente de outra. Isto pode ser feito através do uso da função TENDÊNCIA do Excel, que devolve valores em uma tendência linear a partir de um conjunto dado de pontos.

A regressão linear aproxima uma reta y(x) = μx + c a um conjunto de pontos dados onde há dependência de uma variável em relação a outra. A reta é calculada de forma a minimizar a distância entre seus pontos e os valores observados.

Por exemplo, sabendo-se que há uma relação entre as vendas de uma loja e sua quantidade de clientes em determinado dia, e que esta relação pode seguir um modelo linear, dados obtidos por regressão podem servir para a estimativa das vendas de acordo com a quantidade esperada de clientes em uma data.

A diferença entre as funções TENDÊNCIA e PREVISÃO é que TENDÊNCIA pode ser aplicada em forma matricial, de forma a calcular vários valores simultaneamente.

Sintaxe da função TENDÊNCIA

=TENDÊNCIA(val_conhecidos_y; val_conhecidos_x; novos_x; constante)

Val_conhecidos_y são valores de y que você já conhece na relação y(x) = μx + c.
Val_conhecidos_x são valores de x que você já conhece na relação y(x) = μx + c. Para cada valor de x há um valor de y correspondente de acordo com a relação acima.
Novos_x são os novos valores de x para os quais você deseja que a função TENDÊNCIA devolva valores y(x) correspondentes. Você pode trabalhar em forma matricial se quiser fornecer mais de uma célula a este argumento, ou utilizar apenas um valor para novos_x;
Constante é um valor lógico que força a constante c a se igualar a zero. Este valor é opcional e definido por padrão como verdadeiro. Caso o valor utilizado seja falso, a constante será zerada de forma a obtermos a relação y(x) = μx: utilize esta opção para obrigar a reta da regressão a passar pelo ponto (0; 0).

Suponha que o gerente de vendas de uma loja quer prever as vendas futuras com base na quantidade de clientes estimados para as próximas semanas. O departamento de vendas praparou uma tabela que relaciona o número de clientes com vendas totais efetuadas pela loja no período de 20 semanas.

Observe a planilha:

Neste caso, nossa variável independente (x) é o número de clientes; a variável dependente (y) é o volume de vendas. Note que a tabela do departamento não possui os dados para as semanas 21, 22 e 23. O objetivo do gerente é preparar uma função que estime a tendência das vendas para estas semanas.

Veja abaixo um gráfico dos dados disponíveis. Ele contém os pares (clientes; vendas) da listagem e a reta que melhor representa estes valores:

Linha de tendência dos valores de exemplo.

Ao usar a função TENDÊNCIA, o Excel devolverá o valor de y(x) para o x dado. Note que elaborar um gráfico não é um passo necessário para o uso desta função.
Calcularemos a tendência para a semana 21 na célula H24. Siga os passos abaixo:

Selecione a célula H24;
Abra o assistente de função;
Selecione a categoria estatística;

Exemplo

Selecione a função TENDÊNCIA;
Insira os argumentos da função. Os valores conhecidos de y são H4:H23; os valores associados de x são G4:G23 e o valor novo de x está em G24. Observe o preenchimento dos campos: