You are previewing Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition.
O'Reilly logo
Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Book Description

Learn to maintain and update scorecards and dashboards with Excel

Balanced Scorecards and operational dashboards measure organizational performance and Microsoft Excel is the tool used worldwide to create these scorecards and dashboards. This book covers time-proven step-by-step processes on how to guide executive teams and managers in creating scorecards and dashboards. It then shows Excel developers how to create those scorecards and dashboards. This is the only book that converts theory into practice. The author addresses the people and processes you need to identify strategy and operational metrics and then implement them in dashboards in three versions of Excel. You'll learn how balanced scorecards help organizations translate strategy into action and the ways that performance dashboards enable managers monitor operations.

  • Covers Excel 2010 back to Excel 2003

  • Shows how to develop consensus on strategy and operational plans with the executive teams

  • Details steps in creating tactical action plans

  • Gives step-by-step guidance in creating the most powerful management dashboards

  • Puts over ten years of experience in one book

Balanced Scorecards & Operational Dashboards with Microsoft Excel, Second Edition is the ultimate resource for enhancing your strategic and operational performance.

Table of Contents

  1. Cover
  2. Part I: Strategic Performance with Balanced Scorecards
    1. Chapter 1: Accelerating Strategic Performance
      1. Managing with a 500-Year-Old System
      2. The Failure of Modern Management Systems
      3. A Modern Strategic Management System
      4. Why Use a Balanced Scorecard?
      5. Building a Balanced Scorecard
      6. Does the Balanced Scorecard Guarantee Business Success?
      7. Does the Balanced Scorecard Really Work?
      8. Do Small and Midsized Businesses Benefit from the Balanced Scorecard?
      9. Is the Balanced Scorecard Worth Developing?
      10. Summary
    2. Chapter 2: Developing Your Strategic Foundation
      1. Developing Your Strategic Foundation
      2. Developing Your Strategic Assessment
      3. Developing Your Strategic Destination Statement
      4. Summary
    3. Chapter 3: Preparing to Build Your Balanced Scorecard
      1. Why Use a Balanced Scorecard?
      2. Is Your Organization Ready for the Balanced Scorecard Journey?
      3. You Must Make Your Case for Change
      4. Motivating Executives
      5. Building Balanced Scorecard Teams
      6. Background Research
      7. Interviewing Executives
      8. Summary
    4. Chapter 4: Step-by-Step to Building Your Strategy Map
      1. What Is a Strategy Map?
      2. Perspectives: Monitoring Your Strategy from Different Points of View
      3. Strategic Themes: Concentrating Resources and Momentum along Specific Themes
      4. Objectives and Causal Links: Modeling What Drives Your Business Success
      5. Selecting a Strategy Map Facilitator
      6. Step-by-Step to Creating Your Strategy Map
      7. Summary
    5. Chapter 5: Step-by-Step from Strategy to Action
      1. Turning Your Strategy Map into Measurable Action
      2. Strategic Theme Teams
      3. Motivating and Educating the Strategic Theme Teams
      4. Brainstorming Initiatives
      5. Developing a Robust List of Initiatives
      6. Prioritizing Initiatives
      7. Summary
    6. Chapter 6: Step-by-Step to Selecting Metrics and Setting Targets
      1. Achieving Balance in Your Balanced Scorecard
      2. The Right Number of Measures
      3. If You Have More Than the “Critical Few,” You Lose
      4. Leading and Lagging Metrics: Drivers and Results
      5. Sample Objectives and Metrics
      6. Step-by-Step to Selecting Your Metrics
      7. Defining the Metric with a Metric Definition
      8. Look Out! What You Measure Is What You Get!
      9. Critical Questions to Ask about Your Measures and Metrics
      10. Setting Targets
      11. Summary
    7. Chapter 7: Step-by-Step to Developing Your Implementation Plan
      1. Step-by-Step to Translating Initiatives into Projects
      2. Monitoring Initiatives in Progress
      3. Summary
    8. Chapter 8: Step-by-Step to Rollout and Strategic Reviews
      1. Creating a Culture Focused on Strategy
      2. Strategy Review Meeting
      3. Communication, Training, and Rollout
      4. Summary
  3. Part II: Operational Performance with Dashboards
    1. Chapter 9: Developing Executive and Operational Dashboards
      1. Why Are Dashboards Used with Increasing Frequency?
      2. The Differences between Dashboards and Scorecards
      3. Challenges in Developing Dashboards
      4. Developing Your Dashboard
      5. Summary
    2. Chapter 10: Mapping Your Operational Processes
      1. Before You Map, Know Why
      2. Dashboards and Six Sigma
      3. Types of Process Mapping
      4. Step-by-Step to Building a Map
      5. Summary
    3. Chapter 11: Identifying Critical Metrics and Key Performance Indicators
      1. General Rules for Metrics in Operational Dashboards
      2. Interview the Decision-Makers
      3. Identify Metrics Using Your Map
      4. Cross-Check Your Metric
      5. Summary
  4. Part III: Building Maps, Scorecards, and Dashboards
    1. Chapter 12: Creating Dashboards for Decision-Making
      1. Step-by-Step: Creating Dashboards That Aid Decision-Making
      2. Rules of Design
      3. Tips on Graphical Elements
      4. Some Important Sources on the Art and Science of Visualizing Data
      5. Summary
    2. Chapter 13: Drawing Process and Strategy Maps
      1. Which Drawing Tool Should You Use?
      2. Drawing with Microsoft Office Drawing Tools
      3. Drawing with Microsoft Visio
      4. Summary
    3. Chapter 14: Using Microsoft Excel for Balanced Scorecards and Dashboards
      1. Excel Is the Most Widely Used Balanced Scorecard Software
      2. Consider the Trade-Offs between Excel and Large BI Systems
      3. Disadvantages of Excel
      4. Advantages of Using Excel
      5. When to Use Excel
      6. Solutions
      7. Summary
    4. Chapter 15: Text-Based Dashboards
      1. Alerting with Conditional Formats
      2. Creating In-Cell Charts with Text
      3. Alerting with Conditional Text Icons
      4. Summary
    5. Chapter 16: Custom Labels and Formatting
      1. Combining Numbers, Text, and Dates to Create Custom Labels
      2. Time and Data Calculations
      3. Scaling Numbers with Formatting
      4. Creating Custom Titles and Floating Text
      5. Creating New Color Palettes
      6. Summary
    6. Chapter 17: Working with Data That Changes Size
      1. Using Tables for Data That Changes Size
      2. Naming Ranges for Ease of Use and Functionality
      3. Summary
    7. Chapter 18: Retrieving Data from Lists and Tables of Data
      1. More Powerful Than VLOOKUP: INDEX and MATCH
      2. The Key to Retrieving Data and Creating Interactive Dashboards
      3. Summary
    8. Chapter 19: Creating Miniature Charts and Tables
      1. Using Miniature Charts, Tables, and Sparklines for Greater Information Density and Improved Layout
      2. Creating Miniature Charts from Standard Excel Charts
      3. Creating Sparklines
      4. Excel’s Amazing Camera Tool
      5. Summary
    9. Chapter 20: Controlling Charts with Menus, Combo Boxes, and Buttons
      1. Adding Combo Boxes, Lists, Check Boxes, and More to Your Dashboards
      2. Selecting Data with a Combo Box or List
      3. Selecting Data with Multiple Criteria Using Multiple Combo Boxes
      4. When to Use a Data Validation List or Combo Box
      5. Creating Dynamic Cascading Combo Boxes or Lists
      6. Using Option Buttons
      7. Displaying or Hiding Data with a Check Box
      8. Scrolling Charts through Time with a Slider Bar
      9. Summary
    10. Chapter 21: Working with PivotTables
      1. Basic Concepts of PivotTables
      2. Creating an Auto-Expanding Database or List Name
      3. Using PivotTable Results in Dashboards
      4. Drilling Down to Detail with PivotTables
      5. Updating the PivotTable Linked to Internal or External Data
      6. Summary
    11. Chapter 22: Working with PowerPivot
      1. Basic PowerPivot Concepts
      2. Downloading and Installing the Free PowerPivot Add-In
      3. Downloading Sample Demos for PowerPivot
      4. Connecting to Data
      5. Creating PivotTables or PivotCharts with PowerPivot
      6. Calculating Fields with Data Analysis Expressions (DAX)
      7. Summary
    12. Chapter 23: Smoothing Data and Forecasting Trends
      1. Smoothing Erratic Data
      2. Adding the Analysis ToolPak to Excel
      3. Forecasting Trends
      4. Summary
    13. Chapter 24: Identifying Targets and Displaying Alerts
      1. Charting Target Values
      2. Charting Alerts with Conditional Colors
      3. Charting Alerts for the Top/Bottom n, Quartiles, and Percentiles
      4. Charting Alerts with Line and XY Scatter Diagrams
      5. Adding a Visual Indicator to Top/Bottom n, Quartile, and Percentile Charts
      6. Alerting with E-mailing
      7. Summary
    14. Chapter 25: Building Powerful Decision-Making Charts
      1. Seeing a Full Statistical Picture with a Box-and-Whisker Plot
      2. Bullet Charts: A Better Alternative to Gauges
      3. Pareto Charts Show What Is Most Important
      4. Variance Charts Make a Difference
      5. Project Your Projects with Gantt Charts
      6. Project Variance Gantt Charts
      7. Control Charts
      8. Summary
    15. Chapter 26: Drilling to Detail
      1. Navigating
      2. Drilling Down to Detail
      3. Summary
    16. Chapter 27: Using Excel Add-Ins for Extra Capabilities
      1. ASAP Utilities
      2. FlowBreeze Flowcharting
      3. Systems2Win Value Stream Mapping
      4. PowerPivot
      5. Simtools
      6. Formlist
      7. Managing Excel Add-Ins
      8. Summary
    17. Chapter 28: Finishing Touches
      1. Adding Context and Comments with Briefing Books
      2. Displaying Pop-Up Content and Dynamic Help
      3. Controlling Dashboard Display
      4. Hiding Worksheets
      5. Sending Conditional E-mails from Dashboards
      6. Adding Headers and Footers
      7. Locating and Removing Phantom Links
      8. Protecting Content, Worksheets, and Workbooks
      9. Restricting the User’s Range
      10. Summary
    18. Chapter 29: Data Integration Methods
      1. Should You Use Manual Data Entry or Automated Data Integration?
      2. Manual Data Entry for Dashboards
      3. Automating Data Retrieval with Text Files
      4. Automating Data Retrieval from Databases
      5. Importing Data Using a PivotTable
      6. Refreshing Data Automatically
      7. Linking Imported Data to Your Dashboard
      8. What Is OLAP, and When Should You Use It?
      9. Summary
    19. Chapter 30: Publishing Balanced Scorecards and Dashboards
      1. Publishing Directly in Excel
      2. Publishing Multidashboard Systems
      3. Publishing in PowerPoint
      4. Publishing in PDF
      5. Summary
  5. Introduction
    1. Success through Strategic Execution and Accelerating Operational Performance
    2. Who This Book Will Help
    3. How This Book Is Organized
    4. Free Resources That Extend This Book