Excel Financial Modelling Introduction

Duration: 2 days

Overview:

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.

Topics:

1 - What is Financial Modelling

Basic Financial Statements

Income Statement

Balance Sheet

Cash Flow Statement

Compounding

Capitalization

2 - Importing Data

Converting Files from Other Applications

Copying Data from another Application

Importing Data from External Sources

3 - Using Microsoft Query

Adding a Data Source

Creating a Query

4 - 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

5 - 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

6 - Using AutoFilters

Filtering data

Filtering data with AutoFilter

Specifying a conditional filter

7 - Functions in Excel

Entering Function Arguments

Using the Formula Auditing Toolbar

Using the Watch Window

8 - Decision Making

Logical Functions

Lookup Functions

SumIf and CountIf

Text functions

Dynamic Labelling

Using Find

9 - 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

10 - Using Styles

Creating a new style

Applying a style

Changing the formatting of a style

Removing a style from cells

11 - 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

12 - More on Lookup Functions

Index and Match

Choose

VLookup and HLookup