I

Internship based project

Submitted in partial requirements for the degree of
BBA(HONS)
(Bachelor of Business Administration Honours)
Semester IV
BY
“Keval Rajesh Gala”

Somaiya Vidhyavihar University

Vidhyavihar, Mumbai-400077
2021-22
I
Declaration by the learner,

I, the undersigned ‘Gala Keval Rajesh’hereby declare that the work embodied in this
internship-based project work titled “Internship based project with “Biz Express” forms my
own contribution to the research work carried out under the guidance of Ms. Fayeza
Sonkachwala and is a result of my own research work. This work has not been previously
to submitted to any other university for any degree/diploma to this or any other University.
Wherever reference has been made to previous works of others, it has been clearly indicated
as such and included in the bibliography. I, hereby further declare that all information of
this document has been obtained and presented in accordance with academic rules and
ethical conduct.

College Seal Name and Signature of the learner

Certified by:

Name of the guide: Ms. Fayeza Sonkachwala

I

THE BIG4 FOR MSME

205, Raja Industrial Estate, Purushottam Kheraj Rd, Sarvodaya Nagar, Mulund West, Mumbai, Maharashtra
400080

Tel: +91 93268 57579 Email- info@bizexpress.in

To whomever it may concern,

This is to certify that Mr. Keval Gala has worked with us for the tenure of 3 months in the position of
Financial Analyst from 4/04/22 to 3/07/22.
During the period of his internship program with us, he had been exposed to different processes and was
found diligent, hardworking and inquisitive. He demonstrated his work with self-motivation to learn new
skills.

We wish him all the best for his uncoming career.

Sincerely,

Harshit Shah (Proprietor)

I
Internship Details

Name of the company BizExpess

Type In Office

Location 05, Raja Industrial Estate, Purushottam

Kheraj Rd, Sarvodaya Nagar, Mulund
West, Mumbai
Key Person Harshit Shah(Founder)
Darsh Doshi (Founder)
Internship Detail Financial Modelling & Financial
Projection
Internship Duration 3 months

Type of Intenship Part time

Start Date 4th April 2022

End Date 3rd July 2022

I

INDEX

Chapters Particulars Page n.o

1 Introduction to Financial Modelling
1.1 Introducton 6
1.2 Understanding FM 6
1.3 Uses of FM 7
1.4 Importance of FM 7
1.5 Benefits 9
1.6 Steps Invovled 10

2 Inroduction of the Organization

1.1 Company Profile 13
1.2 Ideology 13
1.3 Services Offered 14
1.4 Clients 15

3 My Work
1.1 Picking Company 16
1.2 Set-up Excel sheet 17
1.3 P&L Projection 20
1.4 Capex 22
1.5 Asset Schedule 23
1.6 Depreciation 24
1.7 Cashflow activities 26
1.8 Valuation Basics 28
1.9 Share Price 36

Learning Outcome 38
Conclusion 39
I
Chapter 1
Introduction to Financial Modelling

1.1 Introduction
Financial modelling is the process of creating a summary of a company's expenses and
earnings in the form of a spreadsheet that can be used to calculate the impact of a future
event or decision.
A financial model has many uses for company executives. Financial analysts most often use
it to analyse and anticipate how a company's stock performance might be affected by future
events or executive decisions.
It is the ability of an individual to perform calculations and construct a simplified financial
representation of one or more aspects of any given firm. These skills encompass simulating
the impact of certain variables such that the company has planned course of action in case
they actually occur.

1.2 Understanding Financial Modelling

Financial modelling is a representation in numbers of a company's operations in the past,
present, and the forecasted future. Such models are intended to be used as decision-making
tools. Company executives might use them to estimate the costs and project the profits of a
proposed new project.
Financial analysts use them to explain or anticipate the impact of events on a company's
stock, from internal factors such as a change of strategy or business model to external factors
such as a change in economic policy or regulation.
Financial models are used to estimate the valuation of a business or to compare businesses
to their peers in the industry. They also are used in strategic planning to test various
scenarios, calculate the cost of new projects, decide on budgets, and allocate corporate
resources.
Financial modelling includes mathematical representations of key financial and operational
ratios that analyse how a business reacts to certain economic events. In general, financial
modelling contains many different financial aspects such as cash flow projections, debt,
inflation, inventory, depreciation, revenue stream and more. These models show the current
financial situation of the company and the future possible situation.
I
1.3 What Is Financial Modelling Used For?

A financial model is used for decision-making and financial analysis by people inside
and outside of companies. Some of the reasons a firm might create a financial model include
the need to raise capital, grow the business organically, sell or divest business units, allocate
capital, budget, forecast, or value a business.

1.4 Importance of Financial Modelling:

Financial Modelling is the main core element to take the major business decisions in a
corporate world. Financial models are the most valuable tools for executing business
choices to get perfect solutions. A model can advise you regarding the grade of risk
associated with implementing certain decisions. They can also be utilized to devise an
effective financial statement that reflects the finances and operations of company. These
models help online internet businesses take quick decisions more confidently.
Financial modelling also plays an important role in capital budgeting. Not only does it
make financial statement analyses and resource allotment for the next big investment easier,
but it also helps in determining the cost of capital. It provides a thorough analysis of
debt/equity structure for this purpose, along with the returns expected by investors.
I
1.4 What are you learning and why?

