By the end of the course, participants will be able to:

• Boost Excel Business Intelligence (BI) expertise in data slicing and dicing, data massaging, and data
• aggregation and modelling
• Perform data normalization, consolidation, report writing, analysis and reconciliation
• Develop dynamic BI models, dashboards, scorecards and flash management reports by linking-up Excel with
• Access, Web, Text, Internet, SQL, ERPs and other databases.
• Advance and enhance the look and feel of reports using dynamic visualization technique
• Acquire numerous tips and tricks that enable them to work efficiently

Business Intelligence: Data Analysis and Reporting Techniques is ideal for:

• Business professionals, business analysts, data analysts, research analysts, finance professionals,
• Marketing and sales professionals,
• HR professionals, IT professionals, administrative staff, supervisors, general business professionals
• Project Managers, Project Team Members
• Staff from any function who need to learn and apply state-of-the-art data analysis techniques to their daily
• business reporting and decision making

• Delegate discussion and involvement
• Case studies
• Exercises
• Best practice examples

Day 1

Data analysis tools and techniques

• Consolidating data from separate files and sheets
• Advanced data validation using lists, dates and custom validation
• Powerful array functions
• 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 and len
• Managing texts and numbers using replace, find, and substitute
• Text change functions

Day 2

Mastering data reporting: the 20 must learn pivot-tables tools

• 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
• Drill down option
• Summarize values by sum, average, minimum, maximum, count

Day 3

Data modeling and integration

• Spinner
• Check box data modeling with if function
• Option button data modeling with if function
• List box data modeling with choose function
• Scenario manager
• Linking Excel with text files
• Linking Excel with databases (Access)
• Linking Excel with SQL
• Linking Excel with internet
• Linking Excel with Excel

Day 4

The look and feel: charting and visualization techniques

• Using the camera tool
• Working with formula-driven visualizations
• Using fancy fonts
• Leveraging symbols in formulas
• Working with sparklines
• Creating unconventional style charts

Day 5

Tips and tricks

• Controlling and protecting your reports, worksheets and workbooks
• Data entry form
• Custom list
• Text-to-speech
• Advanced conditional formatting
• Shortcuts for your daily work

CDGA attendance certificate will be issued to all attendees completing minimum of 80% of the total course duration.