FINANCIAL MODELLING USING SPREADSHEET FILE

SUBMITTED IN PARTIAL FULFILMENT

OF

BACHELORS OF COMMERCE[[Link](HONS)]

[2022-2025]

Submitted To: Submitted By:

MS. NISHA ROMIL
(Assistant Professor) 02251488822

FAIRFIELD INSTITUTE OF MANAGEMENT AND TECHNOLOGY

KAPASHERA, NEW DELHI

GURU GOBIND SINGH INDRAPRASTA UNIVERSITY

DWARKA, NEW DELHI

 DECLARATION

I take this opportunity to express my profound gratitude and deep regards to my guide Ms. NISHA
for here exemplary guidance, monitoring and constant encouragement throughout the course of this
project. The blessing, help and guidance given by her from time to time shall carry me a long way
in the journey of life on which I am about to embark.

Last but not least, my sincere thanks to my parents and friends for their wholehearted support and
encouragement.

I also hereby declare that the project work entitled “Practical lab file on Data Analysis with
Spreadsheets lab” under the guidance of “MS. NISHA” is my original work and it has not been
submitted earlier in any other university or institution.
 CERTIFICATE

This is to certify that the project titled “Financial Modelling Using Spreadsheets Lab” is an
academic work done by Rashi Makkar submitted in the partial fulfilment of the requirements for the
award of degree of Bachelor of Commerce (Hons.) at Fairfield Institute OF management And
Technology, New Delhi under my guidance and direction.

Rashi Makkar has given an undertaking that the information presented in the project has not been
submitted earlier.

Signature of faculty

MS. NISHA

(Assistant professor)
 TABLE OF CONTENT

[Link]. TITTLE PAGE NO.

1. ACKNOWLEDGEMENT

2.
INTRODUCTION

3. NET PRESENT
VALUE(NPV)

4. INTERNAL RATE OF
RETURN(IRR)

5. WEIGHTED AVERAGE
COST OF CAPITAL

6. CAPITAL ASSET
PRICING MODEL

7. PIVOT TABLE AND

DASHBOARD
 ACKNOWLEDGEMENT

The project entitled ‘FINANCIAL MODELLING USING SPREADSHEETS FILE’ for one

required an improved environment, extensive Endeavour , and all necessary support. I take this an

opportunity to express my gratitude to [Link], my project guide for her able guidance,

cooperation and out of box thinking without which this project would not have been exciting at all.

The successful progression of my project also gives me the opportunity to acknowledge and

appreciate the staff of the college that provides me much needed stimulating suggestions and

encouragement in order to stem this project towards completion.

THANKING YOU

ROMIL

02251488822
 INTRODUCTION TO FINANCIAL MODELLING

Financial modelling is a crucial tool used by professionals in finance, investment banking,

corporate finance, and other related fields to make informed decisions about investments,

business operations, and strategic planning. Essentially, financial modelling involves building

mathematical representations of financial situations and scenarios to analyse and forecast

outcomes.

Here's an introductory overview of financial modelling:

1. Purpose: Financial modelling helps in understanding the potential financial performance of

a business, project, or investment opportunity. It aids in decision-making by providing

insights into the potential risks and returns associated with various courses of action.

2. Components: A financial model typically includes various components such as

historical financial data, assumptions about future performance, formulas and calculations,

and output metrics. These components are integrated into a coherent framework to simulate

different scenarios and outcomes.

3. Types of Models: Financial models can vary widely depending on their purpose and

complexity. Common types include:

- Valuation Models: Used to estimate the value of a business, asset, or investment. -

Forecasting Models: Predict future financial performance based on historical data and
assumptions about future conditions.

- Budgeting Models: Aid in planning and managing budgets for businesses or projects. -

Merger & Acquisition (M&A) Models: Assess the financial implications of mergers,

acquisitions, or divestitures.

- Risk Models: Evaluate the potential risks and their impact on financial outcomes.

4. Tools: Financial models are often created using spreadsheet software like Microsoft Excel,

although more advanced models may be built using specialized financial modelling software.

Excel is widely preferred due to its flexibility, familiarity, and accessibilitty.

Best Practices:

- Simplicity: Keep models as simple as possible while still capturing the essential

aspects of the situation.

-Transparency: Document assumptions, methodologies, and sources of data to ensure

transparency and reproducibility.

- Flexibility: Design models to accommodate changes in assumptions or inputs easily. -

Accuracy: Validate models using historical data or sensitivity analysis to ensure accuracy

and reliability.

- User-friendliness: Ensure that the model is user-friendly and understandable for

stakeholders who may not have expertise in financial modelling.

6. Applications: Financial modelling is used in various areas including:

- Corporate finance: Budgeting, strategic planning, and investment analysis.

- Investment banking: Valuation of companies, IPO analysis, and merger modelling.

- Real estate: Property valuation, feasibility analysis, and investment decision-making. -

Portfolio management: Risk assessment, asset allocation, and performance tracking.

In conclusion, financial modelling is a powerful tool for analysing and predicting financial

outcomes, enabling informed decision-making across various domains in finance and

business. Mastering financial modelling requires a combination of technical skills, financial

knowledge, and critical thinking abilities.

Features of Financial Modelling:

1. 2. 3. 4. 5. Flexibility: Models can be adjusted easily to incorporate changes in assumptions or

scenarios.

Customization: Tailored to specific needs, whether it's valuation, forecasting,

budgeting, or risk analysis.

Integration: Combines data from various sources to provide a comprehensive view of

financial situations.

Accuracy: When appropriately constructed and validated, models can provide

