FINANCIAL MODELLING

(PRACTICAL FILE)
([Link]-212)

INSTITUTE OF INFORMATION TECHNOLOGY AND

MANAGEMENT

BATCH (2020-2023)

SUBMITTED TO SUBMITTED BY

[Link] Aneja Yash Kumar

2. Vikas Bharara 05013788820
Batch (2020-2023)
[Link] (H) IV,M
 FINANCIAL MODELING LAB FILE
Yash Kumar

Institute of Information Technology & Management, New Delhi

(Affiliated to GGSIP University)

INDEX
S. No Topic Date Signature

1 Introduction to MS Excel 25/1/2022

2 Functions in MS Excel 29/1/2022

3 Shortcuts Keys 1/2/2022

4 Conditional Formatting 5/2/2022

5 What if Analysis 8/2/2022

6 Pivot Tables and Charts 12/2/202

7 Solver Tool 15/2/2022

8 Look Up function ,V Lookup Function 19/2/2022

9 H lookup function 22/2/2022

10 Index and Match Function 26/2/2022

11 Macros 5/3/2022

Three Statements Analysis-Components

12 8/3/2022
using Templates

Three Statement Analysis

13 15/3/2022
 Ratio Analysis
 Forecasted/Projected statement

Three Statement Analysis

14 22/3/2022
 Sensitivity and Scenario Analysis

Valuation Approaches : DCF-Free Cash

15 flow to the Firm and to the Equity 26/3/2022
shareholders

2
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Valuation Approaches DCF-Terminal

16 Value and Calculation of Present value 29/3/2022
of Cash flows

Valuation Approaches :Trading

17 Comparables and Transaction 2/4/2022
Analysis
Time Value of Money- Financial
18 5/4/2022
Functions

Techniques of Capital Budgeting-

19 19/4/2022
NPV,IRR and PI

Calculation of Cost of Capital

20  Cost of Debt 26/4/2022
 Cost of Equity

21 Calculation of Beta (CAPM Model) 26/4/2022

22 Calculation of WACC 30/4/2022

23 Forecasting Methods- Moving Averages 30/4/2022

Forecasting Methods-Exponential
24 4/5/2022
Smoothing

25 Forecasting Methods- Trend Analysis 4/5/2022

3
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

INTRODUCTION: EXCEL AS A TOOL IN FINANCIAL MODELLING

WHAT IS EXCEL?
Microsoft excel is a software program produced by Microsoft that allows users to organize,
format, and calculate data with formulas using a spreadsheet system.

In other words, Microsoft excel is a software developed and manufactured by Microsoft

Corporation that allows users to organize, format and calculate data with formulas using a
spreadsheet system broken up by rows and columns.

It features the ability to perform basic calculations, use graphing tools, create pivot tables and
create macros.

Excel permits user to arrange data so as to view various factors from different perspectives.

USES OF EXCEL
Excel is typically used to organize data and perform financial analysis. It is used across all
business functions and at companies from small to large. It also saves a lot of time because of
its functions and features.

Some of the uses of Excel are-

1. Analyzing and storing data- One can analyze larger amount of data to discover trends
with the help of graphs and chart, one can summarize the data and store it in an organized
way. Once the data is stored in a systematic way, it can be used easily for multiple
purposes.
2. Excel tool makes work easier– There are so many tools of Ms-Excel that make our work
extremely easy and save our time as well. There are many wonderful tools for sorting,
filtering and searching which make our work easy. Such tools are pivot tables, tables, etc.
3. Data Recovery & Spreadsheets – The lost data can be recovered in MS-Excel without
much inconvenience. The next important use is that there are spreadsheets in MS-Excel
which also makes your work easy.
4. Mathematical Formulas of MS-Excel make things easier – MS-Excel makes it easier
for us to solve complex mathematical problems in a much simpler way without much
manual effort. Some of the mathematical formulas are finding sum, average, etc.
5. Security- All the files of MS-Excel can be kept password protected through visual basic
programming or directly within the Excel file.
6. Keeps Data combined at one location – MS-Excel can be used to keep all your data at
one location. This will help you in saving your data from getting lost.
7. Helps businessmen in developing future strategy – Data can be presented in the form
of charts and graphs so it can help in identifying different trends. MS-Excel simplifies
this task and helps businessmen grow and maximize profits through the same.
4
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

8. Manage Expenses – MS-Excel helps in managing expenses of the businessmen. He can

write his monthly income as well as expenses in excel tables and then he can get to know
that how much he is spending & he can thus, control his expenses accordingly.

BASIC TERMS IN EXCEL-

1) Formulas: In excel, a formula is an expression that operates on values in a range of
cells or a cell.
E.g. A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.
2) Functions: Functions are pre-defined formulas in excel. They eliminate laborious
manual entry of formulas while giving them human friendly names.
E.g. =SUM(A1:A3). The function sums all the values from A1 to A3.

IMPORTANT COMPONENTS OF EXCEL-

1. Workbook: A workbook is a separate file just like every other application has. Each
workbook contains one or more worksheets. In other words, workbook is a collection of
multiple worksheets or can be a single worksheet.
2. Worksheet: a worksheet is made up of individual cells which can contain a value, a
formula, or text. Each worksheet in a workbook is accessible by clicking the tab at the
bottom of the workbook window.
3. Cell: A cell is a smallest but most powerful part of a spreadsheet. Every cell is identified
by its cell address; cell address contains its column number and row number.
 If a cell is on the 11th row and on column A , then its address is A11.

WINDOWS COMPONENTS:
 Column: Column is a vertical set of cells. A single worksheet contains 16384 total
columns
 Row: A row is a horizontal set of cells. A single worksheet contains 1048576 total rows.
 Fill handle: It is a small dot present on the lower right corner of the active cell. Its helps
you fill numerical values , text series, insert ranges, insert serial numbers, etc.
 Address bar: IT is the small input bar at the left side of the window. It shows the address
of the active cell. If you have selected more than one cell, then it will show the address
of the first cell in the range.
 Formula bar: Formula bar is an input bar below the ribbon. It show the content of the
active cell and you can also use it to enter the formula in the cell
 Title bar: The title bar shows the name of the workbook followed by the application
name.
 File name: The file name is a simple menu as like all other application. It contains
options like save, save as, open, print, etc.
 Ribbon tab: Ribbon tabs are the bunch of specific option group which further contains
options.

5
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

 Worksheet tab: This tab shows that all the worksheets which all the worksheets which
are present in the workbook. By default, one see three worksheet in new workbook with
name of sheet 1 and so on.

Fig: Components of Excel

6
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

FUNCTIONS IN MS-EXCEL:
1. Sum: It returns the sum of numeric values in a cell.
Syntax =SUM(number 1,[number 2]……)

Fig: Applying sum function

2. Count: It returns the count of numeric values in a cell. In simple words, it will only return
the count of cells which have numbers and ignore the other entire cell.
Syntax =COUNT(VALUE 1, [VALUE 2]……)

Fig: Applying count function

3. Average: It returns the average of numeric values in a cell. In simple words, it will first
sum the numbers and then divide that sum by the count of numbers.
Syntax =AVERAGE(number 1,number 2……)

7
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying average function

4. Time: It return a valid time serial number as per excel time format. In simple words, one
can create a valid time value by using a number for hours, minutes, and seconds.
Syntax: =TIME(hour, minute, second)

Fig: Applying time function

5. Date: It returns a valid date serial number as per excels time format. In simple words, it
returns a date on the basis of a year, month, and day you provide.
Syntax: =DATE(year, month, day)

Fig: Applying Date Function

6. Left: This function extracts specific characters from the cell/string starting from the left
(start).
8
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

It returns the specific number of characters in the result.

Syntax: =LEFT( text, num_chars)

Fig: Applying left function

7. Right: This function extracts specific characters from the cell/string starting from the
right (last).
Syntax: =RIGHT( text, num_chars)

Fig: Applying right function

8. V Look Up: It looks up for a value in a column and can return that value or a value from
the correspondent columns using the same row number.
Syntax: =VLOOKUP ( lookup_value, table_array, col_index_num, [range_lookup])

Fig: Applying V look up function

9
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

