Dynamic Business Reports and Dashboards Using Excel

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.

Description

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.

Course Objectives

By the end of the course, 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
  • Record, write and edit powerful macros that will perform routine tasks in no-time

How will this Training Course be Presented?

15% of the course is theory based. 85% uses MS Excel as a powerful tool to design and prepare dynamic business reports, dashboards, and scorecards. Groups and individuals will be required to complete exercises, case studies and projects on a daily basis.

Prerequisites

Intermediate-level Excel knowledge

Who is this Training Course for?

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.

The Course Content

Program Outline
 
Essential Reporting Requirement Skills
 
  • The 19 Rules of Pivot Tables and Pivot Charts
  • Multiple Consolidation Ranges
  • Retrieving External Data Using Microsoft Query
  • Importing Text Files Using MS Query
  • Connecting to Access Databases
  • Connecting to SQL Databases
  • XML Data Import
  • Importing from Data Connection Wizard
  • Importing from Microsoft Query
  • Connections Properties
 
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 Sparklines
  • Creating Unconventional Style Charts
  • Fancy Thermometer Charts
  • Colored Chart Bars
 
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
 
Macro-Charged Reporting
 
  • Recording, Editing, Testing VBA Macros
  • Working with Standard VBA Code for Report Design and Management
  • Building a Macro-Driven Reconciliation Program
  • Building Budget Variance Reporting Program
  • Building an Email-Based Report Distribution Program
  • Building a Macro-Based Report Printing System
  • Building a Financial Statement Preparation System
  • Building a Vendor and Invoice Analysis Report

Similar courses

Accounts Payable (AP) is not just processing checks, mailing them and then filing the backup paperwork. To truly and effectively manage accounts payable, one must understand the AP department's building blocks including, but not limited to customer service, vendor management, check requests and Purchase Orders (POs).

More Information

This course is aimed at professionals who have, or will soon have, responsibility for managing and manipulating data using MS Excel on a day to day basis.

More Information

This finance & accounting training course is designed to cover the role and importance of accounting and finance in today’s business world. It will provide an ‘MBA level’ experience in terms of content, instruction, discussion and team exercises.

More Information

This Advanced Finance Programme is a must for all those who would like to attend a course that comprehensively deals with all the key topics of strategic management, financial planning, and financial management. It is also for those who wish to sharpen up their financial management skills, or who wish to refresh or update the skills they already have.

More Information

This ICL Accounts Payable training seminar provides businesses and other organizations with a strong sense of where they stand against industry leaders and shows them how to take their services and organizational processes to state-of-the-art levels.

More Information

This course will enable you and your organization to manage your accounts receivable effectively without compromising your credit sales.

More Information

This seminar provides participants with crucial cost awareness and budgetary skills by combining techniques analysis, problems and examples of real case studies, which is essential in managing and controlling processes / projects in times of increasing global competition where the budgets are inextricably linked with both strategy formulation and cost analysis.

More Information

This short course for middle and senior managers in the public sector aims to create an awareness and understanding of the Public Finance Management Act 1 of 1999 (PFMA), as amended, and its implications.

More Information

You will learn about the accounting processes of recording and reporting business transactions, and how these are summarised as periodic financial reports in accordance with statutory requirements. You will also learn how these financial reports are analysed by a variety of user groups.

More Information

The Strategy & Leadership for CFO's and Senior Finance Executives Programme will enhance your ability to lead whilst giving you the wider business expertise required of today’s high-performing CFOs.

More Information

This course is tailored for experienced users of Microsoft Project, wanting to consolidate their grasp of planning and tracking, and to explore the advanced features of the software.

More Information

This course covers the critical knowledge and skills a project manager needs to create a project plan with Project 2016 during the planning phase of a project.

More Information

The programme aims at equipping managers to protect employees, suppliers and customers by providing critical information in order to successfully manage COVID-19 within the work environment.

More Information

The main purpose of the course is to give delegates a good understanding the power of Power BI to develop dashboards using large data sets.

More Information

The main purpose of the course is to give delegates a good understanding the power of Power BI to develop dashboards using large data sets.

More Information

This course will provide participants with detailed understanding of basic concepts and definitions to enable them read and understand finance related documents and their implications in day-to-day workings.

More Information

Learn the key strategies used by project managers to generate crucial funding for their projects.

More Information

This course provides the knowledge required to pass the Microsoft AZ-900 exam. This course is beneficial for those that will be using Microsoft Azure, whether they are administrators, developers, or database administrators.

More Information

The main purpose of the course is to give delegates a good understanding the power of Power BI to develop dashboards using large data sets.

More Information

Perform powerful data analysis with DAX for Power BI, SQL Server, and Excel

More Information

This Microsoft Word training course aims to provide new users with the essential skills needed to create, edit and print professional looking documents using text, tables, lists and pictures as well as covering simple mail merge

More Information

This Microsoft Word training course aims to provide users with the skills to work with larger documents efficiently, create standard documents for repeated use as well as covering advanced mail merge techniques.

More Information

This Microsoft Word training course aims to provide experienced users with the skills to work with document referencing features, longer document tools, tracking changes, document protection, diagrams and develop automation using fields and macros.

More Information

This course aims to provide new Excel users with a foundation knowledge of Excel’s core features such as formulas, formatting, navigation, printing and creating charts.

More Information

This course aims to provide more experienced users with proficient skills in Excel’s three major strands: formulas, list management and charts, as well as providing tips to assist efficiency.

More Information

This course aims to provide experienced Excel users with proficient skills in developing more complex formulas, list analysis using a variety of tools and creating simple macros as well as features and tips to assist efficiency.

More Information

The course guides you through the basic and advanced features of Excel to help you discover the gems hidden inside. From data analysis, to visualization, the course walks you through the steps required to become a superior data analyst.

More Information

This Microsoft Word training course aims to provide users with the skills to work with larger documents efficiently, create standard documents for repeated use as well as covering advanced mail merge techniques.

More Information

This short course will allow you to become productive by acquiring a basic understanding of Microsoft Word, Microsoft Excel, Microsoft PowerPoint, and Microsoft Outlook, for everyday professional use.

More Information

This Microsoft PowerPoint training course aims to provide new PowerPoint users with the essentials skills needed to create, edit and present professional looking presentations using text, tables, diagrams, charts and pictures as well as providing presentations tips.

More Information

This Microsoft PowerPoint training course aims to provide experienced PowerPoint users with the skills to work with PowerPoints more advanced features such as masters, creating templates and themes, and using media such as video and audio to expand your presentation as well as providing tips to assist efficiency.

More Information

This Advanced Microsoft PowerPoint 2016 training class is for PowerPoint 2016 users who want to build upon their basic skills. Students will use advanced techniques such as working with Masters and Special Effects within their presentations.

More Information

Essentials for Educators and Skills Development Facilitators

More Information

The course begins with the basic concepts and leads students through all the functions they’ll need to plan and manage a small to medium-size project, including how to level resources and capture both cost and schedule progress.

More Information

Extract, transform, and analyse data with business intelligence software frequently seen as the next step after Excel.

More Information

Extract, transform, and analyse data with business intelligence software frequently seen as the next step after Excel.

More Information

Win tenders based on clear winning strategies and a legal understanding of the process.

More Information

This course builds upon the foundational knowledge gained in the Introduction to SharePoint Online course.

More Information