Excel Basics to Blackbelt 2nd Edition by Elliot Bendoly ISBN 1107625521 978-1107625525

Section 1 Getting Oriented

1 Necessary Foundations for Decision Support

2 The Development Environment

2.1 Fixed Data

2.2 Formatting

2.2.1 Static Formatting for Cells

2.2.2 Conditional Formatting

2.3 Labeling (Naming)

2.3.1 Handling Name/Label Typos and Changes

2.3.2 Cell Range Labels

2.3.3 Worksheet Labels

2.3.4 Object Names

2.4 Comments

2.5 Hyperlinks

2.6 Formulae

2.7 Copying Content and Formats

2.8 Built-In Tools and Matrix Functions

Supplement: Logic and Structure in Conditional Statements

Practice Problems

Practice 2.1

Practice 2.2

3 Getting Data: Acquisition, Linkage, and Generation

3.1 Text File Imports and Basic Table Transfers

3.2 More Sophisticated Application Transfers

3.2.1 MapPoint Data Sources

3.2.2 Pulling Data from MapPoint

3.3 Online Data Acquisition

3.3.1 Generalized Web-Data Pulls

3.3.2 Specialized Web-Data Pulls

3.4 Simulating Continuous Data: The Basics

3.4.1 Uniformly Distributed Random Values

3.4.2 Triangularly Distributed Random Values

3.4.3 Normally Distributed Random Values

3.5 Discrete Random Number Generation

3.5.1 Uniform Discrete Randoms

3.5.2 Bernoulli Discrete Randoms

3.5.3 Custom Discrete Randoms

3.6 Living Data Records: The Basics

3.7 Living Records in Practice

3.7.1 Example: Simulated Histories (A Preview of System Simulations)

3.7.2 Example: Web-Import Histories

Supplement: Unique Data Generation and Sharing Tactics

Practice Problems

Practice 3.1

Practice 3.2

Practice 3.3

Section 2 Harvesting Intelligence

4 Structuring Problems and Option Visualization

4.1 Value of Data Visualization

4.1.1 Bar Charts

4.1.1.1 The Basics

4.1.1.2 Compound (Stacked) Bar Charts

4.1.2 Cell-Embedded Sparklines

4.2 Visualizing Continuous Data

4.2.1 Adding in Trendlines and Their Statistics

4.2.2 Kicking Up Scatter Plot Graphics

4.2.3 Depicting Flow in Scatter Plots

4.3 Less Conventional Depictions of Means and Variance

4.4 Depictions in More Than Two Dimensions

4.4.1 Surface Templates and Three-Dimensional Plots

4.4.2 Basic Cartesian Bubble Charts

4.4.3 Polar Bubble Charts

4.4.4 Ternary Bubble Charts

4.4.5 Depicting Depth with Bubble Charts

4.5 Complex-Form Heat Mapping

4.5.1 Basic Heat Mapping

4.5.2 Mapping Excel Data in MapPoint

4.5.3 Excel (MapPoint): Embedded Maps

4.5.4 Routing Information and Insights through MapPoint

4.5.5 WorldWide Telescope

4.6 Selective Pruning for Presentation and Analysis

4.6.1 How to Build and Modify PivotTables

4.6.2 Selective Pruning by Row and Column

4.6.3 PivotCharts

4.7 Visualizing Constraints

Practice Problems

Practice 4.1

Practice 4.2

Practice 4.3

5 Simplification Tactics

5.1 Heuristics in Decision-Making Practice

5.1.1 The Recognition Heuristic

5.1.2 Nearest Next: A Routing Heuristic

5.1.3 MinSlack and SPT: Two Project-Management Heuristics

5.1.4 The Punchline: Relevance to DSS Designs

5.2 Heuristics Applied to Data Rationalization

5.3 Attribute Grouping Approaches

5.3.1 Trivial Consolidation Approaches

5.3.2 Consolidations Using Statistically Derived Weightings

5.4 Data Grouping Approaches

5.4.1 Quartile-Based Categorization

5.4.2 Categorization Based on p-levels and z-scores

5.4.3 Multidimensional Bins

5.4.4 Cluster Analysis for Multidimensional Splits

5.5 Giving Form to Future Categorization

Supplement: Making Heuristics Automatic (the Inelegant Way)

Second Stage of the Nearest Next Heuristic Automated

Practice Problems

Practice 5.1

Practice 5.2

Practice 5.3 (Challenge)

6 The Analytics of Optimization

6.1 Optimization with Solver

6.1.1 Example #1: Atlanta Professional Training

6.1.1.1 Structuring Models for Optimization

6.1.1.2 Getting to the Solution

6.1.2 Example #2: RunFast, Inc.

6.1.3 Example #3: Jumping Java

6.1.4 Example #4: Madrenko Fine Art Galleries

6.2 Deeper Insights into Optimization Solutions

6.2.1 Example #5: Fashion Denim, Inc.

6.2.2 Example #6: Lobo’s Cantina

6.2.2.1 Interpreting Results

6.2.3 Example #7: Strategic Focus for Investment Firms

Supplement: The OUtCoMES Roadmap

Practice Problems

Practice 6.1

Practice 6.2

Practice 6.3

7 Complex Optimization

7.1 How Solver “Solves”

7.1.1 Problems with Multimodality

7.1.2 Problems with Discontinuity

7.2 The Benefit of Alternate Optimization Options

7.2.1 Problems with Vehicle Routing

