Certified Capital Markets Program (CCMP)

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%
Mid Term 20%
End Term 40%

Advanced Excel and Functionalities

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
  • Other Advanced Excel Functionalities

End Term Examination


Core Reference:

Moody’s Analytics and EduEdgePro (2016), Certified Program in Financial Modeling and Analytics, 1st edition

Additional References:

Benninga, S. (2014), Financial Modeling with Excel, 4th edition, The MIT Press

Sengupta, C. (2011), Financial Analysis and Modeling Using Excel and VBA, 2nd edition, Wiley