Course Objectives
Business Intelligence: Data Analysis and Reporting Techniques - Virtual Learning
Course Objectives
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 modeling
Perform data normalization, consolidation, report writing, analysis and reconciliation
Develop dynamic BI models, scorecards and flash management reports
Target Audience
Business professionals, business analysts, data analysts, research analysts, finance professionals, marketing and sales professionals, HR professionals, IT professionals, administrative staff, supervisors, general business professionals and 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.
Target Competencies
Massaging and normalizing data
Reporting, analysis and reconciliation
Modeling and 'what-if' scenarios
Data integration
Note
This is a hands-on training course using laptops. Participants are required to use their own laptops equipped with Excel 2016 or higher for the duration of the training.
Course Outline
Data analysis tools and techniquesAdvanced data validation using lists, dates and custom validationThe incredible table-tools techniqueCell management tools: left, right, mid, concatenate, valueNaming, editing, and managing cells and rangesSubtotal, sumif, sumifs, sumproduct, count, countif, countifsLooking-up data, texts, and values using vlookupSlicing dates into day names, weeks, week numbers, month names, years and quartersText-to-columns and dynamic trimming using trim and lenManaging texts and numbers using replace, find, and substituteText change functions
Mastering data reporting: the 20 must learn pivot-tables toolsCreating pivot tablesNumber formatting techniquesDesigning report layoutSorting in ascending, descending and more sort optionsFiltering labels and valuesExpanding and collapsing reportsDrill down optionSummarize values by sum, average, minimum, maximum, countShow values as % of total and % ofPivot table optionsInserting formulasDate analysisCopying pivot tablesCreating pivot chartsDynamic chart labelingMastering the slicerShowing report filter pagesLinking pivot tables and pivot graphs with PowerPointConditional formatting with pivot tablesDesigning reports using the Getpivotdata
Data modelingSpinnerCheck box data modeling with if functionOption button data modeling with if functionList box data modeling with choose functionScenario manager
Data integration and connectionsIntegrating data from Text fileIntegrating data from Excel fileIntegrating data from Web