Excel For Financial Modeling

Excel for Financial Modeling
© EduPristine – www.edupristine.com
© EduPristine Excel Tips
Agenda
▪ Introduction and Context
▪ Efficiently using excel – preparation for modeling
▪ Creating a template for integrated model
▪ Case: Formatting the data, summarizing and creating a presentable
format
© EduPristine Excel Tips Post your Queries at our Forum 1

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

Agenda
▪ Case: Formatting the data, summarizing and creating a presentable
format

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

Lets take a moment to understand components of Excel
Toolbar
Formula Bar Cells
Worksheet
Name
Excel 2013 Interface
Most of the features are backward compatible

… Lets take a moment to understand components of excel
Most of the features are backward compatible
Excel 2013 Interface

Moving across toolbars in Excel 2007, 2010, 2013 and 2016
Pressing the “ALT” key automatically shows the options

Agenda
▪ Case: Formatting the data, summarizing and creating a presentable format

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

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

Agenda
▪ Case: Formatting the data, summarizing and creating a presentable format

Case: Formatting the data, summarizing and creating a presentable
format
▪ Ramanov, a financial analyst, works with Barclays. His manager gave him a data file which was
imported from the database. This file has the following information:
• Product Name
• Sales Price
• Cost Price
• Vendor Name
• Revenue
▪ Ramanov has never played around with such kind of data, so he is getting problem in following
activities:
• Formatting the Data
• Summarizing
• Finally, converting into a presentable format
Now, suppose he approached you and asked your help in preparing the report. Help Ramanov in
performing the above tasks.

1. Formatting the Data

Case: Formatting the Data
▪ Before we summarize the data or present it to the manager, we need to format the .csv in
desired format so that we can apply excel formulas to summarize and present the same. For the
same, we need to perform the following:
• Space fitting the data
• Formatting the imported file with text to columns
• Getting the relevant information from string of data using Left, Trim
• Using referencing frame work (usage of $) to effectively use formulas
• Converting text to numerical using istext, isnumber, and text to columns
Start Using Data_ERP.csv

Formatting the Data
▪ Step 1:
• For formatting open the imported file and do the “space fitting”
➢ Lets open the Data _ERP.csv file
➢ Save as .xlsx (Excel format)
➢ Remove gridlines with “Alt”+ ”W”+ ”V”+ ”G”
➢ To Autofit data, select the Column A, B & C and press “Alt” + “O” + “C”+ “A”
Hint: Start Using Data_ERP_1

Cont.…
▪ Step 2:
• As you can see the image – Product Name, Sales Price and Cost is given in the same column (column A).
• So the first task is to take the numeric value (example: 250) in the separate column.
➢ Insert a new column next to the data that needs to be separated
❖ To select a column: “Ctrl” + “Spacebar”
❖ To add a new column: “Ctrl” + “+” or “Ctrl” + “Shift” + “=“

Cont.…
• Select the subject column (column A) and

use the “Text to Column” tab to separate Select the 2
column 3
the content of one excel cell into separate
column.
1
➢ Pressing “Text to Column” tab a “Convert
Text to Columns Wizard” will open, select
the “Delimited” option and move to the
“Next” part of the convert wizard.
4
➢ In the Next part select “Other” and provide
“Colon” (:) symbol in the blank box and
press the “Finish” button.
5

Cont.…
▪ Step 3:
• We need to take the Product information in a separate column and for that need to write a logic.
➢ We need to pick Product name as “Product XX” if it is written in the column B or just the upper cell name
• Copy the cell which has logic and select the cells in which you want the logic to be followed and press
enter.

Cont.…
▪ Step 4:
• We need sales price and cost in separate columns
➢ For sales price and cost, we need to extract the number portion form the string. We can extract a part from a
string with the help fo “LEFT”, “RIGHT” or “MID”
• Use reference frame work ($), so that we can copy the same formula in the next column

Cont.…
▪ Step 5:
• Copy the Product Name, Sales Price and cost column and paste it as value.
➢ To paste as value press Alt + E+ S + V

Cont.…
▪ If you notice, Sales price and cost are written as text