9. If: This function returns a value when the specific condition is true and returns another
value if the condition is false.
Syntax: =IF ( logical_test, value_if_true, value_if_false)

Fig: Applying if function

10. Now: It returns the current date and time in the cell where you insert it using the
system’s settings.
Syntax: =NOW ()
There is no argument in NOW function, but you have to enter empty parentheses.

Fig: Applying now function

11. Len

Len function in Excel helps you to know the length of a string that is number of
characters in a string. Syntax = LEN(text)

10
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

12. Mid

Mid function in Excel is used to extract the characters from the middle of a
string. Syntax = MID(text, start_char, num_chars)

13. Rept

Rept function in Excel is used when you want a certain text to be

repeated certain number of times. Syntax =REPT(Text, number_times)

Fig 8: Applying Rept Function

11
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

14. Trim

Trim function in Excel removes the unnecessary spaces from a particular

string.

Syntax =TRIM(Text )

Fig 9: Applying Trim Function

15. Upper

Upper function in Excel converts the text into Upper case from
lower case. Syntax =UPPER(Text )

Fig 10: Applying Upper Function

16. Substitute

Substitute function in Excel helps to replace existing text with a new text
in a particular string. Syntax =SUBSTITUTE(text, old_text, new_text,
instance number)

12
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig 11: Applying Substitute Function

17. Concatenate

Concatenate function in Excel helps to join the text of two or more cells.

Syntax =CONCATENATE(text1, text2….)

Fig 12: Applying Concatenate Function

SHORT-CUT KEYS IN EXCEL

Microsoft Excel Shortcut What does Excel Shortcuts do?
Ctrl+A Select all contents of a worksheet.
Ctrl+B Bold all cells in the highlighted section.
Ctrl+D Fill Content of the selected cell on top to
bottom. CTRL + D (i.e. Ctrl+ Down)

Ctrl+F Search current sheet.

Ctrl+G Go to a certain area.
Ctrl+H Find and replace.
Ctrl+I Puts italics on all cells in the highlighted
section.

13
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Ctrl+K Inserts a hyperlink.

Ctrl+P Print the current sheet.
Ctrl+R Fill Right.
Ctrl+S Saves the open worksheet.
Ctrl+U Underlines all cells in the highlighted
section.

Ctrl+V Pastes everything copied onto

the clipboard.

Ctrl+W Closes the current workbook.

Ctrl+X Cuts all cells within the highlighted
section.

Ctrl+Y Repeats the last entry.

Ctrl+Z Undo the last action.
Ctrl+1 Changes the format of the selected cells.
Ctrl+2 Bolds all cells in the highlighted section.
Ctrl+3 Puts italics all cells in the highlighted
section.

Ctrl+4 Underlines all cells in highlighted

section.

Ctrl+5 Puts a strikethrough all cells in the

highlighted section.

Ctrl+6 Shows or hides objects.

Ctrl+7 Shows or hides the toolbar.
Ctrl+8 Toggles the outline symbols.
Ctrl+9 Hides rows.
Ctrl+0 Hides columns.
Alt+F1 Inserts a chart.
Alt+F2 Save as.
Alt+F4 Exits Excel.
Alt+F8 Opens the macro dialog box.
Alt+F11 Opens the Visual Basic editor.
Alt+Shift+F1 Creates a new worksheet.
Alt+Shift+F2 Saves the current worksheet.

14
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

CONDITIONAL FORMATTING
Conditional formatting is a powerful feature in excel that is often underutilized. By using
conditional formatting, you can tell Excel to highlight portions of your data that meet any given
condition.

o Conditional Formatting allows you to change the formatting (font color, border, shading
of the cells based on the values in it).
o You specify a condition for the values in the cell, and if the condition is satisfied then
automatically the formatting of the cells changes.
o This feature would be very useful if you were working on a profit and loss spreadsheet,
or a temperature variance spreadsheet, or a mark sheet.
PATH: Home tab → Conditional Formatting

E.g. Highlighting top 10 customers, below average performing employees, etc.

Fig: Conditional Formatting

CONDITIONAL FORMATTING MENU:

This menu allows you to select the type of Excel Conditional Formatting that you want to apply
to your cell(s). This can be either-

o Highlight Cell Rules: Apply formatting to cells that satisfy a specific condition.

Fig: Highlight Cell Rules Menu

15
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

o Top/ Bottom Rules: Apply formatting to cells that satisfy a statistical condition in
relation to other cells in the range.
CONTENTS: above average, top 10%, below average, bottom 10 items.

Fig: Top/Bottom Rules Menu

o Data Bars/ Color Scales/ Icon Sets: Apply formatting to all the cells in the range,
depending on their value in relation to one another.

Fig 15: Data Bars/ Color Scales/ Icon sets Menu

o New Rule: Allows you to specify more complex rules, such as rules that depend on the
result of a formula.
16
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Thus, conditional formatting allows you to visually analyze your data, based on a large
number of condition types.
 Greater than, Less than, Between
 Above/ Below average
 Top/ Bottom 10%
 Top/ Bottom 10
 Duplicates/ Unique
 Dates- dynamic or a fix date range
 Text Containing

Fig 16: New Rule in Excel

Examples of conditional formatting includes-

 Putting all negative numbers in a list of data in red.

 Creating data bars to give a visual representation of the largest and the smallest items in
your data set.
 Using icons to indicate the highest and the lowest values in a list of data.

QUES. Create a student’s record and use different conditional formatting options on the data
so created.
STEPS:

Create the data in the Excel worksheet.

[Link]. NAMES M1 M2 M3 M4
1 Rekha 24 45 50 78
2 Rahil 78 58 40 55
3 Reeta 45 67 60 44
4 Ritu 46 76 70 66
5 Ramit 57 90 80 88
6 Yash 76 88 90 67

17
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Highlight Cell Rules

1. Go to the Home Tab → Conditional Formatting → Highlight Cell Rules → Greater than.

Fig: Applying highlight cell rules on data

2. A greater than pop up window will appear. Apply the condition greater than 80.

Fig: Applying condition

3. All the cells containing value greater than 80 will be highlighted.

Fig: Results

Data Bars:

1. Go to the Home Tab → Conditional Formatting → Data Bars.

18
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying Data Bars in Conditional Formatting

2. Bars will be formed as per the marks or number contained in the cell.

Fig: Results
Color Scales:

1. Go to the Home Tab → Conditional Formatting → Color Scales.

Fig: Applying Color Scales in Conditional Formatting

2. Select the color scale you want to apply. The data will be formatted in the selected
criteria. Highest marks with the dark colors whereas lower marks with comparatively
lower colors.

19
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Results
Icon Sets:
1. Go to the Home Tab → Conditional Formatting → Icon Sets.

Fig: Applying Icon Sets in conditional Formatting

2. Select the criteria you want to apply. Data will be formatted in the selected criteria.
Highest marks represented by color green and lowest by red.

Fig: Result

WHAT-IF-ANALYSIS

What-if-analysis is the process of changing the values in cells to see how those changes will
affect the outcome of formulas on the worksheet.

20
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

You can use several different sets of values in one or more formulas to explore all the various
results.

What-if-analysis is useful in many situations while doing data analysis. For example-

o You can propose different budgets based on revenue.

o You can predict the future values based on the given historical values.
o If you are expecting a certain value as the result from a formula, you can find different
sets of input values that produce the desired result.

Excel provides you with the following what-if-analysis tools that can be used based on your
data analysis needs-
o Data Tables
o Scenario Manager
o Goal Seek

 Data Tables and Scenarios take sets of input values and project forward to determine
possible results.
 Goal seek differs from Data Tables and scenarios in the way that it takes a results and
projects backwards to determine possible input values that produce that result.

DATA TABLES: A data table is a range of cells in which you can change values in some of
the cells and come up with different answers to a problem.

For example-

You might want to know how much loan you can afford for a home by analyzing different loan
amounts and interest rates. You can put these different values along with the PMT function in
the Data Table and get the desired result.

o A data table works only with one or two variables, but it can accept many different values
for those variables.

QUES. Assume you own a book store and have 100 books in storage. You sell a certain % for
the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the
highest price, calculate the total profit.
Also, use data tables to calculate
o The total profit if you sell 60% for the highest price, 70% for the highest price, etc.
o The total profit for the different combinations of ‘unit profit (highest price)’ and ‘% sold
for the highest price’.
21
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

