Unit - 1 Excel as a tool of Financial Modelling: Basics of Excel, commands of excel, conditional

formatting, formulas, macros, what if analysis – goal seek, data table and scenario
manager, pivot table and charts, data analysis.
Unit-2 Financial Modelling Basic Concepts: Introduction, advanced function of MS-Excel as a
tool in financial modelling, steps, and components of financial markets
Unit-3 Financial Analysis: Various approaches to valuation- discounted cashflow method –
FCFF and FCFE, dividend discount model, precedent transaction analysis, relative
valuation model, financial ratios and company analysis, market-based methods – EPS and
multiplies, sensitivity analysis, probabilistic analysis- best and worst case.
Unit-4 Other Modelling Techniques: Time Value for Money, capital budgeting models, cost of
capital calculation, leverage, EPS Calculation.
Microsoft Excel is an electronic spreadsheet. You can use it to organize your data into rows
and columns. You can also use it to perform mathematical, financial and statistical
calculations quickly.

The Microsoft Excel Window This Section will introduce you to the Excel window. To
begin this Section, start Microsoft Excel 2007 as follows:
1. Click on Microsoft Start Button
2. Point the mouse on All Programs
3. Click on Microsoft Office
4. Click on Microsoft Excel 2007
The Microsoft Excel window appears and your screen looks similar to the one shown here.
RANGE
 Cut, Copy, Paste and Delete

DELETE

PASTE

CUT
COPY
Financial modeling in Excel is a powerful tool that helps professionals analyze and project financial data.
Excel offers a wide range of features and functions that can be used to create complex financial models.
Here are detailed notes on using Excel as a tool for financial modeling:

Basics of Excel:
Excel is a spreadsheet program that allows users to store, organize, and analyze data in a tabular format.
Here are some basic concepts and elements in Excel:

Workbook: An Excel file is called a workbook. It contains one or more worksheets.

Worksheet: Each workbook can have multiple worksheets. These are the individual tabs where you can
enter and manipulate data.

Cell: Cells are the individual boxes in a worksheet where you can enter data or formulas.

Row and Column: Rows run horizontally, and columns run vertically. Cells are referenced using a
combination of row and column labels, such as A1, B2, etc.

Formula Bar: It is the area at the top of the Excel window where you can enter and edit formulas.
Commands in Excel:
Excel offers a wide range of commands to perform various tasks. Some essential commands for financial
modeling include:

Data Entry: You can input financial data into cells and apply formatting to make the data more readable.

Formulas: Excel has a vast library of functions (e.g., SUM, AVERAGE, NPV, IRR) for performing calculations.

Formatting: You can format cells to change the appearance of data, such as currency formatting, date
formatting, and number formatting.

Sort and Filter: You can sort and filter data to organize it effectively.

Data Validation: You can set rules to control the type of data that can be entered in a cell.

Data Tables: Data tables allow you to create multiple scenarios and see how changes in variables impact your
financial model.
 Conditional formatting
Conditional formatting in Excel is a powerful feature that allows you to automatically format cells based on specific criteria or
rules. This can help you visually highlight important information, identify trends, and make your data more accessible and
meaningful. Here's a detailed explanation of conditional formatting in Excel:

How to Apply Conditional Formatting:

Select the Range: First, select the range of cells to which you want to apply conditional formatting. This can be a single cell, a
row, a column, or an entire worksheet.

Home Tab: Go to the "Home" tab in the Excel ribbon.

Conditional Formatting Button: In the "Styles" group, you'll find the "Conditional Formatting" button. Click on it to open a
dropdown menu with various conditional formatting options.

Choose a Rule: There are several predefined rules to choose from:

Highlight Cells Rules: These rules apply formatting based on the cell's value.
Top/Bottom Rules: Highlight the top or bottom values in a range.
Data Bars, Color Scales, and Icon Sets: These options allow you to visually represent data values with bars, gradients, or
icons.
New Rule: Create custom rules based on your own criteria.
 Common Types of Conditional Formatting:

Highlight Cells Rules:

Greater Than and Less Than: Highlight cells that are greater or less than a specified value.
Between: Highlight cells with values within a specified range.
Equal To: Highlight cells that are equal to a specific value.
Text That Contains: Highlight cells that contain a specific word or phrase.

Top/Bottom Rules:

