Workshop on Business Reports and Dashboard Using Excel

Program Overview:

Business professionals are frequently required to prepare management reports, scorecards, and dashboards. Attending this course will help you reach advanced Excel levels allowing you to produce such important reports. The course starts with pivot tables, the most important tool ever created to perform data reporting, analysis and reconciliation. It then continues with high level modeling techniques, integration and report visualization, and it ends with automation. Your take-away is guaranteed to add value to your daily and periodic tasks. This course is a must for every intermediate-level Excel user.

For Whom:

Business professionals, accountants, finance analysts, senior and junior accountants, business analysts, accounting and finance professionals, research professionals, marketing and sales, administrative staff, supervisors, general staff from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis. Pre-requisite: Intermediate-level Excel knowledge.

Learning Objectives:

At the end of the program, participants will be able to:

• use their Excel expertise in data slicing and dicing, data massaging, data aggregation, data integration with Access, web, text, SQL, and other databases using pivot tables;
• perform advanced and dynamic data validations;
• design outstanding visualization charts, dashboards, scorecards, and flash reports;
• develop master-level report solutions using advanced form controls and buttons; and
• record, write and edit powerful macros that will perform routine tasks in no-time.

Course Outline:

Day 1: Essential reporting requirement skills

The 19 rules of pivot tables and pivot charts

Slicer techniques

Advanced pivot charts techniques

Multiple consolidation ranges

Day 2: Retrieving external data using Microsoft query

Importing text files using MS query

Connecting to access databases

Connecting to SQL databases

Importing from data connection wizard

Importing from Microsoft query

Customizing connections properties

Day 3: Advanced data structuring techniques  

Custom and advanced data validation

Creating and managing innovative conditional formatting

Charting and visualization techniques

Creating dynamic labels

Using the camera tool

Working with formula-driven visualizations

Using fancy fonts

Leveraging symbols in formulas

Working with spark lines

Creating unconventional style charts

Fancy thermometer charts

Colored chart bars

Day 4: Building report solutions

Conceptualizing and understanding report solutions

Developing a report solution

Configuring spreadsheet report data options

Enabling background refresh

Refreshing data when opening the file

Combo-box data modeling tool

List-box data modeling tool

Form controls data modeling tools

Day 5: Spinner

Option-button modeling

Check-box data models

Combo and group-box

Macro charged reporting

Recording, editing, testing VBA macros

Building a macro driven reconciliation program

Building budget variance reporting program

Building a vendor and invoice analysis report

Training Methodology

Lectures, discussions, exercises, and case studies will be used to reinforce these teaching/learning methods.