STEPS:

CALCULATION OF TOTAL PROFIT

1. Calculate the number of books to be sold for the highest price.

Fig: Calculation of number of books for highest price sale

2. Calculate the number of books to be sold for the lowest price.

Fig: Calculation of number of books for lowest price sale

3. Total profit is calculated by multiplying number of books sold with the unit profit on one
book.

22
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Calculation of total profit

ONE VARIABLE DATA TABLE

1. Select cell B11 and type =C9 referring to total profit cell.
2. Type the different percentages in column A.
3. Select the range A11 to B16.

Fig: Performing one variable data test

On the Data tab, in the data tools group, click What-If Analysis → Data Tables.

Fig: Applying Data tables with one variable

4. Click in the ‘Column input cell’ box (the percentages are in a column) and select cell C3.
23
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying Data tables with one variable

5. Click Ok.
6. We will get a total profit if we increase the percentage of books sold from 60% to 70%
and so on.
RESULT:

Fig: Results of data table in one variable

CONCLUSION: If you sell 60% for the highest price, you obtain a total profit of $3800, if you
sell 70% for the highest price; you obtain a total profit of $4100, etc.

TWO VARIABLE DATA TABLE

1. Select cell A11 and type =C9 (refer to the total profit cell).
2. Type the different unit profits (highest price) in row 12.
3. Type the different percentages in column A.
4. Select the range A11:D16.

24
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig 33: Applying Two variable data table

5. On the Data tab, in the data tools group, click What-If Analysis → Data Tables.

6. Click in the ‘Row input cell’ box (the unit profits are in a row) and select cell C6 and
Click in the ‘Column input cell’ box (the percentages are in a column) and select cell
C3.

Fig 34: Applying Two variable data table

7. Click OK. We will get a total profit if we increase the percentage of books sold from
60% to 70% and increase the unit profit from $50 to $60 and so on.

RESULT:

25
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig 35: Results of data tables in two variables.

CONCLUSION: If you sell 60% for the highest price, at a unit profit of $50, you obtain a total
profit of $3800, if you sell 80% for the highest price, at a unit profit of $60; you obtain a total
profit of $5200, etc.

SCENARIO MANAGER: A scenario manager is a set of value that Excel saves and
substitutes automatically in cells on a worksheet.

The key features are-

o You can create and save different groups of values on a worksheet and then switch to any
of these new scenarios to view different results.
o A scenario can have multiple variables, but it can accommodate only up to 32 values.
o You can also create a scenario summary report, which combines all the scenarios on one
worksheet.
For example-
You can create several different budget scenarios that compare various possible income
levels and expenses, and then create a report that lets you compare the scenario side-by-side.
o Scenario manager is a dialogue box that allows you to save the values as a scenario and
name the scenario.

QUES. Assume you own a book store and have 100 books in storage. You sell a certain % for
the highest price of $50 and a certain % for the lower price of $20. But what if you sell 70%
for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100% ?
Use scenario Manager to find out.

STEPS:

1. On the Data tab → What-If Analysis → Scenario Manager. The Scenario Manager Dialog
box appears.

26
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying Scenario Manager

2. Add a scenario by clicking on Add.

Fig: Applying Scenario Manager

3. Type a name (60% highest), select cell C3 (% sold for the highest price) for the Changing
cells and click on OK.

Fig: Applying Scenario Manager

4. Enter the corresponding value 0.6 and click on OK again.

27
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig 39: Applying Scenario Manager

5. Next, add 4 other scenarios (70%, 80%, 90% and 100%).

Fig: Adding different scenarios

6. Click the Summary button in the Scenario Manager. Next, select cell C9 (total profit) for
the result cell and click on OK.
7. It will show you the different scenarios of change in total profit by changing percentage
of books sold on high price.

Fig: Results in the form of scenario summary

CONCLUSION: If you sell 70% for the highest price, you obtain a total profit of $4100, if you
sell 80% for the highest price; you obtain a total profit of $4400, etc.

28
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

GOAL SEEK: Goal Seek is a what-if-analysis tool that helps you to find the input value that
results in a target value that you want.
Goal seek is useful if you know the result that you want from a formula, but not sure what input
value the formula needs, to get that result.
Then, by varying the input value in the formula, goal seek tries to arrive at a solution for the
input value.

QUES. Assume you own a book store and have 100 books in storage. You sell a certain % for
the highest price of $50 and a certain % for the lower price of $20. What if you want to know
how many books you need to sell for the highest price, to obtain a total profit of exactly $4700?

STEPS:

1. On the Data tab → What-If Analysis → Goal Seek. The Goal Seek dialog box appears.

Fig: Applying Goal Seek

2. Select cell C9 → Click in the ‘To value’ box and type 4700 → Click in the ‘By changing
cell’ box and select cell C3 → Click OK.

Fig: Setting the values required

3. We will get the desired percentage of books sold on highest price to earn a profit of $4700.

29
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Results after applying the goal seek

PIVOT TABLES AND CHARTS

A pivot table is a program tool that allows you to re-organize and summarize selected columns
and rows of the data in a spreadsheet or data base table to obtain a desired report.

In other words, Pivot tables are used to summarize, analyze, explore and present summary data.

A pivot table is especially designed for-

o Querying large amount of data in many user friendly ways.

o Subtotaling and aggregating numeric data, summarizing data by categories and
subcategories, and creating custom calculations and formulas.
o Moving rows to columns or columns to row (or pivoting) to see different summaries of
source data.
o Filtering, sorting, grouping and conditionally formatting the most useful and interesting
subset of data enabling you to focus on just the information you want.

WAYS TO WORK WITH PIVOT TABLES: After you create a pivot table by selecting its
data source, arranging fields in the pivot table’s field list and choosing an initial layout, you
can perform the following tasks as you work with pivot tables.

a. Exploring the data:

o Expand and collapse data and show the underlying details that pertain to the values.
o Sort, filter, and group fields and items.
o Change summary functions, and add custom calculations and formulas.

30
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Exploring data with pivot tables

b. Change the form layout and field arrangement:

o Change the pivot table form: compact, outline or tabular.
o Add, rearrange and remove fields.
o Change the order of fields or items.

Fig: Changing the form layout and field arrangement

c. Change the layouts of columns, rows and subtotals:

o Turn column and row field headers on or off or display or hide blank lines.
o Display subtotals above or below the rows.
o Adjust the column widths on refresh.
o Move a column field to the row area or vice versa.
o Merge or unmerge cells for outer row and column items.

31
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Changing the layouts of columns, rows and subtotals

d. Change the display of blanks and errors:

o Change how errors and empty cells are displayed.
o Change how items and labels without data are shown.
o Display or hide blank rows.

Fig: Changing the display of blanks and errors

e. Change the format:

o Manually or conditionally format cells and ranges.
o Change the overall Pivot table’s format style.
o Change the number format for fields.
32
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Changing the format

DIFFERENCE BETWEEN PIVOT CHARTS AND STANDARD CHARTS:

o Row/ Column Orientation: Unlike a standard chart, you can’t switch the row or column
orientation of a pivot chart by using the select data source dialogue box.
Instead you can pivot the row and column labels of the associated pivot table to achieve
the same effect.
o Chart Types: You can change a pivot chart to any chart type except a xy, stock or bubble
chart.
o Source Data: Standard charts are linked directly to worksheet cells, while pivot charts
are based on their associated pivot table data source. Unlike the standard chart, you
cannot change the data range in a pivot chart’s select data source dialogue box.
o Formatting: Most formatting (including charts elements that you add, layout and style)
is preserved when you refresh a pivot chart. However, trend lines, data labels, error bars
and other changes to data sets are not preserved. Standard charts do not lose this
formatting once it is applied.
o Refreshing Pivot Charts: Any changes made onto the data upon which the pivot table
or pivot chart is based, though is automatically rectified, is not updated immediately and
the phenomenon of refresh needs to be carried out, to update the pivot table or pivot chart
and the generation of a revised pivot table or pivot chart.

