Home
All Courses
Services
In-house
E-learning
Coaching
Consultancy
About us
Who We Are
Experts Team
Board of Directors
Partner Associations
Clients
Franchise
Media news
Corporate News
CSR
Interviews
Blog
Jobs
Downloads

Training Calendar 2019

Training Calendar 2020

Training Prospectus

Training Brochure

Training Catalogue

Contact us
Wishlist
Shopping cart
Sign in
EN | AR

Shopping cart

CLOSE

Total:

AED 18.300

Home All Courses FMM Financial Modelling Master
4.9

FMM Financial Modelling Master

Advanced Diploma in Financial Modelling Using Excel and VBA

3 alternative locations available

Course Overview

The ability to create and understand financial models is one of the most valued skills in business and finance today. Microsoft Excel with Visual Basic for Application (VBA) macros programming has been the dominant vehicle used by finance and corporate professionals in the preparation and utilization of the full range of financial models and other applications. However, as a result of the so called ‘95/5’ rule it can be concluded that 95% of Excel users probably only use a mere 5% of the program’s power. Most users know that they could be getting more out of Excel especially by using VBA which would result in them being able to build more flexible, dynamic and professional models. Unfortunately, this aspect of Excel and VBA often appears to be complex and intimidating.

This intensive 5 day workshop starts with basics and progresses in a logical step by step manner to the more complex and rewarding tools needed to build more robust models that save time, reduce unnecessary human errors and customize applications that would be impossible to achieve with suboptimal models. Every section is followed by a direct application related to the financial markets and financial issues.

The course will emphasize some important financial concepts that will assist in building vigorous models. It is designed to give you the information you need without making you wade through cumbersome explanations and endless technical background. Delegates will need some basic knowledge of Excel but not of professional modelling or programming.

01

05

Run This Course

Enjoy this certified

program at your premise of choice, with lower costs, and schedule that suits you best.

FULLY CUSTOMIZED

ACCORDING TO YOUR NEEDS!

5 Key Takeaways

1

Learners will be able to Develop a thorough mastery of excel and VBA software(s) for financial modelling purposes.

Learners will be able to apply the needed tools to build models that are less time and effort consuming.

2

3

