Attention customers: online shopping is now available exclusively through our main website: http://www.manning.com. Thank you.

Professional's Guide to Robust Spreadsheets

Manning Publications Co.
209 Bruce Park Avenue
Greenwich, CT 06830

Professional's Guide to Robust Spreadsheets With Examples in Lotus 1-2-3 and Microsoft Excel Ronny Richardson 1996 | 301 pages ISBN: 013262320X
	$29.95	Softbound print book	Out of print (?)

Table of Contents

List of Tables				xi
Acknowledgments				xiii
Introduction				xv
	The Purpose of This Book			xv
	Who Should Read This Book?			xvi
	How the Book is Organized			xvi
	A Few Conventions			xviii
	Showing Lotus and Excel Differences			xix
	Getting Copies of the Worksheets Used in this Book			xx
	About the Author			xx
	Last Notes			xxi
Chapter 1
Initial Design				1
	1.1	Define the Strategic Purpose		2
	1.2	Define the Output		2
	1.3	Define the Inputs		3
	1.4	Define the Calculations		4
	1.5	Develop a System Diagram		4
	1.6	Putting it Together		4
	1.7	Final Considerations		5
		1.7.1	Software Standards	5
		1.7.2	User Standards	6
		1.7.3	Worksheet Standards	6
	Summary			7
Chapter 2
Basic Layout
	2.1	Dividing the Worksheet Into Sections		9
	2.2	Arranging the Sections of the Worksheet		10
		2.2.1	Top-to-Bottom Arranged Worksheets	11
		2.2.2	Left-to-Right Arranged Worksheets	12
		2.2.3	Ad-hoc Arranged Worksheets	13
		2.2.4	In a Two-Dimensional Environment the Diagonal Arrangement is Best	13
		2.2.5	Finding Sections in a Diagonal Arrangement	15
		2.2.6	You Can Always Rearrange at the End	16
		2.2.7	In a Three-Dimensional Environment	16
	2.3	Intended Audience for a Worksheet		19
	Summary			21
Chapter 3
Input Section
	3.1	Getting Information from the User		23
	3.2	Developing a User Input Section		24
		3.2.1	Column A: Numbers	25
		3.2.2	Column B: The Questions	26
		3.2.3	Column C: The Answers	26
		3.2.4	Column D: Messages: Communicating With the User	26
		3.2.5	Column E: The Values Actually Used	29
	3.3	Taking Special Actions When the Spreadsheet First Starts		32
		3.3.1	Automatically Executed Macro Overview	32
		3.3.2	Reasons to Use an Automatically Executed Macro	32
	3.4	Example: A Biorhythm Chart		34
		3.4.1	The Opening Screen	34
		3.4.2	The Data Entry Section	35
		3.4.3	The Month	36
		3.4.4	The Day	37
		3.4.5	The Year	39
		3.4.6	Biorhythm Chart Input Conclusion	41
	3.5	Do I Really Need All These Prompts and Error Messages?		41
	3.6	Batch Entry		42
	Summary			43
Chapter 4
Processing Section
	4.1	Lay Out Calculations Cleanly		45
	4.2	Remember Precedence		47
	4.3	Variable Assumptions		48
	4.4	Using Range Names		49
	4.5	Do Not Erase With the Space Bar		50
	4.6	Using Absolute References		51
	4.7	Using Spreadsheet Linking		51
		4.7.1	Worksheet Consolidation	52
		4.7.2	Sharing Data	52
		4.7.3	A Problem With Worksheet Links	53
	4.8	Protecting Calculations		55
	Summary			56
Chapter 5
Output Section
	5.1	Deliver Decisions, Do Not Deliver Data		57
		5.1.1	Report Layout	58
		5.1.2	Report Structure	64
	5.2	Formatting Your Worksheet		65
		5.2.1	Basic Formatting Review	66
		5.2.2	Advanced Formatting	68
		5.2.3	Using Named Styles	73
		5.2.4	Using Special Characters in Your Lotus Worksheets	75
		5.2.5	Using Selected Character Formatting in Excel Worksheets	78
		5.2.6	A Word of Caution Regarding Formatting	78
	5.3	Working With Charts		79
		5.3.1	Charts In Lotus	80
		5.3.2	Charts in Excel	83
	5.4	Reports Outside of Your Spreadsheet Program		84
		5.4.1	Object Linking and Embedding	85
		5.4.2	A Warning Regarding Linking	86
		5.4.3	Creating the Worksheet	86
		5.4.4	Linking Steps	88
	5.4.5	Other Linking Considerations		89
	5.5	Paperless Reports		91
		5.5.1	A Few Notes on Writing an Effective Presentation	92
		5.5.2	Anatomy of a Slide	93
		5.5.3	Lotus and Excel are not Presentation Packages!	94
		5.5.4	Configuring the Worksheet	95
		5.5.5	Getting the Data into the Worksheet	97
		5.5.6	An Example	98
		5.5.7	Making Presentations Remotely	102
	Summary			103
