FINANCIAL MODELLING USING SPREADSHEET FILE

SUBMITTED IN PARTIAL FULFILMENT

OF

BACHELORS OF COMMERCE[B.COM(HONS)]

[2022-2025]

Submitted To: Submitted By:

MS. NISHA RASHI

MAKKAR

(Assistant Professor) 02351488822

FAIRFIELD INSTITUTE OF MANAGEMENT AND TECHNOLOGY

KAPASHERA, NEW DELHI

AFFILIATED TO:

GURU GOBIND SINGHINDRAPRASTHA UNIVERSITY

DWARKA, NEW DELHI

 DECLARATION

I take this opportunity to express my profound gratitude and deep regards to my guide Ms. NISHA for her
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

S.NO. TOPIC PAGE NO.

1. ACKNOWLEDGEMENT

2.

INTRODUCTION

3.
INTERNAL RATE
OF RETURN

4. WEIGHTED AVERAGE
COST OF CAPITAL

5. FREE CASH FLOW TO FIRM

 INTRODUCTION TO FINANCIAL MODELLING

Financial modeling 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 modeling involves building
mathematical representations of financial situations and scenarios to analyze and forecast
outcomes.

Here's an introductory overview of financial modeling:

1. Purpose: Financial modeling 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 modeling
software. Excel is widely preferred due to its flexibility, familiarity, and accessibility.
5. 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 modeling.

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

- Corporate finance: Budgeting, strategic planning, and investment analysis.
- Investment banking: Valuation of companies, IPO analysis, and merger modeling.
- Real estate: Property valuation, feasibility analysis, and investment decision-making. -
Portfolio management: Risk assessment, asset allocation, and performance tracking.
In conclusion, financial modeling is a powerful tool for analyzing and predicting financial
outcomes, enabling informed decision-making across various domains in finance and
business. Mastering financial modeling requires a combination of technical skills, financial
knowledge, and critical thinking abilities.

Features of Financial Modeling:

1. Flexibility: Models can be adjusted easily to incorporate changes in assumptions

or scenarios.
2. Customization: Tailored to specific needs, whether it's valuation, forecasting,
budgeting, or risk analysis.
3. Integration: Combines data from various sources to provide a comprehensive view
of financial situations.
4. Accuracy: When appropriately constructed and validated, models can provide
accurate predictions and insights.
5. Scalability: Models can be scaled up or down in complexity depending on the
requirements and available resources.

Advantages of Financial Modeling:

1. Informed Decision Making: Helps decision-makers assess the potential outcomes

of various financial strategies, investments, or business decisions.
2. Risk Management: Enables the identification and quantification of financial
risks, allowing for the development of mitigation strategies.
3. Resource Allocation: Aids in the efficient allocation of resources by prioritizing
investments or projects based on their expected financial returns.
4. Communication Tool: Provides a structured framework for communicating
financial information and strategies to stakeholders, investors, and management.
5. Strategic Planning: Facilitates long-term strategic planning by forecasting
financial performance under different scenarios and assumptions.
Disadvantages of Financial Modeling:

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 Modeling:

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 modeling 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 modeling in practice.
 NET PRESENT VALUE (NPV)

1. What Is Net Present Value (NPV)?

Net present value (NPV) is the difference between the present value of cash inflows and the

present value of cash outflows over a period of time. NPV is used in capital budgeting and

investment planning to analyse the profitability of a projected investment or project.

Positive vs. Negative NPV

A positive NPV indicates that the projected earnings generated by a project or investment. An

investment with a negative NPV will result in a net loss.

How to calculate Net Present Value (NPV)

STEP 1:

Assume the yearly cash flows are earned at the end of the year, with the first payment arriving

exactly one year after the equipment has been purchased. This is a future payment, so it needs

to be adjusted for the time value of money. An investor can perform this calculation easily

with a spreadsheet.
STEP 2:

Formula to calculate NPV:

=NPV (rate,value1,value2…..)

STEP 3: put the value in the formula

By calculating npv@ 12% , we get positive value i.e earning an

STEP 3: put the value in the formula

By calculating npv@ 12% , we get positive value i.e earning

and, By calculating npv@ 15% , we get negative value i.e net

loss
 INTERNAL RATE OF RETURN (IRR)

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 for the

decision.

Lets take an Example:-

The initial investment here is a negative value as it is an outgoing payment. The cash inflows are

represented by positive values.

STEP 2: Now, we would simply select the data from B1:B11 to get IRR , =IRR(B1:B11

The internal rate of return we get is 14%.

CONCLUSION

-The Internal Rate of Return is primarily an indicator of the profitability of prospective

investments or projects.

However, since it is based on speculative figures, it might differ from the actual profitability.

▪ Higher IRR is better

As perhaps substantiated in the example above, a higher IRR indicates better profitability of an

outlay. Therefore, analysts use this metric to compare varying projects to determine which one

would be worth the while and money.

BUILD DYNAMIC MODELS WITH MULTIPLE SCENARIOS USING XIRR, MIRR

MIRR

The Modified Internal Rate of Return (MIRR) is a function in Excel that takes into account the

financing cost (cost of capital) and a reinvestment rate for cash flows from a project or company

over the investment’s time horizon

XIRR

The XIRR function is categorized under Excel financial functions. It will calculate the Internal Rate of
Return (IRR) for a series of cash flows that may not be periodic. It does this by assigning specific dates
to each individual cash flow. The main benefit of using the XIRR Excel function is that such unevenly
timed cash flows can be accurately modelled. To learn more, read why to always use XIRR over IRR
in Excel modelling
 WEIGHTED AVERAGE COST OF CAPITAL (WACC)

The weighted average cost of capital (WACC) is a financial metric that shows what the total cost of

capital (the interest rate paid on funds used for financing operations) is for a firm. Rather than being

dictated by a company's management, WACC is determined by external market participants and

signals the minimum return that a corporation would take in on an existing asset base, in its effort to

capture the interest of investors, creditors, and other capital providers. Companies that don't

demonstrate an inviting WACC number may lose their funding sources, who are likely to deploy

their capital elsewhere.

KEY TAKEAWAYS

The weighted average cost of capital (WACC) is a financial metric that reveals what the total

cost of capital is for a firm.

The cost of capital is the interest rate paid on funds used for financing operations.

Companies fund operations either through debt or equity, where each source has its own

associated cost.

Companies without an inviting WACC number risk losing their funding sources, who are

likely to bring their dollars elsewhere.

WACC=wD × rD×(1−t)+wP × rP + wE × rE

W = the respective weight of debt,

preferred stock/equity, and equityin the total capital structure.

t=tax rate

D=cost of debt

P=cost of preferred stock/equity

E=cost of equity
All companies must finance their operations, and this funding either comes from debt, equity, or a
combination of the two. Each source has a certain cost associated with it. And when analysing
different financing options, calculating the WACC provides the company with its financing cost,
which is then used to discount the project or business in a valuation model.

 Calculation of free cash flow to firm
 Calculation of free cash flow to equity
 Creation of data table
 Scenario manager and solver
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 + βi * [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 – Δ Net WC
NOPAT = Net Operating Profit
D&A = Depreciation and Amortization expense
CAPEX = Capital Expenditure
Δ Net WC = Changes in Net Working Capital