Think about a typical company; as you can imagine, the company can have several moving
parts. For example, a manufacturing company can have a team procuring raw materials,
workforce to manufacture goods, admin team, finance team, regulators, compliance,
marketing, supply chain, distribution, R&D, and whatnot.

Given the enormity, how do you break a company down into smaller parts and gain
meaningful insights into its functioning? How do we gauge its efficiency? Well, this is
where financial modelling comes into play. Eventually, whatever the company does, it all
boils down to numbers and metrics.
For example, successful operations lead to revenue generation, successful cost management
leads to operating profits. Good financial practice leads to manageable debt levels; good
supply chain management leads to better inventory management. Good dividend policy
strikes a balance between a company’s growth and shareholder value. So on and so forth.
So the approach we take here is that if we can systematically analyze the numbers presented
in the financial statements, perhaps it opens up a window to understand the company better.
When I talk about understanding financial statements, I’m talking about getting into granular
details; we go line by line. Many often assume that a series of simple financial ratio analysis
results in great insights into the company. Yes, to some extent, it does, but we can do a lot
more to better understand the company.

Better understanding leads us to a better insightful investment decision. Think about

Financial modelling as a systematic way to understand the company. Here is what the name,
‘Integrated Financial Modelling’, means –
Financial = Indicates that we are working with the company’s financial statements.
Modelling = Indicates that we are laying down a company’s financials systematically,
connecting these financial statements and subjecting the same to a bunch of equations. The
entire thing tied together is called a model, a model with specific input (financial
statements) and a specific output (valuations).
I
Tools of the craft
To learn, build, and benefit from a financial model; it is mandatory to have some background
knowledge about the following –
o How to read an annual report
o How to read the financial statements of the company – Balance Sheet, P&L,
Cash Flow
o It would be best if you were comfortable working with MS Excel or any other
software similar to MS Excel

1.5 Benefits of Financial Modeling

• 1 – Improved and in-Depth Understanding of a Business
• 2 – Periodic Review of Performance
• 3 – Decide the Fund Requirement & Strategy
• 4 – Business Valuation
• 5 – Risk Minimization
• 6 – Financial Models Generate Quick Outputs
• 7 – Much Accurate Financial Budgets and Forecasts
• 8 – Helps in Business Growth
I
1.6 The steps involved

These are the steps involved in building a financial model –

1. Set up a layout – Perhaps the most crucial aspect of financial modelling. I foresee
myself stressing on this several times throughout this module, so bear with me. A typical
Financial model will have multiple excel sheets within a single workbook. We need to
ensure our Excel workbook is appropriately indexed and formatted and the format stays
consistent across the entire model.

2. Historical Data – A rather painful task, but this need to be done. We need to download
the Annual report of the company we are dealing with, preferably for the last five years.
We need to extract the balance sheet and P&L data from the annual report and input this in
our excel sheet. Of course, we will be dealing with consolidated numbers here and not
standalone data.

3. Assumption Sheet – we create an assumption sheet and dump all our assumptions in
one sheet here. We assume things about the company should be close to reality; the further
we go from reality, the more distorted our model gets. Let me give you an example.
Suppose a company’s revenue is growing at 7% year on year for the last five years; what
do you think will be the growth rate for the 6th year? If we have to assume something, it
has to be in the region of 7%, unless you foresee a significant change. Anything higher or
lower will distort the P&L from reality.

4. Projections – Once the assumptions are complete and the schedules, we project the
balance sheet and P&L for either 3 or 5 years forward. This is one of the crucial steps
while building the model.

5.Cashflow derivation – We derive the cash flow statement using the P&L and Balance
sheet data, called the ‘indirect method’, of cash flow preparation. Note, unlike the Balance
sheet and P&L data, historical data of cash flow is not extracted from the annual report but
instead derived. This step can be tricky, it sometimes works and sometimes does not work
due to its complexity.

6. Ratios – Once all the data is in place, we can quickly draw up ratios and charts for our
model. The ratio sheet will include things like liquidity, solvency, profitability ratios etc.
I
7. Valuations – In the valuation sheet, we deploy the discounted cash flow method of
valuation and finally value the company. Think of this step as including a model within a
model. Of course, we will have sufficient checks and balances in places to ensure we are
not going way off the mark, and even if we do, the sensitivity tables that we develop
should help us get back on track.

These are roughly the steps involved in developing a full-fledged integrated financial
model.

The above is an example of Financial Model of a XYZ company.

We built the Balance Sheet and P&L assumption. Within the P&L assumptions, we dealt
with the revenue of the company as well. We did take a rather simplistic approach to
estimate the revenue of the company. The approach is ok as long as you intend to build a
simple financial model.
However, at times, taking efforts to build a dedicated revenue model of a company pays
off. With a dedicated revenue model, you can identify the key revenue drivers and get
some granular insights into the behaviour of these revenue drivers.
I
Scope & Career in Financial Modeling