ADVANTAGES
1. Effective way to represent the data in a pictorial manner.
2. Makes the process of visualization of data easier.
3. Facilitates data analysis.
4. Used to draw various conclusions and form the basis of statistical development.
5. Efficient means of handling with large unsliced raw data by relating them through
filtering and slicing.

DISADVANTAGES

1. It can be a time consuming venture.

2. There are no automatic updates.
33
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

3. It takes time to learn them.

QUES. Given below is the data about quantity, amount and sales of a particular company.
Evaluate the change in sales during the year using pivot table and chart.

Month Amount Quantity Sales

Jan 24 1200 28800

Feb 25 2400 60000

Mar 26 3600 93600

Apr 27 4800 129600

May 28 6000 168000

June 29 7200 208800

July 30 8400 252000

August 31 9600 297600

September 32 10800 345600

October 33 12000 396000

November 34 13200 448800

December 35 14400 504000

STEPS:

PIVOT TABLE

1. Click any single cell inside the data set.

2. On the Insert tab, in the Tables group, click PivotTable.

34
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Inserting pivot table

The following dialog box appears. Excel automatically selects the data for you. The default
location for a new pivot table is New Worksheet. You can change it to existing worksheet.
Click OK.

Fig: Entering the data source

3. Drag the fields to arrange the data in the proper manner.

35
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Pivot Table analysis result

PIVOT CHARTS
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable → Pivot Chart.

The following dialog box appears. Excel automatically selects the data for you. The default
location for a new pivot table is New Worksheet. You can change it to existing worksheet.
Click OK.

Fig: Entering the data source

3. Drag the month’s field to legends field, Sales to values, and quantity to report filter
fields.

36
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Pivot Chart analyzing the Results

37
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

SOLVER TOOL
Solver is Microsoft excel add-in program you can use for what-if Analysis. Use solver to find
an optional (maximum or minimum) value for a formula in one cell –called the objective cell-
subject to constraints, or limits, on the values of other formula cells on a worksheet.

Solver works with a group of cells, called decision variables or simply variable cells that are
used in computing the formulas in the objective and constraint cells.

EXAMPLE:

You can change the amount of your projected advertising budget and see the effect on your
projected profit amount.

Load the Solver Add-In-

1. On the File tab click “Options”.

Fig: Inserting Solver Add-in

2. Under Add-Ins, select Solver Add-In and click on the Go Button.

Fig: Inserting Solver Add-in

3. Check Solver Add-In and click OK.

4. You can find the Solver on the Data tab, in the Analyze group.

38
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Inserting Solver Add-in

QUES.

Supposing, you are the owner of a beauty salon and you are planning on providing a new
service to your clients. For this, you need to buy a new equipment that costs $40,000, which
should be paid by installments within 12 months.

Goal: Calculate the minimal cost per service that will let you pay for the new equipment within
the specified timeframe.

1. On the Data tab, in the Analysis group, click the Solver button.
2. The Solver Parameters window will open where you have to set up the 3 primary
components.

Fig: Applying Solver Function

3. When Solver has finished processing, it will display the Solver Results dialog window,
where you select Keep the Solver Solution and click OK.

39
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying Solver Function

4. In this example, $66.67 appears in cell B5, which is the minimal cost per service that will
let you pay for the new equipment in 12 months, provided there are at least 50 clients per
month.

Fig: Resulted Solver Analysis

40
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

LOOK UP

Use LOOKUP, one of the lookup and reference functions, when you need to look in a single
row or column and find a value from the same position in a second row or column.

For example, let's say you know the part number for an auto part, but you don't know the price.
You can use the LOOKUP function to return the price in cell H2 when you enter the auto part
number in cell H1.

V LOOK UP
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table,
and then returns a value in the same row from another column you specify.

1. Insert the VLOOKUP function shown below.

Fig: Inserting VLOOKUP Function

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Fig: Applying Vlook UP

41
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

When we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the
same, while the relative reference (A2) changes to A3, A4, A5, etc. Visit our page about
the VLOOKUP function for much more information and many examples.

HLOOKUP

Fig: Applying Hlook UP

42
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

INDEX AND MATCH FUNCTION IN EXCEL

MATCH FUNCTION
The MATCH function returns the position of a value in a given range. For example, the MATCH function below looks up the
value 53 in the range B3:B9.

Fig: Applying Match Function

INDEX FUNCTION

The INDEX function below returns a specific value in a one-dimensional range.

Fig: Applying Index Function

The INDEX function returns the 5th value (second argument) in the range E3:E9 (first
argument).
INDEX AND MATCH
Replace the value 5 in the INDEX function (see previous example) with the MATCH function
(see first example) to lookup the salary of ID 53.

43
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Applying Index and Match Function

The MATCH function returns position 5. The INDEX function needs position 5.

44
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

MACROS
A macro is a piece of programming code that runs in excel environment and helps automate
routine tasks.

In other words, an excel macro is an action or a set of actions that you can record, give a name,
save and run as many times as you want and whenever you want.

When you create a macro, you are recording your mouse clicks and keystrokes. When you run
a saved macro, the recorded mouse clicks and keystrokes will be executed in the same sequence
as they are recorded.

RECORDING A MACRO:

1. Go to the View Tab of the ribbon and click the tiny arrow below the macro tab.

2. Click Record Macro.

3. Type in the name of your Macro and click OK to start the recording.

4. Perform the actions in your spreadsheet you want to be turned into a macro.

45
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

5. When you are done, Go the View tab → Macro → Stop Recording.

BENEFITS OF MACRO
1. Macro reduces the possibility of human error that increases with many, repetitive
keystrokes and tasks.
2. Macros reduces the amount of time that must be spent performing basic computing
tasks, freeing users up for more complex problem solving and idea generating
activities.
3. They also make complex computations easier to perform.

46
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

THREE STATEMENT ANALYSIS- COMPONENTS AND TEMPLATES

A 3 statement model links the income statement, balance sheet, and cash flow statement into
one dynamically connected financial model. 3 statement models are the foundation on which
more advanced financial models are built such as discounted cash flow DCF models, merger
models, leveraged buyout LBO models, and various other types of financial models.

COMPONENTS OF FINANCIAL MODEL

A. INCOME STATEMENT
Often, the first place an investor or analyst will look is the income statement. The income
statement shows the performance of the business throughout each period, displaying sales
revenue at the very top. The statement then deducts the cost of goods sold (COGS) to find gross
profit. From there, the gross profit is affected by other operating expenses and income,
depending on the nature of the business, to reach net income at the bottom – “the bottom line”
for the business.

Key features:

 Shows the revenues and expenses of a business

 Expressed over a period of time (i.e. 1 year, 1 quarter, Year-to-Date, etc.)
 Uses accounting principles such as matching and accruals to represent figures (not
presented on a cash basis)
 Used to assess profitability

47
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Income statement template

B. BALANCE SHEET
The balance sheet displays the company’s assets, liabilities, and shareholders’ equity. As
commonly known, assets must equal liabilities plus equity. The asset section begins with cash
and equivalents, which should equal the balance found at the end of the cash flow statement.
The balance sheet then displays the changes in each major account. Net income from the
income statement flows into the balance sheet as a change in retained earnings (adjusted for
payment of dividends).

Key features:

 Shows the financial position of a business

 Expressed as a “snapshot” or point in time (i.e. as at December 31, 2017)
 Has three sections: assets, liabilities, and shareholder’s equity
 Assets = Liabilities + Shareholders Equity

48
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Balance Sheet template

C. CASH FLOW STATEMENT

The cash flow statement then takes net income and adjusts it for any non-cash expenses. Then,
using changes in the balance sheet, usage and receipt of cash is found. The cash flow statement
displays the change in cash per period, as well as the beginning balance and ending balance of
cash.

Key features:

 Shows the increases and decreases in cash

 Expressed over a period of time (i.e. 1 year, 1 quarter, Year-to-Date, etc.)
 Undoes all accounting principles to show pure cash movements
 Has three sections: cash from operations, cash used in investing, and cash from financing
 Shows the net change in cash balance from start to end of the period

49
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Cash Flow Statement Template

SUMMARY COMPARISON

Income
Balance Sheet Cash Flow
Statement
Time Period of time A point in time Period of time

Purpose Profitability Financial Cash movements

