Financial Analysis

Summarising Excel 2003 Data to Make Better Business Decisions (Course Code: 4003)

Course Schedule:

Please call 8622 8900 or email us for further information

Overview:

One of the great challenges business people face today is not collecting information, but making sense of the information they already have. Within the endless rows of Microsoft Excel data is information that can help you make better business decisions, but how can you filter you way through all that information? This course, through hands-on activities, will teach you some of the most effective techniques in data summary and display. It covers the role of business intelligence in today’s workplace and introduces the power of Excel in business intelligence analysis. You also learn how to hone your skills regarding data lists and PivotTables.

Learning Method:

Instructor led.

Who will benefit from this course?

This course is designed for experienced Excel users who have a vested interest in analysing Excel data more effectively. Those who are familiar with tracking project budgets and chart trends, and they have no difficulty using Excel to create formulas, charts, and cell formats.

Pre-requisites

Before attending this course, you must have:

Experience with analysing business data to make decisions about products, projects, and strategic direction
The ability to create formulas, including advanced formulas using the Insert Function dialog box
The ability to create line graphs and column charts from Excel data

What can you expect to gain from this course?

After completing this course, you will be able to:

Describe the role business intelligence plays in organisational planning and explain how to extend business intelligence analysis beyond the spreadsheet
Use data lists and SUBTOTAL formulas to summarise their Excel data
Create and use PivotTables to visualise worksheet data
Limit the data displayed in a PivotTable to only the data needed to make a specific decision

Course Content:

Module 1: Deriving Business Intelligence from Excel Data

This module introduces the process of deriving business intelligence from spreadsheet data both in general, as presented in the course lecture segments, and in the context of your business environment.
What Is Business Intelligence?
The Need for Business Intelligence
Discussion: The Need for Business Intelligence
Summary of Discovering Business Intelligence in Excel

Module 2:Summarising Data Using Lists

This module introduces data lists and SUBTOTAL formulas, which you can use to produce meaningful subsets of your Excel data. The exercises in this module ask you to analyse sample data in terms of sample files for a fictitious company.
Demonstration: Revealing Information in Data Lists
Walkthrough 1: Creating a Data List
Walkthrough 2: Creating a Crosstab Table
Walkthrough 3: Creating a SUBTOTAL Formula
Walkthrough 4: Filtering a Data List
Tips and Tricks for Using Data Lists and SUBTOTAL Formulas

Module 3:Creating a PivotTable

This module introduces PivotTables and shows you how to create them. The information in this module also places PivotTables in the context of analysing product sales by a store or a product.
The Power of PivotTables
Demonstration: Exploring the Capabilities of PivotTables
Walkthrough 1: Creating a PivotTable
Walkthrough 2: Pivoting a PivotTable
Walkthrough 3: Adding Fields to and Removing Fields from the PivotTable
Using PivotTables to Reveal Business Intelligence

Module 4:Fine-Tuning PivotTables

This module extends your knowledge of PivotTables by showing you how to limit the data shown in your PivotTable. You will also learn how to make your data easier to comprehend by formatting your PivotTable, adding or hiding summary rows and columns, and creating dynamic charts based on your data.
Demonstration: Filtering Data in PivotTables
Exercise 1: Filtering a PivotTable
Walkthrough 1: Filtering a PivotTable by Using Page Fields
Walkthrough 2: Formatting a PivotTable
Walkthrough 3: Creating a PivotChart
Revealing Business Intelligence by Using Excel
Discussion: Taking Business Intelligence Outside the Workbook