Banks, equity research businesses, credit rating agencies, and project finance firms, all hire
financial modelers. They work in the core area because a financial model’s output is
utilized for decision-making and financial analysis both inside and outside the
organization.
Anyone in the organization who deals with business development, vendor development,
operations, profitability, MIS, tendering, and marketing could start the financial modeling
tradition because modeling abilities are easily acquired. Financial Modeling is a highly
sought-after financial subject and the scope of financial modeling is vast.

• The Scope of financial modeling is quickly expanding in the finance business.

• Financial modeling is a key tool for cost-effectively estimating business ideas and
risks.
• Financial modeling is the process of developing a visually appealing depiction of a
company’s financial status.
• Financial models are mathematical expressions that are used to illustrate a
company’s economic performance.

Since the financial world is always changing, it is crucial to be equipped with the tools that
are used in Financial Modeling. Financial Modeling covers a wide range of issues in-
depth, which aids in the smooth operation of a corporation.
I
CHAPTER-2
Introduction of the Organization
1.1 Company Profile
BizExpress Advisors Private Limited is a Private incorporated on 09 April 2021. It is
classified as Non-government company and is registered at Registrar of Companies,
Mumbai. Its authorized share capital is Rs. 500,000 and its paid up capital is Rs. 400,000. It
is involved in Business activities. Directors of Bizexpress Advisors Private Limited are
Bhavna Dilip Doshi and Dhanvant Babulal Shah.

1.2 Ideology
Think of us as the Big4 for MSMEs. The idea is clear, work dedicatedly with MSMEs
to unlock their full potential. Our entire team is focused on ensuring just one thing,
excellence.We look for customer satisfaction has our top priority.

The dynamics of business have changed tremendously, what felt impossible then is now the
normal. Such businesses driven by technology, need professionals that have upgraded
themselves with time. Traditional methods of consulting are just not good enough anymore.

We are therefore building a technology focused one-stop-shop for anything finance and
compliance. From setting up your business to managing it, from GST Registration to Fund
Raise, we are building services that can keep pace with the demands of the modern
businesses. We consider ourselves as your extended partners – a team of professionals
dedicated to driving your business to success.
I
1.3 Service Offerings by Company

Taxation
• GST Registration
• GST Filing
• Income Tax Planning & Filing
• TDS Returns

Fund Raise
• Investor Pitch Deck
• Financial Projections
• Financial Modelling
• Bank Loans / Debt
• CFO Services

Compliance
• Accounting & MIS
• MCA / ROC Compliances
• Start-up India (DPIIT) Registration
• ESOP Planning & Structuring

Set-up
• Private Limited Company Registration
• One Person Company Registration
• Limited Liability Partnership Registration
• Registered Partnership Firm
I
1.4 Some of their trusted Clients:

Why Bizexpress?

• Professional Team
Chartered Accountants, Company Secretaries & MBAs under one roof!
• Timely Execution
We give you a defined timeline and stick to it!
• Honest Advice
Our team will give you honest advice keeping your goals in mind!
• Transparent Pricing
No hidden charges. Period.
• Uncompromised Quality
Globally accepted best-practices used to ensure quality!
I
CHAPTER 3

1.1 My Work in the company:

1. Pick a company that is simple to understand. For example, don’t straight away pick
Reliance Industries. It is complex to model for a first-timer (for an experienced person
too)

2. Between a manufacturing and service-oriented company, pick manufacturing. It is

easier to understand manufacturing concepts, i.e. number of units produced, raw
material, inventory, etc. Services can be a bit vague.

3. The company should have 1 or 2 products that contribute to the revenue. The higher
the number of products, the higher the complexity involved. Think of an FMCG
company; they have 100s of products, which means 100s of dependencies, making it
tough to model such companies.

4. Pick a company that gives out as much information as possible in its annual report.
Just to let you know, Infosys is one of the best companies in terms of information
provided in the annual report. The more information the company provides, the fewer
assumptions you have to make in your model, and that’s good news.

5. Ensure the company you pick is consistent in its annual report. Let me explain this.
Assume, I pick a company which manufactures and sells mobiles phone. The
company operates in India and Sri Lanka. The company states how many units sold
in India and Sri Lanka in its first-year annual report. The company also reports the
revenue generated in both these countries. In the 2nd year annual report, the company
chooses to disclose only the revenue generated from both the countries but decides
not to give the data on the number of units sold. This is an inconsistency in reporting,
and such inconsistencies make it difficult to move ahead with the model

6. Avoid banks, financial services, and NBFCs. They are just too complex and have a
ton of regulatory issues.
I
1.2 Set-up Excel Sheet

1. Open a blank excel sheet and save it with whatever name you’d like –

2. Index Column A and Columb B, expand Column C, and Index column D. ‘Index’ in this
context means just to shrink the column. One of the things I like to do is to get rid of the
gridlines in excel. The gridlines in a financial model can be pretty distracting, especially
when you have so many numbers and formulas to manage. We now enter the years from E2
to I2 to indicate the year’s we are interested in. We now label this sheet as the P&L statement
(in cell A1) as shown below –

