GURU GOBIND SINGH INDRAPRASTHA UNIVERSITY

INSTITUTE OF INNOVATION IN TECHNOLOGY & MANAGEMENT

FINANCIAL MODELING USING SPREADSHEETS

PRACTICAL FILE

B.COM 312

SUBMITTED TO: SUBMITTED BY:

 DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

TABLE OF CONTENTS
S.NO CONTENT PAGE NO. FACULTY
SIGNATURE
1. Financial modelling theory part

2. ASSIGNMENT 1 (VLOOKUP
Task)
3. ASSIGNMENT 2

4. ASSIGNMENT 3

5. ASSIGNMENT 4

6. ASSIGNMENT 5

7. ASSIGNMENT 6

8. ASSIGNMENT 7

9. ASSIGNMENT 8

10. ASSIGNMENT 9

11. ASSIGNMENT 10)

12. ASSIGNMENT 11

13. ASSIGNMENT 12

14. ASSIGNMENT 13

15. ASSIGNMENT 14

16. ASSIGNMENT 15

17. ASSIGNMENT 16

18. ASSIGNMENT 17

2
 DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

19. ASSIGNMENT 18

INTRODUCTION TO FINANCIAL MODELING

Financial modeling is the task of building up an abstract representation of a real-life financial decision
making situation; Financial modeling is creating a complete program, which helps you in coming to a
decision regarding investment in a company or a project.
Financial modeling is a mandatory activity for investment bankers, project finance persons, and peoples
researching in equity. To be more specific, financial Modeling refers to the process of building a structure
that integrates the Balance sheet, Income statements, Cash flow statements and Supporting Schedules to
enable decision making in areas like, Business Planning and Forecasting, Equity valuation, Credit
Analysis/Appraisal, Mergers/Acquisition Analysis, Project Appraisal etc.

Financial Modeling is the process by which a firm constructs a financial representation of some, or all
aspects of the firm, or given security. The model is usually characterized by performing calculations, and
makes recommendations, based on that information. The model may also summarize particular events for
the end user and provide direction regarding possible actions or alternatives. This is a model which is
designed to represent the performance of a financial asset , a business, a project, or any other investment.
Financial models can be constructed in many ways, either by the use of computer software, or with a pen
and paper. What's most important, however, is not the kind of user interface used, but the underlying logic
that encompasses the model.
For example, preparing a model of capital budgeting decision , model of a cash budget, model of
financial statements, etc. We have to resort it into a spreadsheet program for preparing a model. MS-Excel is
the most popular spreadsheet program used by the industry and academia.

A good financial model must have two characteristics - it must be accurate, and it must be prepared in
minimum possible time. That is, accuracy and speed are the heart of a good financial model.

NEED FOR FINANCIAL MODELING

Suppose, you have decided to learn financial modeling related to ratio analysis, So first you have to
download some readymade models from internet. Select the simplest of them first. Try to understand the
formulae used in the selected model. Then you may pick up your own data set - the best way of getting hold
of a dataset is to get an annual report of a company. Then you may feed the Income Statement and Balance
Sheet into two different sheets (but in one file) of a spreadsheet. Prepare a third sheet with all major

3
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

financial ratios. Then start defining financial ratios with the help of any standard finance textbook. Finally
link the relevant values of income statement and balance sheet of the selected company. Your own financial
model on ratio analysis is ready! You will certainly gain greater confidence and accuracy via repeated
practice.

Financial modeling supports management in making important business decisions. It involves the
quantification (measurement) of the potential impact of decisions, on the profit and loss account, balance
sheet and cash flow statements. Through financial models, managers can determine the outcome of a
proposal, before even its execution and rely on a rational and comprehensive justification for their decisions.
Moreover, these models enable managers to study different options and scenarios without imposing any risk
on the business. To avoid the common pitfalls related to financial modeling, designers should follow five(5)
basic principles. They should make sure that

(i)The model satisfies its objectives

(ii)Maintain model flexibility

(iii)Take inflation into consideration
(iv)Present the model clearly and interestingly and
(v)Measure outcome.