Learners will be able to apply the best practices in modelling forecasted financial statements (Balance Sheet, Income Statement and Cash Flow Statement.

Learners will implement over 1 million rows of data in seconds with Power Pivot Data Mashup and Data Exploration.

4

5

Learners will construct the way how to get from the basics of VBA to building a loan amortization table using a VBA macro.

02

05

See What’s Inside Our 2019 Catalog

Course Outline

DAY ONE

Design principles for good model building

  • Principle of Occam’s razor

  • Interrelationships within a model

  • Logical arrangement of the parts

  • Setting toggles

  • Model design and structure

  • Attributes of good Excel models

Exploring Excel functions

  • Financial

  • Date and time

  • Statistical

  • Lookup & reference

  • Database

  • Text

  • Logical

  • Information

Helpful starting hints

  • Formula errors vs user errors

  • Warning signs

  • Using the F-Keys and combinations of F-Keys.

  • Name ranges using Create Names

  • Making range names more informative

  • Editing name ranges

  • Data validation

  • The Analysis ToolPak

  • Using formula auditing

The 40 functions you will work with as a financial modeler

  • Basic, intermediate and advanced functions

  • Arguments in advanced functions

  • Dealing with errors

  • Boolean logic

  • Change nested IF’s to something powerful

  • Array formulas

  • Using array formulas with the offset function

  • Calculating geometric return using arrays

  • Annualizing geometric returns

  • The power and manipulation of looking up data

  • Powering the offset with the match function and drop down menus

  • Combining the Index function with the match function

  • Strengthening the If function with (Min, Max, Choose, Offset, And, Or functions)

  • Conditional addition and counting functions

  • Manipulating date functions in complex examples

  • When to use DAYS360 in a model

  • Solving problems with DAYS360

  • Working with parts of numbers

  • Working with numbers in a direction

  • Using the IS-suit (isnumber, istext, isblank, iserror, iserr, islogical, isna, isnontext, isref) with logical formulas

  • Going through a spreadsheet that contains worked examples of all 40 functions

Exercise 1: building a dynamic performance database from scratch using arrays, the offset function, the match function, the index function and drop down menus

DAY TWO

Financial functions

  • Understanding net present value

  • Understanding internal rate of return

  • Going through a worked example of a project cash flow

  • Problems with the IRR calculation

  • Multiple IRR

  • Shooting yourself in the foot with incorrect assumptions

  • NPV

  • XNPV

  • IRR

  • MIRR

  • XIRR

Exercise 2: Modelling the IRR and MIRR of a construction project

Modeling forecasted financial statements

  • Forecasting guidelines

  • Modelling the connection between the income statement and the Balance Sheet

  • Assumptions margin

  • Two ways to balance the balance sheet

  • Using plugs to balance the balance sheet

  • Surplus funds and the necessity to finance

  • Modelling the provision for taxes

  • Static vs dynamic analysis

  • Effect of surplus income from surplus funds

  • Flows in the Cash Flow Statement (Operations, Investment, Financing)

  • Structuring a cash sweep

  • Modelling payment of successive debt tranches

  • Calculation of the post-sweep debt numbers

  • Cash flow variation for cash sweep

  • Reality checks

  • Adding an error trapping formula

  • Using conditional formatting

  • Finding cells that have conditional formats

  • Structuring the input sheet vs output sheets

  • Modelling common size statements

  • Smoothing techniques in forecasting

  • Simple and multiple regression analysis

  • Sensitivity analysis in forecasting

Modeling Ratio Analysis and Key Performance Indicators

  • Efficiency ratios

  • Profitability ratios

  • Leverage ratios

  • Coverage ratios

Exercise 3: Forecasting the financial statements of a company with full ratio analysis for five years in future.

DAY THREE

EDUCATIONAL PARTNER

ANSI - American National Standards Institute

Marking 100 years of experience, ANSI is the official U.S. representative to the International Organization for Standardization (ISO) and, via the U.S. National Committee, the International Electrotechnical Commission (IEC), and is a U.S. representative to the International Accreditation Forum (IAF). ANSI accreditation is nationally and internationally recognized as a mark of quality and assures that employers can have confidence that the certificate holder has completed the prescribed course of study. ANSI’s accreditation process itself follows ISO/IEC 17011, the International Standard that defines quality third-party accreditation practices. ANSI’s Certificate Accreditation Program (ANSI-CAP) accredits assessment–based education and training programs against the American National Standard ASTM E2659. The standard establishes guidelines for quality certificate program development and administration, and forms the foundation for a recognition system that enables consumers, employers, government agencies, and others to distinguish between qualified workers and those with less-than-quality credentials.

03

05

Introducing LEORONʼs mobile app!

LeoronGo

Your Professional Development

Institute in the palm of your hand!

WEBADVISOR • MOODLE • COURSE SCHEDULE •

COURSE CATALOG • STUDENT SERVICES • AND MORE!

NOW AVAILABLE

Who Should Attend?

This highly practical and interactive course has been specifically designed for:

  • Financial Analysts

  • Chief Executive Officers

  • Chief Financial Officers

  • Portfolio managers

  • Corporate accountants

  • Credit analysts

  • Private equity managers

  • Actuarist

  • Venture capitalists

  • Corporate finance analysts

  • Risk managers

  • Board members

  • Investment bankers

  • Regulators  

  • Financial government officials

  • Strategic planners

  • Trustees

  • Compliance officers

  • Management consultants

  • Bank lending officers

  • Internal auditors

  • Management consultants

  • Corporate Finance lawyers

04

05

05

05

Reviews

Submit Review

Overall rating *

Add a photo

REVIEW SUBMITED-THANK YOU

We are processing your review. This may take several days, so we appreciate your patience. We will notify you when this is complete.

RELATED COURSES

CBA Certified Budget Analyst

Advanced Tools in Budgeting, Forecasting & Reporting

4.8

Dec 08 - 12, 2019

Dubai, UAE

35 NASBA CPE credits

5 days

CBA Certified Budget Analyst

Advanced Tools in Budgeting, Forecasting & Reporting

The ‪#‎CBA is the course when it comes to ‪#‎budget planing and accounting. During a 5 day course, our trainer will guide you through the process of attaining full understanding on the concept of ‪#‎PowerPivotsthat handle 200 million rows of data and Monte Carlo simulation using Crystal Ball in Excel.

CCM Certificate in Cash Management

4.9

Dec 08 - 12, 2019

Dubai, UAE

35 NASBA CPE credits

5 days

Upon completion of this course, the participants will be able to understand the impact of accounting transactions on profits and cash resources and what is working capital and its impact on funding requirements. Each participant will be able to analyse working capital efficiency and understand and quantify the impact of inventory on profits and cash flows.

CIFRSS Certified IFRS Specialist

4.9

Dec 08 - 12, 2019

Jeddah, KSA

35 NASBA CPE credits

5 days

Are you up to date with the latest standard in ‪IFRS? ‪Accounting professionals can gain full knowledge on the development of IFRS and earn the CIFRSS™ designation by completing the Certified International Financial Reporting Standards Specialist.course. 18 months membership to the ‪IABFM professional body included!

CVAT Certificate in Value Added Tax Implementation

4.9

Dec 08 - 09, 2019

Dubai, UAE

14 NASBA CPE credits

2 days

This course introduces the basics of Value Added Tax. As such, no great understanding of VAT is required as the course will build from the absolute basics through to some of the more complex issues such as exemption. The workshop does not focus on VAT as applied any one country but takes a global view.

FREQUENTLY ASKED QUESTIONS

Most of our public courses are delivered in English language. You need to be proficient in English to be able to fully participate in the workshop and network with other delegates. For in-house courses we have the capability to train in Arabic, Dutch, German and Portuguese.

LEORON Institute partners with 20+ international bodies and associations.
We also award continuing professional development credits (CPE/PDUs) for:
1. NASBA (National Association of State Boards of Accountancy)
2. Project Management Institute PDUs
3. CISI credits
4. GARP credits
5. HRCI recertification credits
6. SHRM recertification credits

The deadline to register for a public course is 14 days before the course starts. Kindly note that occasionally we do accept late registrations as well, but this needs to be confirmed with the project manager of the training program or with our registration desk that can be reached at +971 4 447 5711 or [email protected].

The course fee covers a premium training experience in a 5-star hotel, learning materials, lunches & refreshments, and for some courses, the certification fee and membership with the accrediting bodies.

Yes, we can provide discounts for group bookings. If you would like to discuss a discount on a corporate level, we will be happy to talk to you.

Radiating Social