I like to keep ‘Profit and Loss statement’, in bold, font size 14. You can see below the line
that I’ve added another line that says that all the numbers stated in this sheet are in INR
Crores unless specified.
What you see above is a basic skeleton of the model. We need a few similar-looking
sheets within the workbook. Remember, we will have other data sheets to include the
Balance sheet, assumption sheet, cash flow sheet, etc. So it’s a good practice to set up
multiple sheets with similar structure in one shot.
I
Annual Report recce
Picking up from the previous chapter, now that we have our excel sheet set up, we will
extract the data from the annual report to our blank excel sheet.
We would be dealing with Relaxo Footwear. Relaxo is one of the largest manufacturers of
footware in the country.
1. As a first step, I download the company’s last five years’ annual report and put these in
a single folder. Usually, a listed company puts up the annual report in the ‘Investors’
section of the website. I’d suggest you download the same from Relaxo’s website.

The years in consideration is Year 1, Year 2, Year 3 up to year 5 etc. It means the latest 5
years of data. So even if you read this 10 years later, it won’t matter.
The data is from the Annual Report, as of March 31st,e. the financial year-end
Year 1A means Year 1 actual data. Year 6P means the year 6 data projected. The projected
data is also as per March 31st. In a sense, this is our vision of how the financial statement
will look like future annual reports.
The idea behind a financial model, quite obviously, is to analyse the historical financial
statements and project them forward. The common practice is to project the number to either
three or five years forward. In this model, we will try and deal with five years projections.

2. Step 2
The idea with the assumption sheet is to lay down each of the financial statements line items
and project it based on our assumptions. So let us go ahead and lay down these line items.
Let me start with the Balance sheet; take a look at these two lines in the balance sheet, i.e.
liabilities and provisions under the current liabilities section –
I

3. To measure historical trends, we usually take the line item as a ratio of another line item.
For the balance sheet, usually, the ratio is measured by keeping the ‘Gross Block’ as the
denominator. Gross block, because the gross block is one of the most oversized balance
sheet items, also sucks up the company’s CAPEX. Liabilities as a percentage of gross blow
hovers between 27% and 35% consistently. So, if I were to figure out what this ratio would
be for Year 6, I can just take the historical average and get a perspective.

With this, we have projected the very first line item of our balance sheet.
• I have used the simple average function here
• The first average, i.e. for the year 6, is the average of Year 2 to Year 5
• The 2nd average, i.e. for year 7, the average is between Year 3 and Year 6
• We are calculating the rolling average here, so at any point, we consider the latest
four years data
• The average which we have calculated hovers within the expected range, i.e. between
27% and 35%, so this is ok.
Whenever you calculate such ratios, it is best if the variance range is narrow. The narrower
the range, the more consistent is the average calculation. The more consistent the average,
the tighter is your model.
I
1.3 P&L Projection
In a typical wedding kitchen, usually, there is one person chopping veggies, one person
grinding the masala, one person frying stuff, another mashing, another preparing the garnish,
and whatnot. Finally, in the end, everything comes together and falls into one gigantic vessel
for the final dish to take shape.
Likewise, so far in this financial model, we have done several things in isolation. But now,
it’s time to tie things up and integrate our model.

Except for the depreciation and interest expense, none of the other line items in the P&L
statement are projected. We projected depreciation from the asset schedule and the interest
expense from the debt schedule. We will now project the rest of the P&L, which is an easy
task.
Starting with revenues, we look at our assumptions for net sales. Recollect, we calculated
the year-on-year growth rate of net sales and then projected the average growth rate.
We know the net sales for the 5th year and the net sales growth for the 6th year (the projected
year); we have to do the math to get the actual value. The math is quite straightforward –
The net sales growth rate for the 6th year = 33.71%
Net sales for 5th year = Rs.1761.12 Crs
Net sales for 6th year = 1761.12*(1+33.71%)= 2354.71 Crs.
I

We need to multiply the percentage in the assumption sheet with the net sales and get the
value.
Next is the calculation of Profit before tax (PBT), which is essentially the difference between
the total income and the total expenses. After calculating the Profit before tax, we need to
calculate the tax amount. The tax amount calculation is a very tricky job, and one would
need the auditor’s help to arrive at the exact value. Since we must continue the model, we
will depend on the averages.
To calculate the average, we have to calculate the tax paid with respect to the PBT in
percentage terms. For example, in Y1, the tax paid is 24.15Cr against the PBT of 71.2Cr. In
percentage terms= 24.15/71.2= 34%
I can now do the same math across Y1 through Y5 and get the yearly percentages. Once the
percentage is in place, I can find out the average across the last five years and treat that as
the tax percentage for year 6. You can do this math in one shot in excel –

