Balanced Scorecards & Operational Dashboards with Microsoft Excel ^{2nd Ed.}

Drive strategic performance with Balanced Scorecards and operational dashboards

Balanced Scorecards help translate business strategy into action. Performance dashboards are crtitical for monitoring operations and tactics. This fully updated guide shows you how to create and implement a program of Balanced Scorecards and dashboards using Excel, the preferred tool for the job. Incorporating the latest enhancements, it leads you step by step through the process, preparing you to leverage these tools for improved performance in your organization.

• Understand how and why Balanced Scorecards work
• Develop your strategic foundation, build your strategy map, select metrics, and set targets
• Create executive and operational dashboards to aid in decision-making
• Identify critical metrics and key performance indicators
• Examine the pros and cons of using Excel so you can leverage its power
• Provide more information with miniature charts and tables
• Smooth data, forecast trends, and display alerts
• Publish with Powerpoint templates or in secure formats
• Add extra functionality to Excel

• Part I Strategic Performance with Balanced Scorecards
1. Accelerating Strategic Performance
• Managing with a 500-Year-Old System
• The Failure of Modern Management Systems
• A Modern Strategic Management System
• Why Use a Balanced Scorecard?
• Building a Balanced Scorecard
• Does the Balanced Scorecard Guarantee Business Success?
• Does the Balanced Scorecard Really Work?
• Do Small and Medium-Sized Businesses Benefit from the Balanced Scorecard?
• Is the Balanced Scorecard Worth Developing?
• Summary
• Notes
2. Developing Your Strategic Foundation
• Developing Your Strategic Foundation
• Mission
• Vision
• Values
• Developing Your Strategic Assessment
• Defining Your External Environment by Using PESTEL
• Defining Your Industry Environment with Porter's 5 Forces
• Defining Your Strengths and Weaknesses with the Balanced Scorecard SWOT
• Developing Your Strategic Destination Statement
• Summary
• Notes
3. Preparing to Build Your Balanced Scorecard
• Why Use a Balanced Scorecard?
• Is Your Organization Ready for the Balanced Scorecard Journey?
• Senior Executive Commitment
• Experienced Facilitator or Consultant
• Executive Sponsor Commitment
• Executive Time and Commitment
• Participative Culture with Open Communication
• You Must Make Your Case for Change
• Motivating Executives
• Building Balanced Scorecard Teams
• Background Research
• Interviewing Executives
• Summary
• Notes
4. Step-by-Step to Building Your Strategy Map
• What Is a Strategy Map?
• Leveraging Your Strategy Map
• Perspectives: Monitoring Your Strategy from Different Points of View
• Strategic Themes: Concentrating Resources and Momentum along Specific Themes
• Objectives and Causal Links: Modeling What Drives Your Business Success
• Selecting a Strategy Map Facilitator
• Step-by-Step to Creating Your Strategy Map
• The Straw Dog Approach
• The Brainstorm and Intensive Discussion Approach
• Conducting Strategy Map Sessions
• Selecting Strategic Theme Sponsors
• Summary
• Notes
5. Step-by-Step from Strategy to Action
• Turning Your Strategy Map into Measurable Action
• Strategic Theme Teams
• Motivating and Educating the Strategic Theme Teams
• Brainstorming Initiatives
• Developing a Robust List of Initiatives
• Prioritizing Initiatives
• Summary
• Notes
6. Step-by-Step to Selecting Metrics and Setting Targets
• Achieving Balance in Your Balanced Scorecard
• The Right Number of Measures
• If You Have More Than the "Critical Few,"" You Lose
• Lead and Lag Metrics: Drivers and Results
• Sample Objectives and Metrics
• Step-by-Step to Selecting Your Metrics
• Defining the Metric with a Metric Definition
• Look Out! What You Measure Is What You Get!
• Critical Questions to Ask about Your Measures and Metrics
• Setting Targets
• Setting Target Values
• Setting Intermediate Targets
• You Will Not Move toward a Target at a Constant Rate
• Summary
7. Step-by-Step to Developing Your Implementation Plan
• Step-by-Step to Translating Initiatives into Projects
• Monitoring Initiatives in Progress
• Summary
8. Step-by-Step to Rollout and Strategic Reviews
• Creating a Culture Focused on Strategy
• Strategy Review Meeting
• Preparing for the Strategy Review Meeting
• Facilitating the First Meeting
• Communication, Training, and Roll-Out
• Communication
• Communication Media
• Training
• Summary
• Part II Operational Performance with Dashboards
9. Developing Executive and Operational Dashboards
• Why Are Dashboards Used with Increasing Frequency?
• What Are the Differences Between Dashboards and Scorecards?
• Challenges in Developing Dashboards
• Developing Your Dashboard
• Summary
10. Mapping Your Operational Processes
• Before You Map, Know Why
• Dashboards and Six Sigma
• Types of Process Mapping
• Six Sigma SIPOC Mapping
• Value Stream Mapping
• Step-by-Step to Building a Map
• Summary
11. Identifying Critical Metrics and Key Performance Indicators
• General Rules for Metrics in Operational Dashboards
• Interview the Decision Makers
• Identify Metrics Using Your Map
• Step-by-Step to Identifying Critical Metrics
• Brainstorming and Identifying Metrics Using an Ishikawa or Fishbone Diagram
• Selecting a Metric
• Cross-Check Your Metric
• What's Simple Can Be Difficult, and What's Difficult Can Be Simple
• Summary
• Part III Building Maps, Scorecards, and Dashboards
12. Creating Dashboards for Decision-Making
• Step-by-Step to Creating Dashboards That Aid Decision Making
• Make Your Dashboards Actionable
• Rules of Design
• Tips on Graphical Elements
• Some Important Sources on the Art and Science of Visualizing Data
• Excel Charting Techniques
• The Art of Visualizing Data
• Summary
13. Drawing Process and Strategy Maps
• Which Drawing Tool Should You Use?
• Drawing with Microsoft Office Drawing Tools
• Displaying the Drawing Toolbar in Versions of Office prior to 2007 and 2010
• Drawing Objects and Connectors
• Adding Text to Objects or Connectors
• Moving Objects or Connectors
• Formatting Objects or Connectors
• Grouping Objects So That They Act As One
• Using Grid and Nudge for Accurate Positioning
• Controlling Objects That Overlap
• Saving Time When You Draw
• Drawing with Microsoft Visio
• Summary
14. Using Microsoft Excel for Balanced Scorecards and Dashboards
• Excel is the Most Widely Used Balanced Scorecard Software
• Consider the Trade-Offs Between Excel and Large BI Systems
• Disadvantages of Excel
• Many Versions of One Truth
• Technical Scalability versus User Scalability
• Hidden Errors
• Spreadsheets Spawn Spreadsheets
• The Ineluctable Modality of User-Built Conundrums
• Advantages of Using Excel
• Get It Done Now!
• Prove the Business Intelligence and Dashboard Concept
• Experienced Users
• Total Costs for Scalability
• Excel Is Flexible and Extendible
• Excel's Chart Engine Is a Powerful Standard
• When to Use Excel
• Solutions
• Summary
15. Text-Based Dashboards
• Alerting with Conditional Formats
• Conditional Formatting with Earlier Versions of Excel
• Conditional Formatting with Excel 2007 and Excel 2010
• Creating In-Cell Charts with Text
• Creating Text Charts with Earlier Versions of Excel
• Creating Data Bars with Excel 2007 and Excel 2010
• Alerting with Conditional Text Icons
• Creating Conditional Icons with Earlier Versions of Excel
• Creating Conditional Icons with Excel 2007 and Excel 2010
• Summary
16. Custom Labels and Formatting
• Combining Numbers, Text, and Dates to Create Custom Labels
• Joining Text with Custom-Formatted Numbers
• Joining Text with Custom-Formatted Dates and Times
• Time and Data Calculations
• Calculating the Beginning and End of Any Month
• Creating a Month Series
• Calculating the Beginning and End of a Quarter
• Automatically Updating Quarterly Titles on the Category (x) Axis
• Scaling Numbers with Formatting
• Scaling Charts and Sheets Separately
• Creating Custom Titles and Floating Text
• Creating Dynamic Chart Titles
• Creating Floating Titles in Charts
• Creating Dynamic Titles That Float in Worksheets
• Rotating Text, Shapes, and Charts in Any Direction by Using a Picture of Cells
• Creating Custom Data Labels
• Creating Pop-Up and Alert Data Labels
• Creating New Color Palettes
• Creating a Custom Color Palette in Excel 2003
• Matching Your Dashboard to the Corporate Identity
• Transferring Color Palettes between Workbooks
• Creating Aesthetically Pleasing Color Palettes
• Summary
17. Working with Data That Changes Size
• Using Tables for Data That Changes Size
• Formatting Tables
• Attaching a Dynamic Range to a Table
• Tables Use English-like Formula References
• Naming Ranges for Ease of Use and Functionality
• Creating Named Ranges
• When to Use Tables or Dynamic Range Names
• Creating Dynamic Range Names That Automatically Adjust When the Size of Data Changes
• Creating Charts That Expand to Include New Data
• Dynamically Changing a Chart's Start Date
• Dynamically Changing a Chart's Start Date and Width
• Dynamically Charting the Last 13 Months of Data
• Creating Dynamic Range Names for Lists
• Summary
18. Retrieving Data from Lists and Tables of Data
• More Powerful Than VLOOKUP: INDEX and MATCH
• The Key to Retrieving Data and Creating Interactive Dashboards
  • Using a Keyword to Retrieve and Chart Data
  • Adding a Drop-Down Selection List to Make Retrieval Easier
  • Retrieving Data Given Multiple Keywords
  • Retrieving Multiple Rows Using a Single Keyword
  • Retrieving Data with a Two-Way Lookup
