Financial Modelling In Ms Excel

Automate your financial reports and financial models with MS Excel

What you'll learn

Participants will learn the theory and application of Financial Modelling and how to use MS Excel in creating Financial Models from scratch.

Learn how to use MS Excel more efficiently

Organize, sort and arrange large data in MS Excel

Learn how to use excel advance functions such as vlook up and macros

Extract data and prepare company's Profit and Loss Statement

Automate Statement of Financial Position - The Balance Sheet

Automate Cash Flow Statement Based on the Profit and Loss and Balance Sheet

Learn the capital structure of a firm, gearing and betas

Learn how to calculate the Weighted Average Cost of Capital - WACC

Understand the concept of Discounted Cash Flow - DCF

Use DCF technique to calculate the NPV and IRR of a project in MS Excel

Prepare a complete investment appraisal financial model

Perform business valuation using different methods

Requirements

Basic knowledge of MS Excel and Basics of Accounting and Finance

Description

Course OverviewFinancial Modelling is an essential skill for accounting and finance professional. It is very much in demand in the job market and is highly valued by employers.Our Financial Modelling training takes you from basics to professional level. This sixteen-hour training is based on practical exercises.  The course focuses 40% on honing the participants MS Excel skills and 60% focus on application of MS Excel in Accounting and Finance.Detailed Content1. Introduction to Excel2. Useful tips and tools for your work in Excel3. Keyboard shortcuts in Excel4. Excel's key functions and functionalities made easy5. Update! SUMIFS6. Financial functions in Excel7. Microsoft Excel's Pivot Tables8. Building a complete P&L in Excel - Case Study9. Introduction to Excel charts10. Profit and Loss - Case Study11. Statement of Financial Position - Case Study12. Statement of Cash Flows - Case Study13. Financial modeling fundamentals14. Introduction to Company Valuation and Introduction to Mergers & Acquisitions15. Learn how to build a Discounted Cash Flow model in Excel - NPV and IRR16. Investment Appraisal - Case Study17. Business Valuation - Case Study18. Capital Budgeting - The theory19. Capital Budgeting - Case Study20. Impact of interest rates and exchange rates on NPV21. Sensitivity Analysis in Capital BudgetingPrerequisites1. Participants are expected to have basic knowledge of MS Excel. This could be measured as an MS Excel user for more than one year.2. Basic knowledge of financial accounting. 3. Microsoft Office 2013 or later installed on your computer.About the InstructorA qualified accounting and finance professional with over twenty years of extensive experience in diversified industry sectors such as auditing, large scale manufacturing and oil and gas.Like most accounting and finance professionals, I started my career as finance executive and then over the years rose to the position of CFO in a multinational company in oil and gas industry.I have also worked as a consultant with the World Bank and European Union on different projects in Middle East, Eastern Europe and CIS countries during 2011 to 2018 as a principal consultant for IFRS and Financial Management.I am qualified professional with three professional qualifications MBA, ACCA and CIMA UK. I have been teaching IFRS, Financial Reporting, Financial Management and Performance Management for over fifteen years and my focus areas are ACCA and CIMA qualifications.

Overview

Section 1: Getting to grips with MS Excel

Lecture 1 Financial Modelling Introduction

Lecture 2 Intro to Excel Ribbon and Menu

Lecture 3 Copy Paste, Cut Paste, Linking the Sheets

Lecture 4 Data Sort and Sub Totals

Lecture 5 Locking Cell References in Formulas

Lecture 6 Fast Scrolling with F5

Lecture 7 Dynamic Naming

Lecture 8 Lock Cell References with $ sign

Section 2: Formating and Data Sorting in MS Excel

Lecture 9 What If Analysis and Conditional Formatting

Lecture 10 Formatting Basics Using Macros

Lecture 11 Data Filters in Tables

Lecture 12 IF and AND Function

Lecture 13 Pivot Tables Basics

Lecture 14 Format Table Function

Lecture 15 DSum and DAverage

Lecture 16 Removing Error

Section 3: Basics of Financial Management

Lecture 17 Compounding and Discounting

Lecture 18 Discounted Cash Flow - DCF

Lecture 19 What is NPV

Lecture 20 Internal Rate of Return - IRR

Section 4: Create Your First Financial Model for Profit and Loss Account

Lecture 21 Forecast Profit and Loss - Introduction

Lecture 22 Forecast Profit and Loss - Revenue and COS.mp4

Lecture 23 Forecast Profit and Loss - Fixed and Variable Costs

Lecture 24 Forecast Profit and Loss - Finalizing.mp4

Lecture 25 Completing the Profit and Loss

Section 5: Financial Model for Statement of Financial Position - Balance Sheet

Lecture 26 Structure of Balance Sheet

Lecture 27 Balance Sheet Assumption

Lecture 28 Introduction to Cash Flow Statement

Section 6: Construct your Statement of Cash Flow Based on PnL and Balance Sheet

Lecture 29 Forecast Cash Flow Statement

Lecture 30 Completing the Statement of Financial Position

Lecture 31 Completing the Forecast Cash Flow Statement

Section 7: Extracting Data From Your System and Preparing Profit and Loss Statement

Lecture 32 Vlook up

Lecture 33 Analyzing the Data and Creating Unique Codes

Lecture 34 Using the V Lookup to Extract the Data

Lecture 35 Mapping the Account Codes to P&L Accounts

Lecture 36 Creating the Final Profit and Loss Output Sheet

Section 8: Capital Budgeting - Investment Appraisal - RISK and Betas

Lecture 37 Cash Flow Based Valuation

Lecture 38 Introduction to Capital Budgeting

Lecture 39 Introduction to Risk

Lecture 40 Beta Values and CAPM

Lecture 41 Geared and Ungeared Betas

Section 9: Financial Modelling and Investment Appriasal

Lecture 42 Introduction to DCF Financial Model

Lecture 43 Fixed Assets and Depreciation Schedule

Lecture 44 Depreciation and Tax Benefits

Lecture 45 Calculating the Operating Cash Flows

Lecture 46 Common Issues with Operational Assumption in DCF Model

Lecture 47 WACC and Beta

Lecture 48 Calulating Project Beta, Cost of Equity and WACC

Lecture 49 Getting the Final Output of DCF Model

Lecture 50 Calculating the Internal Rate of Return (IRR)

Lecture 51 Sensitivity Analysis of the Project

Section 10: Business Valuation

Lecture 52 Fin Modelling - Intro to Business Valuation

Lecture 53 Fin Modelling - Understanding a Case Study and Intro

Lecture 54 Fin Modelling - Business Valuation Final

Accounting and Finance professionals as well as accounting and finance students.