Dashboards and Business Reports Using Excel

Why Attend?
All business professionals are required to prepare some kind of a scorecard or a dashboard. Attending this course will help you reach advanced levels in Excel which you will need to produce such an important tool.. 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. You will take away practical tools that are guaranteed to add value to your daily and periodic tasks.
Course Methodology
15% of the course is theory based. 85% uses MS Excel as a powerful tool to design and prepare dynamic business dashboards and scorecards. Groups and individuals will be required to complete exercises, case studies and projects on a daily basis.
Course Objective

By the end of the course, participants will be able to:
Boost Excel expertise in data slicing and dicing, data massaging, and data aggregation, and data integration with Access, web, text, SQL, and other databases using pivot tables

  • Perform advanced and dynamic data validations and master level formatting techniques
  • 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
  • Understand the principles of data analysis
  • Learn to synthesise and summarise information into a logical framework
  • Consider when to use a chart or a table
  • Know how to use visual effects to improve their reports and presentations
  • Explore how to summarise, present and communicate data clearly and concisely
  • Learn the principles of good dashboard design and presenting data graphically
  • Discover the tools to analyse data & build reports using your existing Excel tools and skills (and perhaps a few you didn’t know you had!)

YOUR TWO DAY COURSE OUTLINE

Data Analysis Tools

Tool selection

Evaluation of Excel vs. other BI alternatives, including Power Pivot in terms of speed, usability and cost

    • Shortcuts
      Speed up your data manipulation in Excel with the use of shortcuts
    • Tables & PivotTables
      Analysing data more quickly and efficiently using structured reference table and summarise using PivotTables
    • Power Pivot
      Demonstration of the Microsoft add-in to Excel, PowerPivot

Using Excel as a Data Analysis Tool

  • Essential Excel tools & functions

    • Overview of must-know tools and functions for data analysis such as LOOKUPS, logical and aggregation functions

  • Creating a robust formula

    • Nest functions together to create succinct and robust formulas

  • Working with text

    • Using text and concatenation functions to string numbers and text together to show in a dashboard or chart.

  • Cleaning your data

    • Tools and tricks to clean data before using it to build a report or dashboard

Charting in Excel

  • Working with charts in Excel 2013/16

    • Overview of new charting features of Excel

  •  Creating a Combo or Bubble chart

    • Show correlations by charting on different axes and chart types or bubble charts

  •  Waterfall charts

    • Comparison of different methods of waterfall charts, create your own template for future use.

Essential reporting requirement skills

  • The 19 rules of pivot tables and pivot charts
  • Advanced pivot charts techniques
  • Multiple consolidation ranges
  • 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

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
  • Option-button modelling, Check-box data models
  • Combo and group-box

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

Dashboards and Business Reports Using Excel

Date: 1-2 November 2017 Venue: Southern Sun Abu Dhabi
Delegate Fee: US$ 2 500, 00

Tel: +27 11 066 1931
Email: info@pldsa.co.za

The above registration fee includes all training materials and meals during the training.

Electronic Transfer or Direct Deposit into our bank account, validated by faxed or emailed copy of transaction slip.

Make payment to:
Account Name: Prosperity Leadership Development
Bank: Standard Bank
Account Number: 252714857
Branch Code: 001206
Branch Name: Clearwater
Swift Code: SBZA ZA JJ
*PLEASE NOTE: Payment is required
in full 5 days from date of invoice.

Our Training Courses

Finance:

● Finance for Non-Financial Managers
● Finance for Project Management
● Forensic Accounting and Fraud Investigation
● How to Conduct a Successful Internal Audit
● Debit & Credits: How Accounting Really Works
● The Essentials of Budgeting: From Creation Through Application

l

Business Report Writing & Proposals:

● Technical Report Writing
● Report Writing Skill
● Advanced Business Report Writing
● Environmental Reporting for Mining
● Operation Workshop

Administrative Skills:

● Filing, Correspondence & Record Management
● 2nd Annual Office Professional & Best Practice In Meeting & Minutes Taking Master Class
● 4-Day Office Executives Combo
● Professional Secretarial & Administration Skill
● Executive PA – for PAs & Executive Secretary

Sales & Marketing:

● Strategies for IT and Technical Professionals
● Successful Telephone Sales Techniques
● Advanced Negotiation Strategies
● Successful Selling Plans
● Successful Importing & Exporting

Mine & Engineering:

● Mine Machine Productivity
● Statistical Analysis & Method Validation for Microbiological Laboratories
● African Woman in Engineering Conference
● Forensic Engineering & Failure Analysis
● High & Medium Voltage Testing
● Procedures Masterclass 2012
● Earthing Systems for Utilities and Industrial Distribution
● Urban Renewal and Sustainable Human Settlements Conference
● Healthcare Waste Management Workshop
● Disaster Risk Management Conference
● Executive PA – for PAs & Executive Secretary

Human Resource:

● Performance Management
● Drafting and Implementing HR Conference
● Occupation Health and Safety in the Construction Sector Workshop
● Labour Relation & Labour Law
● Effective Policy and Procedure
● Development
● Strategy, Design and Implementation
● Organisational Development
● Strategic Business Planning
● Business Contract
● Employment Equity Relations Workshop
● Strategic Facilities Management (SFM)
● Disciplinary Hearing Proceedings
● Documentation & Confidentiality

Leadership & Management:

● Leadership and Management Courses
● Advanced Emotional Intelligence & Leadership Skill Management
● Project Management
● Asset Procurement & Disposal Management
● Mastering Leadership & Management Skills
● Corporate Fraud Control and Investigations
● Managing Discipline Effectively
● Executives Leadership Programme
● Management Empowerment
● Management Skill For New Supervisors
● Managing the IT Department
● IT Compliance Data Security Workshop
● Transforming Government Supply Chain Management
● Fleet Management Workshop
● Stress Management in the Security Sector Workshop
● Knowledge Management in the Public Sector Workshop
● Environmental Management for Sustainable Development Conference
● Water Quality Management Workshop
● 2nd Annual African Woman in Leadership Conference
● Protocol, Diplomacy, Etiquette and Event Coordination

Book your training today!

Contact Us