Financial Modeling Expert by using MS. Excel
11 Sept. to 2 Oct.
06:00 PM to 10:00 PM
تعلم التحليل المالى ودراسات الجدوي وكن خبير للنمذجة المالية باستخدام الاكسيل
Intensive Course in … Financial Modeling by using MS-Excel
Objectives Developing a thorough mastery of excel and VBA software(s) for financial modelling purposes. Learning the needed tools to build models that are less time and effort consuming. Learning the best practice in modelling forecasted financial statements (Balance Sheet, Income Statement and Cash Flow Statement). Learning and Work with over 1 million rows of data in seconds with Power Pivot Data Mashup and Data Exploration. Learning the way how to get from the basics of VBA to building a loan amortization table using a VBA macro.
PREREQUISITES Some knowledge of basic word processing, computing, and spreadsheets. Familiarity with the Microsoft Office work environment. An understanding of the Internet and web-page functionality.
WHO SHOULD ATTEND? All Excel Users particularly: Accountants. Senior Accountants. Financial Analyst. Chief Executive Officers. Chief Financial Officers. Portfolio managers. Corporate accountants. Credit analysts. Private equity managers. Actuaries. Venture capitalists. Corporate finance analysts. Risk managers. Board members. Investment bankers. Regulators. Financial government officials. Strategic planners. Trustees. Compliance officers. Management consultants. Bank lending officers. Internal auditors. Management consultants. Corporate Finance lawyers.
Course outline Day 1: - Excel Best Practice (Tools & Shortcuts). a. Using the F-Keys and combinations of F-Keys and ALT. b. Keyboard shortcuts for efficient model building. c. Cross-referencing. d. Named ranges as a methodology for modeling. e. Using Data-Validation to improve sheet structure. f. Tables – Pivot tables – Pivot Chart. g. Insert shapes and smart graphics.
Day 2: - Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): - a. Case (Dealing with Data Base): Tables – Pivot table – Sum ifs & Countifs. b. Case: Replacing email domain using text function. c. Case study: Using Logical functions (IF, And, ETC.). d. Using Lookup & Reference functions (VLOOKUP, Offset, Match, and Index). e. Date and Time Functions.
Day 3: - Advanced Charting technique: - a. Building the Primary & Secondary Axis in a Chart. b. Double Line Chart. c. Grouped Charting. d. Flexible charts - dynamic chart based for changing ranges. e. Tracking the progress of the construction of the commercial property, Radar Charts. f. Pyramid chart for comparison (Stacked bar charts). g. Water Fall Charts to identify the cost breakdown Structure of a project.
Day 4: - Advanced Tools: - a. What-If analysis: (Worked Example included): - i. Scenario manager ii. Goal Seek iii. Data table. b. Create a solid excel template to include dropdown menus and specific selection by Protecting the worksheet and workbook. c. Case study: Work between multiple sheets. d. Text to the column to split text or merge text. e. Conditional formatting. f. Importing and exporting data from external sources.
Day 5: - A: Advanced Functions: - Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): - a. Case (Array Formulas): Portfolio analysis Sum Product. b. Case (Array Formulas): Calculating geometric return. c. Case (Lookup array more than 2 conditions): Using Dropdown menus – combining Index & Match. d. Combining Logical Functions with another Logical – Lookup – Reference – IS Functions. e. Case: IRR & NPV Flexible model generation.
B: Formula Auditing: - a. Excel Audit complicated functions. b. Evaluate Formulas. c. Breaking circular reference error (Loop) from excel options. d. Dealing with errors.
Day 6: - Macros: - Introduction to Macros & Advanced Application Building in Excel. a. Designing the application to gather user data: - i. Record macro & Understanding the created code. ii. Write your First Macro iii. Errors in VBA iv. Step into VBA v. Writing VBA codes vi. Random Number Generator: Use of loop, Excel functions vii. Message box. viii. Input box. b. Advanced Application: - i. CAPM Model: using Form Controls (Scroll bar) to calculate the required rate of return. ii. Flexible Chart using Form controls (checkbox). iii. Case Study: Interest during construction (IDC) & Breaking circular loops using macros. iv. Advanced Financial Applications using macro (Monte Carlo simulation).
Day 7: - FMCG Case study: Understanding & gathering information 1. Understanding accounting basics double entry and creating (P&L and B/S). 2. Creating a consistent financial model template. 3. Historical financial statement modeling (P&L and B/S): - a) Data Gathering from Annual Reports & Management Discussion. b) Creating the historical statements and linking derived numbers 4. Calculating growth drivers: - a) Gathering Data for growth drivers from research reports and other sources (Revenue - Costing - expenses) b) Calculating P&L growth numbers/ CAGR. c) Calculating P&L ratios. d) Calculating B/S ratios.
Day 8: - FMCG Case study: 1. Building assumptions and projections: - a) Building assumptions for P&L and B/S. b) Linking assumptions for P&L. c) Linking assumptions for B/S. 2. Building schedules: - a) Fixed Assets and depreciation schedule. b) Debt & Interest schedule. 3. Understand & Build forecasted Profit and loss statement. 4. Understand & Build forecasted balance sheet. 5. Understand & Build Cash flow statement. 6. Building interest on excess cash and cash revolver. a) Using Excel for Iteration - Goal Seek & circular referencing. Day 9: - Financial analysis ratios & Valuation: - 1. Understand & Build liquidity and profitability ratios for analysis. 2. Updating integrated model for valuation analysis: - a) Understanding and building trading comparatives. b) Understanding and building transaction comparatives. c) Understanding and building DCF d) Building free cash flow to firm & equity e) Understanding and estimating beta f) Understanding & estimating WACC g) Using data-validation to improve sheet structure h) Building Football field chart to decide which share price (company’s value) to consider.
Day 10: - Sensitivity analysis & Advanced Models: - 1. Scenario & Sensitivity Analysis in Excel 2. Case study: Mergers & Acquisitions (M&A) Modeling 3. Case study: Leveraged Buyout LBO Modeling 4. Theoretical: Corporate & Business Strategy 5. Dashboards & Data Visualization