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 Table

Advanced 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

Financial Modeling – Financial Modeling refers to the process of building a structure that integrates the Balance Sheet, Income Statement, Cash Flow Statement and supporting schedules to enable decision making in areas like, Business Planning and Forecasting, Equity Valuation, Credit Analysis/Appraisal, Merger/acquisition analysis, Project Appraisal etc.

Trainees learn how to build full, dynamic Financial Statement Projection models in Excel from scratch, using real case studies and sensitivity analysis.

Participants develop a model completely from scratch, inputting historical data and assumptions to project financial statements using step-by-step instruction on selecting and developing appropriate projection drivers. At completion, participants will have developed a complete and integrate three-statement model using various supporting schedules.

Understanding an integrated financial Model

Understanding an integrated financial Model

Building Integrated Model – Financial Statements & Projections for an Equity

Creating a template for integrated model

Making Assumptions for forecast

Developing various schedules

Depreciation – Capital Expenditure Phasing, SLM and % methods

Working capital

Debt and Interest

Integrating all the financial statements

Summarizing financial statements

Case Study: Building Integrated Model – Financial Statements & Projections

Building Integrated Model – Valuation

Using DCF and comparables

Summarizing financial statements

Advanced Modelling concepts and techniques

Common Modeling errors- circular reference errors to avoid

Module 4 - Advanced Dashboard & VBA / Macros Programming Objective

Advanced Dashboard & VBA / Macros Programming Objective

To Define KPIs (Key performance Indicator), Customer Service Dashboards or Project Management Dashboard (Gantt Chart)

Dashboard Reports Based on Tables and Number or Charts/Graphs or Both.

Introduction to Programming Introduction to logical thinking flowcharts & algorithms:

Define Objective, Start & End Points; Identifying Solution & Breaking it Into Sequential Steps Writing

Step-by-Step Instructions, Flowcharts, Process Flow Diagrams. Excel Macros – an Introduction

Complete Review of the VBA Language (Subs, Functions, Variables, Arrays, Loops, Logic…etc.)

Excel Macro Language Review (VBA) Including Variables, Data Types, Constants, Arrays, Operators, Expressions, Loops, Logic Decisions And Calling

Overview Of Commanding Excel Using VBA Including A Discussion Of Objects, Properties And Methods

The Power of Macros – What, When, How to use Macros. Introduction to Object Oriented Programming

Objects, Its Functions, Methods and Properties Introduction to Events

Details of Events, How & When to use of Events, Preparing to ‘Macro’ Visual Basic Editor (VBE) – Developer Tab, Security

Introduction to the VBE, Properties window, Project Explorer, Password Protection of Code How to use the VBE – Features, Options, Intelligence Technology

Debugging Mode, Bookmarks, Breakpoints, Watch Window, Immediate Window and Locals Window Inbuilt VBE Help Feature – Tips and Tricks

Form Controls vs. ActiveX Controls Getting into the Code

Message Box and Input Box Working with Data in Excel through VBA

Data Types, Constants and Variables

Different type of data type; How and When to use Variables to Store Information.

For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making and Code Branching

If-Then-Else, Select-Case, And/or Nested Conditions

What is user’s Defined Functions? How to create & use them.

Use of Arrays in VBA programming with one dimensional, two dimensional or multi-dimensional analysis

Excel VBA Power Programming For VBA Macros

Overview of Using User forms To Create Business Wizards

Working with User Forms & User Forms Events like List box, Combo box, Option Buttons, Check box, Text box, Labels, Command button, Toggle button.

How to create dynamic dashboard on user form with different controls

How to link various user form with each other to create a complete interface between user and system

Connection between Excel VBA & other platforms

Testing and Debugging Your Code

Effective Error Handling

Automation Development Reports & Live Projects

Module 5 - SAP FICO

Training Material

Question and Answer for each chapter

Real life scenario discussion

Unit 1: SAP FICO Overview

Lesson:Introducing SAP

Lesson:Introducing SAP FICO

Unit 2: Core Financial Accounting (FI) Configuration

Lesson: Managing Organizational Units in Financial Accounting (FI)

Exercise 1: Create a Company Code

