Excel aplicado a Engenharia de Avaliações

UFMS - Universidade Federal de Mato Grosso do Sul

CCET - Centro de Ciências Exatas e Tecnologia

DEC - Departamento de Estruturas e Construção Civil

Disciplina: Avaliações e Perícias na Construção Civil

Professor Wagner Augusto Andreasi, M. Engº

Acadêmico do Curso de Engª. Cvil: Ademir Aparecido Peixoto de Azevedo

Atualizada pelo Acadêmico do Curso de Engª. Cvil Thiago Winter Macinelli em 2009

FFUUFFMMSS -- CCCCEETT ––DDEECC -- LLAADDEE

AAvvaalliiaaççõõeess ee PPeerríícciiaass nnaa CCoonnssttrruuççããoo CCiivviill

IInnttrroodduuççããoo ààss FFeerrrraammeennttaass ddee AAnnáálliissee EEssttaattííssttiiccaa ddoo EExxcceell 22000077 aapplliiccaaddaass aa EEnnggªª ddee aavvaalliiaaççõõeess

PPrrooff.. WWaaggnneerr AAuugguussttoo AAnnddrreeaassii,, MM.. EEnnggªª..

AAccaadd.. AAddeemmiirr AA.. PPeeiixxoottoo ddee AAzzeevveeddoo -- vv..22000022 ee AAccaadd.. TThhiiaaggoo WWiinntteerr MMaacciinneellllii –– vv..22000099

2

SUMÁRIO

1. Introdução......................................................................................................................3

2. Regressão Linear simples..............................................................................................3

2.1. Calculo das estatísticas de regressão e ANOVA..........................................3

2.2. Determinação do intervalo de confiança......................................................9

2.3. Teste de hipóteses........................................................................................11

2.4. Verificação das hipóteses básicas...............................................................13

3. Regressão Linear Múltipla..........................................................................................16

3.1. Considerações preliminares........................................................................16

3.2. Verificação da colinearidade entre variáveis independentes.....................18

3.3. Calculo das Estatísticas de Regressão e Anova..........................................21

3.4. Determinação do Intervalo de Confiança...................................................25

3.5. Teste de hipóteses........................................................................................27

3.6. Verificação das hipóteses básicas...............................................................28

4. Exemplos Resolvidos....................................................................................................28

5. Referências Bibliográficas...........................................................................................53

6. Anexo 1( Tabela de Durbin-Watson)..........................................................................54






3

1. Introdução

Esta apostila tem o objetivo de apresentar ao leitor as ferramentas de Análise Estatística

disponíveis no Microsoft Excel 2007 aplicadas a Engenharia de Avaliações, utilizando-as em

exemplos de resolução de problemas de regressão linear simples e regressão linear múltipla.

Pressupõe-se nesta apostila que o leitor já tenha conhecimentos teóricos básicos de

estatística, tais como distribuições bilaterais e unilaterais, colinearidade, análise de resíduos,

testes de significância, análise de variância e regressão linear. O objetivo maior desta apostila é

diminuir o esforço e o tempo necessário para realizar uma avaliação utilizando o Microsoft Excel

para realizar os cálculos, que são muito complexos e longos para serem feitos “a mão”.

2. Regressão Linear Simples

2.1. Calculo das Estatísticas de Regressão e Anova

Consideremos o exemplo onde estamos interessados em avaliar um lote de 300 m2 de

área, situado a uma distância de cerca de 2.100 m de um ponto valorizante. Os atributos de

diferenciação levantados compreendem a localização do lote, através da distância do mesmo em

metros ao referido ponto e a área do lote. As características do imóvel avaliando bem como a dos

imóveis da amostra estão apresentados na tabela abaixo:

REGISTRO

N.º

VARIÁVEL

DEPENDENTE

PREÇO UNITÁRIO

(Y)

VARIÁVEIS INDEPENDENTES

OU EXPLICATIVAS

DIST. (X1) ÁREA (X2)

1 100,00 2.200,00 300,00

2 110,00 2.000,00 340,00

3 120,00 1.800,00 270,00

4 140,00 1.500,00 360,00

5 85,00 2.300,00 400,00

6 105,00 1.900,00 500,00

7 120,00 1.300,00 600,00

8 95,00 2.200,00 300,00

9 150,00 900,00 360,00

10 100,00 1.700,00 600,00

Tabela 1.1

Inicialmente poderíamos supor que o atributo distância ao ponto valorizante não seja

influenciante no valor do lote e que a influência da área seja diretamente proporcional a esse

valor, o que possibilitaria a resolução do problema por estatística descritiva.

Entretanto, procuraremos levar em consideração o atributo distância ao ponto valorizante na

formação dos preços, obtendo uma equação de regressão linear simples, relacionando o preço

unitário (PU) com a distância (DIST) do tipo: Ŷi = B0 + B1X1, que terá o seguinte aspecto:






4

PÛ= B0+ B1 * DIST

Para tanto, utilizaremos o Excel 2007 e o suplemento PHStat2 para calcular as Estatísticas de

Regressão, Anova e Correlação. Ative o Excel e insira os dados em sua área de trabalho

(Tabela 1.1), como mostrado na Figura 1.1.

Figura 1.1

Antes de iniciar a Análise de Dados é necessário ativar os suplementos do Excel 2007 para que as

ferramentas de análise estejam disponíveis, para ativá-las, clique com o botão direito sobre a

barra de ferramentas, em seguida clique em “Personalizar barra de tarefas de acesso rápido” ,

clique em “Suplementos”, na opção Gerenciar, selecione a opção “Suplementos do Excel” e

em seguida clique em “IR”, no menu suplementos ative os itens “Ferramentas de Análise” e

“Ferramentas de Análise – VBA” , como mostrado na Figura 1.2, em seguida clique em

“OK”.






5

Figura 1.2

No menu Dados clique em “Análise de Dados”.

Na caixa de diálogo Análise de Dados dê um duplo clique em “Regressão”. (Figura 1.3)

Figura 1.3






6

No campo Intervalo Y de Entrada deve ser fornecido o intervalo que contém as variáveis

dependentes ou explicadas, que em nosso caso é PU. Selecione a célula B1 e arraste até a célula

B11. Clique no campo Intervalo X de Entrada.

Da mesma forma, para este campo, deve ser fornecido o intervalo que contém as variáveis

independentes ou explicativas, que em nosso caso é a distância. Portanto, selecione a célula C1 e

arraste até a célula C11 para selecionar o intervalo. Marque a opção Rótulos, pois em nossos

intervalos incluímos junto aos dados seus respectivos rótulos.

Marque a opção Nível de Confiança e substitua o valor padrão de 95% por 80% que é o valor

recomendado pela NBR-14653-2:2003.

Marque as opções “Resíduos”, “Resíduos Padronizados” e “Plotar Resíduos”.

Figura 1.4

Clique no botão Intervalo de saída e, posteriormente, no campo que está a sua direita. Insira a

referência para a célula onde o Microsoft Excel exibirá o canto superior esquerdo da tabela de

regressão e Anova.

Para tanto, após selecionar o referido campo clique sobre a célula A19 ou referencie a célula A19

digitando “A19” diretamente no campo.

A aparência da caixa de diálogo Regressão ficará semelhante com a da Figura 1.4 mostrada

acima. Clique em OK.






7

O Excel fornecerá as Estatísticas de Regressão, Anova e Tabela de Resíduos.

Figura 1.5

Registro nº Variável Dependente (PU) DIST. (X1) ÁREA (X2)

1 100,00 2.200,00 300

2 110,00 2.000,00 340

3 120,00 1.800,00 270

4 140,00 1.500,00 360

5 85,00 2.300,00 400

6 105,00 1.900,00 500

7 120,00 1.300,00 600

8 95,00 2.200,00 300

9 150,00 900,00 360

10 100,00 1.700,00 600

Tabela 1.2

Antes de fazer a inferência estatística é necessário conhecer o significado dos parâmetros

fornecido pelo Excel:






8

RESUMO DOS RESULTADOS

Estatística de regressão

R múltiplo 0,88054

R-Quadrado 0,77536 R-quadrado ajustado 0,74728

Erro padrão 10,21017

Observações 10

Tabela 1.3

R-múltiplo: Correlação entre as variáveis independentes e a variável dependente.

R-quadrado: Poder de explicação do modelo de regressão, no exemplo 77,5% da variabilidade

dos preços é explicado pelo modelo adotado

R-quadrado ajustado: Idem ao R-quadrado, porém ajustado levanto em conta o numero de

variáveis independentes

Erro padrão: É o desvio padrão do modelo, dado pela raiz quadrada da variância.

ANOVA

gl SQ MQ F F de

significação Regressão 1 2878,51914 2878,51914 27,61233 0,00077 Resíduo 8 833,98085 104,24760

Total 9 3712,5

Coeficientes Erro padrão Stat t valor-P 95% inferiores 95%

superiores

Interseção 184,16104 14,01440 13,14084 1,07E-06 151,84380 216,47830