SCOPE OF FINANCIAL MODELING

Applications of Financial Modeling on Excel:

● Investment Banking: Financial Modeling helps Investment Banker in Valuing the company by
forecasting the revenues. On the basis of Valuation they recommend the buyer or seller on
acquisition of new funds or investments in new funds respectively.

● Equity Research : Financial Modeling enables analyst in examining an organization’s financial

projections, competitor’s projections and other dynamics to determine whether it is a smart or a
risky investment It is a Detailed research to recommend buy/sell price on companies under coverage.
There are over 90,000 listed stocks on global exchanges and each one have to be valued on a regular
basis

● Credit Rating : Financial Modeling equips Credit Analysts in collecting historical information & ,
outstanding debts and forecasting future growth on excel sheet to determine the degree of risk factor
, which helps him/her in giving rating

4
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

● Project Finance : Financial Modeling helps in assessing the financial possibility of a project and
creating a funding plan through debt and equity components

● Mergers & Acquisitions: Financial Modeling helps the companies in access the value of the
company which they want to merge or acquire by forecasting the revenues , preparing debt
schedule , by doing competitor analysis

● Corporate Finance: Financial Modeling helps companies in assessing their own finances & build
financial models for their on projects. & in creating a funding plan through debt and equity
components

LIMITATIONS OF FINANCIAL MODELING

❖ The future direction of markets is highly uncertain. In short, no single model can contain all the
necessary information to capture the uncertain path of outcomes.

❖ As a financial Modeling is a Analytical Study ,so that the result of study is Different from person to
person and company.

❖ The accuracy of a forecast depends on the quality of the data used as a basis for the financial models
and projections. A company entering a new market space does not have any historical data on which
to base the projections.

❖ Consumer behavior is complex. Consumers weight many different factors in their purchase
decisions. Companies may not have a good understanding of which of these factors is most
important.

5
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

ASSIGNMENT 1
VLOOKUP Task:
Create a small inventory list with product names and corresponding prices.
Use VLOOKUP to find the price of a product when its name is entered.

6
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

ASSIGNMENT 2
Scenario Manager Exploration:

Design a simple sales projection for a small business.

Use Scenario Manager to create different scenarios for high, medium, and low sales, and observe the impact
on profits.

7
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

ASSIGNMENT 3
Basic Correlation Analysis:
Collect data for a dependent variable (e.g., sales) and an independent variable (e.g., advertising expenses).
Use simple formulas to calculate the correlation coefficient.
Simple Regression Analysis:

8
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

ASSIGNMENT 4

Collect data for a dependent variable (e.g., sales) and an independent variable (e.g., advertising expenses).
Use the LINEST function to perform a basic linear regression analysis.
Variance Calculation:

ASSIGNMENT 5
9
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

Develop a financial dataset representing a cash flow projection for a company with a minimum of 12 months
Include categories such as "Starting Cash," "Income," "Expenses," and "Net Cash Flow." Use Excel to
create a Waterfall chart based on the cash flow projection dataset.

ASSIGNMENT 6
Develop a financial dataset with a minimum of 15 rows and 3 columns: "Quarter," "Revenue," and
"Expenses." Employ the Name Manager feature to create dynamic named ranges for "Quarter," "Revenue,"
and "Expenses." Ensure that these named ranges automatically adjust as new data is added.

10
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

ASSIGNMENT 7
Develop a financial dataset representing Month, Target sale, Achievement, Profit, Market share. Employ the
Interactivity using controls to create dynamic charts.

ASSIGNMENT 8
Generate a dataset with a minimum of 30 rows and 5 columns. Include relevant data, such as sales,
expenses, profit, and any other metrics suitable for your chosen context. Create at least three different types
of charts (e.g., line chart, bar chart, pie chart) based on the dataset to represent various aspects of the data.
Apply professional design principles, ensuring a clean and visually appealing layout for your dashboard.

ASSIGNMENT 9

You are a financial analyst working for a company considering an investment opportunity. Your task is to
evaluate the investment project using the concepts of Net Present Value (NPV) and Internal Rate of Return
(IRR).

