Training Course on Advanced Data Analysis and Dashboard Reporting with Microsoft Excel & Power BI

Course Description

Data analysis and dashboard reporting have become an emerging requirement for many businesses today. Employees are frequently asked to prepare management reports, scorecards, and dashboard charts in order to help the management in their decision-making process. This Training Course on Advanced Data Analysis and Dashboard Reporting offered by Phoenix Center for Policy, Research and Training will help you understand advanced levels of analysis and reporting and allow you to create custom reports. The course will cover various tools and techniques used to perform data reporting, analysis and visualization. It will also discuss high level modelling techniques, data amalgamation, report visualization, and possible cases of automation.

What can I gain from this ‘Advanced Data Analysis and Dashboard Reporting’ course? You can add more meaning to your daily and periodic tasks by looking at probable automation of your tasks and better representation of your data and information with the use of powerful visualization techniques. Microsoft Excel is one of the most powerful and popular data analysis programs on the market today but not many people know how to use it to its full potential, so this course will teach you how to take your use of Excel to the next level.

This Training Course on Advanced Data Analysis and Dashboard Reporting offered by Phoenix Center for Policy, Research and Training will empower you with the widely sought-after skills necessary to effectively analyze large sets of data. Once the data has been analyzed and prepared for presentation, you will also learn how to present the data using interactive dashboard reports.

Course Objectives

Upon the successful completion of this Training Course on Advanced Data Analysis and Dashboard Reporting, participants will be able to:

• Understand the principles of data analysis
• Uncover the tools to analyze data and construct reports using Excel
• Understand how to use visualization techniques to improve presentation of information
• Understand how to condense, present and convey data clearly and succinctly
• Enhance the efficiency of executing mundane tasks through recording, writing and editing macros
• Understand the principles of great dashboard design and how to present data vividly
• Perform advanced and dynamic data validations
• Design exceptional visualization charts, dashboards, scorecards, and flash reports
• Build custom reports using advanced form controls and buttons
• Comprehend the principles and practice of data analysis for project M&E and the context in which this operates
• Recognize how to apply big data analytics across various industries

Training Methodology

The course is designed to be highly interactive, challenging and stimulating. It will be an instructor led training and will be delivered using a blended learning approach comprising of presentations, discussions, guided sessions of practical exercise, case study review, web-based tutorials, group work, exploration of relevant issues collaborative strength training, performance measurement, and workshops of participants’ displays, all of which adhere to the highest standards of training. The training technique is built on learning by doing, with lecturers using a learner-centered approach to engage participants and provide tasks that allow them to apply what they’ve learned. Experiential knowledge is also given equal importance within the format of training. Our facilitators are seasoned industry professionals with years of expertise in their chosen fields. All facilitation and course materials will be offered in English.

Who Should Attend?

This Training Course on Advanced Data Analysis and Dashboard Reporting would be suitable for, but not limited to:

• Business Professionals
• Finance Analysts/Managers/Controllers, Senior and Junior Accountants
• Business Analysts
• Research Analysts
• Marketing and Sales, Administrative Staff, Supervisors,
• Specialists engaged in data analysis and dashboard reporting using Excel
• Any other person who needs to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis

Course Content

Module 1: Essential Reporting Requirement Skills

• Advanced Pivot Charts Techniques
• Multiple consolidation ranges
• Retrieving external data using Microsoft query
• The rules of pivot tables and pivot charts
• Slicer techniques
• 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

Module 2: Building the Excel Dashboard – LOOKUP Data

• Looking up customer info
• Preparing the data using format as table
• Creating a dropdown menu
• Looking up data with Excel’s VLOOKUP function
• Cleaning up data with Excel’s if function
• Index and match an alternative to VLOOKUP
  

Module 3: Building the Excel Dashboard – Filtering Data

• Adding the order history table
• Formatting orders as a table
• Using excels advanced filter feature
• Record macro for advanced filter
• Modify the VBA filter code

Module 4: Building The Excel Dashboard – Subtotals

• Why use Excel’s subtotal function
• Implementing the subtotal function

Module 5: Building the Excel Dashboard – Pivot Tables and Pivot Charts

• Why use pivot tables
• Summarizing order info with pivot tables
• Prepare a pivot table for customer filter
• Creating the VBA procedure
• Declaring VBA variables
• Assigning values to VBA variables
• Connecting the filter to the pivot table
• Customers with no orders error

Module 6: Building the Excel Dashboard – Interactive Buttons

• Creating interactive charts with slicers
• Modifying the chart slicer

Module 7: Building the Excel Dashboard – Formatting

• Hiding extra worksheets and columns
• Cleaning up the Excel default settings
• Protecting the dashboard
  

Module 8: Advanced Data Structuring Techniques

• Custom and advanced data validation
• Creating and managing innovative conditional formatting

Module 9: 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
• Coloured chart bars

Module 10: 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 modelling tool
• List-box data modelling tool
• Form controls data modelling tools
• Spinner
• Option-button modelling
• Check-box data models
• Combo and group-box

Module 11: Macro Charged Reporting

• Recording, editing, testing VBA macros
• Building a macro-driven reconciliation program
• Building a budget variance reporting program
• Building a vendor and invoice analysis report

Requirements

• Participants should be reasonably proficient in English.
• Applicants must live up to Phoenix Center for Policy, Research and Training admission criteria.

NOTE

1. Discounts: Organizations sponsoring Four Participants will have the 5th attend Free
2. What is catered for by the Course Fees: Fees cater for all requirements for the training – Learning materials, Lunches, Teas, Snacks and Certification. All participants will additionally cater for their travel and accommodation expenses, visa application, insurance, and other personal expenses.
3. Certificate Awarded: Participants are awarded Certificates of Participation at the end of the training.
4. The program content shown here is for guidance purposes only. Our continuous course improvement process may lead to changes in topics and course structure.
5. Approval of Course: Our Programs are NITA Participating organizations can therefore claim reimbursement on fees paid in accordance with NITA Rules.

Booking for Training

Simply send an email to the Training Officer on training@phoenixtrainingcenter.com and we will send you a registration form. We advise you to book early to avoid missing a seat to this training.

Or call us on +254720272325 / +254737566961

Payment Options

We provide 3 payment options, choose one for your convenience, and kindly make payments at least 5 days before the Training start date to reserve your seat:

1. Groups of 5 People and Above – Cheque Payments to: Phoenix Center for Policy, Research and Training Limited should be paid in advance, 5 days to the training.
2. Invoice: We can send a bill directly to you or your company.
3. Deposit directly into Bank Account (Account details provided upon request)

Cancellation Policy

1. Payment for all courses includes a registration fee, which is non-refundable, and equals 15% of the total sum of the course fee.
2. Participants may cancel attendance 14 days or more prior to the training commencement date.
3. No refunds will be made 14 days or less before the training commencement date. However, participants who are unable to attend may opt to attend a similar training course at a later date or send a substitute participant provided the participation criteria have been met.

Tailor Made Courses

This training course can also be customized for your institution upon request for a minimum of 5 participants. You can have it conducted at our Training Centre or at a convenient location. For further inquiries, please contact us on Tel: +254720272325 / +254737566961 or Email training@phoenixtrainingcenter.com  

Accommodation

Accommodation is arranged upon request and at extra cost. For reservations contact the Training Officer on Email: training@phoenixtrainingcenter.com or on Tel: +254720272325 / +254737566961