Financial Modelling Excel Masterclass

DESCRIPTION
This 3-day course is directed at experienced Excel users who are wanting to take their spreadsheets to a new level. You will learn how to create robust and professional models utilizing Excel functionality that most Excel users do not know how to use.

Day 1 is the first part of a three part course on Financial Modelling with Excel. It builds the foundation for building strong financial models. It will cover practical application of excel's features used to solve real world cases where people work with finance. People who would benefit from this course include:

• People who work with financial data
• Accountants who wish to learn to use Excel more effectively
• Managers who want to be able to analyse financial data
• Analysts
• Investors

Outline

• What is Financial Modelling
• Who uses Financial Models
• Basic Financial Statements
  
  • Income Statement
  • Balance Sheet
  • Cash Flow Statement
  • Compounding
  • Capitalization
• A Few Essential tools of the Trade
  
  • Keyboard shortcuts in Excel
  • CTRL combination shortcut keys
  • Function keys
  • Other useful shortcut keys
• Importing Data
  
  • Converting Files from Other Applications
  • Copying Data from another Application
  • Importing Data from External Sources
  • Using Database Terminology
  • Importing Data into Excel
  • Importing Text Files
  • Refreshing data from imported text files
• Using Microsoft Query
  
  • Adding a Data Source
  • Creating a Query
  • Review Questions
• Named Ranges
  
  • Navigating Workbooks using cells or Range names
  • Creating named Ranges based on Cell values
  • Deleting named cells or ranges
  • Using named Cells and ranges in Formulas
  • Named Formulas and Values
  • Dynamic named ranges
• Database and List Management
  
  • Using the Excel database features
  • Creating an Excel database
  • Working with an Excel database
  • Assigning a database list
  • Creating an assigned list
  • Adding summary formulas to an assigned list
  • Sorting a Database
  • Adding other sort criteria
• Using AutoFilters
  
  • Filtering data
  • Filtering data with AutoFilter
  • Specifying a conditional filter
  • Using the Top 10 AutoFilter
  • Removing all AutoFilters
  • Creating Dynamic Named range using Lists and Tables
• Functions in Excel
  
  • Overview of Add ins
  • Using the Function wizard
  • displaying the Insert Function wizard
  • Choosing a function from the Insert Function wizard
  • Entering Function Arguments
  • Using the Formula Auditing Toolbar
  • Using the Watch Window
• Decision Making
  
  • Logical Functions
  • Lookup Functions
  • SumIf and CountIf
  • Text functions
  • Dynamic Labelling
  • Using Find
  • Counting characters
  • Extracting elements of text
  • Removing trailing spaces
  • Changing the case of text
• Date and Time functions
  
  • Extracting elements of dates
  • Getting today's date and/or time
  • Working out how many years between two dates
  • Functions to count the number of whole working days between dates, excluding holidays
  • Functions to state the first working day of a month
  • Data Validation - Using Lists
• Number Formatting
  
  • Common Number Formats
  • Custom Number Formats
• Using Styles
  
  • Creating a new style
  • Applying a style
  • Changing the formatting of a style
  • Removing a style from cells
  • Deleting a Style
• Formatting
  
  • Using conditional formatting
  • Changing and deleting Conditional Formatting
  • Finding cells with Conditional Formatting
  • Copying and applying cell formatting with the Format Painter
  • Copying and applying column width or row height with the Format Painter
  • copying and applying the Format Painter in multiple locations
  • Using Text() to add an ID
• More on Lookup Functions
  
  • Index and Match
  • Choose
  • VookupL and HLookup
  • Getting Excel to apply exchange rates to foreign currency Accounts