position
Measures Revenue, Assets, Increases and
expenses, liabilities, decreases in cash
profitability shareholders'
equity
Starting Revenue Cash balance Net income
Point
Ending Net income Retained Cash balance
Point earnings

50
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

THREE STATEMENT ANALYSES - CASE STUDY

Financial statements are records of a company’s financial condition and activities during a
period of time. Financial statements show the financial performance and strength of a company.
The three core financial statements are the income statement, balance sheet, and cash flow
statement. These three statements are linked together to create the three statement financial
model. Analyzing financial statements can help an analyst assess the profitability and liquidity
of a company. Financial statements are complex. It is best to become familiar with them by
looking at financial statements examples.
Let us have a look at the financial statements of Apollo Tyres Ltd.

1. INTRODUCTION

Apollo Tyres Ltd. came into inception in 1972 and has since been a trusted name in the
business of manufacture and sale of tyres. With our corporate headquarters in Gurgaon,
India, we cater to over a 100 countries across the globe.

At the end of the financial year 2018, the company clocked a turnover of US$ 2.28 billion,
backed by a global workforce of approximately 16,000 employees.

As of March 31, 2018, the company traded in India on the Bombay Stock Exchange and
National Stock Exchange, with 59.68% of shares held by the public, government entities,
banks and financial institutions.

DATE 23-01-2019

INDUSTRY Tyres

SECTOR Public company

TICKER APOLLOTYRE
CURRENT
MP 218.9

YEAR END 31-03-2018

CURRENCY INR

2. COMMOM SIZE STATEMENT OF THE COMPANY

51
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Common Size Statement of the Company

2. COMPARATIVE STATEMENT OF THE COMPANY

Fig: Comparative Statement of the Company

3. CASH FLOW STATEMENT OF THE COMPANY

52
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Cash Flow Statement of the company

4. RATIO ANALYSIS OF THE COMPANY

Fig: Ratio Analyses of the Company

5. BALANCE SHEET OF THE COMPANY

53
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Balance Sheet of the Company

6. FINDINGS ABOUT THE COMPANY FOR FY18

o Gross Sales for the Year - Rs. 149.29bn

o Capital Expenditure for the Year– Rs. 30.80bn
o Net Profit for FY18 Rs. 7.24bn
o EBITDA for FY18 is Rs. 17.68bn
o Dividend payout Ratio of the company is 28.58%

7. SWOT ANALYSIS OF THE COMPANY

STRENGTH - With its entry in the two-wheeler segment, the Company is now a full-range
tyre player in India and can service the large and growing two-wheeler tyre segment.

WEAKNESS - In a rapidly-rising raw material cost scenario, the Company is unable to pass
on cost escalations to consumers, in a timely fashion, due to intense competition and
various market dynamics resulting in pressure on margins.

OPPORTUNITY- In India, the Company’s two-wheeler tyre product has been widely
accepted by the market and there are prospects of scaling up the market share in a fast-
growing and profitable segment.
THREATS- Increased competition from global players in India could impact the
Company’s growth plans and/or profitability.

9. COMPARISON OF THE COMPANY WITH ITS PEERS

54
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Comparison Table

10. CONCLUSIONS DRAWN

o Company stands on position three as compared to its competitors.

o Apollo Tyres Ltd should continue their effective formulation and implementation of
strategies which will help them be among the top players in the tyre industry.
o Apollo is also focusing into product diversification strategies.
o Satisfaction & returns to shareholder is adapted as the key in becoming a true global
brand.

55
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

LINKING THE THREE STATEMENTS

The 3 financial statements are all linked and dependent on each other. In financial modeling,
your first job is to link all three statements together in Excel, so it’s critical to understand how
they’re connected.

ACCOUNTING PRINCIPLES

The income statement is not prepared on a cash basis – that means accounting principles such
as revenue recognition, matching, and accruals can make the income statement very different
from the cash flow statement of the business. If a company prepared its income statement
entirely on a cash basis (i.e., no accounts receivable, nothing capitalized, etc.) it would have no
balance sheet other than shareholders’ equity and cash.

NET INCOME & RETAINED EARNINGS

Net income from the bottom of the income statement links to the balance sheet and cash flow
statement. On the balance sheet, it feeds into retained earnings and on the cash flow statement;
it is the starting point for the cash from operations section.

Fig: Linking of Net Income & Retained earnings

56
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

PP&E, DEPRECIATION, AND CAPEX

Depreciation and other capitalized expenses on the income statement need to be added back to
net income to calculate the cash flow from operations. Depreciation flows out of the balance
sheet from Property Plant and Equipment (PP&E) onto the income statement as an expense,
and then gets added back in the cash flow statement.

Fig: Effect of depreciation on 3 statement Model

WORKING CAPITAL

Modeling net working capital can sometimes be confusing. Changes in current

assets and current liabilities on the balance sheet are related to revenues and expenses
on the income statement but need to be adjusted on the cash flow statement to reflect
the actual amount of cash received or spent by the business. In order to do this, we
create a separate section that calculates the changes in net working capital.

57
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Effect of Working Capital

FINANCING

This can be a tricky part of linking the three statements and requires some additional
schedules. Financing events such as issuing debt affect all three statements in the following
way: the interest expense appears on the income statement, the principal amount of debt owed
sits on the balance sheet, and the change in the principal amount owed is reflected on the cash
from financing section of the cash flow statement.

CASH BALANCE

This is the final step in linking the 3 financial statements. Once all of the above items are linked
up properly, the sum of cash from operations, cash from investing, and cash from financing are
added to the prior period closing cash balance, and the result becomes the current period closing
cash balance on the balance sheet.

HOW TO LINK THE FINANCIAL STATEMENTS FOR FINANCIAL MODELING

If you’re building a financial model in Excel it’s critical to be able to quickly link the three
statements. In order to do this, there are a few basic steps to follow:

1. Enter at least 3 years of historical financial information for the 3 financial

statements
2. Calculate the drivers/ratios of the business for the historical period
3. Enter assumptions about what the drivers will be in the future
4. Build and link the financial statements following the principles discussed
above.

58
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

VALUATION APPROACHES DCF- FREE CASH FLOW TO THE FIRM

AND FREE CASH FLOW TO THE EQUITY
Discounted cash flow (DCF) valuation views the intrinsic value of a security as the present
value of its expected future cash flows. When applied to dividends, the DCF model is the
discounted dividend approach or dividend discount model (DDM). This reading extends DCF
analysis to value a company and its equity securities by valuing free cash flow to the firm
(FCFF) and free cash flow to equity (FCFE). Whereas dividends are the cash flows actually
paid to stockholders, free cash flows are the cash flows available for distribution to
shareholders.

FREE CASH FLOWS TO THE FIRM

FCFF, or Free Cash Flow to Firm, is the cash flow available to all funding providers (debt
holders, preferred stockholders, common stockholders, convertible bond investors, etc.). This
can also be referred to as unlevered free cash flow, and it represents the surplus cash flow
available to a business if it was debt free. A common starting point for calculating it is Net
Operating Profit After Tax (NOPAT) which can be obtained by multiplying Earnings Before
Interest and Taxes (EBIT) by (1-Tax Rate). From that, we remove all non-cash expenses and
remove the effect of CapEx and changes in Net Working Capital, as the core operations are the
focus.

To arrive at the FCFF figure a Financial Analyst will have to undo the work that
the accountants have done, the objective is to get the true cash inflows and outflows of the
business.

FCFF = NOPAT + D&A – CAPEX – Δ Net WC

NOPAT = Net Operating Profit

D&A = Depreciation and Amortisation expense

CAPEX = Capital Expenditure

Δ Net WC = Changes in Net Working Capital

FCFF IN BUSINESS VALUATION

FCFF is an important part of the Two-Step DCF Model, which is an intrinsic

valuation method. The second step, where we calculate the terminal value of the business,
may use the FCFF and grow it with a terminal growth rate, or more commonly, we may use
an exit multiple and assume the business is sold.

DCF Analysis is a valuable Business Valuation technique as it evaluates the intrinsic value of
the business by looking at the cash generating ability of the business.
Conversely, Comps and Precedent Transactions both use a Relative Valuation approach,
which is common in Private Equity due to restricted access to information.

59
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

FREE CASH FLOW TO THE EQUITY