Exercise 2: Create a Company ID for Your Company Code and assign it to Your Company Code

Exercise 3: Exercise 4: Check the Assignment of a Company Code to a controlling Area

Lesson: Checking the Basic Settings in General Ledger (G/L) Accounting

Exercise 4: Check the Basic Settings in G/L Accounting

Lesson: Outlining the Variant Principle

Lesson: Managing Fiscal Year Variants

Exercise 5: Create and Maintain Fiscal Year Variants

Lesson: Identifying the Basic Functionality of Currency Keys and exchange Rate Types

Lesson: Managing Currency Types

Unit 3: Master Data

Lesson: Maintaining General Ledger (G/L) Accounts

Exercise 6: Create Account Groups for G/L Accounts

Lesson: Creating Profit Centers and Segments

Exercise 7: Create Profit Centers, Cost Centers, and Segments

Lesson: Managing Business Partners

Exercise 8: Create Account Groups and Maintain Customer or vendor Accounts

Exercise 9: Maintain Field-Status Configuration

Exercise 10: Define Sensitive Fields

Unit 4: Document Control

Lesson: Configuring the Header and Line Items of Financial Accounting (FI) Documents

Exercise 11: Create a Number Range and Document Type

Exercise 12: Classify the Document Type for Document Splitting

Exercise 13: Create a Field Status Group and Assign It to a G/L Account

Lesson: Managing Posting Periods

Exercise 14: Create and Maintain Posting Period Variants

Lesson: Managing Posting Authorizations

Exercise 15: Create a Tolerance Group and Assign It to a User

Lesson: Creating Simple Documents in FI

Exercise 16: Post FI Documents

Unit 5: Posting Control

Lesson: Analyzing Document Splitting

Exercise 17: Post a Document with Document Splitting

Lesson: Maintaining Default Values

Exercise 18:Maintain Default Values

Lesson: Configuring Change Control

Exercise 19: Maintain the Change Control for a Field

Lesson: Configuring Document Reversal

Exercise 20: Post a Document Reversal

Lesson: Configuring Payment Terms and Cash Discounts

Exercise 21: Maintain Terms of Payment

Lesson: Maintaining Taxes and Tax Codes

Exercise 22: Create a Tax Code and Post a Customer Invoice

Lesson: Posting Cross-Company Code Transactions

Exercise 23: Configure Cross-Company Code Transactions

Exercise 24: Post and Display Cross-Company Code Transactions

Unit 6: Financial Document Clearing

Lesson: Performing Open Item Clearing

Exercise 25: Clear an Account

Exercise 26: Post with Clearing

Lesson: Managing Payment Differences

Exercise 27: Manage Payment Differences

Lesson:Managing Exchange Rate Differences

Unit 7: Automatic Payments

Lesson: Executing an Automatic Payment Run

Lesson: Using Bank Accounts

Exercise 1: Define a House Bank

Lesson: Modifying Master Records and Financial Accounting documents

Exercise 2: Modify Vendor Master Records

Exercise 3: Enter Invoices for Payments

Unit 8: Special General Ledger Transactions

Lesson: Managing the Application View for Special General Ledger Transactions

Exercise 17: Enter a Guarantee Made

Exercise 18: Post a Down Payment Request, a Received Down Payment, and a Customer Invoice, and Perform Clearing

Exercise 19: Post a Customer Invoice and Enter an Individual value Adjustment

Lesson: Maintaining Special General Ledger Transactions

Exercise 20: Configure Special G/L Transactions

Lesson: Understanding Purchasing (MM Integration)

Exercise 21: Perform an Integrated Down Payment Process

Lesson: Understanding Sales and Distribution Integration

Unit 9: Parking Documents

Lesson: Managing Document Parking Basics and Document Holding

Exercise 22: Hold and Park Documents

Lesson: Processing Parked Documents

Exercise 23: Process Parked Documents

Lesson: Managing Document Parking and Workflow

Exercise 24: Enter a Financial Document Using Parking and Workflow

Unit 10: Receivables and Payables

Lesson: Creating Balance Confirmations

Exercise 5: Create a Balance Confirmation

