One Day workshop on Excel Based Financial Modeling

Target Audience Target AudienceMBAs, Managers at all levels,Entrepreneurs and Start up Leaders

Independent Directors and Management Consultants

CAs, CFAs, CMAs, CSs (including semi qualified)

Finance and Accounting Executives in KPO / BPO Entities

Finance and Accounting Executives Finance and Accounting FacultyEquity Research Analysts Investment and Portfolio ConsultantsShare Broking Executives Marketing Directors / ConsultantsSelf Employed Accounting and Finance Professionals / Consultants

Post Graduate and Research Students in Accounting, Finance and Banking

Project Consultants Rating AnalystsFinancial Analysts Bankers

1

Resource Person Workshop will be conducted by CA Mr.

Nalayiram Subramanian, M.Com., FCA,CISA (ISACA, USA), a CharteredAccountant and an Information SystemsAuditor. He specialises in FinancialResearch & Analysis, Financial Modeling,Consulting and Audits.

He possesses more than a quarter of acentury experience, taking roles such asa Financial Controller & MIS Head inIndustries with Domestic and MNCs(including stints abroad), Head ofFinance Faculty in Management &Professional Education, Resource Person-Business Valuation, Corporate, Faculty &Management Development Programsand as a Statutory, Internal , InformationSystems and Management Auditor.

He has authored a book,-“Contemporary FinancialAccounting and Reporting forManagement – a holisticperspective”, a book that has beenreviewed by all India banks’association, viz., IBA and TheCorporate India Journal.http://fintegratedsolutions.com/pub.html

2

_:.,=

_...:

_...

__.,.,.,.....

__

__,,__

_

, 895

One Day Workshop Program SchedulePlace Date Venue Fees

Chennai June 23 & 25, 2016 To be communicated soon Rs. 5,000 per participant

Hyderabad July 1 & 2, 2016 To be communicated soon Rs. 6,000 per participant

Mumbai July 8 & 9, 2016 To be communicated soon Rs. 7,000 per participant

Pune July 15 & 16, 2016 To be communicated soon Rs. 6,000 per participant

Bengaluru July 22 & 23, 2016 To be communicated soon Rs. 6,000 per participant

Payments to be made by Cheque / DD /PO in favour of “SN Corporate Management Consultants P Ltd.,” G2, Sun Rays Apartments, 25, Jayalakshmipuram 2nd Street, Nungambakkam, Chennai – 600 034. For NEFT: Bank : IDBI ; A/c No: 0045102000032984 ; IFSC: IBKL0000045. Please advise us after payment. For Group / Corporate / B’ School / Faculty / Student participation, 20% concession on the fees payable is available. Fees include hands on training, material, refreshments and lunch. No ST is payable.www.fintegratedsolutions.com

3

Registration

You may know more about the Agenda and Focus Areas that will be dealt with in the Workshop, by reading the rest of the slides.

In case, you need further details, you may let us know, by providing your contact details in the following link or by e-mailing / calling us.

o http://fintegratedsolutions.com/contact.html financialmodelling@fintegratedsolutions.com /

nalayiram@hotmail.com +91 81 22 62 4000 / +91 97 90 85 3099 For registrations, please provide details of participants. Registration

will be confirmed after receipt of payment.

4

Excel Based Financial Modeling

-BUILDING FINANCIAL MODELS AND PREPARATION OF PROJECTED FINANCIAL STATEMENTS

5

Focus Areas

F01 : Concepts and Terminologies relating to Financial Modeling and ‘Projected Financial Statements’F02 : Important Concepts relating to application of Advanced MS Excel and VBA Programing in MS ExcelF03 : Introduction to Case Study on Financial ModelingF04 : Preparation of Financial Models and Projected Financial Statements in MS Excel based on the Case Study – Stage I Creating a Master / Control Sheet Building in Parameters, Thresholds and Constraints Identification of Component Models / Schedules Determination and Measurement of other elements of Projected

Financial Statementscontd.,

6

Contd., Focus Areas

F04 : Preparation of Financial Models and Projected Financial Statements in MS Excel based on the Case Study – Stage II Preparation of Revenue, Expense and Profit Models and Projected

Income Statement Preparation of Asset and Liability Models and Projected Balance Sheet Preparation of Funds Flow Models and Projected Funds Flow Statement Preparation of Cash Flow Models and Projected Cash Flow Statement BEP Models and Analysis Du Pont Modeling and Key Ratios Analysis Sensitivity / Scenario AnalysisF05 : Developing a simple menu based VBA - Excel ApplicationF06 : Preparation of Project Report

7

