Financial Modeling
INDIAN INSTITUTE OF QUANTITATIVE FINANCE
CERTIFICATE PROGRAM IN FINANCIAL MODELLING IN EXCEL
CURRICULUM
Advanced Excel (2 Days)
(A) Excel Features and Techniques
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. Excel Shortcuts Excel formulae Usage of names to make formulas easier Data Sorting Excel formatting Data Formats Lines and Borders Colours and Patterns Excel functions Excel charts Dynamic Graphs Conditional Formatting Linking worksheets Data Validation Controls – Macros, Combo Boxes and buttons User Forms and controls Data Tables Splitting screens to facilitate working between several worksheets Excel protection option
(B) Advanced Excel Features and Techniques
1. Use of Pivot Tables 2. Use of HLOOKUP, VLOOKUP, COUNTIF, SUMIF, SUMPRODUCT 3. Excel Database Functions – DCOUNT, DSUM, DPODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET 4. Scenario Analysis in Excel 5. Use of Goal Seek 6. Recording Excel Macro 7. Creating Macro and Running from a Button
(C) Overview
1. Introduction to Modelling 2. Excel as tool for Modelling
INDIAN INSTITUTE OF QUANTITATIVE FINANCE
(D) Designing Spreadsheet models
1. 2. 3. 4. 5. 6. 7. 8. Set aims and Objectives Examine User needs and User interface Set out Key variables and rules Breakdown calculations into manageable groups Produce individual modules Testing and auditing Protection as an application Documentation
Financial Modelling in Excel (2 Days)
(A) Corporate Financial Statements
1. 2. 3. 4. 5. 6. 7. 8. Profit and Loss Account Balance Sheet Financial Ratios Cash flow statement Identify the information contained in the three main financial statements Basic concepts - NPV, IRR, Amortization, Debt repayment, etc. Ratios used in long and short term risk return analysis - DSCR etc. Making and analyzing projected financial statements in case of infrastructure companies a. Sensitivity Analysis b. Long term projections and Scenario Analysis c. Case Study of an
CERTIFICATE PROGRAM IN FINANCIAL MODELLING IN EXCEL
CURRICULUM
Advanced Excel (2 Days)
(A) Excel Features and Techniques
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. Excel Shortcuts Excel formulae Usage of names to make formulas easier Data Sorting Excel formatting Data Formats Lines and Borders Colours and Patterns Excel functions Excel charts Dynamic Graphs Conditional Formatting Linking worksheets Data Validation Controls – Macros, Combo Boxes and buttons User Forms and controls Data Tables Splitting screens to facilitate working between several worksheets Excel protection option
(B) Advanced Excel Features and Techniques
1. Use of Pivot Tables 2. Use of HLOOKUP, VLOOKUP, COUNTIF, SUMIF, SUMPRODUCT 3. Excel Database Functions – DCOUNT, DSUM, DPODUCT, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, DVARP, DGET 4. Scenario Analysis in Excel 5. Use of Goal Seek 6. Recording Excel Macro 7. Creating Macro and Running from a Button
(C) Overview
1. Introduction to Modelling 2. Excel as tool for Modelling
INDIAN INSTITUTE OF QUANTITATIVE FINANCE
(D) Designing Spreadsheet models
1. 2. 3. 4. 5. 6. 7. 8. Set aims and Objectives Examine User needs and User interface Set out Key variables and rules Breakdown calculations into manageable groups Produce individual modules Testing and auditing Protection as an application Documentation
Financial Modelling in Excel (2 Days)
(A) Corporate Financial Statements
1. 2. 3. 4. 5. 6. 7. 8. Profit and Loss Account Balance Sheet Financial Ratios Cash flow statement Identify the information contained in the three main financial statements Basic concepts - NPV, IRR, Amortization, Debt repayment, etc. Ratios used in long and short term risk return analysis - DSCR etc. Making and analyzing projected financial statements in case of infrastructure companies a. Sensitivity Analysis b. Long term projections and Scenario Analysis c. Case Study of an