Financial Modelling using Excel

Course Objectives

Upon completion of this program, candidates should be able to:

  • Understanding of a spreadsheet ad its benefits
  • Equip with essential skills of effectively utilizing spreadsheet software in a day-to-day business environment
  • Be an Excel power-user with the aim of reducing non-value adding tasks.
  • Create Financial Models and Investment Models
  • Create Scenario Analysis and Dash Boards
  • Create Drop-down boxes and scroll-bars for flash analysis
  • Understand and apply Forecasting concepts and being able to track forecasting errors
  • Apply certain new functions appearing in Excel 2007 onwards
  • Perform What If Analysis using:
    – Goal Seek
    – Solver
    – Input Table
    – Scenarios
    – Record a Macro and assign it to an Object

Course Outlines

  • Defining Financial Modeling.
    – What it is?
    – Who uses it?
    – Why it matters?
  • Looking at Examples of Financial Models
    – Project finance models
    – Pricing models
    – Integrated financial statement models
    – Valuation models
    – Reporting models
  • Making Sense of the Different Versions of Excel
    – A rundown of recent Excel versions
    – Focusing on file formats
  • Defining Modern Excel
  • Recognizing the Dangers of Using Excel
    – Capacity
    – Lack of discipline
    – Errors
  • Looking at Alternatives and Supplements to Excel
  • Identifying the Problem That Your Financial Model Needs to Solve
  • Designing How the Problem’s Answer Will Look
  • Gathering Data to Put in Your Model
  • Documenting the Limitations of Your Model
  • Considering the Layout and Design of Your Model
    – Structuring your model: What goes where
    – Defining inputs, calculations, and output blocks
    – Determining your audience
  • Referencing Cells
    – Relative cell referencing
    – Absolute cell referencing
    – Mixed cell referencing
  • Naming Ranges
    – Understanding why you may want to use a named range
    – Creating a named range
    – Finding and using named ranges
    – Editing or deleting a named range
  • Linking in Excel
    – Internal links
    – External links
  • Using Shortcuts
  • Identifying the Difference between a Formula and a Function
  • Finding the Function You Need
  • Getting Familiar with the Most Important Functions
    – SUM
    – MAX and MIN
    – AVERAGE
    – COUNT and COUNTA
    – ROUND, ROUNDUP, and ROUNDDOWN
    – IF
    – COUNTIF and SUMIF
    – VLOOKUP and HLOOKUP
  • Being Aware of Advanced Functions and Functionality
  • Identifying the Differences between Types of Analysis
  • Building Drop-Down Scenarios
    – Using data validations to model profitability scenarios
    – Applying formulas to scenarios
  • Applying Sensitivity Analysis with Data Tables
    – Setting up the calculation
    – Building a data table with one input
    – Building a data table with two inputs
    – Applying probability weightings to your data table
  • Using Scenario Manager to Model Loan Calculations
    – Setting up the model
    – Applying Scenario Manager
  • Deciding Which Data to Display
  • Conveying Your Message by Charting Scenarios
  • Deciding Which Type of Chart to Use
    – Line charts
    – Bar charts
    – Combo charts
    – Pie charts
    – Charts in newer versions of Excel
  • Getting to Know the Case Study
  • Entering Assumptions
    – Revenue assumptions
    – Expense assumptions
    – Other assumptions
  • Calculating Revenue
    – Projecting sales volume
    – Projecting dollar sales
  • Calculating Expenses
    – Staff costs
    – Other costs
    – Depreciation and amortization
  • Building the Income Statement
  • Building the Cash Flow Statement
  • Building the Balance Sheet
  • Building Scenarios
    – Entering your scenario assumptions
    – Building a drop-down box
    – Building the scenario functionality
  • Understanding How the Discounted Cash Flow Valuation Works
  • Step 1: Calculating Free Cash Flow to Firm
  • Step 2: Calculating Weighted Average Cost of Capital
  • Step 3: Finding the Terminal Value
  • Discounting Cash Flows and Valuation
  • Getting Started
    – Making a reusable budget model template
    – Creating dynamic titles
  • Output 1: Calculating Cash Required for Budgeted Asset Purchases
  • Output 2: Calculating Budgeted Depreciation
    – Useful life
    – Written-down date
    – The depreciation schedule for the current year
    – Depreciation in prior periods
  • Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet.

Pre-requisite

Course Details

  • Course Title: Financial Modelling using Excel
  • Class Schedule: TBA
  • Time: 9am to 5pm
  • Duration (Days): 2 days
  • Certification Body: Microsoft

Course Fee

RM1,200