7.2.2 RISKOptimizer on Vehicle Routing

7.2.3 RISKOptimizer on Cluster and Group Development

7.2.4 RISKOptimizer on Schedule Development

Supplement: A Primer on Genetic Algorithms

Genetic Algorithms in General

Genetic Algorithm Options in RISKOptimizer

Practice Problems

Practice 7.1

Practice 7.2

Section 3 Leveraging Dynamic Analysis

8 Controlled Simulation Analysis

8.1 Approaches to the Use of Simulation in Analysis

8.1.1 Simulated Variants

8.1.2 System Simulations

8.1.3 Basics of Simulation Design

8.2 Assessing Simulated Variants

8.2.1 Lobos Cantina Revisited: Simulations in Revenue Management

8.2.2 Perfumery at Phipps: Uncertain Cost and Pricing Effects

8.2.3 Acquiring a Diamond in the Rough: ROI Windows Given Yearly Variation

8.2.4 Design-of-Experiment Approaches to Data Table Use

8.3 Assessing System Simulations

8.4 An Introduction to Stochastic System Structures

Supplement: Simulation Control Made Friendly

Practice Problems

Practice 8.1

Practice 8.2

9 Simulation Search, Optimization, and Reporting

9.1 Basic Simulation Optimization Capabilities

9.1.1 Optimization Stopping Conditions

9.1.2 Simulation Stopping Conditions

9.2 Optimization of Simulated Variants

9.2.1 Using Averages from Data Tables in RISKOptimization

9.2.2 Using RISKOptimizer Iterations for the Same Result (without a Data Table)

9.3 Optimization of System Simulations

9.3.1 RISKOptimizer with Calls to a Reset Macro

9.3.1.1 Use under Excel’s Multiiteration Setting

9.3.1.2 Using RISKOptimizers Built-In Multiiteration Mechanism

9.3.2 RISKOptimizer with Calls to a Recalculation Macro

Practice Problem

10 Visualizing Complex Analytical Dynamics

10.1 Random Walks

10.2 Frictionless Boxes

10.3 Dynamics in Google Documents Visualization

10.4 Path-Directed Flows

10.4.1 An Introduction to Visualizing Path-Directed Flows

10.4.2 Visualizing Progress along Paths

10.4.3 Custom Path Visualizations: Lobo’s Floor Plan Revisited

Supplement: Visually Derived Paths

Plot-Pulled Extractions

Drawn-Path Extractions

Section 4 Advanced Automation and Interfacing

11 Visual Basic Editing and Code Development

11.1 The Visual Basic Editor

11.1.1 Confronting Code

11.1.2 Walking through Code and Checking for Bugs

11.2 Additional VBA Capabilities: Object Manipulations

11.2.1 Incorporating External Objects into Workbooks

11.2.2 Object Macros

11.2.3 Turning on Embedded Browsers

11.2.4 A Final Example: Regression Revisited

11.3 Syntax and Storage

11.3.1 Introduction to VB Data Storage: Variables and Types

11.3.2 Declaring and Using More Complex Variables

11.3.3 Watching for Changes in Stored Information

11.3.4 Facilitating VBA Interaction in Complex Spreadsheets

11.4 Common Operations in VBA

11.4.1. Syntax for Basic Operators

11.4.1.1 Random Numbers

11.4.1.2 OFFSET

11.4.1.3 IF Statements

11.4.2 Date/Time Functionality

11.4.3 Iteration Structures: Loops

11.4.3.1 For-Loops (Fixed-Finite Iteration Structures)

11.4.3.2 While-Loops (Open-Ended Iterations)

11.5 User-Defined Functions

11.5.1 An Introduction to Functions

11.5.2 More Complex Examples

11.5.2.1 Data Access and Presentation: Online Stock Data

11.5.2.2 Complex Math Made Easy: Queuing Equations

11.6 Error Handling

11.7 Making Functions and Macros Available to Other Workbooks: Creating Add-Ins

Practice Problems

Practice 11.1

Practice 11.2

12 Automating Application Calls

12.1 Calls to MapPoint

12.2 Calls to Solver

12.3 Calls to RiskOptimizer

12.3.1 Work-Group Selection Revisited

12.3.2 Inventory System Simulation Revisited

12.4 Calls to XLStat

12.5 A Final Note on the Value of Linguistics

Supplement: Setups for Embedded MapPoint Controls

Written by Denys Lu, Blackbelt Student, Emory University

Part A: The MapPoint Control

Part B: Setting Up the Input Cells

Part C: The MapPoint Code

Part D: The Search Button

Part E: Initializing the MapPoint Control

Part F: Test Run

Practice Problems

Practice 12.1

Practice 12.2

13 Guided and User-Friendly Interfaces

13.1 Interface Locking and Protecting

13.2 Dynamic Interfacing: Pop-Ups and Dialogs

13.2.1 Constructing and Linking a Userform: An Example

13.2.2 More Sophisticated Userform Options

13.3 Customizing Primary Excel Interfaces

13.3.1 Manual Additions in Excel 2007 and Later Versions

13.3.2 Manual Additions in Excel 2003

13.3.3 Fully Automating Button and Menu Setups

13.3.4 Fully Automating Button and Menu Cleanup

13.4 Ribbon Editing for Packaged Add-Ins

13.5 Don’t Give Up on the Spreadsheet

Practice Problem

Glossary of Key Terms

Appendix: Shortcut (Hot Key) Reference

Index