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

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

Upcoming Dates

Course Title Level Term Location Course Date Duration / Days Price*
1Level II CFA® Program – Prep Pro Level 3 July 18, 2021 Abu Dhabi June 15, 2021 34 days (Sun,Tue,Fri) AED 1322
2CMA – Part 2 – Revision Pro Level 1 June 28, 2021 Ajman June 30, 2021 34 days (Sun,Tue,Fri) AED 1500
3CMA – Complimentary Info session Level 1 July 8, 2021 Dubai June 23, 2021 34 days (Sun,Tue,Fri) AED 1200
4Level I CFA® Program – Complimen... Level 1 June 18, 2021 Dubai June 25, 2021 34 days (Sun,Tue,Fri) AED 1
5 Level I CFA® Program – Complime... Level I June 18, 2021 Dubai June 25, 2021 34 days (Sun,Tue,Fri) AED 1
6CMA – Complimentary Info session Level 1 July 8, 2021 Dubai June 23, 2021 34 days (Sun,Tue,Fri) AED 1200
7CMA – Part 2 – Revision Pro Level 1 June 28, 2021 Ajman June 30, 2021 34 days (Sun,Tue,Fri) AED 1500
8Level II CFA® Program – Prep Pro Level 3 July 18, 2021 Abu Dhabi June 15, 2021 34 days (Sun,Tue,Fri) AED 1322

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.