Advanced Techniques for Data Analysis

Introduction

At present, employees find themselves in a situation where processing vast amounts of unstructured information, data, or material has become a significant challenge. Over time, databases and ERPs have been normalized, enabling organizations to manage large quantities of data. However, the key question remains: how can this data be transformed into a valuable asset?

This Advanced Techniques for Data Analysis program is designed to equip you with effective business intelligence (BI) knowledge. You will acquire the ability to clean, normalize, and analyze large datasets. By applying these techniques, you will be able to identify trends from past events, understand current conditions, and develop plans for the future.

Management-level BI tools are integral to decision-making processes at operational, tactical, and strategic levels within an organization, making them valuable in any business context.

This course has a cross-functional nature and is targeted at users from all departments who engage in data cleaning, reporting, analysis, modeling, integration, and automation. Throughout the program, you will exclusively use MS Excel as a BI tool, enabling you to attain an advanced level of expertise and enhance the quality of your contributions to both your position and the organization.

Objectives

Upon completing this Advanced Techniques for Data Analysis course, participants will be able to:

• Confidently use Excel Business Intelligence (BI) for data slicing, dicing, manipulation, aggregation, and modeling beyond basic table functions.
• Manage data normalization, consolidation, report generation, analysis, and reconciliation without hesitation.
• Design dynamic BI models, dashboards, balanced scorecards, and management reports while integrating data from Excel, Access, the Web, Text, Internet, SQL, ERPs, and other databases.
• Improve the appearance and content of reports using advanced visualization tools.
• Gain a wealth of tips and tricks to work faster and smarter.

Training Methodology

• Use of interactive lectures and discussions
• Practice on real data sets
• Use of case studies
• Group work and collaborative learning
• Guided practice with the more complex features of Excel
• Data analysis competitions with debriefs
• Individual and small group support
• Simulations to teach business scenarios
• Periodic quizzes to evaluate participants' understanding
• Final project demonstrating acquired techniques

Course Outline

Unit 1: Making Sense of Data Analysis

• Bringing together information stored in various files and sheets
• Use of lists, dates, and custom validation to create and validate data at a higher level
• Using array functions at an advanced level
• Tools used to control cells include left, right, mid, concatenate, and value
• Naming cells, editing their contents, and placing them in ranges
• Excel’s Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, and Countifs
• Using Vlookup in Excel data
• Mastering the table-tools technique
• Decomposing dates into days of the week, weeks, week numbers, months, years, and quarters
• Splitting cell data using text to columns and utilizing the trim and len functions
• Wording items and numbers through the aid of find, replace, or substitute
• Using wording change functions

Unit 2: Effective Reporting

• 20 essential tools for mastering pivot-tables
• Building and designing pivot tables
• Numbering techniques
• Preparing report layouts
• Sorting in ascending, descending, and other custom orders
• Queuing labels and values
• Collapsing, expanding, and summarizing reports
• Typing values by sum, average, min, max, count, and more
• Showing measurement fractions as parts of a whole
• Moving around fields in a pivot table
• Writing necessary formulas for advanced analysis
• Analyzing collected data
• Visualizing tables as graphs and editing dynamic charts
• Proficient use of slicers
• Grouping report pages for filters
• Integrating tiles and brushes into presentations using PowerPoint
• Formatting for maximum visual impact
• Stylizing reports with the getpivotdata function

Unit 3: Data Modeling and Integration

• Spinner
• Checkbox 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)
• Connecting Excel with SQL
• Linking Excel with the Internet
• Linking Excel with Excel

Unit 4: Overview of Financial Modelling

• Define the terms model and financial model
• Learn the 10 steps to create robust financial models
• Upgrade your model using flow-charting techniques
• Explore strategic and operational business perspectives
• Understand the organizational planning model
• Product/decision/information cycle
• Purposes of financial analysis
• Value-added net generating new wealth
• Basic metrics of wealth creation and finance

Unit 5: Use of Time Series Analysis and Evaluating Investment Portfolio

• Formation of time series models with histograms and moving averages
• Use of exponential smoothing and regression analysis for data analysis
• Training in exponential smoothing as a tool
• Validation of time series analysis
• Understanding sensitivity analysis and its importance
• Creating “What-if” scenarios in financial or operational models
• Using Excel’s scenario and goal seek tools
• Measuring the risk of equities in individual stocks
• Plotting expected returns against risk using variance
• Applying Modern Portfolio Theory and the Capital Asset Pricing Model
• Constructing a balanced portfolio
• Evaluating the beta of ordinary shares using Excel

Unit 6: The Look and Feel

• Methods and rapid visual representations of data
• Working with the camera tool
• Using visualization tools based on formulas
• Using custom fonts
• Creating figures in formulas
• Embedding sparklines
• Designing non-typical charts

Unit 7: Tips and Tricks

• Managing and securing reports, templates, worksheets, and workbooks
• Using the data entry form
• Creating custom lists
• Public speaking with text
• Advanced conditional formatting techniques
• Stamping primary keys in every document to enhance daily activities