السعر: 820 USD
1,025 USD
تدريب إفتراضي أونلاين. المحاضرات قد تكون فردية أو ضمن مجموعة.
  • مدة الدورة التدريبية: 40 Hours
  • مواعيد الدورة: Full Time, Weekday evening classes

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

    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

    المتطلبات

    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.

    دورات يمكنك الالتحاق بها على الفور... خذ دورة عبر الإنترنت على Financial Modeling ابتداءً من الآن. See all courses

    هل هذه الدورة التدريبية الاختيار المناسب لك؟

    قيِم هذه الصفحة

    لم تجد ما كنت تبحث عنه؟

    أو