DIST. (X1) -0,040259 0,00766 -5,25474 0,00077 -0,05793 -0,02259

Inferior 80,0%

Superior 80,0%

164,58550 203,73660

-0,05096 -0,02956

Tabela 1.4






9

RESULTADOS DE RESÍDUOS

Observação Previsto(a) PREÇO UNITÁRIO

(Y) Resíduos Resíduos padrão

1 95,59121 4,40878 0,45799

2 103,64301 6,35698 0,66038

3 111,69481 8,30518 0,86276

4 123,77252 16,22747 1,68575

5 91,56531 -6,56531 -0,68202

6 107,66891 -2,66891 -0,27725

7 131,82432 -11,82432 -1,22834

8 95,59121 -0,59121 -0,06141

9 147,92792 2,07207 0,21525

10 115,72072 -15,72072 -1,63311

Tabela 1.5

Agora de posse dos coeficientes da equação de regressão linear simples, podemos concluir que a

equação PÛ = B0 + B1 * DIST, fazendo as devidas substituições com os coeficientes, é:

PÛ = 184,161 – 0,04026*DIST,

Que para DIST = 2.100 m resulta:

PÛ = R$ 99,62 / m2

2.2. Determinação do Intervalo de Confiança:

Para o cálculo do Intevalo de confiança utilizaremos o “PHStat2”. O software será

disponibilizado pelo professor no inicio do curso, ou pode-se fazer o download a partir do site

http://www.dec.ufms.br/lade. ALTERAR

Após instalado o “PHStat” só estará disponível no Excel quando ele for aberto, portanto, toda

vez que forem utilizar o “PHStat” deve-se abrir o “Excel” e o “PHStat”. O leitor saberá se o

software esta disponivel verficando se esta aparecendo o menu “Suplementos” na barra de

ferramentas. No caso de o menu “Suplementos” não estar aparecendo, a instalação pode ter

sido feita de forma incorreta, ou o usuário não abriu o “PHStat”

Na barra de ferramentas abra o menu “Suplementos”, clique em “PHStat”, clique em

“Regression” e clique em “Simple Linear Regression”

Configure o PHStat conforme mostrado na figura 1.6, onde X é o valor da variável independente

para o terreno que esta sendo avaliado, no caso X é a distancia 2100 metros. Clique em OK, o

PHStat criará um nova planilha com nome “Estimated” com os intervalos de confiança para o

preço previsto e para uma nova observação.

http://www.dec.ufms.br/lade






10

Fig 1.6

Tabela 1.6

Intervalo de confiança para o valor previsto:

R$ 93,954 < PÛ < R$ 105,279

A NBR 14653-2:2003 sugere a classificação da avaliação quanto ao Grau de Precisão (item

9.2.2). O nível de precisão é encontrado através do valor em porcentagem da amplitude do

For Average Predicted Y (YHat)

Interval Half Width 5,662778

Confidence Interval Lower Limit 93,95434

Confidence Interval Upper Limit 105,2799

For Individual Response Y


Prediction Interval Lower Limit 84,27229

Prediction Interval Upper Limit 114,9619






11

intervalo de confiança de 80% sobre o valor pontual da avaliação. Como fornecemos para o

campo “confidence levels for interval estimates” o valor de 80%, os valor superior e inferior

fornecidos pelo PHStat referem-se a um intervalo de confiança de 80% sobre o valor pontual da

avaliação, com estes dados pode-se calcular a amplitude que é dada por:

%4,11114,062,99

954,93279,105infsup

PÛ

PÛPÛAMPLITUDE

Descrição Grau

III II I

Amplitude do intervalo de confiança de 80%

em torno do valor central da estimativa ≤ 30% 30% - 50% > 50%

NOTA Observar subseção 9.1.

Tabela 1.7

Através da Tabela 1.7, que foi transcrita da NBR 14653-2, pode-se classificar a avaliação quanto

ao grau de precisão, como a amplitude do intervalo de confiança é menor do que 30% , a

avaliação apresenta Grau de Precisão 3

O intervalo para uma resposta individual de Y, é o intervalo de confiança para uma nova

observação que possua as mesmas caracteristicas quanto as variáveis da observação

utilizada para o cálculo, não é de interesse para a avaliação pois o que nos interessa é o

intervalo de confiança para a observação que foi avaliada.

2.3. Teste de hipóteses

Testada a hipótese nula da regressão (ß1 = 0) pelo teste F, a mesma foi rejeitada ao nível de

1%, uma vez que:

Fcalculado = 27,61 > F (λ)

(k), (n-k-1) = F (1%)

(1), (8) = 11,30.

(Fcalculado está localizado na célula E30 da tabela do Excel).

Nota: Para obter F (1%)

(1), (8) pode-se consultar uma tabela F ou através da função “INVF” do

Excel, para a obtenção através da função “INVF” deve-se proceder da seguinte forma:

Escolha uma célula vazia do Excel e digite a expressão a seguir:

=INVF(“prob”; “k”; “n-k-1”) , onde:

-“prob”: é o nível de significância desejado, em nosso caso 1%

-“k”: é o numero de variáveis do modelo, em nosso caso é 1

-“n” é o numero de amostras do modelo, em nosso caso é 10






12

Substituindo os dados, a fórmula ficará como a mostrada a seguir:

=INVF(0,01;1;8)

Pressione ENTER, e o Excel fornecerá o valor de F (1%)

(1), (8)

Na célula E31 o Excel fornece o F de significação, que é o nível de significância com o qual se

aceita a hipótese nula da regressão, no modelo como F de significação < 0,01, rejeita-se a

hipótese nula da regressão ao nível de 1%.

Testada a hipótese nula do regressor (ß1 =0), a mesma foi rejeitada ao nível de 5%, uma vez

que:

Nota: Para obter %5),8(t pode-se consultar uma tabela t ou através da função “INVT” do Excel,

para a obtenção através da função “INVT” deve-se proceder da seguinte forma:

Escolha uma célula vazia do Excel e digite a expressão a seguir:

=INVT(“prob”; “n-k-1”) , onde:

-“prob”: é o nível de significância desejado, em nosso caso 5%

-“k”: é o numero de variáveis do modelo, em nosso caso é 1

-“n” é o numero de amostras do modelo, em nosso caso é 10

Substituindo os dados, a fórmula ficará como a mostrada a seguir:

=INVT(0,05;8)

Pressione ENTER, e o Excel fornecerá o valor de %5),8(t

Outra forma de fazer o teste é através do valor P (Célula E36) que representa o nível de

significância com a qual se aceita a hipótese nula do regressor, no modelo, como valor P < 0,05,

rejeita-se a hipótese nula do regressor ao nível de 5%.

Obs.: Os níveis de significância adotados para o teste de hipóteses devem ser escolhidos

através da NBR 14653, item 9.2.1. Os níveis de significância dependem do Grau de

Fundamentação escolhido pelo avaliador.

2.4. Verificação das hipóteses básicas

31,225,5007661,0

04026,01%5),8(),2(

1

1

ttS

Bt n

B






13

Primeira hipótese: A variável independente corresponde a números reais que não contenham

nenhuma perturbação aleatória.

De fato, no caso de dados imobiliários, as variáveis independentes estão relacionados com as

características fixas de cada elemento tomado com referência, estando a hipótese atendida.

Segunda hipótese: O número de observações, n, deve ser superior ao número de parâmetros

estimados pelo modelo.

Para evitar o problema de micro numerosidade, a NBR 14653-2 no item 3 da Tabela 1 determina

o número mínimo dados de mercado utilizados na modelagem, a princípio:

)1(3 kn , onde k é o numero de variáveis independentes.

Terceira hipótese: Os erros são variáveis aleatórias com valor esperado nulo e variância

constante.

Um gráfico de resíduos versus valores previsto, apresentando pontos distribuídos aleatoriamente

em torno de uma reta que passa na origem sem nenhum padrão definido, é um indicador

favorável a verificação da hipótese.

Construa um gráfico com os “valores previstos” (Células B43:B52) da tabela de resíduos no

eixo das abscissas e com os “resíduos” (Células C43:C52) no eixo das ordenadas

O gráfico deverá ficar igual ao apresentado abaixo:

Gráfico 1.1






14

Como os pontos estão distribuídos de forma aleatória em torno da reta horizontal que passa pela

origem e não apresentam nenhum padrão definido, aceita-se a hipótese como atendida, classifica-

se também o modelo como homocedástico1.

Quarta hipótese: Os erros são variáveis aleatórias com distribuição normal.

Um gráfico de resíduos padronizados versus preço previsto, apresentando 95% dos pontos no

intervalo [-1,96;+1,96] é um indicador favorável a verificação da hipótese.

Construa um gráfico com os “valores previstos” (Células B43:B52) da tabela de resíduos no

eixo das abscissas e “resíduos padronizados” (Células D43:D52) no eixo das ordenadas.

O gráfico deverá ficar igual ao apresentado abaixo:

Tabela 1.2

Como 100% dos pontos estão distribuídos no intervalo [-1,96;+1,96] se aceita a quarta hipótese.

Quinta hipótese: Os erros são “não correlacionados”, isto é, são independentes sob a condição

de normalidade.

A verificação é feita com o auxilio da razão de Von Neumann, que foi tabelada por Durbin

Watson para os níveis de significância de 5%, 2,5% e 1%, considerando modelos com 15 a 100

observações com até seis variáveis (Ver Anexo 1).

A estatística de Von Neumann pode ser calculada através do PHStat.. Na barra de ferramentas

abra o menu “Suplementos”, clique em “PHStat”, clique em “Regression” e clique em

“Simple Linear Regression”. Informe o intervalo de células das variáveis dependentes e

independentes, e selecione a opção “Durbin-Watson Statistic”, em seguida clique em “OK”, o

1 Homocedástico: Quando os pontos apresentam dispersão totalmente aleatória em torno de uma reta horizontal que

passa pela origem e sem nenhum padrão definido.






15

menu deverá ficar como o apresentado na Figura 1.7. O PHStat criará uma nova planilha com o

valor da Razão de Von Neumann (Durbin-Watson Statistic).

Figura 1.7

Durbin-Watson Calculations

Sum of Squared Difference of

Residuals 1138,727801

Sum of Squared Residuals 833,9808559

Durbin-Watson Statistic 1,365412399

Tabela 1.8

Para que a quinta hipótese seja satisfeita: du < Durbin-Watson Statistic < (4 - du), onde du é o

valor tabelado por Durbin-Watson (ver Anexo 1) . Escolhe-se o valor de du para 15 observações

(du não foi tabelado para 10 observações) e 1 variável independente ao nível de significância de

1%.

07,1tabdu






16

Como:

1,07 < Durbin-Watson Statistic < (4 -1,07)

1,07 < 1,37 < 2,93

Aceita-se a quinta hipótese.

Desta forma podemos concluir que a equação encontrada para a regressão

PÛ = 184,161 – 0,04026*DIST, é válida para inferência do valor do preço unitário, quando a

distância do terreno ao ponto valorizante for igual a 2100 m.

Obs. Neste exemplo assim como no seguinte, estamos supondo a amostra já saneada, isto é,

isenta de dados supostamente discrepantes. Mais tarde será ilustrado como proceder para sanear

uma amostra utilizando o critério de Chauvenet.

3. Regressão Linear Múltipla

3.1. Considerações preliminares

É recomendado ao leitor que só inicie a leitura deste capitulo após ter lido o capitulo que trata de

regressão linear simples, pois é necessário o total conhecimento de regressão linear simples para

proceder a analise de regressão múltipla.

Para aplicação da metodologia, será apresentada a avaliação do valor de aluguel de um

apartamento, no exemplo foram escolhidas varias variáveis independentes e o objetivo é

selecionar quais variáveis explicam melhor a variação do valor de mercado do aluguel do imóvel.

Os atributos de diferenciação levantados compreendem:

-Variáveis quantitativas

- Número de quartos

- Número de banheiros

- Número de vagas de garagem.

Variáveis dicotômicas:

-Área de lazer (Recebe valor 1 se o apartamento possuir, e 0 em caso contrário)

Variáveis qualitativas:

-Estado de conservação (Recebe valor 1 para bom, 2 para regular e 3 para ruim), é

considerada como qualitativa, mas na verdade é uma variável tricotômica.

É de grande importância o avaliador verificar após a regressão se os coeficientes

encontrados para cada variável condiz com a realidade conforme o exemplo:

Ex: O coeficiente de uma variável com valor da área não pode ser negativo, pois sabe-se que

quanto maior a área do imóvel maior seu valor.

O imóvel avaliando possui as seguintes características:

- Numero de quartos: 3






17

-Numero de banheiros: 2

- Numero de vagas de garagem: 1

-Área de lazer: Não possui (Recebe valor 0)

-Estado de conservação: Regular (Recebe valor 2)

Desta forma, tentar-se-á obter uma equação de regressão múltipla relacionando o preço unitário

(PU) com as variáveis descritas anteriormente. A equação será do tipo:

PUi = Bo + B1X1 + B2X2 + ....+ BnXn

Os dados coletados do mercado para a avaliação são os descritos na tabela abaixo:

N. de

registro Preço

Numero de

quartos

(X1)

Número de

banheiros

(X2)

Vagas de

garagem

(X3)

Estado de

conservação

(X4)

Área de

lazer (X5)

1 450 3 2 1 2 0

2 400 3 1 1 3 1

3 500 3 2 1 2 1

4 500 3 2 2 3 0

5 350 2 1 1 2 0

6 600 3 2 2 1 0

7 350 2 1 1 2 0

8 300 2 1 1 2 0

9 450 2 1 1 1 0

10 350 2 1 1 2 0

11 500 3 2 1 2 0

12 500 3 2 1 2 0

13 500 3 2 2 2 0

14 350 2 1 1 2 0

15 400 2 1 1 2 0

16 300 2 1 1 2 0

17 500 3 2 1 2 1

18 450 2 1 1 1 0

19 400 2 1 1 2 0

20 480 3 2 1 3 1

21 420 3 2 1 3 0

Tabela 2.1

Na regressão múltipla deve-se atentar a correlação entre variáveis independentes, que se for alta

pode prejudicar a estimativa dos parâmetros do modelo, gerando um modelo tendencioso que não

explica de fato o comportamento dos valores de mercado.






18

3.2. Verificação da colinearidade entre variáveis independentes

Inicialmente será verificado a correlação entre as variáveis utilizando a tabela de correlação

do Excel e o fator inflacionário de variância (FIV) obtido através do PHStat. A tabela de

correlação fornece a correlação das variáveis uma a uma, enquanto o FIV fornece a correlação de

uma variável com todas as outras variáveis do modelo.

O FIV é dado por:

21

1

jrFIV

, onde:

jr - Correlação entre uma variável independente com as variáveis independentes restantes.

Portanto quando jr =0,9, o FIV é aproximadamente 5. Alguns autores consideram como valor

critico para o FIV o valor 5, outros consideram crítico o valor 10, nesta apostila consideraremos

como valor crítico o FIV igual a 5 (LEVINE et al, 2005).

No menu Dados clique em Análise de Dados.

Na caixa de diálogo Análise de Dados dê um duplo clique em Regressão.

Com o Excel ativo, e com os dados já dispostos em sua área de trabalho (Tabela 2.1), no menu

Dados clique em “Análise de Dados”.

Figura 2.1

Na caixa de diálogo Análise de Dados escolha “Correlação” e clique em “OK”.






19

No campo Intervalo de Entrada deve ser fornecido o intervalo que contêm todas as variáveis

envolvidas na regressão, que neste exemplo são seis: PU, X1, X2, X3, X4 e X5. Portanto,

selecione B1 e arraste até G22.

Marque a opção Rótulos na primeira linha, pois em nossa seleção incluímos com os dados seus

respectivos rótulos.

Marque em Opções de saída o botão Intervalo de saída. Para inserir a referência para a célula

superior esquerda da tabela de saída, clique no campo que está imediatamente à direita do botão

intervalo de saída e insira a referência de célula “A24”. Clique em OK.

O resultado será uma tabela de Correlação como a mostrada na Figura 2.2:

Figura 2.2

De posse das Correlações entre os dados, deve-se verificar se há alguma correlação superior a

0,70 entre variáveis independentes. Segundo Dantas (2005) correlações acima de 0,7 podem ser

prejudiciais ao modelo. No exemplo, as variáveis “número de banheiros” e “numero de quartos”

apresentam correlação igual a 0,90, o que é prejudicial ao modelo, uma das duas variáveis deve

ser excluída do modelo. Para determinar qual variável a ser excluída será calculado o FIV através

do PHStat.

Para o cálculo do FIV utilizaremos o PHStat2. Na barra de ferramentas abra o menu

“Suplementos”, clique em “PHStat”, clique em “Regression” e clique em “Multiple

Regression”

Configure o PHStat conforme mostrado na Figura 2.3, selecione a opção “Variance

inflationary factor (VIF)” e clique em “OK”






20

O PHStat criará varias planihas contendo o FIV de cada variável, deve-se verificar se existe

alguma variavel com FIV maior que 5, caso exista, deve-se excluir a variavel. Caso exista 2

variáveis com FIV maior que 5 deve-se excluir a variavel com maior FIV e após proceder uma

nova verificação do FIV até que não haja nenhuma variável com FIV maior que 5.

Figura 2.3

Os FIVs calculados são:

-Numero de quartos: FIV = 10,119

-Numero de banheiros: FIV = 7,448

-Vagas de garagem: FIV = 1,532

-Estado de conservação: FIV = 1,385

-Área de lazer: FIV = 1,893

As variaveis “número de quartos” e “numero de banheiros” possuem FIV maior que 5, porém o

FIV da variavel numero de quartos é maior, deve-se excluir a variavel „numero de quartos” e

fazer nova verficação do FIV.

Após a exclusão da variável numero de quartos a planilha deve ficar como a Tabela 2.2:






21

N. de

registro Preço

Número de

banheiros (X1)

Vagas de

garagem

(X2)

Estado de

conservação

(X3)

Área de

lazer (X4)

1 450 2 1 2 0

2 400 1 1 3 1

3 500 2 1 2 1

4 500 2 2 3 0

5 350 1 1 2 0

6 600 2 2 1 0

7 350 1 1 2 0

8 300 1 1 2 0

9 450 1 1 1 0

10 350 1 1 2 0

11 500 2 1 2 0

12 500 2 1 2 0

13 500 2 2 2 0

14 350 1 1 2 0

15 400 1 1 2 0

16 300 1 1 2 0

17 500 2 1 2 1

18 450 1 1 1 0

19 400 1 1 2 0

20 480 2 1 3 1

21 420 2 1 3 0

Tabela 2.2

Após refazer os cálculos do FIV, devem-se obter os seguintes resultados:

-Numero de banheiros: FIV = 1,495

-Vagas de garagem: FIV = 1,407

-Estado de conservação: FIV = 1,207

-Área de lazer: FIV = 1,358

Como todos os FIVs são menores que 5 não há problema de colinearidade no modelo com 4

variáveis independentes.

3.3. Calculo das Estatísticas de Regressão e Anova

No menu “Dados” clique em “Análise de Dados”. Na caixa de diálogo Análise de Dados

escolha “Regressão” e clique em “OK”.

O Excel exibirá a caixa de diálogo Regressão. No campo Intervalo Y de entrada, vamos inserir

as referências de células que contém os dados da variável dependente, que neste exemplo é

Preço. Clique em B1 e arraste o mouse até B22.






22

No campo Intervalo X de entrada, vamos inserir as referências de células que contém os dados

das variáveis dependentes, que neste exemplo são X1, X2, X3 e X4. Clique no campo Intervalo

X de entrada. Selecione a célula C1 e arraste o mouse até a célula F22, a referência de célula

aparecerá automaticamente no referido campo.

Como na referência dos dados dependentes e independentes, nos referimos aos dados com seus

respectivos rótulos, marque a opção Rótulos.

Dado que estamos trabalhando com um intervalo de confiança de 80% marque o botão Intervalo

de confiança e substitua o valor padrão de 95% por 80%. Em Opções de saída marque o botão

Intervalo de saída para introduzir uma referência de célula.

Selecione as opções: Resíduos, Resíduos Padronizados e Plotar Resíduos

Clique no campo à direita do botão. Selecione a célula A31. A configuração da caixa de diálogo

Regressão se parecerá como a mostrada na Figura 2.4. Clique em OK.

Figura 2.4

O resultado será a tabela de regressão como mostrada na Figura 2.5:






23

Figura 2.5

As tabelas de Regressão, Anova, Resíduos e Correlação gerada pelo Microsoft Excel estão

apresentadas abaixo:



R múltiplo 0,91573

R-Quadrado 0,83856

R-quadrado ajustado 0,79821


Observações 21

ANOVA

gl SQ MQ F F de

significação

Regressão 4 104134,06220 26033,51554 20,77810 3,55556E-06

Resíduo 16 20046,89023 1252,93064 Total 20 124180,95240






24

Coeficientes Erro padrão Stat t valor-P

95% inferiores

95% superiores

Interseção 302,88334 39,27305 7,71224 8,89007E-07 219,62820 386,13849 Número de banheiros (X1) 108,66723 18,91222 5,74587 3,00723E-05 68,57510 148,75937 Vagas de garagem (X2) 61,80280 26,18965 2,35982 0,031321044 6,28322 117,32238 Estado de conservação (X3) -55,24505 14,75194 -3,74493 0,001766555 -86,51778 -23,97232

Área de lazer (X4) 53,25881 22,92891 2,32278 0,033699697 4,65167 101,86595

Inferior 80,0%

Superior 80,0%

250,38482 355,38187

83,38618 133,94829

26,79360 96,81201

-74,96482 -35,52528

22,60841 83,90921


Observação Previsto(a) Preço Resíduos Resíduos padrão

1 471,53 -21,53052 -0,68005 2 360,87 39,12295 1,23572 3 524,78 -24,78933 -0,78299 4 478,08 21,91172 0,69209 5 362,86 -12,86328 -0,40629 6 588,57 11,42161 0,36076 7 362,86 -12,86328 -0,40629 8 362,86 -62,86328 -1,98558 9 418,11 31,89165 1,00732

10 362,86 -12,86328 -0,40629 11 471,53 28,46947 0,89923 12 471,53 28,46947 0,89923 13 533,33 -33,33333 -1,05285 14 362,86 -12,86328 -0,40629 15 362,86 37,13671 1,17299 16 362,86 -62,86328 -1,98558 17 524,78 -24,78933 -0,78299 18 418,10 31,89165 1,00732 19 362,86 37,13671 1,17299 20 469,54 10,45571 0,33025 21 416,29 3,71453 0,11732






25

Através das estatísticas de regressão e da tabela Anova, obtivemos a seguinte equação de

regressão:

426,53325,55280,61166,10888,302 XXXXPÛ

Substituindo os valores das variáveis independentes do imóvel avaliando:

-Numero de banheiros (X1): 2

- Numero de vagas de garagem (X2): 1

-Estado de conservação (X3): 2

-Área de lazer (X4): 0

Temos que o valor previsto para o aluguel do imóvel é:

PÛ = R$ 471,53

3.4. Determinação do Intervalo de Confiança

Para o cálculo do Intevalo de confiança utilizaremos o PHStat2. Na barra de ferramentas abra o

menu “Suplementos”, clique em “PHStat”, clique em “Regression” e clique em “Multiple

Regression”

Configure o PHStat conforme mostrado na figura 2.6. Clique em OK, o PHStat criará um nova

planilha com nome “Intervals” com os intervalos de confiança para o preço previsto e para uma

nova observação.

Nas células B6:B9 deve-se informar os valores das variáveis independentes do imóvel avaliando.

Para o imóvel avaliando desta apostila devem-se preencher os campos como segue descrito na

tabela 2.3:

Data

Confidence Level 80%

1

Número de banheiros (X1) given value 2

Vagas de garagem (X2) given value 1

Estado de conservação (X3) given value 2

Área de lazer (X4) given value 0

Tabela 2.3






26

Figura 2.6

Nas células A29:B39, o PHStat fornece o valor previsto para o imóvel e os intervalos de

confiança para o valor previsto e para uma resposta individual, conforme abaixo.

Predicted Y (YHat) 471,53

For Average Predicted Y (Yhat)




For Individual Response Y


Prediction Interval Lower Limit 419,39

Prediction Interval Upper Limit 523,67

Tabela 2.4


R$ 449,64 < PÛ < R$ 493,43






27



intervalo de confiança de 80% sobre o valor pontual da avaliação.

%3,9093,053,471

64,44943,493infsup

PÛ

PÛPÛAMPLITUDE

Como a amplitude é menor do que 30% ,através da Tabela 1.7 podemos classificar a avaliação

com Grau de Precisão 3

O intervalo para uma resposta individual de Y não é de interesse para a avaliação (Ver item

2.2 desta apostila).

3.5. Teste de hipóteses

Testada a hipótese nula da regressão (ß1 = 0) pelo teste F, a mesma foi rejeitada ao nível de

1%, uma vez que:

01,01055,3 6 ãosifnificaçdeF

( ãosignificaçdeF está localizado na célula F42 da tabela do Excel)

OBS: F de significação é o nível de significância em que se aceita a hipótese nula da regressão.

Caso fosse aceita a hipótese nula da regressão a equação obtida não poderia ser utilizada para

explicar o comportamento dos valores de mercado dos imóveis.

Testada a hipótese nula dos regressores (ß1 =0), a mesma foi rejeitada ao nível de 5%, uma vez

que:

(Valor P das variáveis está localizado nas células E47:E51)

OBS: Valor P é o nível de significância em que se aceita a hipótese nula dos regressores.

Caso alguma variável independente tivesse a hipótese nula aceita, ela poderia ser excluída do

modelo, e dever-se-ia recalcular as estatísticas de regressão, tabela Anova, e tabela de resíduos.

05,01001,3)1( 5 XPValor

05,0031,0)2( XPValor

05,0001,0)3( XPValor

05,0034,0)4( XPValor






28

3.6. Verificação das hipóteses básicas

Deve-se proceder a verificação das 5 hipóteses básicas descritas na Regressão Linear Simples e

verificar uma nova hipótese que só se aplica a Regressão Múltipla.

Sexta hipótese: Não deve existir nenhuma relação exata entre quaisquer variáveis

independentes.

Para proceder esta verificação deve-se consultar a tabela de correlações e assegurar que não

exista nenhuma correlação igual a 1 entre duas variáveis independentes.

TABELA DE CORRELAÇÃO

Preço Número de banheiros

Vagas de garagem

Estado de conservação

Área de lazer

Preço 1 Número de banheiros 0,794124877 1

Vagas de garagem 0,543532721 0,428174419 1 Estado de conservação -0,14093509 0,252509201 -0,03378687 1

Área de lazer 0,246310115 0,265935942 -0,19802951 0,381375432 1

No modelo não existe nenhum correlação igual a 1 entre duas variáveis independentes, então, se

aceita a sexta hipótese.

Obs. Neste exemplo, estamos supondo a amostra já saneada, isto é, isenta de dados supostamente

discrepantes. Mais tarde será ilustrado como proceder para sanear uma amostra utilizando o

critério de Chauvenet.

4. Exemplos Resolvidos

4.1. Um corretor de imóveis quer saber se em determinado bairro da cidade

(representado pela amostra abaixo - tab. 3.1) se a dimensão da frente de um terreno ou sua

testada, influi ou não na formação de preços unitários (R$/m2). Esse é um problema típico que

pela aplicação de inferência estatística, podemos afirmar se a dimensão da frente dos terrenos

influi ou não na formação de seus preços unitários.

Amostra Frente (m) Profund. (m) R$/m2

1 15,00 31,50 31,75

2 15,00 30,00 44,44

3 15,00 37,65 31,87

4 12,00 30,00 38,85

5 15,00 30,00 51,11

6 12,00 30,00 36,11

Planilha 4.1.1






29

Resolução:

1º) Saneamento da amostra (Critério de Chauvenet)