▪ To convert from text to number, we can follow following steps:
• Select one column, lets start with sales price
• Go to Text to columns
• Just press Finish, now sales price column is numerical
• Repeat the same for next column

Cont.…
▪ Step 6: 1
• Change the name of the column A from “Product Name” to “Date”
• Filter the data and delete the unwanted text .
➢ To filter press - Ctrl + Shift + L
• Select “Items” (as shown in the image) in “Column A” which you
want to remove (which are not dates)
• As shown in the image (below) excel will sort the data that needs to
be deleted.

Cont.…
▪ Step 7:
• Select the sorted data
• Delete the selected visible cells
➢ To delete – press Ctrl –
• Once the data in visible cells is deleted, remove the

“Filter”
➢ To remove Filter – press “Crtl + Shift + L”
Excel
image
after
deleting
&
removing
filter

Cont.…
▪ Step 8:
• Delete the “Column B,” as it contains no data.
➢ To delete select the entire column, right click and to delete –
press Ctrl –
Blank
Select Column
Blank
Column
Final View of Imported

Data in Excel

Summarizing Data

Summarizing Data using Pivot Tables
▪ We have formatted the file in desired format, now we will help Ramanov in achieving following
summarization items
• Find out Quarterly revenue numbers
• Find out Quarterly – Product wise revenue numbers

Summarizing Data using Pivot Tables
▪ Step 1:
• Select the sorted data
• Press Alt + D + P, a Pivot Table Wizard will open, select “Microsoft Excel list or database” and “pivot Table”
and click the “Finish” button.
• Doing this, a pivot table filed list will appear in excel sheet.

Cont…
▪ In excel 2016, if data is with dates, then excel pivot table groups the data “Month wise”
▪ You can see there is a new field with name “Months”
▪ Uncheck “Months”, if you don’t want to deal with monthly data

Cont.…
▪ Step 2: Pivot Table

• If you are using excel 2016, once you Created in
uncheck “Months” field, you will see Excel
“Date” and “Revenue” as the fields in
Rows and Values section respectively
• If you are using an excel version
before 2016, then you drag “Date” to
Row Labels & “Revenue” to Value Box
1 2

Cont.…
▪ Step 3:
• Group the Data (say grouping monthly, quarterly or yearly sales).
➢ To Group – “Right Click” on the pivot table and select the “Group”
option.
➢ In Grouping window, select monthly, quarterly or yearly option to sort 2
the data as required.
• To Ungroup – “Right Click” on the pivot table and select the
“Ungroup” option.

Grouping Data Product wise in Pivot
▪ Lets group data Product Wise
• Create a new pivot table by dragging “Product Name” in “Row Labels” , “Date” in “Column Label” and
“Revenue” in “Values” box
• To Group – “Right Click” on the pivot table and select the “Group” option
• In Grouping window, select monthly, quarterly or yearly option to sort the data as required.
5
6
3 4
1 2

Presentable Format

Presenting Data
▪ We have created pivot tables which is helping us to summarize data at ease. Now, we need to
apply functions which will help us in solving following Ramanov’s problems:
• As revenue numbers are large, Ramanov’s manager wants to see the same in ‘000 units
• Manager also want the Quarterly – Product wise share in %age format to understand the product revenue
share
• Manager warned Ramanov that the data source will change every day, so he should make the model
flexible. Show Ramanov how can we update the data source
• Creating slicer to use during the presentation so that it is easy to summarize on the basis of different
combinations
© EduPristine Excel Tips Post your Queries at our Forum

Using Calculated Field in PivotTable
▪ To convert Revenue numbers in the pivot table to be displayed in thousand

• In the PivotTable Tools, Click on the “Analyze” tab, select “Fields, Items & Sets” and then click on the
“Calculated Field…”
Put the cursors on pivot table
Pivot Table
1
showing
product wise
quarterly
data.
2
3

Cont.…
• An “Insert Calculated Field” window will open up.

