Financial Modeling using Excel - Day 1

What is financial modelling?

Who uses financial models?

Basic Financial Statements and Principles
• 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
• Choosing names for Cells and Ranges
• Naming Cells or Ranges
• Navigating Workbooks using Cell 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 Range

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
• Sorting data within a database
• Adding other sorting criteria

Using AutoFilters
• Filtering data
• Filtering data with AutoFilter
• Specifying a conditional filter with AutoFilter
• 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 in the Insert Function wizard
• Using the Formula Auditing Toolbar
• Displaying the Formula Auditing toolbar
• Using the Formula Auditing toolbar
• Tracing Precedents
• Removing Precedent Arrows
• Tracing Dependents
• Removing Dependent Arrows
• Adding a new comment
• Evaluating Formulas one step at a time
• 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 & 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
• Number Formats (Custom and 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 conditional formatting
• 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

Lookup Functions
• Index and Match
• Choose
• VLookup and HLookup
• Getting Excel to apply exchange rates to foreign currency accounts