Excel: Implementing Balanced Scorecards with KPIs

1h 4mIntermediate2019-12-12

Authors

Curt Frye

President of Technology and Society, Incorporated

Course details

Discover how to use Excel to create a balanced scorecard to measure business performance based on data available throughout your organization. This course shows intermediate Excel users how to capture metrics related to the financial, process, customer, and learning and growth aspects of their business and display them in an easy-to-understand visualization built entirely in Excel. Instructor Curt Frye provides an overview of balanced scorecards, including the four perspectives and related key performance indicators (KPIs). He then shows how to ingest data from various sources and levels of the organization into dashboards that summarize overall performance, using popular Excel features such as PivotTables and conditional formatting.

Learning objectives
The four perspectives of a balanced score card
Determining objectives with each perspective
Generating and maintain momentum
Measuring program effectiveness
Creating a PivotTable from an external data source
Importing data into Excel
Adding conditional formatting
Changing PivotTable views

Skills covered

Microsoft OfficeOperations ManagementOffice 365SpreadsheetsBusiness StrategyMicrosoft ExcelProject ManagementBusiness Analysis and StrategyLeadership and ManagementBusiness Software and ToolsMicrosoftDeep Dive (X:Y)

Concepts

0. Introduction

01 - Measure business performance with a balanced scorecard
02 - What you should know

1. Introducing Balanced Scorecards

03 - The balanced scorecard methodology
04 - The four perspectives of a balanced scorecard
05 - Defining key performance indicators (KPIs)
06 - Introducing the course's scenario

2. Design a Balanced Scorecard

07 - Determine objectives with each perspective
08 - Identify barriers to successful implementation
09 - Generate and maintain momentum
10 - Measure program effectiveness

3. Create a Balanced Scorecard in an Excel Worksheet

11 - Create a PivotTable from an external data source
12 - Import a large data set into Excel
13 - Add data icon conditional formats
14 - Create a conditional formatting rule based on a formula
15 - Change PivotTable views using GETPIVOTDATA formulas

Conclusion

16 - Next steps