• Summary
19. Creating Miniature Charts and Tables
• Using Miniature Charts, Tables, and Sparklines for Greater Information Density and Improved Layout
• Creating Miniature Charts from Standard Excel Charts
• Removing Titles, Gridlines, and Legends
• Formatting the Y-axis to Remove Unnecessary Scales and Width
• Reducing the Size and Width of Y-Axis Numeric Values
• Formatting the X-Axis
• Formatting Multiple Miniature Charts
• Creating Sparklines
• Modifying or Deleting Sparklines
• Sparkline Tricks
• Excel's Amazing Camera Tool
• Taking Pictures with the Camera Tool
• Using Camera Pictures of Charts, Tables, and Miniature Charts
• Simultaneously Formatting the Size of Multiple Charts or Camera Pictures
• The Dark Side of Excel's Camera Tool
• Summary
20. Controlling Charts with Menus, Combo Boxes, and Buttons
• Adding Combo Boxes, Lists, Check Boxes, and More to Your Dashboards
• Selecting Data with a Combo Box or List
• Selecting Data with Multiple Criteria Using Multiple Combo Boxes
• When to Use a Data Validation List or Combo Box
• Creating Dynamic Cascading Combo Boxes or Lists
• Using Option Buttons
• Displaying or Hiding Data with a Check Box
• Scrolling Charts through Time with a Slider Bar
• Summary
21. Working with PivotTables
• Basic Concepts of PivotTables
• Creating an Auto-Expanding Database or List Name
• Using PivotTable Results in Dashboards
• Retrieving a Single Cell of Data from a PivotTable
• Dynamically Retrieving Data from a PivotTable
• Building a Safe User-Controlled PivotTable Display
• Drilling Down to Detail with PivotTables
• Updating the PivotTable Linked to Internal or External Data
• Summary
22. Working with PowerPivot
• Basic PowerPivot Concepts
• PowerPivot Advantages
• PowerPivot Disadvantages
• Downloading and Installing the Free PowerPivot Add-In
• Downloading Sample Demos for PowerPivot
• Connecting to Data
• Creating PivotTables or PivotCharts with PowerPivot
• Nuilding Free-Form Reports with PowerPivot and GetPivotData
• Sorting Months in Date Order with the Custom Sort Order List
• Calculating Fields with Data Analysis Expressions (DAX)
• Summary
23. Smoothing Data and Forecasting Trends
• Smoothing Erratic Data
• Smoothing Data with Simple Moving Averages
• Smoothing Data with Weighted Moving Averages
• Exponential Weighted Moving Average
• Adding the Analysis ToolPak to Excel
• Exponential Smoothing with the Analysis ToolPak
• Forecasting Trends
• Forecasting with Worksheet Functions
• Summary
24. Identifying Targets and Displaying Alerts
• Charting Target Values
• Charting Alerts with Conditional Colors
• Charting Alerts for the Top/Bottom n, Quartiles, and Percentiles
• Charting Alerts with Line and XY Scatter Diagrams
• Adding a Visual Indicator to Top/Bottom n, Quartile, and Percentile Charts
• Alerting with E-mailing
• Summary
25. Building Powerful Decision-Making Charts
• Seeing a Full Statistical Picture with a Box-and-Whisker Plot
• Bullet Charts: A Better Alternative to Gauges
• Pareto Charts Show What Is Most Important
• Variance Charts Make a Difference
• Project Your Projects with Gantt Charts
• Project Variance Gantt Charts
• Control Charts
• Summary
26. Drilling to Detail
• Navigating
• Navigating with Simple Hyperlinks
• Navigating with a Drop-down Menu
• Drilling-Down to Detail
• Drill-Downs in PivotTables and PowerPivots
• Drilling into a Data List by Clicking on a Row
• Summary
27. Using Excel Add-ins for Extra Capabilities
• ASAP Utilities
• FlowBreeze Flowcharting
• Systems2Win Value Strem Mapping
• PowerPivot
• Simtools
• Formlist
• Managing Excel Add-Ins
• Summary
28. Finishing Touches
• Adding Context and Comments with Briefing Books
• Displaying Pop-Up Content and Dynamic Help
• Controlling Dashboard Display
• Hiding Worksheets
• Sending Conditional E-mails from Dashboards
• Adding Headers and Footers
• Locating and Removing Phantom Links
• Protecting Content, Worksheets, and Workbooks
• Restricting the User's Range
• Summary
29. Data Integration Methods
• Should You Use Manual Data Entry or Automated Data Integration?
• Manual Data Entry for Dashboards
• Automating Data Retrieval with Text Files
• Creating Text Data Files
• Step-by-Step to Importing a Text Data File
• Automating Data Retrieval from Databases
• Step-by-Step to Importing Data from a Relational Database
• Importing Data Using a PivotTable
• Importing Data to a PivotTable
• Refreshing Data Automatically
• Linking Imported Data to Your Dashboard
• What is OLAP and When Should You Use It?
• Summary
30. Publishing Balanced Scorecards and Dashboards
• Publishing Directly in Excel
• Workbook Architecture
• Publishing Multi-Dashboard Systems
• Hierarchy of Views
• Deciding How to Publish Your Scorecard or Dashboard
• Publishing in PowerPoint
• Advantages and Disadvantages of Publishing in PowerPoint
• PowerPoint Architecture
• When You Should Use Powerpoint for Publishing
• Creating a Powerpoint Presentation
• Publishing in PDF
• Advantages and Disadvantages of Publishing in PDF
• PDF Architecture
• Creating Complex or High-Security Balanced Scorecards in PDF
• Summary

Surprisingly, I was impressed with this book. Not only was it very practical and mostly stuck to the premise of using Excel to create and handle Balanced Scorecards, but it also discussed when not to use it, in a very objective manner.

If you're into one of the subjects: Balanced Scorecards or Excel, this is worth reading, otherwise, skip it, as you will be bored.