Exam Preparation
FMMFinancial Modelling MasterAdvanced Diploma in Financial Modelling Using Excel and VBA
Rating:
English
Advanced
FACE 2 FACE
ON SITE TRAINING
LIVE VIRTUAL
TRAINING
COACHING
& MENTORING
SELF-PACED
TRAINING
Select Date
Download Brochure
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 hat 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.
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 hat 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.
Key Takeaways
1
Learners will be able to Develop a thorough mastery of excel and VBA software(s) for financial modelling purposes.
2
Learners will be able to apply the needed tools to build models that are less time and effort consuming.
3
Learners will be able to apply the best practices in modelling forecasted financial statements (Balance Sheet, Income Statement and Cash Flow Statement.
4
Learners will implement over 1 million rows of data in seconds with Power Pivot Data Mashup and Data Exploration.
5
Learners will construct the way how to get from the basics of VBA to building a loan amortization table using a VBA macro.
ANSI National Accreditation Board (ANAB)
The ANSI National Accreditation Board (ANAB) is the largest multi-disciplinary accreditation body in the western hemisphere, with more than 2,500 organizations accredited in approximately 80 countries. ANAB provides accreditation and training and serves as architects for the conformity assessment structure of industry-specific programs. The ANSI National Accreditation Board (ANAB) is a wholly owned subsidiary of the American National Standards Institute (ANSI), a non-profit organization.
Course Outline
Day 1
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 tructure
→ Attributes of good Excel models
→ Interrelationships within a model
→ Logical arrangement of the parts
→ Setting toggles
→ Model design and tructure
→ Attributes of good Excel models
Exploring Excel functions
→ Financial
→ Date and time
→ Statistical
→ Lookup & reference
→ Database
→ Text
→ Logical
→ Information
→ 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
→ 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
→ 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 2
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
→ 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
→ 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.
→ 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 3
Optimization Solutions with Excel Solver
→ Installing the solver add-in
→ Goal seek
→ Solver parameters
→ Decision variables and constraints
→ Tolerance levels
→ Solving problems with integer constraints
→ Using Solver for a working capital management model
→ Using Solver for a capital budgeting model
→ Using Solver for an inventory policy model
→ Using Solver for a cash management model
→ Using Solver for a capacity planning model
Exercise 4: building a model from scratch for a product mix and pricing problem
→ Goal seek
→ Solver parameters
→ Decision variables and constraints
→ Tolerance levels
→ Solving problems with integer constraints
→ Using Solver for a working capital management model
→ Using Solver for a capital budgeting model
→ Using Solver for an inventory policy model
→ Using Solver for a cash management model
→ Using Solver for a capacity planning model
Exercise 4: building a model from scratch for a product mix and pricing problem
Pivot Tables
→ Introduction to Pivot tables
→ Creating a Pivot Table report
→ Categorizing raw data
→ The Pivot Table wizard
→ Percentage of column, percentage of raw, and percentage of previous
→ Top and bottom 10 feature
→ Creating formulas in pivot tables
→ Retrieving data from external sources including access and internet
→ Linking Pivot Tables to MySQL
→ Pivot charts manipulation
→ Building one variable Data Tables
→ Building two variables Data Tables
Exercise 5: building a Pivot Table for portfolio data
Power Pivot Extensions:
→ Combine data from different sources in one pivot table
→ Combine huge amount of data in the most optimal way
→ Connect to databases
→ Private calculated members
→ Calculations library
→ Limitations
→ View Pivot Table MDX
→ Filtering Pivot Table to a list
→ Changing Pivot Table Defaults
→ Searching in OLAPS
→ Distributing Pivot Tables
→ Best practice
Exercise 6: creating Power Pivot applications for financial analysis
→ Creating a Pivot Table report
→ Categorizing raw data
→ The Pivot Table wizard
→ Percentage of column, percentage of raw, and percentage of previous
→ Top and bottom 10 feature
→ Creating formulas in pivot tables
→ Retrieving data from external sources including access and internet
→ Linking Pivot Tables to MySQL
→ Pivot charts manipulation
→ Building one variable Data Tables
→ Building two variables Data Tables
Exercise 5: building a Pivot Table for portfolio data
Power Pivot Extensions:
→ Combine data from different sources in one pivot table
→ Combine huge amount of data in the most optimal way
→ Connect to databases
→ Private calculated members
→ Calculations library
→ Limitations
→ View Pivot Table MDX
→ Filtering Pivot Table to a list
→ Changing Pivot Table Defaults
→ Searching in OLAPS
→ Distributing Pivot Tables
→ Best practice
Exercise 6: creating Power Pivot applications for financial analysis
Day 4
Monte Carlo Simulation using Crystal Ball:
→ Introduction to Crystal Ball
→ Introduction to probability distributions and their importance in decision making
→ Stochastic vs static models
→ Building a stochastic model
→ Moving away from “Best, Worst and Normal forecasts” to more dynamic solutions
→ Monte Carlo simulation versus “what if” scenarios
→ Incorporating decision rules into Monte Carlo simulations
→ Introducing assumptions
→ Using the step functionality vs running the simulation for 1 million trials
→ Using the Fit to automatically get the distribution of historical numbers
→ Step by step application of Monte Carlo Simulation
Exercise 7: Using Monte Carlo Simulation to calculate the NPV of a project
→ Introduction to probability distributions and their importance in decision making
→ Stochastic vs static models
→ Building a stochastic model
→ Moving away from “Best, Worst and Normal forecasts” to more dynamic solutions
→ Monte Carlo simulation versus “what if” scenarios
→ Incorporating decision rules into Monte Carlo simulations
→ Introducing assumptions
→ Using the step functionality vs running the simulation for 1 million trials
→ Using the Fit to automatically get the distribution of historical numbers
→ Step by step application of Monte Carlo Simulation
Exercise 7: Using Monte Carlo Simulation to calculate the NPV of a project
Introduction to VBA
→ What is a macro?
→ Creating a simple macro
→ Changing multiple properties at once
→ Assigning a shortcut key to a macro
→ Looking inside a macro
→ Objects, properties and methods
→ Navigating the Visual Basic Auditor
→ Manipulating recorded properties
→ Eliminating repeated objects in a recorded macro
→ The Select…Selection structure
→ The With Selection structure
→ Making long statements more readable
→ Designate a trusted location for macros
→ Looping
→ Branching
→ Automating spreadsheets
→ Retriveing data from non-Excel sources
→ Recording a macro that runs other macros
Case Study: Automating database update with new month data:
• Task one: automating transfer of data from a non-Excel source
• Task two: automating data filling
• Task three: automating column addition
• Task four: automating data base update
• Task five: recording a macro that runs other macros
• Handling pop up messages automatically
• Simplifying the subroutine statements
• Finding your ways in VBA even if you are not a programmer
Exercise 8: practical automatic update of a database with huge monthly data.
→ Creating a simple macro
→ Changing multiple properties at once
→ Assigning a shortcut key to a macro
→ Looking inside a macro
→ Objects, properties and methods
→ Navigating the Visual Basic Auditor
→ Manipulating recorded properties
→ Eliminating repeated objects in a recorded macro
→ The Select…Selection structure
→ The With Selection structure
→ Making long statements more readable
→ Designate a trusted location for macros
→ Looping
→ Branching
→ Automating spreadsheets
→ Retriveing data from non-Excel sources
→ Recording a macro that runs other macros
Case Study: Automating database update with new month data:
• Task one: automating transfer of data from a non-Excel source
• Task two: automating data filling
• Task three: automating column addition
• Task four: automating data base update
• Task five: recording a macro that runs other macros
• Handling pop up messages automatically
• Simplifying the subroutine statements
• Finding your ways in VBA even if you are not a programmer
Exercise 8: practical automatic update of a database with huge monthly data.
Loops
→ Creating loops
→ For Each Loop
→ For Loop
→ Do Loop
→ Managing large loops
→ Set a breakpoint
→ Set a temporary breakpoint
→ Show progress in a loop
Exercise 9: recording a macro in Excel and navigating through the Basic Editor
→ For Each Loop
→ For Loop
→ Do Loop
→ Managing large loops
→ Set a breakpoint
→ Set a temporary breakpoint
→ Show progress in a loop
Exercise 9: recording a macro in Excel and navigating through the Basic Editor
Day 5
Building custom new functions in Excel using VBA
→ Using a custom function from a worksheet
→ Introduction to Project Explorer
→ Adding arguments to a custom function
→ Making a function volatile
→ Making arguments optional
→ Using a custom function from a macro
→ Developing and storing a new add-in
→ Making use of the hundreds of freely available custom functions in the Internet
Exercise 10: Building a new custom Function in Excel and storing it in an add-in to be available whenever Excel opens
Case study: Building a loan amortization table using VBA
→ Coding in VBA
→ Best practice order structure
→ Analyzing the problem and translating it into a macro
→ Explaining the parameters of the table
→ Setting the required calculations
→ Designing the code
→ Testing the code
→ Analyzing potential errors
→ Running the macro in different settings
→ Creating a custom function for the loan amortization table
Exercise 11: Analyzing the loan amortization code in VBA using twodifferent ways
→ Introduction to Project Explorer
→ Adding arguments to a custom function
→ Making a function volatile
→ Making arguments optional
→ Using a custom function from a macro
→ Developing and storing a new add-in
→ Making use of the hundreds of freely available custom functions in the Internet
Exercise 10: Building a new custom Function in Excel and storing it in an add-in to be available whenever Excel opens
Case study: Building a loan amortization table using VBA
→ Coding in VBA
→ Best practice order structure
→ Analyzing the problem and translating it into a macro
→ Explaining the parameters of the table
→ Setting the required calculations
→ Designing the code
→ Testing the code
→ Analyzing potential errors
→ Running the macro in different settings
→ Creating a custom function for the loan amortization table
Exercise 11: Analyzing the loan amortization code in VBA using twodifferent ways
Who Should Attend?
This highly practical and interactive course has been specifically designed for
All Excel Users particularly:
→ 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
FAQ
What language will the course be taught in and what level of English do I need to take part in a LEORON training program?
Most LEORON courses are delivered in English. However, there are some courses offered in Arabic, mainly online. For our in-house courses, sessions can be curated and delivered in any language upon request. In general, the best way to confirm language availability is to check with our Enrollment Managers for the most up-to-date information. Simply click on “Let’s talk on WhatsApp” to chat with us directly.
What formats are the courses offered in?
LEORON delivers training in various formats including face-to-face, live virtual sessions, self-paced learning, in-house delivery as well as online courses.
Are LEORON Public courses certified by an official body/organization?
Yes, most LEORON public courses are accredited by internationally recognized bodies such as CIPD, ATD, PMI, EdEx, and many others—depending on the course.
Who accredits LEORON’s training programs?
LEORON partners with over 20 international bodies such as PMI, CIPD, ATD, EdEx, NASBA, CISI, GARP, HRCI, SHRM, ACCA, ASQ, IIA, ILM, IAC, and others
Are CPD points or PDUs provided?
Yes, learners can earn CPD credits and professional development units (PDUs) including NASBA CPEs, PMI PDUs, CISI, GARP, HRCI, SHRM, and more.
How can I register for a course?
You can register through our website by filling in the inquiry form, or by speaking directly with one of our consultants via WhatsApp or email. Once we confirm your interest, we’ll guide you through the steps.
When is the registration deadline for public courses?
Registration typically closes 14 days before the course start date, with occasional late registrations accepted upon confirmation
What is included in the course fee?
The fee generally covers 5-star venue facilities, training materials, certified instruction, lunches and refreshments, plus certification and membership where applicabl0065
Are there group rates or discounts?
Yes, group bookings and corporate-level discounts are available. Learners are encouraged to reach out to discuss specific arrangements
What support is available with registration?
Enrollment Managers and a Registration Desk assist with the entire process, including deadlines, travel logistics, and course customization. As well as any other special requests you might have. Simply to go your preferred course and click on “Let’s chat on WhatsApp” to do so.
Can I request a bespoke course at my location or within my organization?
Yes, in-house training is fully customizable in terms of curriculum, language, delivery, and timing. You can suggest dates and locations. Simply to go your preferred course and click on “Let’s chat on WhatsApp” in order to address any questions or concerns in this regards.
What is the refund or cancellation policy?
Refund and cancellation policies vary depending on the course type and location. Generally, cancellations made at least 14 days before the course start date may be eligible for a full or partial refund, while cancellations made closer to the course date may incur a fee. For exact terms, please consult your Enrollment Manager or refer to the course confirmation email.
Can I register multiple employees from my company?
Yes. We support group registrations and offer corporate packages for organizations enrolling multiple participants. Our team can help coordinate the logistics for group bookings.
Who should attend these courses?
LEORON caters to a variety of professionals: from those seeking leadership development to project managers, HR specialists, finance professionals, cybersecurity, procurement, Ai enthusiasts and many others.
Do I need prior experience or academic qualifications?
Not always. Many specialized paths, like cybersecurity, accept learners without prior experience. However, some courses (e.g., PMI PDU-based ones) may have recommended prerequisites. Its always better to chat with one of our Enrollment Managers to discuss more. Simply to go your preferred course and click on “Let’s chat on WhatsApp” to do so.
Will I receive a certificate after completing the course?
Yes. Upon full attendance and successful completion, you will receive a certificate of participation or accreditation, depending on the course.
Are meals and refreshments included in face-to-face courses?
Yes. For in-person courses, lunch and coffee breaks are provided daily at the venue.
Can LEORON deliver a course in-house at our organization?
Absolutely. All programs can be delivered privately at your company or virtually for your team, customized to match your internal goals and structure.
Reviews
- Review:The CFM course exceeded my expectations. The instructor, Mr. Hamed, utilized every minute in the course. He is a world-class trainer and made a complex subject very interesting and understandable. My learning curve in Macro & VBA accelerated sharply during the course. I would definitely recommend Mr. Hamed and LEORON for financial modeling courses. -Saudi Venture Capital Investment Company, Kingdom of Saudi ArabiaMuaath AltukhaifiFinancial Analyst
- Review:Without doubt this has been one of the most beneficial courses that I have ever attended, thanks for the very efficient and knowledgeable instructor as well as for the most fitting and impeccable arrangements by the team. -Sahara Petrochemicals CompanyShahul Hameed AyubFinance Manager
- Review:Extremely Intensive course, led by a fantastic dynamic trainer. From Day 1, I was very excited about the course. By Day 5, I was totally blown out by the tremendous possibilities now open to me by using all the functionalities of the Macros/VBA/Power Pivot. A definitely MUST HAVE for all financial experts. -Rogers Aviation, MaputoClaudinette PermalFinance and Admin Manager
- Review:Great course!!! The whole course was broken down in different chapters and this was structured in very digestible segments. Further, the mix of presented material with hands-on practical hit on all of the key areas. I can take what I learned and start applying it directly to my current tasks. Hamed presented the material with an expert knowledge of the concepts and backed it up with consulting experiences. His technical expertise in the area was reflected in all areas of the course and it was done very professionally. He made a point not only to share theoretical understanding but highlighted both features right & wrong with detailed explaining how to use them in building successful models. Tons of time saved. Complicate things made easy. Thanks, Hamed Great job -Alghanim KuwaitReyaz HussainSenior Analyst – Group Support
- Review:I use Excel regularly, but the CFM training has exposed me to many more tools in excel, as well as skills in building dynamic models, and detailed financial analysis. This has been made possible by the in-depth knowledge and skills of Hamed, and his hands-on practical approach to the training. -Accra, GhanaNoah TeyeCredit Analyst
