DATA ANALYSIS AND BUSINESS REPORTING TECHNIQUES USING EXCEL

Available Dates & Locations

Dates
June 16, 2024
June 20, 2024
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$4,950.00

Dates
June 16, 2024
June 20, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$3,750.00

Dates
July 14, 2024
July 18, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Cairo)

Language
Fees

$4,950.00

Dates
July 14, 2024
July 18, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Cairo)

Language
Fees

$3,750.00

Dates
July 14, 2024
July 18, 2024
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$4,950.00

Dates
July 14, 2024
July 18, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$3,750.00

Dates
September 1, 2024
September 5, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$4,950.00

Dates
September 1, 2024
September 5, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$3,750.00

Dates
September 1, 2024
September 5, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$3,750.00

Dates
September 1, 2024
September 5, 2024
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$4,950.00

Dates
October 27, 2024
October 31, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$4,950.00

Dates
October 27, 2024
October 31, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$3,750.00

Dates
November 24, 2024
November 28, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$3,750.00

Dates
November 24, 2024
November 28, 2024
Duration
5 Days
Venue

09:00 am – 03:30 pm (Virginia)

Language
Fees

$4,950.00

Dates
December 22, 2024
December 26, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$4,950.00

Dates
December 22, 2024
December 26, 2024
Location
Duration
5 Days
Venue

09:00 am – 03:30 pm (Dubai)

Language
Fees

$3,750.00

Create an Account to View the Course Brochure

Please create an account to view this course brochure.

Name(Required)
Account Password(Required)
This field is for validation purposes and should be left unchanged.

COURSE OVERVIEW

There is no doubt that Excel is the accountant’s, finance and business professional’s best friend! You and I know how over-whelmed we are with the abundance of unstructured data. Monthly, quarterly and annual closing are immensely data driven and require moving and exporting data from ‘ERPs’ and databases to Excel. This hands-on course will advance your data massaging, modeling, integration and automation skills to new levels. You will also master normalization and massaging of noisy data, preparation of reports, analysis and reconciliation.

This course is Excel based allowing you to develop an exclusive level of expertise and adding immediate value to your job.

COURSE OBJECTIVES

By completely attending this course, participants will be able to:

Competency I:

  • Apply massaging and normalization techniques to unstructured data to prepare it for analysis.
  • Identify and address data quality issues such as duplicates, missing values, and outliers.
  • Use Excel tools and functions to clean, transform and restructure data.

Competency II:

  • Create Pivot Tables to perform data analysis and produce insightful reports.
  • Customize scorecards and management reports to meet specific needs.
  • Use advanced Pivot Table features such as calculated fields, groupings, and slicers.

Competency III:

  • Design dynamic reporting models using different modeling techniques, such as scenario analysis, forecasting, and trend analysis.
  • Perform What-If-Analysis to explore various business scenarios and their potential outcomes
  • Use Excel’s advanced charting and visualization tools to create dynamic and interactive reports.

Competency IV:

  • Integrate Excel with various file types such as Access, web, text, SQL, and other databases.
  • Use Excel’s built-in data connectors and import/export features to retrieve and update data automatically.
  • Create automated reports that refresh and update data in real-time.

Competency V:

  • Record and edit macros to automate repetitive tasks and increase efficiency.
  • Use Excel’s VBA programming language to write customized code and automate complex tasks.
  • Generate reports and perform data analysis efficiently by running and editing recorded macros.

 

Note

This course requires the use of laptops with Excel 2016/2019/365. Delegates must bring their own laptops with Windows-based Excel fully installed.

TARGET COMPETENCIES

  • Massaging and Normalizing Data
  • Pivot Tables Reporting
  • Modeling Techniques
  • Integration and Linking
  • Macros and Automation

This course is designed for anyone who works with data, accountants, finance professionals, business analysts, HR Professionals, Sales & Marketing, IT, and other data-driven professionals. It is ideal for those who want to improve their Excel skills and learn how to manage, manipulate, and analyze large amounts of data efficiently.

This is a hands-on course with about 20% on design and structure and 80% uses MS Excel as a commanding tool to perform routine and periodic tasks. Individuals will be required to complete exercises, case studies, and projects on a daily basis.

DATA MASSAGING TOOLS AND TECHNIQUES

  • Merge and consolidate data
  • Data validation using numbers, lists, dates, and text length
  • The magical select, shift, select
  • The surgeon functions: left, right, mid, concatenate, value
  • To name or not to name: Naming, editing, and managing cells and ranges
  • Sum and brothers: Subtotal, sumif, sumifs, sumproduct, Count and sisters: count, countif, countifs
  • Finding things around: Looking-up data, texts, and values using vlookup
  • The incredible table tools technique
  • Slicing dates into day names, weeks, week numbers, month names, years and quarters
  • Text to columns and dynamic trimming using find, substitute, trim, len, and replace
  • Make me look professional: Text change functions

PIVOT TABLES: THE ONE AND ONLY!

  • The 19 Rules
  • Design Rules
    • Designing Pivot Tables
    • Number formatting techniques
    • Designing report layout
    • Sorting in ascending, descending and more sort options
    • Filtering labels and values
    • Expanding and collapsing reports
  • Analytics Rules
    • Summarize values by sum, average, minimum, maximum, count
    • Show values as % of total and % of
    • Pivot table options
    • Inserting formulas
    • Date analysis
    • Copying pivot tables
  • Visualization Rules
    • Creating pivot charts
    • Dynamic chart labeling
    • Mastering the slicer
    • Showing report filter pages
    • Linking pivot tables with PowerPoint
    • Conditional formatting with pivot tables
    • Extracting data using the ‘GetPivotData’

DATA MODELING

  • Goal Seek
  • Scenario Manager
    • Creating new scenarios, editing & deleting scenarios
    • Summarizing scenarios in Pivot TablesScenario summary reports
  • ‘what-if’ analysis using spinner
    • Designing three types of spinners
    • Number spinners
    • Percent spinners
    • Text Spinners
  • Working around spinner restrictions
  • Check box data modeling
    • Performing selective and comparative analysis
    • Linking the check box to reports and graphs
    • Designing visualization
  • Option box data modeling with ‘if’ function
    List box

    • Linking data from different sources
    • Creating graphs
    • Designing a dynamic dashboard using list box

DATA INTEGRATION

  • Linking Excel with text filesUsing get and transform data
    • Splitting columns
    • Perform data transformation
    • Data properties
    • Performing automatic refresh of data’
  • Linking Excel with databases (Access)
  • Linking Excel with multiple Excel files
  • Append data
    • Files
    • Folders

ADVANCED REPORTING USING POWER PIVOT

  • The “ETL” Extract, Load, Transform
    • Get data from various data sources (Data extract)
    • Transform Data using PowerQuery tools
    • Direct load data as tables
    • Load data to the Data Model
    • Create Relationship between data sources
    • Manage relationships
    • Advanced reporting using PowerPivot

INTRODUCTION TO LEARNING THE ULTIMATE TOOL IN EXCEL: MACROS

  • Macro basics
  • Planning a macro
  • Designing your control board
  • Recording macro
  • Testing macro
  • Editing macro
  • Macro workshops
  • Advanced filter with macro
0
    0
    Your Cart
    Your Cart is EmptyReturn to Courses
      Open chat
      1
      💬 Need help?
      Welcome to Virginia Institute of Finance and Management! 👋
      Thank you for reaching out to us.😊 How may we help you?