accurate predictions and insights.

Scalability: Models can be scaled up or down in complexity depending on the

requirements and available resources.

Advantages of Financial Modelling:

1. 2. 3. 4. 5. Informed Decision Making: Helps decision-makers assess the potential outcomes of

various financial strategies, investments, or business decisions.

Risk Management: Enables the identification and quantification of financial risks,

allowing for the development of mitigation strategies.

Resource Allocation: Aids in the efficient allocation of resources by prioritizing

investments or projects based on their expected financial returns.

Communication Tool: Provides a structured framework for communicating financial

information and strategies to stakeholders, investors, and management.

Strategic Planning: Facilitates long-term strategic planning by forecasting financial

performance under different scenarios and assumptions.

Disadvantages of Financial Modelling:

1. Complexity: Developing and understanding complex financial models requires

specialized skills and knowledge, which may be challenging for some users.

2. Assumption Sensitivity: Results are highly dependent on the accuracy of

assumptions, and small changes in inputs can lead to significant variations in

outcomes.

3. Data Limitations: Relies heavily on historical data and assumptions about future

trends, which may not always accurately reflect real-world conditions.

4. Over-reliance: Users may become overly reliant on models, leading to a false sense of

certainty or neglect of qualitative factors that can impact financial outcomes.

5. Time and Resources: Constructing and maintaining sophisticated financial models

can be time- consuming and resource-intensive, especially for large or complex projects.

Uses of Financial Modelling:

1. Valuation: Estimating the value of companies, assets, or investment opportunities

using various valuation techniques such as discounted cash flow (DCF) analysis or

comparable company analysis (CCA).

2. Forecasting: Predicting future financial performance based on historical data, industry

trends, and economic factors.

3. Budgeting and Planning: Creating budgets, financial plans, and forecasts to guide

resource allocation and strategic decision-making.

4. Investment Analysis: Assessing the financial viability and potential returns of

investment opportunities, including stocks, bonds, real estate, and projects.

5. Risk Management: Identifying, quantifying, and managing financial risks through

techniques such as sensitivity analysis, scenario analysis, and Monte Carlo simulation.

6. Mergers and Acquisitions (M&A): Evaluating the financial implications of mergers,

acquisitions, or divestitures, including synergies, valuation, and financing options.

Financial modelling is a versatile tool with numerous applications across various domains in

finance and business. While it offers significant advantages in terms of informed decision

making and risk management, it also comes with challenges such as complexity, reliance on

assumptions, and resource requirements. Understanding these features, advantages,

disadvantages, and uses is essential for effectively leveraging financial modelling in practices
 NET PROFIT VALUE

NPV[NET PROFIT VALUE]

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

5. Excel will help you select the project with higher returns.

Fig: Resulted output

 INTERNAL RATE OF RETURN

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%).

1..What is the Internal Rate of Return (IRR)?

The Internal Rate of Return (IRR) is the discount rate that makes the net present value (NPV) of

a project zero. In other words, it is the expected compound annual rate of return that will be

earned on a project or investment. In the example below, an initial investment of $50 has a 22%

IRR. That is equal to earning a 22% compound annual growth rate.

PURPOSE OF IRR :

When calculating IRR, expected cash flows for a project or investment are given and the NPV

equals zero. Put another way, the initial cash investment for the beginning period will be equal to
the

present value of the future cash flows of that investment. (Cost paid = present value of future cash

flows, and hence, the net present value = 0).

Once the internal rate of return is determined, it is typically compared to a company’s hurdle rate

or cost of capital. If the IRR is greater than or equal to the cost of capital, the company would

accept the project as a good investment. (That is, of course, assuming this is the sole basis .
 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.

Solution:

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

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

Fig: Calculating Beta

 WEIGHTED AVERAGE OF COST CAPITAL
( 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,

 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%

Fig: Resulted WACC
 CAPITAL ASSET PRICING MODEL (CAPM)

CAPM takes into account the riskiness of an investment relative to the market. The model is less
exact due to the estimates made in the calculation (because it uses historical information).
CAPM Formula:
E(Ri) = Rf + Bi * [E(Rm) - Rf]
Where:
E(Ri) = Expected return on asset
Rf = Risk-free rate of return
ßi = Beta of asset
E(Rm) = Expected market return
The risk-free rate in the CAPM formula accounts for the time value of money. The other
components of the CAPM formula account for the investor taking on additional risk.
The beta of a potential investment is a measure of how much risk the investment will add to a
portfolio that looks like the market. If a stock is riskier than the market, it will have a beta greater
than one. If a stock has a beta of less than one, the formula assumes it will reduce the risk of a
portfolio.
 CALCULATION OF FREE CASH FLOW TO 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.
Example of How to Calculate FCFF
Below, we have a quick snippet from our Business Valuation Modeling Course, which has a step-
by-step guide on building a DCF Model. Part of the two-step DCF Model is to calculate the FCFF
for projected years.
How to calculate the FCFF for projected years as part of the two-step DCF model
FCFF Formula:
FCFF = NOPAT + D&A - CAPEX - A Net WC
NOPAT = Net Operating Profit
D&A = Depreciation and Amortization expense
CAPEX = Capital Expenditure
A Net WC = Changes in Net Working Capital
 PIVOT TABLE AND DASHBOARD

QUES. Create a dashboard and pivot table for the sales data. (given in the Excel file)

Step 1: Start by selecting the entire sales data table using the shortcut key CtrI + A, then go to
Insert tab and select the option Pivot table. Decide where you want to keep your pivot table
whether in existing worksheet or in new worksheet then select OK