GURU GOBIND SINGH

INDRAPRASTHAUNIVERSITY,
NEW DELHI

Practical Lab File ON

Financial Modelling Using Spreadsheets Lab
Submitted in partial fulfilment of the requirement for the award of degree of
B.COM Hons
Session: 2021-2024

SUBMITTED AT:
DELHI SCHOOL OF PROFESSIONAL STUDIES & RESEARCH
ROHINI, NEW DELHI-85
[AFFILIATED TO GGSIPU-NEW DELHI]

Submitted to :- Submitted by :-
Ms. Ayushi Guglani Mr. DHRUV GUPTA
Assistant Professor 02212588821
 Declaration

I take this opportunity to express my profound gratitude and deep regards to my guide Ms. Ayushi
Guglani 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. Ayushi Gugli”” is my original work and it has not been submitted earlier
in any other university or institution.

DHRUV GUPTA
BCOM Hons- 6B
 Certificate

This is to certify that the project titled “Financial Modelling Using Spreadsheets Lab” is an
academic work done by DHRUV GUPTA submitted in the partial fulfilment of the
requirements for the award of degree of Bachelor of Commerce (Hons.) at Delhi School of
Professional Studies and Research, New Delhi under my guidance and direction.

DHRUV GUPTA has given an undertaking that the information presented in the project has
not been submitted earlier.

(Signature of Faculty)

MS. AYUSHI GUGLANI

(Assistant Professor), DSPSR

 TABLE OF CONTENT

TOPICS PAGE
NO.
INTRODUCTION TO FINANCIAL MODELLING
UNIT 1 (INTRODUCTION)
 Combination of multiple functions in a problem as VLOOKUP match,
index match
 VLOOKUP and if
 Offset function
 Sensitivity analysis using different ways
 Scenario manager and how to use that in model
 Iterative calculation
 Correlation using spreadsheet
 Regression using spreadsheet
 Variance using spreadsheet
 Summarize data from different sheets into single sheet using
indirect function
UNIT 2 (CHARTING)
 Rules of creating a bar chart
 Pictures as linked objects in spreadsheet
 Creating dynamic charts at the same time in same location using
filters
 Now and then analysis chart
 Waterfall charts
 Thermometer charts
 Change in chart using sensitivity analysis
 Interactivity using from control
 Creation of dashboard
 Conditional formatting
UNIT 3 (FINANCIAL ANALYSIS)
 Calculate Net Present Value (NPV)
 Calculate Internal Rate of Return (IRR)
 Build dynamic models with multiple scenarios using XIRR, MIRR
UNIT 4 (OTHER MODELLING TECHNIQUES)
 Weighted average cost of capital (WACC)
 Capital asset pricing model (CAPM)
 Calculation of free cash flow to firm
 Calculation of free cash flow to equity
 Creation of data table
 Scenario manager and solver
UNIT 1 (INTRODUCTION)

INTRODUCTION TO FINACIAL
MODELLING

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
decisionmaking 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.
UNIT 3 (FINANCIAL ANALYSIS)
CALCULATE NET PRESENT VALUE (NPV)

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

CALCULATE INTERNAL RATE OF RETURN (IRR)

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
UNIT 4 (OTHER MODELLING TECHNIQUES)
• 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