Financial Modeling in Excel

2013

EduPristine [Link]

Agenda

Introduction and Context

Efficiently using excel preparation for modeling
Excel Summarization Using Pivot Tables
Creating a template for integrated model

EduPristine | (Confidential)

Excel as the most important tool for modeling

Excel is one of the most widely used tools in financial industry

Easy to use
High reach & access to software across geographies
Flexibility
Robustness
Inbuilt features (Most people would not even be using 95% of the features) & Extendibility
Modular and Object Oriented Architecture

Excel as a data-store
Easy to store and retrieve information
Flexibility to put many data-types in the same sheet

Functions and a range of features

Excel is easily extendible to be used as a Modeling tool

Modeling Context

Understand the industry models being used

Create your own models Rather than just using them
Improve & enhance productivity in work
Extend these models for your use
Debug Problems

EduPristine | (Confidential)

Agenda

Introduction and context

Efficiently using excel preparation for modeling
Excel Summarization Using Pivot Tables
Creating a template for integrated model

EduPristine | (Confidential)

Key aspects of Modeling & Excel Usage

Building a ROBUST model is a must for other people to use your model

It should generate the correct results

It should have proper area for Inputs/ Outputs
It should be able to handle errors properly
Naming/ Labeling of data items should be done properly
Accidental changing of model parameters should be avoided
The model should be easy to understand on computer and in printout
Reusable components can be made in the excel sheet, which can be made later

SPEED is the key in modeling

A large model might have multiple excel sheets and a lot of formulas and calculations. It is necessary to
navigate through the excel sheet in a speedy manner and understand it
It is a fact that mouse is 5 times slower than using the keyboard to use excel. Due to heavy involvement
of the users, having a strong command over the keyboard shortcuts is a must!
A well designed excel sheet is easy to understand as well

EduPristine | (Confidential)

Lets take a moment to understand components of Excel

Toolbar

Formula Bar

Cells

Worksheet
Name

Excel 2007 Interface

Excel 2003 and 2007 are being widely used in the industry. Most of features of 2007 are backward compatible

EduPristine | (Confidential)

 Lets take a moment to understand components of excel

Excel 2003 and 2007 are being widely used in the industry. Most of features of 2007 are backward compatible

Excel 2007 Interface

EduPristine | (Confidential)

CTRL + S
CTRL + C
CTRL + V
CTRL + X
CTRL + Z
CTRL + A
CTRL + B
ALT + TAB
ALT + F4
CTRL + TAB

Basic Editing and Saving Excel

Save Workbook
Copy
Paste
Cut
Undo
Select All
Bold
Switch Program
Close Program
Switch workbooks
Some Special Shortcuts
Paste Special
Hide Row
Unhide Row
Hide Column
Unhide Column
Fit column width
New worksheet
Highlight row
Highlight column
Group rows/columns
Ungroup rows/columns
Delete selected cells
Edit cells

CTRL + ALT + V
CTRL + 9
SHIFT + CTRL + 9
CTRL + 0
SHIFT + CTRL + 0
ALT + H + O + I
SHIFT + F11
SHIFT + Spacebar
CTRL + Spacebar
SHIFT + ALT +
SHIFT + ALT
CTRL + Minus sign
F2

CTRL + 1
ALT + H + 0
ALT + H + 9
SHIFT + CTRL + ~
SHIFT + CTRL + !
SHIFT + CTRL + #
SHIFT + CTRL + $
SHIFT + CTRL + %
CTRL + ;
= (equals sign)
ALT + =
CTRL +
CTRL + ~
F9
SHIFT + CTRL + Enter
ALT + M + P
ALT + M + D
ALT + M + A + A
CTRL + [
CTRL + ]
F5 + Enter
SHIFT + CTRL + {
SHIFT + CTRL + }

Navigating / Editing
Arrow keys
CTRL + Pg Up/Down
CTRL + Arrow keys
SHIFT + Arrow keys
SHIFT + CTRL + Arrow
Home
CTRL + Home
SHIFT + ENTER
TAB
SHIFT + TAB
ALT +

Move to new cells

Switch worksheets
Go to end of continuous range Select a cell
Select range
Select continuous range
Move to beginning of line
Move to cell A1
Move to cell above
Move to cell to the right
Move to cell to the left
Display a drop-down list

Formatting
Format Box
Increase decimal
Decrease decimal
General format
Number format
Date format
Currency format
Percentage format
Enter the date
Formulas
Start a formula
Insert AutoSum formula
Copy formula from above cell
Show formulas/values
Recalculate all workbooks
Enter array formula
Auditing Formulas
Trace immediate precedents
Trace immediate dependents
Remove tracing arrows
Go to precedent cells
Go to dependent cells
Go back to original cell
Trace all precedents (indirect)
Trace all dependents (indirect)
Inside the cell Editing

ALT + ENTER
SHIFT + Arrow
SHIFT + CTRL + Arrow
F4
ESC

Start new line in same cell

Highlight within cells
Highlight contiguous items
Toggle $
Cancel a cell entry

Change all Inputs to Blue:

Press F5 then Select "Special" then "Constants", "OK then Manually change
selection to blue font color
Change all Formulas to Black: Select "Formulas" instead of "Constants then
change selection to black color

8
EduPristine | (Confidential)

Moving across toolbars in Excel 2003 and 2007

Press Alt Key to activate Main toolbar
For Example, Alt F for file

Use CTRL + TAB to Go to

next toolbar

Use Arrow key to navigate

further

In 2007, Pressing the ALT key automatically shows the options

EduPristine | (Confidential)

Cell Referencing - $
Use $ for Absolute Reference
A1 vs $A1 vs A$1 vs $A$1

What happens when you copy paste cells

Source

Destination

$A$1

$A$1

A$1

C$1

$A1

$A3

A1

C3

Change the referencing of a cell by F2 + F4

Refer Worksheet A

EduPristine | (Confidential)

10

Simple Exercises in Excel

The financials of a start-up company are given to you. The company would be eligible for funding if
their operating profit margin is greater than 35% and CAGR in revenue growth is greater than 50%.
Create a model to check if the company is eligible for funding. Also visually indicate the eligibility.
Use worksheet B
Hint

Use (P(t)/ P(t-k))^(1/k) -1 to calculate CAGR

Use AND function to calculate eligibility
Use IF function to print it
Use conditional formatting to output the results

Email ids of 100 people are given to you. They all need to be migrated to a new domain of
[Link]. Write a function to migrate all email ids to new domain
Use worksheet C
Hint
Use FIND to find the common character
Use LEFT function
Use CONCATENATE function

EduPristine | (Confidential)

11

Arrays in Excel
Array (Can be loosely thought of as a list) is a
group of cells or values that Excel treats as a
unit
No longer treats the cells individually, but list of
cells as an individual entity
Since individual cells are not independent
entities, so they cannot be changed individually
Enables apply a formula to every cell in the range
using just a single operation

For any matrix Operation

Calculate the exact size of the transposed Matrix
Select the appropriate range

Use the function

Press SHIFT + CTRL + ENTER

Use Worksheet D
To Run Array Functions Remember to use CTRL + SHIFT + ENTER

EduPristine | (Confidential)

12

Frequently Used Array functions

Function Name

Function

SUMPRODUCT()

To Sumproduct 2 matrices

TRANSPOSE()

To transpose a matrix

MATCH()

Match and Index are used in conjunction as a lookup function

INDEX()

Match and Index are used in conjunction as a lookup function

VLOOKUP()

To lookup for a particular value in array, with the starting column acting as a
lookup reference

COLUMN()

Returns the column number of the cell referenced

ROW()

Returns the row number of the cell referenced

HLOOKUP()

To lookup for a particular value in array, with the top row acting as lookup
reference

EduPristine | (Confidential)

13

Using the VLookUp Function

VLOOKUP() function
The V in VLOOKUP() stands for vertical
Works by looking in the first column of a table for the
value you specify
It then looks across the appropriate number of columns
(which you specify) and returns whatever value it finds
there
The final option (range_lookup) is a Boolean value that
determines how Excel finds the value. Always use
FALSE- for exact match
Whenever looking for data picked from the net, trim the strings
before comparison

Question (Use WorkSheet E)

The ID of defaulters and their amount and phone number
has been provided by the Credit Bureau in Columns A to D.
Column F contains the IDs of your clients. Use Vlookup
function to find out the default amount of your clients.

One of the most widely used functions in Excel

EduPristine | (Confidential)

14

Using the HLookup Function

HLOOKUP() function is
H in HLOOKUP() stands for horizontal
Similar to VLOOKUP()
It searches for the lookup value in the
first row of a table

Question: Use Worksheet F

Various expenses of Pristine are
given in a table. Write a function to
calculate the total expenses for any
desired month (user should be able to
change this value, and automatically
the expenses should be updated)

Both Vlookup and Hlookup have a limitation of using the first Column/ Row as reference

EduPristine | (Confidential)

15

Index & Match Functions

INDEX() returns the reference or the value of a cell at the
intersection of a row and column inside a reference

MATCH() function
looks through a row or column of cells for a value
If it finds a match, it returns the relative position of the match
in the row or column
Use the usual wildcard characters within the lookup_value
argument (provided that match_type is 0 and lookup_value is
text)
Use the question mark (?) for single characters and the
asterisk (*) for multiple characters
See Worksheet G

EduPristine | (Confidential)

16

Use Index and Match Any Column & row Lookup

Index and Match can be used in conjunction to perform complex lookup functions
Use Match to generate the row number that you are interested in
Use Index to generate the value that you are looking for
Similar to implementation of a new kind of directory service

Always Use FALSE in

the boolean required
for comparison

Use Worksheet H
Various expenses of Pristine are given in a table. Write a function to calculate the
desired expense head for any desired month (user should be able to change both the
values, and automatically the expenses should be updated)

Easily Extendible approach to get any generalized kind of search

(where both row and column can be variable)

EduPristine | (Confidential)

17

Agenda

Introduction and context

Efficiently using excel preparation for modeling
Excel Summarization Using Pivot Tables
Creating a template for integrated model

