Use Google Chrome for best Experience.

MS Excel- Advanced Excel & Statistical Modelling

Online
Category : Ms Excel
Author Karan Aggarwal

₹ 24000 ₹31000
International ₹ 24000 ₹ 31000

add_shopping_cart Add to Cart

Ms Excel- basic and Advance Excel :

Content : 

Session 1 – Warm up & Basic Excel (Data Types & Components of Formulas), Lookup Functions including Vlookup, Hlookup, Index & Match, Logical Statements using IF, AND, OR.

Session 2 – Pivot Tables, What if Analysis (Goal Seek, Scenario Analysis & Data Tables) & Solver, Date Functions, Text Functions, Creating Dashboard.

Statistical Modelling Using Excel :

Content :

  1.  Monte Carlo Simulations
  2.  Discrete Probability Distributions
  3.  Continuous Probability Distributions
  4.  DISTRIBUTIONS PRACTICAL CLASS
  5.  GPD & Log Normal distribution
  6.  Parameter Estimation
  7.  Copulas
  8.  Revision of Previous classes
  9.  Goodness of Fit Test

Financial Modelling :

Content :  

FAST™ Modelling Standard: www.fast-standard.org - a set of rules on the structure and detailed design of spreadsheet based models. (Flexible, Structured, Appropriate and Transparent).

General Flow:

  1. PART-1: Mini-caselets will be used to explain the concept of Flags, Circularity Switch, scenario building formulas and logic building Excel functions
  2. PART-2: One case study will be taken up to explain the general linkages between Assumptions, Working Schedules, Income Statement, Balance Sheet and Cash Flow Statement. This will ensure that the model is correctly integrated.A full blown Financial Model (Greenfield Hydro Power Project) will be taken up – refer Annexure

Specific areas which require attention and will be covered as a part of the model:

  1. Assumptions build-up (Project Start Date, Gestation Period, Project Commissioning Date etc.)
  2. Accommodating project delays
  3. Upfront capex in a phased manner
  4. Building automated timeline (FY)
  5. Timing and Event activation Flags (True/False)
  6. Operation Start Flag
  7. Operation Continued Flag
  8. Operational Year sequence counter
  9. Loan Repayment Flag
  10. Revenue Buildup (PPA / Merchant Sale) with YoY escalation in prices
  11. Capex Schedule (The journey from Capex > CWIP > PPE or Gross Fixed Assets)
  12. Use of BASE Analysis (Beginning, Addition, Subtraction, Ending)
  13. Amortization/Depreciation schedule
  14. Building depreciation calculations that stops after the book balance is fully depreciated
  15. Interest during Construction (IDC) – to be capitalized
  16. Hard Capex vs. Soft Capex (IDC)
  17. Dividing Interest accrued between IDC and P/L in the year of start of operation
  18. Waterfall Structure of funding
  19. Equity drawdown, followed by Debt and Govt Grant
  20. Incorporating Revolver Balance & Minimum Cash Balances
  21. Debt schedules & repayment
  22. Working Capital schedule & Linkages
  23. Building iterative calculation for Interest
  24. Incorporating circularity switch to build a failsafe mechanism in case iteration leads to irreversible errors

Others: IRR, NPV, Ratios, What If Analysis, Relevant Shortcuts.


Previews (2)

  • Skill level

    Intermediate

  • Language

    English

  • Deliverables

    DVD,Excel Book,Statistical Modelling Book, Unfilled & Filled Excel Templates.

  • System Requirement

    4GB Ram; 32/64 Bit processor, Windows 7,8 and 10. Minimum 1 GB graphics card

  • Does Not Support

    Apple products(Mac and Mac book).

  • Max. Views

    2 per video

Request Callback