Consiste em se eliminar os registros da amostra, cujos preços (Pori) distem mais do que um certo

limite (dlim) da média de preços da amostra, ou seja, elimina-se todos os registros cujos preços

(Pori) estiverem fora do intervalo:

(Pori - dlim) ≤ Pori (i) ≤ (Pori + dlim)

Com dlim = Sp x (d/Sp)crítico, onde:

Sp é o desvio padrão dos preços da amostra, dado por:

e (d/Sp)crítico uma função da quantidade de registros da amostra, conforme a seguir:

n = 5 6 7 8 9 10

(d/Sp)crítico 1,65 1,73 1,80 1,86 1,92 1,96

n = 11 12 13 14 15 16

(d/Sp)crítico 1,98 2,03 2,05 2,10 2,12 2,16

n = 17 18 19 20 21 22

(d/Sp)crítico 2,18 2,20 2,23 2,24 2,26 2,28

n = 24 26 30 40 50

(d/Sp)crítico 2,31 2,35 2,39 2,50 2,58

Tabela 4.1.2

Com a tabela 3.1 inserida no Microsoft Excel, utilize as funções MÉDIA e DESVPAD para

calcular Pori que é a média aritmética dos preços unitários (R$/m2) e Sp que é o desvio padrão dos

preços da amostra. Daí, vem que:

