Course Objectives
Next Generation Excel: Advanced Business and Financial Reporting
Course Methodology
20% of the course is ‘design and structure’ focused while 80% uses MS Excel as a powerful tool to perform daily, monthly and periodic tasks. Groups and individuals will be required to complete exercises, case studies, and projects on a daily basis.
Course Objectives
By the end of the course, participants will be able to:
Apply Excel reporting expertise in business, finance, and accounting by enhancing data slicing and dicing, data massaging, and data analysis skills
Use pivot tables and pivot charts to perform automated report writing, analysis and reconciliation most efficiently
Develop special flash and management reports by linking-up Excel with Access, web, text, SQL, and other databases
Repeat tasks and generate reports efficiently by recording, running and editing macros
Acquire numerous tips and tricks that will improve working efficiency
Target Audience
Business, finance and accounting professionals, senior and junior accountants, business analysts, research professionals, marketing and sales professionals, administrative staff, supervisors, general business professionals and staff from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis.
Target Competencies
Reporting, analysis, and reconciliation
Data modeling
Integration with external data sources
Automation and macros
Massaging of unstructured and noisy data
Note
This is a hands-on training course using laptops, which will be made available by Meirc for the duration of the course. For courses outside the UAE, participants are required to bring their own laptops with a fully working version of Microsoft Excel 2013/2016.
Course Outline
Data massaging: tools and techniques
Consolidating data from separate files and sheets
Advanced data validation using lists, dates and custom validation
Cell management tools: left, right, mid, concatenate, value
Naming, editing, and managing cells and ranges
Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
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 Trim, Len
Managing texts and numbers using replace, find, and substitute
Text change functions
Reporting, analysis and reconciliations using pivot tables
The 20 must learn rules
Creating pivot tables
Number formatting techniques
Designing report layout
Sorting in ascending, descending and more sort options
Filtering labels and values
Expanding and collapsing reports
Summarize data by sum, average, minimum, maximum, count
Show values as % of total and % of
Pivot table options
Drill down option
Showing report filter pages
Inserting formulas
Date analysis
Copying pivot tables
Creating pivot charts
Dynamic chart labeling
Mastering the slicer
Linking pivot tables and pivot graphs with PowerPoint
Conditional formatting with pivot tables
Designing reports using GetPivotData
Modeling and integration techniques
Perform 'what-if' analysis using spinner
Check box data modeling with 'if' function
Option button data modeling with 'if' function
List box data modeling with 'Choose' function
Linking Excel with text files
Linking Excel with databases (Access)
Linking Excel with multiple Excel files and SQL
Linking Excel with internet
Linking Excel with Excel
Scenario manager
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
Tips and tricks in Excel
Data entry form
Custom list
Camera tool
Text to speech
Protecting worksheets and workbooks
Data massaging: tools and techniques
Consolidating data from separate files and sheets
Advanced data validation using lists, dates and custom validation
Cell management tools: left, right, mid, concatenate, value
Naming, editing, and managing cells and ranges
Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
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 Trim, Len
Managing texts and numbers using replace, find, and substitute
Text change functions
Reporting, analysis and reconciliations using pivot tables
The 20 must learn rules
Creating pivot tables
Number formatting techniques
Designing report layout
Sorting in ascending, descending and more sort options
Filtering labels and values
Expanding and collapsing reports
Summarize data by sum, average, minimum, maximum, count
Show values as % of total and % of
Pivot table options
Drill down option
Showing report filter pages
Inserting formulas
Date analysis
Copying pivot tables
Creating pivot charts
Dynamic chart labeling
Mastering the slicer
Linking pivot tables and pivot graphs with PowerPoint
Conditional formatting with pivot tables
Designing reports using GetPivotData
Modeling and integration techniques
Perform 'what-if' analysis using spinner
Check box data modeling with 'if' function
Option button data modeling with 'if' function
List box data modeling with 'Choose' function
Linking Excel with text files
Linking Excel with databases (Access)
Linking Excel with multiple Excel files and SQL
Linking Excel with internet
Linking Excel with Excel
Scenario manager
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
Tips and tricks in Excel
Data entry form
Custom list
Camera tool
Text to speech
Protecting worksheets and workbooks