Microsoft Excel Financial Modelling

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

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