Sp = 7,59 e Pori = 39,02, n = 6 e dlim = 7,59*1,73 = 13,13, de modo que o intervalo:

(Pori - dlim) ≤ Pori (i) ≤ (Pori + dlim) é igual a:

(39,02 – 13,13) ≤ Pori (i) ≤ (39,02 + 13,13) = 25,89 ≤ Pori (i) ≤ 52,15

1

)(1

2

n

PoriPori(i)

Sp

n

i






30

Com isto, concluímos que a amostra não tem nenhum dado supostamente discrepante. Isto é, a

amostra já está saneada.

2º) Cálculo das Correlações, Regressores e Anova.

Correlações:

Frente (m) Profund. (m) R$/m2

Frente (m) 1

Profund. (m) 0,38602956 1

R$/m2 0,15734108 -0,56507026 1


R múltiplo 0,69640049

R-Quadrado 0,48497365

R-quadrado ajustado

0,14162275


Observações 6

ANOVA

gl SQ MQ F F de significação

Regressão 2 139,680598 69,8402992 1,4124723 0,369610395

Resíduo 3 148,336285 49,4454283

Total 5 288,016883

Coeficientes Erro padrão Stat t Valor-P 95% inferiores

Interseção 66,2600311 36,8151914 1,79980135 0,1697137 -50,90244869

Frente (m) 2,16161984 2,2004549 0,98235135 0,3983645 -4,84121629

Profund. (m) -1,8239823 1,11402051 -1,63729688 0,2000917 -5,369296092

95% superiores

Inferior 80,0% Superior 80,0%

183,4225108 5,9661244 126,5539377

9,164455972 -1,44216483 5,765404507

1,721331482 -3,64846416 0,000499551

Tabela 4.1.3

3º) Teste de hipótese nula da regressão (ß1 = 0 e ß2 = 0)

Testada a hipótese nula da regressão (ß1 = 0 e ß2 = 0), ambas foram aceitas, uma vez que:

Fcalculado= 1,4124723 < F (λ)

(k), (n-k-1) = F (1%)

(2), (3) = 30,81 (tabelado)

Isto significa que ß1 e ß2 não são variáveis influenciantes ao nível de significância de 1% ou sua

influência é muito reduzida na formação do preço unitário dos terrenos. Como a hipótese nula do

teste foi aceita, poderíamos concluir nossa análise nesta etapa. Mas para ilustrar o roteiro

normalmente adotado quando a hipótese nula é rejeitada, vamos continuar os testes.






31

4º) Teste de hipótese nula dos regressores (ß1 = 0 ou ß2 = 0)

Testada a hipótese nula dos regressores (ß1 = 0 ou ß2 = 0), a primeira foi aceita, uma vez :

|t1| = 0,982 < t (n-k-1), = t (3),5% = 2,3534

Isto significa que ß1 não influi decisivamente e suficientemente na formação de preços para ser

incluído na equação de regressão. Isto é, a equação que melhor representa a formação de preços

dos terrenos desta amostra, é uma equação que não envolve a variável frente do terreno.

|t2| = 1,636 < t (n-k-1), = t (3),5% = 2,3534

Assim temos também que ß2 não influi decisivamente na formação de preços, com um nível de

confiança de 90%, para fazer parte da equação que procura descrever a variação de preços no

mercado imobiliário da região de coleta das amostras. Deste modo, não podemos obter, a partir

dos dados da tab. 3.1, uma equação que represente o preço unitário dos terrenos de forma

eficiente.

Como esperávamos, este teste apenas confirmou o que já havíamos concluído no 4.º passo:

Entretanto, como a amostra é muito pequena, pode acontecer desta não ser representativa, isto é,

corre-se o risco de ser tendenciosa. Daí deveríamos suspeitar que a amostra não é de fato

representativa, tendo em vista que a bibliografia técnica afirma que a frente ou testada do terreno

influi decisivamente na formação de seu preço unitário.

4.2. A tabela a seguir se refere a uma pesquisa de terrenos urbanos em uma dada área

central de uma cidade X. A pesquisa concentrou-se ao longo das Avenidas AP e PF, duas vias de

comércio intenso e nas ruas transversais até uma distância máxima de 270m. Registrou-se os

preços dos terrenos em oferta, suas áreas e distância às referidas avenidas. Verifique se pode ou

não obter uma equação de regressão a partir da amostra abaixo.

982,0200,2

161,2

82,46

)15,9(82,46*12

44,49

16161984,2

)(.2

2

2

2121

11

x

xxxx

SQ

SSQSQ

QMR

bt

636,1114,1

823,1

00,12

)15,9(82,46*12

44,49

823,1

)(.2

1

2

2121

22

x

xxxx

SQ

SSQSQ

QMR

bt






32

Registro n.º VT ÁREA DIST.

1 170000 422 0

2 100000 360 0

3 150000 2055 110 (esquina)

4 180000 901 40 (esquina)

5 1080000 7200 70 (esquina)

6 700000 8057 270 (esquina)

7 50000 430 40

8 20000 280 40

9 55000 360 40

10 18000 200 40

Planilha 4.2.1

Resolução:

1º) Saneamento da amostra (Critério de Chauvenet)

(Pori – dlim) ≤ Pori (i) ≤ (Pori + dlim)

Como: dlim = Sp x (d/Sp)crítico

Sp = 352750,5, Pori = 252300, n =10 e (d/Sp)crítico = 1,96

dlim = 352750,5 * 1,96 = 691390,94


252300 – 691390,94 ≤ Pori (i) ≤ 252300 + 691390,94

-439090,94 ≤ Pori (i) ≤ 943690,94

Como o registro n.º 5 está fora do intervalo acima, concluímos que se trata de um dado espúrio,

portanto, será eliminado e não considerado para fins de inferência estatística.

Vamos recalcular novamente o intervalo para o critério de Chauvenet para verificar se existe

mais algum dado discrepante.

Sp = 211745,13, Pori = 160333,33, n = 9 e (d/Sp)crítico = 1,92

dlim = 211745,13*1.92 = 406550,65


