Applications of Spreadsheet Optimization Capabilities in Teaching Planning Methods

Abstract

E lectronic spreadsheets were among the first personal computer programs used in planning and are widely used in planning education and practice. Several widely used books present spreadsheet models for a variety of planning applications (e.g., see Klosterman, Brail, and Bossard 1993). Nevertheless, few planners realize the tremendous potential of modern spreadsheets for conducting sophisticated analyses-such as the fitting of spatial interaction models-applications that until recently could be carried out only with customized programs or complicated spreadsheet models.Modern electronic spreadsheets include powerful optimizing capabilities, such as the Solver in Excel, that extend the capabilities of spreadsheets to perform new types of analysis. Planning models that previously could have been implemented only with more complex programming can now be implemented within the familiar environment of the spreadsheet.This article illustrates the use of these optimization capabilities of spreadsheets to implement facility location and spatial interaction models. The article describes the use of these spreadsheet models in the teaching of planning methods, using the models as a part of an integrated project involving the determination of the location of a new public facility. While the example being used involves the location of a new public library, the spreadsheet models have broader applicability to a wide range of planning problems involving the location of many different types of public facilities, from social service centers to public safety facilities. The example provides new ideas for the use of spreadsheets in the teaching of planning methods. In addition, the example demonstrates the potential that spreadsheets provide for planning educators, students, and practitioners to develop their own sophisticated spreadsheet models. ᭤ Spreadsheets in Planning, Optimization in SpreadsheetsPlanners recognized very early the great potential of electronic spreadsheets for carrying out the analyses required as a part of the planning process, and spreadsheets were AbstractThe powerful optimization capabilities included in modern electronic spreadsheets, such as the Solver in Excel, provide opportunities for implementing facility location and spatial interaction models in the spreadsheet environment. Such models are used as part of an integrated public facility location assignment in the teaching of planning methods. These spreadsheet applications provide students with the tools for applying the models to realistic planning problems and illustrate the potential for using electronic spreadsheets for implementing sophisticated planning methods.

Economic Impact Analysis amid Rapid Change

“…A multitude of software options provide broad access to econometric techniques at all levels of statistical and computing sophistication, and, as with input–output software, enable economic impact studies to be conducted with insufficient attention to (or perhaps not enough patience for) the subtleties of the method and its prerequisite conditions. Outside of the scholarly realm, econometric models are not yet commonly applied in economic impact analyses, but are frequently employed for generating forecasts of population and land values (sometimes as components within a more complex set of models), which in turn are essential inputs into fiscal impact and facility siting analyses (e.g., Norton 2007; Ottensman 2000; Edwards and Huddleston 2010). The numerous cautions elucidated by scholars and researchers (contained in any standard econometrics or statistics textbook; e.g., Allison 1999; Gujarati 2003) should be evaluated thoroughly by analysts in considering whether to apply an econometric model for impact assessment and by educators in teaching econometric modeling and forecasting.…”