Data Analytics with Excel (Advanced Excel, Financial and Accounting)

• Basic knowledge of Excel

Audience

• Business manager
• Analyst
• Accountant

Introduction

• Introduction to the Data and Data Formats.
• Protection of Cells, Rows, Columns and Sheets.
• Password protection to the Worksheet, Sheets, Rows, Columns, and Cells.
• Text Functions: Concatenate, Left, Mid, Right, Lower, Upper, Proper, Replace, Rept, Find, Search, Substitute.
• Logical Functions: If, If with OR, If with AND, If with AND &OR, If with OR&AND,
• Nested If (For Multiple Conditions.
• Introduction to Name Manager.
• Math & Trig Functions: Sumif, Sumifs, Subtotal
• Statistical Functions: AverageA, Averageif, Averageifs,CountA, Countblack, Countif, Countifs
• Lookup Functions: Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset. Choose.
• Use of Data Validation.
• Use of Data Validation as a magical tool.
• Introduction to the Interactive Charts & Dashboard.
• Adding Switches to Dashboard.
• Use of Multiple formulas for Dashboard.
• Pivot Table.
• Pivot Chart & Slicers.

Introduction to VBA

• What Is VBA? Need and Application of VBA
• Introduction to Developer tab
• Introduction to Macro Recordings: Using the Excel Macro Recorder, Macro Security, Recording Macro, Naming Macro, Executing Macro, Saving and Editing Macro
• Working In the Visual Basic Editor: Project Explorer, Properties window, Object Browser, Standard Module and Sheet Module
• Debugging mode, Breakpoints, Bookmarks, Watch window, immediate window and Locals window, Inbuilt VBE Help feature
• Introducing the Excel Object Model: Application, Workbooks, Worksheet Objects.
• Variable, Constant and Data types: Variable Naming rules, Declaring, Initializing Variables, Option Explicit, Object Variables.
• Simple Dialog Boxes: Message boxes and Input boxes

VBA Language Detailed Concepts

• Scope and lifetime of variables
• VBA Sub and Function Procedures
• Using VBA and Worksheet Functions
• Working with Range Objects: Properties and Methods of Ranges – Range referencing, selection, Active cell, cells and offset properties, resize ranges, value, formula and text properties, clear, delete, copy, paste and format ranges, Filters, special cells method, union and intersect ranges, current region property, working with dynamic ranges, last cell, last row and last column
• Workbooks and Worksheets: The Workbooks Collection, Getting a Filename from a Path, Files in the Same Directory, Overwriting an Existing Workbook, Saving Changes, The Sheets Collection, Worksheets, Copy and Move

Controlling Program Flow with Loops and Logical & Error Handling

• Using Conditional constructs & Loops: If-Then-Else, Select-Case, And/Or conditions, Using Looping constructs: For-Next, For-Each, Do-While, Do-Until Decision-making and Code Branching, Using Label Constructs
• Multiple Inner Loops for Detailed Looping Concept.
• Automatic Procedures and Events
• Types of Errors: Design Errors, Compile Errors, Runtime Errors. Logical Errors.
• How to Debug the Errors.

Writing of Formula in VBA

• Introduction of programming concepts for writing the Excel Formulas in to the Program.
• Difference in between the concept of “Worksheet function Vs. Activecell Formula”
• Difference between the A1 and R1C1 style of writing formula in VBA.
• Look at the style A1 and R1C1 and decide which is better.
• Find out the better solution to write the formulas in VBA.
• Writing of the basic formulas in to VBA.
• Writing of the complex formulas in to the VBA.
• Writing of Logical formulas and Lookup formulas in VBA.
• Freezing of the formula in VBA.
• Other useful formulas to be write in Class.
• Excel Multiple Formulas Writing in VBA.

Error Handling and Arrays

• Data Validation & Input restrictions Effective Coding
• Testing and debugging your code
• Error-Handling Techniques
• Bug Extermination Techniques
• Using On error Go to and On error Resume next
• Introduction to arrays
• Static Arrays, Dynamic Arrays
• One dimensional, Two dimensional and multidimensional arrays

Using Names, Sort and Filter

• Named Ranges: Naming Ranges, Using the Name Property of the Range Object, Working with Named Ranges, Determining which Names Overlap a Range
• Sorting: Structuring the Data, Sorting a Range, Sorting a Table,
• Filter: AutoFilter Object, Filter Object, Date Custom Filter, Adding Combo Boxes, Copying the Visible Rows, Advanced Filter

PivotTables and Pivot charts with VBA  Macros

• PivotTables and Pivot charts: Creating a PivotTable Report, PivotCaches, PivotTables Collection, PivotFields, CalculatedFields, PivotItems, Grouping, Visible Property, CalculatedItems, Updating Pivot Tables, Synchronizing multiple pivot tables, PivotCharts, External Data Sources

Advanced VBA Functions

• User Define Function (UDF): Detailed discussion on User Defined Function: What is User Defined Function, Use of User Defined Function and How to create any Function OR Formula which is not available in Excel and you want it to work for you by the help of Macro.
• Create Your Own Formulas With VBA Macros Coding.
• ADD-IN: Discussion on ADD-IN: What is ADD-IN, Use of ADD-IN and How to create ADD-IN.
• Working With VBA Events: Discussion on VBA Events, What are Events, How and when to use the VBA Events.
• Detailed Programing in Worksheet and Workbook Events.

Charts and Dashboards With VBA Macors

• Charts: Chart Sheets-Adding a Chart Sheet Using VBA Code, Embedded Charts, Using the Macro Recorder, Adding an Embedded Chart Using VBA Code, Creating charts, Defining Chart Series with Arrays, Converting a Chart to Use Arrays, Changing chart type and series type, Adding secondary axis, Changing chart marker options, Determining the Ranges Used in a Chart, Creating Dynamic Charts, Chart Labels, Copying Charts as picture/chart/excel objects into other Microsoft application.
• Creating Interactive Dashboards : Introduction to dashboard and interactive dashboards, Creating dashboard using Named ranges, Charts, Tables, Pivot Tables and user forms, Choosing the right chart – Bullet Chart, Thermo meter chart, Using Alerts in dashboards, Interactive Dashboard Examples..

Example of Analysis Requiring Merging Data:

• Sample problem which used the sample problem will be made similar to day to day scenario of client

Example of Unifying Data :

• Data for the case study
• Analysis of the data
• Solution in Powerpoint

Example of Analyses :

• Example of analysis in Excel
• Generally we analyze for Sales data but it can be customized depending on client requirement 
• Efficiency of marketing activities
• Model of E-commerce
• Expansion strategy into other countries – Case Introduction
• Expansion strategy into other countries – Data for the case study
• Expansion strategy into other countries – Which function to use
• Expansion strategy into other countries – Solution in Excel
• Expansion strategy into other countries – Solution in Power Point

Binary Classification:

• Introduction to Binary Classification
• Bombers and Seagulls: Confusion Matrix
• Costs Determine Optimal Threshold
• Calculating Positive and Negative Predictive Values
• How to Calculate the Area Under the ROC Curve
• Binary Classification with More than One Input Variable

Exercise  : Binary Classification (practice)

Information Measures:

• In this module, you will learn how to calculate and apply the vitally useful uncertainty metric known as “entropy.” In contrast to the more familiar “probability” that represents the uncertainty that a single outcome will occur, “entropy” quantifies the aggregate uncertainty of all possible outcomes.
• The entropy measure provides the framework for accountability in data-analytic work. Entropy gives you the power to quantify the uncertainty of future outcomes relevant to your business twice: using the best-available estimates before you begin a project, and then again after you have built a predictive model. The difference between the two measures is the Information Gain contributed by your work.
• Quantifying the Informational Edge
• Probability and Entropy
• Entropy of a Guessing Game
• Dependence and Mutual Information
• The Monty Hall Problem
• Learning from One Coin Toss, Part1
• Learning From One Coin Toss, Part 2

Linear Regression:

• Introducing the Gaussian
• Introduction to Standardization
• Standard Normal Probability Distribution in Excel
• Calculating Probabilities from Z-scores
• Central Limit Theorem
• Algebra with Gaussians
• Markowitz Portfolio Optimization
• Standardizing x and y Coordinates for Linear Regression
• Standardization Simplifies Linear Regression
• Modeling Error in Linear Regression
• Information Gain from Linear Regression

Other Useful functions and tools in Excel :

• Conditional Formatting
• How to use indirect function
• Histograms
• Regression - introduction
• Regression - example
• Data validation
• Using solver – Planning the flow of finished goods – Introduction
• Using solver – Planning the flow of finished goods – Excel - First Option
• Using solver – Planning the flow of finished goods – Excel - Second Option
• Sample Example Problem
• Conditional Formatting
• How to use indirect function
• Histograms
• Regression - introduction
• Regression - example
• Data validation
• Using solver – Planning the flow of finished goods – Introduction
• Using solver – Planning the flow of finished goods – Excel - First Option
• Using solver – Planning the flow of finished goods – Excel - Second Option
• Sample Example Problem and different case studies

Note: All the exercises given will be focused on finance sector .