EduPristine | (Confidential)

18

Pivot Tables An introduction

Easy Summarization & Analysis Tool

EduPristine | (Confidential)

19

Pivot Tables
Summarize data in one field
called a data field

and break it down according to the data in

another field.
The unique values in the second field (called
the row field) become the row headings

Further break down your data by specifying a

third field
called the column field

EduPristine | (Confidential)

20

Terms used in Excel

Data source: The original data
Range, a table, imported data, or an external data
source.

Field: A category of data, such as Region, Quarter, or

Sales
Most PivotTables are derived from tables or databases,
a PivotTable field is directly analogous to a table or
database field

Row field: A field with a limited set of distinct text,

numeric, or date values to use as row labels in the
PivotTable
Column field: A field with a limited set of distinct text,
numeric, or date values to use as column labels for the
PivotTable
Report filter: A field with a limited set of distinct text,
numeric, or date values that you use to filter the
PivotTable view
PivotTable items: The items from the source list used
as row, column, and page labels

EduPristine | (Confidential)

21

Summarize Large Data

To start the pivot wizard:
ALT D + P
To complete the process, press finish

Use Example I
Calculate Region Wise, Year wise sales
Give Product wise Data for 2008 Only

EduPristine | (Confidential)

22

Summarize Large Data

To start the pivot wizard:

ALT D + P
To complete the process, press finish

Use Example I
Calculate Region Wise, Year wise sales
Give Product wise Data for 2008 Only

EduPristine | (Confidential)

23

Filtering for Particular Rows/ Columns

Use Example I
Calculate Region Wise, Year wise sales
Give Product wise Data for 2008 Only
Get Data only for Delhi

EduPristine | (Confidential)

24

Other Customization Options

Selecting the entire PivotTable: Choose Options, Select, Entire PivotTable
Selecting PivotTable items: Select the entire PivotTable, then choose Options, Select. In the list,
click the PivotTable element you want to select: Labels and Values, Values, or Labels
Formatting the PivotTable: Choose the Design tab and then click a style in the PivotTable Styles
gallery
Sorting the PivotTable: Click any label in either the row field or the column field, choose the Options
tab, and then click either Sort A to Z or Sort Z to A. (If the field contains dates, click Sort Oldest to
Newest or Sort Newest to Oldest, instead.)
Refreshing PivotTable data: Choose Options and then click the top half of the Refresh button
Filtering the PivotTable: Click-and-drag a field to the Report Filter area, drop down the report filter
list, and then click an item in the list

EduPristine | (Confidential)

25

Give the average sales in regions

Use Example I
Calculate Region Wise, Year
wise sales
Give Product wise Data for
2008 Only
Get Data only for Delhi
Instead of Total, give the
average sales

EduPristine | (Confidential)

26

Changing the Pivot Table

Click any cell in the PivotTables
data area
Choose Formulas, Calculated Field
Excel displays the Insert
Calculated Field dialog box
Use the Name text box to enter a
name for the calculated item
Use the Formula text box to enter
the formula you want to use for the
calculated item

Use Example I
Calculate Region Wise, Year
wise sales
Give Product wise Data for 2008
Only
Get Data only for Delhi
Instead of Total, give the
average sales
Calculate the forecast for sales
in 2009

EduPristine | (Confidential)

27

Errors in Functions
Error

Meaning

# DIV/ 0!

Divide by Zero Error

Generally seen when trying to divide by empty cell

# NAME?

Function name not found error

Generally seen in misspelling of function

# N/A

Data Not available

Quite often seen Vlookup cannot find data

# REF!

Invalid Cell reference

Typically when a cell is referenced, which has been deleted

# VALUE!

Argument/ Operand of the wrong type

# NUM!

Problem in value
Typically when a +ve number is expected, and ve number is given as argument

Important to understand what kind of error can occur and handle it properly

EduPristine | (Confidential)

28

Use ISERROR() to Handle Errors

Excel 2003 has ISERROR() to determine, if there are any exceptions in the formulas
Handle exception and exit the error gracefully

Excel 2007 also has an IFERROR() function to handle errors

Use Worksheet E to handle the errors and display Not Defaulted

EduPristine | (Confidential)

29

Agenda

Introduction and context

Understanding an integrated financial Model
Efficiently using excel preparation for modeling
Creating a template for integrated model

EduPristine | (Confidential)

30

Creating the template for financial modeling

Matrix Integrity across sheets and tower model for within the sheet

Tower Model within sheet

Reduce the size of the first 1-2 columns to create
the headings
Ctrl + arrow keys to move faster from 1 section to
another

Larger column width to put in the names and text

information

Matrix integrity to be maintained across multiple

sheets
Eases copy paste and faster dragging of formulas
Reduces chances of errors in writing the formulas
linked across sheets

EduPristine | (Confidential)

31

Freeze panes at the right spots to ease navigation in the model

The years (indicating Actual and Projected) are put in one of the top row
Rows and columns are frozen at the intersection of the left hand information column and the years
row at the top
Hint: Shortcut Alt + w + f

EduPristine | (Confidential)

32