Excel Basics to Blackbelt: An Accelerated Guide to Decision Support Designs

343 Pages·2008·14.33 MB·English

by  Elliot Bendoly

This page intentionally left blank
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
EXCEL BASICS TO BLACKBELT
ExcelBasicstoBlackbeltisintendedtoserveasanacceleratedguidetodeci-
sion support designs. Its structure is designed to enhance the skills in Excel
of those who have never used it for anything but possibly storing phone
numbers, enabling them to reach a level of mastery that will allow them to
developuserinterfacesandautomatedapplications.Toaccomplishthis,the
majorthemeofthetextistheintegrationofthebasics;asaresult,readerswill
beabletodevelopdecisionsupporttoolsthatareatoncehighlyintuitivefrom
a working components perspective but also highly significant from the per-
spectiveofpracticaluseanddistribution.Applicationsintegrationdiscussed
includestheuseofMicroSoftMapPoint,XLStat,andRISKOptimizer,aswell
as how to leverage Excel’s iteration mode, Web queries, Visual Basic code,
andinterfacedevelopment.Thereareampleexamplesthroughoutthetext.
FortheaccompanyingWebsitematerial,pleasegoto
http://www.cambridge.org/bendoly.
ElliotBendolyisanassociateprofessoratEmoryUniversity’sGoizuetaBusi-
ness School. He holds a Ph.D. from Indiana University in operations man-
agementanddecisionscienceswithaninformationsystemsspecializationin
enterpriseresourceplanning(ERP)andknowledgemanagement.Professor
Bendoly serves on the editorial boards of the Journal of Operations Man-
agement and Decision Sciences. His research has been published in leading
academic journals, including the Journal of Operations Management, Pro-
duction and Operations Management, MIS Quarterly, Information Systems
Research,JournalofAppliedPsychology,DecisionSciences,andtheJournal
of Service Research. He is also the coeditor of Strategic ERP Extension and
Use. He has served as the academic liaison for APICS: The Association for
Operations Management and is a cofounder of the Behavioral Dynamics
inOperationsManagementNetwork.Hehaslecturedondecisionsupportat
research institutions such as Harvard University, as well as to practitioners
atfirmssuchasATKearneyandPriceWaterhouseCoopersInc.
i
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
ii
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
EXCEL BASICS TO BLACKBELT
An Accelerated Guide to Decision Support Designs
ELLIOT BENDOLY
EmoryUniversity
iii
CAMBRIDGEUNIVERSITY PRESS
Cambridge, New York, Melbourne, Madrid, Cape Town, Singapore, São Paulo
Cambridge University Press
The Edinburgh Building, Cambridge CB2 8RU, UK
Published in the United States of America by Cambridge University Press, New York
www.cambridge.org
Information on this title: www.cambridge.org/9780521889056
© Elliot Bendoly 2008
This publication is in copyright. Subject to statutory exception and to the provision of
relevant collective licensing agreements, no reproduction of any part may take place
without the written permission of Cambridge University Press.
First published in print format 2008
ISBN-13 978-0-511-41415-2 eBook (EBL)
ISBN-13 978-0-521-88905-6 hardback
Cambridge University Press has no responsibility for the persistence or accuracy of urls
for external or third-party internet websites referred to in this publication, and does not
guarantee that any content on such websites is, or will remain, accurate or appropriate.
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
Contents
SECTION 1. GETTING ORIENTED
1. NecessaryFoundationsforDecisionSupport page 3
2. TheDevelopmentEnvironment 8
2.1. FixedData 8
2.2. Formatting 12
2.3. Labeling(Naming) 15
2.4. Comments 18
2.5. Hyperlinks 19
2.6. Formulae 19
2.7. CopyingContentandFormats 23
2.8. Built-InTools 23
Chapter2Supplement:LogicandStructurein
ConditionalStatements 27
PracticeProblems 33
3. GettingData–Acquisition,Linkage,andGeneration 34
3.1. TextFileImportsandBasicTableTransfers 34
3.2. MoreSophisticatedApplicationTransfers 36
3.3. OnlineDataAcquisition 47
3.4. SimulatingData:TheBasics 49
3.5. LivingDataRecords:TheBasics 53
3.6. LivingRecordsinPractice 56
PracticeProblems 58
SECTION 2. HARVESTING INTELLIGENCE
4. StructuringProblemsandOptionVisualization 63
4.1. ValueofDataVisualization 64
4.2. SelectivePruningforPresentationandAnalysis 78
v
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
vi Contents
4.3. VisualizingConstraints 82
PracticeProblems 87
5. SimplificationTactics 89
5.1. HeuristicsinDecision-MakingPractice 90
5.2. HeuristicsAppliedtoDataRationalization 94
5.3. AttributeGroupingApproaches 95
5.4. DataGroupingApproaches 102
5.5. GivingFormtoFutureCategorization 112
CitedReferences 116
Chapter5Supplement:MakingHeuristicsAutomatic
(theNon-ElegantWay) 116
PracticeProblems 119
6. TheAnalyticsofOptimization 122
6.1. OptimizationwithSolver 122
6.2. DeeperInsightsintoOptimizationSolutions 133
PracticeProblems 150
7. ComplexOptimization 154
7.1. HowSolver“Solves” 154
7.2. TheBenefitofAlternateOptimizationOptions 157
Chapter7Supplement:APrimeronGeneticAlgorithms 173
PracticeProblems 179
SECTION 3. LEVERAGING DYNAMIC ANALYSIS
8. ControlledSimulationAnalysis 183
8.1. ApproachestotheUseofSimulationinAnalysis 184
8.2. AssessingSimulatedVariants 187
8.3. AssessingSystemSimulations 192
8.4. AnIntroductiontoStochasticSystemStructures 199
Chapter8Supplement:SimulationControlMadeFriendly 202
PracticeProblems 207
9. ScenarioGenerationandOptimization 209
9.1. BasicSimulationOptimizationCapabilities 210
9.2. OptimizationofSimulatedVariants 213
9.3. OptimizationofSystemSimulations 216
PracticeProblem 227
10. VisualizingComplexAnalyticalDynamics 229
10.1. RandomWalks 230
10.2. FrictionlessBoxes 231
10.3. Path-DirectedFlows 232
Chapter10Supplement:VisuallyDerivedPaths 240
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
Contents vii
SECTION4. ADVANCEDAUTOMATIONANDINTERFACING
11. VBEditingandCodeDevelopment 249
11.1. TheVisualBasicEditor 249
11.2. ObjectManipulations 254
11.3. SyntaxandCoding 259
11.4. User-DefinedFunctions 273
PracticeProblems 279
12. AutomatingApplicationCalls 281
12.1. CallstoMapPoint 281
12.2. CallstoSolver 285
12.3. CallstoRiskOptimizer 288
12.4. CallstoXLStat 294
12.5. AFinalNoteontheValueofLinguistics 296
PracticeProblems 296
13. GuidedandUser-FriendlyInterfaces 298
13.1. InterfaceLockingandProtecting 300
13.2. DynamicInterfacing:Pop-Ups/Dialogs 303
13.3. CustomizingPrimaryExcelInterfaces 306
13.4. Don’tGiveUpontheSpreadsheet 314
PracticeProblem 314
GlossaryofKeyTerms 317
Appendix–Shortcut(HotKey)Reference 325
Index 327
P1:SFK
9780521889056pre CUUS195/Bendoly 9780521889056 June14,2008 19:16
viii