Lesson: Managing the Foreign Currency Valuation

Exercise 6: Manage the Foreign Currency Valuation of Open Items

Lesson: Managing Value Adjustments

Exercise 7: Execute a Flat-Rate Individual Value Adjustment

Lesson: Regrouping Receivables and Payables

Exercise 8: Regroup Receivables According to Their Remaining Life

Unit 11: Accruals and Deferrals

Lesson: Posting Accruals and Deferrals

Exercise 9: Post Accruals

Unit 6: Technical, Organizational, and Documentary Closing Activities

Lesson: Managing Technical Steps During the Period-End Closing Activties

Lesson: Executing the Balance Audit Trail

Exercise 10: Execute the Balance Audit Reports

Lesson: Explaining the Purpose of Document Splitting in General Ledger Accounting

Lesson: Posting Ledger Group-Specific FI Documents

Exercise 11: Post to a Ledger Group

Unit 12: Asset Accounting Organizational Structures

Lesson: Assigning a Chart of Accounts and a Chart of Depreciation to a Company Code

Exercise 1: Assign a Chart of Depreciation to a Company Code

Lesson: Assigning Management Accounting Objects to Fixed Assets

Lesson: Defining How Depreciation Areas Post to the General Ledger

Exercise 2: Define Depreciation Areas and the Update of the General Ledger

Lesson: Understanding the Importance of Asset Classes in Fixed Assets

Unit 13: Asset Accounting Master Data

Lesson: Explaining the Components of an Asset Class

Exercise 3: Use Number Assignment for FI-AA

Exercise 4: Use Account Determination

Exercise 5: Maintain the Screen Layout of Asset Master Data

Exercise 6: Maintain the Screen Layout of Asset Depreciation Areas

Exercise 7: Copy Asset Classes from Reference

Exercise 8: Display Asset Class 4000

Exercise 9: Manage Asset Classes for Low-Value Assignments

Lesson: Creating and Changing Master Data in Asset Accounting

Exercise 10: Create Asset Master Records

Exercise 11: Maintain Time-Dependent Data

Lesson: Carrying Out Mass Changes to Asset Master Data Using a Worklist

Exercise 12: Define the User Fields and Mass Changes of Asset Master Records

Unit 14: Asset Transactions

Lesson: Processing Acquisitions

Exercise 13: Post an Integrated Asset Acquisition and Analyze the Asset Value in the Asset Explorer

Lesson: Posting Non-Integrated Asset Acquisitions and understanding the Integration with Materials Management (MM)

Exercise 14: Make Various Non-Integrated Asset Acquisition Postings

Exercise 15: Make Postings Integrated with Materials Management

Lesson: Posting Integrated and Non-Integrated Asset Retirements

Exercise 16: Post Integrated Asset Retirements with Accounts Receivable (AR)

Lesson: Performing Transfers Within Company Code and Intercompany Asset Transfers

Exercise 17: Transfer Assets Within Company Code

Exercise 18: Post Intercompany Asset Transfer (Across Company Boundaries)

Exercise 19: Post Intercompany Asset Transactions (Within One company)

Lesson: Representing, Distributing, and Settling Assets Under Construction

Exercise 20: Create and Post to an Asset Under Construction (AUC) and Settle It to Completed Assets

Lesson: Entering and Analyzing Unplanned Depreciation

Exercise 21: Post Unplanned Depreciation

Unit 15: Periodic Processing and Valuation

Lesson: Defining Depreciation Areas, Keys, Calculation, and Posting

Exercise 22: Analyze Depreciation Areas

Exercise 23: Understand and Maintain Depreciation Keys

Exercise 24: Maintain Time-Dependent Depreciation parameters

Exercise 25: Analyze Cost-Accounting Depreciation Area

Exercise 26: Execute and Analyze the Depreciation Run

Lesson: Executing Programs for Fiscal Year Change and Year-End Closing in Asset Accounting

Exercise 27: Execute Year-End Closing in Asset Accounting

Lesson: Using the Accounts Approach for Parallel Accounting in Asset Accounting

Exercise 28: Manage Parallel Accounting in FI-AA

Exercise 29: Manage Unilateral Asset Postings