Top 10 Items: Highlight the top or bottom 10 values in a range.

Above Average and Below Average: Highlight values above or below the average in the range.
Data Bars, Color Scales, and Icon Sets:

Data Bars: Display a horizontal bar within each cell to represent the cell's value relative to
the highest and lowest values in the range.
Color Scales: Apply color gradients to cells based on their values.
Icon Sets: Use icons (e.g., arrows pointing up or down) to indicate the value's significance.
New Rule:

Use a Formula: Create custom conditional formatting rules based on your own formula. For
example, you can use logical functions like IF to define complex conditions.
 Managing and Editing Conditional Formatting Rules:
• After applying conditional formatting, you can manage, edit, or delete rules:
• Go to the "Conditional Formatting" dropdown.
• Select "Manage Rules" to open the "Conditional Formatting Rules Manager." Here, you can see all the
applied rules, edit them, or remove them.

Conditional formatting is a versatile tool that enhances the readability and interpretation of your Excel data,
making it an essential feature for data analysis and reporting.
 Macros
• Macros in Excel are automated sequences of commands and actions that can be
created using Visual Basic for Applications (VBA).
• They allow users to automate repetitive tasks, perform complex calculations, and build
custom functions.
• You can record macros by navigating to the "Developer" tab (you may need to enable it
in Excel settings) and selecting "Record Macro."
• Excel records your actions and converts them into VBA code.
• Recorded macros can be edited and customized in the VBA editor. This allows you to
fine-tune their behavior or add conditional statements and loops.
• Macros save time by automating repetitive tasks.
• They reduce the risk of human error in data processing and calculations.
• Complex and customized functions can be built for specific needs.
 Pivot Tables
• Pivot tables are a data summarization and analysis tool in Excel.
• They enable you to transform large datasets into concise, meaningful summaries.

Key steps for creating a pivot table:

• Select the data range you want to analyze.
• Go to the "Insert" tab and choose "PivotTable."
• Drag and drop fields into the Rows, Columns, and Values areas to create a summary table.
 Charts
• Charts are visual representations of data, making it easier to understand and analyze trends.
• Excel provides various chart types, including bar charts, line charts, pie charts, and more.

To create a chart:
• Select the data you want to visualize.
• Go to the "Insert" tab and choose the chart type.
• Customize the chart by adding titles, labels, and formatting options.

BENEFITS:
• Pivot tables help in data exploration and summarization.
• Charts visually convey data trends and patterns.
• They aid in making data-driven decisions and presenting information to stakeholders.
 Data Analysis
Excel provides several data analysis tools for financial modeling:

Regression Analysis: Excel can perform linear and multiple regression analysis to model relationships
between variables.

Solver Add-in: It's used for optimizing complex financial models by finding the best values for specific
variables.

Financial Functions: Excel has a range of financial functions for present value, future value, net present
value, internal rate of return, and more.

Financial modeling in Excel is a comprehensive skill, and these notes cover the basics and important tools
to get you started. As you gain experience, you can explore more advanced features and techniques to
create sophisticated financial models.
SUM Function:
The SUM function is used to add up a range of numbers.

Syntax:

=SUM(number1, [number2], [number3], ...)

number1: The first number or range you want to add.
[number2], [number3], etc.: Additional numbers or ranges you want to add (optional).
Example:
To add the values in cells A1 through A5, you can use the SUM function like this:

=SUM(A1:A5)
This formula will return the sum of the values in the specified range.
AVERAGE Function:
The AVERAGE function calculates the arithmetic mean (average) of a range of numbers.

Syntax:

=AVERAGE(number1, [number2], [number3], ...)

number1: The first number or range for which you want to calculate the average.
[number2], [number3], etc.: Additional numbers or ranges for which you want to calculate the average
(optional).
Example:
To find the average of values in cells B1 to B10, you can use the AVERAGE function like this:

=AVERAGE(B1:B10)
This formula will return the average of the values in the specified range.

Both the SUM and AVERAGE functions are commonly used in financial modeling and data analysis to perform
calculations on numerical data.
MIN Function:
The MIN function is used to find the smallest value in a range.

Syntax:

=MIN(number1, [number2], [number3], ...)

number1: The first number or range from which you want to find the smallest value.
[number2], [number3], etc.: Additional numbers or ranges (optional).
Example:
To find the smallest value among the numbers in cells E1 to E6, you can use the MIN function like this:

