Microsoft Excel Financial Modelling
Duration: 2 Days
Overview:
Click here for
printer friendly version of outline
This 2-day course is directed at experienced Excel users who want 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.
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
Prerequisites:
You need to have Intermediate Excel Skills.
Course Topics:
What is Financial Modelling
Basic Financial Statements
Income Statement
Balance Sheet
Cash Flow Statement
Compounding
Capitalization
Importing Data
Converting Files from Other Applications
Copying Data from another Application
Importing Data from External Sources
Using Microsoft Query
Adding a Data Source
Creating a Query
Named Ranges
Navigating Workbooks using cells or Range names
Creating named Ranges based on Cell values
Using named Cells and ranges in Formulas
Dynamic named ranges
Database and List Management
Using the Excel database features
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
Functions in Excel
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
Date and Time functions
Extracting elements of dates
Getting today's date and/or time
Functions to count the number of whole working days between dates, excluding holidays
Functions to state the first working day of a month
Using Styles
Creating a new style
Applying a style
Changing the formatting of a style
Removing a style from cells
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