Free cash flow to equity (FCFE) is the amount of cash a business generates that is available to
be potentially distributed to shareholders. It is calculated as Cash from Operations less Capital
Expenditures.

Formula:

FCFE = Cash from Operating Activities – Capital Expenditures + Net Debt Issued
(Repaid)

VALUATION APPROACHES DCF- TERMINAL VALUE AND

CALCULATIONS OF PRESENT VALUE OF CASH FLOWS

Terminal value is the estimated value of a business beyond the explicit forecast period. It is a
critical part of the financial model as it typically makes up a large percentage of the total value
of a business. There are two approaches to the terminal value formula: (1) perpetual growth,
and (2) exit multiple.

WHY IS A TERMINAL VALUE USED?

When building a Discounted Cash Flow / DCF model there are two major components: (1) the
forecast period and (2) the terminal value.

The forecast period is typically 3-5 years for a normal business (but can be much longer in
some types of businesses, like oil and gas or mining) because this is a reasonable amount of
time to make detailed assumptions for. Anything beyond that becomes a real guessing game,
which is where the terminal value comes in.

PERPETUAL GROWTH DCF TERMINAL VALUE FORMULA

This method assumes the business will continue to generate Free Cash Flow (FCF) at a
normalized state forever (perpetuity). The formula for calculating the terminal value is:

The formula for calculating the terminal value is:

TV = (FCFn x (1 + g)) / (WACC – g)

60
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Where:
TV = terminal value
FCF = free cash flow
g = perpetual growth rate of FCF
WACC = weighted average cost of capital

EXIT MULTIPLE DCF TERMINAL VALUE FORMULA

The exit multiple approach assumes the business is sold for a multiple of some metric
(i.e. EBITDA) based on currently observed comparable trading multiples for similar
businesses. The formula for calculating the terminal value is:

The formula for calculating the terminal value is:

TV = Financial metric (i.e. EBITDA) x trading multiple (i.e. 10x)

WHICH TERMINAL VALUE METHOD IS MORE COMMON?

The exit multiple approach is more common among industry professionals as they prefer to
compare the value of a business to something they can observe in the market.

EXAMPLE FROM A FINANCIAL MODEL

Below is an example of a DCF Model with a terminal value formula that uses the Exit Multiple
approach. The model assumes an 8.0x EV/EBITDA sale of the business that closes on
12/31/2022.

DCF models are very sensitive to assumptions that are made about terminal value.

A common way to help represent this is through sensitivity analysis.

61
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Example

CALCULATIONS OF PRESENT VALUE OF CASH FLOWS

The NPV formula is a way of calculating the Net Present Value (NPV) of a series of cash flows
based on a specified discount rate. The NPV formula can be very useful for financial analysis
and financial modeling when determining the value of an investment (a company, a project, a
cost-saving initiative, etc.).

Fig: Calculations Of Present Value Of Cash Flows

NPV = F / [ (1 + i)^n ]

Where,
62
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

PV = Present Value
F = Future payment (cash flow)
i = Discount rate (or interest rate)
n = the number of periods in the future the cash flow is

HOW TO USE THE NPV FORMULA IN EXCEL

Most financial analysts never calculate the net present value by hand nor with a calculator,
instead, they use Excel.

=NPV(discount rate, series of cash flow)

Example of how to use the NPV function:

Step 1: Set a discount rate in a cell.

Step 2: Establish a series of cash flows (must be in consecutive cells).

Step 3: Type =NPV(“ and select the discount rate ”, “ then select the cash flow cells”)

Fig: Calculating Present Value

63
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

TRADING COMPARABLES AND TRANSACTION COMPARABLES

TRADING COMPS
Trading multiples are also called “Peer Group Analysis”, “Public Market Multiples” and
“Comparable Company Analysis”.

HOW TO IDENTIFY COMPARABLE COMPANIES?

To find out comparable and similar companies to the one that is being valued, the parameters
below needs to be obtained. One of the fastest ways to consider few companies is to look at the
competitors of the target.

 Business Mix
 Products and services offered
 Type of customers
 Geographical location

 Size
o Revenue
o Assets
o EBITDA margin

ANALYSIS OF VARIOUS MULTIPLES:

 EV / Revenue – This is one of the most popular multiples used across industries as it is
difficult to manipulate revenue figures. This multiple becomes relevant especially when
a company has negative EBITDA, as the multiple EV / EBITDA will not be relevant. EV
/ Revenue is in the range of 1.0x to 3.0x.

 EV / EBITDA – This is one of the most commonly used multiples and it acts as a proxy
for free cash flows. EV / EBITDA is often in the range of 6.0x to 15.0x.

 EV / EBIT – EBIT is derived after adjustment of depreciation and amortization as it

reflects real expenses and considers wear and tear of a firm’s assets. Since EBIT is less
than EBITDA, the multiple is higher and is in the range of 10.0x to 20.0x.

 EV / Capital Employed – This is not one of the popular ways to calculate multiples, but
is still used by capital-intensive companies. The invested capital determines potential
earnings, however, it doesn’t consider differences in profitability.

 P / E – This valuation metric takes into consideration the price in numerator and earnings
per share in the denominator. P/E multiple is similar to equity value to net income,
wherein it is divided by fully diluted shares. This multiple ranges from 12.0x to 30.0x.

 PEG Ratio – It is simply the P/E ratio divided by the EPS growth rate and is often in the
range of 0.5x to 3.5x. The best part of this method is it considers the growth prospects of
the company while capturing its growth rate. A company in the growth stage will have
more value than a company that has reached the maturity stage.
64
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

STEPS IN PERFORMING COMPARABLE COMPANY ANALYSIS

In the next section of this guide, we will go through a detailed list of how to build your own
comps table. This type of work will be routine for anyone working as an analyst in investment
banking, equity research, corporate development, or private equity.

1. FIND THE RIGHT COMPARABLE COMPANIES

This is the first and probably the hardest (or most subjective) step in performing ratio analysis
of public companies. The very first thing an analyst should do is look up the company you are
trying to value on CapIQ or Bloomberg so you can get a detailed description and industry
classification of the business.

The next step is to search either of those databases for companies that operate in the same
industry and that have similar characteristics. The closer the match, the better.

The analyst will run a screen based on criteria that include:

1. Industry classification
2. Geography
3. Size (revenue, assets, employees)
4. Growth rate
5. Margins and profitability

2. GATHER FINANCIAL INFORMATION

Once you’ve found the list of companies that you feel are most relevant to the company you’re
trying to value it’s time to gather their financial information.

Once again, you will probably be working with Bloomberg Terminal or Capital IQ and you
can easily use either of them to import financial information directly into Excel.

The information you need will vary widely by industry and the company’s stage in the business
lifecycle. For mature businesses, you will look at metrics like EBITDA and EPS, but for earlier
stage companies you may look at Gross Profit or Revenue.

3. SETUP THE COMPS TABLE

In Excel, you now need to create a table that lists all the relevant information about the
companies you’re going to analyze.

The main information in comparable company analysis includes:

 Company name
 Share price
 Market capitalization
 Net debt
 Enterprise value
 Revenue
65
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

 EBITDA
 EPS
 Analyst estimates

The above information can be organized as shown in our example comparable company’s
analysis shown below.

4. CALCULATE THE COMPARABLE RATIOS

With a combination of historical financials and analyst estimates populated in the comps table,
it’s time to start calculating the various ratios that will be used to value the company in question.

The main ratios included in a comparable company analysis are:

 EV/Revenue
 EV/Gross Profit
 EV/EBITDA
 P/E
 P/NAV
 P/B

TRANSACTION COMPS
Precedent transaction analysis is a valuation method in which the price paid for similar
companies in the past is considered an indicator of a company’s value.

66
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Precedent transaction analysis creates an estimate of what a share of stock would be worth in
the case of an acquisition.

Also known as "M&A comps."

Data sources for precedent transaction analysis include the Securities Data Corporation, which
is a repository of mergers and acquisitions data. Trade publications, research reports, and the
annual filings are also good sources of data.

LIMITATIONS OF PRECEDENT TRANSACTION ANALYSIS

While this type of analysis benefits from using publicly available information, the amount and
quality of the information relating to transactions can sometimes be limited. This can make
drawing conclusions difficult.

