BUILDING DYNAMIC DASHBOARDS FOR BUSINESS INTELLIGENCE WITH EXCEL
What is this course about?
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 link 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 MS Excel version 2013, PC Based windows software and not Macintosh. (Laptop will be provided)
Who is this course for?
This fast pace Advanced level course is aimed at frequent Excel users who has to prepare interactive, informative and maintenance-free reports for management to analyze key pieces of business information. You should be a confident Excel user who has adequate experience in using and maintaining Excel reports and be able to create Excel table-driven reports and charts. This course is not suitable for those who seldom use Excel at work.
Due to the nature of this course, a pre-requisite questionnaire will be provided to you to ensure the suitability and level is right for you. This course requires EXCEL ability beyond normal Advanced Level certification courses and requires participants to develop macros and interactive charts. A high degree of working knowledge and in depth experience using EXCEL is required for this particular course.
What will I learn?
Understanding Charts and Chart Tools
Participants will learn how to determine which type of chart can best display their data, create chart using Quick Analysis tool, create chart using Recommended Chart Tool, add and remove chart data series quickly, create dynamic chart titles and chart labels that can be updated automatically, create chart with multiple-lines Category Axis, customize and format charts using Chart Elements and Chart Style icons as well as filter chart using Chart Filter icon.
Creating Combo Charts
Participants will learn how to create Line-Column chart, create secondary value axis on a chart, reverse the category axis and values axis of a chart, show 3-months forecasting trends on a Line chart using Linear Trendline, add a moving average Trendline to a Line chart, display different levels on a chart as well as compare different targets with a chart.
Using In-Cell Visualization
Participants will learn how to create in-cell Bar chart using the Data Bar Conditional Formatting features, how to create in-cell line chart, column chart and Win/Loss chart using the Sparklines tool, as well as how to use Icon Sets and symbols to visualize the values on 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 and Linked Picture options 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.
Introducing Excel Macro
Participants will learn how to record macro using Macro Recorder and edit macro using VBA (Microsoft Visual Basic for Application), assign macro to a form control button on the worksheet as well as assign macro to a command button on the Quick Access Toolbar. Lowest values in the report, using icons and using Data Bar to create in-cell data visualization.
Understanding Excel 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, Slicer and Timeline.
Building PivotTable Driven Dashboard
Participants will learn how to import Microsoft Access data into Excel using Ms Query Wizard, filter pivot data with Report Filter, Slicer and Timeline, sort pivot data by color as well as build a dynamic PivotTable driven BI Dashboard.
Creating Dynamic Charts and Tables
Participants will learn how create dynamic chart using table filter and slicer, define dynamic range names using OFFSET function, create dynamic charts with defined names, INDEX & MATCH functions as well as control chart data series using Data Validation drop-down list.
Adding Interactive Controls to Dashboard
Participants will learn how to use various form controls such as buttons, combo box, check box, option button and scroll bar to manipulate the dashboard. For example, when a check box is checked, charts and tables will be updated automatically. Participants will also learn how to switch between different dashboards using hyperlink buttons and build a dynamic dashboard using form controls and advanced functions.
The case studies are based on real-life business scenarios and problems encountered by Excel users in their daily jobs. Participants will learn useful skills to provide efficient and practical solutions to overcome the problems and increase productivity.
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.