Advanced Financial Modelling

Module 1 - Advanced Excel

Advanced Excel

Lookup & Reference Functions:-VLookup, HLookup, Xlookup, Index, Match, Offset, Indirect, Address, Column, Columns, Row, Rows, Choose, Arrays Concept In Lookup Formula’sSum, Sumif, Sumifs, Count, Counta, Countblank, Countif, Countifs, Average, Averagea, Averageif, Averageifs, Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct.

Text Functions & Data Validation:- Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time.

Logical Functions:-And, Or, If, Iferror, Not, Nested If, Isna, Isblank, Iserr, Iseven, Isodd, Islogical, Isytext, Max, Min

Data Management:- Data Collection Method With Data Quality, Collaboration & Security Like Share Your Workbook On Share Drive With Quality

Analysis:- Single/Multidimensional Analysis, Like Three Dimensional (3D) Tables Sensitive Analysis Like Data Table, Manual What-If Analysis, Threshold Values, Goal Seek, One-Variable Data Table,Advanced Chart, Graph & Dashboard:- Advanced Chart Technique, How To Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line Chart, Column Chart, Speedometer Chart, Gantt Chart, Two-Variable Data TableAdvanced Filter, Validation & Pivot Table:- Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data Split. Pivot Table and Charts, Import and Export Data, Protect/Unprotect Sheets/Workbooks, Worksheet Formatting and Print Display.

Module 2 - Financial Statement Analysis & Corporate Finance

Financial analysis is a process of selecting, evaluating, and interpreting financial data, with other pertinent information, in order to formulate an assessment of a company’s present and future financial condition and performance

Introduction to Financial Statements – P/L, B/S, C/F statements reflecting operating, financing and investing activities of a business.

Understanding the purpose

Understanding of key headers and sub-headers

Format of the financial statements

General Accounting Principles and best practices

Navigating the Annual Report

Common size Analysis – Horizontal and Vertical

Financial Ratios – Financial ratio analysis is the use of relationships among financial statement accounts to gauge the financial condition and performance of a company

Activity Ratios

Liquidity Ratios

Solvency Ratios

Profitability Ratios

Rate of return

DuPont Analysis

Other important ratios

Pro Forma Analysis and basic Projection technique

Corporate Finance– Understanding of capital structure and how corporate finance works is quite essential for any professional pursuing his/her career in Finance. It gives a bird’s eye view of an organisation and provides an understanding of how various financing activities impact the valuation of a firm. CFO of any company endeavours to optimize the capital structure by selecting the right financing tools to address the operating and investing needs of a company.

Time value of money

NPV and IRR

Future Value

Excel formulas like NPV, XNPV, IRR, XIRR, PV, FV, etc.

Understanding of Debt

Understanding schedules for debt repayment and interest repayment

Developing the schedules on excel

Capital Structure and Cost of Capital

Debt and Equity

Cost of Debt

Cost of Equity

WACC

Understanding tax shield

Measure of Leverage

Impact of leverage on cost of capital

CAPM model and Beta calculation

Understanding of Beta

Levered and Unlevered Beta

Calculation of levered Beta

Calculation of cost of equity

Free Cash Flow

Free Cash flow to Firm

Free Cash flow to Equity

Valuation: Valuation is the Process to determine the current worth of asset. In SLA course, the asset is a company. For the valuation of companies, following are the three widely used techniques.

DCF Valuation

Relative Valuation

Transaction Valuation

Application of these techniques with real case studies and companies

Module 3 - Financial Modeling

Module 4 - Advanced Dashboard & VBA / Macros Programming Objective

Module 5 - SAP FICO