160333,33 – 406550,65 ≤ Pori (i) ≤ 160333,33 + 406550.65

-246217 ≤ Pori (i) 566883,98






33

Desta forma, o registro nº 6, também constitui um dado espúrio, de modo que, deveremos

recalcular um novo intervalo:

Sp = 66593,52, Pori = 92875, n = 8 e (d/Sp)crítico = 1,86

dlim = 66593,52*1.86 = 123863,94


-30988,94 ≤ Pori (i) ≤ 216738,94

Com isto, a amostra já está saneada.

2º) Cálculo das Correlações, Regressores e Anova.

Registro

n.º

VT Área DIST.

1 170000 422 0

2 100000 360 0

3 150000 205 110 (esquina)

4 180000 901 40 (esquina)

5 50000 430 40

6 20000 280 40

7 55000 360 40

8 18000 200 40

CORRELAÇÕES

VT Área DIST.

VT 1

Área 0,571241 1

DIST. 0,0396682 0,813187 1



R múltiplo 0,926914

R-Quadrado 0,859169

R-quadrado ajustado

0,802837


Observações 8

ANOVA

gl SQ MQ F F de signific.

Regressão 2 2,67E+10 1,33E+10 15,25179 0,007443

Resíduo 5 4,37E+09 8,74E+08






34

Total 7 3,1E+10

Coeficientes Erro padrão

Stat t valor-P 95% inferiores 95% superiores

Interseção 80100,28 16575,96 4,832317 0,004747 37490,5 122710,1

Área 172,5055 31,26266 5,517941 0,002677 92,14243 252,8686

DIST. -2457,27 564,9533 -4,34951 0,007363 -3909,52 -1005,01

Inferior 80,0%

Superior 80,0%

55636,08 104564,5

126,3654 218,6456

-3291,07 -1623,46

Tabela 4.2.1

3º) Teste de hipótese nula da regressão (ß1 = 0 e ß2 = 0)

Testada a hipótese nula da regressão (ß1 = 0 e ß2 = 0), ambas foram aceitas , uma vez que:

Fcalculado= 15,25179 < F (λ)

(k), (n-k-1) = F (1%)

(2), (5) = 13,27 (tabelado)

Como rejeitamos a hipótese de nulidade de ß1 e ß2 , então, a equação de regressão obtida pela

modelagem é significante ao nível de 1% para explicar o comportamento de mercado.

4.3. A tabela a seguir se refere a uma pesquisa de apartamentos na cidade de

Campo Grande - MS. Registrou-se os preços dos apartamentos em oferta, suas áreas, valores de

condomínio, número de vagas de garagem, se posui portaria, quadra, piscina e Sacada. Pretende-

se encontrar o valor de mercado de um imóvel com as seguintes características:

Área = 115m²

Condomínio = R$ 150,00

Garagem = 1 vaga

Portaria = Possui (Recebe valor 1 por se tratar de uma variável dicotômica)

Armário = Não Possui (Recebe valor 0 por se tratar de uma variável dicotômica)

Quadra = Possui (Recebe valor 1 por se tratar de uma variável dicotômica)

Piscina = Não Possui (Recebe valor 0 por se tratar de uma variável dicotômica)

Sacada = Possui (Recebe valor 1 por se tratar de uma variável dicotômica)

Nº registro

Residencial Valor Área Cond. Garagem Portaria Armário Quadra Piscina Sacada

1 Cachoeirinha

II R$ 140.000 118 R$ 255 2 1 1 1 0 0

2 Itacolomi R$ 145.000 98 R$ 350 1 1 1 1 1 1

3 Via Park R$ 185.000 103 R$ 0 2 0 1 0 0 0

4 - R$ 145.000 107 R$ 220 2 1 0 0 1 1

5 Dominica R$ 95.000 73 R$ 180 1 1 0 0 1 1






35

6 Tacuma R$ 195.000 154 R$ 0 2 0 1 0 0 1

7 Cachoeirinha

II R$ 130.000 102 R$ 255 1 1 1 1 0 0

8 Vitoria R$ 140.000 70 R$ 0 1 0 1 0 0 0

9 - R$ 210.000 122 R$ 240 1 1 0 1 1 1

10 Monte Castelo

R$ 185.000 116 R$ 260 2 1 0 1 0 1

11 Sevilha R$ 90.000 80 R$ 220 1 1 1 1 0 1

12 Parque dos coqueiros

R$ 75.000 72 R$ 150 1 0 1 0 0 1

13 Cedro R$ 90.000 78 R$ 215 1 1 1 1 1 1

14 Marques de

Labradil R$ 110.000 90 R$ 110 1 1 0 0 0 1

15 Las Palmas R$ 95.000 93 R$ 130 1 1 1 0 0 0

16 - R$ 85.000 87 R$ 180 1 1 0 0 0 0

17 Sam Lorenço R$ 125.000 76 R$ 275 2 1 0 1 1 1

18 Mangaratiba R$ 85.000 87 R$ 190 1 1 0 0 0 0

19 Nova Suécia R$ 105.000 110 R$ 200 2 1 1 0 0 0

20 Nova

Portugal R$ 93.000 110 R$ 215 2 1 1 0 0 0

21 Nova

Inglaterra R$ 100.000 110 R$ 200 2 1 1 0 0 0

Planilha 4.3.1

1º) Verificação da colinearidade.

Supondo a amostra já saneada pelo critério de Chauvenet, calcula-se a tabela de

correlações obtendo o seguinte resultado:

Valor Área Cond. Garagem Portaria Armário Quadra Piscina Sacada

Valor 1,00 Área 0,66 1,00

Cond. -0,15 -0,05 1,00 Garagem 0,36 0,58 -0,02 1,00

Portaria -0,29 -0,04 0,78 -0,07 1,00 Armário -0,10 0,12 -0,21 0,08 -0,38 1,00

Quadra 0,29 0,03 0,66 -0,08 0,38 0,01 1,00 Piscina 0,16 -0,17 0,45 -0,12 0,31 -0,37 0,37 1,00

Sacada 0,22 -0,05 0,22 -0,14 0,02 -0,36 0,36 0,60 1,00

Tabela 4.3.1






36

Verifica-se que a variável “condomínio” possui uma correlação alta com a variável

“portaria”, faremos o cálculo dos FIVs para verificar se alguma variável deve ser excluída do

modelo.

Os FIVs calculados são:

- Área: FIV = 1,66

- Condomínio: FIV = 4,78

- Garagem: FIV = 1,64

- Portaria: FIV = 3,72

- Armário: FIV = 1,66

- Quadra: FIV = 2,24

- Piscina: FIV = 1,99

- Sacada: FIV = 2,09

Como todos os FIVs são menores que 5 não há problema de colinearidade no modelo.

2º) Calculo da estatística de regressão e anova.

RESUMO DOS RESULTADOS Estatística de regressão

R múltiplo 0,935055217 R-Quadrado 0,874328258 R-quadrado

ajustado 0,790547097 Erro padrão 18540,55309 Observações 21

ANOVA

gl SQ MQ F F de

significação Regressão 8 2,87E+10 3,59E+09 10,44 0,0002353 Resíduo 12 4,125E+09 3,44E+08

Total 20 3,282E+10

Coeficientes Erro

padrão Stat t valor-P 95% inferiores 95%

superiores

Interseção 61518,859 25665,059 2,397 0,034 5599,498 117438,219

Área 1328,125 256,993 5,168 0,000 768,186 1888,064

Cond. -141,130 98,052 -1,439 0,176 -354,768 72,508

Garagem 1530,987 10479,344 0,146 0,886 -21301,543 24363,516

Portaria -50393,393 19875,963 -2,535 0,026 -93699,397 -7087,390

Armário -33156,881 10723,162 -3,092 0,009 -56520,644 -9793,117

Quadra 51370,673 12464,005 4,122 0,001 24213,938 78527,407

Piscina 29013,363 12644,275 2,295 0,041 1463,853 56562,872






37

Sacada -18193,101 11705,927 -1,554 0,146 -43698,125 7311,923

Tabela 4.3.2

3º) Teste de hipótese nula da regressão

Pelo teste F, a mesma foi rejeitada ao nível de 1%, uma vez que:

01,00002353,0 ãosifnificaçdeF (O modelo é significativo)

4º) Teste de hipótese nula dos regressores

Testando as variáveis ao nível de 20%, tem-se:

Área - 20,000,0 PValor (Significativa para o modelo)

Condominio - 20,018,0 PValor (Significativa para o modelo)

Garagem - 20,088,0 PValor (Não é significativa para o modelo)

Portaria - 20,003,0 PValor (Significativa para o modelo)

Armário - 20,001,0 PValor (Significativa para o modelo)

Quadra - 20,000,0 PValor (Significativa para o modelo)

Piscina - 20,004,0 PValor (Significativa para o modelo)

Sacada - 20,015,0 PValor (Significativa para o modelo)

5º) Análise crítica dos valores obtidos na regressão