Chapter 6
Macro Section
	6.1	What Is a Macro?		105
	6.2	Why Write Macros?		105
		6.2.1	Automating Repetitive Tasks	106
		6.2.2	Performing Tricky Tasks	106
		6.2.3	Error Prevention	106
	6.3	The Macro Development Process		107
	6.4	Lotus Macros		108
		6.4.1	Formatting and Documenting Your Macro	108
		6.4.2	Writing Your First Macro	110
		6.4.3	Recording a Macro	111
		6.4.4	Using Commands in Macros	113
		6.4.5	Macro Command Syntax	115
		6.4.6	Some Useful Macro Techniques	116
		6.4.7	Debugging Macros	128
		6.4.8	Macro Management	132
	6.5	Excel Macros		137
		6.5.1	Understanding the Terminology	137
		6.5.2	Recording Your First Macro	141
		6.5.3	Running Your First Macro	144
		6.5.4	Variables	144
		6.5.5	Controlling Code Flow	145
		6.5.6	Interacting With the User	148
		6.5.7	Macro Menus	151
		6.5.8	Modifying the Menu System	152
		6.5.9	Debugging Macros	152
	6.6	Macro Viruses		155
	Summary			155
Chapter 7
Worksheet Quality Control				159
	7.1	What Management Must Do		161
	7.2	What The Model Builder Must Do		162
		7.2.1	Building Error-Resistant Worksheets	162
		7.2.2	Avoiding Errors When Upgrading a Worksheet	166
		7.2.3	Testing with a Set of Standard Scenarios	167
		7.2.4	Testing with Extreme Data	168
		7.2.5	Auditing Your Worksheets	169
	7.3	Working With Someone Else's Model		175
		7.3.1	Up-Front Work	176
		7.3.2	Quick Tour	176
		7.3.3	Initial Documentation	177
		7.3.4	Formulas Audit	177
		7.3.5	Understanding the Worksheet	178
		7.3.6	Final Documentation	180
	7.4	The Bottom Line		181
	Summary			181
Chapter 8
Documenting the Worksheet				183
	8.1	In a Corporate Setting		184
	8.2	External Documentation		184
		8.2.1	Executive Summary	184
		8.2.2	Major Assumptions	185
		8.2.3	Table of Range Names	185
		8.2.4	Table of Macros	185
		8.2.5	Formulas	186
		8.2.6	Sample Inputs and Outputs	186
		8.2.7	Logical Documentation	186
	8.3	Internal Documentation		186
		8.3.1	Documenting Formulas	186
		8.3.2	Documenting Macros	187
	8.4	Users' Group		188
		8.4.1	Software Depository	188
		8.4.2	Meetings on Using the Model	189
		8.4.3	Model Modifications	189
		8.4.4	Synergy	189
	Summary			190
Chapter 9
Worksheet Standards
	9.1	Learning New Models		191
	9.2	Overall Organizational Standards		192
		9.2.1	Organizational Standards for Range Names	192
		9.2.2	Organizational Standards for Labels	192
	9.3	Input Standards		193
		9.3.1	Visual	193
		9.3.2	Data Verification	195
	9.4	Processing Standards		195
		9.4.1	Technical Standards	196
		9.4.2	Formatting Standards	196
	9.5	Output Standards		197
		9.5.1	Structure	197
		9.5.2	Visual Output	198
	Summary			200
Chapter 10
Corporate Concerns
	10.1	Is a Spreadsheet the Right Tool?		203
		10.1.1	When Spreadsheets Excel	204
		10.1.2	When to Consider Writing a Program	205
		10.1.3	The Result of Using a Spreadsheet Rather Than      a Programming Language	206
	10.2	Who Should Write the Model?		208
	Summary			210
