• Author / Uploaded
• pera

Citation preview

Using Excel For Principles of Econometrics, Fourth Edition

i

Using Excel For Principles of Econometrics, Fourth Edition

GENEVIEVE BRIAND Washington State University

R. CARTER HILL Louisiana State University

JOHN WILEY & SONS, INC New York / Chichester / Weinheim / Brisbane / Singapore / Toronto

ii

Genevieve Briand dedicates this work to Tom Trulove Carter Hill dedicates this work to Todd and Peter

To order books or for customer service call 1-800-CALL-WILEY (225-5945).

Copyright © 2011 John Wiley & Sons, Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc. 222 Rosewood Drive, Danvers, MA 01923, (978)750-8400, fax (978)646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, (201)748-6011, fax (201)748-6008. ISBN 978-0-470-58581-8

iii

Preface This book is a supplement to Principles of Econometrics, 4th Edition by R. Carter Hill, William E. Griffiths and Guay C. Lim (Wiley, 2011). This book is not a substitute for the textbook, nor is it a stand alone computer manual. It is a companion to the textbook, showing how to perform the examples in the textbook using Excel 2007. This book will be useful to students taking econometrics, as well as their instructors, and others who wish to use Excel for econometric analysis. In addition to this computer manual for Excel, there are similar manuals and support for the software packages EViews, Gretl, Shazam, and Stata. In addition, all the data for Principles of Econometrics, 4th in various formats, including Excel, are available at http://www.wiley.com/college/hill. Individual data files, as well as errata for this manual and the textbook, can also be found at http://principlesofeconometrics.com. The chapters in this book parallel the chapters in Principles of Econometrics, 4th. Thus, if you seek help for the examples in Chapter 11 of the textbook, check Chapter 11 in this book. However within a Chapter the sections numbers in Principles of Econometrics, 4th do not necessarily correspond to the Excel manual sections. This work is a revision of Using Excel 2007 for Principles of Econometrics, 3rd Edition by Genevieve Briand and R. Carter Hill (Wiley, 2010). Genevieve Briand is the corresponding author. We welcome comments on this book, and suggestions for improvement. *

Genevieve Briand School of Economic Sciences Washington State University Pullman, WA 99164 [email protected] R. Carter Hill Economics Department Louisiana State University Baton Rouge, LA 70803 [email protected]

*

Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation. Our use does not directly or indirectly imply Microsoft sponsorship, affiliation, or endorsement.

iv

BRIEF CONTENTS 1. Introduction to Excel

1

2. The Simple Linear Regression Model

19

3. Interval Estimation and Hypothesis Testing

67

4. Prediction, Goodness-of-Fit and Modeling Issues 5. The Multiple Linear Regression

143

6. Further Inference in the Multiple Regression Model 7. Using Indicator Variables 8. Heteroskedasticity

95

154

180

204

9. Regression with Time Series Data: Stationary Variables 10. Random Regressors and Moment-Based Estimation 11. Simultaneous Equations Models

228

262

278

12. Nonstationary Time-Series Data and Cointegration

294

13. Vector Error Correction and Vector Autoregressive Models 14. Time-Varying Volatility and ARCH Models 15. Panel Data Models

328

355

16. Qualitative and Limited Dependent Variable Models A. Mathematical Tools

391

402

B. Review of Probability Concepts C. Review of Statistical Inference Index

310

416 431

466

v

CONTENTS CHAPTER 1 Introduction to Excel 1 1.1 Starting Excel 1 1.2 Entering Data 3 1.3 Using Excel for Calculations 3 1.3.1 Arithmetic Operations 3 1.3.2 Mathematical Functions 4 1.4 Editing your Data 6 1.5 Saving and Printing your Data 8 1.6 Importing Data into Excel 10 1.6.1 Resources for Economists on the Internet 10 1.6.2 Data Files for Principles of Econometrics 13 1.6.2a John Wiley & Sons Website 13 1.6.2b Principles of Econometrics Website 14 1.6.3 Importing ASCII Files 14 CHAPTER 2 The Simple Linear Regression Model 19 2.1 Plotting the Food Expenditure Data 19 2.1.1 Using Chart Tools 21 2.1.2 Editing the Graph 23 2.1.2a Editing the Vertical Axis 23 2.1.2b Axis Titles 24 2.1.2c Gridlines and Markers 25 2.1.2d Moving the Chart 26 2.2 Estimating a Simple Regression 27 2.2.1 Using Least Squares Estimators’ Formulas 27 2.2.2 Using Excel Regression Analysis Routine 31 2.3 Plotting a Simple Regression 34 2.3.1 Using Two Points 34 2.3.2 Using Excel Built-in Feature 38 2.3.3 Using a Regression Option 38 2.3.4 Editing the Chart 40 2.4 Expected Values of b1 and b2 44

2.4.1 2.4.2

2.5 2.6

2.7

Model Assumptions 45 Random Number Generation 47 2.4.3 The LINEST Function 49 2.4.4 Repeated Sampling 50 Variance and Covariance of b1 and b2 52 Nonlinear Relationships 53 2.6.1 A Quadratic Model 53 2.6.1a Estimating the Model 53 2.6.1b Scatter Plot of Data with Fitted Quadratic Relationship 55 2.6.2 A Log-Linear Model 57 2.6.2a Histograms of PRICE and ln(PRICE) 57 2.6.2b Estimating the Model 61 2.6.2c Scatter Plot of Data with Fitted LogLinear Relationship 62 Regression with Indicator Variables 63 2.7.1 Histograms of House Prices 63 2.7.2 Estimating the Model 65

CHAPTER 3 Interval Estimation and Hypothesis Testing 67 3.1 Interval Estimation 68 3.1.1 The t-Distribution 68 3.1.1a The t-Distribution versus Normal Distribution 68 3.1.1b t-Critical Values and Interval Estimates 69 3.1.1c Percentile Values 69 3.1.1d TINV Function 69 3.1.1e Appendix E: Table 2 in POE 71 3.1.2 Obtaining Interval Estimates 71 3.1.3 An Illustration 71

vi

3.2

3.3

3.4

3.1.3a Using the Interval Estimator Formula 71 3.1.3b Excel Regression Default Output 73 3.1.3c Excel Regression Confidence Level Option 74 3.1.4 The Repeated Sampling Context (Advanced Material) 75 3.1.4a Model Assumptions 75 3.1.4b Repeated Random Sampling 75 3.1.4c The LINEST Function Revisited 77 3.1.4d The Simulation Template 78 3.1.4e The IF Function 79 3.1.4f The OR Function 79 3.1.4g The COUNTIF Function 80 Hypothesis Tests 81 3.2.1 One-Tail Tests with Alternative “Greater Than” (>) 81 3.2.2 One-Tail Tests with Alternative “Less Than” (