We now have the PBT and the provision for current year taxes, PAT of the company is PBT-
Taxes, which I’ve calculated.
The previous year’s Profit is the last year’s closing balance, i.e., ‘balance carried to balance
sheet.’ Yes, we apply the base rule again. We now add up the PAT and the Profit available
for appropriation to get the total corpus available for allocation.
I
1.4 CAPEX
Capital expenditures (CapEx) are funds used by a company to acquire, upgrade, and
maintain physical assets such as property, plants, buildings, technology, or equipment.
CapEx is often used to undertake new projects or investments by a company. Making capital
expenditures on fixed assets can include repairing a roof (if the useful life of the roof is
extended), purchasing a piece of equipment, or building a new factory. This type of financial
outlay is made by companies to increase the scope of their operations or add some future
economic benefit to the operation.

Understanding Capital Expenditures (CapEx)

CapEx can tell you how much a company invests in existing and new fixed assets to
maintain or grow its business. Put differently, CapEx is any type of expense that a company
capitalizes or shows on its balance sheet as an investment rather than on its income statement
as an expenditure. Capitalizing an asset requires the company to spread the cost of the
expenditure over the useful life of the asset.
The amount of capital expenditures a company is likely to have depends on the industry.
Some of the most capital-intensive industries have the highest levels of capital expenditures,
including oil exploration and production, telecommunications, manufacturing, and utility
industries.
CAPEX Formula:

CapEx=Capital expenditures
ΔPP&E=Change in property, plant, and equipment
I
1.5 Asset schedule
A Asset Schedule is the complete listing of all fixed assets that a business owns. This is not
only important for reconciling your Balance Sheet, having one makes it quicker and easier
for an accountant to prepare year end taxes, or if you’re looking to sell your assets.
1)We will now start building the asset schedule for the model. As a first step, let’s set up
our excel sheet.

As you can imagine, I’ve linked the closing gross block for Year 1 as the opening gross
block for Year 2.
The closing gross block for year 2 is 310.58, and this means the assets of the company has
increased –
310.58 – 257.78= 52.80
Hence the CAPEX for year two must be 52.80 Crs. Since the CAPEX has increased, there
has been no disposal of assets. I can add this on excel –

The numbers here will match the numbers stated in the balance sheet, but we have managed
to extract the CAPEX numbers from gross block, which wasn’t explicitly available in the
balance sheet.
Remember, the end objective is to arrive at the netblock of the company. Net block, as you
know –
Gross Block – Depreciation = Net block.
Net block is the difference between the closing gross block and the closing balance of
depreciation.
I
1.6 Depreciation
At this stage, here is how the asset schedule looks –

For Year 5, Depreciation and Amortization stated in P&L is 41.71Cr. The gross block, as
stated in the balance sheet for Year 5, is 538.76Cr. The projected Gross Block for Year 6 is
588.77 Crs. Given this, what do you think will be depreciation for Year 6?
Let us apply the proportion technique.
For gross block worth 538.76Cr, the company reported depreciation of 41.71Cr, which
means –
41.71/538.76 = 7.74%
For Year 6, the gross block value is 588.77 Crs, so what is the depreciation given the same
proportion?
7.74% * 588.77 = 45.58Cr
With this, we can estimate that the depreciation for the next year would be 45.58Crs.
Remember, this number flows into the P&L.
The depreciation value will remain the same at 7.74%.
Instead of taking the previous year’s proportion and assuming the same proportion will hold
for the next year, you can calculate the depreciation to gross block ratio for all the historical
years and then take the five years rolling average for the future years.
It will look something like this –
I
Y6 = Average of Y1 to Y5
Y7 = Average of Y2 to Y6
Y8 = Average of Y3 to Y7
So on and so forth.
The resulting value is the depreciation amount for Year 6. Note, this is a projection that we
are making. I can extend the same math to all the future years and get the depreciation
expense for the year.

At this point, my P&L looks like this –

The current year depreciation stated in the P&L is the depreciation value (in Rupee terms)
applicable only for the financial year under consideration. The company’s finance team
calculates the current year depreciation by factoring in all the assets (gross block) on its
books. The current year depreciation stated in the P&L changes for each year based on how
the gross block changes.
I
1.7 Cashflow activities
A company can be looked at from the perspective of its activities. Broadly speaking, the
activities are –
1. Operating activities
2. Investing activities
3. Financing activities

Consider Bajaj Auto, for example; what does the company do? It manufactures two and
three-wheeler vehicles, sells these vehicles, and services these vehicles. The company needs
to invest in plants, machinery, and equipment to carry out the operations. To finance the
operations, it may (or may not) needs funds from external sources. If the company borrows
money, they have to repay. Then, of course, from the profits, dividends are distributed.
Each of these categories either generates cash or consumes cash. For example, consider the
inventories of a company. The inventory of a company is directly related to the company’s
operations. If the company’s inventory has increased compared to the previous year, then it
means that more money is stuck in terms of finished goods. Hence, inventory (which is an
operational activity) has consumed cash. On the other hand, if the inventory is less in year
two than in year one, inventory has generated cash or conserved cash.
Then, by summing cash flow from different activities, you should generate the company’s
cash flow statement and get the company’s cash position.

1. Cashflow from operating activity

