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
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
Introduction
- Measure business performance with a balanced scorecard
- What you should know
Introducing Balanced Scorecards
- The balanced scorecard methodology
- The four perspectives of a balanced scorecard
- Defining key performance indicators (KPIs)
- Introducing the course's scenario
Design a Balanced Scorecard
- Determine objectives with each perspective
- Identify barriers to successful implementation
- Generate and maintain momentum
- Measure program effectiveness
Create a Balanced Scorecard in an Excel Worksheet
- Create a PivotTable from an external data source
- Import a large data set into Excel
- Add data icon conditional formats
- Create a conditional formatting rule based on a formula
- Change PivotTable views using GETPIVOTDATA formulas
Conclusion
- Next steps