While every transaction is different, and thus makes direct comparisons difficult, precedent
transaction analysis does help provide a general assessment of the market’s demand for a
particular asset and an approximate valuation of the asset.

STEPS TO PERFORM PRECEDENT TRANSACTION ANALYSIS:

1. SEARCH FOR RELEVANT TRANSACTIONS

The process begins by looking for other transactions that have happened in (ideally) recent
history and are in the same industry.

The screening process requires setting criteria such as:

 Industry classification
 Type of company (public, private, etc.)
 Financial metrics (revenue, EBITDA, net income)
 Geography (headquarters, revenue mix, customer mix, employees)
 Company size (revenue, employees, locations)
 Product mix (the more similar to the company in question the better)
 Type of buyer (private equity, strategic / competitor, public/private)
 Deal size (value)
 Valuation (multiple paid i.e. EV/Revenue, EV/EBITDA etc)

2. ANALYZE AND REFINE THE AVAILABLE TRANSACTIONS

Once the initial screen has been performed and the data is transferred into Excel then it’s time
to start filtering out the transactions that don’t fit the current situation.

In order to sort and filter the transactions, an Analyst has to careful “scrub” the transactions by
carefully reading the business descriptions of the companies on the list and removing any that
aren’t a close enough fit.

3. DETERMINE A RANGE OF VALUATION MULTIPLES

67
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

When a short list is prepared (following steps 1 and 2) the average (or selected range) of
valuation multiples can be calculated.

The most common multiples for precedent transaction analysis are EV/EBITDA and
EV/Revenue.

An Analyst may exclude any extreme outliers such as transactions that had EV/EBITDA
multiples much lower or much higher than the average (assuming there is a good justification
for doing so).

4. APPLY THE VALUATION MULTIPLES TO THE COMPANY IN QUESTION

After a range of valuation multiples from past transaction has been determined, those ratios can
be applied to the financial metrics of the company in question.

For example, if the valuation range was:

 5x EV/EBITDA (low)
 0x EV/EBITDA (high)

And the company in question has EBITDA of $150 million,

The valuation ranges for the business would be:

 $675 million (low)

 $900 million (high)

5. GRAPH THE RESULTS (WITH OTHER METHODS) IN A FOOTBALL FIELD

Once a valuation range has been determined for the business that’s being valued it’s important
to graph the results so they can be easily understood and compared to other methods.

The main valuation methods included in the chart are:

 Comparable company analysis

 Precedent transactions analysis
 DCF analysis
 Ability-to-pay analysis
 52-week hi/lo (if a public company)

Comparable company analysis V.S. Precedent transaction analysis

Both methods are a form or relative valuation, where the company in question is being
compared to other businesses to derive its value. However, “comps” are current multiples that
can be observed in the public markets, while “precedents” include a takeover premium and
took place in the past.

68
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

The main similarities are:

 Relative valuation
 Use multiples (EV/Revenue, EV/EBITDA)
 Hard to find perfectly comparable companies
 Shows what a presumably rational investor/acquirer willing to pay (observable)

The main differences are:

 Takeover premium (included in precedents, not in comps)

 Timing (precedents quickly become old, comps are current)

 Available information (difficult to find for precedents, readily available for comps)

COMPANY VALUATION – CASE STUDY

Comparable company analysis (or “comps” for short) is a valuation methodology that looks at
ratios of similar public companies and uses them to derive the value of another business. Comps
is a relative form of valuation, unlike a discounted cash flow (DCF) analysis, which is
an intrinsic form of valuation.

STEPS IN PERFORMING COMPARABLE COMPANY ANALYSIS

1 Find the right comparable companies

This is the first and probably the hardest (or most subjective) step in performing ratio analysis
of public companies. The very first thing an analyst should do is look up the company you are
trying to value on CapIQ or Bloomberg so you can get a detailed description and industry
classification of the business.

The analyst will run a screen based on criteria that include:

1. Industry classification
2. Geography
3. Size (revenue, assets, employees)
4. Growth rate
5. Margins and profitability

69
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

2 .Gather financial information

Once you’ve found the list of companies that you feel are most relevant to the company you’re
trying to value it’s time to gather their financial information.

The information you need will vary widely by industry and the company’s stage in the business
lifecycle. For mature businesses, you will look at metrics like EBITDA and EPS, but for earlier
stage companies you may look at Gross Profit or Revenue.

3 Setup the Comps Table

In Excel, you now need to create a table that lists all the relevant information about the
companies you’re going to analyze.

The main information in comparable company analysis includes:

 Company name
 Share price
 Market capitalization
 Net debt
 Enterprise value
 Revenue
 EBITDA
 EPS
 Analyst estimates

The above information can be organized as shown in our example comparable companies’
analysis shown below

4. Calculate the comparable ratios

70
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

With a combination of historical financials and analyst estimates populated in the comps table,
it’s time to start calculating the various ratios that will be used to value the company in question.

The main ratios included in a comparable company analysis are:

 EV/Revenue
 EV/Gross Profit
 EV/EBITDA
 P/E
 P/NAV
 P/B

Fig: Resulted Ratios

TIME VALUE OF MONEY

‘A bird in hand is worth two in the bush’ – this adage applies to financial transactions too.’

Time value of money (TVM) is the idea that money that is available at the present time is
worth more than the same amount in the future, due to its potential earning capacity. This core
principle of finance holds that provided money can earn interest, any amount of money is worth
more the sooner it is received.

There are five (5) variables that you need to know:

1. Present value (PV) - This is your current starting amount. It is the money you have in
your hand at the present time, your initial investment for your future.
2. Future value (FV) - This is your ending amount at a point in time in the future. It should
be worth more than the present value, provided it is earning interest and growing over time.
3. The number of periods (N) - This is the timeline for your investment (or debts). It is
usually measured in years, but it could be any scale of time such as quarterly, monthly, or
even daily.
4. Interest rate (I) - This is the growth rate of your money over the lifetime of the investment.
It is stated in a percentage value, such as 8% or .08.
5. Payment amount (PMT) - These are a series of equal, evenly-spaced cash flows.

QUES.
71
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

The following information about a company is given. You are required to calculate the Present
and the Future value.

Fig: Particulars of a company

PRESENT VALUE-STEPS:

1. To calculate the present value of the investment, Excel provides with the function of
present value with syntax, “=PV(rate,nper,pmt,[fv].[type])”.
2. Apply the formula to calculate the present value of the investment.

Fig: Applying PV formula

3. Excel will calculate the present value of the investment.

Fig: Resulted PV
FUTURE VALUE –STEPS

1. To calculate the future value of the investment, Excel provides with the function of future
value with syntax, “=FV(rate,nper,pmt,[pv].[type])”.
2. Apply the formula to calculate the future value of the investment.

72
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Calculating Future Value

3. Excel will calculate the future value of the investment.

Fig: Resulted FV

73
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

TECHNIQUES OF CAPITAL BUDGETING: NPV, IRR

NPV
The NPV (Net Present Value) function on excel calculates the Net Present Value for periodic
cash flows, based on a supplied discount rate, and a series of payments. The NPV in Excel is
generally leveraged under Financial calculation.

1. Rate (argument required): It is the discount rate over the length of the period.
2. Value1, Value2: Value1 is required. They are numeric values that represent series of
payments and income where:
o Outgoing payments are mentioned as negative numbers.
o Incoming payments are mentioned as positive numbers.

QUES. Following information about A Ltd is given:

Particulars PROJECT A PROJECT B

Rate of Interest 12% 12%

Cost of project -500000 -550000

Cash Inflows

1 400000 20000

2 60000 500000

3 50000 60000

4 70000 70000

5 80000 80000
Calculate NPV of the project.

SOLUTION:
1. To calculate PV of the project, one can use the formula of NPV provided by Excel.
2. NPV calculated only the present value of future inflows and not the NPV of project.
3. In order to calculate the NPV of the project, we need to Add the Outflow in the initial
year of the project.

74
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Calculating NPV

4. In order to know the project to be selected, we need to use the IF function in Excel. The
project with higher NPV will be given preference.

[Link] will help you select the project with higher returns.

Fig: Resulted output

IRR

The Excel IRR function is a financial function that returns the internal rate of return (IRR) for
a series of cash flows that occur at regular intervals.