Project Details:

Initial Investment: $100,000

Expected Cash Flows:

Year 1: $30,000

Year 2: $40,000

Year 3: $50,000

Year 4: $60,000

Year 5: $70,000

Instructions:

Calculate the NPV of the project assuming a discount rate of 10%.

11
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

Calculate the IRR of the project.

Based on your calculations, make a recommendation to the company whether to undertake the investment or
not.

Provide a brief explanation justifying your recommendation, highlighting the importance of NPV and IRR in
investment decision making.

ASSIGNMENT 10

You are a financial analyst working for a small manufacturing company called "Scenario Manager." The
company is considering two different projects: Project X and Project Y. Your task is to evaluate these
projects using the Modified Internal Rate of Return (MIRR) method and provide a recommendation to the
management team.

Instructions:

Create a dynamic financial model to calculate MIRR method for each project to adjust for potential
reinvestment rates.

Compare the MIRR results of Project X and Project Y.

Based on your analysis, make a recommendation to the management team regarding which project to
pursue.

Provide a brief explanation justifying your recommendation, highlighting the importance of MIRR in
investment decision-making.

ASSIGNMENT 11

You are a financial analyst working for a consulting firm specializing in investment analysis. Your task is to
evaluate two potential projects, Project X and Project Y, for a client. Your objective is to utilize the concept
of Lowest Common Denominators (LCD) to determine the value of each project and provide a
recommendation to the client.

Instructions:

Calculate the Lowest Common Denominator (LCD) for the cash flows of both Project X and Project Y.
Determine the present value of the cash flows for each project using the calculated LCD.
Compare the present value of the cash flows for Project X and Project Y.
Based on your analysis, recommend which project the client should pursue and justify your
recommendation.

ASSIGNMENT 12
12
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

You are a financial analyst tasked with evaluating two potential projects, Project A and Project B, for your
company. Your objective is to utilize the concept of Annual Equivalency Cash Flow (AECF) to determine
the value of each project and provide a recommendation to the management team.

Instructions:

Calculate the Annual Equivalency Cash Flow (AECF) for both Project A and Project B.
Determine the present value of the AECF for each project
Compare the present value of the AECF for Project A and Project B.
Based on your analysis, recommend which project the company should pursue and justify your
recommendation.
ASSIGNMENT 13

You are a financial analyst working for a company that is evaluating its cost of capital using the Weighted
Average Cost of Capital (WACC) method. Your task is to calculate the WACC for the company using Excel
based on provided data.

Data:

● Cost of Equity (Ke):

● Cost of Debt (Kd):
● Corporate Tax Rate:
● Market Value of Equity (E):
● Market Value of Debt (D):
ASSIGNMENT 14
You are a financial analyst tasked with estimating the required rate of return for a company's equity
using the Capital Asset Pricing Model (CAPM). Your objective is to calculate the cost of equity for
the company based on provided data using Excel.

Data:

Risk-Free Rate (Rf):

Market Risk Premium (Rm - Rf):

Beta (β) of the company's stock

ASSIGNMENT 15

You are a financial analyst working for a company tasked with calculating the Free Cash Flow to Firm
(FCFF) for a given period. Your objective is to compute the FCFF using Excel based on provided financial
data.

13
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

Data:

● EBIT (Earnings Before Interest and Taxes):

● Tax Rate:
● Depreciation and Amortization (D&A) Expense:
● Change in Net Working Capital (NWC):
● Capital Expenditures (CapEx):

ASSIGNMENT 16

You are a financial analyst tasked with calculating the Free Cash Flow to Equity (FCFE) for a company.
Your objective is to compute the FCFE using Excel based on provided financial data.

Data:

● Net Income:
● Depreciation and Amortization (D&A) Expense:
● Change in Net Working Capital (NWC):
● Capital Expenditures (CapEx):
● Debt Repayments (Debt Repay):
● Additional Equity Issued (Equity Issued):

14
DATE…………………… ROLL NO:
FACULTY SIGNATURE ………………..

15