-->

## Course Instructor/s

EduEdgePro Pvt Ltd & Moody’s Analytics

### COURSE DESCRIPTION

Advanced Excel and Functionalities course is specially designed to impart practical skills that are in line with industry best practices. The course enables a student to apply advanced Excel skills such as Lookups, Data Validation, Sensitivity and Scenario Analysis to financial datasets. This course also focuses on aggregation and analysis of data using statistical functions, mathematical functions, tables, logical functions, conditional formatting and effectively presenting the data in the form of dashboards using charting and graphing techniques. Student pursuing this course will be able to handle large datasets and effectively analyze and present them in an elegant manner by the end of this course.

Credit Value : Full

Programme & Semester : B.CoM. – Specialization in Financial Analytics Semester – 2

Pre-requisite : Microsoft Excel Basics

### COURSE OBJECTIVES

• Applying Excel functions and functionalities to Finance
• Understand concepts in Financial Management, Statistical Finance and Corporate Finance using Excel
• Learning how to analyze financial datasets in Excel
• Learning how to create financial dashboards with applications to Finance and Banking

### Learning Outcomes

• Learn basic and intermediate Excel functions and functionalities
• Learn how to perform advanced searches, lookups, filters and aggregations on financial datasets
• Understand and learn how to perform scenario and sensitivity analysis in Finance
• Learn how to aggregate and validate datasets
• Learn how to build basic applications and dashboards in Excel for Finance
• Learn how to perform Statistics for Finance in Excel
• Understand how to use conditional formatting, charting and data validation for financial data sets and dashboarding
• Learn how to create financial reports

### Course Evaluation

 Continuous Assessment 40% Class Participation 5% Projects 20% In-class assignments 15%

#### Detailed Curriculum

##### Advanced Excel Functions on financial datasets:
• Lookups and Searches (within table, across tables)
• Multiple Lookups and Reverse Lookups
• Applications of Sumifs and Countifs
• Conditional Sumproducts and dashboarding
• Usefuls functions - string / statistical / date-time / financial
• Error Handling Functions
• Formula Auditing
##### Advanced Lookups and Searches on financial datasets
• Reverse lookups, dynamic searches
• Multiple Lookups with applications to financial data sets
• Multi-dimensional searches in Excel
• Applications on financial time series and datasets
##### Data Aggregation techniques in Excel for financial datasets
• Data Aggregation methods
• Dynamic modeling using Named Ranges
• Using Tables as a powerful way to build dynamic formulae
• Aggregation using Pivot Tables & Techniques
• Filtering Techniques
• Conditional aggregation techniques (using datasets)
##### Dashboard creation:
• Applications from Finance for aggregating financial datasets and financial time series
• Writing Complex Logic and applied examples
##### Using Excel for Advanced functionalities in Finance
• Solver & Optimization Techniques
• Scenario Analysis
• Sensitivity Analysis
• Goal Seek
• Examples from various finance and banking domains on applications of above principles
##### Data Validation Techniques
• Using data validation rules to create financial models
• Using multi level data validation and linkages in a financial model
• How to effectively use data validation techniques
##### Important Excel to-knows for financial modeling
• Conditional Formatting
• Charting & Graphing Techniques
• In-cell charting - Sparklines, Inline Charts, etc
• Security Features