=IRR (values, [guess])

Where, values - Array or reference to cells that contain values.
guess - [optional] An estimate for expected IRR. Default is .1 (10%).

75
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

QUES. Following information about A Ltd is given:

Particulars PROJECT A PROJECT B

Rate of Interest 12% 12%

Cost of project -500000 -550000

Cash Inflows

1 400000 20000

2 60000 500000

3 50000 60000

4 70000 70000

5 80000 80000
Calculate IRR of the project.
SOLUTION:

1. To calculate the IRR of both the projects, we can use the IRR function in excel.

Fig: Calculating IRR

2. Excel will calculate the IRR of both the projects.

76
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

TECHNIQUES OF CAPITAL BUDGETING - PI

Profitability index is an important measure in project finance to decide whether to invest in a
project or not.

If the profitability index is greater than 1, the project is accepted, and if it is less than 1, the
project is rejected.

QUES. The company’s cost of capital is 10%, you are required to calculate PI. The cash
flows at the present level of operations under the two alternatives are as follows:

Year Project Project

x y
0 25 40
1 - 10
2 5 14
3 20 16
4 14 17
5 14 15
SOLUTION:

1. In the given question, Present value of the cash inflows is calculated using the NPV function of
MS Excel.

Fig: Calculating Present Value

[Link] calculate the Profitability Index of both the projects, Present value of inflows is divided by the
initial outflow of the project.

Fig: Calculating PI

77
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

CAPITAL BUDGETING MODEL – CASE STUDY

CALCULATION OF COST OF CAPITAL

78
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

COST OF DEBT

A company's cost of debt is the effective interest rate a company pays on its debt obligations,
including bonds, mortgages, and any other forms of debt the company may have. Because
interest expense is deductible, it's generally more useful to determine a company's after-tax
cost of debt. Cost of debt, along with cost of equity, makes up a company's cost of capital.
CALCULATING COST OF DEBT
In order to calculate a company's cost of debt, you'll need two pieces of information: the
effective interest rate it pays on its debt and its marginal tax rate.
Many companies publish their average debt interest rate, but if not, it's fairly easy to calculate
using the company's financial statements. On the income statement, you can find the total
interest the company paid (note: If you're looking at a quarterly income statement, multiply this
figure by four in order to annualize the data). Then, on the balance sheet, you can find the total
amount of debt the company is carrying. Divide the annual interest by total debt and then
multiply the result by 100, and you'll get the effective interest rate on the company's debt
obligations.

EXAMPLE
To illustrate this concept, let's say that Company X paid $10 million in interest last year. Over
the past four quarters, the company's debt obligations averaged $250 million. Dividing its
interest paid by its average debt, then multiplying the result by 100, reveals an average interest
rate of 4%.
If Company X's marginal tax rate is 40%, then we can calculate the after-tax cost of debt as
follows:

COST OF EQUITY

Cost of equity refers to a shareholder's required rate of return on an equity investment. It is the
rate of return that could have been earned by putting the same money into a different
investment with equal risk.

How it works (Example):

The cost of equity is the rate of return required to persuade an investor to make a given equity
investment.

In general, there are two ways to determine cost of equity.

First is the dividend growth model:

79
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Cost of Equity = (Next Year's Annual Dividend / Current Stock Price) + Dividend Growth
Rate

Second is the Capital Asset Pricing Model (CAPM):

ra = rf + Ba (rm-rf)

where:
rf = the rate of return on risk-free securities (typically Treasuries)
Ba = the beta of the investment in question
rm = the market's overall expected rate of return

Why it Matters:

Cost of equity is a key component of stock valuation. Because an investor expects his
or her equity investment to grow by at least the cost of equity, cost of equity can be used
as the discount rate used to calculate an equity investment's fair value.

Both cost of equity calculation methods have advantages and disadvantages.

The dividend growth model is simple and straightforward, but it does not apply to
companies that don't pay dividends, and it assumes that dividends grow at a constant
rate over time. The dividend growth model also quite sensitive to changes in the
dividend growth rate, and it does not explicitly consider the risk of the investment.

BETA CALCULATION

The Beta coefficient is a measure of sensitivity or correlation of a security or investment

portfolio to movements in the overall market. We can derive a statistical measure of risk by
comparing the returns of an individual security/portfolio to the returns of the overall market
and identify the proportion of risk that can be attributed to the market.

HOW TO CALCULATE THE BETA COEFFICIENT

To calculate the Beta of a stock or portfolio, divide the covariance of the excess asset returns
and excess market returns by the variance of the excess market returns over the risk-free rate
of return:

QUES. The data regarding the shares of X-Ltd is given. Calculate the Beta

80
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Solution:

2. To calculate Beta in Excel, Slope formula is used with syntax,”

=SLOPE(known_y’s,known_x’s)”

Fig: Calculating Beta

CALCULATION OF WACC
WACC Formula is a calculation of a firm’s cost of capital in which each category is
proportionally weighted. It is the average rate that a company is expected to pay to its
stakeholders to finance its assets. In simple terms the minimum return that the firm should earn
on the existing asset base so that the investors and lenders are interested or they will invest
elsewhere.

where,

81
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

 E = Market cap i.e. Market value of the firm’s equity

 D = Market value of the firm’s debt
 V = total value of the capital or total value of firm’s financing = D + E
 E/V = percentage of capital that is equity
 D/V = percentage of capital that is debt
 Re = cost of equity (required rate of return)
 Rd = cost of debt
 Tc = Corporate tax rate

QUES:
Following information regarding Delta Airways is given. You are required to calculate the
Weighted Average Cost of Capital.

STEPS:
1. To calculate WACC, we need to first calculate the Market value of equity which will be
obtained by multiplying the number of shares with the price per share.

2. Now applying the formula of WACC in Excel worksheet.

3. Excel will calculate the value of WACC of Delta Airways as 12.63%

82
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

Fig: Resulted WACC

FORECASTING METHODS – MOVING AVEARGE

Moving average (also referred to as rolling average, running average or moving mean) can be
defined as a series of averages for different subsets of the same data set.

It is frequently used in statistics, seasonally-adjusted economic and weather forecasting to

understand underlying trends. In stock trading, moving average is an indicator that shows the
average value of a security over a given period of time

QUES: Given below are the yearly sales of a company. Calculate the 3 year moving average of the
company.

Steps:

1. To calculate the moving average of the sales, Go to Data tab → Data Analysis Tool Pack.

2. Select Moving Average. Click OK.

83
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

3. Select the sales row as input range and the blank trend column as output range. Click ok.

[Link] will calculate the moving average of the yearly sales of the company. The results show an
increasing trend in the sales.

FORECASTING METHODS – EXPONENTIAL SMOOTHING

Exponential smoothing is a rule of thumb technique for smoothing time series data using the
exponential window function. Whereas in the simple moving average the past observations are
weighted equally, exponential functions are used to assign exponentially decreasing weights
over time. It is an easily learned and easily applied procedure for making some determination
based on prior assumptions by the user, such as seasonality. Exponential smoothing is often
used for analysis of time-series data.

STEPS OF PERFORMING EXPONENTIAL SMOOTHING:

1. Go to Data tab → Data Analysis Pack.

2. Select exponential smoothing → Click OK.

84
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

[Link] the input and the output range. Click OK.

4. Excel will represent the exponential smoothing results.

Fig: Resulted Exponential Smoothing Analysis

85
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

FORECASTING METHODS – TREND ANALYSIS

TREND returns values along a linear trend. Fits a straight line (using the method of least
squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the
array of new_x's that you specify.

QUES:
You are given information regarding the inflows of two projects of the company. Calculate
the trends and also create the Graph showing the trends.

STEPS:

1. To calculate the trends of project A, Select the inflows of project A as independent variables.

2. To calculate the trends of project B, Select the inflows of project B as independent variables.

3. Excel will let you know the trend of the inflows.

86
05013788820
[Link] Hons (M)
 FINANCIAL MODELING LAB FILE
Yash Kumar

4. To add a Trend analysis chart, Go to Insert → Line

5. Excel will create a trend analysis chart based on the data.

Fig: Resulted Trend Analysis

87
05013788820
[Link] Hons (M)