السعر: 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 ابتداءً من الآن.
    • Accounting Diploma Brentwood Open Learning College
      74 USD
      820 USD
      مدة الدورة التدريبية: 1 To 260 Hours
    • Accounting and Tax St. Paul's College
      13 USD
      325 USD
      مدة الدورة التدريبية: Upto 2 Hours
    See all courses

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

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

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

    أو