What does the Excel Analytics program cover?

Gain mastery over key Microsoft Excel tools and functions that will enable you to automate daily tasks, enhance efficiency and accelerate performance. Discover the industry techniques and best practices in analyzing and presenting financial data.

Learning Objectives

icon

Grasp keyboard shortcuts to improve speed and efficiency

icon

Become well-versed with the excel quick access tool bar and cell styling tools

icon

Master various intermediate and advanced Excel functions

icon

Produce high quality & dynamic charts for data presentations

icon

Learn and apply pivot table to analyze large volumes of data

icon

Perform various statistical analysis in excel

img

Who is it for?

The course is suitable for anyone in the financial services industry who wants to improve their excel skills.  Professionals who are working or aspiring to work in the field of of equity research, investment banking, financial accounting and analysis, budgeting and data analytics can pursue this course.

Program Agenda

Formatting and Efficiency Skills

– Custom number formatting
– Conditional formatting – Basic and Advanced
– Cell styles template – How to create, apply and update
– Excel Quick Access Tool Bar – How to set up
– Keyboard shortcuts for speed and efficiency
– Cell Names and Range Names – Create, edit and delete
– Auditing and Analysis file/model: Auditing tool- F5(special)

Working with Functions

– Basic Math Functions: SUM, AVERAGE, MAX, MIN, SUMIF, COUNTIF, LARGE, SMALL
– Logical Functions: IF and nested IFs, AND, OR
– Date functions: EOMONTH, EDATE, YEARFRAC, DATE, TODAY
– Lookup functions: VLOOKUP, HLOOKUP, LOOKUP
– Text functions: RIGHT, LEFT, LEN, TRIM, PROPER, UPPER, LOWER
– Advanced Data Analytics functions: SUMPRODUCT, INDEX, MATCH, OFFSET, CHOOSE, AVERAGEIF, MEDIANIF, MAXIF, MINIF
– Time Value of Money functions: PV, FV, PMT, NPV, RATE, NPER, IRR, XNPV, XIRR, MIRR
– Statistical functions: CORREL, STDEV, NORM.INV, LARGE, SMALL, MEDIAN, MODE, PERCENTILE

Tools to improve efficiency

– Data Validation: Drop-Down Lists & Customizing spreadsheets and controlling data inputs
– What If Analysis: Data Table: Sensitivity Analysis, Goal Seek and Scenario Manager & Scenario Analysis

– Developer tool: VBA Drop-Down Lists, Check Boxes & Working with large Data Sets: Sorting, Sub-totals, Filtering & Using Excel “Wild-Card” tools for Data Mining

Charting in excel

– Types of charts
– Choosing the right chart for the data
– Dynamic chart titles and labels
– Combo Charts & Interactive charts – Checkboxes and Dropdowns
– Chart Annotations

Pivot Tables

– Uses of Pivot tables
– Adding fields to the report: Filters, Rows, Columns, Values
– Placement of pivot table fields
– Adding layers & rearrangement
– Changing Values field: SUM, MAX, MIN, AVERAGE, etc.
– Inserting calculated fields
– Creating a report filter
– Renaming of your pivot table
– Moving the pivot table Defer layout update (large data sets)
– Clearing all and data refresh

Advanced Financial Analytics

– Simple and Multiple Regression Analysis tools
– Monte Carlo simulation: Predict investment performance
– Bell curve: Analyze investment performance
– Calculate historical and analytical Value at Risk (VaR)
– Hedge fund return analytics: LP perspective
– Private Equity Fund analytics: LP perspective

Our Expert Trainers

What Our Students Say

Request a Call Back

The answer to your doubts is just a click away! Simply fill in your details in the form, and we will get in touch with you shortly.