The focus of this three-day program is to enable candidates to build a robust and integrated financial model that captures real life business complexities. The candidates will gain insight into the application of financial tools to specific business problems and also on applying excel models to improve quality of corporate analysis and facilitate informed decision making. The program will cover key excel functions, practical modelling aspects, while adding layers of sophistication and also professional presentation of information in the form of charts and dashboards.The ultimate objective of the program is to provide candidates a practitioner’s view of financial modelling by combining practical Excel exercises with corporate finance and valuation.
Candidates are methodically guided through excel spreadsheets and will be inducted into using all the requisite excel tools and key functions that are the building blocks for preparing a financial model. Through multiple exercises they will perform ratio analysis, forecast and build a dynamic financial model from scratch and add real life complexities through sensitivity analysis.
Module 1: Key Excel Functions And Data Management Tools
Starts with a brief session on excel skills and move on to using Excel functionality in the context of Financial analysis and perform the key excel functions and advanced data analytics; building complex formulas; handling, analyzing and interpreting extensive data.
Key Excel Functions and Pivot tables.
- Different ways of summing using SUM SUMIF, SUMPRODUCT, COUNT, COUNTA, COUNTIF, 3D SUM and other basic math functions, Date Functions and Text functions.
- Building nested formulas using IF, AND, OR; IFERROR & Concatenation functions.
- Use of HLOOKUP, VLOOKUP, CHOOSE, OFFSET, INDEX, MATCH, INDIRECT functions.
- Creating drop down menus with Data Validation, product, arrays and table functions explained with examples and Goal Seek functions.
- Sort & subtotals, Use of Filters to handle extensive data and use conditional formatting for communicating key insights and interpret complex data.
- Review the pivot table functionality and how it can be applied to different data sets.
- Apply the pivot Tables to the excel dashboard.
Module 2: Corporate Financial Analysis, Forecasting and building a Basic Model
Session starts with understanding the link between financial statements, performing ratio analysis and then using excel to make forecasts, and to build own interactive financial model from scratch.
Corporate Financial Analysis
- Understand the conceptual link between the three financial statements and linking them in excel.
- Perform vertical, trend and horizontal analysis and extract 60 different variables from financial statements to measure the health of firms.
- Calculate key financial ratios like, profitability ratios, activity ratios, solvency ratios, Liquidity ratios and valuation ratios and perform DuPont analysis.
- Build a complete dashboard of ratios with charts and sparklines.
Using Excel to Forecast and Build a Basic Financial Model from Scratch
- Identify revenue and cost drivers, and scope for operating leverage.
- Forecast revenues, costs and profitability margins using different methods.
- Understand input variable rules, financial modelling attributes and best practices.
- Step by step guide in building models from scratch with an end result containing following statements.
- Income statement, balance sheet and cash flow statement.
- Working capital schedule, depreciation and capital expenditure schedule.
- Shareholders’ equity, Debt and interest schedule.
- Building checks and balances in the model.
Module 3: Sensitivity and Scenario Analysis, Investment Appraisal and Valuation
In this session candidates add complexities to financial model that capture real life scenarios; run scenario and sensitivity analysis and integrate the valuation concepts to effectively value projects/companies. The day will end with preparation of various charts and dashboards to meet specific requirements.
Scenario Planning & Sensitivity Analysis
- Calculate target debt level based on cash flow and interest cover.
- Build scenarios for various conditions/outcomes and project different estimates and variables into a business model.
- Project feasibility and financing.
- Build a payback model and a discounted payback model; calculate break even performance, identify unit sales and sales growth.
- Practical exercises to learn the contextual use of functions such as Present Value (PV), Net Present Value (NPV), Internal Rate of Return, XIRR.
- Build a complete investment appraisal dashboard.
Professional Presentation of Information – Charts and Dashboards.
- Understand various valuation techniques and use multiple valuation methodologies like Discounted cash flow Analysis, comparable approach to value a firm and its equity.
- Principles and best practices of well-made charts and how to prepare right charts for right purpose - simple to complex charts and.
- Present information in the form of dashboards - templates for various purposes.
Wrap-Up Session with Recap of Key Concepts