The idea here is to find out if the company’s operation has generated cash or not. We start
with the PAT, add back depreciation, and then add the net change in working capital by
considering each line separately.
Here is what the cash flow from operating activity looks like –
I
2. Cashflow from investing and financing activities
The first thing we need to consider while dealing with investing activities is the CAPEX
spend. If the CAPEX spend increases, then it consumes cash, and if the CAPEX spend
decreases, it generates cash (or conserves cash). We can get the CAPEX data from the asset
schedule.
Here is what the cash flow from Investing activities looks like –

The other two line items, i.e., capital work in progress and investments, are straightforward,
and we get that from the balance sheet. The total of all the four-line items is the cash flow
from investing activities.

3. Cashflow from Financing activities

Past service cost of employee benefit is a one-time cost specific to this company. Costs such
are one time in nature should be dealt with slightly differently. Here, you don’t consider the
difference between the two years; instead, take the expense applicable for that year directly.
Dividends, too, are a yearly expense, and the company may even decide not to pay dividends
for a year. So all such one-time costs should be treated as is. I’ve highlighted the same in
the formula bar above.
We have now calculated the cash flows from all three activities.
I
1.8 Valuations basics
We are at the last stage in our Financial Modelling journey. As the last step, we have to
build a valuation model, which will sit within the integrated model that we are building. A
valuation model helps us measure the value we are willing to pay for a given business. There
are many ways to build a valuation model, but regardless of the approach you take, the final
output results in estimating the worth of the company on a per-share basis.

With the valuation exercise, the idea is simple, we value the company and arrive at the share
price. We refer to this as the fair price of the company’s stock. Fair price because we have
considered everything that matters in our model (remember all the assumptions and
schedules). We then compare the fair price of the company with the actual market price of
the company traded on the stock exchange and conclude as –
• Overvalued, if market price > fair price
• Undervalued if market price < fair price
• Fairly valued if market price = fair price

Valuations in the context of investments help us understand the price we are willing to pay
to acquire a portion of the business. There are three main techniques based on which we can
value a company, they are –

• Relative valuation
• Option based valuation
• Absolute valuation
I
1) Relative valuations
The relative valuation is based on the theory that if there are two identical companies in the
market, then their valuations should be similar too. By identical, I mean the companies you
compare should be similar in terms of business, products, size, geographic spread,
financials, etc, regulatory landscape, etc.
Let’s put this in context. Assume there are only 3 companies in the country that
manufactures cars. The Profit after tax for these companies along with the respective stock
prices are as below –
Company 1 – PAT is Rs.100, a stock trading at Rs.1005 per share
Company 2 – PAT is Rs.220, a stock trading at Rs.2185 per share
Company 3 – PAT is Rs.75, a stock trading at Rs.785 per share

If you do a simple ratio check i.e. dividing the company’s stock price by its profitability
(measured in terms of PAT), we get the following results –
Company 1 : 1005/100 = 10.05x
Company 2 : 2185/220 = 9.93x
Company 3 : 785/75 = 10.46x
From the above, we know that the industry as such is valuing the car industry at roughly 10x
its earnings. Now, assume a 4th company enters the market with similar dynamics. The
earnings of this company are Rs.300, what is the likely stock price?
Well, by the method of relative valuation, we can assign roughly 10x the earnings, so the
stock price should be around Rs.3000. However, if the stock price is higher or lower than
Rs.3000, then we can conclude that the stock is overvalued or undervalued respectively.
While I’ve considered just one ratio to illustrate the relative valuation method, there are
several other ratios that you can consider.

Most investors find conducting relative valuations on companies easy since it is very
intuitive and relative to the industry. But there are a few limitations with relative valuations.
One, the markets themselves could be valuing the industry wrongly by sometimes assigning
very high valuations to companies (remember the dot com era where all stocks were highly
valued) or sometimes assigning super-low valuations. Super low valuations could be
because the market as a whole can find it difficult to understand business models.
The other problem is that there are no two companies that are the same. In reality, each
company is different, and these differences have an impact on valuations.
I
2) Option based valuations

Let us talk about a company, maybe an EV car manufacturing company. The company
announces that they have set up an R&D to develop an EV car that can run 2,000 Kms per
single charge. The announcement is phenomenal as most EVs can run up to 450 km per
charge.
How will the market value such an announcement?
Well, for now, it is just an announcement and the market knows that the R&D experiment
can fail. However, if it’s a success, the value of the company can grow multi-fold. In other
words, the value of the company is contingent upon a certain event, the event happens to be
the success of the R&D experiment.
We can generalize this – ‘the value of a company should be X provided Y happens’

Remember this equation ‘ Intensive Value of a Call option = Max[(Spot-Strike), 0]. If you
are familiar with it, good, else don’t worry about it as long as you get the point that the value
of the company (or option) will be Rs.XYZ provided ABC happens.

Given this, if you were asked to value such companies, how will you value them? Well, you
can value the basis of the framework on how you value options. Such a valuation technique
is called the ‘option-based valuation technique’.

Option based valuation technique is a very niche technique and cant be used across all
companies. But this is something you should be aware of. Many tech companies in the US
are valued based on the option-based valuation technique. Probably in India too, this may
become popular. For example, think about a company that has an internet business. Their
business model can be dependent on acquiring n number of customers of which a certain
percentage of them will turn into paying customers.

