Advanced Financial Modelling using Excel and VBA

(5-day program) Radix Training Academy (Pty) Ltd +27 (0) 84 819 8043 [email protected] Introduction: 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 programs 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. Some of the topics that will be discussed include: The 40 Excel functions that financial modellers use the most Best practice in modelling forecasted financial statements (Balance Sheet, Income Statement and Cash Flow Statement) Monte Carlo Simulation modelling The powerful combination of arrays, the offset function, the match function and drop down menus The danger of using IRR in isolation and possible solutions Modelling and automating ratio analysis in Excel Various Optimization solutions using Excel Solver (cash and inventory management, capacity planning and capital budgeting)

Pivot Tables and OLAP tables. From the basics of VBA to building a loan amortization table using a VBA macro Coding a User Form in VBA to update your database automatically.

General: It is essential to bring your Laptop that are macros-enabled with Excel 2007 (Excel 2010 is also permitted but not Excel 2003). Arthur Clarke, the renowned science writer, once said Any sufficiently advanced technology is indistinguishable from magic. This course is ultimately about understanding and using the magic of modelling and programming Microsoft Excel and VBA.

Course outline:

Day 1 Design principles for good model building: Principle of Occams 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 IFs 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

Modelling 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 What-If analysis: scenario manager, goal seek, data table Modelling 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 OLAP Pivot Table Extensions: Connect to an OLAP database 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 OLAP applications for financial analysis Day 4 Monte Carlo Simulation: Monte Carlo simulation versus "what if" scenarios Stochastic vs static models Incorporating decision rules into Monte Carlo simulations Comparing simulation to reality 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 SelectSelection 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 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 8: 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 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

Exercise 9: 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 10: Analyzing the loan amortization code in VBA using two different ways.

Building a User Form in Excel using VBA: Inserting new user form Using the toolbox Private Subs and Public Subs Adding and adjusting various toolbox controls TextBox ComoBox Label CheckBox CommandButton OptionButton Coding User Forms Initializing control properties Activating User Forms Practical uses of User Forms Linking the code to the various ActiveX Controls Utilizing VBA resources in the Internet Exercise11: Analyzing the coding of a user form to update data base of a company

Who should attend? All Excel Users particularly: Financial Analysts Chief Executive Officers Chief Financial Officers

Portfolio managers Corporate accountants Credit analysts Private equity managers 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 Compliance officers Management consultants Corporate Finance lawyers

About the Presenter:

Hamed Behairy, CFA, an internationally renowned financial modeller, consultant and trainer over the past 14 years. With global experience in the fields of financial analysis, modelling, corporate governance, consultation and training, Hamed has been consistently acclaimed as one of the top financial consultants in many countries around the world. The presenter is widely renowned as a seasoned valuation modelling expert. Hamed is the founder and CEO of Radix Training Academy (Pty) Ltd and cofounder and CEO of Radix Corporate Governance (Pty) Ltd based in Cape Town, South Africa. His clients include multinational corporates, pension funds, and asset management companies, among others Hamed has extensive training experience. His workshops combine professional knowledge along with excellent presentation skills. Participants' comments of Hamed's workshops indicate that they leave with deep understanding of the material covered and conclusive ability to apply these concepts at their workplace upon completion of his courses. He has substantial experience in training corporate executives on strategic matters including

valuation modelling, corporate restructuring, M&A modelling, applied corporate finance modelling, financial analysis modelling, attribution analysis modelling and other modelling courses. Hamed worked as a financial analyst and modeller in an asset management company. While lecturing accounting and financial analysis at the University of Alexandria, Hamed was awarded the best teaching talented lecturer. Hamed is a Chartered Financial Analyst (CFA). He qualified with BCom (honours) in accounting - cum laude with first class honour, and BCom (honours) in Financial Analysis and Portfolio Management - cum laude, with first class honour. Hamed's hands-on training approach is mixed with energy, humour and continuous interaction with delegates. The systematic build up of his workshops ensures a bottom-up explanation, leading delegates quickly from the basics through the more advanced topics.

Testimonials: Trainer has extensive knowledge. He conducts the training with passion. He is able to draw attention & participation from the crowd. The course is conducted in a structured manner Sew Yin, Astro Media, Malaysia If I did not learn anything, I saw an excellent example of presentation skills! Luckily I learned both Ulricke Meintjes, Muncih Reinsurance, South Africa Really enjoyed the course. It was intense but I learned a lot of valuable information to take back to the office. Hamed was excellent, very knowledgeable and entertaining. Great Job, thank you! Emma Winterflood, Darwin Airport, Australia The presenter is brilliant in explaining complex concepts. I found the contents of the course very practical Cecilia Pavanello, Netvigator, Hong Kong Hamed is an excellent presenter. He keeps the spirit up, energetic, a wealth of knowledge and experience Anver Dollie, ABSA Bank, South Africa