Ao término do calculo das estatísticas de regressão e dos testes de hipóteses devemos verificar se

o modelo realmente serve para explicar o comportamento real do mercado imobiliário. A priori

esta tudo correto em nosso modelo, mas vamos nos atentar aos coeficientes obtidos para as

variáveis: Portaria, Armário e Sacada.

Coeficientes:

-Portaria = R$ -50393,39

-Armário = R$ -33156,88

-Sacada = R$ -18193,10

Os coeficientes negativos para estas variáveis não condizem com a realidade. Podemos ver

através do exemplo:

Um apartamento que não possua Armário Embutido tem um valor de mercado, se o seu

proprietário resolver investir na instalação de Armário Embutido no apartamento seu

apartamento, este investimento ira agregar valor ao imóvel, ao contrário do que diz o modelo,

cujo diz que se o proprietário investir na instalação de armário embutido o valor do imóvel irá

diminuir em R$ 33156,88.

Para tentar descobrir o que esta afetando o modelo de regressão plota-se gráficos das variáveis

explicativas versus variável explicada, conforme demonstrado a seguir:






38

Gráfico 4.3.1

Pontos indicados pelas setas correspondem as amostras: 3, 6, 8, 9 e 10

Gráfico 4.3.2

Pontos indicados pelas setas correspondem as amostras: 3, 6, 9 e 10






39

Gráfico 4.3.3


Gráfico 4.3.4







40

Gráfico 4.3.5


Gráfico 4.3.6







41

Gráfico 4.3.7


Gráfico 4.3.8


A plotagem desses gráficos é feita para verificar a existência de pontos influenciantes. “Entende-

se por pontos influenciantes aqueles com pequenos resíduos, em algumas vez até nulos, mas que

se distanciam da massa de dados, podendo alterar completamente as tendências naturais indicadas

pelo mercado (DANTAS, 2005, p. 113).”






42

Pode-se concluir então que as amostras 3, 6, 9, 10 são pontos influenciantes no modelo, a solução

é excluir esses dados de nossa amostra e proceder a analise de regressão novamente.

Após a exclusão a planilha deve ficar como a planilha 4.3.2:

Nº registro


1 Cachoeirinha

II R$ 140.000 118 R$ 255 2 1 1 1 0 0

2 Itacolomi R$ 145.000 98 R$ 350 1 1 1 1 1 1

3 - R$ 145.000 107 R$ 220 2 1 0 0 1 1

4 Dominica R$ 95.000 73 R$ 180 1 1 0 0 1 1

5 Cachoeirinha

II R$ 130.000 102 R$ 255 1 1 1 1 0 0

6 Vitoria R$ 140.000 70 R$ 0 1 0 1 0 0 0

7 Sevilha R$ 90.000 80 R$ 220 1 1 1 1 0 1


R$ 75.000 72 R$ 150 1 0 1 0 0 1

9 Cedro R$ 90.000 78 R$ 215 1 1 1 1 1 1

10 Marques de

Labradil R$ 110.000 90 R$ 110 1 1 0 0 0 1

11 Las Palmas R$ 95.000 93 R$ 130 1 1 1 0 0 0

12 - R$ 85.000 87 R$ 180 1 1 0 0 0 0

13 Sam Lorenço R$ 125.000 76 R$ 275 2 1 0 1 1 1

14 Mangaratiba R$ 85.000 87 R$ 190 1 1 0 0 0 0

15 Nova Suécia R$ 105.000 110 R$ 200 2 1 1 0 0 0

16 Nova

Portugal R$ 93.000 110 R$ 215 2 1 1 0 0 0

17 Nova

Inglaterra R$ 100.000 110 R$ 200 2 1 1 0 0 0

Planilha 4.3.2

*Observe que o número de registro das amostras foram alterados para que não haja confusão

quanto ao número real de amostras do modelo.

Verificando se ainda existem pontos influenciantes no modelo, plota-se o gráfico do Valor x Área

(Gráfico 4.3.9), que supostamente é a variável que mais influencia no modelo.

Como demonstrado no gráfico 4.3.9, ainda existem pontos influenciantes no modelo.






43

Gráfico 4.3.9


Procedendo novamente a exclusão das amostras que foram consideradas como pontos

influenciantes, temos uma nova planilha:

Nº registro


1 Cachoeirinha

II R$ 140.000 118 R$ 255 2 1 1 1 0 0

2 - R$ 145.000 107 R$ 220 2 1 0 0 1 1

3 Dominica R$ 95.000 73 R$ 180 1 1 0 0 1 1

4 Cachoeirinha

II R$ 130.000 102 R$ 255 1 1 1 1 0 0

5 Sevilha R$ 90.000 80 R$ 220 1 1 1 1 0 1


R$ 75.000 72 R$ 150 1 0 1 0 0 1

7 Cedro R$ 90.000 78 R$ 215 1 1 1 1 1 1

8 Marques de

Labradil R$ 110.000 90 R$ 110 1 1 0 0 0 1

9 Las Palmas R$ 95.000 93 R$ 130 1 1 1 0 0 0

10 - R$ 85.000 87 R$ 180 1 1 0 0 0 0

11 Mangaratiba R$ 85.000 87 R$ 190 1 1 0 0 0 0

12 Nova Suécia R$ 105.000 110 R$ 200 2 1 1 0 0 0

13 Nova

Portugal R$ 93.000 110 R$ 215 2 1 1 0 0 0

Planilha 4.3.3

6º) Calculo da estatística de regressão e anova para a nova planilha.






44


Estatística de regressão R múltiplo 0,97 R-Quadrado 0,94 R-quadrado

ajustado 0,83 Erro padrão 9070,31 Observações 13,00

ANOVA

gl SQ MQ F F de

significação Regressão 8 5,58E+09 6,98E+08 8,48396 0,02767 Resíduo 4 3,29E+08 8,23E+07

Total 12 5,91E+09

Coeficientes Erro padrão Stat t valor-P 95%

inferiores 95%

superiores

Interseção -100482,85 55292,778 -1,817 0,143 -

254000,212 53034,513

Área 2810,96 599,113 4,692 0,009 1147,557 4474,366

Cond. 30,86 162,813 0,190 0,859 -421,186 482,899

Garagem -38747,74 19758,679 -1,961 0,121 -93606,632 16111,143

Portaria -23408,23 14971,673 -1,564 0,193 -64976,258 18159,800

Armário -12745,33 9749,310 -1,307 0,261 -39813,757 14323,090

Quadra 10060,42 17584,117 0,572 0,598 -38760,919 58881,752

Piscina 18423,20 10288,165 1,791 0,148 -10141,328 46987,722

Sacada 19958,17 12599,302 1,584 0,188 -15023,101 54939,439

Tabela 4.3.3

Como possuímos apenas 13 amostras devemos escolher no máximo 3 variáveis para o

modelo, pois a norma exige que )1(3 kn , onde k é o numero de variáveis independentes.

Serão escolhidas as variáveis: área, piscina e sacada, pois apresentem coeficiente com sinal

coerente com a realidade de mercado e são significantes ao nível de 20%.

Temos então uma nova planilha contendo apenas as 3 variáveis escolhidas.






45

Nº registro

Residencial Valor Área Piscina Sacada

1 Cachoeirinha

II R$ 140.000 118 0 0

2 - R$ 145.000 107 1 1

3 Dominica R$ 95.000 73 1 1

4 Cachoeirinha

II R$ 130.000 102 0 0

5 Sevilha R$ 90.000 80 0 1


R$ 75.000 72 0 1

7 Cedro R$ 90.000 78 1 1

8 Marques de

Labradil R$ 110.000 90 0 1

9 Las Palmas R$ 95.000 93 0 0

10 - R$ 85.000 87 0 0

11 Mangaratiba R$ 85.000 87 0 0

12 Nova Suécia R$ 105.000 110 0 0

13 Nova

Portugal R$ 93.000 110 0 0

Planilha 4.3.4

7º) Calculo da estatística de regressão e anova para planilha com 3 variáveis.


Estatística de regressão R múltiplo 0,853295582 R-Quadrado 0,72811335 R-quadrado


ANOVA

gl SQ MQ F F de

significação Regressão 3 4,31E+09 1435092742 8,034 0,0065 Resíduo 9 1,61E+09 178627205,5

Total 12 5,91E+09






46

Coeficientes Erro padrão Stat t valor-P 95%

inferiores 95%

superiores

Interseção -43408,756 32710,009 -1,327 0,217 -

117403,937 30586,425

Área 1466,565 319,976 4,583 0,001 742,728 2190,401

Piscina 10511,655 11045,229 0,952 0,366 -14474,389 35497,698

Sacada 16772,531 11286,761 1,486 0,171 -8759,895 42304,958


Observação Previsto(a) Valor Resíduos Resíduos padrão

1 129645,89 10354,11 0,89

2 140797,86 4202,14 0,36

3 90934,66 4065,34 0,35

4 106180,85 23819,15 2,06

