Financial Modeling Expert by Using Excel

Course details

- Excel Best Practice (Tools & Shortcuts). ✓ Using the F-Keys and combinations of F-Keys and ALT. ✓ Keyboard shortcuts for efficient model building. ✓ Cross-referencing. ✓ Named ranges as a methodology for modeling. ✓ Using Data-Validation to improve sheet structure. ✓ Tables – Pivot tables – Pivot Chart. ✓ Insert shapes and smart graphics. - Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): - ✓ Case (Dealing with Data Base): Tables – Pivot table – Sum ifs & Countifs. ✓ Case: Replacing email domain using text function. ✓ Case study: Using Logical functions (IF, And, ETC.). ✓ Using Lookup & Reference functions (VLOOKUP, Offset, Match, and Index). ✓ Date and Time Functions. - Advanced Charting technique: - ✓ Building the Primary & Secondary Axis in a Chart. ✓ Double Line Chart. ✓ Grouped Charting.
✓ Flexible charts - dynamic chart based for changing ranges. ✓ Tracking the progress of the construction of the commercial property, Radar Charts. ✓ Pyramid chart for comparison (Stacked bar charts). ✓ Waterfall Charts to identify the cost breakdown Structure of a project. - Advanced Tools: - ✓ What-If analysis: (Worked Example included): - ✓ Scenario manager ✓ Goal Seek ✓ Data table. ✓ Create a solid excel template to include dropdown menus and specific selection by Protecting the worksheet and workbook. ✓ Case study: Work between multiple sheets. ✓ Text to the column to split text or merge text. ✓ Conditional formatting. ✓ Importing and exporting data from external sources. - A: Advanced Functions: - Introduction and context in Advanced functions for modeling (small case studies for application of the formulas and the concept): - ✓ Case (Array Formulas): Portfolio analysis Sum Product. ✓ Case (Array Formulas): Calculating geometric return. Case (Lookup array more than 2 conditions): ✓ Using Dropdown menus – combining Index & Match. ✓ Combining Logical Functions with another Logical – Lookup – Reference – IS Functions. ✓ Case: IRR & NPV Flexible model generation. - B: Formula Auditing: - ✓ Excel Audit complicated functions. ✓ Evaluate Formulas. ✓ Breaking circular reference error (Loop) from excel options. ✓ Dealing with errors.
- Macros: - Introduction to Macros & Advanced Application Building in Excel. ✓ Designing the application to gather user data: - ✓ Record macro & Understanding the created code. ✓ Write your First Macro ✓ Errors in VBA ✓ Step into VBA ✓ Writing VBA codes ✓ Random Number Generator: Use of loop, Excel functions ✓ Message box. ✓ Input box. Advanced Application: - ✓ CAPM Model: using Form Controls (Scroll bar) to calculate the required rate of return. ✓ Flexible Chart using Form controls (checkbox). ✓ Case Study: Interest during construction (IDC) & Breaking circular loops using macros. ✓ Advanced Financial Applications using macro (Monte Carlo simulation). - FMCG Case study: Understanding & gathering information ✓ Understanding accounting basics double entry and creating (P&L and B/S). ✓ Creating a consistent financial model template. Historical financial statement modeling (P&L and B/S): - ✓ Data Gathering from Annual Reports & Management Discussion. ✓ Creating the historical statements and linking derived numbers Calculating growth drivers: - ✓ Gathering Data for growth drivers from research reports and other sources (Revenue - Costing - expenses) ✓ Calculating P&L growth numbers/ CAGR. ✓ Calculating P&L ratios. ✓ Calculating B/S ratios.
- FMCG Case study: Building assumptions and projections: - ✓ Building assumptions for P&L and B/S. ✓ Linking assumptions for P&L. ✓ Linking assumptions for B/S. Building schedules: - ✓ Fixed Assets and depreciation schedule. ✓ Debt & Interest schedule. ✓ Understand & Build forecasted Profit and loss statement. ✓ Understand & Build forecasted balance sheet. ✓ Understand & Build Cash flow statement. Building interest on excess cash and cash revolver. ✓ Using Excel for Iteration - Goal Seek & circular referencing. - Financial analysis ratios & Valuation: - ✓ Understand & Build liquidity and profitability ratios for analysis. Updating integrated model for valuation analysis: - ✓ Understanding and building trading comparatives. ✓ Understanding and building transaction comparatives. ✓ Understanding and building DCF ✓ Building free cash flow to firm & equity ✓ Understanding and estimating beta ✓ Understanding & estimating WACC ✓ Using data-validation to improve sheet structure ✓ Building Football field chart to decide which share price (company’s value) to consider. - Sensitivity analysis & Advanced Models: - ✓ Scenario & Sensitivity Analysis in Excel ✓ Case study: Mergers & Acquisitions (M&A) Modeling ✓ Case study: Leveraged Buyout LBO Modeling ✓ Theoretical: Corporate & Business Strategy ✓ Dashboards & Data Visualization.
  Updated on 15 July, 2021