F01 : Concepts and terminologies -Projected Financial Statements and Financial Modeling

Meaning of Financial Statements –General Purpose Financial Statements and Projected Financial Statements and Distinction between them

Objectives / Need for Preparation of Projected Financial Statements Inter - Relationship between different financial statements Key Elements of different financial statements Identification of Key Factors / Components Assumptions Meaning of Financial Models and Steps involved in building

Financial Models Characteristics of typical Financial Models

8

F02 : Advanced MS Excel and VBA for Excel Concepts

Fixed vs. Relative referencing

Naming Ranges and use of ranges in Calculation

Logical Formulas and Functions

Excel Macros – Creating and Editing Macros

Designing Excel Sheet with Controls

VBA Macros / User Defined Functions

Other MS Excel / VBA Concepts

9

F03 : Case Study

Discussion of a case study of setting up an Educational Institution Market Research

Key Activities

Key Assumptions

Cost of Project

Pre-Operation Stage

Working Capital Determinants

10

F04 : Preparation of Financial Models – Designing Control Sheet

Checklist

Growth Rates

Control Values –including Statistical Validation

Check for extraordinary values

Check for illogical values

Formatting and Error Messages

11

F04 : Preparation of Financial Models –parameters, thresholds and constraints

Debtors Turn Over

Creditors Turn Over

Inventories Turn Over

Cash Turn Over

Other important parameters, thresholds and constraints

12

F04 : Building up Component Models

Cost of Project Models Capital Structure Models Pre-Operative Expenses Model Cap Ex / Depreciation Model Working Capital Structure Model Revenue Model Expenses Model Profit Model Loan Amortization Model Income Tax Model

13

F04 : Contd., Component Models

Accounts Receivable Model

Accounts Payable Model

Cash and Cash Equivalents Model

Inventories Model

Other Current Liabilities and Current Assets Model

14

F04 : Preparation of Projected Financial Statements – Income Statement

Link with Component Models-Revenue Model-Expenses Model-Profit Model-Depreciation Model-Loan Amortization Model-Cap Ex / Depreciation Model-Income Tax Model

Entering Formulas and Functions

15

F04 : Preparation of Projected Financial Statements – Projected Balance Sheet

Statement of Changes in Equity

Link to Previous Balance Sheet

Link to Income Statement

Links to Other Models / Statements

Entering Formulas and Functions

16

F04 : Preparation of Projected Financial Statements - Funds Flow Model

Link to Balance Sheet Links to Other Models / Statements Determination of and Classification of Inflows and Outflows of Funds

Funds from Operating Activities

Funds from Investing Activities

Funds from Financing Activities

Formula to Identify Increase or Decrease in Working Capital Validation of Closing Working Capital Entering Formulas and Functions

17

F04 : Preparation of Projected Financial Statements - Cash Flow Model

Link to Balance Sheet Link to Other Models / Statements Determination of and Classification of Inflows and Outflows of Cash

and Cash Equivalents Cash and Cash Equivalents from Operating Activities Cash and Cash Equivalents from Investing Activities Cash and Cash Equivalents from Financing Activities

Formula to Identify Increase or Decrease in Cash and Cash Equivalents

Validation of Closing Cash and Cash Equivalents Entering Formulas and Functions

18

F04 : Preparation of Projected Financial Statements – BEP Analysis

Identification of Variable Costs and Fixed Costs

Calculation of P/V Ratio

BEP Analysis and Margin of Safety

Entering Formulas and Functions

19

F04 : Preparation of Projected Financial Statements – Du Pont Modeling & Key Ratios Analysis Activity Ratios Operating Ratios Profitability Ratios Liquidity Ratios DSCR Ratios Solvency Ratios Cash Flow Ratios Return on Assets Ratios Valuation Ratios Entering Formulas and Functions

20

F04 : Preparation of Projected Financial Statements –Sensitivity Analysis

Identifying Variables

Building Scenarios

Outcome Analysis

Entering Formulas and Functions

21

F05 : Preparation of Projected Financial Statements – Developing a Simple VBA Menu Based Application

Inserting Control Buttons

Naming and Setting the properties for Control Buttons

Opening and Editing the VBA Macro Code

Writing VBA Code

22

F06 : Preparation of Projected Financial Statements – Project Report

Executive Summary

Salient Features about the business

Analysis of Projected Financial Performance and Project Viability

Charts and Graphs

Comments and Conclusions

23

Quiz and Short Tests

Concepts Projected Financial Statements Schedules MS Excel VBA for Excel Financial Analysis Report Preparation

24

Feedback Session

Two Way Feedback

25