We will now move to the last valuation technique, perhaps the most popular one called the
‘Absolute valuation’, of a company.
I
3) Absolute valuations
If you were to value any company, you can either value its assets or its Equity. If you choose
to value the assets of the company, then you are essentially valuing the overall company and
that’s called the ‘Enterprise Value’, of the company, also called the value of the firm.
However, if you choose to look at only the equity portion, then it is just that, you are valuing
the company from an equity holder’s perspective because the value of equity is what matters
to the shareholders.

When it comes to measuring the value of a company (either via the enterprise or equity
holders), you need three things –
• Cashflow estimation
• Discount rate
• Timing of cash flow
The cashflow is either –
• The cash flow to the firm/ enterprise or
• The cash flow to the equity holders
Once you identify the cash flow (past and future), you need to discount the cash flow.

Since an enterprise will have both debt and equity holders, the discount rate should reflect
the expectation of both these parties. The blended discount rate is called, ”Weighted average
cost of capital”, or just WACC. We will discuss more on WACC in the subsequent chapters.
Lastly, we need to know the timing of the cash flow so that we can discount these cash flows
appropriately. Of course, you will know what I’m referring to here if you are familiar with
the concept of net present value. Over the next few chapters, we will build the valuation
model step by step and integrate it within the main model.

At its core, three key inputs drive the absolute valuations –

o The cashflow
o The timing of the cashflow
o The rate at which the cash flow gets discounted
I
4) Free cash flow calculation
To calculate the free cash flow (FCFE or FCFE), we need to start all over from the P&L
again.

The company’s business operations ideally should generate positive cash, which is also the
company’s revenue. The company pays off the cost of goods sold from the revenue
generated. After paying for the cost of goods sold, the company pays the sales and general
administrative costs. Usually, both get clubbed as the ‘expenses’ of the company. After
adjusting for this, the company is left with ‘Earning before the interest and Tax’ or the EBIT.
EBIT is one of the key margin metrics we use to analyze a company.
From EBIT, interest is paid to get us to the Profit before tax or PBT. From PBT, the company
pays the taxes due for the financial year and finally arrives at the company’s bottom line,
i.e., Profit after taxes or PAT.
So we have –
PAT + Depreciation + Amortization + Deferred Taxes
The balance sheet equation of working capital is –
Working capital = Current Assets – Current Liabilities

Considering both the above, our free cash flow equation looks like this –
PAT + Depreciation + Amortization + Deferred Taxes – Change in working capital –
change in fixed asset investments.
I
Weighted average cost of capital

Weighted average cost of capital (WACC) represents a firm’s average after-tax cost of
capital from all sources, including common stock, preferred stock, bonds, and other forms
of debt. WACC is the average rate that a company expects to pay to finance its assets.
WACC is a common way to determine required rate of return (RRR) because it expresses,
in a single number, the return that both bondholders and shareholders demand to provide
the company with capital. A firm’s WACC is likely to be higher if its stock is relatively
volatile or if its debt is seen as risky because investors will require greater returns.

Perhaps the best way to understand WACC is by taking an example. Assume a company has
Rs.300Crs in debt and Rs.200 Crs in Equity. Equity folks expect a 12% return, while debt
holders expect 8%.
Given the capital structure, what is the blended rate or the weighted average cost of capital?
We know that WACC is = Weight of debt * return expectation of debt holders + weight of
equity * return expectation of equity holders.
The total capital = Debt + Equity
= 300 + 200
= 500 Crs
Weight of debt = 300 / 500
= 60%
Weight of equity = (1-weight of debt)
= 1- 60%
= 40%
Hence, the blended rate or WACC is =
= 60% * 8% + 40%*12%
= 9.6%
WACC is commonly used as a hurdle rate against which companies and investors can gauge
the desirability of a given project or acquisition.
I
Discounted Cash Flow Analysis (DCF)
WACC is the weighted average return expectation of debt holders and equity holders. We
will use the WACC to discount the cash flows.
We start the calculation with EBIT and take the tax shield effect on EBIT. EBIT is earnings
before interest and taxes; hence to calculate EBIT, we subtract all the expenses from total
income, except the interest.

We multiply EBIT with (1-tax rate) to factor in the tax shield effect on EBIT. To this, we
add back all the non-cash charges and deduct working capital and CAPEX charges to arrive
at the free cash flow to the Firm. I’ve made these calculations in excel, and here is how my
sheet looks now –
I

Notice that I’ve indexed columns E,F,G, and H to ensure I link columns J to N with years 6
to 10, just like in the other sheets. You are free to format this sheet in whatever way you
think makes sense.
EBIT and depreciation numbers come from P&L. The working capital and CAPEX numbers
come from the cash flow statement.

Terminal Growth value

We now have the free cash flow to the Firm, projected up until the next five years, i.e., till
year 10. However, this does not mean the company will stop generating free cash flow after
five years. We assume that the company will not only continue to exist but will also continue
to generate free cash flow. The rate at which the cash flow grows is called the ‘terminal
growth rate,’ which is usually equivalent to the long-term inflation value of the country.
Here is the calculated value –