5 90688,96 -688,96 -0,06

6 78956,44 -3956,44 -0,34

7 98267,48 -8267,48 -0,71

8 105354,60 4645,40 0,40

9 92981,77 2018,23 0,17

10 84182,38 817,62 0,07

11 84182,38 817,62 0,07

12 117913,37 -12913,37 -1,12

13 117913,37 -24913,37 -2,15

Tabela 4.3.4

Observe que temos duas amostras com resíduos altos. Deve-se verificar a amostra quanto a

presença de outliers. “Entende-se por outlier um dado que contém grande resíduo em relação aos

demais que compõe a amostra. Estes pontos ponde ser detectados com facilidade através de uma

análise gráfica dos resíduos padronizados versus os valores previsto (DANTAS, 2005, p. 112).”

Plota-se o gráfico 4.3.10 Resíduos padrão x Previsto(a) Valor, através de uma analise visual é

possível identificar a presença de dois outliers indicados pelas setas correspondentes as amostras

4 e 13. Para prosseguir deve-se excluir as duas amostras do modelo, e conseqüente deve-se

excluir também uma variável, pois como teremos apenas 11amostras quando a norma exige no

mínimo 12 amostras para um modelo com 3 variáveis. Excluiremos a variável piscina, pois é a

única das 3 variáveis que não é significante para o modelo.






47

Gráfico 4.3.10

Após a exclusão das amostras 4 e 13 e da variável piscina, a planilha ficara como a planilha

4.3.5.

Nº registro

Residencial Valor Área Sacada

1 Cachoeirinha

II R$ 140.000 118 0

2 - R$ 145.000 107 1

3 Dominica R$ 95.000 73 1

4 Sevilha R$ 90.000 80 1


R$ 75.000 72 1

6 Cedro R$ 90.000 78 1

7 Marques de

Labradil R$ 110.000 90 1

8 Las Palmas R$ 95.000 93 0

9 - R$ 85.000 87 0

10 Mangaratiba R$ 85.000 87 0

11 Nova Suécia R$ 105.000 110 0

Planilha 4.3.5

Continuaremos verificando a presença de outliers, para isso calculamos novamente a tabela de

resíduos da planilha 4.3.5.







48

Observação Previsto(a)

Valor Resíduos Resíduos padrão

1 133096,72 6903,28 0,94

2 139567,84 5432,16 0,74

3 83921,08 11078,92 1,51

4 95377,77 -5377,77 -0,73

5 82284,41 -7284,41 -0,99

6 92104,43 -2104,43 -0,29

7 111744,46 -1744,46 -0,24

8 92179,98 2820,02 0,39

9 82359,97 2640,03 0,36

10 82359,97 2640,03 0,36

11 120003,36 -

15003,36 -2,05

Tabela 4.3.5

Plota-se novamente o gráfico Resíduos padrão x Previsto(a)

Gráfico 4.3.11

Através de uma analise visual é possível identificar a presença de dois outliers indicados pelas

setas, correspondentes as amostras 3 e 11. Para prosseguir deve-se excluir do modelo as amostras

3 e 11 e fazer nova verificação.

A nova planilha ficará como a planilha a seguir:






49

Nº registro


1 Cachoeirinha

II R$ 140.000 118 0

2 - R$ 145.000 107 1

3 Sevilha R$ 90.000 80 1


R$ 75.000 72 1

5 Cedro R$ 90.000 78 1

6 Marques de

Labradil R$ 110.000 90 1

7 Las Palmas R$ 95.000 93 0

8 - R$ 85.000 87 0


Planilha 4.3.6

Calculando novamente a tabela de resíduos:


Observação Previsto(a)

Valor Resíduos Resíduos padrão

1 142164,20 -2164,20 -1,26

2 142632,03 2367,97 1,38

3 91841,99 -1841,99 -1,07

4 76793,09 -1793,09 -1,04

5 88079,77 1920,23 1,12

6 110653,12 -653,12 -0,38

7 95136,38 -136,38 -0,08

8 83849,71 1150,29 0,67

9 83849,71 1150,29 0,67

Tabela 4.3.6






50

Plota-se novamente o gráfico Resíduos padrão x Previsto(a)

Gráfico 4.3.12

Através da analise visual, verifica-se que não há presença de outliers. Adota-se então este

conjunto de amostras para compor o modelo.

8º) Calculo da estatística de regressão e Anova para planilha com 2 variáveis e 9 amostras.

Nº registro


1 Cachoeirinha

II R$ 140.000 118 0

2 - R$ 145.000 107 1

3 Sevilha R$ 90.000 80 1


R$ 75.000 72 1

5 Cedro R$ 90.000 78 1

6 Marques de

Labradil R$ 110.000 90 1

7 Las Palmas R$ 95.000 93 0

8 - R$ 85.000 87 0


Planilha 4.3.7







51

R múltiplo 0,998 R-Quadrado 0,995 R-quadrado


ANOVA

gl SQ MQ F F de

significação

Regressão 2 4,98E+09 2,49E+09 630,50 1,06E-07 Resíduo 6 2,37E+07 3,95E+06

Total 8 5,00E+09

Coeficientes Erro

padrão Stat t valor-P 95%

inferiores 95%

superiores

Interseção -79807,07 5195,15 -15,36 4,81E-06 -92519,2 -67095,0

Área 1881,11 52,98 35,51 3,33E-08 1751,5 2010,7

Sacada 21160,07 1451,30 14,58 6,53E-06 17608,9 24711,3

Tabela 4.3.7

Pode-se observar que os coeficientes referentes as variáveis são positivos o que condizem

com a realidade de mercado. Percebe-se também que com a exclusão dos pontos

influenciantes e outliers do modelo, conseguiu-se uma melhora significativa no modelo de

regressão.

9º) Teste de hipótese nula da regressão

Pelo teste F, a mesma foi rejeitada ao nível de 1%, uma vez que:

01,007-1,06E ãosifnificaçdeF (O modelo é significativo)

10º) Teste de hipótese nula dos regressores

Testando as variáveis ao nível de 1%, tem-se:

Área - 01,008-3,33E PValor (Significativa para o modelo)

Sacada - 01,006-6,53E PValor (Significativa para o modelo)

11º) Determinação do valor de mercado do imóvel e intervalo de confiança






52

Como descrito no enunciado do exercício, o imóvel possui as seguintes características:

Área = 115m²

Sacada = Possui (Recebe valor 1 por se tratar de uma variável dicotômica)

Data

Confidence Level 80%

1

Área given value 115

Sacada given value 1

Predicted Y (YHat) 157680,9

For Average Predicted Y (Yhat)




07,7980707,2116011,1881 SacadaÁreaPÛ

Substituindo os valores das variáveis independentes do imóvel avaliando, temos que o valor

previsto para o imóvel é:

PÛ = R$ 157680,90


R$ 155086,00 < PÛ < R$ 160275,90



intervalo de confiança de 80% sobre o valor pontual da avaliação.

%3,3033,090,157680

00,15508690,160275infsup

PÛ

PÛPÛAMPLITUDE

Como a amplitude é menor do que 30% ,através da Tabela 1.7 podemos classificar a avaliação

com Grau de Precisão 3.

Obs: Fica a cargo do leitor realizar a verificação das hipóteses básicas do modelo.

5. Bibliografia

DANTAS, Rubens Alves (2005) Engenharia de avaliações: uma introdução a metodologia científica. 2.

ed. rev. de acordo com a NBR-14653-2:2004. São Paulo: PINI.






53

GUNST, Richard F., MASON, Robert L., Regression Analysis and its application: A data-Oriented

Approach. Nova York: MARCEL DEKKER, INC.

Levine, David M e outros (2005). Estatística – Teoria e aplicações usando o Microsoft Excel em

português. Trad. Eduardo Benedito Curtolo e Teresa Cristina Padilha de Souza. 3ª ed. Rio de Janeiro:

LTC.

NBR – 14653 – “Norma Brasileira para Avaliação de Bens”. ABNT.

Acedido em 18 de agosto de 2009, no Web site da: Universidade Federal de Santa Catarina:

http://www.inf.ufsc.br/~ogliari/arquivos/regressao_linear_multipla.ppt#33

Acedido em 21 de agosto de 2009, no Web site da: Universidade Federal do Pernambuco:

http://www.de.ufpe.br/~gjaa/Introreg.doc

Acedido em 27 de agosto de 2009, no Web site da: Universidade Federal do Pernambuco:

http://www.cin.ufpe.br/~rmcrs/ESAP/arquivos/RegressaoMultipla.pdf

6. Anexo 1

http://www.inf.ufsc.br/~ogliari/arquivos/regressao_linear_multipla.ppt#33

http://www.de.ufpe.br/~gjaa/Introreg.doc

http://www.cin.ufpe.br/~rmcrs/ESAP/arquivos/RegressaoMultipla.pdf






54

.

(DANTAS, 2005, p. 238)

Engineering

Excel aplicado a Engenharia de Avaliações