Management Science: The Art of Modeling with Spreadsheets, Third Edition

Chapter 8. Regression Analysis

INTRODUCTION

Spreadsheet models consist of numerical inputs (data and perhaps decision variables) and calculated variables that are related by equations. These equations, or relationships among variables, are drawn from a variety of sources. Some are essentially definitions, such as Profit = Revenue - Cost, or Net Cash Flow = Cash Income + Depreciation - Capital Expenditures. Other relationships are essentially logical, such as this IF statement for units sold: IF(Demand>Units available, Units available, Demand). Another class of relationships attempts to describe the behavior of individuals or other entities. The relationship between advertising and sales in the Advertising Budget example of Chapters 5 and 6 (Unit sales = 35 x Seasonal factor x SQRT(3000 + Advertising)) is such a behavioral relationship, because it describes how advertising influences the decisions of consumers.

Two aspects of a behavioral relationship are important: its structural form and its parameters. In the relationship for unit sales above, the parameters are the numbers 35 and 3,000, whereas the structural form is nonlinear. The ultimate outcome of a spreadsheet model can depend, of course, on both the values of the parameters and the functional form of the relationship. This is why in Chapter 6 we advocated performing sensitivity analysis on both the parameters and the structure of the key relationships in a model.

In Chapter 2 we pointed out that we can incorporate parameters ...