About this Course

This course is customised for corporate managers and executives who are required to prepare various types of impressive executive management reports and business key performance reports. The main objective of this course is to provide the participants invaluable insights and equip them with advanced charting and reporting skills to professionally present their reports.

A business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators for an enterprise. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen. They may be tailored for a specific role and display metrics targeted for a single point of view or department. The essential features of a BI dashboard product include a customizable interface and the ability to pull real-time data from multiple sources. 

The course will train participants how to create dynamic charts and dashboards using Excel lists or data downloaded from ERP systems, how to charts and tables to PowerPoint slides presentation, how to use advanced Excel built-in functions and reporting features to enhance user interactivity with the dashboard, how to build and manage an efficient data model that feeds the dashboard with updatable business data, and also how to use Macro to manipulate charts and tables, compute and update formula results automatically. 

At the end of each session, a case study related to the topics learned will be given. Participants will have chance to discuss the case study scenario and come out with the solution. These case studies help participant to have better understanding of the topics learned and know how to apply the skills in their work. Model answers of the case studies and samples of Excel worksheets will be given to participants for reference and revision after the course.

This course will be running on PC Based windows software and not Macintosh.

You will Learn: 

Understanding Excel Charts

Participants will learn how to determine which type of chart can best display their data, prepare summary tables for charting, create charts using contiguous and non-contiguous data ranges, add and remove chart data series, create chart title and chart labels that can be updated automatically, create a chart with multiple-lines Category Axis, and also format charts using various formatting tools.

Creating Combination Charts

Participants will learn how to create different types of combination charts such as Line-Column chart, Bar-of-Pie chart and Pie-of-pie chart, create a secondary value axis on a chart, reverse the category and values axis on a chart, add Trendlines to a chart and how to show 3-months forecasting trend with Linear Trendline.

Using In-Cell Visualization

Participants will learn how to create in-cell line charts, in-cell column charts and in-cell Win/Loss chart using Sparklines tool, how to create in-cell bar chart using Data Bar formatting features as well as use Icon Sets and symbols to create in-cell visualization in a table.

Working with Dynamic Charts

Participants will learn how to import Microsoft Access data into Excel as a table, build dynamic chart using filter, define dynamic range names using Offset function, create charts using dynamic names and control chart series using data validation drop-down list.

Presenting Your Reports

Participants will learn how to convert a chart into a picture, use Excel Camera tool to take live picture of a range of cell that updates automatically, use Screenshot tool to capture a snapshot of charts or tables, paste link chart or table onto a slide so that it can be updated automatically when source data is changed, as well as insert Excel worksheet object into Microsoft PowerPoint for presentation purpose.

Adding Interactive Controls

Participants will learn how to use various form controls such as buttons, combo box, check boxes, group box and option buttons to manipulate dashboard. For example, when a check box is checked, charts and tables on a dashboard will be updated automatically.

Building Dynamic Dashboard

Participants will learn some useful dashboard fundamental knowledge, how to import data source, build an effective data model and design dashboard layout using frames, how to present complex ratios and statistics on a dashboard as well as how to create a dynamic and professional-look dashboard using PivotTable, PivotChart and Slicer.

Introducing Excel Macro

Participants will learn how to record macro using Macro Recorder and edit macro using VBA (Microsoft Visual Basic for Application), how to assign macro to a button on a worksheet and to a command button on the Quick Access Toolbar.

Course Durations：2days

Quick Tip

A dashboard is an interactive graphical user interface that organizes and presents critical business information in multiple dynamic charts and tables on one screen so it can be monitored at a glance. An effective executive dashboard should be simple, easy to read and interpret, contain only meaningful and useful information so that decision makers can focus on summaries, key trends, comparisons and exceptions to make quick and effective decisions.

Rate this course:

Log in to submit

Comments