FMM1Financial Modelling Master LVT – Advanced Diploma in Financial Modelling using Excel and VBA
Rating:
English
Advanced
No available sessions at this moment.
Get in touch with our Training Advisior to find out about the next available sessions.
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.
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.
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 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 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
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 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
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
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
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.
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
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 two different ways
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
Related Courses
FAQ
What language will the course be taught in and what level of English do I need to take part in an LEORON training program?
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.
Are LEORON Public courses certified by an official body/organization?
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
What is the deadline for registering to a public course?
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 +91 4 95 5711 or register@leoron.com.
What does the course fee cover?
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.
Does LEORON give discounts?
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.
Reviews
- Review:IAMM Internal Audit Maturity ModelIn recognition of their dedication and contribution supporting IKEA Saudi Arabia in arranging training programs during 2017 & 2018, looking forward for more development and exciting effort this year. On behalf of IKEA Saudi Arabia, THANK YOU!Dalal Kutbi
- Review:Certified Professional in Quality and Patient SafetyDating back to 2014, Mobily’s “LEORON” experience has grown from a single collaboration to a long-term partnership. We consider “LEORON” Institute as a strategic partner, whose contribution has been nothing but premium in equipping our staff with field-based knowledge and information. Past three years have resulted with an expanded collaboration with superior customer service and support. Best Regards,Turki S. Alsahaan
- Review:Certified Professional in Quality and Patient SafetySince the partnership was signed between BAE Systems Saudi Development & Training and LEORON in 2017, we have been working together to offer the Saudi market a complete portfolio of training solutions benefiting from the wide and extensive experience of both parties. Recognizing the great success of this partnership, we are looking for further collaborations in the future that will position both companies as one of the leading training providers in Saudi Arabia. We thank the LEORON team for their full cooperation and continuing support, and look forward to further success together in the years to come.Emad Alrajih
- Review:Certified Professional in Quality and Patient SafetyWe have been working with LEORON for the past two years and will be working with them again this upcoming year. The programs they delivered were fruitful and exciting and our organization has received positive feedback from the participants. What our organization aims to do is to provide at no cost training for all the private sector employees so that they may benefit from our offered programs in Innovation and Professional development. Leoron has helped us achieve this goal. We look forward to continuing this service and wish them the best of luck. Regards,Nasser M. Al-Subaie
