UBC Theses and Dissertations

UBC Theses Logo

UBC Theses and Dissertations

The value of spreadsheet programs to planners Kenyon, Mitchell Alan William 1988

Your browser doesn't seem to have a PDF viewer, please download the PDF to view this item.

Item Metadata


831-UBC_1988_A8 K46.pdf [ 13.35MB ]
JSON: 831-1.0097683.json
JSON-LD: 831-1.0097683-ld.json
RDF/XML (Pretty): 831-1.0097683-rdf.xml
RDF/JSON: 831-1.0097683-rdf.json
Turtle: 831-1.0097683-turtle.txt
N-Triples: 831-1.0097683-rdf-ntriples.txt
Original Record: 831-1.0097683-source.json
Full Text

Full Text

T H E V A L U E O F S P R E A D S H E E T P R O G R A M S T O P L A N N E R S B y M I T C H E L L A L A N W I L L I A M K E N Y O N B . A . ( H o n o u r s ) , T h e U n i v e r s i t y o f A l b e r t a , 1984 A T H E S I S S U B M I T T E D I N P A R T I A L F U L F I L L M E N T O F T H E R E Q U I R E M E N T S F O R T H E D E G R E E O F M A S T E R ; O F A R T S i n T H E F A C U L T Y O F G R A D U A T E S T U D I E S S c h o o l o f C o m m u n i t y a n d R e g i o n a l P l a n n i n g W e a c c e p t t h i s t h e s i s as c o n f o r m i n g to t h e r e q u i r e d s t a n d a r d T H E U N I V E R S I T Y O F B R I T I S H C O L U M B I A A p r i l , 1988 © M i t c h e l l A l a n W i l l i a m K e n y o n , 1988 In presenting this thesis in partial fulfilment of the requirements for an advanced degree at the University of British Columbia, I agree that the Library shall make it freely available for reference and study. I further agree that permission for extensive copying of this thesis for scholarly purposes may be granted by the head of my department or by his or her representatives. It is understood that copying or publication of this thesis for financial gain shall not be allowed without my written permission. Department of C Q m m u n i t y a n c l Regional Planning The University of British Columbia 1956 Main Mall Vancouver, Canada V6T 1Y3 A p r i l 16, 1988 DE-6(3/81) ABSTRACT According to Brail, a planning professor at Rutgers University, it is abundantly clear that the electronic spreadsheet is the single most important computer tool available to the practicing planner, (working paper, 1985,1) What is the value, measured in increased productivity, of spreadsheet programs to planners? Spreadsheet programs, referred to simply as spreadsheets, are an enhanced version of the paper spreadsheet. Paper spreadsheets are tabular forms commonly used by bookkeepers and accountants. Spreadsheets perform computations that would be tedious and perhaps impossible on a paper spreadsheet. A number of factors influence the value of spreadsheets to planners. This thesis discuses three factors in detail: the capabilities of spreadsheets, the utility of the planning models whose computations sometimes involve spreadsheets, and the savings in development time by employing spreadsheet templates. Assessing the capabilities of spreadsheets requires clarifying the concept of spreadsheets and describing their general applications. Clarifying the concept includes defining spreadsheet terms such as worksheet, functions, macros, and templates', depicting the evolution of spreadsheets; highlighting differences between spreadsheet packages; and noting the limitations of spreadsheets. Describing the general applications involves discussing the three major uses of spreadsheets: table production, numerical analysis, and the testing of "what i f scenarios. Evaluating the utility of the planning models whose computations sometimes involve spreadsheets requires two steps. The first step establishes the relationship between models and spreadsheets in data analysis for planning. The second step examines the advantages and disadvantages of the planning models. Determining the savings in development time by employing templates involves three steps. The first step constructs criteria by which to evaluate the structure of a ii template for accuracy and ease of use. The second step examines how well available templates fit the planning model discussed. The third step evaluates the structure of each of the templates using the criteria constructed. Five chapters compose the body of the thesis. The first chapter explains the concept of the spreadsheet. The second chapter illustrates the general applications of spreadsheets, determines the role of spreadsheets in data analysis for planning, and develops criteria by which to evaluate a template's structure. Each of the last three chapters demonstrates the potential of spreadsheets to planners by describing and evaluating planning models and spreadsheet templates. There are many factors influencing the value of spreadsheets to planners not covered in this thesis which may prove grounds for further research. The thesis ignores the affect the planner's access to computers and programs, knowledge of computers, and attitude towards computers has on the value of spreadsheets. Furthermore, the thesis does not establish the relative worth of spreadsheets in comparison to other possible tools for performing the computation of a planning model. Therefore, this thesis is unable to confirm Brail's claim that spreadsheets are the most important computer tool. Nevertheless, this thesis provides reasons for Brail's enthusiasm towards spreadsheets. iii TABLE OF CONTENTS TITLE PAGE i ABSTRACT ii TABLE OF CONTENTS iv LIST OF TABLES ix LIST OF FIGURES x ACKNOWLEDGEMENTS xiii Chapter 1 INTRODUCTION 1.1 Reason for this Thesis 2 1.2 Planning Context 2 1.3 Methodology 2 1.4 Scope and Limitations 4 1.5 Organization of the Chapters 4 1.6 Conventions used in this Thesis 5 1.7 Chapter Summary 5 Chapter 2 THE SPREADSHEET CONCEPT 2.0 Introduction 7 2.1 Worksheet 7 2.2 Functions 9 2.3 Macros 9 2.4 Templates 11 2.5 Evolution of Spreadsheets 13 2.6 Differences Between Spreadsheet Packages 15 2.7 Limitations of Spreadsheets 15 2.8 Chapter Summary 17 IV Chapter 3 GENERAL APPLICATIONS 3.0 Introduction 3.1 Table Production 3.2 Numerical Analysis 3.3 Testing "What i f Scenarios 3.4 Role of Spreadsheets in Data Analysis for Planning 3.5 Criteria to Evaluate a Template's Structure 3.6 Chapter Summary Chapters 4,5 and 6 SPECIFIC APPLICATIONS OF SPREADSHEETS IN PLANNING Chapter 4 DEMOGRAPHIC APPLICATIONS 4.0 Introduction 34 4.0.1 Overview of Population Forecasting Models 34 4.1 Mathematical Trend Extrapolation 4.1.1 Planning Literature 37 4.1.2 Small Area Population Projection Template 38 4.1.3 Structure of Small Area Population Projection 41 4.2 Cohort Survival Model 4.2.1 Planning Literature 41 4.2.2 Population Projection Template 43 4.2.3 Structure of Population Projection 45 4.2.4 Hajjey Template 45 4.2.5 Structure of Haljey 47 4.4 Residential Carrying Capacity Model 4.3.1 Planning Literature 48 4.3.2 Land Template 48 4.3.3 Structure of Land 51 4.5 Chapter Summary 54 19 19 20 22 23 28 31 Chapter 5 ECONOMIC APPLICATIONS 5.0 Introduction 57 5.1 Economic Base Analysis 5.1.1 Planning Literature 57 5.1.2 Location Quotient Template 61 5.1.3 Structure of Location Quotient 63 5.2 Shift Share Analysis 5.2.1 Planning Literature 63 5.2.2 Employment Shifts/Shares Template 69 5.2.3 Structure of Employment Shifts/Shares 71 5.2.4 Shift Share Analysis Template 71 5.2.5 Structure of Shift Share Analysis 72 5.3 Cost/Benefit Analysis 5.3.1 Planning Literature 72 5.3.2 Benefit/Cost Analysis Template 83 5.3.3 Structure of Benefit/Cost Analysis 86 5.4 Chapter Summary 87 Chapter 6 TRANSPORTATIONAL APPLICATIONS 6.0 Introduction 90 6.1 Spatial Distribution Analysis 6.1.1 Planning Literature 90 6.1.2 Spatial Distribution Analysis Template 93 6.1.3 Structure of Spatial Distribution Analysis 94 6.1 Disaggregate Elasticity Model 6.2.1 Planning Literature 95 6.2.2 Disaggregate Elasticity Model Template 98 6.2.3 Structure of Disaggregate Elasticity Model 100 6.2 Route Evaluation Model 6.3.1 Planning Literature 103 6.3.2 Route Evaluation - Cost Model Template 106 6.3.3 Structure of Route Evaluation - Cost Model 108 6.4 Chapter Summary 110 Chapter 7 CONCLUSION 7.0 Introduction 113 7.1 Spreadsheets 113 7.1.1 Concept 113 7.1.2 General Applications 114 7.2 Planning Models 114 7.3 Templates 115 7.3.1 Applicapility of Templates 116 7.3.2 Structure of Templates 116 7.4 The Future of Spreadsheets in Planning 119 7.5 Further Research 119 7.6 Closing Comments 119 BIBLIOGRAPHY 122 APPENDIX A Trademark Acknowledgements 131 B Letter sent to Planners and Planning Agencies 132 C Glossary of Computer and Spreadsheet Terms 133 D Graph Types 137 E Major Spreadsheet Functions 139 F Catalogue of Spreadsheet Templates 141 G Spreadsheet Add Ons and Peripheral Packages 157 H List of Spreadsheet Programs 163 I A Comparison of Two Spreadsheet Programs 165 J Statistical Capabilities 166 K Solving Unsolvable Equations 169 L Pascal Program for the Cohort Survival Model 170 BIOGRAPHICAL FORM 174 SPREADSHEET TEMPLATES File name Disk DEMOGRAPHIC TEMPLATES Small Area Population Projection POPULATN 1 Population Projection POPULATION 3 Halley HALLEY 1 Land LAND 2 (ten year comparisons) LAND-TC 2 (long range comparisons) LAND-TC 2 (alternative A) LAND-A 2 (alternative B) LAND-B 2 ECONOMIC TEMPLATES Location Quotients  Employment Shifts/Shares  Shift Share Analysis  Benefit/Cost Analysis LOCATION 1 SfflFTSHl 1 SHIFTSH2 2 BENEFIT.COST 3 TRANSPORTATION TEMPLATES Demand Elasticity Model DEL 1 (case study) DELCASE 1 (elasticity tables) DELELAS 1 Route Evalutation - Cost Model RECM 2 (all routes) RECM-AR 2 (demo route A) RECM-A 2 (demo route B) RECM-B 2 (demo route C) RECM-C 2 Spatial Distribution Analysis SPATIAL 3 Disk 1 is Lotus version 1A (1983) for IBM computers Disk 2 is Lotus version 2 (1985) templates for IBM computers Disk 3 is VisiCalc version 2 (1981) templates converted to Appleworks viii LIST OF TABLES Table I: Evolution of Spreadsheets 14 Table II: Comparing a Stream of Benefits 77 Table HI: Present Value 79 Table IV: Constraints on Projects 81 Table V: Illustration of the Three Decision Algorithms 82 Table VI: Normalizing Procedure for Ranking Projects 82 Table VII: Example Transit Elasticities 97 Table VIII: Chicago Example of Measures of Effectiveness 104 Table IX: List of Major Spreadsheet Functions 139 Table X: List of Spreadsheet Programs 163 Table XI: Comparison of Two Spreadsheet Programs 165 Table XII: Names of Statistical Templates 166 ix LIST OF FIGURES Figure 1: Work-sheet Displaying Labels and Values 8 Figure 2: Worksheet Displaying the Formulas behind the Values 8 Figure 3: Spreadsheet's Logical Limit 16 Figure 4: Example Table and Graph 19 Figure 5: The "Calculator" Capabilities 20 Figure 6: The "Large Statistical Package" Capabilities 21 Figure 7: Data Analysis for Planning 24 Figure 8: Selection of Analysis 25 Figure 9: Template Structural Evaluation Form 29 Figure 10: Worksheet Map for Air Quality Template 32 Figure 11: Mathematical Trend Extrapolation Models 37 Figure 12: Population Projection Template 39 Figure 13: 1980 Population Pyramid 41 Figure 14: Population Projection Template 44 Figure 15: Hallev Template 46 Figure 16: Land Template 49 Figure 17: Graphs Generated with Land 50 Figure 18: Partial Decipherment of a Cell Formula 53 Figure 19: Tracking Cell Dependencies 54 Figure 20: Location Quotient Template 62 Figure 21: Regional Proportion and Regional Shift Components 66 Figure 22: Employment Shifts/Shares Template 69 Figure 23: Shift Share Analysis Template 71 Figure 24: Consumer Surplus 75 Figure 25: Non-marginal Changes 75 Figure 26: Monopolistic Price 76 Figure 27: Cost/Benefit Analysis Template 84 Figure 28: Template Design 86 Figure 29: Spatial Distribution Analysis Template 93 Figure 30: Disaggregate Elasticity Model 99 Figure 31: Example of "Paging" 100 Figure 32: Graph Generated with Disaggregate Elasticity Model 101 Figure 33: Error Flag Table 101 Figure 34: On-line Documentation 102 Figure 35: Evaluation Matrix 105 Figure 36: Impact - Incidence Matrix 105 Figure 37: Example Evaluation Matrix 106 Figure 38: Route Evaluation - Cost Model Template 107 Figure 39: Allroute Template 108 Figure 40: Example of Macros 109 Figure 41: Summary of the Template Structural Evaluations 118 Figure 42: Line Graph 137 Figure 43: Bar Chart 137 Figure 44: Column Chart 138 Figure 45: Pie Chart 138 Figure 46: Area Chart 138 xii ACKNOWLEDGEMENTS I would like to thank the following people for their support, without which this thesis could not have been possible. Daniel J. Bower, TIME Support Center Catherine J. Carlson, City of Miami Planning Department Tim Cartwright, United Nations Centre for Human Settlements Craig Davis, University of British Columbia Christopher R. Fleet, U.S. Department of Transportation Henry Hightower, University of British Columbia Rick Kuner, New Alternatives, Inc. Robert L. Peskin, Manager, Peat, Marwick, Mitchell & Co. Appendix A gives the trademark acknowledgements INTRODUCTION Introduction 1 1.1 THE REASON FOR THIS THESIS Planners do not comprehend the full potential of spreadsheet programs. Few planners have attained a high level of proficiency in microcompu-ters. Furthermore, applications software [such as spreadsheets] and templates are only beginning to become available in our field. (Sawicki 1985, 214) By assessing the capabilities of spreadsheets, the effectiveness of the planning models whose computations involve spreadsheets, and the savings in development time by employing templates, this thesis attempts to determine and communicate some of the value of spreadsheets to planners. 1.2 THE PLANNING CONTEXT Spreadsheets are for planning. As Roger Clark claims in his spreadsheet manual Executive VisiCalc for the IBM Personal Computer, "if you are a planner, you will be able to do your job with greater accuracy and with a better chance of being right [by using spreadsheets]"(Clark 1983, vi). In fact, spreadsheets such as Multiplan, PFSiplan, PlannerCalc, MicroPlan, and VP Planner incorporate the word "plan" into their name. Chapter 3 will explain how spreadsheets and planning can be synonymous. Many planners use spreadsheets successfully and the second half of this thesis describes some of their experiences. 1.3 THE METHODOLOGY The primary source of information was "hands on experience" in learning to use spreadsheet programs, writing spreadsheet applications, learning to use and modify other planners' applications, and exploring the limitations of spreadsheets. The secondary source of information was from articles and manuals about spreadsheets; articles, books, and lectures about planning models; and template documentation. The method of analysis was to search for planning applications, to select some applications, and to evaluate the applications. The search for planning applications consisted of contacting by letter (see Appendix B) the planners and planning agencies advertising templates in the U.S. Department of Transportation's "Software and Introduction 2 Source Book", the United Nation's "Microcomputer Users Directory", and the American Planning Association's "Planning Software Survey" and requesting tem-plates, documentation, and leads on other distributed templates. The leads on other distributed templates in the replies from the letters were followed up. Most of the planning applications mentioned in the thesis come from four sources: Bureau of Economic and Business Research, University of Florida; Urban and Regional Studies Institute, Mankato State University; Transit Industry Microcomputer Exchange, Rensselaer Polytechnic Institute; and the Center for Microcomputers in Transporta-tion, University of Florida. The selection of the planning applications consisted of choosing applications that either use familiar planning models1 or are especially appropriate to illustrate points about spreadsheets2. The survey was not random since not all planning applications were considered, and the selection of those applications for evaluation was biased. The evaluation of the templates involved three steps. The first step was to review the literature on planning models used in spreadsheet templates. The second step was to discuss templates in the context of the planning literature. The third step was to evaluate the structure of templates for accuracy and ease of use. The three adjectives kept in mind when writing the thesis were: informative, lasting, and comprehensible. Informative means that the reader will have a better understanding of spreadsheets from reading this thesis. Lasting means that the content of the thesis will not be obsolete with the release of the next spreadsheet version, and comprehensible means that the reader will be able to follow the thesis without needing any computer equipment, reference materials or tutors. 'Templates which use familiar planning models include: Small Area Population Projection. Population Projection, Location Quotients. Shift Share Analysis. Employment Shifts/Shares. Benefit/Cost Analysis, and Spatial Distribution  Analysis. templates which are especially appropriate to illustrate points about spreadsheets include: Hallev. Land. Demand  Elasticity Model, and Route Evaluation - Cost Model. Introduction 3 1.4 SCOPE AND LIMITATIONS The intent of this thesis is to determine how productive a tool spreadsheet programs are for planners. However, it is beyond the scope of the thesis to determine if there are better tools than spreadsheets for performing the computation of a particular planning model. The thesis also demonstrates a breadth of knowledge of planning but ignores computer science topics such as how to use a spreadsheet and what computer equipment is needed. Furthermore, the thesis does not exhaust the list of spreadsheet applications to planning since the list is exceedingly long. 1.5 T H E ORGANIZATION OF THE CHAPTERS Chapter 1 explains the objectives of the thesis, the relevance to planning, the methodology used, the scope and limitations of the thesis, the organization of the chapters, and the conventions used. Chapter 2 is an overview of spreadsheets which looks at their functions, macros, templates, evolution, differences, and constraints. The first half of Chapter 3 discusses the three general applications of spread-sheets: table production, numerical analysis, and the testing of "what i f scenarios. The second half determines the role of spreadsheets in data analysis for planning and develops criteria by which to evaluate a template's structure. Chapters 4, 5, and 6 deal with demographic, economic, and transportation planning applications, respectively. Each chapter evaluates three applications. The format of each evaluation consists of examining the purpose, limitations, and vari-ations of the model; the applicability of one or two templates to the model; and the structure of the template(s) for accuracy and ease of use. Chapter 7 is a summary of the findings. The Appendices elaborate on the ideas presented in the main text either in a more technical manner or in a direction which goes beyond the objectives of the thesis. Introduction 4 1.6 CONVENTIONS USED IN THIS THESIS This thesis uses a number of conventions with regard to spreadsheet terms. The glossary in Appendix C defines all spreadsheet terms in the thesis and chapter two, "The Spreadsheet Concept", defines some of these terms in more detail. The definitions below distinguish between the terms which the reader may think are interchangeable. WORKSHEET: is a two-dimensional table, each cell of which may be assigned a numeric constant, a formula with which to calculate a value that may use constants and formulas in other cells, or a verbal label or comment. SPREADSHEET: is a computer software package with a worksheet as its principle module. A less common definition is that spreadsheets are a programming language like BASIC and as many versions of BASIC (GW/BASICA, Applesoft BASIC, etc.) exist, so do many versions of spreadsheets (Lotus, VisiCalc, etc.). TEMPLATE: is the product of a spreadsheet. It is usually sold as a separate software product, that enables the spreadsheet to solve a specific business [or planning] problem. A template includes column and row labels, a format for information, and formulas for calculating answers. The buyer reads the template into the spreadsheet program and fills in the variable information, and the spreadsheet utilizes the formulas to furnish the desired results. (Porter, 295) When spreadsheets are defined as programming languages, then a template is referred to as a computer program written in the spreadsheet language. DEVELOPER: is the person who writes templates from blank worksheets. USER: is a person who puts a template to work in an actual planning situation. The user enters data, then retrieves information, and if necessary, modifies the template. ANALYST, ECONOMIST, FORECASTER, and PLANNER are interchangeable. 1.7 CHAPTER SUMMARY This thesis attempts to determine the value of spreadsheets to planners. Introduction 5 CONCEPT Concept 6 2.0 INTRODUCTION Spreadsheets are powerful programs. Planners use spreadsheets for many applications including cohort survival models, cost-benefit analyses, and spatial distribution analysis. When purchasing software packages, it makes economic sense to purchase one spreadsheet program that does many applications rather than purchas-ing many dedicated programs that do only one application each, especially when a dedicated program can cost more than a spreadsheet program.1 When writing software, using a spreadsheet is often far faster and more economical, with nominal loss of throughput (the overall time to solve a problem) [than a traditional programming language]. In fact, given the life cycle of the computer upon which they are based, speedy development and maintenance are far more pertinent than computational speed. (Brail, working paper) Furthermore, modifying a spreadsheet template is relatively simple. (Bower and Abkowitz, 1986,1) In short, spreadsheets are useful, inexpensive and easy to use. According to planning professor Richard Brail, spreadsheets have had a major impact on planners. Spreadsheets have introduced the microcomputer to a wide variety of individuals with an impact which far exceeds any previous software package for any computer, micro, mini, or mainframe.(Brail 1984, 55) This chapter explains in detail what this remarkable software package called "spread-sheet" is. 2.1 WORKSHEET Integrated spreadsheet packages may consist of several modules such as worksheet, database and graphics (see Appendix D). It is the worksheet which is the heart of the spreadsheet package and the focus of this thesis. The three distinguishing 'The cost of T -model , a dedicated transportation program for analyzing intersection capacity is $1200 (US). The cost of the Lotus 1-2-3 spreadsheet program is $500 (US) and the Lotus 1-2-3 template that interactively calculates volume/capacity at individual intersections is available for $2 (US) from the Center for Microcomputers in Transpor-tation at the University of F lor ida. Concept 7 characteristics of the worksheet are its table format, calculator abilities, and modifia-bility. The table format is a matrix of columns and rows resembling a Hippodamus gridiron street pattern. Most spreadsheets identify the columns by letter designation and the rows by numbers. The intersection of a column and a row forms a cell. The cell's address contains the column's letter designation and the row's number (see Figures 1 and 2). In addition to having an address, the user can give a cell or a block Figure 1: A Worksheet Displaying Labels and Values A B C D E F G 1 ECONOMIC BASE ANALYSIS TEMPLATE 2 20-Jan-88 3 EMPLOYMENT 4 UNITED STATES LOCAL GOVERNMENT FOR LOCAL EXPORT 5 DESCRIPTION EMPLOYMENT % OF TOTAL EMPLOYMENT % OF TOTAL REQUIREMENT EMPLOYMENT 6 7 CONTRACT CONSTRUCTION 4,346,100 4.81 3,579 9.78 1,759 1,820 8 MANUFACTURING 20,285,000 22.44 4,168 11.39 8,208 0 9 TRANSPORTATION & PUBLIC UTILITIES 5,146,000 5.69 1,490 4.07 2,082 0 10 WHOLESALE TRADE 5,275,000 5.83 1,666 4.55 2,135 0 11 RETAIL TRADE 15,035,200 16.63 11,919 32.58 6,084 5,835 12 FINANCE, INSURANCE & REAL ESTATE 5,045,700 5.58 3,387 9.26 2,042 1,345 13 SERVICES 16,546,400 18.30 9,496 25.96 6,696 2,800 14 OTHERS 18,727,000 20.71 878 2.40 7,578 0 15 16 TOTAL 90,406,400 100 36,583 100 36,583 11,801 Figure 2: A Worksheet Displaying the Formulas Behind the Values A B C D E F G 1 ECONOMIC BASE ANALYSIS TEMPLATE 2 30700 3 EMPLOYMENT 4 UNITED ST TES LOCALGOVEI NMENT FORLOCAL EXPORT 5 DESCRIPTION EMPLOYMENT %OF TOTAL EMPLOYMENT % OF TOTAL REQUIREMENT EMPLOYMENT 6 7 C0NTRACTCONSTRUCT10N 4346100 =87/$B$16*100 3579 =D7/$D$16'100 =C7'$D$16/100 =IF(D7-F7<0,0,D7-F7) 8 MANUFACTURING 20285000 =B8/$B$16'100 4168 =D8/$D$16'100 =C8'$D$16/100 =IF(DB-F8<0,0,D8-F8) 9 TRANSPORTATION & PUBLIC UTILITIES 5146000 =B9/$B$16*100 1490 =D9/$D$16*100 =C9*$D$16/100 =IF(D9-F9<0,0,D9-F9) 10 WHOLESALE TRADE 5275000 =B10/$B$16'100 1666 =D10/$D$16*100 =C10'$D$16/100 =IF(D10-F10<0,0,D10-F10) 11 RETAILTRADE 15035200 =B11/$B$16*100 11919 =D11/$D$16*100 =C11 *$D$16/100 =IF(D11-F11<0,0,D11-F11) 12 FINANCE, INSURANCE & REAL ESTATE 5045700 =B12/$B$16*100 3387 = D12/$D$16'100 =C12'$D$16/100 =IF(D12-F12<0,0,D12-F12) 13 SERVICES 16546400 = B13/$B$16'100 9496 =D13/$D$16*100 =C13*$D$16/100 =IF(D13-F13<0,0,D13-F13) 14 OTHERS 18727000 =B14/$B$16*100 878 =D14/$D$16*100 =C14'$D$16/100 =IF(D14-F14<0,0,D14-F14) 15 16 TOTAL =SUM(B7:B14) =SUM(C7:C14) =SUM(D7:014) =SUM(E7:E14) =SUM(F7:F14) =SUM(G7:G14) Concept 8 of cells a name for easy reference. A cell that is not empty contains either a label (text) or a value. A value is either a constant or a formula. Cells also contain format specifications and cell protection. Enabling cell protection prevents accidental erasures of important labels and values. One of the advantages of spreadsheets is that the user can quickly move or copy the contents of one cell to another. The second feature of the worksheet is that it has some of the capabilities of a programmable calculator. Section 2.2 and Appendix E discuss some of the similar functions. Perhaps the biggest difference between a worksheet and a calculator is convenience. The worksheet has a larger screen than a calculator. Furthermore, while all worksheets have printing capabilities, calculators seldom do. Foremost, the worksheet automatically remembers data, formulas and the formulas' references to the cells containing data without having to program or use memory keys. This retention of data and formulas results in the worksheet's third significant feature, modifiability. The spreadsheet's commands simplify the modification of the data and formulas. This can be a crucial time saver, for if a number in the data is wrong or subject to change, the user can change the number without having to re-enter the formulas and data. If the user changes a number or formula, the spreadsheet automatically recalculates all the formulas and instantly displays new results. 2.2 FUNCTIONS In addition to the five familiar arithmetic operations of addition, subtraction, multiplication, division, and exponentiation, spreadsheets have over fifty built-in functions. These functions are pre-defined formulas which, when given parameters, will return a result. Such functions fall into seven categories: arithmetic, trigonomet-ric, regression analysis, financial, logical and matrix. Appendix E lists some of these functions. 2.3 MACROS A macro is a set of commands that the developer builds into the worksheet which the user executes with a single command. The three advantages of using macros are Concept 9 that they save the user time, they are powerful, and they reduce human errors because they avoid human repetition. Macros are a time saver when the user needs to execute the same set of commands more than once. With a macro the user has only to type a single command each time the set of commands is needed, instead of having to type every command in the set. So macros are sometimes called "typing alternatives". For example, to print the "Intersection Capacity" template in Appendix F, the user can either type "p" while holding the ALTernate key down to invoke a macro, or type the much longer "/ pparp~gpq". However, macros are more powerful than the term "typing alternative" implies. The Lotus Command [macro] Language is far from being just a keyboard enhancer, it is as capable as any programming language, including BASIC, C, and Pascal, and is perhaps even more capable than some programming languages.(Quinn 1986, 22) Macros have most of the logical structures found in the programming environ-ment. This enables developers to build macros to expedite their work and to create macro-driven templates that increase the users' productivity. Macro-driven templates take over control of all operations of the spreadsheet so the user has only to enter the data while the macro does all the heavy work. The operations performed by the macro can include such diverse tasks as prompting the user for data, positioning the data in the worksheet, formatting the data, applying formulas to the data, plotting graphs, printing the results and saving the worksheet on disk. Macro categories include: menu, map, input, move, copy, computation, graph, database, print, and file. 1) MENU-macros provide a list of options for the template to execute. 2) MAP-macros help the user move around the template. 3) INPUT-macros (similar to "range" in Lotus) prompt for, position, and format data. 4) MOVE-macros move sections of the worksheet to another location. Concept 10 5) COPY-macros copy sections of the worksheet to another location. 6) COMPUTATION-macros apply formulas to data. 7) GRAPH-macros draw a graph. 8) DATABASE-macros perform data sorts, query, etc. 9) PRINT-macros print all or part of the worksheet. 10) FILE-macros retrieve, save, combine, and erase files. Macros also reduce human error. Once a macro's syntax is correct, users can invoke the macro over and over again with confidence, since a syntactical error will not occur.2 In contrast, if the user has to enter the commands manually over and over again, sooner or later a human error will occur. Since these error free macros work in other templates, it is helpful to maintain a macro library. 2.4 TEMPLATES A template is a program written in the spreadsheet language, containing formulas, macros, and documentation, which accomplishes a desired task (such as the computation in a planning model) that can be repeated using different data with few or no modifications to the program. In the same way that a circle cut out of paper becomes a template for drawing other circles, every worksheet containing formulas or macros becomes a template upon completion. By reusing a template, the user saves considerable time designing a layout and typing in formulas and macros. This is why a number of organizations distribute templates (see Appendix F). Distributed templates have a number of problems. Since planners design templates for specific agencies and not for distribution, the templates may not conform to the needs of another organization. In addition, errors may plague the templates. "Experts say that one or more bugs infest one out of every five spreadsheets [templates]"(Bryan, December, 1986,38). Furthermore, the template may have poor structure, making it difficult to adapt to another agency's needs, especially if the documentation fails to provide adequate information on the template's objectives, 2 However, another kind of error could occur if the user does not use the macro for its intended purpose or if the macro does not perform as intended by the developer. Concept 11 operations, or model. There is a potential for abuse or misuse of analytic techniques and models that are poorly understood by the user, inadequately described by the author, theoretically weak, and inappropriate for the intended purpose(Klosterman 1986, 201). To reduce the potential for abuse or misuse, planners should carefully check a distributed template for applicability and structure. Therefore, the user needs to have a good working knowledge of the spreadsheet program, the planning model, and the template. It is also helpful to have a logical framework for judging a template's usefulness such as the one described in section 3.5. When evaluating a template, there are many possible conclusions. The best outcome is that the user has only to change the data. A slightly different outcome is that the template requires some minor modifications before changing the data. Another outcome is that modifying the template is less practical than using the template as a guide for the development of a new template. The worst outcome is that the template is not useful because it is completely inapplicable to the user's needs, contains too many errors, or is not well structured. A careful evaluation of the template should prevent the user from using an inappropriate template or a poorly structured template requiring the user to spend unforeseen hours modifying the template and entering the data. However, even with a careful evaluation, there is no guarantee that the template does not contain any errors or hidden and unacceptable assumptions as the following disclaimer points out. Although Lotus has reviewed and tested the applications in this book, LOTUS MAKES NO WARRANTY OR REPRESENTATION, EITHER EXPRESS OR IMPLIED, WITH RESPECT TO THE APPLICATIONS IN THIS BOOK, INCLUDING THEIR QUALITY, PERFORMANCE, MERCHANTABILITY, OR FITNESS FOR A PARTICULAR PUR-POSE. AS A RESULT, YOU, THE READER, ARE ASSUMING THE ENTIRE RISK AS TO THEIR QUALITY AND PERFORMANCE. (The Lotus Guide to Learning 123 1985. ii) Concept 12 Even if the quality and performance of a template is satisfactory, there are still opportunities for human errors to occur when entering the data or using the spreadsheet's commands. A Fort Lauderdale construction company underbid a job by $254,000 because the "@SUM" function did not work as expected. The company is "suing Lotus Development Corp. for millions of dollars in damages it claims were caused by an error in l-2-3"(Berry, December, 1986, 36). Whether the spreadsheet erred, the template erred, or the user erred is not as important as stressing the need to double check some of the results manually, with additional computation in the template, or with a spreadsheet auditor product (see Appendix G). 2.5 EVOLUTION OF SPREADSHEETS Quattro Ad PC Magazine December 22, 1987 we ma mt mr HAHC-HUO CALCULATOR VISICAI.C* LOTUS 1-3-3' OUATTRCT The evolution of spreadsheets has been very rapid and largely painless for the user. Spreadsheets are one of the few programs that have always taken and continue to take microcomputers to their limits in speed, memory and display capabilities. So the development of spreadsheets has kept pace with the rapid evolution of microcom-puters. In fact, Jared Taylor, PC Magazine's West Coast editor, claims "the history of spreadsheets is the history of Microcomputers" (1987, 94). Fortunately, the fast pace has been relatively painless for the user because new versions build upon the older versions. New versions will accept data from older Concept 13 versions. Also, new programs use successful programs like VisiCalc and Lotus 1-2-3 as industry standards so it takes the user familiar with these programs almost no time to adjust to the new programs and learn their added features. Table I gives a more detailed chronology of the evolution of spreadsheets dating from the birth of Microcomputers to the present. Table I: Evo lut ion of Spreadsheets D A T E E V E N T early 1940s 1974 1974-1977 1977 1978 1979 1980 1982 1984 1987 THE FIRST COMPUTERS were developed during World War II for military applications such as breaking coded messages and computing shell trajectories THE SILICON CHIP was developed by the Intel Corp. The chip enabled the processing power of a room-sized computer to fit into a chip less than one inch square. THE FIRST MICROCOMPUTERS were hobby kits like the MITS Altair. These microcomputers had lights and switches instead of a monitor and keyboard. Programming was done in machine language, and printers and disk drives did not exist. THE FIRST PRE-ASSEMBLED HOME COMPUTER was an Apple II with a disk drive. It was soon followed by the Radio Shack TRS-80 Model 1. The computer store was born. THE FIRST GENERATION OF SPREADSHEETS was conceived at the Havard School of Business by Daniel Brickin. Brickin was frustrated with the tedius manual method of doing coporate financial anlaysis under different assumptions. So he got together with two programming friends, Daniel Fylstra and Robert Frankson and developed VisiCalc for the new Apple microcomputer. VisiCalc was not only the granddaddy of spreadsheets, but also the first successful business program for microcomputers. In fact, some observesr argue that "VisiCalc launched the entire personal computer industry" (David Ewing and Joseph-David Carrabis, 1986, p. 37) ATTITUDES about home computers changed. "It is no longer demeaning for an executive to have a computer on his desk, and to be seen actually manipulating the keyboard. Managers and businessmen at all levels are using the machines, and it was the VisiCalc program that started the change of attitude." (Roger Clark, 1983, p. vi) with the change in attitude came the change in the name from "home computers" to "personal computers". DATABASE AND GRAPHICS MODULES were added with the introduction of SuperCalc. Other spreadsheets soon followed, including CalcStar, PerfectCalc, and Mulitplan. Data management capabilities were minimal (SuperCalc did not have the ability to sort until early 1983). Graphics capabilities were also minimal and usually required using another program like VisiPlot. THE SECOND GENERATION OF SPREADSHEETS were integrated programs like Context MBA and Lotus 1-2-3. Lotusl-2-3 integrated the worksheet, with a sophisticated database, and built in graphics. Today, Lotus 1-2-3 is the most successful business program for microcomputers, supporting over 2 million users (Byte, November 1986, p.122 and Tim Berry, September 1986, p. 57). IMPROVED INTEGRATED SPREADSHEETS like Symphony, Smart Framework, and Enable were released. These programs integrated the worksheet, database and graphics with a word processor and communications package. THE THIRD GENERATION OF SPREADSHEETS began with the release of the IBM version of Excel. Excel is a graphics-based program so it has cheery colors, scroll bars, icons, and menu bars. It is the first spreadsheet designed for the state of the art 80386 computers. Concept 14 2.6 THE DIFFERENCES BETWEEN SPREADSHEET PROGRAMS More than 50 spreadsheet programs exist as Appendix H illustrates. Almost all of these programs can handle the spreadsheet applications described in the latter half of this thesis. The difference between spreadsheet programs is usually insignificant to the needs of the user. Actually, I don't see much difference between spreadsheets... so I don't see why we worry so much about which one, instead of what we do with the one we've got. (Berry, January, 1986, 57) The only time a planner should worry about the differences between spread-sheets is when he does not have one. When shopping for a spreadsheet program, the most significant criteria are the spreadsheet's system requirements, number of mod-ules, and price. The system requirements include the microcomputer's make, model, and memory size. The number of modules range from one to over seven and may include a database, graphics, word processing, and communications module in addition to the worksheet module. The matter of price speaks for itself. Appendix I gives a more detailed look at the differences between spreadsheet programs by making ( a thorough comparison between two popular spreadsheet programs for the IBM PC. 2.7 LIMITATIONS OF SPREADSHEET USE Limitations of spreadsheet use can be classified as internal (or machine) and external (or human). Internal limitations are those inherent in the spreadsheet program. External limitations are those created by the user. The two major internal limitations are the principle limitations of computers: speed and memory. Version two of Lotus can perform fewer than 300 calculations/ second3 and stores only 75,068 values in a computer with 512K memory4. It also takes 3 This is based on a test using Lotus 1-2-3 release 2, and a computer with no enhancements. The test was to clock how fast Lotus can perform 8,000 simple calculations. Each calculation consisted of adding 1 and 1 together. It took lotus approximately 30 seconds to do the calculations. Therefore, based on this test, the current version of Lotus performs just over 250 calculations per second. 'This is based on a test using Lotus 1-2-3 release 2, and a computer with 512K memory. The test was to determine how many values of "1,000" the spreadsheet could hold before the "memory fu l l " error message would appear. The answer was 75,068 values or 3.6% of the Concept 15 time to load files and modules. In one test, Multiplan took 3 minutes, 26 seconds to load a 43K spreadsheet(Bryan, October, 1986, 66). While these figures will improve over time, speed and memory will always be limitations. Two other internal limitations are the spreadsheet's "logical limits"(Berry, October 1986,56) and "mechanicalness"(Lee 1973,167). The "logical limits" refers to the point where spreadsheets become too complex to be readable or less productive than traditional programming languages (see Figure 3). The "logical limits" of spreadsheets is much lower than Pascal or Modula because spreadsheet programs impose less discipline or structure on their user. "Mechanicalness" refers to two characteristics of computers, rounding errors and iteration. Rounding errors can add up as the programmer for a major financial institute found out when he put all the discarded fractions into his account5. Iteration means that the computer performs operations one at a time and therefore can not perform two operations simultaneously as simulation analysis often requires. 5For example, if you discard everything after the second digit, then you are discarding, on average, a half a cent/transaction. That adds up to $5,000 for every million transactions. As for many spreadsheet programs, they must round any number exceeding 10 significant digits past the decimal place. Concept 16 Additional internal limitations of spreadsheets exist and developers of spread-sheet products are continuously at work to reduce these limitations. Appendix G gives a description of some of their innovative "add-ons" and peripheral packages. External limitations are practical time and monetary limits set by the user. Although users may seldom reach one of the internal limitations of spreadsheets, they may stumble into one of the external limits the first time they use a spreadsheet program. Time or funds could run out while learning to use the spreadsheet program, designing the template, debugging errors, collecting data, entering the data, or using the statistical features of the spreadsheet. Furthermore, it is not uncommon to waste time or funds due to the computer losing power along with the worksheet or the computer breaking down and needing professional servicing. 2.8 CHAPTER SUMMARY A spreadsheet program is a computer language containing a worksheet, func-tions, and sometimes macros. The heart of the spreadsheet is the worksheet, a matrix of cells formed by the intersection of rows and columns. A developer uses a worksheet to create a template which enables users to accomplish a desired task using different data with few or no modifications to the template. The success of spreadsheets and templates has launched a new era in computer programs for the business world. However, spreadsheets and templates are not without their limitations and so users should evaluate their usefulness before employing them. Concept 17 GENERAL General 18 3.0 INTRODUCTION This chapter has five sections. The first three sections discuss respectively, the three general applications of spreadsheets: table production, numerical analysis, and the testing of "what i f scenarios. The fourth section defines the role of spreadsheets in data analysis for planning. The fifth section constructs criteria by which to evaluate a template's structure. 3.1 TABLE PRODUCTION Spreadsheets process numerical data as word processors process text. There-fore, tables and reports that contain more numerical data than text are easier to produce using a spreadsheet than a word processor. Even if the numerical data requires no calculations, the spreadsheet's ca-pacity to format, to manipulate rows and columns, and to ease movement between cells makes them more functional than word processors for data processing. However, if no calculations are required, then a database program might be more suitable than a spreadsheet program. Spread-sheets also facilitate printing blank forms for data collection by ena-bling the user to erase the data in the worksheet and then print the work-sheet with just the column and row headings. Figure 4 gives an ex-ample of a spreadsheet table and graph. F i g u r e 4 : A T a b l e a n d G r a p h A B C D 1 ANALYSIS OF DWELLING U MITS 2 USING 1986 CENSUS DATA 3 4 Census tract Single Low-rise High-rise 5 1 1720 735 440 6 2 525 155 50 7 3 1470. 330 0 8 4 65 4125 860 9 5 195 3105 320 10 6 30 1165 3840 11 7 450 460 300 12 8 1630 200 0 13 9 935 215 0 14 10 355 290 1665 Analysis of Dwelling Units 6,000 5,000 • High-rise m Low-rise • Single 1 2 3 4 5 6 7 8 9 10 Census Tract General 19 3.2 NUMERICAL ANALYSIS Spreadsheets are more than just table generators, they "are extremely versatile, and by far the quickest medium for developing numerical applications" (DJ. Bower and M.D. Abkowitz 1986,2). Ned Levine describes spreadsheets as '"super calcula-tors' with calculating potential somewhere between large statistical packages for mainframe computers and built-in functions on hand calculators "(Levine 1985,509). While some hand calculators do a few things better than spreadsheets1, spread-sheets out perform calculators in the four basic operations of a calculator (addition, subtraction, multiplication, and division) and the arithmetic, trigonometric, statistical, and financial built-in functions listed in Appendix E. Using these 'calculator' capabilities, it takes only a few seconds to sum the rows and columns in Figure 4 and produce Figure 5. Essentially, all the planning applications of calculators are also the Figure 5: The "Calculator" Capabi l i t ies A I B | C D E 1 ANALYSIS OF DWELLING UNITS 2 U S I N G 1986 C E N S U S D A T A 3 !' 4 Census tract { Single Low-rise High-rise Total 5 i f 1 , 7 2 0 7 3 5 4 4 0 2 , 8 9 5 6 2 j 5 2 5 1 5 5 5 0 7 3 0 7 3 | 1 , 4 7 0 3 3 0 0 1 , 8 0 0 8 41 6 5 4 , 1 2 5 8 6 0 5 , 0 5 0 9 5 1 9 5 3,1 0 5 3 2 0 3 , 6 2 0 1 0 6 3 0 1 ,1 6 5 3 , 8 4 0 5 , 0 3 5 1 1 7 | 4 5 0 4 6 0 3 0 0 1 ,.2.10 1 2 a! i , 6 3 0 2 0 0 0 1 , 8 3 0 1 3 91 9 3 5 21 5 0 1 ,1 50 1 4 1 0 | 3 5 5 2 9 0 1 , 6 6 5 2 , 3 1 0 1 5 Total j 7 . 3 7 5 1 0 . 7 8 0 7 . 4 7 5 2 5 . 6 3 0 Total Dwelling Units by Type Total Dwelling Units by Census Tract High-rise 29.17% Single 18.18% detached 28.77% 16.36% 9.09% 10.91% 14.55% 12.73% • 1 m 2 3 m 4 • 5 m 6 m 7 8 m 9 m 10 'The Hewlett-Packard 15C calculator (February 1984) provides some advanced mathematics capabilities not built into Lotus 1-2-3 release 2 (1985) such as complex number calculations, solving for roots, and numerical integration. Both HP-15C and Lotus release 2 have limited matrix calculations. General 20 planning applications of computer-generated spreadsheets. In addition to rivaling the calculator, spreadsheets also compete with some of the large statistical packages on mainframe and personal computers. No longer will statistical functions, hidden from view in a subroutine of the Statistical Package for the Social Sciences (SPSS), magically produce solutions without any apparent mathematical foundation or logic. The formulas and functional relationships of every spreadsheet can be examined and analyzed in detail. (Hudson) Spreadsheets are more user friendly and easier to understand than large statistical packages. Appendix J lists some of the statistical functions of the large packages that have been replicated to some degree on spreadsheets. One of these functions is regression analysis and Figure 6 shows the results of performing regression analysis Figure 6: The "Large Statistical Package" Capabilities F I G -T— Oensus tract 1 O 1 1 1 2 1 3 1 4 1 5 1 6 1 7 H T R E G R E S S I O N A N A L Y S I S : D w e l l i n g T y p e by M o b i l i t y U S I N G 1986 C E N S U S D A T A 1 0 Single Detached 5 9 % 7 2 % 8 2 % 1 % 5 % 1 % 3 7 % 8 9 % 81 % 1 5 % S l o p e of the r e g r e s s i o n l ine : Y - a x i x i n t e r c e p t of t h e l i n e : M O B I L I T Y N O N - M O V E R S Actual 6 2 % 4 5 % 5 5 % 3 0 % 2 6 % 2 5 % 3 6 % 4 9 % 6 3 % 4 4 % 0 . 3 1 9 2 4 2 7 2 9 . 3 5 % Predicted 4 8 % 5 2 % 5 5 % 3 0 % 31 % 3 0 % 41 % 5 8 % 5 5 % 3 4 % R e g r e s s i o n Ana lys is : percentage of Single De tached Dwel l ings by percen tage of N o n - m o v e r s 7 0 % T 6 0 % + 5 0 % Percentage 40% of N o n - M o v e r s 3 0 % + 2 0 % •• 1.0% •• 0% 1 t J 1 • A c t u a l 0 Pred ic ted H 1- -I 3 4 5 6 7 8 C e n s u s Tract 9 1 0 General 21 with Excel on the table in Figure 5. However, large statistical packages are superior to spreadsheets in many ways. Statistical packages on mainframe computers will inherently surpass the spreadsheet/ microcomputer speed and memory limitations. Furthermore the accuracy2 and stability3 of the calculations in a mainframe computer exceeds those in a spreadsheet. With over twenty years of testing and refinement, mainframe routines are more reliable than spreadsheet routines. Compared to microcomputers, mainframe computers have better file backup and retrieval routines, fewer system crashes, and such crashes are not as abrupt, enabling users to save their work. While large statistical packages on microcomputers are prone to the same limitations and lack of refinement as spreadsheets, microcomputer statistical packages provide a variety of goodness of fit and diagnostic tools which are not readily available in spreadsheets. Developers of spreadsheet products should include a variety of goodness of fit and diagnostic tools into spreadsheets. 3.3 TESTING "WHAT IF" SCENARIOS While spreadsheets are excellent table generators and 'super calculators', the real power of the electronic spreadsheet lies in the ease with which "what i f questions can be asked, allowing planners to consider a far broader range of alternatives than they could previously. (Ottensmann 1984, 4) In fact, "If there is one prime reason for most purchasers obtaining a spreadsheet program, it is the capability of seeing instantly the results of [asking a 'what i f question by making] a change in an assumption" (Clark 1983,57). A 'what i f question is a question which the user can easily answer by making simple changes in the data or formulas and observing instantly how the results reflect these changes. In addition to enabling planners to consider a wider range of alternatives, this "what i f capability makes performing sensitivity analysis very easy. 2Microcomputers have fewer significant digits than mainframe computers. 3Microsoft Works will round 9.5 to 10 in the spreadsheet module and to 9 in the database module. Lotus in one instance rounded 9.5 to 9 using the "@round" function and to 10 using the format command. General 22 Examples of' what i f questions include "what if the policy maker wants to use a lower interest rate in evaluating capital projects?" "What if the analysts used the wrong employment figure for the lumber and wood products industry?" "What if the data in last year's population projection needs to be updated with this year's census result?". The user can answer these questions in a fraction of the time it took to develop the worksheet. There is virtually an infinite number of "what i f questions that a spreadsheet can solve. Appendix K explains how 'what i f questions can solve unsolvable equations where it is impossible to isolate the unknown variable on the left side of the equation. Like any tool of society, the user can employ spreadsheets for both constructive and destructive purposes. "The dangers inherent in spreadsheet^  s 'what i f ] prolifera-tion include allowing agencies and politicians to make self serving predictions under the guise of scientific objectivity"(Levine 1985, 510). For example, the decision maker may be asking "what if the interest rate is lower" in order to find a rate which justifies the cost of a proposed highway project. 3.4 ROLE OF SPREADSHEETS IN DATA ANALYSIS FOR PLANNING Figure 7 presents Hightower's flow diagram of data analysis for planning. The decision whether or not to cast spreadsheets for a role in data analysis is made during the "selection of analysis" in the box labeled "research design". The "selection of analysis" can be divided into the "selection of model" and the "selection of tool" as Figure 8 illustrates. Models are simplifications of reality used to improve a planner's understanding of a complex phenomenon. Models may take many forms, including paper drawings, three dimensional sculptures, and mathematical equations. Tools are the instruments used to construct, operate, and perform the computations of the model and may include pencils, chisels, or spreadsheets. If planners select spreadsheets, their role would include portions of the boxes in Hightower's diagram labeled "adapt or develop elements", "data preparation and checking", and "specific findings". General 23 Figure 7: Data Analysis for Planning EVALUATION ACTION POLICY QUESTION: What is the problem? What are our opt ions? What i ssues separate the o p t i o n s ? Classi fy isssus as P r o f e s s i o n a l / P o l i t i c a l R e s e a r c h a b l e I RESEARCH OBJECTIVES and time schedule & budget RESEARCH DESIGN A c c u r a c y , credibi l i ty , etc. Spec i f i ca t ion of Universe Selection of analysis and data techniques I ADAPT OR DEVELOP ELEMENTS Sample Frame Data co l lec t ion tools A n a l y s i s too ls Admin i s t ra t i ve p lan DECISION-TAKING STATEMENT OF POLICY IMPLICATIONS I RESEARCH REPORT OF CONCLUSIONS SECONDARY SOURCES FIELD WORK INTERPRETATION OF RESULTS i SPECIFIC FINDINGS i DATA PREPARATION & CHECKING General 24 FIGURE 8: Selection of Analysis SELECTION OF MODEL Sat isf ies research objectives, time schedule, and budget? Acceptab le level of errors? I SELECTION OF TOOL EVALUATING TOOLS U s e a b l e ? A c c e p t a b l e l im i ta t i ons? t ~ EVALUATING EXISTING APPLICATIONS App l i cab le to model? Accura teand easy to use? The factors influencing the "selection of model" include the research objec-tives, time schedule, and budget as specified in the previous box in Hightower's diagram. Therefore, both the data required by the model and the model itself must be in line with these three factors. The data must have measurement and specification errors which are acceptable to the research objectives. The model must meet the research objectives without exceeding the time and cost allotted. The time and cost of employing the model is partly dependent on the "selection of tool". The model may fail to achieve the research objectives due to specification errors or an accumulation of measurement errors in the model. Specification error is the discrepancies between the model and the reality the model is supposed to represent. Measurement error is the discrepancy between a variable's true value and the value used by the analyst as a result of inaccurate or imprecise reading of the input variable(s). Measurement error accumulates relative to the variable's value and in absolute size with every mathematical operation in the model. The one exception is General 25 P PROBLEM NOT SOLVABLE NO SATISFACTORY TOOL EXISTS addition, which reduces the relative size of the measurement error. (Alonso 1968, p. 249)4 Generally, as models become more complex, the specification errors decline and the measurement errors increase. The more complex the model, in the sense of having more operations of the same kind or more "explosive" operations such as raising to powers, the more the measurement errors cumulate as the data churn through their arithmetic. (Alonso 1968) Therefore, perhaps models of moderate complexity offer the optimal balance between measurement and specification errors. Since spreadsheets are unnecessary for simple models and lack the structure required by highly complex models (see section 2.8), selecting a model of moderate complexity improves the possibility of selecting spreadsheets in the "selection of tool". The "selection of tool" evaluates applicability, time, and cost of using each possible tool to determine which tool is most in line with the model, time schedule, and budget. The applicability consideration is whether the tool has the ability to perform the computations of the model selected. The time considerations include the time it takes to learn how to use the tool, construct the model using the tool, correct errors, enter the data, compute the initial results, compute "what i f results, prepare output (graphs and tables) for reports, and recover from computer malfunctions. The budget considerations include the cost of renting or purchasing the necessary equipment and the cost attached to the possibility of an undetected error in the computations. The "selection of tool" can be broken into two steps: evaluating the usefulness of available tools and evaluating the usefulness of existing applications of the tools. The first step is to evaluate the ability of each tool to perform the computations of the Specification error and the accumulation of measurement error through basic algebraic operations is illustrated with the following example. Assume: Z = X 2 - Y is true (no specification error) X= 10 with a measurement error of ± 1 Y = 16 with a measurement error of ± 5 Then Z = 84 with a measurement error of 21 using Alonso's formula. Thus, a simplier model, Z = X 2= 100,has a smaller measurement error of 20 and a larger specification error of Y (approximately 16). General 26 planning model. Every suitable tool available is evaluated, which may include spreadsheets, traditional programming languages, statistical packages, pencil and paper, and calculators. Spreadsheets are a suitable tool if the response to two questions is positive. First, is it possible to conceptualize the planning model as a two dimensional table? Second, is it possible to enter the model and the data into a worksheet without exceeding the three internal limitations of spreadsheets referred to as "memory", "logical limits" and "mechanicalness" in section 2.8 and the external limitations such as allotted time and funds? The second step, "evaluating the usefulness of existing applications of the tools", is to see if the planner can save some of the time and cost of developing the application. The evaluation of existing applications such as spreadsheet templates includes examining their applicability and structure. Chapters four, five, and six demonstrate the evaluation of a template's applicability and structure. Selecting spreadsheets over other tools is more likely if part of the model is repetitive. The model is repetitive if a number of items in the data are the same, if the model has repetitive operations, if it is useful to ask "what i f questions, or if the planner will use the data and/or model over again in the future. Usually, the time it takes to design and test a template makes them impractical for non-repetitive tasks, but for tasks that are repetitive, they can be invaluable. If spreadsheets are the selected tool, the first task involves learning to use spreadsheets and any appropriate templates, constructing the selected model either from a blank worksheet or by modifying an existing template, and correcting programming errors. These tasks are referred to as "analysis tools" in Hightower's box labeled "adapt or develop elements". Next, planners use spreadsheets as represented by the box labeled "data preparation and checking" to enter the data, check it for validity, and prepare it for computation. Finally, planners use spreadsheets as represented by the box "specific findings" to compute initial and "what i f results and prepare graphs and tables for reports. General 27 3.5 CRITERIA TO EVALUATE A TEMPLATE'S STRUCTURE Once a planner has determined that spreadsheets are a possible tool, the planner should evaluate the usefulness of the available templates to see if development time can be saved. When evaluating a template's usefulness, the planner needs to answer the questions "Is the template applicable to the model selected?", and "How accurate and easy to use is the template?". To answer the latter question requires evaluating the structure of the template. Structure makes a template easier to modify and use and minimizes some types of errors such as erasing vital formulas, misusing macros, and misinterpreting results. The developer introduces structure into a spreadsheet by using good programming style as well as by using some of the features unique to spreadsheets. A well structured template is well FRAMED. FRAMED in the context of this paper refers to the author's acronym for Flexibility, Readability, Advanced spreadsheet features, Modular design, Errors, and Documentation. The acronym represents the eight elements of structured template design in Richard Brail's "Pushing the Spreadsheet Envelope". The acronym also provides the organizational structure for the form in Figure 9 which is used in evaluating the structure of the templates described in the next three chapters. Flexibility allows the parameters of the problem to change without having to drastically change the template. A flexible template enables the user to easily test "what i f scenarios, perform sensitivity analysis, and adapt the template to other problems. Therefore, the developer should use variables instead of constants so if a value changes the user only has to change it in one cell and not in every cell that contains the value. Adding or deleting columns and rows of data should not destroy vital parts of the template. Readability means being easy to understand. Therefore, range and graph names should be descriptive. For example, descriptive range names such as "revenue" and "expenditure" make more sense than ambiguous names such as " X " and " Y " . The template should use range names for clarity in formulas. For instance, "revenue -expenditure" is easier to follow than "B14 - B27". Formulas should not contain General 28 READABILITY Not all of the titles, range, and graph names are descriptive. Descriptive range names should be used more. Not all of the formulas are easy to understand. The purpose of every label and formula is not clear. The format of the values and labels needs improvement. The row and column titles are not always displayed. The worksheet is not broken into screen size pieces. The printout does not fit neatly on the pages. The data, calculations, and results are not separated. Adjustments are not separated ADVANCED SPREADSHEET FEATURES Cell highlighting and protection is not used enough. There are not enough pre-designed graphs. There are not enough macros. MODULAR DESIGN The template is not broken into modules. ERRORS There are programming errors. There are recalculation errors. The template does not adequately flag errors. DOCUMENTATION Template's objectives are not well documented. The template's operation is not well documented. The template's model is not well documented. There are not enough help screens. There is not an adequate worksheet map or table of Contents. General 29 mystical algorithms even if they are a more efficient use of computer time and memory. This may mean breaking a long formula into several smaller formulas. The purpose of every label and formula in the template must be clear. The labels and values should have readable formats. The template should always display row and column titles. The template should utilize "paging" which means the template is divided into appropriate sizes for the screen and printed page. The template should separate data, calculations, results, and adjustments5 to make it easier for users to find their way through the spreadsheet and to decrease the possibility of accidentally entering data or adjustments in the wrong cells. Well structured templates take advantage of the advanced spreadsheet features which are not found in the first generation of spreadsheets or in traditional program-ming languages, such as cell highlighting and protection, pre-designed graphs, and macros. Cell highlighting and protection guides the user when entering data to the appropriate cells and away from the cells containing formulas or documentation. Pre-designed graphs enable the user to display a graph by simply selecting its name. Macros save the user time and reduce the likelihood of errors. Modular design is essentially a "divide and conquer" technique, where each unique task such as inputting the data, performing computations, orprinting the results is a separate module or macro. An example of modular design is dividing the model into two templates, one for computing monthly statistics and a second for computing annual statistics. Another example is dividing one macro into five, an input macro, a calculations macro, a print macro, a file macro, and a menu macro which calls up the other four macros. A modular design enables pieces of the model to be separately built, tested, modified and used again. Separating macros makes each one shorter in length and therefore faster to test, and gives them a hierarchical structure which is easier to understand. s Adjustments are changes by the user to the original data based on assumptions and should be kept separated so observers can quickly see the location, direction, and magnitude of each adjustment. General 30 A well structured template should contain no programming errors, avoid recalculation errors, and try to flag user errors such as entering erroneous data. Programming errors are semantic errors such as referencing the wrong cell and syntax errors6 such as having an unequal number of left and right brackets in an equation. Recalculation errors occur when the user does not realize that the formulas in the template need to be recalculated one or more times before producing the right results. Flagging errors means finding input data or results which fall outside the permissible range of values. And finally, documentation enables the user or someone else to understand the logic behind the template. It is imperative that the developer documents the template's objectives, operations, and model on either the screen or in a printed manual. It is also important to document the variables, formulas, fundamental assumptions, range names, and macros used. With large templates it is also necessary to provide a map or table of contents (see Figure 10) and some form of help on the screen. In most cases, it is better to have too much documentation than not enough. 3.6 CHAPTER SUMMARY Spreadsheet programs can produce tables easier than word processors, perform numerical analysis with capabilities between programmable calculators and large Statistical packages, and test "what i f scenarios in a fraction of the time it took to develop the template, allowing planners to consider a wider range of alternatives and to conduct sensitivity analysis more quickly. These abilities are the reasons for the success of spreadsheets and fit well "with resource-constrained planning and public policy options" (Brail 1984, 56). The role of spreadsheets in data analysis for planning is to perform the computations of planning models. Using spreadsheets involves constructing or modifying a template, entering data, computing initial and "what i f results, and preparing graphs and tables for reports. Spreadsheets compete with other tools such 6Syntax errors are not common in templates since most spreadsheets will not allow the user to enter a formula which contains a syntax error. General 31 Figure 10: Worksheet Map for A i r Quality Template A1 DOCUMENTATION N5 III INPUT AREA I M 1 Z } 012 CALCULATION AREA V41 A17 I COEFFICIENTS l _ 1 O A H— J24 • 1 1 1 1 I I 1 I I I I I I I I A O OUTPUT 043 DATA TABLE II: WIDTH ADJUSTMENTS AREA J69 1 1 1 1 1 11 1 1 1 1 1 1 1 1 A72 DATA TABLE 1: j \ o TRUCK FACTORS 196 as programmable calculators and large statistical packages for this role. Spreadsheets stand a better chance of being selected if the planning model is middle sized, has repetitive elements, and can be conceptualized in a two dimensional table. The possibility of selecting spreadsheets is further improved if using an existing template can save development time. To be of any value, an existing template must be both applicable to the planning model and well structured. Structure enables planners to employ someone else's template with minimal difficulty. A structured template is well FRAMED (flexible, readable, advanced in the use of spreadsheet features, modular, error free, and documented). General 32 DEMOGRAPHIC Demographic 33 4.0 ORGANIZATION OF CHAPTERS 4, 5, AND 6 Of the fifty applications found, nine applications are evaluated. These nine applications group into three general categories: demographic, economic, and trans-portation. A separate chapter discusses each category with each describing three applications. This chapter focuses on the demographic applications and, in particular, population forecasting models. Discussion of each demographic application is broken into three parts: the planning literature, the template, and the evaluation of the template's structure. The planning literature is a review of the literature on the model used in the template in the context of planning and without reference to spreadsheets. The particular aspects of the model discussed include its purpose, limitations, and variations. The template describes the version of the model used in the template, including its input, formulas, output, and limitations. The evaluation of the template's structure examines the structure of the template using the criteria in section 3.5. The template and The evaluation of the template's structure attempt to provide from the illustrative applica-tion insight into the usefulness of spreadsheets. Each of chapters four, five, and six follows this format in the discussion of planning applications and begins with an introductory section which overviews the models. 4.0.1 OVERVIEW OF POPULATION FORECASTING MODELS Planning is by definition future oriented. To plan for the future usually requires some knowledge about the future population. This is why Hightower 1968, Kruecke-berg and Silvers 1974, Baxter and Williams 1978, Chapin and Kaiser 1979, Simmons 1981, Isserman 1984, and Levine 1985 have stressed the immense importance of population forecasts to planning. The substantial importance of population projection to all aspects of planning programs justifies the use of adequate time and resources to produce results that are reliable, flexible enough to reflect the conse-quences of local change, and sufficiently detailed to serve as a basis for the design of specialized local facilities. (Hightower 1968, 51) Demographic 34 Simmons points out that underestimating the population may result in over-crowded local facilities, expensive expansion programs, and decisions being made without adequate time to evaluate, while overestimating the population can induce excess capital costs, redundant employment, and problems in fiscal planning. Forecasting problems are not exclusive to population models. Although this chapter focuses on demographic models, the remaining part of this discussion on forecasting is applicable to many disciplines including economic and transportation. While estimations, projections, and forecasts can be derived using the same model, the terms are not interchangeable. An estimation is an approximation of the past or present population. A projection is a conditional statement about the future population. A forecast is an unconditional statement about the future population, or in other words, it is a prediction of the most likely future population level. The difference between projection and forecast is subtle but significant. Projections, unlike forecasts, can never be wrong, excluding computational errors. For example, the analyst projecting that if the population increases by 1 % per annum then the population in the year 2000 will be 2.3 million, does not claim that the population will increase by 1% per annum or will be 2.3 million in the year 2000. On the other hand, the analyst forecasting the population does claim that the population in the year 2000 will be 2.3 million. Failure to distinguish between the two terms leads planners to mistake unlikely projections as forecasts. Forecasts require the analyst to make assumptions about the future. While assumptions distinguish forecasts from projections, they are frequently the weakest component of the forecast. There are a number of factors which contribute to the weakness of assumptions. Planners often spend such a disproportionate amount of resources on modeling efforts, data acquisition, model buildings, computer program-ming, debugging, updating data, and redoing... that adequate resources are not available for thinking about the future and structural change. (Isserman 1984, 213) Demographic 35 Most assumptions are conservative, adopting past trends over something radically different, since the analyst is less likely to lose respect. Also, assumptions are often biased towards supporting a desirable action. Forecasting is not an exact science. Errors happen when making assumptions, defining the boundaries of the study area, and in using present population figures which are, at best, estimates within plus or minus 5% of the actual population and at worse outdated. The errors increase the further the analyst tries to project into the future. Computers will not rectify the situation since the analyst does the thinking not the computer. . . . The computer is a dumb machine, only as good as the assumptions you put into it. (Berry, April 1986, 48) Rarely is the level of possible error spelled out. Occasionally, analysts provide high, medium, and low forecasts, but without stating their probability. It is hardly comforting to the planner to learn that the community of 500,000 will be between 600,000 and 1.24 Million in the year 2010. Without stating the probability of a forecast, planners may place too much confidence in the forecast Planners are justified in placing confidence in forecasts which are self fulfilling. A forecast which predicts higher population levels, will attract businesses, industries, and subsequently people. The converse occurs with a forecast for a lower population. On the other hand, if the forecast predicts an extremely undesirable state, then policy makers will do everything in their power to prevent the situation. Hence, the quality of forecasts is difficult to verify since there is no way of accounting for the influence the forecast has on the outcome. With forecasts unverifiable, dependent on assump-tions, and not revealing their assumptions or probabilities, it is tempting for analysts "to produce self-serving forecasts which are cloaked in the guise of technical objectivity" (Wachs 1982, 563). Two of the most common types of population projection models are mathemati-cal trend extrapolation and cohort component models (Isserman 1984, 209). A less common population projection method is the residential carrying capacity approach. Demographic 36 Figure 11: Mathematical Trend Extrapolation Models T i m e EXPONENTIAL T i m e MODIFIED EXPONENTIAL ime GOMPERTZ T ime C O M P A R A T I V E T ime RATIO c »- o O Ul C <D o •*-~ »-« ffl o E Q- v> Population of larger reg ion 4.1 MATHEMATICAL TREND EXTRAPOLATION 4.1.1 PLANNING LITERATURE Mathematical trend extrapolation models determine the future population by projecting the past trend to the most recent data. The difference between the models is in their projection of the past trend. The most frequently used models are the linear model, the exponential, the modified exponen-tial, the Gompertz curve, the comparative method, and the ratio method as shown in Figure 11. (Krueckeberg and Silvers 1978, 259) The linear model projects the population along a straight line depicting equal increments of growth per unit of time. The exponential model projects the population along an exponential curve depicting increasing increments of growth per unit of time. The modified exponential model projects the population with declining increments of growth as the popu-lation approaches an upper capacity limit which could be based on all property being (re)developed to the maximum density allowed by the future land use zoning. The Gompertz model projects the population along an "S - curve" bounded by a lower and an upper limit, depicting increments of growth which would initially increase exponentially, but would begin to decrease exponentially as the population approaches the upper capacity limit. The comparative method projects the population based on the trends of a similar but older area while allowing for the time lag. The ratio method assigns a portion of the larger region's forecast to the local area in much the same way as shift-share analyses forecasts local employment from a forecast of regional employment. Demographic 37 The advantage of mathematical extrapolation models is that they require very few resources to produce. The disadvantages are several. The choice of years used to establish the trend can reverse the results. The models' simplistic structure ignores the real causes of population change. Most of the models only work if the past trend is in a single direction, either predominantly growth or decline. Also, the models do not disaggregate the population. Predicting the total population is not always sufficient for the planner. Many planning decisions, especially those concerning transportation, housing, education, health care, child care, and senior citizens, are more sensitive to the shifts in the age structure of the population than to the change in the total population. For instance, when deciding whether to build an elementary school, knowing the number of school aged children in the future is more important than knowing the predicted total population. In addition to disaggregating the population by age, it may be helpful to disaggregate by other demographic variables such as ethnic origin or religion where substantial differences exist in the factors affecting changes of population level. 4.1.2 SMALL AREA POPULATION PROJECTION TEMPLATE Neil Sipe and Robert Hopkins 1984, p. 23 (See Figure 12) This template calculates six projections of the future population of the local areas inside a region using six mathematical trend extrapolation models. Note the word projections. Referring to the template's result as a forecast is a mistake unless the result is the most likely future population level. The data required by the six models include the projected year, the forecasted population for the region, and the population of every local area in 1970 and 1980. The six models are linear, exponential, shift, share, average, and adjusted average. The linear and exponential models are as described in "The Planning Models". The linear model uses a linear extrapolation of the population in each area in 1970 and 1980 to compute the area's population for the projected year. The exponential model uses natural logarithms to produce the area's projected populations. The shift and share Demographic 38 Demographic 39 models are derivatives of the ratio model. The shift model is like the linear model except that the sum of the local areas' projected populations must equal the user's forecasted population for the entire region. The share model distributes the user's forecasted population for the entire region among the local areas based on their share of the region's population in 1980. The average and adjusted average models are a combination of different models. The average model averages the results of the linear, exponential, shift, and share models. The adjusted average is the same as the average model except, like the shift model, the sum of the local areas' projected populations must equal the region's total forecasted population. Equations 4.1.1 to 4.1.6 express the six models algebraically. Each of these equations involves several calculations. The example shown in Figure 12 requires a total of 484 calculations. Since, as shown in section 3.2, numerical analysis is one of the strengths of spreadsheets, there is merit in using spreadsheets to tackle this planning problem. Mathematical Trend Extrapolation Equations v LINEAR = A 1 9 8 0 - A 1970 ( Y e a r _ 1 9 g ( ) ) + A 4 1 1 EXPONENTIAL = N A T U R A L (base e) L O G ^ ^1970 10 ^ ) A1980 4 A - 2 f A 1 9 8 q Year - 1980 A 1 9 8 p A 1 9 7 q n SHIFT = R Y e a r ( ^ R _ + m R _ - ^ — - J 4.1.3 SHARE = R^ggQ ] r ™™ (RYear-Rl980) + A i 9 8 0 4.1.4 A a ^ SHARE + LINEAR + SHIFT + EXPONENTIAL A V E R A G E = ^ 4.1.5 n ^ A V E R A G E ; ADJUSTED A V E R A G E = A V E R A G E 1 = 1 b 4.1.6 R Year Where: Year = Projection year A1970 = Local area's 1970 population -^ 1980 = Local area's 1980 population Ri 970 = Region's 1970 population R19g0 = Region's 1980 population RYear = Region's forecasted population in the projection year n = number of areas Demographic 40 4.1.3 STRUCTURE OF SMALL AREA POPULATION PROJECTION This template has problems with readability and flexibility. In reading this template, it is unclear what the purpose of the first of the two columns under the heading "Exponential" is for. The flexibility problem is one in which this template requires the user to use data specifically for 1970 and 1980. However, in Canada, most population figures are available for 1971 and 1981. Furthermore, the population figures for 1980 are no longer the most recent. Therefore, the template is useful only as a guide for the development of a new template which would be more readable and work with population figures for any year. 4.2 COHORT SURVIVAL MODEL 4.2.1 PLANNING LITERATURE Cohort survival models not only return the total population, but also the predicted population for each cohort (age group by sex) as required to produce Figure 13. The predicted population is the sum of the present population, net natural increase, and net migration. r Figure 13: 1980 Popula t ion Pyramid 75+ 70-74 65-69 60-64 55-59 50-54 45-49 40-44 35-39 30-34 25-29 20-24 15-19 10-14 5-9 0-4 Baby boom generation •+- 4-100 80 (1,000) 60 40 20 Females 20 40 Males 60 80 On the national scale, net natural increase is usually more difficult to compute and larger in size than net migration since national immigration policies control migration. However, on smaller scales, net migration is more difficult to compute and Demographic 41 larger in size than net natural increase. The two ways of calculating Migration are the residual method and complex models. The residual method computes migration levels by subtracting a past population level and natural increase from the present population. Complex models consider the influence of planning policies and economic conditions on migration. However, it is hard to assess the impact of planning policies and economic conditions on migration and even more difficult to foresee changes in planning policies and economic conditions which are governed by volatile markets, uncertain technology, "foot loose" industries, and other unstable factors. Cohort survival models have two important advantages over mathematical trend extrapolation models. First, cohort survival models identify which age groups are changing and the direction and magnitude of the change. Second, these models reveal the interactions among the real causes of population change: mortality, fertility, and migration. For example, even though birth rates are increasing, the model could predict that the number of children born will decline due to the decline in the number of women at the child bearing age. A major disadvantage of cohort survival models is their need for more data and computational effort than mathematical trend extrapolation. In fact, "The substantial amount of computational effort required to do a cohort survival projection may explain, though it cannot excuse, the general neglect of this approach by urban planners" (Hightower 1968,61) Perhaps the neglect of this approach is simply because the loss in expedience out weights the gains in accuracy. Research has demonstrated that sophisticated cohort-component projec-tions . . . are usually no more accurate than simple mathematical extrapolations or ratio techniques. (Levine 1985, 509) Demographic 42 4.2.2 POPULATION PROJECTION TEMPLATE Filipovitch 1985, chapter 3 (see Figure 14) This template uses the cohort survival model to compute a projection. The template requires the user to input, for every five year cohort, the present male and female population, the male and female survival rates, the male and female migration rates, and for appropriate cohorts the birth rate/1,000 females. The output is then the forecasted male and female population for each cohort in five, ten, fifteen, and twenty years from the present. The template allows the user to select any survival, migration and birth rates, including the most recent rates known, rates extrapolated from short or long term trends, rates based on assumptions about changes in technology, public awareness, attitudes, economic performance and public policy, and erroneous rates or rates which deliberately benefit the user. The user can ask "what i f questions by adjusting the selected rates while keeping everything else constant. For example, the user may ask "If the mortality rate tripled for males and doubled for females between the ages of 25 and 40 due to the AIDS virus, what would be the population in the year 2007". However, unless the rates are the most likely to occur, the user must not interpret the results as a forecast. An advantage of this template is that it enables a planner to enter new information when it becomes available. The template then automatically updates the forecast. For instance, using the example shown inFigure 14, when the planner in 1993 replaces the template's forecasted population figures for 1992 with the actual popula-tion figures for 1992, the template automatically updates the forecast for 2007. This procedure is conversant with the notion that planning is a continuous process which requires monitoring and updating. Spreadsheets suit cohort-survival models well because like mathematical trend extrapolation models, cohort-survival models consist of extensive numerical analy-ses. In Population Projection's documentation, Filipovitch states "coupled with the Demographic 43 F i g u r e 1 4 : P o p u l a t i o n P r o j e c t i o n T e m p l a t e Demographic 44 microcomputer [and spreadsheets], composite models can be calculated almost as easily as simple models". (Filipovitch 1985, 3.4) As an aside, Filipovitch notes that spreadsheets are not as well suited for trend-line analysis. Although this was true when Filipovitch wrote the cohort-survival documentation, today some of the new spread-sheet programs, such as Javelin, contain functions that perform trend-line analysis. 4.2.3 STRUCTURE OF POPULATION PROTECTION This template has problems with formats and recalculation errors. The spreadsheet's value format commands can correct the format problems in seconds. However, even with bad formats, this template is far more readable than the cohort-survival Pascal program in Appendix L. The recalculation error occurs because the formulas in the template need to be recalculated one or more times before producing the right results. This is a particular concern to users with older spreadsheet programs, circular references, or the automatic recalculation turned off. Older spreadsheet programs only perform calculations in a columnwise or rowwise order. However, most spreadsheets developed after 1982 perform calculations in a natural order; that is, formulas that are referenced by other formulas are calculated first irrespective of whether they come first in the column or row. Circular references, which occur when formulas directly or indirectly referencing each other, require several iterations of recalculations. Some spreadsheets notify the user if the template contains a circular reference. Automatic recalculation may be turned off to eliminate the recalculation time between cell entries. Possible remedies for recalculation errors include executing the recalculation command repeatedly until the values stop changing, retyping the template on a spreadsheet program which can perform calculations in a natural order, or restructuring the template so the sequence of calculations follows a columnwise or rowwise order. 4.2.4 H A L L E Y TEMPLATE Ned Levine, 1984 (see Figure 15) This template constructs a life expectancy table, an age structure model, and a ten year population projection. The template computes migration using the residual Demographic 45 Figure 15: Hallev Template - A) Columns Showing Input Data Al AW AX EXACT TOTAL TOTAL NUMBER OF NUMBER OF NUMBER OF FEMALES IN EACH AGE NUMBER OF MALES IN EACH AGE AGE NUMBER OF NUMBER OF FEMALES IN MALES IN (insert TOTAL GROUP 10 GROUP 10 INTERVALS (t) FEMALE DEATHS MALE DEATHS EACHAGEGROUP EACH AGE GROUP growth rate YEARSAGO YEARS AGO x to x+n dF(x) dM(x) F(x) M(x) below in AM 1) ( t -10)F(x) (t-10)M(x) -1 1540 1538 116123 114000 GROWTH RATE 1-4 275 275 499650 497650 0.011760 5-9 180 180 669000 666000 10-14 181 181 667371 664000 FEMALE 650000 650000 15-19 791 791 608671 605000 AGE DISCREPANCY 600000 600000 20-24 800 800 577501 573000 0.055428 600000 600000 25-29 857 857 520973 515000 560000 560000 ^ 30-34 970 970 483792 477000 MALE 495000 495000 35-39 1 079 1079 465964 460000 AGE DISCREPANCY 505000 505000 40-44 1100 1 100 445000 439000 0.055800 440000 440000 45-49 2335 2335 . 405000 398000 400000 400000 50-54 2850 2850 391000 384000 TOTAL 380000 380000 55-59 4175 4175 327000 320000 AGE DISCREPANCY 325000 325000 60-64 5107 5107 264840 257000 0.111227 250000 250000 65-69 5874 5874 228745 221000 175000 175000 70-74 6896 6896 137588 125000 85000 85000 75-79 6921 6921 109798 100000 50000 50000 80-84 7881 7881 73199 63000 35000 35000 85+ 9555 9555 48624 38000 25000 25000 59367 59365 7039839 6916650 5575000 5575000 C D E F G Al AW AX B) Sample of the fifty columns in Hallev AC AR BE BG BQ BR BS 1 MALE DIFFERENCE IN MALE ADJUSTMENT OF 2 AVERAGE LIFE AGE PROPORTIONS: EXACT AGE MALE SURVIVAL EXPECTED EXPECTED EXPECTED 3 EXACT EXPECTANCY REAL AND STABLE INTERVALS RATE FOR NEXT TOTAL NUMBER OF NUMBER OF 4 AGE AT BEGINNING POP. WITH GROWTH IN 10 YEARS 10 YEARS POPULATION FEMALES MALES 5 INTERVALS (t) OF INTERVAL RATE OF r TIME (t+10) (no change=1.0000) IN 10 YEARS IN 10 YEARS IN 10 YEARS 6 x to x+n eM(x) pM(x) - p(kLM(x)] x to x+n Adj[sM] TIME (t+10) TIME F(t+10) TIME F(t+10) 7 8 9 0-4 1.0000 1477014 723737 753277 1 0 -1 70.70 -0.0044 5-9 1.0000 1474807 730029 744777 1 1 1 -4 70.66 -0.0088 10-14 1.0000 1257053 632229 624824 1 2 5-9 66.81 0.0007 1 5-19 1.0000 1350218 678668 671550 1 3 10-14 61.90 0.0060 20-24 1.0000 1276466 642346 634120 14 15-19 56.98 0.0029 25-29 1.0000 1122636 566252 556384 1 5 20-24 52.33 0.0037 30-34 1.0000 1116589 564425 552164 1 6 25-29 47.68 0.0004 35-39 1.0000 949811 480702 469109 1 7 30-34 43.06 -0.0002 40-44 1.0000 965206 489290 475916 1 8 35-39 38.47 0.0020 45-49 1 .0000 929489 471789 457700 1 9 40-44 33.90 0.0033 50-54 1.0000 901503 457882 443621 20 45-49 29.29 0.0020 55-59 1.0000 799369 407492 391877 21 50-54 25.08 0.0048 60-64 1.0000 808962 414210 394752 22 55-59 20.93 0.0008 65-69 1.0000 831541 427426 404114 23 60-64 17.17 -0.0023 70-75 1.0000 806633 428692 377941 24 65-69 13.69 -0.0013 75-79 1.0000 944315 502315 442000 25 70-74 10.27 -0.0076 80-84 indeterminate 512752 287752 225000 i 26 75-79 7.75 -0.0033 85 + indeterminate 365553 213553 152000 27 80-84 4.95 0.0012 28 85 + 3.98 0.0000 29 ===== = = = =========== 30 17889914 9118788 8771126 31 32 33 34 C AC AR BE BG BQ BR BS Demographic 46 method previously described. The user inputs for each cohort: the current number of females and males, the current annual number of female and male deaths, and the number of females and males ten years ago. The user must also find, on a trial an error basis, the value for the "growth rate" which minimizes the total age discrepancy. An attraction of this template is that unlike Filipovitch's Population Projection template, this template explicitly differentiates assumptions about the various rates from the most recent rates known. The template calculates the recent rates using data from the last ten years. If the user wants to make assumptions about future changes to the rates, these adjustments to the rates go in a separate column (see Column BQ in Figure 15). With the adjustments made in this manner, an observer quickly sees which of the rates the user has made assumptions about and what the size of these adjustments are. However, the observer still has to consult with the user to learn the reasons for these assumptions. 4.2.5 STRUCTURE OF H A L L E Y At first glance, this fifty column template appears to exemplify "spreadsheet proliferation"; that is, the developer of the template produces columns of numbers because they are easy to calculate with the spreadsheet and not because planners require them. However, the developer has really used the spreadsheet's ability to show the user all the mathematical steps taken towards the end results. If these middle steps are unnecessary, the hidden column feature of the spreadsheet keeps the unwanted columns from appearing on the screen or on the printout. Even with showing all the mathematical steps taken, it is not automatically apparent what the purpose of the "growth rate" is. Furthermore, it would be helpful if a macro performed the tedious task of determining the optimal "growth rate". It would also be helpful if the template included a worksheet map, map macro, or a table of contents to help users find their way through the 50 columns. Demographic 47 4.3 RESIDENTIAL CARRYING CAPACITY MODEL 4.3.1 PLANNING LITERATURE Like the modified exponential model, residential carrying capacity models project the population with declining increments of growth per unit of time as the population approaches an upper capacity limit. The limit must be a controlling factor in the population growth which will not change under the growing pressures of confinement as the population approaches the limits. Limits are set by public policies such as zoning, subdivision control, and septic tank requirements or by nature such as soil characteristics. Hightower (1968, p. 57) confirms the validity of the carrying capacity model and even gives a mathematical function for calculating a trajectory of population approaching a fixed limit. 4.3.2 LAND TEMPLATE Philip B. Herr & Associates, 1986 (see Figure 16) Land predicts the population based on the land's potential development. Land also forecasts employment, school enrollment, water demand, sewage generation and collection, and trip generation. However, Herr warns that his residential carrying capacity model "is not intended to provide basic growth forecasts, but rather to illustrate the difference in growth and its consequences which various land manage-ment actions would make"(1986, p. 1). To improve the reliability of these forecasts, Land's inputs can be fine tuned until the model's population projection equals that of more credible external population forecasts. Land helps planners first predict the consequences and then evaluate the differences between the consequences of various land management actions, such as rezoning land from one district to another, changing the density rules in one or more districts, imposing a growth rate control, acquiring open space, and annexing addi-tional land. Land is a combination of three templates: Land. Comp TN. Comp LR. The first and main template, Land, requires the user to input the following key information to determine the annual number of acres developed and dwelling units Demographic 48 F i g u r e 1 6 : L a n d T e m p l a t e (SO o f the 6 lO r o w s ) I T T H E L A N D M O D E L Version 2. February 13.1 987 Philip B. Herr & Associates, 261 Newbury St.. Boston, MA 021 1 6 {ALT A for table menus} {ALT D to Delete tables} {ALT S to Save file and file extracts) 9 Table # Name Graph name Range name 1 O 1 1 1 Land Use by Zoning District LAND LAND 1 2 2 Zoning Analysis ZONE 1 3 3 Demographic Change Variables DEMOG 1 4 4 Ten-Year Housing Projections T.HOUSE TH 1 5 5 Long Range Housing Projections L_HOUSE LH 1 6 6 Ten-Year Pop & Enroll Projections N_POP TP 1 7 7 Long Range Population Projections L_POP LP 1 8 E- 1 Long Range School Projections L_SCHOOL LS 1 9 J - 1 Ten-Year Employment Projections T_EMPLOY TE 2 O J -2 Long Range Employment Projections L_EMPLOY LE 2 1 W- 1 Water variables WATER 2 2 W-2 Ten-Year Water Projections T_WATER TW 2 3 W-3 Long Range Water Projections L_WATER LW 2 4 S- 1 Sewage Variables SEWER 2 5 S-2 Ten-Year Sewage Projections T_SEWER TSW 2 6 S-3 Long-Range Sewage Projections L-SEWER LSW 2 7 T- 1 Ten-Year Trip Generation T_TRIP TT 2 8 T-2 Long-Range Trip Generation L_TRIP LT 2 9 Ten-Year Summary TEN 3 O Long Range Summary LONG 3 1 3 2 3 3 3 4 3 5 G R O W T H S I M U L A T I O N 3 6 [Com munity] 3 7 [Case name] 3 8 3 9 4 O Base Year: 1 985 4 1 ASSUMPTIONS: [Describe] 4 2 [Describe] 4 3 [Describe] 4 4 [Describe] 4 5 [Describe] 4 7 4 8 5 O 5 1 S 2 5 3 Table 1 LAND USE BY ZONING DISTRICT (acres) [Community] [Case name] 5 6 5 7 District :G'fathered or Total Developed P r U n d e v e I I v a t e Available o p e d Undevelble Public S 8 5 9 6 O 6 1 6 2 6 3 6 6 7 1 7 2 7 3 7 4 7 S 7 S 7 8 7 9 Single-family [District name] [District name] [District name] [District name] [District name] [District name] Multifamlly [District name] [District name] [District name] [District name] Non-residential [District name] [District name] [District name] [District name] [District name] [District name] Total NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA Demographic 49 built: the available undeveloped land, the factors influencing the number of dwelling units built upon the available land, and the constraints upon the number of dwelling units built each year. The template then adds to this vital information several other factors: pertinent ratios, such as population/dwelling unit and number of jobs/ developed acre; estimates concerning changes of ratios over time; and base year data for housing, population, employment, water consumption, and sewage collected. As a result, the template projects at a sketch level the future population, employment, school enrollment by school level, water demand, sewage generation and collection, and auto trip generation, (see the left side of Figure 17) F i g u r e 17: G r a p h s generated w i t h L a n d HOUSING Base Case c -a 120 100 " HOUSING Comparison 1987 2002 2017 2032 2047 Year "O" Base Case Annex ALR S E W A G E C O L L E C T E D Base Case S E W A G E C O L L E C T E D 2 3 ^ Comparison 1987 2047 1987 2002 2017 2032 2047 Year E3 Other E3 I & I • Residential -0" Base Case • • Annex ALR Demographic 50 The user then runs template Comp TN (Ten year Comparisons) or Comp LR (Long range Comparisons) to compare alternative management strategies. Both templates import extracted data files from Land for up to four alternative cases. Figure 17 (right side), produced using Comp LR. compares two cases for a municipality, the base case, and the case of rezoning 7,500 acres of land from the Agricultural Land Reserve. Thus, Comp TN and Comp LR provide an innovative way of looking at the effects of various land management actions upon a community. Instead of focusing in on what the future will be, the templates emphasize the differences between projections. Stating the differences between projections is more fruitful in some planning applications than describing the future. While this technique is profitable, the user must realize that Land suffers from an accumulation of errors. The template's model strings together a number of estimates and weak assumptions so the results are questionable. At best, the model provides projections at a sketch level appropriate to clarifying the differences between land management actions before conducting more detailed analysis appropriate to facility design. At worst, the models are as Herr & Associates describe Land's ten-year trip generation impact measure, merely "illustra-tive, rather than analytically useful" (Herr and Associates 1986, 8). 4.3.3 STRUCTURE OF LAND In addition to being an innovative planning application, Land's three templates demonstrate further techniques concerning user convenience, documentation, error checking, and modular design, which planners may want to incorporate into other templates. In the area of user convenience, these templates include macros that help the user import files, save files, delete unnecessary sections in the template, and move to the different tables in the template. These templates also use cell highlighting and protection to help the user find the cells needing entries and to prevent the user from Demographic 51 making entries in the cells containing key formulas. The most significant user convenience is that these templates contain pre-designed graphics. Pre-designed graphics allow the user to produce graphs like the ones in figure 17 by hitting as few as five keys. These pre-designed graphics are "the real 'product' of the effort [spent developing Land!" (Herr and Associates 1986, 9). In the area of documentation, each of the three templates starts by giving the user a list of the tables found in the template and their corresponding graph names and range names. In addition, the main template encourages the user to write comments about the values entered by providing space in the template for noting assumptions. Similarly, the main template provides spaces in some of the tables for the user to enter values which are not needed in any formula but are helpful in providing context. Despite these efforts, the three templates, and indeed all the other templates described in this thesis, fail to provide enough documentation in the template for a user to learn to operate it without having to read a separate document. In the area of error checking, the main template begins to explore the potential for spreadsheets to find errors. The template performs three primitive checks which the template's documentation refers to as an "error check", a "reasonability check", and a "reality check". These checks catch erroneous values entered by the user. The main template also provides an opportunity for the user to check and modify the template's forecasts by comparing the template's population forecast with up to four other population forecasts imported from outside the template. By separating the operations of the model into separate macros in different templates, Land is a prime example of modular design. The modularization of the model's operations makes it clear to the user what the purpose and outcome of each macro and template is. However, Land fails in the end because it clouds the equations used to obtain the results. Normally, an advantage of spreadsheets is the users ability to quickly see the formula used to obtain a result by moving the cursor to the cell containing the result. Demographic 52 Figure 18: Partial Decipherment of an Equation + C249 / Cell C249 shows the population in the year 2000\ \ but how did the template arrive at this result? / + C 2 4 7 +1141 A 10 + B248 * 1140 A 5 + B 2 4 9 ] + C226 MORE MORE MORE MORE + B226 + C225 * 1139 MORE MORE + C215 * B169 * H 1 3 9 A 5 + C216 * I 1 3 9 A 5 MORE MORE MORE @MIN(B139 , C139 * G 1 6 8 , D139 INPUT X INPUT X INPUT C214 * 1.25 + C221 / F 1 9 0 — r INPUT T + F164 X + D164 +E164 X INPUT X INPUT MORE: additional formulas INPUT: a value inputed by the user +G167 - B 1 6 8 1 MORE +G166 - B 1 6 7 1 MORE + G165 - B166 1 MORE + G164 - B 1 6 5 + G125 * D168 + D167 — 1 ~ MORE + B168 1 MORE @MIN(B139 , C139 * G154 , T INPUT INPUT MORE D139 ~ r ~ INPUT + G100 + G 1 0 8 z r z MORE + G118 — r + G122 MORE INPUT + G123 ~1 INPUT @SUM(G93..G99) D164) MORE However, Land nests the equations so deep as shown in Figures 18 and 19, that it is difficult for the user to figure out how the template arrived at a result. The documentation compounds the problem by not explaining the equations used in the model. So Land is a "black box". Demographic 53 Figure 19: T rack ing Ce l l Dependencies Info: Land.wkl t Cel l : C249 Formula = C247 * 1141 A 10 + B248 * 1140A5 + B249 Precedents [All Levels] C247, B248:B249, B227:B231, C214:C216, B193: C193, B192:E192, B224:C226, C221:C223, F190:H190, G191: H192, B139:1141, D164:H164, B191:C191, B167:E174, B222: B223, G125:H125, B165:C169, G165:H173, D165:E166, G122: H123, G93:H100, G103:H108, G111 :H118, D93:F98, B103: F106, B111:F116, D59:D64, D67:D70, D73:D78 Notes 4.4 CHAPTER SUMMARY Planning often requires population forecasts. Forecasting requires assump-tions. However, models can not make these assumptions: "The model does not do the entire job . . . important decisions must be made outside the model with limited help from the model" (Isserman 1984, 213). Three completely different types of population forecasting models are mathe-matical trend extrapolation, cohort-survival, and residential carrying capacity. There is significant debate over the choice of model to use. In general, simple projection models based on a small number of variables will do as well as more complex schemes. (Simmons 1981,84) Demographic 54 Cohort survival model is not the ultimate answer, but it is the best of those operationally available to planners. (Hightower 1968, 51-2) Research has demonstrated that sophisticated cohort-component projec-tions . . . are usually no more accurate than simple mathematical extrapolations or ratio techniques. (Levine 1985, 509) Simple models suffer from specification errors while complex models suffer from measurement errors. The criteria to consider in selecting a model are that it is reliable, sufficiently detailed, and operational. Reliability is assessed by the record of the model's past performance1 in combination with speculations about changes which may influence the model's future performance. Having enough details depends on the level of disaggregation required. Operational is the ability to employ the model using the resources available to the planner. The four templates evaluated perform the computations for mathematical trend extrapolations, two versions of the cohort-survival model, and a residential carrying capacity model. The templates substantiate the last chapter's claim that spreadsheets are excellent for performing numerical analysis and asking "what i f questions. While spreadsheets in general are excellent, the structure of the templates is not excellent. The use of constants instead of variables in Small Area Population  Projection severely limits its ability. Inadequate formats and recalculation errors plague Population Projection. Users of Hallev need a worksheet map and a better understanding of all the formulas. Land is too much of a black box. However, despite the seriousness of these short-comings, resolving these structural problems takes less time than developing the templates from a blank worksheet. Which in most cases, is poor, according to an empirical study by Simmons (1981). Demographic 55 ECONOMIC Economic 56 5.0 INTRODUCTION One of the most useful areas of application for today's generation of microcomputer-based spreadsheets is in local economic analysis. (Lan-dis 1985, 216) Two types of economic analysis are impact studies and evaluation studies. Impact studies describe the economic, social, and environmental consequences of a project. For example, an impact study of a proposed high tech plant may conclude the plant will cost $5 million, provide 100 new middle class jobs, and cause minimal environmental damage. Proponents of the plant may state "the benefits of 100 new jobs in the region justifies the construction of the plant". However, this one sided statement ignores opportunity costs and thus confuses impact studies with evaluation studies. Evalu-ation studies compare the impacts of a project against the impacts of other alternatives. Continuing the example, $5 million may be better spent providing 200 low cost housing units for the poor. Comparing the relative merits of alternatives requires attaching weights to impacts. Attaching weights to impacts requires knowing the objectives of the decision maker. An important distinction made between impact and evaluation studies by Waters (1976) is that knowing the objectives of the decision maker is essential for evaluation studies butnot impact studies. Nevertheless, knowing the decisionmaker's objectives can avoid wasting time assessing irrelevant impacts. The next two sections, "Economic Base Analysis" and "Shift Share Analysis" are examples of impact studies. The following section "Cost/Benefit Analysis" is an example of an evaluation study. 5.1 ECONOMIC BASE ANALYSIS 5.1.1 PLANNING LITURATURE Economic base analysis estimates the region's total economic activity after a change in the region's base activity. In determining the economic base multiplier, the model separates the economic activity of each industry in the region into two sectors Economic 57 ECONOMIC BASE ANALYSIS EQUATIONS T = B + S 5.1.1 S = | b 5.1.2 T = B + | b 5.1.3 V B 5.1.4 T k=g 5.1.5 T = kB 5.1.6 LQi = ^ 5.1.7 E" bi = 0 5.1.8a b i = (1" Lf^) e i 5 - L 8 b q Ei L Q i = E~ e 5.1.9 Z[ = -g-e 5.1.10 ifZi<=eithenbi = 0 5.1.11a ifZj>eithenbi = ei-Zi 5.1.11b s i = e i - b i 5.1.12 Where: T = total economic activity B = base activity S = service activity k = economic base multiplier LQi = location quotient in industry i q = local employment in industry i e = total local employment E| = national employment in industry i E = total national employment bj = local base activity in industry i s j = local service activity in industry i Economic 58 the base sector, which consists of all economic activity whose output is destined directly and indirectly (through further processing) for con-sumption outside the economy, and 2) the service sector, which produces solely for domestic consumption (Davis).1 Equation 5.1.1 expresses total economic activity as the sum of base activity and service activity. Equation 5.1.6 expresses total economic activity as a function of base activity. To arrive at equation 5.1.6 from 5.1.1 requires several steps. Assume service activity is a proportion of base activity as shown in equation 5.1.2. Substitute equation 5.1.2 into equation 5.1.1 to yield equation 5.1.3. Factor out base activity in equation 5.1.3 to yield equation 5.1.4. Rearrange equation 5.1.4 to yield the economic base multiplier, (1 + S/B) or k as shown in equation 5.1.5. Lastly, substitute equation 5.1.5 into 5.1.4 to yield equation 5.1.6. However, equation 5.1.6 requires data on base activity which is rarely available. Therefore, planners estimate base activity using either a survey or a less expensive technique such as the location quotient approach. Planners usually measure base and service activity by employment since this information is readily available by Standard Industrial Classification (SIC) code. The location quotient approach assumes, for each industry, that any economic activity in the region above the national average must be base. The rational of this assumption is that a region is likely to consume its proportional share of the national production. So a region would export any surplus between the region's production and the region's proportional share of the national production, and import any shortfalls. Equation 5.1.7 expresses the location quotient for industry i , algebraically. If the location quotient for industry i is less than or equal to one, then the base activity of industry i is 0 as shown in equation 5.1.8a. However if the location quotient for industry i is greater than one, then economists use equation 5.1.8b to calculate the base activity of industry i . Summing the base activity for all industries and putting the result into equation 5.1.6 yields the region's total economic activity. 1The literature occasionally refers to base as export, and service as residential, local, or nonbasic Economic 59 Economic base analysis makes many assumptions, each simplifying reality and each therefore becoming a theoretical weakness in the model. The model assumes base activity is the sole source of growth in the economy to the exclusion of government spending and investment. The model assumes the products are homogeneous; there is no cross hauling (simultaneously exporting and importing the same products); that labour productivity patterns are homogeneous; that the nation is self sufficient; that the economic base multiplier is constant; there is homogeneous consumption among employees in different industries; and there is an elastic supply of resources. Economic base analysis using location quotients has been severely criticized by regional economists for its theoretical weaknesses and practical difficulties. Blumenfeld (1955), Isard (1960), and Tiebout (1956) are by now classic theoretical critiques [of economic base analysis], while Greytak (1969), Leigh (1970), and Tiebout (1962) are cited widely as evidence of the inaccuracy of estimates based on location quotients. (Isserman 1977,33) Lewis states in his critique of the economic base model that the various alternatives commonly used at each step in the process of an economic-base analysis . . . generates widely varying estimates of that multiplier... regional scientists [should therefore] turn their attention to other types of models including input-output frameworks and case studies for determining appropriate multipliers. (Lewis 58) Leigh (p. 205) in his study of location quotients concludes "the utility of the method must be heavily qualified" since his empirical evaluation of location quo-tient revealed that industries classified as service, using the location quotient method, actually export between 35 to 84% of their goods. However, despite the negative criticism, with modifications, economic base analysis using location quotients "can be considered a useful method for estimating aggregate impact multipliers" (Isserman 1977, 49) Six of these modifications are described below. (1) Assign all the higher-level government and tourist employment to base employment to overcome the model assuming base activity is the sole source of growth in the economy. Economic 60 (2) Disaggregate the employment data from the two digit to the three - or four - digit Standard Industrial Classification code to minimize the assumption that the products are homogeneous and therefore there is no need for cross hauling. (3) Calculate the ratio of value added to employment for each industry locally and nationally to avoid assuming homogeneous labour productivity patterns. (4) Adjust national employment figures based on the nation's exports and imports to take into consideration that the nation is not self-sufficient. The figure for the industry's national employment should equal the industry's total employment minus its export component plus its import equivalent component. (5) Use a sample of employees' consumption patterns (or income as a surrogate for consumption patterns) to avoid assuming there is a homogeneous consumption among employees in different industries. (6) Adjust the location quotient accordingly to compensate if previous analysis reveals the model consistendy errs by the same amount. 5.1.2 LOCATION QUOTIENT TEMPLATE Neil Sipe and Robert Hopkins 1984, p. 7. (see Figure 20) The template identifies the region's base industries and determines the diversifi-cation in the local economy by dividing the local employment in each industry between the base and service sectors using a simpler location quotient technique than equations 5.1.7 to 5.1.8b. Rearranging equation 5.1.7 yields equation 5.1.9. Simplifying equation 5.1.9 by letting Zi = ei / LQi yields equation 5.1.10. Thus, if Z. <= 1 then the local base activity of industry i is shown in equation 5.1.11a. However, if Z. > 1 then the local base activity of industry i is shown in equation 5.1.11b. Local service activity in industry i is the difference between total local activity in industry i and local base activity in industry i as shown in equation 5.1.12 The next logical step, which this template does not take, is to calculate the economic base multiplier and total economic activity. To achieve this step, the template can be modified based on equations 5.1.5 and 5.1.6. Economic 61 Figure 20: Location Quotient Tempate A | B I D I E F | G I H I , I J U I L |M i N 82 CALCULATION OF LOCATION QUOTIENTS 83 EMPLOYMENT 84 SIC UNITED STATES LOCAL GOVERNMENT FOR LOCAL EXPORT 85 CODE DESCRIPTION EMPLOYMENT % OFTOTAL EMPLOYMENT %OF TOTAL REQUIREMENT EMPLOYMENT 86 87 CONTRACT CONSTRUCTION 88 89 15 GENERAL CONTRACTORS 1173000 1.30 940 2.57 475 465 90 16 HEAVY CONSTRUCTION 926900 1.03 491 1.34 375 116 91 17 SPECIAL TRADE 2246200 2.48 2148 5.87 909 1239 92 ALL OTHERS 0 0.00 0 0.00 0 0 93 94 MANUFACTURING 95 96 20 FOOD AND KINDRED PRODUCTS 1708000 1.89 158 0.43 691 0 97 24 LUMBER AND WOOD PRODUCTS 690500 0.76 384 1.05 279 105 98 27 PRINTING AND PUBUSHING 1252100 1.38 437 1.19 507 0 99 32 STONE, CLAY AND GLASS 662100 0.73 183 0.50 268 0 1 00 35 MACHINERY EXCEPT ELECTRICAL 2494000 2.76 385 1.05 1009 0 101 38 INSTRUMENTS AND RELATED PRODUCTS 711300 0.79 54 0.15 288 0 102 ALL OTHERS 12767000 14.12 2567 7.02 5166 0 1 03 104 TRANSPORTATION & PUBUC UTILITIES 105 106 42 TRUCKING AND WAREHOUSING 1280200 1.42 179 0.49 518 0 107 47 TRANSPORTATION SERVICES 197600 0.22 59 0.16 80 0 108 49 ELECTRIC, GAS & SANITARY SERVICES 827400 0.92 211 0.58 335 0 109 ALL OTHERS 2840800 3.14 1041 2.85 1150 0 110 111 WHOLESALE TRADE 112 113 50 WHOLESALE TRADE - DURABLES 3122000 3.45 948 2.59 1263 0 114 51 WHOLESALE TRADE - NONDURABLES 2153000 2.38 718 1.96 871 0 115 ALL OTHERS 0 0.00 0 0.00 0 0 116 117 RETAILTRADE 118 119 53 GENERAL MERCHANDISE 2244600 2.48 1494 4.08 908 586 120 54 FOOD STORES 2383600 2.64 1787 4.88 965 822 121 55 AUTO DEALERS & SERVICE STATIONS 1688500 1.87 1222 3.34 683 539 122 56 APPAREL AND ACCESSORY STORES 956700 1.06 734 2.01 387 347 123 57 FURNITURE AND HOME FURNISHINGS 606400 0.67 496 1.36 245 251 124 58 EATING AND DRINKING PLACES 4625800 5.12 4282 11.70 1872 2410 125 59 MISCELLANEOUS RETAIL 1912200 2.12 1411 3.86 774 637 126 ALL OTHERS 617400 0.68 493 1.35 250 243 127 128 FINANCE, INSURANCE & REAL ESTATE 129 130 60 BANKING 1570600 1.74 654 1.79 636 18 131 61 CREDIT AGENCIES OTHER THAN BANKS 569800 0.63 340 0.93 231 109 132 63 INSURANCE CARRIERS 1224100 1.35 1387 3.79 495 692 133 65 REAL ESTATE 981000 1.09 734 2.01 397 337 134 ALL OTHERS 700200 0.77 272 0.74 283 0 135 136 SERVICES 137 138 70 HOTELS AND OTHER LODGING 1075800 1.19 751 2.05 435 316 139 72 PERSONAL SERVICES 900700 1.00 598 1.63 364 234 140 73 BUSINESS SERVICES 3092000 3.42 1040 2.84 1251 0 141 75 AUTO REPAIR, SERVICES & GARAGES 570900 0.63 362 0.99 231 131 142 76 MISCELLANEOUS REPAIR 288800 0.32 186 0.51 117 69 143 78 MOTION PICTURES 216900 0.24 104 0.28 88 16 144 79 AMUSEMENT & RECREATION 763500 0.84 278 0.76 309 0 145 80 HEALTH SERVICES 4295900 4.75 3071 8.39 1738 1333 146 81 LEGAL SERVICES 497700 0.55 373 1.02 201 172 147 82 EDUCATION SERVICES 1138200 1.26 270 0.74 461 0 148 83 SOCIAL SERVICES 1134300 1.25 694 1.90 459 235 149 86 MEMBERSHIP ORGANIZATIONS 1539300 1.70 635 1.74 623 12 150 89 MISCELLANEOUS SERVICES 997400 1.10 1114 3.05 404 710 151 ALL OTHERS 35000 0.04 20 0.05 14 6 152 153 154 OTHERS: 155 NONCLASSIFIABLE ESTABLISHMENTS 18727000 20.71 878 2.40 7578 0 156 1 57 TOTAL 90406400 100.00 36583 100.00 12350 Economic 62 As this template illustrates, economic base analysis is ideal for computation by spreadsheets. To calculate the base multiplier, the planner has only to enter the employment by Standard Industrial Classification code for the nation and the local region. To calculate the total employment after a change in the base employment, the planner just inputs the change in the base employment. The template is simple and inexpensive to use. Although, the accuracy of the results is questionable due to the weaknesses of the model used in the template, modifying the model as shown in the "Planning Literature" will improve the accuracy. It should also be noted that to use Location Quotients in Canada requires additional modifications since it contains the American Standard Industrial Codes. 5.1.3 STRUCTURE OF LOCATION QUOTIENT This template has several problems with readability. The values are not well formatted. The row and column titles are not always displayed. The printout does not fit neatly on the pages. Nevertheless, these problems are minor and easily corrected in minutes. 5.2 SHIFT SHARE ANALYSIS 5.2.1 PLANNING LITERATURE Perloff, Dunn, Lampard, and Muth (1960) developed Shift share analysis as a quick and inexpensive method to calculate the components of the local economic condition (Stevens and Moore 1980, 421). In the words of Zimmerman, shift share analysis is a "descriptive device for explaining historical trends in regional employ-ment" (1975,29). For example, Dunn (1960), used the model to provide a historical summary of how and why each state in the U.S. grew the way it did between 1939 and 1954. The model works with a number of economic indicators, such as regional output, value-added, and employment data. However, as with economic base analyses, employment data is the most frequently used since it is readily available by Standard Industrial Classification code. Therefore, the remainder of this section, assumes employment measures economic activity. Economic 63 The first problem facing economists using shift share analyses is the lack of consistency in the use of common terms and equations to describe shift share analyses: "There does not appear to be a standard set of mathematical definitions or terms for the components of regional employment change" (Stevens and Moore, 420). Econo-mists use a number of terms to describe the three components of local employment in an industry, as shown below. REGIONAL SHARE INDUSTRY MIX NATIONAL SHARE competitive share industrial mix national growth differential shift proportional shift economic growth To come to an agreement on a common set of definitions, this thesis will use the terms and equations encouraged by Stevens and Moore (1980) and capitalized above. Shift share analysis is essentially an accounting procedure, where the sum of the three components, regional share, industry mix, and national share, equals the local employment in the industry at the end period of analysis. The regional share is the employment in the industry at the local level which can be attributed to differences between the local area and the nation (or other reference economy). The industry mix is the employment attributed to the national employment in that industry. The national share is the employment attributed to the national employment in all industries. Put in simpler terms, regional share reflects the local area's advantages, industry mix reflects the industry, and national share reflects the national economy. Equations 5.2.1a, 5.2.1b, and 5.2.1c express the three components, algebrai-cally. The local employment in an industry equals the sum of the three components as shown in equation 5.2.2. Combining the national share and industry mix in equation 5.2.2 gives the Regional Proportion (RP) as shown in equation 5.2.3. Thus, the shift share model can be simplified to equation 5.2.4. While shift share analysis was originally developed to describe the components of total employment in an industry, a minor modification to the model by Ashby in 1964 enables the model to describe the components of employment change itself. Subtracting (E.t ) from both sides of equation 5.2.2 yields equation 5.2.5. Redefining Economic 64 SHIFT S H A R E ANALYSIS EQUATIONS I M i = e i t - i ( iST- £1) 5-2Ab N S i = e i t - l E ^ r 5 - 2 - l c ei t = RSi + IMi + NSi 5.2.2 RPi = IMi + NS i = e i t . i 5.2.3 ej t = RSi (shift component) + RPi (share component) 5.2.4 eit-ei t_i = RSi + IMi + NSi-e i t _i 5.2.5 NSi = e i t . i ^ - - l ) 5.2.6 eit - eu-1 = IMi + RSi + NS i 5.2.7 eit" eit-l = RSi (shift component) + RF\ (share component) 5.2.8 Constant Shift: where RPi = 0, thus e^ = RSi 5.2.9 Constant Share: where RSi = 0, thus eit = RPi 5.2.10 eit+1 - eit = R S i + I M i + N S i 5.2.11 Constant growth: q t+l - e i t = R S i + R P i 5.2.12 Constant shift: q t + i - ei t= RSi 5.2.13 Constant share: qt+i - eit = R P i 5.2.14 INTERi = (f i t - fit.i) ^ 5.2.15 INTRAi = (e i t - eit-1) - INTERi 5.2.16 where RSi = regional share in industry i IMi = industry mix in industry i NSi = national share in industry i ei = local employment in industry i Ei = national employment in industry i e = local and employment in all industries E = national employment in all industries t-i = beginning of the analysis period t = end of the analysis period INTERi = the inter-firm component INTRAi = the intra-firm component fi = the number of local firms in industry i Economic 65 the national share component by subtracting (e.t x) from itself yields equation 5.2.6. Substituting equation 5.2.6 into equation 5.2.5 yields equation 5.2.7 The two major components of change, regional proportion and regional shift are displayed algebraically in equation 5.2.8 and graphically using the spreadsheet's graphics module in Figure 21. Figure 21: Regional Proportion and Regional Shift Components 0.06 X! CO C o • i-H W> <L> Pi -0.08 -•Co • Mining • Fishing • Government • Mi istuction • Communication • Finance lufacturing • Trans • Educational • •Health • Business Servk • Agriculture • Accommadation xMtation • Other • Retail • Wholesale edging • Real Estate s -0.16 -0.12 -0.08 -0.04 0.0 0.4 0.8 1.2 1.6 2.0 2.2 Regional Proportion The two extremes of equation 5.2.8 are the constant shift and the constant share. Constant shift implies that changes in local industry employment are solely attributed to the advantages and disadvantages of the local area as shown in equation 5.2.9. Constant share implies that changes in local industry employment are solely attributed to the national growth (decline) in the industry of which the local area receives exactly its proportional share as shown in equation 5.2.10. In 1969, Brown changed t-1 to t and t to t+1 in equation 5.2.7 in order to make economic forecasts. The result is equation 5.2.11. By assuming the continuing stability of the components of change in the future, planners can use shift share analysis to develop and compare alternative employment projections. (Landis 1985, p. 221) Three of these alternative employment projections Economic 66 are constant growth, constant shift, and constant share shown in equations 5.2.12, 5.2.13, and 5.2.14, respectively. The use of shift share analysis in making economic forecasts continues to grow in popularity primarily because the model uses easily accessible data. Stevens and Moore find theoretical faults with the use of shift share analysis as a predictive tool: "It is hard to justify either a constant shift or a constant share assumption on theoretical grounds" (Stevens and Moore, p. 429). Constant shift wrongly assumes the local area's advantages or disadvantages regarding available resources, productivity, access to markets, etc., will continue. Constant share falsely assumes homogeneous consumption and production patterns across the nation. These assumptions can cause grossly inaccurate results. Nevertheless, Stevens and Moore do give some support to the model because of its practicality: "the simplicity of the basic shift share model, and the ease with which it can be applied may provide a pragmatic justification for the use of some assumption of constancy in making quick, rough, but reasonably accurate short run employment projections" (Stevens and Moore, 429). Cina, after testing six models on twenty metropolitan areas found shift share analysis to be "superior overall to the other nonsophisticated techniques" (Cina 1978, 9). However, planners should view Cina's findings with caution for the same reason Stevens and Moore (1980, 423) criticized Brown's results. Both Cina and Brown substituted for national industrial forecasted rates the actual rates and, therefore, Cina and Brown do not add the national forecasting errors to the local forecasting errors. While Alonso (1968, 249) shows how these errors dramatically compound, Cina suggests two errors might cancel each other: "since U.S. forecasting errors could be partially offset by local errors in the opposite direction, the impact of U.S. forecasting errors is not a direct one-to-one relationship" (Cina 1978,10) Some of the literature suggests that the accuracy of the shift share model varies with the circumstances. Zimmerman concludes that the shift share model performs Economic 67 better at forecasting export oriented industry than service oriented industry (Zimmer-man 1975,35). Cina concludes that shift-share analysis yields better results for slower growing metropolitan areas than for faster growing areas (Cina 1978, 9). There is also evidence in the literature to suggest that some variations of the shift share model perform better than others. For example, Zimmerman found his modified shift share model, which uses a polynomial projection of the share component from the past to the present, gave the fewest errors when compared with the variations described in Hellman and Marcus (1970). Hellman and Marcus describe the fixed ratio of employment to population, population constant share, employment constant share, and population weighted employment constant share techniques. Despite the lack of evidence and the contradictions in the literature, most of the economists mentioned in this section conclude with some kind words about shift share analyses: Shift share still seems to have some value in serving its original purpose of making ex post analyses of the components of regional employment change . . . Shift-share is unrivaled in its ability to provide quick, inexpensive, and useful indication of past regional performance and to identify problems which may deserve the attention of public policy-makers or may require further study. (Stevens and Moore 1980, 433). Shift share analysis i s . . . a very useful approach for getting an idea of the causes of recent regional change (Krueckeberg and Silvers 1978, 420). Shift share methods were overall better than other projection techniques confirming the findings of other researchers (Zimmerman 1975, 37) With certain additional modifications . . . the S/S [shift share] method could potentially become even more reliable for forecasting (Cina 1978, 10) Economic 68 5.2.2 E M P L O Y M E N T SHIFTS/SHARES TEMPLATE Neil Sipe and Robert Hopkins 1984, p. 11 (see Figure 22) Figure 22: Employment Shifts/SharesTemplate BI C 1 D I - I f G I ,,,„. r • I "3 r K I L 1 M | N 8 2 C A L C U L A T I O N O F E M P L O Y M E N T S H I F T S / S H A R E S 8 3 8 4 UNITED S T A T E S L O C A L G O V T 8 5 SIC E M P L O Y M E N T E M P L O Y M E N T NATIONAL INDUSTRIAL COMPETIT IVE T O T A L 8 6 C O D E DESCRIPTION 1 9 7 7 1 9 8 0 % C H A N G E 1 9 7 7 1 9 8 0 % C H A N G E G R O W T H MIX S H A R E C H A N G E 8 7 8 8 C O N T R A C T C O N S T R U C T I O N 8 9 9 0 1 5 G E N E R A L C O N T R A C T O R S 1 1 0 8 4 0 0 1 1 7 3 0 0 0 5 . 8 3 8 7 4 9 4 0 7 . 5 5 84 - 3 3 1 5 6 6 9 1 1 6 HEAVY CONSTRUCTION 7 8 7 1 0 0 9 2 6 9 0 0 1 7 . 7 6 3 0 3 491 6 2 . 0 5 2 9 2 5 1 3 4 1 8 8 9 2 1 7 S P E C I A L T R A D E 1 9 5 5 3 0 0 2 2 4 6 2 0 0 1 4 . 8 8 1 4 6 0 2 1 4 8 4 7 . 1 2 1 4 0 7 7 471 6 8 8 9 3 A L L O T H E R S 0 0 0 . 0 0 0 0 0 . 0 0 0 0 0 0 9 4 9 5 M A N U F A C T U R I N G 9 6 9 7 2 0 F O O D A N D KINDRED P R O D U C T S 1 7 1 1 0 0 0 1 7 0 8 0 0 0 • 0 . 1 8 6 4 7 1 5 8 - 7 5 . 5 8 6 2 - 6 3 • 4 8 8 • 4 8 9 9 8 2 4 L U M B E R A N D W O O D P R O D U C T S 7 2 1 9 0 0 6 9 0 5 0 0 - 4 . 3 5 4 8 7 3 8 4 - 2 1 . 1 5 4 7 - 6 8 • 8 2 - 1 0 3 9 9 2 7 PRINTING A N D PUBLISHING 1 1 4 1 4 0 0 1 2 5 2 1 0 0 9 . 7 0 3 5 7 4 3 7 22 .41 34 0 4 5 8 0 1 0 0 3 2 S T O N E CLAY AND G L A S S 6 6 8 7 0 0 6 6 2 1 0 0 - 0 . 9 9 1 7 8 1 8 3 2.81 17 - 1 9 7 5 1 0 1 3 5 MACHINERY E X C E P T ELECTRICAL 2 1 7 4 7 0 0 2 4 9 4 0 0 0 1 4 . 6 8 3 0 0 3 8 5 2 8 . 3 3 2 9 1 5 41 8 5 1 0 2 3 8 INSTRUMENTS A N D R E L A T E D PR0DUQdT66 1 0 0 7 1 1 3 0 0 1 5 . 6 4 0 5 4 0 . 0 0 0 0 0 5 4 1 0 3 A L L O T H E R S 1 2 6 4 9 2 0 0 1 2 7 6 7 0 0 0 0 . 9 3 2 1 6 7 2 5 6 7 1 8 . 4 6 2 0 9 • 1 8 8 3 8 0 4 0 0 1 0 4 1 0 5 T R A N S P O R T A T I O N & P U B U C UTILITIES 1 0 6 1 0 7 4 2 TRUCKING AND W A R E H O U S I N G 1 2 1 9 5 0 0 1 2 8 0 2 0 0 4 . 9 8 1 6 4 1 7 9 9 . 1 5 1 6 - 8 7 1 5 1 0 8 4 7 TRANSPORTATION S E R V I C E S 1 5 5 5 0 0 1 9 7 6 0 0 2 7 . 0 7 0 5 9 0 . 0 0 0 0 0 5 9 1 0 9 4 9 ELECTRIC , G A S S SANITARY S E R V C E S U 5 7 0 0 8 2 7 4 0 0 1 0 . 9 6 1 0 7 211 9 7 . 2 0 1 0 1 9 2 1 0 4 1 1 0 A L L O T H E R S 2 5 9 2 3 0 0 2 8 4 0 8 0 0 9 . 5 9 1 0 2 4 1041 1 .66 9 9 0 -81 1 7 1 1 1 1 1 2 W H O L E S A L E T R A D E 1 1 3 1 1 4 5 0 W H O L E S A L E T R A D E - D U R A B L E S 2 7 1 7 0 0 0 3 1 2 2 0 0 0 14 .91 831 9 4 8 1 4 . 0 8 80 4 4 - 7 1 1 7 1 1 5 51 W H O L E S A L E T R A D E - N O N D U R A B L E 3 9 9 1 0 0 0 2 1 5 3 0 0 0 8 . 1 4 6 9 4 7 1 8 3 . 4 6 67 - 1 0 - 3 2 2 4 1 1 6 A L L O T H E R S 0 0 0 . 0 0 0 0 0 . 0 0 0 0 0 0 1 1 7 1 1 8 RETAIL T R A D E 1 1 9 1 2 0 5 3 G E N E R A L MERCHANDISE 2 2 0 4 3 0 0 2 2 4 4 6 0 0 . 1 .83 1 1 3 0 1 4 9 4 32 .21 1 0 9 - 8 8 3 4 3 3 6 4 1 2 1 5 4 F O O D S T O R E S 2 1 0 6 3 0 0 2 3 8 3 6 0 0 1 3 . 1 7 1 5 9 3 1 7 8 7 1 2 . 1 8 1 5 3 5 6 - 1 6 1 9 4 1 2 2 5 5 A U T O D E A L E R S 4 SERVICE STAT IONS 8 0 0 8 0 0 1 6 8 8 5 0 0 - 6 . 2 4 1 3 5 5 1 2 2 2 - 9 . 8 2 1 3 0 - 2 1 5 - 4 9 • 1 3 3 1 2 3 5 6 A P P A R E L AND A C C E S S O R Y S T O R E S 8 6 9 9 0 0 9 5 6 7 0 0 9 . 9 8 5 8 2 7 3 4 2 6 . 1 2 5 6 2 9 4 1 5 2 1 2 4 5 7 FURNITURE A N D H O M E F U R N I S H I N G S 5 6 2 5 0 0 6 0 6 4 0 0 7 . 8 0 3 7 7 4 9 6 3 1 . 5 6 3 6 - 7 9 0 1 1 9 1 2 5 5 8 EAT ING A N D DRINKING P L A C E S 3 9 4 8 6 0 0 4 6 2 5 8 0 0 1 7 . 1 5 2 8 5 8 4 2 8 2 4 9 . 8 3 2 7 5 2 1 5 9 3 4 1 4 2 4 1 2 6 5 9 M I S C E L L A N E O U S RETAIL 1 7 3 9 7 0 0 1 9 1 2 2 0 0 9 . 9 2 1 1 2 0 1411 2 5 . 9 8 1 0 8 3 1 8 0 291 1 2 7 A L L O T H E R S 5 7 5 8 0 0 6 1 7 4 0 0 7 . 2 2 3 1 5 4 9 3 56 .51 3 0 - 8 1 5 5 1 7 8 1 2 8 1 2 9 F INANCE, I N S U R A N C E * R E A L E S T A T E 1 3 0 1 3 1 6 0 BANKING 1 3 5 6 7 0 0 1 5 7 0 6 0 0 1 5 . 7 7 5 5 5 6 5 4 1 7 . 8 4 5 3 3 4 11 9 9 1 3 2 61 CREDIT A G E N C I E S O T H E R THAN BANK8 7 7 7 0 0 5 6 9 8 0 0 1 9 . 2 8 3 1 0 3 4 0 9 . 6 8 3 0 3 0 - 3 0 3 0 1 3 3 6 3 INSURANCE CARRIERS 1 1 4 0 9 0 0 1 2 2 4 1 0 0 7 . 2 9 7 1 0 1 3 8 7 9 5 . 3 5 6 8 - 1 7 6 2 5 6 7 7 1 3 4 6 5 R E A L E S T A T E 8 1 5 0 0 0 9 8 1 0 0 0 2 0 . 3 7 7 6 5 7 3 4 - 4 . 0 5 74 8 2 • 1 8 7 - 3 1 1 3 5 A L L O T H E R S 6 7 6 7 0 0 7 0 0 2 0 0 3 . 4 7 3 3 0 2 7 2 - 1 7 . 5 8 3 2 - 2 0 - 6 9 - 5 8 1 3 6 1 3 7 S E R V I C E S 1 3 8 1 3 9 7 0 HOTELS AND O T H E R LODGING 9 5 6 1 0 0 1 0 7 5 8 0 0 1 2 . 5 2 5 4 0 751 3 9 . 0 7 52 1 6 1 4 3 211 1 4 0 7 2 P E R S O N A L S E R V I C E S 8 8 8 1 0 0 9 0 0 7 0 0 1 .42 5 1 5 5 9 8 1 6 . 1 2 50 - 4 2 7 6 83 1 4 1 7 3 BUSINESS S E R V I C E S 2 3 5 7 2 0 0 3 0 9 2 0 0 0 3 1 . 1 7 6 2 9 1 0 4 0 6 5 . 3 4 61 1 3 6 2 1 5 411 1 4 2 7 5 A U T O REPAIR, S E R V I C E S 4 G A R A G E S 4 9 7 7 0 0 5 7 0 9 0 0 14 .71 2 5 4 3 6 2 4 2 . 5 2 24 1 3 71 1 0 8 1 4 3 7 6 M I S C E L L A N E O U S REPAIR 2 4 0 7 0 0 2 8 8 8 0 0 1 9 . 9 8 1 2 4 1 8 6 5 0 . 0 0 12 1 3 3 7 6 2 1 4 4 7 8 MOTION P I C T U R E S 2 1 4 0 0 0 2 1 6 9 0 0 1 .36 7 0 1 0 4 4 8 . 5 7 7 - 6 3 3 3 4 1 4 5 7 9 A M U S E M E N T S R E C R E A T I O N 6 6 5 7 0 0 7 6 3 5 0 0 1 4 . 6 9 1 9 7 2 7 8 4 1 . 1 2 1 9 1 0 5 2 81 1 4 6 8 0 H E A L T H S E R V I C E S 4 5 8 3 9 0 0 4 2 9 5 9 0 0 - 6 . 2 8 1 5 9 9 3 0 7 1 9 2 . 0 6 1 5 4 - 2 5 4 1 5 7 2 1 4 7 2 1 4 7 81 L E G A L S E R V I C E S 3 9 3 9 0 0 4 9 7 7 0 0 2 6 . 3 5 2 8 0 3 7 3 33 .21 27 4 7 1 9 9 3 1 4 8 8 2 EDUCATION S E R V I C E S 1 0 3 1 0 0 0 1 1 3 8 2 0 0 1 0 . 4 0 2 9 0 2 7 0 - 6 . 9 0 28 2 - 5 0 - 2 0 1 4 9 8 3 SOCIAL SERVICES 8 5 4 6 0 0 1 1 3 4 3 0 0 3 2 . 7 3 3 8 0 6 9 4 8 2 . 6 3 3 7 8 8 1 9 0 3 1 4 1 5 0 8 6 M E M B E R S H I P ORGANIZATIONS 1 4 9 5 4 0 0 1 5 3 9 3 0 0 2 . 9 4 461 6 3 5 3 7 . 7 4 44 - 3 1 1 6 0 1 7 4 1 51 8 9 M ISCELLANEOUS S E R V I C E S 7 8 6 8 0 0 9 9 7 4 0 0 2 6 . 7 7 7 6 3 1 1 1 4 4 6 . 0 0 7 3 131 1 4 7 351 1 5 2 A L L O T H E R S 0 3 5 0 0 0 0 . 0 0 0 2 0 0 . 0 0 0 0 0 2 0 1 5 3 1 5 4 O T H E R S 1 5 5 781 1 S 6 NONCLASSIF IABLE E S T A B L I S H M E N T B 2 7 7 2 0 0 1 8 7 2 7 0 0 0 1 5 . 0 5 9 7 8 7 8 8 0 5 . 1 5 9 5 7 6 6 1 5 7 1 5 8 T O T A L 8 2 4 7 1 0 0 0 9 0 4 0 6 4 0 0 9 . 6 2 2 7 7 9 2 3 6 5 8 3 3 1 . 6 3 2 6 7 4 - 3 2 6 0 1 6 8791 Economic 69 Shift share analysis requires enough data manipulation to make the use of programmable calculators impractical (Landis 1985, 216).2 In this context, John Landis promotes the use of electronic spreadsheets. Electronic spreadsheets . . . offer an excellent way out of this dilemma. Devoting only minimal time to data entry, planners can use spreadsheets to better study and interpret changes in the local economy. (Landis 1985, 216) This template calculates the components of local change using equation 5.2.7. However, Sipe and Hopkins (1984,11) are inaccurate when they say that the template "can be used to determine why certain industries are strong while others have lost their competitive edge". Spreadsheets are very good at answering questions like "how many", "which ones", and "what i f , but they can not answer "why". What Sipe and Hopkins could have said is "the template's results show how competitive each industry is relative to the other industries and to the same industry in other parts of the country. It is now up to the user to speculate if it is the lower labour productivity, geographical advantages, higher transportation costs, economies of scale, or another factor which explains why certain industries are strong while others have lost their competitive edge". Calculating the components of change is easy, explaining them is difficult: "The question of structural change and some of the other issues raised by various authors create doubts about the correct interpretation of the various shift components" (Stevens and Moore 1980,433). There are a number of ways to improve this template. For example, using the three- and four-digit Standard Industrial Classification code will provide more detailed analysis. Also, the user can experiment with different past years or average together several past years to obtain more accurate information. Furthermore, the user can try variations of the shift share model such as those found in Zimmerman (1975) and Hellman and Marcus (1970). 2Landis also states that "none of the more common mainframe packages (such as SAS [Statistical Analysis System] or SPSS [Statistical Package for the Social Sciences]) are easily adaptable to running a shiftshare program". However, James Heald (1985,515) disputes this point having written an 84 line program in SAS to do a shiftshare analysis. Economic 70 5.2.3 STRUCTURE OF E M P L O Y M E N T SHIFTS/SHARES Sipe and Hopkins developed both this template and Location Quotients. Therefore, it is not surprising that the two templates share the same structural problems. The employment values are not formatted with commas. The row and column titles are not always displayed. The printout does not fit neatly on the page. As in template Location Quotients, these problems are minor and are easily corrected in minutes. 5.2.4 SHIFT SHARE ANALYSIS TEMPLATE Landis, 1985 (see Figure 23) Figure 23: Shift Share Analysis Template (selected columns) A I B I c I D I o I U AA I AH | Al 1 S H I F T - S H A R E A N A L Y S I S 2 J o h n Land i s S C E N E R I O I S C E N E R I O II S C E N E R I O III 3 Modif ied for the C a n a d i a n S tandard Industrial C o d e s E M P L O Y M E N T C H A N G E 4 D U E T O C H A N G E 5 DIVISION L O C A L L O C A L F U T U R E F U T U R E F U T U R E IN T H E F I R M 6 P A S T P R E S E N T G R O W T H G R O W T H G R O W T H N U M B E R S S I Z E 7 8 A : A G R I C U L T U R E 4 5 6 5 0 0 4 8 2 4 5 0 6 0 -1 6 9 B : F I S H I N G 5 1 -1 - 1 0 0 - 4 1 0 C: LOGGING 6 0 0 5 6 0 - 3 7 - 6 4 3 3 1 0 0 -1 4 0 1 1 D : M I N I N G 4 0 0 4 5 0 5 6 3 5 1 9 2 0 3 0 1 2 E : M A N U F A C T U R I N G 3 2 5 3 5 0 2 7 1 0 21 6 1 9 1 3 F: C O N S T R U C T I O N 1 5 9 2 0 0 5 2 4 2 1 0 -1 0 51 1 4 G : T R A N S P O R T A T I O N 5 4 8 5 5 0 2 - 2 4 2 4 - 7 5 7 7 1 5 H : C O M M U N I C A T I O N . 3 6 9 4 0 0 3 4 14 4 6 -1 6 4 7 1 6 I: W H O L E S A L E 2 5 8 3 0 0 4 9 3 4 7 5 4 3 8 1 7 J : R E T A I L 7 5 3 7 7 5 2 3 -1 5 7 2 - 3 3 0 3 5 2 1 8 K: F I N A N C E 1 5 9 1 7 7 2 0 1 2 2 5 2 7 - 9 1 9 L: R E A L E S T A T E 4 5 6 4 9 0 3 7 1 3 9 8 6 0 - 2 6 20 M : B U S I N E S S S E R V I C E 2 5 8 2 2 5 - 2 9 - 4 0 5 - 6 3 3 0 2 1 N : G O V E R N M E N T 4 5 6 4 2 5 - 2 9 - 4 9 - 3 3 - 3 9 8 22 O: E D U C A T I O N A L 3 6 9 3 5 0 - 1 8 - 3 5 1 8 0 - 1 9 23 P : H E A L T H 1 4 7 1 0 0 - 3 2 - 3 7 - 6 0 - 4 7 24 Q: A C C O M M O D A T I O N 7 8 9 9 0 0 1 2 7 8 3 2 0 0 - 1 9 2 3 0 3 2 5 R : O T H E R 3 6 9 4 0 5 4 0 2 0 5 8 8 4 - 4 8 26 27 T O T A L 6 8 7 6 7 1 5 8 2 9 4 - 5 1 3 4 4 1 5 3 5 - 1 2 5 3 In this template, Landis extends the potential of Sipe and Hopkins' template, by providing graphs (see Figure 21), three employment projections, and the inter-firm and intra-firm components of the change in local industry employment. The three employment projections are the results of equations 5.2.12, 5.2.13, and 5.2.14, respectively. The inter-firm component is the employment change attributed to the growth or decline in the number of firms as shown in equation 5.2.15. The intra-firm component is the employment change attributed to the increase or decrease in the average firm size as shown in equation 5.2.16. Economic 71 5.2.5 STRUCTURE OF SHIFT SHARE ANALYSES This template is a cell listing in a magazine article (Landis 1985,219). So the user decides upon formats and cell highlighting when entering the template. The remaining structural aspects are still determined by the developer via the cell listing. Upon entering the template, the only complaint is that Landis abbreviates the titles to the point where they are not descriptive. The user can easily remedy this problem. 5.3 COST/BENEFIT ANALYSIS 5.3.1 PLANNING LITERATURE Planners are constantly determining which course of comparable action pro-vides the most benefits for the costs incurred. Dating back to 1844 (Prest and Turvey 1965, 685), cost/benefit analysis is today the most commonly used method to determine the best course of action (Filipovitch 1985, 2.1). Despite its popularity, "cost-benefit analysis is one of the techniques most prone to misunderstanding and misapplication in the hands of the uninitiated (not to mention the unscrupulous!)" (Williams 1972, 200) To minimize the misunderstanding and misapplication, the planner needs to answer five questions. (Prest and Turvey 1965, pp 686 and 703) 1. Which costs and which benefits are to be included? 2. How are they to be valued? 3. At what interest rate are they to be discounted? 4. What are the relevant constraints? 5. Which decision algorithm will be used? 1. WHICH COSTS AND WHICH BENEFITS ARE TO BE INCLUDED? This enumeration issue can be divided into four categories: boundary, externali-ties, secondary benefits, and project life, (a) Boundary Should the transit authority evaluating a proposed automated rail system include the cost to society of increasing unemployment? Should the Port of Vancouver in its evaluation of a new grain terminal take into consideration the loss of grain Economic 72 shipment in the old terminal, in Prince Rupert's terminal, or in Seattle's terminal? How far the sponsoring body should take the effects of a project into account depends on its mandate. (b) Externalities Externalities include psychic externalities, technological spillovers, and pecu-niary spillovers. (i) Psychic Externalities Psychic externalities are feelings, such as the grief, guilt, and pain associated with a death. Subsets of psychic externalities are non-user benefits which can be split into option values and preservation values. Option values are the effects on persons who value the availability of a resource, such as a park, but never use the resource. Preservation values are the benefits received from the knowledge that an entity, such as a rare bird, exists. The subtle difference between option and preservation value can be illustrated using the example of a symphony. If a person considers hearing the symphony, it is an option value. If a person does not consider hearing the symphony, but is enriched by the knowledge that the city has a symphony, or that others enjoy the symphony, then it is a preservation value. Planners should include psychic externali-ties in cost/benefit analysis. (ii) Technological Spillovers Technological spillovers are "the external effects of their actions in so far as they alter the physical production possibilities of other producers or the satisfactions that consumers can get from given resources" (Prest and Turvey 1965,688). An example of a technological spillover is the cost imposed by a Hydro electric plant on both commercial and recreational fishing. Amajor difference between cost/benefit analysis in the private and public sector is that the public sector should include technological spillovers while the private sector should not. (iii) Pecuniary Spillovers Apecuniary spillover is the change in price of existing assets, or, in other words, the distribution of wealth within the boundaries of the study. An example of a pecuniary spillover is the decrease in fishermen's property value resulting from the Economic 73 new Hydro electric plant. Counting both the fishermen's decline in production possibility and their decline in property value is double counting. Therefore, Prest and Turvey (1965, 690) argue that planners should not include pecuniary spillovers. (c) Secondary Benefits A few economists include and label some pecuniary spillovers as secondary benefits (or costs). Secondary benefits are benefits accruing to third parties. For example, a mining company will profit from a new hydro plant if the plant reduces the cost of electricity or provides the supply of electricity needed to expand operations. Whether to include the mining company's profit as a secondary benefit in the analyses of the hydro plant is a contentious issue. The majority of economists would recommend their exclusion unless the lower market price of electricity fails to reflect marginal social benefits. (d) Project life Planners estimate the life of most projects to be between 30 to 50 years. While it is difficult to estimate the life of a project, the benefits and costs of a project beyond thirty years usually have very little significance in a cost/benefit analysis due to the rate of discount. For instance, $1,000 worth of benefits in thirty years is equal to $15 today with the rate of discount at 15%. Planners should perform sensitivity analysis on the project life selected. If the choice of project life changes the favourability of the project, then the planner should make the decision maker aware of this. 2. HOW A R E THEY TO BE VALUED? Usually, economists assess the value of costs and benefits at the market price for philosophical and practical reasons. However, there are a number of problems with using the market price. Economic 74 Figure 24: Consumer Surplus Price , Maximum People are willing to pay Market price Consumer Surplus Market Value Quantity sold Quantity (a) Consumer Surplus Consumer surplus is the amount people are willing to pay for a commodity above its market price (see Figure 24). Having multiple prices for the same commodity as theater and airlines do, is an attempt by the producer to convert some of the consumer surplus into producer surplus. Cost/benefit analysis done by the public sector should include consumer surplus, but not the private sector. (b) Inflation Estimating all values in either constant dollars or inflated dollars can resolve the problem of inflation. "Most analysts estimate future benefits and costs in constant prices." (Anderson 1977, 92) (c) Relative Prices Analysts should make adjustments for changes in relative prices of costs and benefits. For example, an analyst making a comparison between heating with solar energy or oil, must consider that the cost of oil will increase relative to the cost of collecting solar energy in the future. (d) Non-marginal Changes When a project is large enough to affect the prices, the benefits accruing from the project "cannot be measured by multiplying the additional quantum of output either by the old or the new price" (Prest andTurvey 1965,691) as shown in Figure 25. Figure 25: Non-marginal Changes Economic 75 (e) Market Imperfections Market price values for costs and benefits may be inappropriate due to market imperfections. For example, the benefit to society accruing from a hydro plant will be higher if the plant sells electricity at the competitive market price than at a monopo-listic price since the market price represents the most efficient allocation of resources (see Figure 26). Therefore, in monopolistic situations, the planner should use the price set by the monopoly and not the market. Figure 26: Monopolistic Price Price Monopolistic Market Quantity V ) (f) Collective Goods Market prices do not represent the value of goods which benefit all members of the population, such as fire protection service, mosquitoe spraying programs, and parks. The value of a collective good is equal to the sum of all members of the population's willingness to pay, rather than the highest willingness to pay by a single member. The "free rider" syndrome makes estimating peoples' willingness to pay for collective goods difficult, since individuals tend to understate their willingness to pay for a collective good in the hopes that they will pay less for the same quantity. For Economic 76 example, if planners ask commuters "how high a toll would you pay to cover the construction cost of a proposed highway?", the commuters will tend to understate their willingness to pay in the hopes that they will pay less and the government or other users will pay more. (g) Intangibles (unmarketable outputs) Many costs and benefits are difficult to value in dollar terms. Some costs and benefits (such as the scenic effect of building electricity transmission lines) cannot be quantified, and others, although they can be quantified, cannot be valued in any market sense (e.g., a reduction of lives lost). (Prest and Turvey 1965, 694) (h) Risk and Uncertainty Market price is inappropriate when there is risk or uncertainty. The planner must identify and account for significant risks and uncertainties such as the possibility of technological innovations, demographic swings, a response from competitors, gov-ernment action, changes in relative prices, and other predicaments (strikes, exchange rate changes, etc). For example, the future value of land zoned agriculture and adjacent to residential land should lie between the market prices of agricultural and residential land since the future zoning of the land is uncertain. 3. AT WHAT INTEREST RATE ARE THEY TO BE DISCOUNTED? Comparing a stream of benefits and costs is difficult. Which of the projects in Table II is preferable? Making present and future dollars comparable requires discounting future dollars into present values using equation 5.3.1. Table II: Compar ing a Stream of Benefits Project Net Benefits Year 1 Year 2 Year 3 Year 4 A B $35 -$20 -$5 $35 -$10 $10 $5 $-30 Economic 77 C O S T / B E N E F I T A N A L Y S I S E Q U A T I O N S P V ^ X d _ 5 5 1 ( l + r ) n ° ' : > ' L § > 1 5.5.2 B - C > 0 5.5.3 i > r 5.5.4 b-l-Cl < b2'C2 b n-Cn _ d + i ) + ( l T i ^ - - - ( l + i ) n _ U 5-5-5 b l , - ± 2 — , Pn q C9 Cn (1+i) (1+i)2 *' ' (l+i)n (1+r) (l+r)2'" * (l+r)n where: 5.5.6 | ( b x - c x ) ( l + r ) n - x (l+i)n = b 0 " c 0 5.5.7 PV = present value FY = future value r = discount rate n = the number of time periods B = Present value of all benefits C = Present value of all costs bi,b2,—b n = series of prospective benefits ci,C2,...c n = series of prospective costs i = internal rate of return and is given by equation (5) or (6) Discounting diminishes the relative weight attached to future costs and benefits. The higher the discount rate or the further the costs and benefits are from the present, the greater the diminution. So many economists argue that the desires of future generations are not adequately addressed. On the other hand, discounting minimizes the large measurement errors associated with estimating long range costs and benefits. Discounting involves some rather tedious arithmetic which planners may accomplish using tables or programmable calculators as Anderson (1977, 81) suggests, or using any spreadsheet's built-in present value function as done in Table in . Table HI is the net present values for the two streams of benefits in Table II using different discount Economic 78 Table III: Present Va lue RATE 0.00% 0.05% 0.20% JECT A B $10.00 $10.00 $10.67 $10.94 $11.90 $11.46 PRO A o r B B PREFERABLE A V J rates. As Table HI shows, the answer to "which project is preferable?" depends on the discount rate. The two most common discount rates for public sector investment are the social opportunity cost rate and the social time preference rate. (i) The Social Opportunity Cost Rate The social opportunity cost rate is the opportunity cost of capital in the private sector. However, the cost of capital in the private sector varies from sector to sector. A solution is to use "a weighted average private sector rate of return in which the weights are the proportions of total financing for the project(s) in question which are diverted from each sector of the economy" ("B.C. Environment & Land Use Commit-tee Secretaria" 1977,67). However, using a weighted average can be laborious since each project requires a unique weighted average and identification of the sectors from which funds are diverted may be difficult. (ii) Social Time Preference Rate The social time preference rate is the economist's best guess at a rate which compensates for individuals tendency to underestimate the value of their future consumption and adequately accounts for the welfare of future generations. Since the social time preference rate is not based on the opportunity cost of capital in the private sector, it therefore "implies an inherendy inefficient allocation of resources" ("B.C. Environment & Land Use Committee Secretaria" 1977,64). Although less economi-cally efficient, the social time preference rate is more equitable in theory. Since social time preference rate is not based on anything tangible, it is more difficult to measure than social opportunity cost rate. So, "for practical considerations, analysis must proceed largely on the assumption that economic efficiency is the Economic 79 primary basis for determination of the discount rate". ("B.C. Environment & Land Use Committee Secretaria" 1977,66). Even with this assumption which advocates using the social opportunity cost rate, economists have difficulty in measuring the discount rate. Krutilla, Eckstein, Harberger, and Stockfish, well respected economists, each defended a different rate ranging from 8.7% to 13.5% in testimony before the Joint Economic Committee of the U.S. Congress. Therefore, planners should perform a sensitivity analysis on the discount rate selected. If the analysis reveals that small changes in the discount rate result in major changes in the findings, then the planner should make the decisionmaker aware of this. 4. WHAT A R E THE RELEVANT CONSTRAINTS? Table IV lists constraints which may impose on projects. In some cases, planners should treat constraints as costs, like the cost of over coming the obstacle. 5. WHAT DECISION ALGORITHM WILL BE USED? Three common decision algorithm are benefit-cost ratio, net present value, and internal rate of return as shown algebraically in equations 5.2.2, 5.2.3, and 5.3.4, respectively. A project is selected if these algorithms hold true. The internal rate of return algorithm requires calculating the internal rate of return using either equation 5.3.5 or 5.3.6. The internal rate of return "is that rate of discount which equalizes the present discounted value of benefits and the present discounted value of costs of a project" ("B.C. Environment & Land Use Committee Secretaria" 1977, 77). An advantage of using the internal rate of return decision algorithm with equation 5.3.5 is that the choice of the appropriate rate of discount can be the last step, and therefore left up to the decision maker. Of the three decision algorithms, the internal rate of return is the most difficult to use since it involves the solution of a difficult algebraic equation. There are, however, many computer programs that can solve these equations using an interactive numerical process. (Anderson 1977, 93). Where equations 5.3.5 and 5.3.6 have multiple roots, i will have multiple (the number of roots minus 1) values. A solution is to calculate an average internal rate of Economic 80 Table IV: Constraints on Projects CONSTRAINT CONSEQUENCE EXAMPLE Divisibility project can neither be scaled down or up a proposed bridge can not be reduced to less than the span of the river Mutual exclusivity only one of the projects evaluated can be selected the planner can only select one of the proposed intersection design for a particular location Natural project must obey the laws of nature the planner can not zone unstable terrain for high-rises Distributional project must compensate losers Pareto optimality must be maintained Political project can not exceed a certain level of regret A politician will not support a planner's proposal if there is a strong possibility that the project will jeopardize the politician's chances of being relected. Legal project must be within the law A planner will follow the regulations regarding public participation even if it is more profitable to skip public participation and risk the consequences. Moral project must be within society's and the decision maker's framework of values In the interest of expedience, a planner may find it benefitial to incorporate some the decision maker's values, even at the cost of reducing the plan's potential. Administrative project must be manageable with given human resources hiring additional staff is not permitted Budgetary project can not exceed the funds allotted borrowing funds is not permitted ^ Economic 81 return as shown in equation 5.3.7. Another solution is to employ the spreadsheet's built-in internal rate of return function which has a user's guess at the value as one of its arguments. Table V illustrates the three decision algorithms. ^ — ; N Table V : I l lustration of the Three Decision Algor i thms PROJECT DISCOUNTED (r = 0.1) YEAR 1 YEAR 2 YEAR 3 YEAR 4 BENEFITS COSTS B-C B/C IRR(i) A -$20 $15 $16 $0 $24.42 $18.18 $6.24 1.34 0.34% B -$100 $0 $0 $160 $109.28 $90.91 $18.37 1.20 0.17% C -$45 $351 -$402 $0 $290.08 $342.94 $-52.85 0.85 0.39% > PREFERABLE B A C In Table V, each of the three decision algorithms produces a different ranking of projects. These three decision rules will rank projects consistendy if the costs of all of the projects are the same. Therefore, an analyst may use this fact to get superficial consistency by reducing each project to the same cost and changing benefits propor-tionately. The normalizing procedure for ranking projects requires that projects have a common outiay. Planners should scale projects up or down to a common outlay or else excess funds should be dealt with by assuming that they will be placed in investments in the private sector and there(sic) will earn the social opportunity cost rate of discount". ("B.C. Environment & Land Use Committee Secre-taria" 1977, 79) Table VI illustrates the results of the normalizing procedure for ranking projects. Table VI: Normalizing Procedure for Ranking Projects DISCOUNT g BENEFITS W A $134.30 O B $120.21 £ C $98.91 DISCOUNT COSTS B-C B/C IRR(i) $100.00 $34.29 1.34 34.5% $100.00 $20.21 1.20 17.0% $100.00 -$1.09 0.99 0.1% PREFERABLE A A A Economic 82 MODEL SUMMARY On the surface, cost/benefit analysis is a simple model. However, a deeper look reveals a host of questions. Which costs and which benefits are to be included? How are they to be valued? At what interest rate are they to be discounted? What are the relevant constraints? Which decision algorithm will be used? It is easy to see why cost/ benefit analysis is prone to misunderstanding and misapplication. Nevertheless, "it isn't so bad when you consider the alternative" (Williams 1972,224). In fact, "a major benefit of cost-benefit analysis is the learning process induced by having to justify the value placed on particular hard-to-measure outputs." (Bishop and Cicchetti 1975,124) 5.3.2 BENEFIT/COST ANALYSIS TEMPLATE Filipovitch 1985, chapter 2 (see Figure 27) This template provides a framework for comparing up to four alternative projects, taking into consideration four evaluation measures and the costs of each alternative. The framework enables the user to define the alternatives, evaluation measures, and costs, and to place weights on the evaluation measures. The template uses the "benefits divided by costs" decision algorithm. Although the template's results look good on paper, they are questionable due to a number of weaknesses in the version of the cost benefit analysis model used in the template. The values assigned to the evaluation measures and weights are highly subjective. There is no accounting for benefits or costs over time. The version does not consider constraints or indivisibilities and therefore it is possible to produce ridiculous results such as favouring using 60 ferries in a forty meter crossing or building 1.7 wooden bridges. Finally, the decision algorithm fails when some of the alternatives' costs are zero. These weaknesses give cause to question the template's accuracy and biases. The developer of this template warns the user not to assume the results are accurate: "the measurement error introduced by this estimation process could, in some cases, reverse the conclusions to be drawn" (Filipovitch 1985,2.11). The measurement error Economic 83 Figure 27: Cost/Benefit Analysis Template A B C | D E F 1 COST/BENEFIT ANALYSIS 2 VISICALC T E M P L A T E 1.2 3 4 EVALUATION M EASURES 5 6 7 8 9 1 0 1 1 PROGRAM 1 2 ALTERNATE 1 3 1 4 1 5 1 6 1 7 TABLE O F IMPACTS 1 8 1 9 20 21 WGTS FOR EVAL MSRS COSTS FOR PROGRAMS 22 23 MSR WGT PROGRAM COST 24 25 26 27 28 29 TOTAL BUDGET = 30 31 32 33 SUMMARY OF COSTS AND BEN EFITS 34 n n n n n t t i i H H n n n n n n f i n n n n n n n t i H n n 35 WEIGHTED NO. OF WEIGHTED E F F E C T / 36 PROGRAM UNIT UNITS IN PROGRAM COST 37 ALTERNATE EFFECT BUDGET EFFECT RATIO 38 39 1 0 ERROR ERROR ERROR 40 41 2 0 ERROR ERROR ERROR 42 43 3 0 ERROR ERROR ERROR 44 45 4 0 ERROR ERROR ERROR 46 47 48 ANALYZED BY 49 50 DATE Economic 84 results from the difficulty of estimating the evaluation measures, weights for evalu-ation measures, and costs of alternatives. While analysts are more accurate in estimating costs than benefits, there are many examples of cost over runs in the millions of dollars such as the Coquihalla highway. Smithies (1955) warns the user not to assume the results of cost benefit analysis are unbiased. Judgment plays such an important role in estimation of benefit cost ratios that little significance can be attached to the precise numerical results obtained. In general, spreadsheets have the ability to keep the model's limitations from being recognized and to disguise subjectivity as objectivity. Observing the following three recommendations minimizes these concerns. First, the template should spell out the level of error. The case for using cost-benefit analysis is strengthened, not weakened, if its limitations are openly recognized and indeed emphasized (Prest and Turvey 1965,731). Second, users should modify the template or develop their own template as re-quired by the uniqueness of the problem. Ideally, a cost benefit analysis template should offer the user a sufficient selection of decision algorithms and other vari-ables to accommodate all possible problems. Third, someone other than the spreadsheet user should answer the five questions outlined in "The Planning Model" before the user modifies the template, since spreadsheets make it too tempting for the user to adjust figures in favour of a particular choice. A further recommendation, made possible by the spreadsheet's "what i f capabilities, is to perform a sensitivity analysis on each of the assumptions made. If the analysis reveals that the results are sensitive to one of the assumptions, the user should make that fact known to the decision maker. For example, the planner should inform the decision maker if a minor change in the life expectancy of a project affects which alternative has the greatest net benefit. Economic 85 Figure 28: Template Design ORIGINAL DESIGN M E A S U R E S CO LU > CC LU WEIGHT COST CO LU CC 5.3.3 STRUCTURE OF BENEFIT/COST ANALYSIS TEMPLATE As humans are not perfect, templates are not perfect. The Benefit/Cost Analysis template is no exception. Improving the template's design as shown in Figure 28 can resolve some of these problems. The improved design transposes Table 2 in Figure 28 so the evaluation measures' axis is as it is in Table 1. As a general rule, variables should use the same axis throughout a template. Next, the improved design horizontally aligns tables with program alternatives and vertically aligns tables with evaluation measures. As a rule, align tables with the same variable. Lastly, the improved design places the summary to the right of all other tables. As a rule, when using co-lumnwise calculation, order the tables from left to right. These changes make adding and deleting evaluation measures and program alternatives easier, the template more readable, and recalcula-tion errors extinct. The biggest disadvantage of the improved i t r r j i \ \ \ \ x t / • i X \ \ s X X S f d . \ \ \ \ s s t s * \ S \ \ • X X • \ X \ s '•sty-,' /• x y s * , \ s \ \ f * * * i . \ \ \ \ X X s s * • \ \ \ \ / • s s > \ \ \ \ * * t t * , \ \ \ \ S f * f * , \ \ \ \ * S f f * \ \ \ N co III :RN> U.I 1— 1 1 < S U M M A R Y CO LU > CC LU WEIGHT M E A S U R E S s s s s s s s s s s s s s s \ \ \ \ \ \ Si \ \ \ \ \ \ • s s s s s s ^\ y s s s s s s \ \ \ \ \ \ / \ \ \ \ \ \ • * t f f * f s s s s s s N \ \ \ \ X \ \ \ \ \ • s s s s s s s s s s s s s s \ \ \ \ \ \ \ \ \ \ \ \ \ ' IMPROVED DESIGN COST SUMMARY CO hi > z CC LU A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A A design is that the user may have to print the sum-mary table on a separate page. Economic 86 However, the improved design does not fix everything. The user should add error flag tables to check the validity of data. Since if you "feed the computer garbage, it will merely give garbage back to you" (Filipovitch, chapter 2,11). The documen-tation should elaborate on the template's operation and model. While Filipovitch goes to great length to discuss general models of cost/benefit analysis, he provides no operating instructions, no equations, and very few details about the version of the model used in the template. McTrans spring 87/page 5 5.4 CHAPTER SUMMARY At first glance, economic base analysis, shift share analysis, and cost/benefit analysis seem very simple. However, the user must be aware of the models' limitations and weaknesses. There is great potential for the the abuse or misuse of analytic techniques and models that are poorly understood by the user, inadequately described by the author, theoretically weak, and inappropriate for the intended purpose (Klosterman 1986, 201). Economic 87 If the model has oversimplified the problem, the model is useful only as a preliminary estimate for screening alternatives before conducting a more complex and expensive analysis. If the readily available templates and easily accessible data are inappropriate, Planners should not use them. Using an inappropriate model, template, or data, is like searching in the shallow water for the locker key lost in the deep water. To produce quick results, planners may mistakenly overlook the template's weaknesses: "spreadsheets may allow for faster analysis, but by themselves they do not assure better analysis... spreadsheets show only the numerical results, and not the assumptions behind the numbers" (Landis 1985,222). To assure better analysis, it is important that the analyst understands the assumptions put into the template's model: "the analyst does the thinking not the computer... the computer is a dumb machine, only as good as the assumptions you put into it" (Berry, April 1986,48). The most common weakness in the structure of the templates reviewed in this chapter is their readability. However, the readability problems are usually not serious. Users can quickly remedy the problem. On the whole, the structure of the templates are adequate. Economic 88 TRANSPORTATION 6.0 INTRODUCTION The urban transportation system forms the fabric of the city. In addition to facilitating movement, the transportation system defines the boundaries of communi-ties, influences patterns of growth and regional development, controls the economic viability of the central business district, affects noise and air pollution levels, impacts on annual fuel consumption, and enhances the military's defence position. Transpor-tation planning is therefore a vital activity. Three of the models used in transportation planning are the spatial distribution analysis model, the disaggregate elasticity model, and the route evaluation cost model. The purpose of the first two models, like the population models in chapter 4, is to forecast. The spatial distribution analysis forecasts movement over space. The disaggregate elasticity model forecasts future transit ridership after a change in fares or services. The purpose of the third model, like the economic models in chapter 5, is to assist in impact and evaluation studies. More specifically, the route evaluation cost model assesses the transportation system's performance. 6.1 SPATIAL DISTRIBUTION ANALYSIS 6.1.1 PLANNING LITERATURE Spatial distribution analysis is the study of movement over space based on Newton's law of gravity, which states that physical bodies "are attracted to each other in proportion to size; the force of attraction is weakened by their distance from each other"(Filipovitch, 4.1). Hence, movement is promoted by the pull of attraction and impeded by the friction of distance. The gravity model takes many forms, the simplest being equation 6.1.1. While the simple model determines the attraction between locations, it does not predict the probability of movement between locations. In contrast, the unconstrained probability model shown in equation 6.1.2 does predict the movement from one location to another. However, the unconstrained probability model overestimates movement since it implies there is some movement for every opportunity. In reality, opportunities do Transportation 90 S P A T I A L D I S T R I B U T I O N A N A L Y S I S E Q U A T I O N S SiS j 6.1.1 Sj^P jb Mij = K - L ^ L 6 . L 2 Dib M y = ° i~rT 1 6 1 3 j=i T i j = ; 1 6.1.4 n S ° j f ij-j=l Mij = O i - i r J Ky 6.1.5 i=l where: Ajj = attraction between i and j iandj = zones K = a scaling or adjusting factor Si = size of i which can be expressed in many ways including the number of people, households, or stores Sj = size of j expressed in the same units as Si fij = friction of distance between i and j measured in units of either space or time. My = Movement fromi (origin) to j (destination) of a resource such as automobile trips, book circulations, or income a , b, and c = variable exponents used to fine tune the model Pj = opportunities of j and the unit of measurement can be different than §. For example, Sj may be measured in wage earners, and Dj in the number of jobs. Oi = all movement (total number of trips) originating from zone i Ty = number of trips from zone i to zone j Dj = total number of trips destined to zone j fy-c = "friction factor" measured in travel costs and contains a negative exponent Transportation 91 not always induce movement. For example, if nobody is hungry, no one is likely to go to a restaurant, regardless of the opportunities. The planner can correct this overestimating problem by using the constrained probability model which limits the movement from I to J to some proportion of all movements from I as shown in equation 6.1.3. This equation closely resembles equation 6.1.4, the standard version used in transportation planning applications for more than two decades (Meyer and Miller 1984, 251). While equations 6.1.3 and 6.1.4 insure that the predicted total number of trips from zone i , is equal to the observed total number of trips generated from zone i , the equations fail to insure that the predicted total number of trips to zone j , is equal to the observed total number of trips attracted to zone j . To achieve the second condition requires an iterative balancing procedure which systematically adjusts the exponents until predicted attraction equals observed attraction. In addition to predicting trips between zones, the model lends itself well to a wide range of planning applications. For example, planners use the model to analyze the demand for libraries, hospitals, schools, and other services; determine the "break-point" distance between two attractions; dimension trade areas; estimate the potential number of clients; study aggregate patterns of movement such as shopping patterns; speculate the value of land for housing or other uses; etc. Despite the model's widespread use, it suffers from specification error as Meyer and Miller (1984, 251) point out. Its predictive capabilities are unclear, especially in light of its explicit lack of behavioral assumptions (aside from the recognition that travel distance or time is an important determinant of spatial interaction). Furthermore, the model does not work if any of the distances are zero. The specification error in the model is large (Smith and Hutchinson, 1979) which is evident by the model's dependency on either constants or exponents to correct the error. There is no natural way of determining the value of the exponent on distance in the socio-Transportation 92 economic realm, yet empirically that exponent is rarely two exactly, almost always more than one, and rarely more than four (Hightower). 6.1.2 SPATIAL DISTRIBUTION ANALYSIS TEMPLATE Anthony J. Filipovitch, chapter 4 (see Figure 29) Equation 6.1.5, the model used in this template, is a variant of equation 6.1.3 with two fundamental differences. The first difference is in the adjusting factors. Exponentb is deleted, exponentc is fixed at 2, and the constant K is added. Although there is no evidence, Filipovitch claims that this shuffling of adjusting factors improves the model's ease of use at the cost of adding to the specification error. Figure 29: Spatial Distribution Analysis Template A | B | C | D E F G H I J K 34 RELATIVE ATTRACTIVENESS MATRIX 35 36 37 SITE1 SITE 2 SITE 3 SITE 4 38 NBRH01 ERROR ERROF ERROR ERROR 39 HBRHD2 ERROR ERROF ERROR ERROR 40 NBRHD3 ERROR ERROF ERROR ERROR 41 NBRHD4 ERROR ERROF ERROR ERROR 42 43 IF YOU WANT TO MAKE ADJUSTMENTS TO 44 TO THIS MATRIX, GO TO H50. ADJUSTMENTS TO MATRIX 45 46 47 SPATIAL DISTRIBUTION MATRIX SITE 1 SITE 2 SITE 3 SITE 4 48 NBRHD1 1 1 1 1 49 HBRHD2 1 1 1 1 50 SITE1 SITE 2 SITE 3 SITE 4 NBRHD3 1 1 1 1 51 NBRHD1 ERROR ERROF ERROR ERROR NBRHD4 1 1 1 1 52 HBRHD2 ERROR ERROF ERROR ERROR 53 NBRHD3 ERROR ERROF ERROR ERROR 54 NBRHD4 ERROR ERROF ERROR ERROR 55 56 ERROR ERROF ERROR ERROR 57 58 59 TOTAL IMPACTS 60 61 62 NBRHD1 ERROR 63 HBRHD2 ERROR 64 NBRHD3 ERROR 65 NBRHD4 ERROR 66 67 ANALYZED.BX 68 DATE Transportation 93 The second difference is in the constraint that predicted and observed movement must be equal. Equation 6.1.3 insures that the predicted total movement from i is equal to the observed, and relies on an iterative balancing procedure to insure that the predicted total movement to j is equal to the observed. Equation 6.1.5 does the opposite, insuring that the predicted j equals the observed, and relying on a procedure to insure that the predicted i equals the observed. 6.1.3 STRUCTURE OF SPATIAL DISTRIBUTION ANALYSIS Spatial Distribution Analysis has almost every type of structural problem. However, each of these problems has a ready solution. Vertically aligning the matrix adjustment table with the other tables will eliminate the need to add two columns for every additional column required. Elaborating on the titles, such as replacing "NBRHD" with "Neighborhood", will improve the template's readability. Separating equation 6.1.5 into smaller formulas will also improve readability. Importing this VisiCalc template into a more sophisticated spreadsheet will enable the user to write a macro to perform the iterative balancing procedure. Moving the total impacts table to the left of the spatial distribution matrix table will avoid the recalculation error. Correcting the mistakes in the poorly edited documentation will improve the user's understanding of the template. Klosterman points out some of these mistakes. The monograph... has duplicate and misplaced pages, an incomplete and incorrect table of contents, and no list of figures. There are numerous typographical errors, misspelled words, and grammatical errors in the text. (Klosterman 1986, 200) For example, "H50" in the phrase "If you want to make adjustments to to [sic] this matrix, go to H50" should be G44. Furthermore, Filipovitch needs to expand the documentation, especially in the area of insuring predicted and observed movement is equal. Transportation 94 6.2 DISAGGREGATE ELASTICITY MODELS 6.2.1 PLANNING LITERATURE Transportation planners use demand elasticity models to calculate future demand after a change in the transport system. In a transit system, demand would equate to ridership and a change in the transport system would be either a change in fares or services (routes, frequency, seating capacity, etc.). As the name implies, disaggregate elasticity models are both disaggregate-based and elasticity-based. DISAGGREGATE-BASED MODELS During the late 1950's and 1960's, planners developed multi-stage aggregate models to help forecast future demand. The stages of these models are trip generation, trip distribution (see the spatial distribution analyses section), modal split, and trip assignment. However, these models are plagued with aggregation bias, also referred to as averaging error, and thus "their ability to explain travel patterns seems to be so poor that their continued use in the development of policy advice is difficult to justify" (Hutchinson 1982,64). So, planners began developing disaggregate behavior models in the 1970's., "Disaggregate" in this context means segmenting the market into categories such as fare-type (peak, off peak, zones, elderly, youth, etc.) and service type (Saturday, weekday, peak, off peak, express, CBD routes, etc.) categories. Each category has its own elasticity value in an elasticity-based model. ELASTICITY-BASED MODELS Elasticity is a measure of the sensitivity of demand to changes in the transport system. Elasticity-based models require the analyst to know the variables which affect demand and the elasticity (magnitude and direction of the effect) of each variable. The variables are divided into direct and indirect. Direct variables charac-terize the mode of travel being studied while indirect variables characterize the other modes of travel. For example, in a study of transit ridership, transit fare is a direct variable while cab fare is an indirect variable. Transportation 95 Meyer and Miller (1984, 235) define elasticity as "the rate of change of demand with respect to that variable". Equation 6.2.1 expresses elasticity, algebrai-cally. D I S A G G R E G A T E E L A S T I C I T Y M O D E L E Q U A T I O N S -AD AD _ Percent change in D _ Dp _ Ax  e Dx ~ Percent change in x ~ Ax ~ ^0 ^0 *o GTRTP m = base month ridership x growth factorm 6.2.2 GSTRTP m = GTRIP m x Seasonal factorm 6.2.3 C G S T R I P m = GSTRJLPm - base month ridership 6.2.4 %TRJPf , Fmf-B f \ T R I P m f = - ^ T 1 G S T R I P m ( l + 2E f F m f ( 1 . E g + B ^ l + E f ) ) 6 " 2 " 5 %TRTPf CTRIPmf = T R I P m f j ^ j - i G STRIP m 6.2.6 P A S S m = S + N 6.2.7 C T R I P j m = T R I P j m - G S T R I P m x R j m 6.2.8 CHANGE m = C G S T R I P m + CTRIPmf + P A S S m + C T R T P j m 6.2.9 where: e Dx = m e elasticity of demand with respect to variable "x" D = demand x = variable Do = initial demand xfj = initial value of x AD = change in D Ax = change in x GTRTPm = growth adjusted ridership for the month of m GSTRTP m = Growth and seasonality adjusted ridership during month C G S T R I P m = change in ridership due to growth and seasonality during month m TRTP m f = ridership in fare category f during month m %TRIPf = percentage of trips in fare category f F m f = effective fare which takes into account the fare phase-in factors for month m in fare category f Ef = elasticity in fare category f Bf = base month fare in category f CTRIPmf = Change in ridership due to the fare variation for month m in fare category f P A S S m = Change in the number of pass users trip during month m S = the number of trips by transit users switched to pass users category N = the number of trips by new transit users using passes. CTRIPjm = Change in the ridership of service category j during month m R j m = ridership in the service type category j in the month m C H A N G E m = Total change in ridership during month m Transportation 96 An absolute elasticity value greater than 1 is "elastic", that is, a 1% change in the variable results in a greater than 1% change in demand. Similarly an absolute elasticity value less than 1 is "inelastic". If the value is exactly 1, it has unit elasticity. Table VII gives examples of the elasticities computed from a number of transit studies. Three points worth noting from the examples are that transit demand is inelastic, that service elasticities are higher than fare elasticities, and that elas-ticities vary between categories. Also, the more discretionary the trip, the higher the elasticity. For instance, shopping trips have higher elasticities than work trips. Similarly, trips by upper income groups have higher elasticities than trips by lower income groups. Elasticity based models make a couple of assumptions. The first assump-tion is that all variables except the one being studied remain constant. This as-sumption is acceptable for short term Table VII: Example Transit Elasticities source: Mayworm, Lago, and McEnroe 1980. Aggregate fare elasticity -0.28 DISAGGREGATE FARE ELASTICITIES Trip purpose: Work School Shop Income group: Less than $5,000 $5,000 to $14,999 $15,000 or more -0.10 -0.19 -0.23 -0.19 -0.25 -0.28 DISAGGREGATE SERVICE ELASTICITIES Bus Headway (time between arrivals): Peak -0.37 Off-peak -0.46 Vehicle-miles (total miles traveled by all vehicles for a period of time): Peak +0.33 Off-peak +0.63 predictions, but other variables are apt to change in long term predictions. The second assumption is that elasticities remain constant. This assumption is acceptable for incremental changes, but elasticities are apt to vary for large changes. One of the practical difficulties of disaggregate elasticity models is the cost of calibrating the elasticities for each variable by each category. The most readily usable method for computing elasticities is the quasi-experimental approach, in which planners alter fares or services under relatively controlled conditions and monitor changes to ridership. (Meyer and Miller 1984,236) If resources are not available to Transportation 97 gather the necessary data to compute local elasticities, planners may use available data from other jurisdictions but at the price of reducing the accuracy of the results. On the whole, "elasticity models are extremely useful in analyzing incremental system changes, particularly when limited data and time are available for the analysis" (Meyer and Miller 1984, 237). However, Hutchinson (1982, 65) criticizes demand elasticity models since they predict changes in ridership based on changes to the transport system and not to demographic changes such as an older population, new employment areas, and different life styles. 6.2.2 DISAGGREGATE ELASTICITY MODEL TEMPLATE Technology Research and Analysis Corporation, 1984 (see Figure 30) The input into the template includes base month ridership, annual nominal growth factor, seasonal factors, fares, average trips per month, percentage of transit users in each category, percentage of pass users in each category, number and definition of service units, number of transit trips per service unit, elasticities for each category, fare phase-in factors, service phase-in factors, pass penetration curve data (pass/cash user ratio for given fare savings), and inflation index. The model assumes the elasticity values are constant over time and for all changes. Furthermore, in calculating the effect on ridership caused by a change in one factor, the model assumes the other influencing factors remain constant. The model used in the template consists of five steps. Step one is estimating the effect of growth and seasonality as shown in equations 6.2.2,6.2.3, and 6.2.4. Step two is estimating the impact of fare change using equations 6.2.5 and 6.2.6. Step three is predicting pass users market share using equation 6.2.7. Step four is estimating the impact of service change using equation 6.2.8. Step five is adding the results of the last four steps to arrive at the system-wide monthly ridership forecast as shown in equation 6.2.9. These five steps are duplicated with minor variations for forecasting the total change in fare revenue during month m. Transportation 98 Figure 30: Disaggregate Elasticity Model Template I I B H 1 0 1 1 1 2 13 1 4 1 5 1 6 1 7 18 1 9 20 21 22 23 24 25 26 27 28 2 9 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 D I S A G G R E G A T E E L A S T I C I T Y ( D E L ) T R A N S I T R I D E R S H I P / R E V E N U E M O D E L VERSION DATE: DECEMBER 21, 1984 Prepared by: Sponsored by: Technology Research and Analysis Corporation (TRAAC) 2020 14th Street North Arlington, VA 22201 U.S. DOT/UMTA Office of Management, Research and Transit Services Washington, D.C. 20590 NOTES: To set initial forecast month, hold down "Alt" key and press M To display a graph, hold down "Alt" key and press G (press the "Esc" key three times after viewing graph) F A R E SYSTEM-WIDE FARE ELAS (IF FACTORS USED): CL# DESCRIPTION 1 . X X X BASE MO % TOT TRIPS CAN USE PASS? (1=YES 0=NO) 100 X X X FARE (CONT.) CL# DESCRIPTION 3. xxx BASE MO % TOT TRIPS CAN USE PASS? (1=YES 0=NO) ELASTICITY -ABSOLUTE -FACTOR -EFFECTIVE -0.33 0.00 -0.33 0.00 0.00 0.00 ELASTICITY -ABSOLUTE -FACTOR -EFFECTIVE Transportation 6.2.3 STRUCTURE OF DISAGGREGATE ELASTICITY MODEL Despite being written nearly three years ago, Disaggregate Elasticity Model is the best structured template evaluated. It features paging (see Figure 31), ten pre-designed graphs (see Figure 32), a macro enabling the user to select the initial month, and an error flags table (see Figure 33). The template's written documentation includes a 42 page chapter titled "Model Description", a twelve page chapter called "Running the Model", and seven appendices. A second template provides on-line documentation such as a table of contents, eleven reference tables, and built-in notes (see Figure 34). R O W 1 C O L U M N Figure 31: Example of "Paging" 1 80 fPage 1-1 TITLE P a g e 2-1 INPUT BY F A R E C A T E G O R I E S P a g e 1-2 G R O W T H & S E A S O A L I T Y I N P U T / O U T P U T " V p a g e 1-3 A B ^ N v ^ P a g e 1-4 A K INFLATION INPUT & G R . & S E A S O N A L I T Y IMPACT O U T P U T e 3-1 P a g e 4-1 P a g e 2-2 POLICY INPUT & F A R E I M P A C T O U T P U T P a g e 3-2 P a g e 5-1 P a g e 4-2 P a g e 2-3 POLICY INPUT & F A R E IMPACT O U T P U T > l P a g e 3-3 INPUT P A S S U S E D A T A >i P a g e 5-2 POLICY INPUT & C / P RATIO ESTIMATION P a g e 4-3 G R O W T H & S E A S O N A L I T Y I M P A C T O U T P U T P a g e 2-4 POLICY INPUT & F A R E I M P A C T O U T P U T < P a g e 3-4 P a g e 6-1 INPUT N E W P A S S D A T A P a g e 5-3 POLICY INPUT & C / P RATIO ESTIMATION P a g e 4-4 P a g e 6-2 P a g e 5-4 POLICY INPUT & C / P RATIO ESTIMATION < P A S S PRICE IMPACT O U T P U T P a g e 7-1 INPUT BY S E R V I C E T Y P E P a g e 6-3 P A S S PRICE IMPACT O U T P U T P a g e 8-1 P a g e 7-2 POLICY INPUT & S E R V I C E IMPACT O U T P U T P a g e 6-4 P A S S PRICE I M P A C T O U T P U T P a g e 9-1 E R R O R F L A G S P a g e 8-2 P a g e 7-3 POLICY INPUT & S E R V I C E I M P A C T O U T P U T <6> P a g e 9-2 SYSTEMWIDE O U T P U T P a g e 8-3 P a g e 7-4 POLICY INPUT & S E R V I C E I M P A C T O U T P U T P a g e 9-3 P a g e 8-4 SYSTEMWIDE O U T P U T P a g e 9-4 SYSTEMWIDE O U T P U T -< Transportation 100 Figure 32: Graph Generated with Disaggregate Elasticity Model GROWTH, SEASONALITY, FARE & SERVICE MAY JUN JUL AUG SEP OCT NOV DEC J A N FEB MAR APR MONTH While the template is exemplary, there is still room for improvement, especially in the area of macros. The template's macros are poorly structured, containing an excessive number of " / X G " ("goto") statements. The graph macro's branches should end with " {esc} {esc}" so the user can return from viewing a graph by pressing any key Figure 33: E r ro r Flag Table A | B | C | D | E F G H 161 Page 9-1 162 E R R O R F L A G S FLAG 1 63 (SHOULD 1 64 TEST B E : 0) 1 65 1 66 SEASONAL FACTORS MUST ADD TO 12.0: I o 1 67 | | 168 BASE MO % OF TOT TRIPS SUMMED | | 169 OVER FARE C L A S S E S MUST BE 100: I o | 1 70 j | 171 % OF USERS IN FREQUENCY CLASSES | | 1 72 MUST S U M TO 100: I o | 1 73 I | 1 74 BASE MO % OF TOTAL SERV UNITS SUMMED | 1 75 OVER SERVICE C L A S S E S MUST BE 100: | ERROR | 1 76 | | 1 77 BASE MO % OF TOT TRIPS SUMMED OVER I | 1 78 SERVICE C L A S S E S MUST BE 100: I o | 1 79 Transportation 101 Figure 34: On-line Documentation A) Table of Contents I I I I I 10 11 12 13 14 15 16 17 18 19 20 TRANSIT FARE AND SERVICE ELASTICITIES T AB - -> TABLE OF CONTENTS P G | DN | V TABLE OF CONTENTS NOTES F-1 FARE AGGRE VALUES S-1 SERVICE BUS SUMMARY F-2 FARE AGGRE -TIME -MODE S - 2 SERVICE VEHICLE MILES -TIME -MODE F-3 FARE BUS DISAGG - P O P -TIME + - • S - 3 HEADWAY BUS DISAGG -TIME F-4 FARE DISAGG -MODE -TIME - P U R P S-4 HEADWAY COMMUT RAIL SUMMARY F-5 FARE DISAGG -LENGTH -RT T Y P S - 5 HEADWAY COMMUT RAIL DISAGG -T IME + F - 6 F A R E D I S A G G - C A P T I V - INCOME - A G E + - + -HOLD DOWN "ALT" KEY AND P R E S S "D" TO GET SELECTION MENU B) Built-in Notes I B 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 NOTES: 1. Adjustment Factors: Adjustment factors are included to give the analyst the option of computing his own Disaggregate Elasticities for individual rider market subgroups. The adjustment factors are simply multiplied by an aggregate elasticity supplied by the analyst. The adjustment factor of 1.00 is shown for the aggregate elasticity corresponding to each type of service variable. 2. Approaches to Elasticity Estimation: Elasticity values in all tables except S-2 are estimated based on analyses of observed responses to changes in current fares and services at various transit properties. Table S-2 shows elasticity estimates derived from time-series analysis of historical patronage data using least-squares regression. 3. Source of Data: "Patronage Impacts of Changes in Transit Fares and Services," by Mayworm P., et a l , Ecosometrics, Inc., sponsored by Office of Ser-vice & Methods Demonstrations, DQT/UMTA, PB 81-167-652, Sept.1980. instead of having to "press the {Esc} key three times after viewing graph" as warned by the opening screen. Lastly, adding an input macro would speed the process of entering data by guiding the user to the appropriate cells. Transportation 102 6.3 ROUTE EVALUATION COST MODEL 6.3.1 PLANNING LITERATURE Evaluation is the process of determining the desirability of different courses of action and presenting this information to decision makers in a comprehensible and useful form. (Meyer and Miller 1984,180) The evaluation process does not make the final decision, but rather provides enough information from a measures of effectiveness analysis and a comparative assessment, for the decision maker to make the decision. The measures of effective-ness analysis evaluates the alternatives against the goals and objectives of the decision maker. Comparative assessment, such as cost benefit analysis, rates the alternatives against each other by reducing the measures of effectiveness to a single dimension such as dollars. A lack of measures of effectiveness analysis means the decision maker is ignorant of the selected alternative's consequences. Alack of comparative assessment makes it difficult for the decision maker to select an alternative. "Measures of effectiveness" and "comparative assessment" are equivalent to "impact studies" and "evaluation studies" respectively, as described in the Economic chapter with the exception that measures of effectiveness, unlike impact studies, must relate to the decision maker's objectives. This section will concentrate on measures of effective-ness since the cost/benefit analysis section adequately describes comparative assess-ment. Measures of effectiveness are the criteria used to evaluate the alternatives. Before the late 1960's, the only criterion was "which alternative maximizes the monetary benefits returned for the costs incurred?". Today, the measures of effective-ness have broadened to include consequences which are difficult to quantify and even more difficult to measure in monetary terms, such as impact on air quality, community cohesion, energy consumption, equitable distribution of resources, and economic development. A measure of effectiveness should exhibit the following six major characteris-tics (Meyer and Miller 1984, 378-9): Transportation 103 1. Relate to objectives. The measure should relate to the objectives of the decision maker. 2. Measurable within budgetary con-straints. The cost of obtaining and analyzing the measure's value should not exceed the benefit of the information to the evaluation. 3. Unbiased. The measure should not inherently favour one alternative over another. For example, "ve-hicle miles" may be a more bi-ased measure than "passenger miles" when comparing bus and automobile alternatives. 4. Sensitive. The measure must be sensitive to differences between the alternatives so the measure will have different values for different alternatives. 5. Understandable. The measure's value must be meaningful to the decision maker. 6. Necessary. The benefit of the infor-mation must outweigh the cost of managing the information. Pro-viding too much information can be as ineffective as providing too litde. These characteristics dictate that the measures of effectiveness will vary between evaluations since evaluations have different objectives and budgetary constraints. Table VIII lists the measures of effectiveness proposed by planners in the 1975 Chicago Area Transportation Study. Table VIII: Chicago Example of Measures of Effectiveness Transportation System Performance Average trip time (by mode, trip type, and income) Average trip speed (by mode and trip type) Peak-period corridor and link volume/capacity ratios Percent of population within 10 minutes walking time of an entry point of a transit system with headways of 15 minutes or better (also computed for 30- and 60-rninute headways) Vehicle miles of travel within various volume/capacity ratio ranges Mode split for entire region and subareas Social and Neighborhood Impact Estimated monetary residential relocation costs Number of relocated households Number of relocated community facilities Number of historic sites taken Economic Impact Number of jobs relocated or eliminated (by income and area) Number of commercial establishments relocated Tax base removed Man-years of construction employment Environmental Impact Noise levels (by geographic area) Air pollution emissions Area exceeding air quality standards Cumulative percent of population working or residing in areas above air quality standards Maximum concentrations of pollutants Number of acres of open space consumed by plan implementation Energy Total annual fuel consumption Fuel consumption per passenger mile Regional Development Accessibility maps to the central business district and to regional centers Percent of designated regional centers within 1 mile of a major transportation service Average frequency of transit service Average number of transfers Percent standing passengers Average annual total transportation cost per user Average out-of-pocket cost per trip (by trip purpose) Total number of accidents Total number of accidents per passenger mile Equity Average travel times for the elderly, handicapped, and poor Relative average time between majority and minority Accessibility maps for minorities and poor Number of low-income jobs within 60 minutes by transit Capital and Operating Costs Total annual cost of transportation for all modes Total public capital cost Total operating and maintenance costs Route miles of construction Source: Memorandum from D. Shults to CATS technical Staff, "Evaluative Measures", Chicago Area Transportation Study, July 17, 1975. Reprinted in Meyer and Miller 1984, 381-2. Transportation 104 Supplying many measures of effectiveness and a comparative assessment without overwhelming the decision maker is difficult. One way of providing the measures of effectiveness and comparative assessment in a comprehensible and useful form is to employ the evaluation matrix shown in Figure 35. The planner can add a third dimension to the evaluation matrix, the impact-incidence matrix shown in Figure 36, to illustrate the distributional impacts over geographical areas and socio-economic classes. The impact-incidence matrix enables the decision maker to identify which groups are adversely affected and the level of their required compensation. Figure 35: Evaluat ion M a t r b : to CO CD c <D > +—• O CD — 111 o CO CD t_ Z3 CO CO CD Alternatives 1 2 3 4 5 | n 1 2 3 n B/C's ................ Comparative Assessment Figure 36: Impact - Incidence Matrix Groups Impacted 1 2 3 4 5 fr,r„„„„„„ Measures Of Effectiveness i Measures Of Effectiveness 2 Measures Of Effectiveness 3 Measures Of Effectiveness 4 Measures Of Effectiveness Measures Of Effectiveness n In summary, the evaluation process consists of the following three steps. First, the planner selects measures of effectiveness in accordance to the six major character-istics and determines the value of each measure for each alternative. Second, the planner performs a comparative assessment which involves reducing the values to a single dimension. At this point, the comparative assessment reveals the best alterna-tive based on efficiency and equity. However, since selections based on efficiency and equity are frequently overruled by politics, the planner must proceed to the next step. Third, the planner presents the measures of effectiveness and comparative assessment in a comprehensible and useful form to the decision maker as shown in Figure 36. The decision maker then makes the decision. Transportation 105 Figure 37: Example Evaluat ion M a t r i x Al ternat ives 1 2 3 4 5 Measures Of Effectiveness % of population within 10 minutes walking time to entry point 50% 60% 45% 75% 85% Measures Of Effectiveness Number of relocated households 0 25 0 25 50 Measures Of Effectiveness Added tax base -$1M 0 -$1M +$2M +$1M Measures Of Effectiveness Noise levels in South Millwoods low med low high med Measures Of Effectiveness Percent of low-income jobs within 60 minutes by transit 40% 50% 40% 80% 95% Measures Of Effectiveness Deficit per passenger $0.15 $0.32 $0.08 $0.76 $0.61 Comparative Assessment 2.2 1.7 1.5 1.1 0.7 6.3.2 ROUTE EVALUATION - COST MODEL TEMPLATES Don Boyd, 1986 The Route Evaluation Cost Model consists of two templates Master and Allroute. and a data file for each route. Template Master (see Figure 37) creates a file for each route with the following inputs: bus route number; round trip distance; average hourly pay with and without benefits; the fuel and maintenance cost per kilometer; average overall hourly cost which includes the cost of insurance, administration, capital, etc.; the average fare; and for each time period, number of operating hours, number of round trips, and the average number of rides per trip. After entering 68 required pieces of data, the template generates 329 different results, or put another way, the template generates five values for every one entered. The user must repeat the process of entering the necessary data and saving the template under a different name for each of the routes. Demol. Demo2. and Demo3 are example route files Transportation 106 Figure 38: Route Evaluation • Cost Model Template Template Allroute (see Figure 38) amalgamates a range of information from each of the route files created with Master. Once assembled, the user employs the spreadsheet's data base management capabilities to select the routes that do not meet the criteria of the transit policies. While this template computes values for several measures of effectiveness categorized as transportation system performance in Table VIII, the template ignores the more difficult to quantify measures of effectiveness such as impacts on air quality, community cohesion, energy cohesion, energy consumption, equitable distribution of resources, and economic development. It is not apparent if the developer's reason for omitting these more difficult to quantify measures is because he believes the only criterion is "which alternative maximizes the monetary benefits for the costs in-Transportation 107 Figure 39: Allroute A | B C D E F | G | H | 1 | J | K L M N O P Q i ALLROUTE ROUTE EVALUATION • COST MODEL 2 JJL.LyJJLL.JJJJULU (Written by Don Boyd-BC Transit Victoria) nvvwwvvvvww. 3 W A R N I N G ! ! ! • 4 DO NOT PRESS CALC KEY (F9 ALL ROUTES • BY DESCENDING ORDER OF RIDES/HOUR 5 PRESS "ALT V FOR MACROS 1 6 OPERA. COST-OPERATING COSTSONLY(WAGES, BENEFI f T S J S M E) 7 ISHEET DATE: JANUARY 1986 TOTAL COST-OVERALL COSTS BASED ONJ 45.00/HOUR 8 9 WWWWWWWHHWV 10 11 RTE BUS BUS TOTAL TOTAL RIDES TOTAL 1% jOPERA. TOTAL COST RIDES TOTAL DEF/PAS DEF/PAS 12 i ]DAY (TIME HRS KMS TRIPS SEATS /TRIP RIDES JCAP iOOST COST /RIDE /HOUR REVENUE OPERA. TOTAL 13 14 15 2|Mon-Fri Day Base 24.00 $960 48.0 4,704 96.0 4,608 98% ! $767.04 1080 $0.23 192 $2,760.19 ($0.43) $0.36 16 5|Mon-Ffi 35.66 $1,019 54.5 5,341 99.0 5,396 101% $1,605.00 $0.30 151.3 $4J19J5 m ($0.56) ($0.45) 17 IJMon-Fri Day Base 12.00 $240 24.0 2,352 35.0 840 36% $311.76 $540.00 $0.64 70.0 $469.56 ($0.19) $0.08 18 SUMS&AVG. 71.7 $2,219 126.5 12,397 85.7 10,844 87% ! $2,097 $3,225 $0.30 151.3 $7,248.22 ($0.48) ($0.37) curred?"; he already had other methods for calculating difficult measures; or he believes spreadsheets are useless for calculating difficult measures. As well, the template does not provide a comparative assessment other than ranking the bus routes in descending order by rides per hour. 6.3.3 STRUCTURE OF ROUTE EVALUATION - COST MODEL The Allroute template has several structural problems. Each problem has a solution. Allroute has twenty four macros (see Figure 39). Changing the constant in the last line of each macro to a variable will make it easier to move macros around. For example, in macro "/A", the last line "/XGX16-" should be "/XG/B". The template's documentation states "DO NOT INSERT ROWS!". Moving macro " /A" in Allroute one row down so it begins on the same line as the other macros will make it possible to add and delete rows. Even better, moving all the macros so they begin on line 13 will ease adding and deleting rows and also make the template more readable since the route names in the macros would be on the same line as the route information in the summary table. Transportation 108 Figure 40: Example of Macros u w Y Z AA AB 10 11 12 13 14 15 16 17 18 19 20 21 22 23 MACROS The following macros combine the range names listed below from all demo route files. Note: To stop the macro execution enter "Ctrl-Break". \A - MFDB \M - MFAMFR \B - MFAM \F - MFPM \C - MFEVE \N - MFLN \G - MFTOTAL \0 - SATAMFR \H - SATAM \l - SATDB \J - SATPM \K - SATEVE \Q - SATLN \L - SATOTAL \P - PRINTS SPREADSHEET \R-SUNAM  \T-SUNDB \U - SUNPM W-SUNEVE \W -SUNLN \D - SUNTOTAL \P /PP0P66~QRDATA~A~GPQ{H0ME} \Z - Demo Macro (Cell AB40) \Y {G0T0}S1~ \A (GOTO}A14~ /FCCNMFDB-DEM01-{D0WN}/FCCNMFDB~DEM02~ {D0WN}/FCCNMFDB~DEM03~ {DOWN}{RIGHT 8}{EDIT}~{LEFT 5}{EDIT}~{R}{EDIT}~{R}{EDIT}~{R}{EDIT}~{R}{EDIT}~{R}{EDIT}--{R}{E /DSG{BEEP}{GOTO}CQ25~{WAIT@NOW+@TIME(0,0,10)}{HOME} /PP0P66~QRDATA~A~GPQ {GOTO}A14~ /XGX16-B) Demo Macro with Documentation AA AB AE 44 45 46 47 48 49 50 51 \E /FCCNMFDB-DEM01-{D0WN}/FCCNMFDB~DEM02~ {D0WN}/FCCNMFDB~DEM03~ {DOWN}{RIGHT 8}{EDIT}~{LEFT 5}{EDIT)~{R}{ED /DSG{BEEP}{GOTO}CQ25~{WAIT@NOW+@TIME(0 /PP0P66~QRDATA~A~GPQ{H0ME} To activate this macro enter Alt E. The next three steps combine range name. MFBD from the three DEMO files. This next long step totals each column by editing This sorts the data by rides/hour. This prints the spreadsheet. Allroute should produce graphs for the decision maker which visually highlight the differences in the deficit per passenger between the routes. The documentation states in bold "start editing all the macros by inserting your new file names. This will take you considerable time". Creating a macro which inserts route names into all the macros will save the user considerable time. Last and most serious, template Allroute has two programming errors. Like the other structural problems, programming errors, once discovered, are easily remedied. The Demo macro should begin with "{goto}A14~". The template should define the range "DATA", which is used by over twenty macros. Transportation 109 6.4 CHAPTER SUMMARY There are many transportation planning models employed in an urban area at a given time, each at a different level of complexity and purpose. For example, fiscal planners might be using the ridership forecasting models to examine alternative fare structures; long range transit planners might be employing evaluation models to identify routes to phase out; transportation engineers might be performing spatial distribution analyses to determine trip distribution. Every model has its limitations. Planners must know and abide by these limitations, which in some cases prevent the use of a model beyond the sketch level. The templates described in this chapter and in Appendix F cover all the transportation disciplines mentioned in the following quote. The proliferation of microcomputer applications has reached every urban transportation discipline, including traffic engineering, public transpor-tation, urban transportation planning, and transportation design and construction. (Bower and Abkowitz 1986,1) The range of applications include: estimating revenues and expenses, analyzing cash flows, comparing transit services, enhancing non-spreadsheet programs, computing fuel and labour costs, calculating capacity and flows, determining modal split, evaluating routes, planning routes, assessing the need for traffic lights, monitoring transportation for the elderly and disabled, regulating policies, forecasting demand, providing passenger information, and studying spatial distribution. The structures of the three templates evaluated in this chapter are exemplary, especially that of Demand Elasticity Model, which provides well written documen-tation containing information on the template's objectives, operations, and theory, and demonstrates the use of macros, on-line documentation, custom menus, pre-designed graphs, error flag tables, and a table of contents. The Route Evaluation - Cost Model makes good use of range names and the Spatial Distribution Analysis wisely keeps adjustments in the "Adjustments to Matrix" table which is separate from the other values. Transportation 110 Nevertheless, the user can always improve the structure, either by adding more features (macros, pre-designed graphs, documentation) or by fixing the existing layout. Of course, the user should only undertake improvements, if the gains in productivity justify the time spent enhancing the template. Transportation 111 CONCLUSION Conclusion 112 7.0 INTRODUCTION A number of factors influence the value of spreadsheets to planners. The capabilities of spreadsheets, the effectiveness of the planning models whose compu-tations sometimes involve spreadsheets, and the savings in development time by employing templates are of particular interest. 7.1 SPREADSHEETS Spreadsheets are powerful tools whose capabilities need assessing. Assessing spreadsheets' capabilities requires clarifying the concept of spreadsheets and describ-ing their general applications. 7.1.1 CONCEPT The worksheet module forms the heart of the spreadsheet package. However, spreadsheet packages frequently include integrated modules such as word processing, database, and graphics, which enable users to present information in many formats. The worksheet is distinguishable by its table format, calculator abilities, and modifiability. The worksheet presents data in a two-dimensional table. Each cell of the table either is blank or contains a label (text), a numeric constant, or a formula. In calculating a value, the formula may reference constants and formulas in other cells and use built-in functions similar to those on a sophisticated calculator. Since worksheets permanently store the contents of cells, changes to one cell does not require re-entering the contents of the other cells. If a cell changes, the worksheet automati-cally recalculates all the formulas referencing the cell and displays their new value. Many spreadsheets enable the developer to build macros. A macro is a set of commands similar to a program written in BASIC which executes with a single command. Building a macro is advisable whenever the same set of commands is executed more than once. Spreadsheets however have a number of limitations. For example, spread-sheets only perform a limited number of calculations per second and store a fixed amount of information. The user also sets limitations on the time and money allotted to performing computations in planning models. Conclusion 113 7.1.2 GENERAL APPLICATIONS The worksheet's three distinguishing traits: table format, calculator abilities, and modifiability, respectively, support the three general applications of spreadsheets: table production, numerical analysis, and the testing of "what i f scenarios. Spread-sheets' table format and capacity to customize cells, to manipulate rows and columns, and to ease movement between cells, make spreadsheets ideal for producing tables. Spreadsheets' calculator abilities allows numerical analysis with a performance between programmable calculators and large statistical packages. Spreadsheets' modifiability permits quick answers to "what i f question. A "what i f question is a question easily answered by making simple changes in the data or formulas and by observing how the results reflect these changes. This "what i f capability enables planners to consider a wider range of alternatives and simplifies performing sensitivity analysis. Spreadsheets are not the only tool capable of table production, numerical analysis, and the testing of "what i f scenarios. However, there is a temptation for spreadsheet users to forget about other tools and attempt to solve every problem within the prison walls of the spreadsheet matrix. Dvorak warns "spreadsheet programming is turning the users into humorless accountant types. It is the embodiment of the bookkeeper's thought pattern." (1987, p. 71) Planners must remember that the value of spreadsheets depends on the capabilities of the other tools found in the planning office and on the particular application. In some cases, spreadsheets are of no value. In other cases, spreadsheets provide preliminary estimates before conducting addi-tional analysis using a more sophisticated tool. There is no case where spreadsheets are the only tool which can solve the problem. 7.2 PLANNING MODELS The effectiveness of the planning models whose computations uses spread-sheets influences the value of spreadsheets to planners. Therefore, if no planning model's computations uses spreadsheets, then spreadsheets would be of no value to planners. Conclusion 114 However, as demonstrated, a number of planning models exist whose compu-tations involve spreadsheets. These models include mathematical trend extrapolation, cohort survival model, residential carrying capacity model, economic base analysis, shift share analysis, cost/benefit analysis, spatial distribution analysis, disaggregate elasticity model, and route evaluation model. Yet, the existence of these models does not prove that spreadsheets have some value to planners since if these models are of only historical or academic interest in planning then the value of spreadsheets to professional planners remains nonexistent. Determining the effectiveness of these models requires evaluating their theo-retical weaknesses and practical difficulties. For example, a theoretical weakness of economic base analysis is its assumption that base activity is the sole source of growth in the economy to the exclusion of government spending and investment. In contrast, a practical difficulty of the cohort survival model is the expense incurred in obtaining the necessary data. Despite the controversy in the literature over the utility of these models, most planners conclude that the majority of the models discussed in this thesis have some merit. The relative simplicity of these models seems to negate the value of spread-sheets to complex models. However, this conclusion is premature because none of the models evaluated approach the "logical limits" of spreadsheets. Nevertheless, at some point models become too intricate for spreadsheets. 7.3 TEMPLATES A template is a program written in the spreadsheet language, containing formulas, macros, and documentation, which accomplishes a desired task, such as the computation in a planning model, and which is repeatable with different data and no or only a few modifications to the program. Reusing a template, saves the hours spent designing a worksheet and typing in formulas and macros. Thus, a template enhances the value of spreadsheets. However, a template that is inapplicable to the model, has an inadequate Conclusion 115 structure, takes too long to be delivered1, is incompatible with the available spread-sheets, or is too expensive2, serves no benefit. The next two sections elaborate upon the applicability and structure of templates. 7.3.1 APPLICABILITY OF TEMPLATES Modifying models to fit templates, or put another way, modifying the ends to fit the means, is a mistake. For example, if a cost/benefit analysis model with an internal rate of return decision algorithm was originally selected, switching to a model with a benefits divided by costs decision algorithm in order to use an available template may produce a different ranking of the projects. Where no templates apply to the model, the planner might develop or modify a template, or use some alternative tool such as a traditional programming language or a large statistical package. Developing or modifying a template forces the planner to examine its applica-bility which is why Landis encourages planners to develop their own templates instead of using distributed templates. analysts should avoid inserting their own numbers into spreadsheets developed elsewhere. The simple effort of developing one's own spreadsheet (starting perhaps with the program logic designed else-where) will help local analysts avoid misusing this powerful tool. (Landis 1985, 222) 7.3 2 STRUCTURE OF TEMPLATES Occasionally, a template which passes the model applicability test, may be structurally inadequate. A template with an inadequate structure is not FRAMED, meaning that the template has problems regarding Flexibility, Readability, Advanced spreadsheet features, Modular design, Errors, and Documentation. For example, a flexibility problem is the Small Area Population Projection template not accommo-dating data for years other than 1970 and 1980. A readability problem is Halley not 'Electronic mail reduces the delivery time from several weeks to several seconds and avoids the delays and charges associated with clearing customs. 2While most templates are affordable, costing less than $25., the occasional template is expensive. For example, CityCip, a Lotus 123 template, costs $1,500 per license (Lima 1985,9). Conclusion 116 making the purpose of the growth rate very clear. An advanced spreadsheet feature problem is Allroute requiring twenty repetitive edits every time the user adds or deletes a route. Amodular design problem is combining several distinct tasks into one macro. An errors problem is Population Projection's recalculation error, which occurs unless the user performs several iterations of calculations. A documentation error is Benefit/  Cost Analysis not fully explaining the structure and limitations of the model used. Notably, almost no standardization between the templates exits. Using com-mon macros and similar layouts would reduce the structural problems. Over all, the structure of the templates evaluated is weak considering they are designed for distribution (see Figure 41). The templates are weakest in the area of readability and strongest in the area of modular design. However, the reason modular design was not a problem was because most of the templates worked with simple models. This may also explain why there is no need for additional error flags or help screens. Other areas of concern are the inability to add and delete rows and columns, the need for more macros in the transportation applications, the recalculation errors in the Appleworks templates, and the documentation of the models. The templates excel in the areas of separating data, formulas, results, and adjustments; cell highlighting; and documenting their objectives. The best structural templates are Population Projection. Hallev. and Disaggre- gate Elasticity Model since the user only has to change the data. In addition to changing the data, Location Quotient. Employment Shifts/Shares, and Route Evalu- tation - Cost Model require some modifications. Small Area Population Projection and Shift Share Analysis are only useful as guides for the development of new templates. Land. Cost/Benefit Analysis, and Spatial Distribution Analysis are useless, largely due to poor documentation of the template's model. Screening out the templates which either have poor structure or are are faster to recreate than Loomis's rush courier service, leaves just two out of eleven templates, Hallev and Disaggregate Elasticity Model. If this ratio is normal, than over 80% of templates now distributed are of little value to planners. Nevertheless, planners can Conclusion 117 Figure 41: Summary of the Template Structural Evaluations FLEXIBILITY Cons tan ts are used instead of va r iab les . It is dif f icult to add or de le te rows and c o l u m n s . READABILITY Not al l of the t i t les, range, and graph names are descr ip t i ve . Descr ip t ive range names shou ld be used more. Not al l of the fo rmu las are easy to unders tand . T h e pu rpose of every label and fo rmu la is not c lear . The format of the va lues and labe ls needs improvement . The row and co lumn t i t les are not a lways d i sp layed . The worksheet is not broken into sc reen s i z e p i eces . The printout d o e s not fit neatly on the p a g e s . The da ta , ca lcu la t ions , and resul ts are not sepa ra ted . A d j u s t m e n t s a re not s e p a r a t e d ADVANCED SPREADSHEET FEATURES C e l l h ighl ight ing and protect ion is not used enough . The re are not enough p re -des igned g raphs . There are not enough mac ros . J • MODULAR DESIGN The templa te is not b roken into modu les . ERRORS ; T h e r e a r e p r o g r a m m i n g e r r o r s . T h e r e are r e c a l c u l a t i o n e r ro r s . The template does not adequate ly f lag errors. DOCUMENTATION Temp la te ' s ob jec t ives are not wel l documen ted . The templa te 's operat ion is not wel l documen ted . The template 's model is not wel l documen ted . There are not enough help s c r e e n s . There is not an adequate worksheet map or table of Conten ts . Conclusion 118 benefit from learning about the mistakes and strengths of these templates before developing their own templates. 7.4 FUTURE OF SPREADSHEETS IN PLANNING Spreadsheets are unlikely to revolutionize planning. Furthermore, not every planner needs to use one. However, those planners that work with numbers should find spreadsheets a valuable asset. Their advantages are growing with every new version or "add-on", while their disadvantages are declining in significance as limitations such as speed, memory, and access to a microcomputer are improving over time. As spreadsheets' capabilities multiply, as planners adapt more models to two dimensional tables, as distributed templates become more plentiful and better built, the value of spreadsheets to planners will increase. So, planning schools should place more emphasis on spreadsheets in their curriculum. One day in the future, planners will view spreadsheets like today's four-function calculator: "how did we ever manage with-out". 7.5 FURTHER RESEARCH Of course, many questions on the value of spreadsheets to planners remain unanswered. For example, how can the capabilities of spreadsheets, the utility of the planning models whose computations sometimes involve spreadsheets, and the savings of development time by employing templates be improved? What are the comparative values of alternatives to spreadsheets? How does the planner's access to computers and programs, knowledge of computers, and attitude towards computers, influence the value of spreadsheets? What are some other factors influencing the value of spreadsheets? 7.6 CLOSING COMMENTS The thesis opens with this quote: It is abundantly clear that the electronic spreadsheet is the single most important computer tool available to the practicing planner. (Brail, working paper, 1985,1) Conclusion 119 Perhaps, after reading this thesis, Brail might say more precisely: Spreadsheets are a resource savings tool since they can perform table production, numerical analysis, and the testing of "what i f scenarios with less effort than other available tools. On the other hand, no task absolutely requires a spreadsheet, and in fact, spreadsheets' capabilities only work with relatively simple models which are adaptable to two dimensional tables. While such planning models exist, their utility is questionable. Nevertheless, the general consensus is that these models have merit. So templates have been developed around these models to save additional resources. Most of these templates, however, lack the structure necessary for them to be of any real value. At any rate, spreadsheets save resources, but it will take more than a spreadsheet to calculate the size and best uses of this savings. Conclusion 120 BIBLIOGRAPHY Bibliography 121 \ BIBLIOGRAPHY PLANNING LITERATURE Alonso, William. "Predicting Best with Imperfect Data", Journal of the American  Planning Association. July 1968, pp. 248-54. Anderson, L. G. and R. F. Selte. "Accounting for Benefits and Costs Over Time", Benefit-Cost Analysis: A Practical Guide. 1977. B.C. Environment & Land Use Committee Secretariat, "Discount Rates, Discount-ing methods and Ranking Criteria", Guidelines for Benefit-Cost Analysis. 1977. Cina, Craig E. "An Empirical Test of Nonsophisticated Employment Projection Techniques at the SMSA level." Regional Science Perspectives. 8(1) 1978, pp. 1-11. Davies, Craig. "Economic Base and Input-Output Multipliers: A Comparison for Vancouver, B.C." Davies, Ross. Marketing Geography with Special Reference to Retailing. Lon-don: Methuen & Co. Ltd., 1976. Dunn, Edgar S. Jr. "A Statistical and Analytical Technique for Regional Analysis". Regional Science Assosciation. Papers and Proceedings of. Volume 6, 1960, pp. 97-112. Frankena, Mark W. Urban Transporation Economics. Toronto: Buterworth & Co., 1979. Greenberg, M . R. "A Test of Alternative Models for Projecting County Industrial Production at the 2,3, and 4-digit Standard Industrial Code Levels." Re- gional and Urban Economics. 1,1972, pp. 397-417. Hellman, D. and M . Marcus. "A Critical Analysis of Employment Projection Meth-ods: A Test Case of New Jersey," New Brunswick, N.J.: N.J. Water Re-sources Research Institute, Rutgers University, 1970. Bibliography 122 Hightower, Henry. "Population Studies." Principles and Practice of Urban Plan- ning. Ed. William I. Goodman and Eric C. Freund. Washington: International City Managers Association, 1968, pp. 51-75. Hutchinson, Bruce. "The Effectiveness of Urban Transport Systems Analysis". Systems Analysis in Urban Policv-Making and Planning. Ed Michael Batty and Bruce Hutchinson. New York: Plenum Press, 1982, pp. 53 - 67. Isserman, Andrew M . "The Location Quotient Approach to Estimating Regional Economic Impacts." Journal of the American Planning Association. 43(1), January 1977, pp 33-41. Isserman, Andrew M . "Projection, Forecast, and Plan." Journal of the American  Planning Association. Spring 1984, pp. 208-221. Keyfitx, Nathan. "Can Knowledge Improve Forecasts" Population and Develop- ment Review. 8 (1982), pp. 729-51. Krueckeberg and Silvers. "Projecting Population". Urban Planning Analysis: Methods and Models. Toronto: John Wiley & Sons, Inc., 1974, pp. 259-287. Lee, D.B. "Requiem for Large-Scale Models." Journal of the American Institute  of Planners. Volume 39, pp. 163-178. Leigh, Roger. "The Use of Location Quotients in Urban Economic Base Studies." Land Economics, pp. 202 - 205. Lewis, W. Craig. "Export Base Theory and Multiplier Estimation: A Critique." pp. 58 - 70. Mayworm, P. D. "Transit Fare Elasticity: Role in Fare Policy and Planning." Washington, D. C , 1982. Meyer, Michael D. and Eric J. Miller. Urban Transportation Planning: A Deci- sion - Oriented Approach. New York: McGraw - Hill, Inc., 1984. Models. "Projecting Population." pp. 259-87. Models. "Regional Income and Employment Analysis." pp. 417-27. Bibliography 123 Moen, Elizabeth W. "Voodoo Forecasting: Techinical, Political and Ethical Issues Regarding the Projection of Local Population Growth." Population Re- search and Policy Review. 3 (1984), pp. 1-25. Prest, A. R. and R. Turvey. "Cost-Benefit Analysis: A Survey." The Economic  Journal. December 1965. Sawicki, David S. "Quantitative Methods in Urban Planning." Introduction to  Urban Planning. Ed. Anthony J. Cantanese and James C. Snyder. New York: McGraw Hill Book Company, 1979, pp. 149-171. Simmons, Jim. "Population Forecasting: How little we Know." Plan Canada. 21(3) September, 1981, pp. 75-84. Stevens, Benjamin H. and Craig L. Moore. "A Critical Review of the Literature on Shift-Share as a Forecasting Technique." Journal of Regional Science. 20(4) 1980, pp. 419-437. Wachs, Martin. "Ethical Dilemmas in Forecasting for Public Policy." Public Ad- ministration Review. Ed. Carl W. Stenberg. 42(4), pp. 562-9. Waters, W.G. "Impact Studies and the Evaluation of Public Projects". The Annals  of Regional Science. Ed. Michael K. Mischaikow. 10(1), pp. 98-103. Williams, Alan. "Cost-Benefit Analysis: Bastard Science and/or Indisdious Poison in the Body Politick" Journal of Public Economics. 1, 1972. Zimmerman, Rae. "A Variant of the Shift and Share Projection Formulation." Journal of Regional Science, 15(1), 1975, pp. 29-38. MICROCOMPUTER LITERATURE Berry, Tim. "Building Your First Macro", Business Software. M & T Publishing, Inc, 4(9), September, 1986, p. 57. Berry, Tim. "Making Sense of Financial Functions", Business Software. M & T Publishing, Inc, 4(1), January, 1986, p. 30. Berry, Tim. "How to Structure Spreadsheets", Business Software. M & T Publish-ing, Inc, 4(10), October, 1986, p. 56. Bibliography 124 Berry, Tim. "Who's to Blame for Spreadsheet Errors?", Business Software. M & T Publishing, Inc, 4(12), December, 1986, p. 36. Bower, D. J. and M . D. Abkowitz. "Trends in the Application of Microcomputers in the Urban Transportation Environment", Renssalaer Polytechnic Institute, Troy, New York, May 1986. Brail, Richard K. "Pushing the Spreadsheet Envelope." Working Paper. Brail, Richard K. "Pushing the Spreadsheet Envelope." Mug Shots. Winter 1985, 2(2), p. 7. Brail, Richard K. "The Micro in the Classroom." Journal of Planning Education  and Research. August 1984, pp. 55-60. Bryan, Marvin. "When are Multiplan and SuperCalc viable alternatives to 1-2-3?", Business Software. M & T Publishing, Lie, 4(10), October, 1986, p. 66. Bryan, Marvin. "Bug-Proofing Your Spreadsheets", Business Software. M & T Publishing, Inc, 4(12), December, 1986, p. 38. Dvorak, John C. "Spreadsheets? Just Say No", PC Magazine. 6(22), December 22, 1987, p. 71. Foster, Douglas B. "The Rocky Road to Automation." CIP Forum. July 1985, pp. 6 and 7. Heald, James. "Mainframe Computers for Shiftshare Analysis." Journal of the  American Planning Association. Autumn 1985, pp. 515 and 516. Harrison, Graham and Paul Parker. "Use of Spreadsheets for Data Analysis and Display." Microcomputers for Local Government Planning & Manage- ment, ed. Dr. Peter Newton and Dr. Michael Taylor. North Melbourne: Har-green Publishing, 1986, pp. 42-50. "How to Use Spreadsheet Templates." TIME Support Center. Transit Industry Microcomputer Exchange, Department of Civil Engineering, Rensselaer Polytechnic Institute, Troy, New York. Bibliography 125 Klosterman, Richard E. "An Assessment of Three Microcomputer Software Pack-ages for Planning Analysis." Journal of the American Planning Associa- tion. Spring 1986, pp. 199-202. Landis, John D. "Electronic Spreadsheets in Planning: the case of Shiftshare Analysis." Journal of the American Planning Association. 51(2), Spring 1985, pp. 216-24. Levine, Ned. "The Construction of a Population Analysis Program Using a Micro-computer Spreadsheet." Journal of the American Planning Association. Autumn 1985, pp. 496-511. Ottensmann, John. "Analyzing Planning Alternatives Using Electronic Spreadsheets." Journal of Planning Education and Research. August 1984, pp. 33-45 Person, Ron. "Solving Unsolvable Equations With Circular Reference Formulas", Absoulte Reference. 4f8h August. 1986. p. 1. Poter, Kent. The New American Computer Dictionary. Scarborough, Ontario: New American Library of Canada Limited, 1985. Quinn, Bob. "Making the Lotus Command Lanquage Work for You", Absolute  Reference. August 1986, p. 22. Sawicki, David. "Microcomputer Applications in Planning." Journal of the Ameri- can Planning Association. 51(2), Spring 1985, pp. 209-15. Taylor, Jared. "Challenging 1-2-3 on Price and Power", PC Magazine, 6(18), Octo-ber 27, 1987 pp. 94-102. Taylor, Jared. "A New Face for Spreadsheets", PC Magazine. 6(22), December 22, 1987, p. 103. Wensley, James and James Batchelder, "Use of Spreadsheets in Transit Service Planning" Multisystems, Inc., October 1985, p. 2. Bibliography 126 MICROCOMPUTER PERIODICALS Absolute Reference. "The journal for 1-2-3 and Symphony users", Que Corpora-tion, IV(7-12), July 1986 - December 1986. Business Software. "Building better PC applications", 3(11), November 1985, 4(1-12), January 1986 - December 1986. Bvte. "The small systems journal", 11(12), November 1986. MacUser. "The Macintosh resouce", September 1987. MacWorld. "The Macintosh Magazine", April 1987. Microcomputers in Planning Association. Ed. R. Lee Stockman, Alto, Michigan, IV(6), November-December 1984. Mug Shots. "Newsletter of the Planners' Microcomputer Users Group", Ed. Rich-ard Klosterman, Department of Urban Studies, The University of Akron, 1(1-2), Fall and Winter 1984, 2(1-2), Summer and Winter, 1985. PC WORLD. "The comprehensive guied to IBM Personal Computers and Com-patibles", November 1986 PC MAGAZINE. "The independent guide to IBM-standard personal computing", 6(22), December 22,1987. TIME Capsule. "Newsletter of the Transit Industry Microcomputer Exchange", 4(1-4), Fall and Winter 1985; Spring and Summer 1986, 5(1-2), Fall and Winter 1986. Bibliography 127 SPREADSHEET MANUALS AND TEMPLATE CATALOGUES Aron, Arthur and Elaine Aron. Using Appleworks. Indianapolis: Que Corporation, 1985. Boyd, Don. Route Evaluation - Cost Model (RECM) User's Guide. Victoria: B.C. Transit, 1986. Carlton, Thomas. 123 Financial Macros. Indianapolis: Que Corporation, August 1986. Clark, Roger. Executive VisiCalc for the IBM Personal Computer. Addison-Wesley Publishing Company, 1983. Ewing, David and Joseph-David Carrabis. Using Javelin, Indianapolis: Que Corpo-ration, 1986. Harrison, Bill . Framework II: An Introduction. Ashton-Tate Publishing Group distributed through the McGraw-Hill Book Company. Lima, Robert J. Planning Sofware Survey. Chicago: American Planning Associa-tion, Planning Advisory Service Report Number 388, July 1985. Lotus staff. The Lotus Guide to Learning 123. Reading, Massachusetts: Addison-Wesley Publishing Company, Inc., December 1985. Technology Research and Analysis Corporation. User's manual: Disaggregate  Elasticity Model (DEL). Arlington. 1984. U.S. Department of Transportation. Microcomputers in Transportation: software  and source book. Washington: February 1986. United Nations Centre for Human Settlements (Habitat). Microcomputer Users  Directory. Issue No. 2, February 1985, Issue No. 3, January 1987. Whited, William L. Using Microcomputers in Urban Planning. 1982. Bibliography 128 TEMPLATE DOCUMENTATION Beardmore, R. M . Project Financial Planning System: User's Guide. Harare, Zimbabwe: Ministry of Construction and National Housing, March 1984. Cambridge Systematics, Inc. Roadway AO: Air Quality Impacts of Roadway  Improvement Alternatives. Cambridge, Massachussetts: August 1983. Cambridge Systematics, Inc. Intersection AO: Air Quality Impacts of Signalized  Intersection Improvement Alternatives. Cambridge, Massachussetts: Au-gust 1983. Carlson, Catherine J. The New Orleans Geographical Information Management  System: an Urban Planning Management Tool. New Orleans, Lousiana: New Orleans City Planning Commission, June 1985. Carlson, Catherine J. New Orleans Geographical Information Management System: Version 1.0 User's Manual. New Orleans, Louisiana: New Orleans City Planning Commission, June 1985. Filipovitch, Anthony J. Urban Analytical Tools: a Handbook. Urban and Regional Studies Institute, Mankato State University, March 1985. Levine, Ned. Halley. Los Angelas: Graduate School of Architecture and Urban Planning, University of California, 1984. Philip B. Herr & Associates. "IMP" - Development Impact System. Boston, Massachusets: Community and Regional Planning Consultants, September 1985. Philip B. Herr & Associates. The LAND Model. Boston, Massachusets: Commu-nity and Regional Planning Consultants, October, 1986. Sipe, Neil and Rober Hopkins. Microcomputers in Economic Analysis: Spread- sheet Templates for Local Government. Florida: Bureau of Economic and Business Research, University of Florida, December 1984. Bibliography 129 APPENDIX Appendix 130 APPENDIX A T R A D E M A R K ACKNOWLEDGEMENTS Every attempt has been made to supply trademarks about company names, products, and services mentioned in this thesis. Ability - Xanaro Technologies Apple II - Apple Computer, Inc. Apple Works - Apple Computer, Inc. Aura - S of trend Context MBA - MBA Corporate MBA - MBA Electric Desk - Alpha Software Enable - The Software Group Excel - Microsoft Corporation Framework II - Ashton-Tate InteCalc - Schuchardt Software InteMate - Schuchardt Software Intuit - Noumenon Corporation Javelin - Javelin Software Corporation Jazz - Lotus Development Corporation Knowledge Man - Micro Data Base Systems Lotus 1-2-3 - Lotus Development Corporation Macintosh - Apple Computer, Inc. Magic Office - Artsci Open Access - Software Products Perfect: Calc - Thorn Emi Perfect:Family - Thorn Emi PFS:Family - Software Publishing PFS:Plan - Software Publishing Plan:Family - Chang Labs Propel - Pro Computing Smart Software - Innovative Software SuperCalc4 - Sorcim Symphony - Lotus Development Corporation T/Maker III - T/Maker Company VisiCalc - Software Arts Products, Inc. Appendix A 131 APPENDIX C GLOSSARY OF COMPUTER AND SPREADSHEET TERMS Argument or parameter Defines the scope of a command or function. In the function SQRT(x), x is the argument. Byte A unit of memory. One byte is equivalent to one character. Cell A unit of information in a worksheet referred to by its row and column address. The cell contains either a value or a label. A value is a numeric constant or formula. A label is a string of characters. Cells also contain format specifications and protection controls to prevent accidental erasure of its content. Cell annotation Enables the user to attach notes to cells which explain the logic behind the cells' content. Communication module Enables the planner to access information from another computer over the phone line and paste the information into the worksheet module for statistical analysis. Copy protection Some spreadsheet programs are copy protected to discourage piracy. However copy protection prevents the legitimate user from making back up copies in case the original is destroyed. Also, copy protection prevents copying the program on to a hard disk. With a growing number of large organizations refusing to buy copy protected programs, most spreadsheets are now being released not copy protected. Compatible is an imitation which works just like the original. Constant A value that does not change. Cursor A highlighted part of the screen indicating where the focus of attention is. Data Validation A check to ensure the data falls within a permissible range. Database module manages information. Databases provide less statistical capabili-ties than spreadsheets but more capabilities in locating and presenting the information. Planners may use a database to store items such as transportation networks, land use zones, permit applications, census information, and municipal licenses. Appendix C 133 Desk Top Tools Programs that run while the computer is turned on, even when using another program such as a spreadsheet. Useful desk top tools include spreadsheet auditors for finding errors, note pad for documenting the worksheet, and a calculator for verifying the results. Dialogue boxes A box containing comments which guides the user through the next task. Extended memory boards Enables the user to work with very large templates. Function A set of commands which replaces the tedious task of writing formulas. The Excel spreadsheet contains 131 built-in functions such as FACT(x), which gives the factorial of argument x. Excel also enables the user to create functions using special macros. Graphics Module Draws graphs directly from the data in a template. Any changes to the data are reflected in all future graphs. It takes the user virtually no time to create a graph. Appendix I discusses Add-on packages which produce presentation quality graphs and Appendix D shows five types of graphs. Help screen A screenful of information accessed by the user when guidance is required. Help screens explain the purposes of commands, how to use a command, whatto do next, etc. Lotus 123 spreadsheet contains 200help screens. Templates, such as Land, also have helps screens, although much fewer screens than spreadsheets. Integration Modules are linked together so information can be passed back and forth. Kilobyte (K) A unit of memory equivalent to 1,024 bytes or about a page of information. The Excel spreadsheet requires a computer with at least 640K of accessible memory. Label A string of characters. Text. Linking Templates The ability to reference cells in other templates. Macro A set of commands which the user invokes with a single command. Macros save typing time and allow the spreadsheet user some of the programming ability of traditional languages such as BASIC. Appendix C 134 Mainframe A computer larger than a microcomputer of the day in both size and capabilities. The phrase of the day is used since tomorrows microcomputers will be larger in capabilities than yesterdays mainframes. Megabyte (Mb) A unit of memory equivalent to 1,048,576 bytes or about five theses. While some floppy disks hold 1.2Mb, planners will find a hard disk with over 10Mb more convenient for storing spreadsheet templates. Memory A storage of information for later retrieval. Menu A list of options from which to choose the programs next action. Microcomputer A computer small enough to fit on top of a desk. Mouse A mechanical device, connected to a computer, that the user moves around on a desk top to control the cursor on the screen. Outlining Is another view. It enables the user to organize, edit, format, and refine a document quickly and easily. Password protection Prevents unauthorized use of templates. Templates with copy protection on requires the user to enter a password before manipulating the template. Programming language A vocabulary of English like verbs (PRINT, READ, ASSIGN, SAVE, OPEN) and implied verbs (+, -, /, *, =) and set of grammar rules (syntax) used to form commands that make sense to both a human and a computer program. Appendix L is an example of Pascal, a traditional programming language. Query A method for finding information that meets the criteria specified by the user. Range A group of neighboring cells Recorder or learn mode The programs ability to translate the users actions into a macro. The actions can then be repeated by invoking the macro. Spreadsheet A computer program with a worksheet module. System Several devices (printer, hard disk, computer, scanner) linked together. Appendix C 135 Template A spreadsheet application which solves a specific planning problem. Templates are to spreadsheets, as documents are to word processors, and as programs are to programming languages. Track dependencies The ability to find all the cells that depend on a particular reference, or all the cells the reference depends on. User friendly Easy to use and understand. View Away of displaying pertinent information. The formula view displays formulas instead of their results. The graph view displays a graph of the values. The constant view highlights the cells containing constants. Some spreadsheets enable the user to adjust the point size of the letters to either improve readability or fit more information in the view. For example, page preview reduces the lettering so the user can see on the screen what a printed page would look like. Window A viewport. The user can split the screen into several windows, each displaying a different view at the same time. For example, windows may be used to show several parts of the template or to show a graph beside its values. Word processing module The module enables the user to write documents. A planner can insert information from a template into a document and vice versa. In addition to being able to insert the spreadsheets results into a document, planners can use spreadsheets to produce tables for documents. Producing a table with a spreadsheet and inserting it into a document is faster than creating a table with the word processor. Worksheet module A two-dimensional table, each cell of which may be assigned a numeric constant, a formula with which to calculate a value that may use constants and formulas in other cells, or a verbal label or comment. Appendix C 136 APPENDIX D G R A P H TYPES A spreadsheet's graphics module can produce many types of graphs. Figures 42 to 46 are an Excel line graph, bar chart, column chart, pie chart, and an area chart, respectively ' P R O J E C T P R I D E C A S H F L O W A N A L Y S I S " Figure 42: Line Graph T h o u s a n d s 150 100 -50 •100 -150 *- Revenue Expenditures a- CASH FLOW Project Year 1980 POPULATION PYRAMID 20-24 •100 -80 -60 (1,000) -40 -20 20 40 60 80 Felmales Males Figure 43: Bar Chart Appendix D 137 Figure 44: Column Chart Population and Occuppied Dwelling Units m CO-OPS APARTMENTS M HOUSES POPULATION D 450 w 400 e 1 350 1 300 i n 250 g 200 u 150 n 100 i t 50 I I I I • I I I I • I I I I I • Bicycle m Auto m Bus B Walk • Other MODAL SPLIT FOR AFTERNOON TRAFFIC V ; / 1980 1985 1990 a t 200 j 100 (1,000) Figure 45: Pie Chart Figure 46: Area Chart Regional Employment JOBS (1,000) TERTIARY SECONDARY PRIMARY 1940 1950 1960 1970 1980 Appendix D 138 LIST OF MAJOR SPREADSHEET FUNCTIONS APPENDIX E By listing the major spreadsheet functions, Table IX gives planners a better idea of what spreadsheets can do. TABLE IX: LIST OF MAJOR SPREADSHEET FUNCTIONS ARITHMETIC FUNCTIONS The absolute (positive) value of a number A number raised to the power of another number Integer part of a real number The smallest integer not less than its parameter1 The largest integer not greater than its parameter1 The log (base 10) of a number The natural log (base any number) of a number The remainder of a division calculation A random number between 0 and 1 A number rounded to a specified number of places The positive square root of a number TRIGONOMETRIC FUNCTIONS Pi accurate to 15 decimals The sin of an angle The cosine of an angle The tangent of an angle The arc sine of an angle The arc cosine of an angle The are tangent of an angle SIMPLE STATISTICAL FUNCTIONS The average of items in a list The maximum value in a list The Minimum value in a list The sum of the values in a list The number of items in a list The standard deviation of a list of numbers Variance of a list of numbers REGRESSION AND TREND ANALYSIS The regression line showing the relationship between two or more variables The correlation coefficient showing how closely two or more variables are related. Expressed another way, it is how closely the regression line fits the actual values or simply put, it is pearson's r. Appendix E 139 The slope of regression line is "the rise over the run", the change in Y divided by the change in X. The Intercept the point on the Y axis that the regression line crosses. Predicted values of Y given the value X The Rolling average of values over time2 The Rolling maximum of values over time2 The Rolling minimum of values over time2 The Cumulative sum of series2 FINANCIAL FUNCTIONS The fair market price of a bond2 The yearly bond yield to maturity2 The present value of an ordinary annuity. The net present value of a stream of cash flows. The value at a given day in the future of an ordinary annuity (series of payments made at equally spaced intervals). The values at the end of a variable's time span2 The number of periods to accumulate the future value by making equal payments into an interest bearing account. Interpolation, i.e. fill in the blank values in a variable's time span.2 The compound growth rate for an initial investment that grows to a specified future value over a specified number of periods. The internal rate of return which is the rate of return, or profit, that an investment is likely to earn. The modified internal rate of return using the risk rate of return and the safe rate of return specified.1 The mortgage loan payment for a given principal, interest rate and number of periods. The straight line depreciation given asset's cost, salvage value, and depreciable life. The double declining balance depreciation where greater depreciation expense occurs in the earlier periods. The sum of years digits depreciation, where greater depreciation expense occurs in the earlier periods. MATRIX FUNCTIONS Invert. Multiply. 1A11 the functions are found in Lotus 1-2-3 release 2 except for these functions found in Framework and 2These functions found in Javalin. Appendix E 140 APPENDIX F CATALOGUE OF SPREADSHEET TEMPLATES The catalogue gives the name, source, and a brief description of 50 templates bringing the total number of templates discussed in this thesis to 61. The intention of the catalogue is not to promote software, but rather to illustrate by example, the broad range of planning and other local government applications. AMORTIZATION SCHEDULE Neil Sipe and Robert Hopkins Bureau of Economic and Business Research 221 Matherly Hall University of Florida College of Business Administration Gainesville, Florida 32611 This template generates a listing of the payment stream for any loan with equal periodic payments. The payment stream includes the period, payment, interest paid, principal paid and principal balance still owing. The loan could be for any planning project. BUDGET C A L C U L A T O R TIME Support Center Transit Industry Microcomputer Exchange Department of Civil Engineering Rensselaer Polytechnic Institute Troy, New York 12180-3590 Budget Calculator is more than just an accounting program. It is a dedicated transit template used to estimate future revenue and expenses and to test the effects of fare and service changes on ridership. Transit planners have made better decisions with the aid of this template. Appendix F 141 The template is designed to use the readily available data from the Urban Mass Transit Administration's forms 201, 203, 301, and 406. The 42 page documentation with 38 illustrations gives a comprehensive description of how to use the template and an adequate explanation of the elasticity concept used. CAPITAL COSTS OF SERVICES Neil Sipe and Robert Hopkins This template enables planners to assess the impacts of a new residential development on the local government capital expenditures for police, fire, parks/ recreation and libraries. The data required includes the current population, new housing units, average household size, and the annual expenditure on each of the services. The results consist of the total annual capital expenditure and the portion of the annual capital expenditure attributed to the new households for each of the services. CAPITAL COSTS OF WATER TREATMENT Neil Sipe and Robert Hopkins This template estimates the capital costs of constructing a water treatment facility. The template considers water use/capita, water use/employee, increase in population, increase in the number of employees, and financing costs to calculate the total and annual cost of the facility. CAPITAL COSTS OF WASTEWATER COLLECTION Neil Sipe and Robert Hopkins This template estimates the capital costs of wastewater collection lines. The template considers existing flows and system configuration, new housing, new employees, and financing costs in order to determine the additional wastewater flow, required pipe diameter, and the total and annual costs. Appendix F 142 CAPITAL COSTS OF WASTEWATER TREATMENT Neil Sipe and Robert Hopkins As in water treatment, templates are used to estimate the capital costs of wastewater treatment facilities. This template yields dollar figures for secondary treatment, secondary treatment with nutrient removal, advanced waste treatment, and advanced waste treatment with nutrient removal. CAPITAL COSTS OF WATER DISTRIBUTION Neil Sipe and Robert Hopkins This template calculates the costs of distributing water from the treatment facility to the consumer. The template considers the existing conditions, future projections, and financing costs to yield the length of additional water lines required, the average diameter of the water lines, and the total and annual costs. CAPITAL IMPROVEMENT PLANNING Anthony Filipovitch Urban and Regional Studies Institute Mankato State University In addition to determining the costs of capital improvements, spreadsheets are used for capital improvement planning. The information needed by this template includes facts about the city's budget, the criteria by which projects are to be judged and their relative weight, the dept capacity for capital improvements, and the costs of each project. Using this information, the template ranks the priority of the project and assists the user in apportioning the projects over the next five years. CASH FLOW ANALYSES Jack Reilly Capital District Transportation Authority Albany, New York distributed by the TIME Support Center Cash flow analyses is another area where spreadsheets are used. This template records the revenue (cash on hand, passenger revenues, state aid) and expenditures Appendix F 143 (system costs, loan payments) over a period of time and computes the cash balance for each week revealing when funds are scarce. CITYCIP Myles Schachter Foresight Solutions, Inc. 804 New Hampshire St. Lawrence, KS 66044 This template is a comprehensive capital improvement program. Existing and proposed projects may be entered and the program will calculate required tax rates, revenue funds needed, and statutory debt. CITYCIP forecasts future assessed valuations (for six years) and automatically calculates bond payments based on alternate payment schedules. CITYCIP can be used to test "what i f scenarios or monitor project implementation. COMPUTERIZATION OF PEDESTRIAN TIMINGS Lowell Bender and Robert Kochevar City of Lakewood Traffic Engineering Division 445 South Allison Parkway Lakewood, CO 80226-3105 This temple calculates flashing "Don't Walk", yellow, and red clearance periods. CONSOLIDATED PROJECT MANAGEMENT SYSTEM Lawrence Harman and Joy Hearn Executive Office of Transportation and Construction 10 Park Plaza, Boston, MA 02115 Spreadsheets help planners manage projects by enabling planners to consoli-date many aspects of the project into one system. This system uses a template for scheduling, accounting, and reporting, and a local area network for data communica-tions. Appendix F 144 COST ALLOCATION AND PERFORMANCE INDICATOR TIME Support Center This template calculates various statistics for comparing local, express, and paratransit services. However, the template's documentation and design are so poor that the template provides a perfect example of an application that has virtually no usefulness to anybody other than the developer. COUNTS PLUS II ENHANCEMENT James Bonneson Omaha, NE distributed by the Center for Microcomputers in Transportation Spreadsheet programs are used to enhance non-spreadsheet programs. This is the case with the Counts Plus IT Enhancement. This template replaces the need by the Counts Plus ][ BASIC program for collecting twenty four hour traffic counts by extrapolating eight hour counts. The template is then converted into a "DIF" file and read as data in the BASIC program. DETERMINING SOLID WASTE CHARGES Neil Sipe and Robert Hopkins This template helps planners determine the solid waste charges for each land use class. DEVELOPMENT IMPACT SYSTEM Philip B. Herr & Associates Herr Associates 261 Newbury Street Boston, Massachusetts 02116 The Development Impact System is a set of programs primarily written in BASIC for evaluating development alternatives such as rezonings and conditional use approvals. One of the programs in the set is MULTIYR. MULTTYR is a template which models the impacts of development alternatives on employment, population, and school enrollment over multiple years, reflecting the user's estimates of build-out Appendix F 145 rates and inflation. The template also calculates the net present value of the tax balance. E M P L O Y M E N T MULTIPLIER - REGRESSION ANALYSIS Neil Sipe and Robert Hopkins This template enables planners to calculate the employment multiplier for residential/commercial (service industry) projects and industrial/export generating (base industry) projects. The inputs are time series data for population, total employment, and export employment. EVALUATION OF ALTERNATIVE PROPOSALS Rick Kuner New Alternatives, Inc. 8 South Michigan Avenue, Suite 610 Chicago, IL 60603 The consideration of alternatives is the essence of planning analysis. Alternative plans are devised and their consequences are predicted and evaluated. (Ottensman, p. 33) This template evaluates alternative plans, policies, and programs from data concerning the evaluation criteria, the weight of each criteria, the alternative propos-als, and the consequences of each alternative. This template strongly resembles the Cost/Benefit Analysis template. FINANCIAL STATEMENT Anthony Filipovitch This template displays the current financial position of a firm or department and projects that position one year into the future, given the performance for the last three years. The current position is divided into three parts, income statements, balance sheet, and cash flow. Appendix F 146 FINANCING ALTERNATIVES FOR CAPITAL IMPROVEMENTS Neil Sipe and Robert Hopkins This template assists in finding feasible alternatives for financing capital improvements. Some of these alternatives include ad valorem tax, impact fees, user fees, intergovernmental transfers, and tourist development taxes. FINANCING ALTERNATIVES FOR SERVICES Neil Sipe and Robert Hopkins This template determines a monthly user pay charge for refuse collection and disposal based on the costs associated with differing levels of service for differing customer groups in an effort to reduce property taxes. Similar templates can be used to determine the user pay charges for other services. FISCAL IMPACT ANALYSIS - NONRESIDENTIAL Neil Sipe and Robert Hopkins Planners use this template to examine the impacts of a nonresidential develop-ment project upon local government and school board operating revenues and expenditures. The template requires information about the project, demographic characteristics of the project's employees, and the local government revenue and expenditure patterns. The employment multiplier must be adjusted according to whether the project is a service or an export industry. FISCAL IMPACT ANALYSIS - RESIDENTIAL Neil Sipe and Robert Hopkins This template examines the impacts of a residential development project upon the local government and school board operating revenues and expenditures. The inputs are similar to those in the fiscal impact analysis for nonresidential projects. Appendix F 147 FISCAL IMPACT ASSESSMENT OF A DEVELOPMENT Anthony Filipovitch This template has the same purpose as the fiscal impact analyses, except that it does not calculate the impacts on the school board and it uses different inputs and three different models. One of the different inputs is whether the city is growing or declining. The three models are the Service Standard method, the Comparable City method, and the Employment Anticipation method. F U E L COST AND OPERATOR'S WAGES Jack Reilly Capital District Transportation Authority Albany, New York distributed by the TIME Support Center Spreadsheets are frequently used to perform simple arithmetic calculations such as those found on the back of contest forms. Here is one. Total the number of bus kilometers scheduled, divide it by the expected kilometers/liter, and multiply that figure by the price per liter to determine the fuel cost. Fuel Cost and Operator's  Wages uses this method to calculate the fuel cost. The template can also calculate the operator's wages by substituting the driver's scheduled hours, expected driver produc-tivity, and the wage rate into the above formula. GEOGRAPHICAL INFORMATION MANAGEMENT SYSTEM Catherine J. Carlson Associate Planner New Orleans City Planning Commission 1300 Perdido Street, Suite 9W New Orleans, Louisiana 70112 Spreadsheets help "an office on a shoe-string budget, with limited expertise, time and interest, to automate and carry out its planning functions more efficiently"(Carlson, p. 1). This template assists planning offices catalog and refer-ence essential planning support data, perform data tabulation and statistical analysis, Appendix F 148 present data graphically, facilitate easy sharing of information between cities, prevent duplication of efforts, and avoid the need for an in-house programmer or consultant. This template will soon also include mapping capability, a growth monitoring system, and a system for the prevention of and response to hazardous materials incidents. The template's main menu shown in Figure 27 lists the data files used in this template. To this menu will soon include census data for population projections. By being menu-driven, the template is more user-friendly. User-friendliness is particularly important with some management systems because of their massive size and the large number of people using them. This system's size is so enormous that the template and its data files fill up approximately 20 disks. Currently, the major constraint of the system is the computer's internal memory capacity. IMPACT M A N A G E M E N T P R O G R A M Philip B. Herr Herr Associates 261 Newbury Street Boston, MA-2116 This template estimates selected impacts of development alternatives and is intended for use in evaluating such decisions as rezonings, conditional use approvals, and UDAG proposals. Impact Management Program is the forerunner to Land. INTERSECTION DESIGN AIR QUALITY IMPACT ANALYSIS Cambridge Systematics, Inc. Cambridge, Massachusetts The template requires the user to input the following information for each of the four approaches to the intersection and for both the existing and proposed facilities: the direction of traffic (one-way or two-way), the average number of vehicles per day, the width of the approach, the length of the green time, the cycle length of traffic light, the presence of parking on either side, the percentage of left turns, the percentage of right turns, the percentage of trucks, the type of area, the metropolitan size, the Appendix F 149 percentage of vehicles per day occurring in the peak hour, and the percentage volume growth. The results consist of five rates for three pollutants in three different years. The five rates are the emissions (in grams) per vehicle mile, the total day emissions (in pounds), the difference in the total day emissions between the proposed and the existing roadway, the difference in the total year emissions between the proposed and the existing roadway, and the difference in the total day emissions between the proposed and the base year of the existing facility. The three pollutants are carbon monoxide, non-methane hydrocarbons, and oxides of nitrogen. INTERSECTION CAPACITY Warren Tighe, Lawrence Tai, and Mike Aronson DKS Associates, Oakland, California distributed by the Center for Microcomputers in Transportation This template calculates volume/capacity ratios at signalized intersections. The program is based on the Planning Method of critical movement analysis in the Transportation Board Circular 212. The template contains two macros for printing and one macro for inputting data. The latter macro utilizes the spreadsheet's input mode which guides the user to every (unprotected) cell requiring data. LABOUR ANALYSIS Arthur Mergner Jr. ATE Management & Service Co., Inc. 617 Vine Street, Suite 800 Cincinnati, OH 45202 This template aids in development of labour agreement costs during the labour negotiation process. The template accepts the many variable inputs of a labour agreement: wage rates, fringe benefit costs, vacations, holidays, etc., and produces an immediate estimate of the cost of the proposed contract. A similar template could aid in the planning negotiation process. Appendix F 150 LEFT TURN WARRANT STUDY Lowell Bender and Robert Kochevar This template aids in determining the need for a left turn arrow at a signalized intersection based upon the input of turning movement volumes and left turn vehicle delays. L INK FLOWS Warren Tighe, Lawrence Tai, and Mike Aronson DKS Associates Distributed by the Center for Microcomputers in Transportation This template calculates the source and arrival volumes for links in the TRANSYT program. The documentation claims the "template can save time and minimize mathematical errors when preparing data for input to TRANSYT". The template contains a macro for producing blank input forms and a macro for helping the user input data. A unique feature of this template is that all of the documentation is on-line as a help screen for the user. L O C A L GOVERNMENT BUDGET Anthony Filipovitch This template uses the expense data from the previous two years and the current budget to project the next year's budget. The template is specifically designed to enable the administrator to test "what i f scenarios with the allocation of resources between the departments. L O C A L GOVERNMENT FISCAL TRENDS Neil Sipe and Robert Hopkins This template examines the trends in a local government's annual revenues and expenditures. The template uses four different models: simple percent change, compound percent change, simple percent change in constant dollars, and compound percent change in constant dollars. By modifying the template, planners can examine the growth rates for population, income, employment, and other items. Appendix F 151 RAIL TRANSIT CAPITAL CASH FLOW ANALYSIS Dr. Robert Peskin Peat, Marwick, Mitchell & Co. 1990 K Street, N.W Washington, DC 20006 This template is used to evaluate alternative construction schedules and financ-ing strategies in performing financing feasibility studies for major transit investments. R E A L ESTATE PRO FORMA Anthony Filipovitch Planners often need to know the financial aspects of a real estate development project in order to work out the details of a public/private partnership. This template estimates the income, expenses, and financial soundness of a project using both the "front door" and "back door" approach. The "front door" approach begins with the costs to arrive at the minimum rents needed to break even. The "back door" approach begins with the market value for rents and arrives at the maximum amount of money available for the costs. RIDE C H E C K CALCULATIONS Jack Reilly Capital District Transportation Authority Albany, New York distributed by the TIME Support Center The calculations performed by this template include: distance between stops, cumulative boardings and alightings, passenger load by stop, passenger miles between stops, passenger minutes between stops, maximum passenger load, total boardings, passenger miles, average trip time, and average trip length. ROADWAY DESIGN AIR QUALITY IMPACT ANALYSIS Cambridge Systematics, Inc. Cambridge, Massachusetts This template requires the user to input the following information for both the existing and the proposed roadway: the volume of vehicles, the length of roadway, the Appendix F 153 number of lanes, the type of roadway, the width of the lanes, the presence of obstructions along either side, the width of the shoulder, the truck percentage of the volume of vehicles, the slope of the terrain, the peak hour percentage of the daily volumes, and the expected percentage growth in the peak hour volumes. The results consist of five rates for three pollutants in three different years. The five rates are the emissions (in grams) per vehicle mile, the total day emissions (in pounds), the difference in the total day emissions between the proposed and the existing roadway, the difference in the total year emissions between the proposed and the existing roadway, and the difference in the total day emissions between the proposed and the base year of the existing facility. The three pollutants are carbon monoxide, non-methane hydrocarbons, and oxides of nitrogen. ROUTE EVALUATION Ben Lin Northern Virginia Transportation Commission 2009 N . 14th Street, Suite 300 Arlington, Virginia 22201 This template organizes and processes ridership, mileage, and bus trip schedule information into tables and graphs by route direction. The performance indicators provided in the summary table are total bus trips, total passengers, maximum load, average load/trip, hours/trip, passengers/hour, passengers/mile, passenger miles/trip, passenger miles/hour, and passenger miles/mile. ROUTE PLANNING Tom Hillegass Urban Mass Transportation Administration Washington, D.C. distributed by the TIME Support Center This template assists in planning routes by calculating the costs of different alternatives such as changing the running time or layover. A unique feature of this template is that it freezes titles so that no matter where the user is on the worksheet, Appendix F 154 the bottom left cell always contains the proverbial "bottom line" which in this case is the route's annual cost. SERVICE PLANNING CASE STUDIES Transit Industry Microcomputer Exchange Support This template analyzes a series of service modifications using cost allocations and a supply versus cost model. SIGNALIZED CAPACITY ANALYSIS Brian Jahn Deshazo, Starek & Tang, Inc. 330 Union Station Dallas, Texas, 75202-4802 This template performs the signalized capacity analysis procedure described in the 1985 Highway Capacity Manual. Chapter 9. SIGNAL WARRANT STUDIES James D. Schroll Anne Arundel County, Maryland Department of Public Works distributed by the Center for Microcomputers in Transportation Signal Warrant Studies is composed of two templates. The first template collates and analyzes turning movement volumes collected in 15 minute intervals. In other words, the template adds four 15 minute intervals together to produce hourly totals and for each column of turning movement volumes, provides the sum, maximum value, number of values, and average value. The second template imports the hourly totals from the first template and combines that data with information about the intersection (number of approach lanes, accidents, pedestrians, etc.) to analyze approach volumes and determine whether or not an intersection warrants signalization. Appendix F 155 SPECIAL TRANSPORTATION MONITORING P R O G R A M Tri-Met Portland, Oregon June 1986 distributed by the TIME Support Center This program monitors special transportation providers (private companies and volunteers) for the elderly and handicapped. The template's evaluation summary table gives information on the providers' monthly efficiencies and costs. More specifically, the summary displays by provider: rides per day, rides per hour, average trip length, cost per ride, cost per mile, and cost per hour. A fiscal summary table and twelve graph settings are also included in the template. T A X INCREMENT FINANCING Anthony Filipovitch This template determines the feasibility of tax-increment financing. Tax increment financing is a technique for financing a project from the stream of revenue generated by the project and is obtained through a general obligation bond in which the city accepts the risk of the project. T R A V E L TIME D E L A Y STUDY / SUMMARY Lowell Bender and Robert Kochevar This template takes field data collected during a travel time and delay study and calculates link to link speeds and delay for each trip with an overall summary for all runs, including average delay, average speed, and average travel time. Appendix F 156 APPENDIX G SPREADSHEET ADD ONS AND PERIPHERAL PACKAGES A number of add ons and peripheral packages have been developed to expand the internal limitations of spreadsheets. "Lotus Corporation has counted over a thousand products that enhance 1-2-3 (and also Symphony) in some way." ("46 tools that build on 1-2-3", Business Software. October, 1986, p. 27). Some of these tools are described below. The descriptions are intended not to sell the product, but to give planners a better idea of how far the limits of spreadsheets can be improved upon. AUDITING TOOLS Unlike most spreadsheets, Excelhas many auditing features which help the user understand the template and find causes of errors. Excel tracks cell dependencies, highlights cells that fit the user specified criteria, and differentiates between types of errors. However, the user may require more auditing ability than Excel or their spreadsheet program provides. Cambridge Spreadsheet Analyst, program that handles spreadsheet files, $139.90. It tracks down errors in spreadsheets, analyzes macros, allows the user to document the spreadsheet, and has a MAP mode to show a whole spreadsheet on one screen. Spreadsheet Auditor 3.0, program that handles spreadsheet files, $149. It enables the user to analyze and document the spreadsheet. X-View86, program, $59.95. It can debug spreadsheets. Exsys, program that reads spreadsheet files, $395. It ties an expert system to a database or spreadsheet to analyze the data. It explains why information was needed and how results were reached. C E L L ANNOTATION Most spreadsheets do not have cell annotation. Appendix G 157 Note-it, coresident, $79.95. It can put notes on spreadsheet files and cells. Annotated cells can be highlighted. Smart Notes, coresident, $79.95. It can put notes on spreadsheet files, cells, rows and columns. Cell/Mate, program, $89. Documents cells, rows, columns, and formulas in plain English. It also has spreadsheet auditor capabilities. COMMUNICATIONS Most network configurations, including UBC's MTS, do not allow two users to work on the same file at the same time. Also, transferring templates between different operating systems or programs requires a translation utility not found in most spreadsheets. Close-up, coresident, $440. Lets two planners work on the same template at the same time using two computers linked by modem or cable. Includes a movable "chat" window. DESQview, program, $99.95. It permits data transfer and concurrent operation of a spreadsheet program with up to eight other programs. Symphony Link, program that handles spreadsheet files. It provides micro - to -mainframe communications. COPY PROTECTION Planners may want to make a copy of a spreadsheet program which is copy protected. Copy II PC, program, $39.95. Allows the user to make backup copies and run spreadsheet programs from hard disk without needing a key floppy disk in drive A. DATABASE While many spreadsheet programs have a database module, the module's capabilities are very limited. Appendix G 158 Paradox, program that reads spreadsheet files, $695. It can generate substantial database applications without requiring programming. Q&A, program that reads spreadsheet files, $695. It combines artificially intelligent single-file data management, word processing and report generation. Infocom's Cornerstone, program that reads spreadsheet files, $99.95. DECISION SUPPORT Spreadsheets do not provide an efficient means of determining the optimal solution. To determine the optimal solution with spreadsheets, the user must either use the trial and error method or write a macro which systematically tests all possibilities. What's Best, coresident, $149 - $995. It replaces the trial and error "what i f approach which is time consuming and does not guarantee optimal results, with linear program-ming to calculate the optimal solution. Goal Seeking, coresident, $49.95. It analyzes a template to see what set of conditions will meet a given goal. DESKTOP TOOLS Planners may want to use a module that is not included in the spreadsheet. Desktop tools are programs that can be used without having to leave the spreadsheet program. TimeFrame, coresident. It features an appointment book, calendar, calculator, directory, letter writer, and mail merge/mail labels. Spotlight, coresident. It includes a calendar, notepads, business addresses, phone numbers, calculator, and more. ENGLISH Using plain English instead of symbols makes a program easier to understand and use. However, most spreadsheets do not translate formulas into plain English or make it possible to give instructions in plain English. Appendix G 159 HAL, coresident. Makes it possible to interact with the spreadsheet in plain English. GRAPHICS Most graphics modules in spreadsheet packages do not have a wide selection of types, allow the user to customize graphs, or produce presentation quality graphs. Freelance, program that reads spreadsheet files, $395. It allows the user to customize graphs and charts to a high presentation quality. Graphwriter, program that reads spreadsheet files. It provides 23 different types of graphs and charts. Harvard Presentation Graphics, program that reads spreadsheet files. It transforms data into dazzling graphs. LINKING TEMPLATES Some spreadsheets do not enable the user to link templates together. X, Y, Z.Consolidate, program that reads templates, $145 - $395. It summarizes multiple templates. X, Y,Z:Query, program that reads templates, $195 -$395. It extracts data from multiple files into one. M E M O R Y Memory is one of the two major internal limitations of spreadsheets. Users may desire more memory in the computer or storage device. SQZ, coresident, $79.95. It reduces the amount of disk space needed for spreadsheets by 80 - 95%, and cuts the time to retrieve files, save files and transfer files using a modem. Top Board, computer card. It expands the computer's memory up to 8 megabytes and is compatible with all major spreadsheets. Appendix G 160 MOUSE Although using a mouse makes a spreadsheets easier to use, many spreadsheets are not designed for a mouse. Logicmouse Plus Package, hardware and software, $119. It enables popular spread-sheets to be mouse-driven. PASSWORD PROTECTION Many spreadsheets do not have password protection. Password +, $49. It electronically locks and unlocks spreadsheet files. PRINTING Spreadsheets printing capabilities are limited. For example, most spreadsheets will not print landscape (sideways). Sideways, coresident, $69.95. It allows users to print spreadsheets sideways. Printer Boss V6.0, coresident, $99.95. It allows the users to print spreadsheets sideways. PROJECT M A N A G E M E N T Most spreadsheets do not have a time management or project processor module. Harvard Total Project Manager, handles spreadsheet files, $495. It supports planning, scheduling, controlling and reporting multiple projects, and includes both PERT and Gantt charting. RECORDER Some spreadsheets do not have a learn mode for recording macro commands. Quickcode, coresident, $149. It can record macros as the user types in the keystrokes. Macro +, coresident, $49. It can record macros as the user types in the keystrokes. It also lets you create and save ten "hyperspace" macros that exist in memory, but not in the worksheet. Appendix G 161 REPORT GENERATION Most spreadsheets do not have a report generation module for creating custom-ized reports. 1-2-3 Report Writer, program that reads spreadsheet files. It creates clear, concise, customized reports. Reflex, program that reads spreadsheet files, $149. It writes reports, and includes a database manager and graphic analyses. SLIDES Planners often need to produce slides of templates for presentations. Bell & Howel Color Digital Imager, hardware and software. It can produce profes-sional quality slides quickly and confidentially from popular spreadsheet programs. SPEED Speed is one of the two major internal limitations of spreadsheets. Speed Demon, computer card. It runs spreadsheet programs up to three times faster. ReCalc+, software for math coprocessor, $95. It decreases the time it takes to perform spreadsheet calculations by rerouting the data to Intel's 8087 math coprocessor. STATISTICS Spreadsheets' statistical capabilities are not as sophisticated as large statistical packages. 1,2,3 Forecast, program that reads spreadsheet files, $89.95. It performs regression with 10 independent variables, seasonal analysis and adjustments, smoothing, linear and non - linear trends, decomposition, and complete diagnostics. Statgraphics, program that reads spreadsheet files, $795. It integrates powerful statistics with high resolution color graphics. WORD PROCESSING Most spreadsheets do not have a word processing module. Wordstar 2000 Release 2, program that reads spreadsheet files, $495. Easy 1.5, program that reads spreadsheet files, $99. Appendix G 162 APPENDIX H LIST OF SPREADSHEET PROGRAMS Most planners do not realize just how many spreadsheet programs there are on the market. Table X lists some of the available spreadsheets to give planners a more realistic picture of how many spreadsheets programs exits. Table X also identifies the spreadsheets' system requirement and modules to show the diversity and similarities between spreadsheet programs. The similarity being that all these programs have a worksheet module and are capable of performing the applications described in this thesis. Note however, Table X is for illustrative purposes only since the details on some of the spreadsheets is incomplete. Table X : L is t of Spreadsheet Programs MODULES WS: Worksheet DB: Data base GR: Graphics WP: Word processing CO: Communications OL: Outlining SC: Spell checker PROGRAM Ability Appleworks Assistant Calcstar Click-on Worksheet Corporate MBA Crunch EconoCalc Electric Desk Enable Excel SYSTEM IBM Apple IBM IBM Mac IBM Mac IBM IBM IBM Mac, IBM OT: Other 1 - Sideways 2 - FRED programming language 3 - Time Management 4 - Font designing 5 - Calculator 6 - Project Processor 7 - Form design 8 - Mail merge 9 - Report generator 10 - 3-D graphics 11 - Presentation (Slide show) MODULES WS DB GR WP CO OL SC OT • • • 11 Appendix H 163 P R O G R A M SYSTEM WS DB GR WP CO P L SC OT First Choice IBM • FlashCalc IBM • • Framework IBM • • • • • • 2 InteCalc (3D) IBM • Integrated-7 IBM • • InteMate IBM • • • • • Intuit IBM • • 5,7,8,9 Javelin IBM • • • Jazz Mac • • • • • Knowledge Man IBM • • • • Lotus 1-2-3 IBM • • • Lucid 3-D IBM • • Magic Office Apple • • • • MagicCalc IBM • MathPlan IBM • • MicroPlan IBM • • Microsoft Works Mac, IBM • • • • • MouseCalc Apple • Multiplan IBM, CP7M, • • Apple, Mac Open Access IBM • • • • • 3,5,10 Perfect:Calc IBM • Perfect:family IBM • • • • • • PFSifamily IBM, Apple • • • • • PFS:plan IBM, Apple • Plan Perfect IBM • • PlannerCalc IBM • • PractiCalc Apple • • Propel IBM • • • • • • Quartet Mac • • Quattro IBM • • Silk IBM Smart IBM • • • • • 3,4,5,6 SuperCalc IBM,CP/M, Apple • • • 1 Symphony IBM • • • • • T/Maker HI I B M , CP/M • • • • • VIP Professional Apple • • • VisiCalc IBM, Apple • • • VP Planner IBM • • • Appendix H APPENDIX I A COMPARISON OF TWO SPREADSHEET PROGRAMS Table XI: A Comparison of Two Spreadsheet Programs EXCEL LOTUS 123 ! G E N E R A L S495 Pr ice $495 640K Memory required 256K • Supports mouse 0 • Not copy protected O • Password protection • S P R E A D S H E E T C A P A B I L I T I E S 256 x 16,384 Max. work area: columns x rows 256 x 8,1 92 • Uses 80287 • • Can vary row height 0 • Hides rows o • Hides columns • Many No. of split screens 2 • Merges contents • • Links templates o • Cal ls user-written programs 0 O N - S C R E E N C O N T R O L S • Displays colors and fonts o • Prints from screen o • Offers print preview mode o • Customizes menus 0 • Customizes formats o • Dialogue boxes 0 ; | | C E L L C O N T R O L . 240 Max. no of characters per cell 240 • Cell protection • • Ability to hide contents • Cell Annotation 0 • Track dependencies o • Long cell entries fit in edit window 0 I ! F E A T U R E S • Undo o 7 Formula error values 1 • Performs minimal recalc 0 • Suspends recalc 0 • Search and Replace o ! F U N C T I O N S 131 No. of functions 89 • Customizes functions 0 | • j • j M A C R O L A N G U A G E 355 No. of statements 42 • Learn mode 0 « Runs 123 macros • - I I G R A P H I C S 7 No. of graph types 5 44 No. of graph variations 5 • Can display graphs with worksheet 0 • - Yes 0 - No Table XI gives a comparison between Lotus 123, the most popular PC spreadsheet, and Excel, "the most powerful PC spreadsheet" (Jared Tay-lor, 1987, p. 103). The in-tention of this comparison is not to determine which program is better, but rather, to highlight differ-ences between programs in general. Explanations of the terms used in the com-parison can be found in the glossary provided in Ap-pendix C. Appendix I 165 THE STATISTICAL CAPABILITIES APPENDIX J Spreadsheets can perform some of the simpler statistical functions of the large packages on mainframe computers. For example, Perfect Stat is a disk containing a library of over 70 spreadsheet templates that replicate the power of mainframe computers. Table XII lists the names of these templates. T A B L E XII: THE NAMES OF STATISTICAL TEMPLATES DESCRIPTIVE STATISTICS • Frequency distribution statistics • Summary statistics: raw data • Percentile points and percentile ranks MEASURES OF ASSOCIATION • Correlation matrix • Pearson correlation & regression • Chi Square measures of association • Biserial correlation • Partial correlation • Part correlation • Part - whole correlation • Kendall's Concordance, W • Kendall's Tau PROBABILITY • Permutations & combinations • Binomial probabilities • Normal curve probability • Geometric distribution probabilities • Exponential distribution probabilities • Hypergeometric distribution probabilities • Poisson distribution probabilities • Probability for the F distribution • Probability for the Student T distribution • Probability for the Chi Square distribution Appendix J 166 CONFIDENCE INTERVALS • Confidence intervals for the mean • Confidence intervals for a difference between means • Confidence intervals for a porportion • Confidence intervals for a difference between proportions: independent samples • Confidence intervals for a difference between proportions: correlated smaples • Confidence intervals for standard deviation • Confidence intervals for semi»partial correlation • Confidence intervals for difference between correlations: independent samples • Confidence intervals for diffeerece between correlations: correlated samples HYPOTHESIS TESTING: PARAMETRIC • One way analysis of variance: raw data input • One way analysis of variance: summary data input • N-way analysis of variance • Simple linear regression: raw data input • Simple linear regression: summary data input • One sample test of means • Comparing two means: independant & correlated samples • Test Pearson r, part-whole, Rho, Phi & Point-biserial; test simple regression slope • Test partial and semi partial correlation • One sample test of proportions • Comparising two independent porportions • Comparing two correlated porportions • One sample test of standard deviations • Comparing two variables: independent samples • Comparing two variables: correlated samples • Comparing two correlations: independent samples • Comparing two correlations: correlated samples HYPOTHESIS TESTING: NON'PARAMETRIC • 2x2 Chi square with Yates correction • RxC Chi square • McNemar's test of change • Chi Square test for a uniform distribution • Chi Square goodness of fit test for a single sample • Extension of the median test • Chi square test for a normal curve fit • Chi square test for an exponential curve fit • Kolmogorov - Smirnov one sample test • Kolmogorov - Smirnov two sample test Appendix J • Wald - Wolfitz two sample runs test • Mann-Whitney U»test • Wikoxin matched»pairs signed ranks test • Cochran Q-test • Friedman two-way analysis of variance • Kruskal-Wallis one-way analysis of variance • Randomization test for two independent samples • Randomization test for two correlated samples REGRESSION • Simple linear regression: raw data input • Simple linear regression: summary data input • Hierarchical multiple regression PSYCOHOMETRIC PROCEDURES • Spearman-Brown Prophecy formula • Generalized SpearmamBrown formula • Coefficient Alpha • Estimates of true scores • Correction for attenuation • Reliability of linear combinations • Correlations among sums • Statistical transformations SOURCE: Walter W. Hudson, Ph.D. Perfect Stat. Users' guide Thorn EMI ComputerSoftware, Inc. 3187-C Airway Avenue Costa Mesa, California, 92626 Appendix J APPENDIX K SOLVING UNSOLVABLE EQUATIONS Spreadsheets can seemingly magically solve equations where it is impossible to isolate the unknown variable by itself on the left side of the equation. The magic is simply the old trial and errormethod using a 'what i f question inside a do loop routine. For example, in the equation Y + Y A 3 = 30 (where A means "to the power of), the spreadsheet might start off by asking "what if Y= 1; would the two sides of the equation be equal. If the two sides are equal then Y must be 1, and if they are not equal than the computer will systematically continue asking 'what i f questions using a different value for Y each time until the two sides are equal, which in this case is when Y=3. This technique is useful for solving many financial and goal seeking problems such as finding the interest (INT) from the equation of an annuity with a balloon payment (Ron Person, 1986, p. 1): PV = @PV(PMT, INT, N) + BLN * (1 + INT)A-N Appendix K 169 APPENDIX L PASCAL P R O G R A M FOR THE COHORT SURVIVAL MODEL program COHORT_SURVIVAL; (* a program to project future populations *) const INTERVALS = 5; YEARS = 5; type VECTOR = array [1 .. INTERVALS] of REAL; var MRATE: array [1 .. INTERVALS, 1 . . .INTERVALS] of REAL; FRATE: array [1 .. INTERVALS, 1 . . .INTERVALS] of REAL; MCOHORT: VECTOR; FCOHORT: VECTOR; TEMP: VECTOR; I, J, K, L: INTEGER; procedure HISTOGRAM(MALES, FEMALES: VECTOR; T: INTEGER); const SCALE = 1.0; var STARS: PACKED array [1.. 25] of CHAR; var I, J: INTEGER; var TEMP, MTOTAL, FTOTAL: REAL; begin (* Histogram for population peramid *) MTOTAL := 0; FTOTAL := 0; for I := 1 TO INTERVALS do begin MTOTAL := MTOTAL + FEMALES [I]; FTOTAL := FTOTAL + MALES [I]; end; TEMP := mtotal + ftotal; Appendix L 170 WRITE(TOPULATION FOR TIME t + \ T: 1,' IS \ TEMP: 7: 3); WRITELN(' '); for i := 1 to intervals do begin TEMP := FEMALES [I]; forJ:=lT0 25do STARS[J] :=' '; WRITE(INTERVALS + 1 -1: 2, MALES [I]: 10: 3); for J := 25 DOWNTO 1 do begin ifMALES[I]> SCALE then STARS [J] :='*'; MALES [I] := MALES[IJ - SCALE end; WRITEC STARS, T); forJ:=lT0 25do STARS[J] :=' '; for J := 1 TO 25 do begin if FEMALES [I] > SCALE then STARS[J] := '*'; FEMALES[I] := F E M A L E S U J - SCALE end; WRITELN(STARS,' ', TEMP: 10: 3); end; WRTTELNC FTOTAL: 10: 3,' MALES I FEMALES SCALE: 4: 3,' ', MTOTAL: 10: 3); WRITELN end;HISTOGRAM:; begin (* MAIN PROGRAM *) WRrTELN('COHORT-SURViVAL PROGRAM WRITELNC by Mitchell Kenyon'); WRITELNC March, 1985'); WRITELN; WRITELNC AGE COHORTS USED:'); WRITELNC 1. 0- 14 years'); wri te lnC 2. 15 - 29 years'); wri te lnC 3. 30 - 44 years'); writelnC 4. 45 - 59 years'); writelnC 5. 60 + years'); writeln;Writeln('POPULATION SCALE: 1.000 = 1,000 people'); Writeln; (* READ survival rate arrays for males and then females *) for I := 1 TO INTERVALS do begin for J := 1 TO INTERVALS do Appendix L READ(MRATE[I, JJ); READLN end; for I := 1 TO INTERVALS do begin for J := 1 TO INTERVALS do READ(FRATE[L J]); READLN end; (* ECHO INPUT *) WRITELNCMALE SURVIVAL RATES '); for I := 1 TO INTERVALS do begin WRITE(INTERVALS + 1-1: 2); for J := 1 TO INTERVALS do WRITE(MR ATE [I, JJ: 10: 2); WRITELN end; WRITELN; WRITELN('FEMALE SURVIVAL RATES '); for I := 1 TO INTERVALS do begin WRlTE(INTERVALS + 1-1: 2); for J := 1 TO INTERVALS do WRITE(FRATE[I, JJ: 10: 2); WRITELN end; WRITELN; (* read population ('000) for males by cohort and then females Starting with the oldest population cohort *) for I := 1 TO INTERVALS do RE AD(MCOHORT[I]); READLN; for I := 1 TO INTERVALS do READ(FCOHORT[IJ); READLN; HISTOGRAM(MCOHORT, FCOHORT, 0); for L := 1 TO YEARS do begin (* calculate next time period's population using matrix multiplication *) (* calculate new female population *) for I := 1 TO INTERVALS do TEMP[IJ :=0; for I := 1 TO INTERVALS do for K := 1 TO INTERVALS do TEMP[I] := TEMP[I] + FRATE[I, K] * FCOHORT[K]; for I := 1 TO INTERVALS do FCOHORT[I] :=TEMP[I]; (* calculate new male population *) for I := 1 TO INTERVALS do TEMP[I] := 0; Appendix L for I := 1 TO INTERVALS do for K := 1 TO INTERVALS do TEMP[I] := TEMP[I] + MRATE [I, K] * MCOHORT[K]; for I := 1 TO INTERVALS do MCOHORT[I] := TEMP[I]; fflSTOGRAM(MCOHORT, FCOHORT, L); end; WRTTELN; WRTTELNC***** END OF PROGRAM *****') end ;COHORT_SURVIVAL:. /EXEC .2 .5 0 0 0 0 0 .65 0 0 0 0 0 .75 0 0 0 0 0 .85 0 0 .459 1.02 0 .4 .6 0 0 0 0 0 .7 0 0 0 0 0 .8 0 0 0 0 0 .9 0 0 .441 .98 0 3 5 12 10 20 3 5 12 10 20 Appendix L 


Citation Scheme:


Citations by CSL (citeproc-js)

Usage Statistics



Customize your widget with the following options, then copy and paste the code below into the HTML of your page to embed this item in your website.
                            <div id="ubcOpenCollectionsWidgetDisplay">
                            <script id="ubcOpenCollectionsWidget"
                            async >
IIIF logo Our image viewer uses the IIIF 2.0 standard. To load this item in other compatible viewers, use this url:


Related Items