Financial Modeling Expert by Using Excel هذه الدورة تدرس باللغة الإنجليزية

تفاصيل الدورة

Course Outline

Day 1: 

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.

Day 2: 

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.

Day 3: 

Advanced Charting technique: -

• Building the Primary & Secondary Axis in a Chart.
• Double Line Chart.

Financial Modeling by using MS-Excel

• 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).
• Water Fall Charts to identify the cost breakdown Structure of a project.

Day 4: 

Advanced Tools: 

• What-If analysis: (Worked Example included): -
• i. Scenario manager
  
• ii. Goal Seek
  
• iii. 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.

Day 5: 

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.
  

Formula Auditing: 

• Excel Audit complicated functions.
  
• Evaluate Formulas.
  
• Breaking circular reference error (Loop) from excel options.
  
• Dealing with errors.

Day 6: 

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).

Day 7: 

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): -
  

                    a) Data Gathering from Annual Reports & Management Discussion.
                    b) Creating the historical statements and linking derived numbers

• 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:

• 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.

• Building schedules: 

                    a) Fixed Assets and depreciation schedule.
                    b) 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.
  

                    a) Using Excel for Iteration - Goal Seek & circular referencing.

Day 9: 

 Financial analysis ratios & Valuation: -

• Understand & Build liquidity and profitability ratios for analysis.
  
• 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: 

• 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
  

تحديث بتاريخ 15 July, 2021