The terminal value is a big number and has an impact on the final valuation of the
company.
I
1.7 Share price
We sum up all the present value of the future free cash flow, i.e., from Year 6 to 10, along
with the current value of the terminal value to arrive at the ‘Enterprise Value. We deduct
the present-day debt from the enterprise value and add the present-day cash to give equity
holders the free cash flow.
The present-day debt and cash value come from the balance sheet.
And, here you go –

The share price is Rs.300. What does this mean?

The price you see here is an outcome of the entire valuation exercise. We have made many
assumptions here, and if these assumptions are made intelligently, then with some
confidence, we can conclude that Rs.300 is the fair value of the stock. You can now
compare the stock’s market value on the stock exchanges and decide to buy or wait. For
example, if the stock is trading at Rs.425, then you know that it is overvalued compared to
its fair value; hence you can avoid buying the stock.
If the stock is trading at Rs.225, the stock is undervalued, and you can go ahead and invest
in the stock. Or if the stock is trading at Rs.300, it is said that it is fairly valued.
I
Closing thoughts

The model we have built is integrated, meaning that any change in any number in this
model will impact the share price.

For example, in the assumption sheet, I’ll change the material consumed as a percentage of
sales for Year 6 to 60% from 65%. The share price will change to Rs.462 from Rs.300.
Or I can change the terminal growth rate to 4.5% from 4%, and subsequently, the share
price changes to Rs.323. I encourage you to make these changes and see for yourself,
which is the beauty of this model. All the sheets and numbers are linked, and any
difference across the sheet will result in the final output.

You can make these changes when you think the difference is justified, which brings me to
my next point.
Building a financial model is pretty straightforward. A seasoned modeler will probably
create a good model in a few days. But what is essential is to keep the model up to date.
Once you build a model, track the company closely, especially the management interviews
and statements. Whenever new information comes, make an appropriate change in the
model.
For example, during the following quarterly result announcement, the company may say
they want to slow down their CAPEX spending. Immediately, tweak your model and
adjust for a lower CAPEX spend, and accordingly, the share price changes and gets re-
rated. Maintain a separate sheet in the workbook detailing the reasons based on which you
made the changes. The sheet acts as your working notes.

One last thing before I end this chapter and module – the final output, i.e., the share price
is Rs.300. That does not mean, Rs.300 is strictly the fair value of the stock. The share price
is an output of a model we have built, and the model is undoubtedly prone to inadvertent
errors. Therefore, you need to factor in model errors. I’d assign a 10% band as a modeling
error, which means I’ll consider the stock’s fair price anywhere between Rs.270 to Rs.330.

I’ll be happy to buy the stock anywhere within this range, preferably at the lower end, as it
gives me some margin of safety.
I
Learning Objectives from the Internship

1. To get practical knowledge of working for firm.

2. Exploration of the finance industry
3. Gain Knowledge of new ways of doing business
4. Get insights of practicality of finance work
5. Company Insights
6. Usage of MS Excel for financial projections
7. Learn about Balance sheet, Cash Flows
8. Reviewing schemes
9. Exploration to different departments of the organisation
10. Develop organizational skills to complete the project in a timely manner.
11. Crisis management
12. Importance of customer service
13. Create Soft skills
14. Developed skills required to interact with senior management and staff (for example:
Controller, CFO, managers, senior associates, staff, and office staff) in a professional
manner.
15. Learn internal and external financial reporting procedure used by the company.
I
Conclusion
The internship opportunity I had with BizExpress was a huge step in my professional career.
I have developed number of skills and also gained a lot of knowledge in the financial word.
One of the goals of the finance internship is to gain transferable skills that interns can apply
to the organization they're interning at or any other they'll work with in the future. Unlike
other highly differentiated occupations, finance personnel are a necessity in every
organization, the only difference being that the accounts of for-profit and nonprofit
organizations differ. The financial operations of government and its agencies are also
distinct from those of regular organizations.

I also got experience and exposure in the corporate world which is great in the future.
Meeting new people inspired and motivated me to learn various skills and helped me in
progressing my resume. I got to connect with potential mentors, learn financial trade secrets,
and build relationships with coworkers with a range of expertise. It’s also a plus as it’s a
paid finance internship!

Financial Internships is one of the best ways to build a professional network. While you’re
working as an intern do connect from managers to other colleagues, this will help you to
grab the job opportunities after completion of your graduation.

Financial models are used by corporations almost every day. These models help while
making several key strategic decisions. The process of model creation forces the business to
think about and list down the drivers which impact the various aspects of the business. The
process also forces the business to think about the various changes that may happen
internally as well as in the external environment. Hence, it would be fair to say that
companies which create financial models are somehow forced to do more due diligence as
compared to their counterparts. This creates a better understanding of the business. Creation
of financial models, therefore, has a spillover effect which leads to a better understanding
of the underlying business.

At last I appreciate all my teachers and college for encouraging me to look out for an
internship, which eventually gave me great exposure to the corporate world.
I

THANK YOU