About QFBA
Course has been added to your shopping cart

Financial Modeling Using Excel-Advanced

Start Date:
10 Dec 2018
QAR 7500
Course Name: Financial Modeling using Excel - Advance
Course Description:
This highly practical, hands-on course has been designed to build on your basic knowledge of finance and excel.The course will strengthen the already existing model structuring skills.A structured design strategy will be adopted throughout the course in order to develop the skills of good excel model development.These skills are then applied to building high quality financial models. Participants will have the chance to work on their own models.
On completion of the course, participants will be able to:
  • Design, cross-check and                structure financial models effectively
  • Build robust and effective models in which add- ins can be perform and to which amendments can be made without destroying the model integrity
  • Create and compare scenarios –               current case, best case, worst case and expected case – in order to make decision-making easier
  • Create models quickly following                the basic rules of good model construction        
Target Audience:
Analysts, Investors, Investment advisers, Planners and other     finance professionals.
Course Structure
The course is conducted using a combination of lectures, discussions and practical workshop sessions.
Course Facts
Duration:  5 Days

Program Contents:
  • Useful ideas to speed up spreadsheet design
  • Validating data and producing dropdown lists
  • Sumif Countif and excel’s database
  • Vlookup, Hlookup, Lookup, Match & Index
  • Row and column functions
  • Outlining – collapsing rows and  columns
  • Working with arrays, array formulas and array constants
  • Some other advanced functions (e.g. data consolidation, macro recording and use of the CTRL key)
  • Model   structure , Types of financial models, Monthly financial  models
  • Financial ratios and sensitivity analysis
  • Cost of capital, loans – capital and interest repayment
  • Return on investment
  • Scenarios and Valuing companies
  • Model optimisation and Basic forecasting
Program Modules:
Module 1: Introduction to Excel
You start by identifying parts of an Excel program and learn how to use mathematical features, functions and cell references.
Software Objective
  • Start Microsoft Excel
  • Learn How to move around a workbook
  • Using formulas
  • Formatting
  • Exercises
Module 2: Financial Accounting and Management Accounting
Now you apply what you have learned so far using Excel for accounting projects. 
  • Financial Statement Analysis using ratios
  • Break-Even Analysis
  • Flexible Budget and Variance Analysis
  • Companies Valuation
  • Exercises
Module 3: Model Building: Mastering Key Steps to Practically Design and Structure your Spreadsheet Layout
Practical Exercises in order to determine the best financing structure and address all the risks for your company’s financial processes, you need to design and construct a financial model that will assist you in effectively managing your company’s financial affairs. By applying this model, you will be able to use tried and tested spreadsheet techniques for tracking your company’s cash flows and monitoring the profitability of your organization. Master the key steps in successfully building a comprehensive and accurate finance model as a decision-making tool by understanding:
  • What is financial modelling, its objectives and rules?
  • Formatting of the spread sheet
  • The layout in designing an error-free financial model
  • Data validation
  • The features of filters
  • The appropriate use of custom filters
  • How to access data using VLOOKUP and HLOOKUP?
Module 4: Developing the Appropriate Techniques for using Data Tables
Practical Exercises
By constructing data tables in your spreadsheet model, you will be able to view, not only a few scenarios of your data, but also a large number of permutations that can be summarized in one table. This will help you to achieve a broader perspective of your financial model. Data tables are the basis of Excel’s most sophisticated way of developing a sensitivity analysis. You can learn how to apply these key techniques by:
  • Examining the features of a data table
  • Understanding the use of data tables in a marketing budget
  • Applying conditional formatting within data tables
  • Analysing various formatting techniques
Module 5: Effectively using Pivot Tables to Enhance the Functionality of your Spreadsheet Model
Practical Exercises
When overseeing your company/ department’s financial model, you are expected to effectively manage the revenues and costs and at the same time, consider the impact your decisions have on the profitability and growth of your company, either strategically or tactically. Pivot Tables are used to perform a cross-tabulation of data, summarizing data into one or more classifications. By creating Pivot Charts, which combine all the same functionality of standard Excel charts with the dynamic characteristics of Pivot Tables, you will achieve a graphic representation of a Pivot Table that updates data whenever it is changed. Boost your understanding of this key feature by learning how to:
  • Understand the key features of a Pivot Table
  • Prepare a Pivot Table
  • Analyse and display data from different points of view
  • Create a Pivot Chart from your Pivot Table
Module 6: Applying Goal Seek and Solver Features to Maximize the Output of your Financial Model
Practical Exercises
When you are estimating future costs and preparing forecasting reports for your management team, you need to know your products/services and your market. But, is that sufficient for dealing with uncertainty? When you use the Goal Seek feature to find a value of a single input you will be able to achieve a desired level of an output. By applying the Solver technique, you will be able to determine a combination of inputs which optimizes (maximizes or minimizes) an output. Understand the practical application of these techniques in your financial model by:
  • Undertaking a sensitivity analysis with the use of data tables
  • Using Goal Seek to find the ‘indifference point’
  • Adopting Solver to maximise profit
  • Using Excel for forecasting
 Module 7: Applying Scenario Techniques to Determine Alternatives and Constraints in your Financial Model
Practical Exercises
When modeling more complicated problems than data tables, you may encounter as many as 32 variables during the process. By creating multiple scenarios for a single ‘what-if’ model, you will be able to assess each scenario with its own set of variables. You can examine the relationships between scenarios, using scenario summary and pivot table reports by:
  • Defining scenarios in your spread sheet
  • Understanding how to browse your scenarios
  • Adding, editing and deleting scenarios
  • Routing and merging scenarios
  • Creating scenario reports
 Module 8: Charting your Data in order to create Effective Analysis of your Financial Model
Practical Exercises
Charts have a way of changing over time. New data arrive, old data become obsolete or irrelevant and new visual comparisons become meaningful. Excel’s procedures for working with the data can be most effective in creating your charts. You can also add new points and series to a chart, and change the order in which Excel plots your series and multilevel categories of information. Learn how to add trend lines and error bars and apply the various techniques for effectively charting your spreadsheet data by:
  • Adding data
  • Removing data
  • Changing or replacing data
  • Plotting or marking every “nth” point
  • Changing the plot order
  • Using multilevel categories
  • Applying trend lines
  • Creating error bars
Module 9: Determining the appropriate Capital Budgeting Models to formulate Effective Capital Investment Decisions
Practical Exercises
When there is uncertainty about the changes to the variables that are applied to your cash flow statement, you also need to determine whether or not an investment is viable. You can also develop your ability to pinpoint which quantitative method is most beneficial for calculating your future project cash flow or when calculating the cost of payback on assets purchased. Discover how Excel can help you decide whether to undertake an investment project and correctly rank your projects. Learn how and when to use Excel’s NPV, IRR functions to assess your capital expenditure project by:
  • Understanding the NPV rule for judging investments and projects
  • Assessing the IRR rule for judging investments
  • Determining the appropriate method to use - NPV or IRR
  • Evaluating when IRR and NPV give the same answer
  • Establishing when to apply IRR and NPV for different project rankings

Sign up for our newsletter and get important course updates right in your inbox.

لنتائج أفضل، يرجى ملء الإستمارة بالأحرف الإنكليزيةء كل المعلومات مطلوبة
Read about our data protection policy here.
*All fields are required
© 2016 QFBA - QATAR Finance and Business Academy