=MIN(E1:E6)
This formula will return the smallest value in the specified range.
MAX Function:
The MAX function is used to find the largest value in a range.

Syntax:

=MAX(number1, [number2], [number3], ...)

number1: The first number or range from which you want to find the largest value.
[number2], [number3], etc.: Additional numbers or ranges (optional).

Example:
To find the largest value among the numbers in cells F1 to F4, you can use the MAX function like this:

=MAX(F1:F4)
This formula will return the largest value in the specified range.

These functions are commonly used in financial modeling and data analysis to perform calculations and statistical
analysis on numerical data.
COUNT Function:
The COUNT function is used to count the number of cells in a range that contain numbers.

Syntax:

=COUNT(value1, [value2], [value3], ...)

value1: The first value or range to be counted.

[value2], [value3], etc.: Additional values or ranges to be counted (optional).

Example:
To count the number of cells in the range C1 to C10 that contain numbers, you can use the COUNT function like
this:

=COUNT(C1:C10)
This formula will return the count of cells in the specified range that contain numeric values.
COUNTA Function:
The COUNTA function is used to count the number of non-empty cells in a range.

Syntax:
=COUNTA(value1, [value2], [value3], ...)

value1: The first value or range to be counted.

[value2], [value3], etc.: Additional values or ranges to be counted (optional).

Example:
To count the number of non-empty cells in the range D1 to D8, you can use the COUNTA function like this:

=COUNTA(D1:D8)
This formula will return the count of cells in the specified range that are not empty.
COUNTIF Function:

The COUNTIF function in Excel is used to count the number of cells within a specified range
that meet a specific condition or criteria. Here's the syntax for the COUNTIF function:

Syntax:
=COUNTIF(range, criteria)
• range: The range of cells you want to count based on the given criteria.
• criteria: The condition or criteria used to determine which cells to count.

Example:
Suppose you have a range of numbers in cells A1 to A10, and you want to count how many
of these numbers are greater than 5. You can use the COUNTIF function like this:
=COUNTIF(A1:A10, ">5")
In this example, the formula will count the number of cells in the range A1:A10 that contain
values greater than 5 and return the count as the result.
AND Function:
The AND function returns TRUE if all specified conditions are true and FALSE if any of them are false.
=AND(logical1, [logical2], ...)
• logical1: The first condition to be evaluated.
• [logical2], [logical3], etc.: Additional conditions to be evaluated (optional).
Example:
Suppose you want to check if both cell A1 and B1 contain values greater than 10. You can use the AND function like this:
=AND(A1>10, B1>10)
If both conditions are true, this formula will return TRUE. If either or both conditions are false, it will return FALSE.

OR Function:
The OR function returns TRUE if at least one of the specified conditions is true and FALSE if all of them are false.
=OR(logical1, [logical2], ...)
• logical1: The first condition to be evaluated.
• [logical2], [logical3], etc.: Additional conditions to be evaluated (optional).
Example:
Suppose you want to check if either cell A1 or B1 (or both) contain values greater than 10. You can use the OR function like
this:
=OR(A1>10, B1>10)
If at least one of these conditions is true, this formula will return TRUE. If both conditions are false, it will return FALSE.
IF Function:

The IF function in Excel is used to perform conditional operations. It allows you to make decisions and perform different
calculations based on whether a condition is met or not. Here's the syntax for the IF function:
The IF function has three parts: the logical test, the value if true, and the value if false.

Syntax:
=IF(logical_test, value_if_true, value_if_false)
logical_test: This is the condition or test that you want to evaluate. If the condition is met, the function returns value_if_true,
otherwise, it returns value_if_false.

value_if_true: The value or expression that is returned if the logical_test is true.

value_if_false: The value or expression that is returned if the logical_test is false.

Example:
Suppose you want to determine if a value in cell A1 is greater than 10. If it is, you want to display "Yes," and if it's not, you
want to display "No." You can use the IF function like this:

=IF(A1>10, "Yes", "No")

In this example, if the value in cell A1 is greater than 10, the formula returns "Yes." Otherwise, it returns "No."

The IF function is widely used in Excel for making logical decisions and is a fundamental building block for creating more
complex formulas and logical tests.