VIFM - Virginia Institute of Finance and Management
 Back to catalogue
Business Reporting
Intermediate

Certificate in Data Analysis & Business Reporting Techniques Using Excel

2–5 daysENclassroom · virtual

Course overview

There is no doubt that Excel is the accountant's, finance and business professional's best friend! You and I know how overwhelmed 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.

Target audience

This course is designed to suit aspiring professionals Interested in data analytics, analysis, business reporting and data visualization. This includes: • Business Unit Managers • finance and accounting professionals • Senior and Junior accountants • Business analysts • Research professionals • Marketing and sales professionals • Administrative staff • Supervisors • General business professionals

Course objectives

  • Competency I: massaging and normalizing unstructured data.
  • Competency II: Performing reporting and analysis using Pivot Tables and Power Pivot and creating customized scorecards and management reports.
  • Competency III: Design dynamic reporting models using different modelling techniques. Perform What-If-Analysis.
  • Competency IV: Integrate Excel with many different file types such as access, web, text, SQL, and other databases. Reports will update automatically. Transforming Data using PowerQuery.
  • Competency V: Perform repetitive tasks and generate reports efficiently by recording, running and editing.

Target competencies

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

Course methodology

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.

Course outline

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

    • Insert Pivot Tables
    • Number formatting techniques
    • Change report layout
    • Sorting in ascending, descending and more sort options
    • Labels Filter, Values Filter and Top 10
    • Expanding and collapsing reports

  • ANALYTICS RULES

    • Summarize values by sum, average, minimum, maximum, count
    • Show values as % of total, difference from and % of other values
    • Pivot table options
    • Inserting calculated field
    • Date analysis
    • Copying pivot tables

  • VISUALIZATION RULES

    • Creating pivot charts
    • Dynamic chart labeling
    • Mastering the slicer
    • Show report filter pages
    • Linking pivot charts with PowerPoint
    • Extracting data using the 'GetPivotData'
    • Convert Pivot Table to Formulas using OLAP

DATA MODELING

  • Working around spinner restrictions

    • Designing three types of spinners
      • Number spinners
      • Percent spinners
      • Text Spinners

  • Check box data modeling

  • Option box data modeling with 'if' function

  • List box data modeling with CHOOSE function

  • 'what-if' analysis Goal Seek

    • Change one variable to the goal
    • Change multiple variables using Solver

  • 'what-if' analysis Scenario Manager

    • Creating new scenarios, editing & deleting scenarios
    • Summarizing scenarios in Pivot Tables
    • Scenario summary reports

  • 'WHAT-IF' Data Table

    • Calculate multiple values in a table using PMT,FV functions
    • Calculate values using one variable or multiple variables

DATA INTEGRATION USING POWERQUERY

  • GET DATA FROM DIFFERENT DATA SOURCES

    • Linking Excel with text files
      • Using 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

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.

AUTOMATION USING MACROS

  • Macro basics
  • Planning a macro
  • Recording macro
  • Testing macro
  • Editing macro
  • Relative Reference macro
  • Advanced filter with macro

Want to run this for your team?

Request a tailored quote - we'll come back with delivery options, language preferences, group-size pricing, and dates that work for you.

Request a quote