Building Dynamic Dashboards For Business Intelligence with EXCEL
  • S$750
  • 2-days
  • Advanced Level

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 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.


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. 

Prerequisite:
You should be a confident Excel user who has experience in using and maintaining Excel reports and be able to create Excel table-driven reports and charts. A pre-requisite questionnaire will be provided to you to ensure the suitability and level is right for you.

Notice:
The course will actively be using Excel 2013 versionIt will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)


  • 19-20 Apr 2018
  • 16-17 Jul 2018
  • 13-14 Dec 2018
  • *SDF for all companies
Day 1
Understanding Charts and Chart Tools

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

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 Visualizations

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.

Presenting Your Reports

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

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.

Day 2
Understanding Excel Dashboard

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

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

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 contr chart data series using Data Validation drop-down list.

Adding Interactive Controls to Dashboard

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.

Case Studies

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.

About Valene Ang

Valene Ang is a Certified Microsoft Training Specialist who has more than 10 years of training experience and working with companies to improve their data processes.

Her qualifications include a Bachelor’s in Business Computing, Microsoft Certified Trainer (MCT), Certified Instructor of Microsoft Certified Application Specialist (MCAS), and Master Instructor of Microsoft Office Specialist (MOS). Valene also holds an Advanced Certificate in Training and Assessment (ACTA) - a national qualification awarded by WDA (Workforce Development Authority) in Singapore.

She has trained different levels of management executives from small and medium sized organisations, government offices and multinational companies such as PSA International, Komatsu Asia Pte. Ltd., DFS Galleria Singapore, Neptune Orient Lines Limited (NOL), National Environment Agency (NEA), Inland Revenue Authority of Singapore (IRAS), CPF Board, Public Utility Board (PUB), Canadian High Commission, British High Commission, Temasek Polytechnic, Republic Polytechnic, Baxter Healthcare Pte. Ltd, and many more.

She has also conducted customized training and one-to one coaching sessions for many companies’ executives on Microsoft Office 2003 and Microsoft Office 2007. She is a much-sought-after trainer, judging by the very good evaluation she received from her past participants.

 

Comprehensive. Very good course and would recommend to anyone who wants to learn to use dashboards.

William Chan, Deputy Director, Singapore Polytechnic

Excellent! I will recommend my colleague to attend!

Phong Joo Tin, Marketing Manager, Singtel

This course was very effective in bringing across the various uses of Excel as an analytical tool. Important point was the need to plan ahead and to create the staging area on 'Data Models' to facilitate generation of dashboards for reporting

Sigit Gunawan, Director, Agri-Food & Veterinary Authority

Unique to this Course

Free Excel Templates and Spreadsheet

Cutomised case study for In-house companies to suits your business needs

Free comprehensive list of Excel resources

After Course support