Chapter 11
Batch Processing
	11.1	When to Consider Batch Entry		211
	11.2	Modifying the Worksheet for Batch Entry		212
	11.3	An Example		213
		11.3.1	Without Batch Entry	213
		11.3.2	Adding the Look-up Table	215
		11.3.3	Adding the Macro in Lotus	217
		11.3.4	Adding the Macro in Excel	219
	11.4	Batch Entry Problem		219
	11.5	Built-in Batch Entry Support		220
		11.5.1	Version Manager in Lotus	220
		11.5.2	Scenarios in Excel	225
	11.6	Deciding Between Macro-Based Batch Entry and      Built-in Batch Entry		227
	Summary			228
Appendix A
Discussion of Selected Lotus and Excel Functions				229
Appendix B
Documentation for Selected Worksheets				235
Appendix C
Chapter 6 Tables				257
Index				275

---

List of Tables

Table 2.1	Deciding which features to include with which types of worksheets	21
Table 3.1	Formulas used to generate error messages in the      heat pump model input area	28
Table 3.2	Formulas used to generate the values used in the      heat pump model input area	31
Table 4.1	Cells used to calculate gas bill using multiple cells	47
Table 4.2	Operation precedence	48
Table 4.3	Formulas used to calculate gas bills using multiple inputs	50
Table 5.1	Common compose sequences	77
Table 5.2	Compose sequences for common formatting operations	78
Table 5.3	Suggested column widths for slides	96
Table 6.1	Macro Commands for manipulating charts	258
Table 6.2	Macro commands for data manipulation	259
Table 6.3	Macro commands for manipulating a database	260
Table 6.4	Macro commands for direct data exchange and      object linking and embedding	261
Table 6.5	Macro commands for editing worksheets	262
Table 6.6	Macro commands for manipulating files	263
Table 6.7	Macro commands for controlling the      flow of macro programs	264
Table 6.8	Macro commands that duplicate user keystrokes	265
Table 6.9	Macro commands for navigating the worksheet	267
Table 6.10	Macro commands for manipulating ranges	268
Table 6.11	Macro commands for using the Solver and Backsolver tools	269
Table 6.12	Macro commands for manipulating worksheet styles	270
Table 6.13	Macro commands for manipulating text files	271
Table 6.14	Macro commands for working with tools	271
Table 6.15	Macro commands for manipulating the user environment	272
Table 6.16	Macro commands for managing scenarios	274
Table 6.17	Macro commands that control window and displays	274
Table 6.18	Menu options for a macro example with explanation and macro code	117
Table 6.19	Common properties	140
Table 7.1	Common spreadsheet problems	179
Table 11.1	Selected formulas for BREAK-1.WK4	213
Table 11.2	Formulas in BREAK-2.WK4 used to pull inputs from data table	217
Table B2.1	Range names used in BIORYTHM.WK4	239
Table B2.2	Range names used in BREAK-3.WK4	242
Table B2.3	Range names used in BIORYTHM.WK4	248
Table B2.4	Range names used in BREAK-3.XLS	251

DESCRIPTION

This book teaches you how to develop large, bug-free, easy-to-understand, and easy-to-maintain spreadsheets. It explains how techniques that work on small spreadsheets fail on larger projects. Methods from this book can be critical when the developer of the spreadsheet isn't the one using or maintaining it. The Professional's Guide to Robust Spreadsheets is rich with real-life examples in both Lotus 1-2-3 and Microsoft Excel. This book helps you:

• Design for ease of use
• Plan for trouble-free maintenance
• Reduce programming errors
• Write spreadsheets that self-detect and correct wrong data
• Write effective reports
• Bring quality control to your design
• Set organizational standards for spreadsheet design
• Automate routine tasks with macros

There's a real art to constructing effective spreadsheets, and a few lessons from this book will make you a master.

WHAT THE EXPERTS SAY ABOUT THIS BOOK...

"I found myself marvelling at how relatively profound points could be so clearly expressed. This refreshing clarity made the book fun as well as educational."
--Allan G. Atherton, Computer Consultant

"This is the only book I've read that shows how to write better spreadsheets, rather than simply use a spreadsheet program."
--Peter J. Luise, President, Renaissance Soluations

ABOUT THE AUTHOR...

Ronny Richardson, now a faculty member at Southern Polytechnic State University, was formerly a financial analyst at a Fortune 100 company where he developed and maintained thousands of spreadsheets, large and small. He has written 20 computer books, including several best sellers.

Home | Catalog | Privacy Policy | About Manning

© 2003-2006 Manning Publications Co.