➢ Fill the “Name” option (since we need the sales number in thousand, we name it as “Revenue Thousand.”
➢ Fill the “Formula” option (since we need sales figure in thousand set the formula as Revenue/1000)
❖ Go to “Fields” double click on the “Revenue” option and divide it by 1000.

Cont.…
• Pressing “OK”, it will take you to the excel

sheet.
• As you can see in the image, “Revenue
Thousand” gets added into the “Pivot Table
Field List” and in the “Values” box.
• Now in the Value box there are two items
(Revenue and Revenue Thousand), remove
the “Revenue” item from the box by dragging
it back to the “choose fields” option or by
just removing “Right Tick” from revenue
option.

Using Value Field Setting in Pivot:
• Now to remove the decimals

➢1 Left Click on the pivot table and select “Value Field This is how pivot table will look like.
Settings”
➢ In Value Field Setting, click on the “Number
Format,” a Format Cell window will open, in
category option select “Number,” right tick on the
“Use 1000 Separators” and make “Decimal places”
“0.”
➢ After selecting the all the required option press
“OK”
4 z 6
z 5
2
3 7

Converting Numbers into %age in Pivot:
▪ Now to convert the data in percentage (%age) form in the

pivot table.
• To see %age contribution of each product compare to column
grand total.
➢ First click on the “Sum of Revenue Thousand,” select “Value Field
Settings”
➢ In Value Field Settings select “Show Value As” option and from the 2
drop down list select “% of Column Total” and press “OK”
1
• Note: Even you can see product contribution as %age of grand
total and quarterly difference in sale growth rate using the “Value
Field Settings” option.
3
4

Update data source
▪ To change the data source, you need to update the pivot

table. 1
• In Pivot Table Tools, select “Analyze”– “Change Data Source” –
“Change Data Source.”
2
• A “Change Pivot Table Data Source” window appears, in that select
the new table or range in the “Table or Range” option and press
“OK.”
• Doing this existing pivot table will get updated without creating
new pivot for changed data base.

Adding Slicer along with Pivot Table
▪ Slicer are visual controls that allow you to quickly and easily filter 1
your data in an interactive way in the pivot table. Now, to add
slicer along with the pivot table:
• In Pivot Table Tools, select “Analyze” – “Insert Slicer” – “Insert Slicer” 2
• An “Insert Slicer” window will appear, “Right Tick” on the items for
which you need slicer and press “OK”
Pivot
with
Slicer
3

Removing Slicer
1
▪ To remove Slicer, “Right Click” on the slicer and select “Remove……” option.

Presenting Data – 2
▪ Ramanov’s manager has also asked for following tasks:

• Make the model in such a manner that the new information can easily be added to the existing data
• Calculate the profit per transaction
• Add the pivot column to Pivot Data source
• Every year, 4th July is a holiday nation wide and all of the vendors don’t sell on this day. Check the data to
find out if despite the rule, anyone is selling on this day
• Summarize the Revenue, Count, and Average Revenue (rounded) as per Vendor Name and Product wise
• Extract “Total Revenue” for a combination of “Product Name” and “Vendor Name” by creating drop-down
list for “Product Name”
• Create a formula in such a manner that one can extract the output with a combination of “Revenue/
Count/ Average Revenue”, “Product Name” and “Vendor Name”
• Identify (to present) the products/ days on which company is paying more than $225
• Identify (to present) top 10 profit making days out of the given database
• Identify (to present) sales contribution of each day in a bird eye view and in a pictorial view
• Identify (to present) which days has performed well and which has not

Converting Data into Table Format
▪ In the previous slides we have shown how to format the imported files as per your requirement
▪ Now we will see how to convert the formatted data into tabular format in excel which in turn
will make our lives easier.
• Step 1:
➢ Select the entire data that needs to be converted in table format.

Cont.…
• Step 2:
➢ Under “Home” tab click on the “Format as Table” option and select any of the given table style.
➢ A “Format As Table” window will open, which select the area where the data for your table is located, select
“My table has header” option and press “OK.”
1
2
Selected
Data
converted
into Table
Format

Creating Pivot Table
▪ You can easily prepare pivot table from data into tabular format.
▪ The main advantage is that as new rows/columns are added in the table, the pivot table filed list
can be updated by refreshing the existing pivot table to include the new fields. Therefore no
need of creating new pivot table
2 because new data has been added.
• Select the table, press “Alt + D + P,” a pivot table window will open, select “Next” option and rename the
Range as “Table 2,” press “Finish”
1
Select the
Table
4
5
3
46

Adding a new column
▪ You can easily add columns/rows in the table.

• Add a new column called as “Profit” in the existing table.
• The procedure is…
➢ Add a heading in column “G”: “Profit and press enter. A new column will be added to the table
➢ Apply the formula for profit per unit and as you press “Enter”, the whole column will populate automatically

Refresh the data source
▪ Now a pivot table has been created

• “Refresh” the pivot table by pressing the refresh
button and “Profit” will start showing in the field list
“Profit”
included in
the “Filed
List”

Looking up for relevant information
▪ We need to find out if there is any entry on 4th Jul, use VLOOKUP.
▪ When you use lookup functions (HLOOKUP & VLOOKUP), and select the table as array, it will be
named as “Table 1” or “Table 2”. So if your table gets extended (with new rows/column) the
array in the lookup functions will be taken care automatically, that is, the table format makes the
lookup function very flexible.

Summarizing Revenue against Product Name/ Vendor Name
▪ We need to add revenue with a condition: specific “Product Name” and “Vendor Name”
▪ If we have sum with one condition – use SUMIF, but here we have two conditions (more than
one) – we will use SUMIFS
▪ Same will be applicable for count, and average with two conditions

Summarizing Revenue against Product Name/ Vendor Name
▪ In case of average the numbers can be unrounded (as shown below)

▪ Also, there is one error – because in that case we have zero count (0/0)
▪ To round the numbers, we will use ROUND function

▪ To avoid any kind of error, we will use IFERROR function

Creating Drop-down button
▪ Creating a drop-down
▪ Selecting list from the “Allow”
▪ Selecting source of the list
▪ Press OK

Extracting information with two dimensions
▪ VLOOKUP and HLOOKUP helps in looking value with one dimension

▪ For two dimensions, we can use INDEX along with MATCH
▪ MATCH helps in finding the position of a value within an array, so it will help in finding out the
row and column number for the “Vendor Name” and “Product Name” respectively
▪ INDEX extracts the value in a given cell (combination of row and column number) within a given
array

Extracting information with three dimensions
▪ INDEX helps even when we have 3 dimensions

▪ Third dimension is the Area selection – we have 3 different arrays to choose from
▪ To make things easy and interesting, create “Name Ranges” – Give names to these different
arrays
▪ Go to “Name Manager”
▪ Create “New” name
▪ Give “Name” and “Reference”
▪ Using “Name Ranges” in the INDEX formula with 3 dimensions

Converting Table Format into Range
▪ Suppose now, you want to stop working with your data in a table without losing any table style
formatting that you applied.
• So for this you need to convert the table to a regular range of data on the worksheet.
• The procedure is….
➢ Click anywhere in the table, this displays the Table Tools, adding the ”Design” tab.
➢ On the Design tab, click ”Convert to Range” option.
Click on 2
the
“Table”
3

Conditional Formatting
▪ To colour the product cost for which you paid

greater than $225. 1
• Select the entire cost column, in the “Home” tab 2
click on the “Conditional Formatting”– select 3 4
“Highlight Cells Rules” and “Greater Than” option
from the drop down list.
• A “Greater Than” window will open, in that, in
the “Format cells that are Greater than” option
put “225” and choose the colour from the drop
box.
Highlighted
the cost >225 5 6
7

Cont.…
▪ To colour the top 10 products that earned the highest 1

profit
• Select the entire Profit column, in the “Home” tab click 2
on the “Conditional Formatting”– select “Top/Bottom
Rules” and “Top 10 Items” option from the drop down 3 4
list.
• A “Top 10 Items” window will open, in that, in the
“Format cells that are Greater than” option put “10” and
choose the colour from the drop box.
Highlighted the
Top 10
profitable 5 6
products
7

Cont.…
▪ To know sales contribution of each product in a bird eye view.

• Again you can use conditional formatting.
➢ Select the entire sales column, in the “Home” tab click on the “Conditional Formatting”– select “Data Bars”
and any one of the “Fill” option from the list.
3
4

Cont.…
▪ To know the sales strength of all products in a pictorial view.

• Again you can use conditional formatting – Icon Sets option.
➢ Select the entire sales column, in the “Home” tab click on the “Conditional Formatting”– select “Icon Sets”
and in Ratings select the “Signal Strength” option.
❖ So it will show maximum signal strength for product having highest sales and vice-versa.
1
2

Cont.…
▪ To know which all products has performed well and which has not.
• Again you can use conditional formatting – colour scale option.
➢ Select the entire sales column, in the “Home” tab click on the “Conditional Formatting”– select “Color Scales”
and any one of the “Fill” option from the list.
❖ So it will show products that performed well in “Dark Green,” average performance in “Yellow” and worst
performance in “Red.”
1
3 4

Thank You!
[email protected]
www.edupristine.com
© EduPristine – www.edupristine.com
© EduPristine Excel Tips

Excel For Financial Modeling

Uploaded by

Copyright:

Available Formats

Excel For Financial Modeling

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel For Financial Modeling

Uploaded by

Copyright:

Available Formats

Excel for Financial Modeling

© EduPristine Excel Tips Post your Queries at our Forum 1

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

▪ Functions and a range of features

© EduPristine Excel Tips Post your Queries at our Forum 2

© EduPristine Excel Tips Post your Queries at our Forum 3

© EduPristine Excel Tips Post your Queries at our Forum 4

▪ SPEED is the key in modeling

© EduPristine Excel Tips Post your Queries at our Forum 5

Excel 2013 Interface

Most of the features are backward compatible

© EduPristine Excel Tips Post your Queries at our Forum 6

Most of the features are backward compatible

Excel 2013 Interface

© EduPristine Excel Tips Post your Queries at our Forum 7

Pressing the “ALT” key automatically shows the options

© EduPristine Excel Tips Post your Queries at our Forum 8

© EduPristine Excel Tips Post your Queries at our Forum 9

▪ Tower Model within sheet

© EduPristine Excel Tips Post your Queries at our Forum 10

© EduPristine Excel Tips Post your Queries at our Forum 11

© EduPristine Excel Tips Post your Queries at our Forum 12

© EduPristine Excel Tips Post your Queries at our Forum 13

© EduPristine Excel Tips Post your Queries at our Forum 14

Start Using Data_ERP.csv

Hint: Start Using Data_ERP_1

© EduPristine Excel Tips Post your Queries at our Forum 17

• Select the subject column (column A) and

© EduPristine Excel Tips Post your Queries at our Forum 18

© EduPristine Excel Tips Post your Queries at our Forum 19

© EduPristine Excel Tips Post your Queries at our Forum 20

Hint: Start Using Data_ERP_2

▪ If you notice, Sales price and cost are written as text

© EduPristine Excel Tips Post your Queries at our Forum 22

© EduPristine Excel Tips Post your Queries at our Forum 23

• Once the data in visible cells is deleted, remove the

© EduPristine Excel Tips Post your Queries at our Forum 24

Final View of Imported

© EduPristine Excel Tips Post your Queries at our Forum 25

© EduPristine Excel Tips Post your Queries at our Forum 26

© EduPristine Excel Tips Post your Queries at our Forum 27

Hint: Start Using Data_ERP_3

© EduPristine Excel Tips Post your Queries at our Forum 29

▪ Step 2: Pivot Table

© EduPristine Excel Tips Post your Queries at our Forum 30

© EduPristine Excel Tips Post your Queries at our Forum 31

Hint: Start Using Data_ERP_4

© EduPristine Excel Tips Post your Queries at our Forum 33

© EduPristine Excel Tips Post your Queries at our Forum

▪ To convert Revenue numbers in the pivot table to be displayed in thousand

Hint: Start Using Data_ERP_5

• An “Insert Calculated Field” window will open up.

© EduPristine Excel Tips Post your Queries at our Forum 36

• Pressing “OK”, it will take you to the excel

© EduPristine Excel Tips Post your Queries at our Forum 37

• Now to remove the decimals

© EduPristine Excel Tips Post your Queries at our Forum 38

▪ Now to convert the data in percentage (%age) form in the

Hint: Start Using Data_ERP_6