financial modeling amit kumar singh
TRANSCRIPT
Module-I
INTRODUCTION
Financial Modeling using Excel
P a g e | 2
Contents
1.1 Objectives
1.2 Introduction to Financial Models
1.3 Steps in creating Financial Models
1.4 Application of Finance Functions in MS Excel
1.5 Capital Budgeting Functions in MS Excel
1.6 Bond Mathematics in MS Excel
1.7 Creating a Dynamic Model in MS Excel
1.7.1 Valuation of Option Contract
1.7.2 Bankruptcy of a Firm
1.8 Summary
1.9 Questions to discuss
1.10 References
1.1 Objective of Financial Modeling using MS Excel
By the end of this Module, you should be able to:
Definition of Financial Modeling
Steps need to take care before creating dynamic Models in Excel
Solving the cases on Capital Budgeting, Bond Mathematics etc in MS Excel
Understanding Complex Financial Models like Binomial Tree, Black Scholes Merton
Model in MS Excel
Financial Modeling using Excel
P a g e | 3
A financial model is a dynamic research based mathematical application that helps to establish
relationship among different variables of financial problems. It helps in making projections. There
is a need to understand the relationship among variables in the form of independent variable and
dependent variables. While creating model there is need to create as much interdependencies as
possible. As more relationship can be established among variables the more useful the model will
become. Along with variables a model include parameter whose specifications is entered by user
The difference is that their values are expected to remain constant or change infrequently within
the context of the model. For example in calculation of Taxable Income, the tax rates and taxable
brackets are parameters because these variables have to be provided for the model to work, these
values are not expected to change frequently. The variables whose values are calculated by the
model are called calculated variable or dependent variable. Some of them may be intermediate
variables, calculated for use in other calculations. Others are of primary interest to the user and are
the output variables of the models.
1.3 Steps in Creating a Model
The Financial Models are created either by using Excel or VBA and a systematic approach is
required. A systematic approach always involves planning ahead and it is time consuming.. Excel
and VBA is a user based application tool which can be edited based on changing conditions. So
while working on financial model certain steps needs to be followed to get a systematic solution.
The following are the steps for creating model
Step 1: Structuring the definition of Problem
Step 2: Understanding the input and output variables of the Model
Step 3: Determine the application and feasibility of model
Step 4: Understanding the Financial and Mathematical Aspects of the Model
Step 5: Re-designing and updating the Model and working on its background application
Step 6: Testing the Model
Step 8: Protect the Model
Step 9: Document the Model
Step10: Updating the model whenever required
Financial Modeling using Excel
P a g e | 4
1.4 Application of Finance Functions in MS Excel
Some of the basis excel financial functions explained in this module are
FV
PV
PMT
IPMT
PPMT
1.4.1 FV Function
MS Excel is used to calculate the Future Value of money. The syntax for the FV function is:
Illustration 1: Mohit has deposited Rs 4000 in his saving bank account and he wishes to withdraw
the money after 4 years. The annual savings bank account rate of the bank is 4%. Compute the
amount of interest received by Mohit.
The interest rate can be calculated using the formula πΉπ = ππ Γ (1 + πππ‘π)π in paper. In excel
the same can be calculated by using the FV function.
Figure 1.1 : Calculation of FV based on mathematical formula
When we write FV in MS Excel, then MS Excel will ask for rate=4%, nper = time = 4 year, PV=
-4000, PMT=0 (applies when annuity is there), type=0 (itβs for annuity concept, by default excel
always takes 0). Both the methods would yield answer of Rs 4679.43
FV(rate,nper,pv,fv,type)
Financial Modeling using Excel
P a g e | 5
Figure 1.2 : Calculation of FV based on FV formula in Excel
The answer obtained is negative in nature as this is bug in FV, PV calculation in MS Excel so
whenever we are using these function always put β-βover PV or FV value
1.4.2 PV Function
Similarly we can use the formula of PV to determine the present value of money. The syntax for
the PV function is
Illustration 2: Imagine after 8 year and 4 month Raj will get Rs 1 lakh from Sohan. Suppose the
interest rate in market is 8% p.a. So, the present value of the same can be determined by using PV
formula in Excel or manually by using ππ =πΉπ
(1+πππ‘π)π
Figure 1.3 : Calculation of PV using mathematical formula
PV(rate,nper,pmt,fv,type
)
Financial Modeling using Excel
P a g e | 6
Using excel, PV is calculated as shown below:
Figure 1.4 : Calculation of PV using PV function in excel
Both the methods give the answer Rs 52672.04
1.4.3 RATE Function
The syntax for Rate function is
Rate can be calculated manually through mathematical formula as
((πΉπ
ππ)
1
πβ 1) Γ 100 = π ππ‘π%
Illustration 3: Ram has Rs 1000 with him and it will get double in 4 years as he has taken a NSC.
The return on investment is calculated as:
Figure 1.5 : Calculation of Rate using mathematical formula
The answer is 18.92%
RATE(nper,pmt,pv,fv,type,gue
ss)
Financial Modeling using Excel
P a g e | 7
Using excel, PV is calculated as shown below:
Figure 1.6 : Calculation of rate of return based on Rate formula in Excel
The answer is 19% (rounded off)
1.4.4 NPER Function
The syntax for NPER Function is given as:
Similarly, time period can be determined mathematically through log(
πΉπ
ππ)
log(1+π%)= ππππ. So, by using
NPER function.
Let us take the same illustration 3. The NPER function in excel is shown below:
Figure 1.7 : Calculation of period of investment based on nper formula in Excel
The NPER value is 4.13 i.e. approximately 4 years.
1.4.5 PMT Function
The PMT function returns the loan payment (principal plus interest) per period, assuming constant
payment amounts and a fixed interest rate. For example, determining the EMI of a loan. The syntax
for the PMT function is
NPER(rate,pmt,pv,fv,type)
Financial Modeling using Excel
P a g e | 8
Illustration 4: Raj has taken a loan of Rs 10,000 at the rate of 5% annually. The term of the loan
is 5 years. The monthly payment is Rs 229.34
Figure 1.8 : Understanding PMT formula in Excel
Now, since EMI calculation uses PV of annuity so we had kept FV=0 in PMT calculation.
1.4.6 IPMT AND PPMT Function
The IPMT function returns the interest part of a loan payment for a given period, assuming constant
payment amounts and a fixed interest rate. The syntax for the IPMT function is:
The PPMT function returns the principal part of a loan payment for a given period, assuming
constant payment amounts and a fixed interest rate. The syntax for PPMT function is given as:
PMT(rate,nper,pv,fv,type)
IPMT(rate,per,nper,pv,fv,type)
PPMT(rate,per,nper,pv,fv,type)
Financial Modeling using Excel
P a g e | 9
Illustration 5: Mathew has taken a loan of 4 lakhs at an interest rate of 13.25%. The loan is to be
paid in 8 equal installments. The equal installment is determined by using the PMT function. The
interest is calculated on the principal left with fixed rate of 13.25%.
Whenever installment is paid, it is always more than the interest which thereby reduce the principal
as extra is used to reduce the value of principal and thereby making the net principal = 0 at the end
of the period.
The Interest is computed using the IPMT function of excel.
In the given calculation of PPMT and IPMT function, we need to put 13.25%/8=rate, per=1 for 1st
period, 2= for 2nd period β¦β¦.nper=8(total period), PV=4,00,000 (total loan value), fv=0,
type=0(annuity).
Figure 1.9 : Loan Amortization Schedule in Excel
Financial Modeling using Excel
P a g e | 10
The function using excel formula is shown in the screenshot below:
Figure 1.10 : Use of IPMT and PPMT formula in Excel during loan amortization
1.5 Capital Budgeting Functions in MS Excel
Net Present Value Function
NPV is the foremost criteria of Capital Budgeting technique to decide whether to accept or reject
a project where the estimation of future cash flow is done through Financial Modeling techniques
and then the same is discounted by WACC (Weightage Average --Cost of Capital) to determine
the present value of Cash Flow. If the sum total of cash inflows is more than the Initial Investment
or (β Initial Investment+ βPV of future Capital Expenditure) then the project is accepted otherwise
rejected.
Illustration 6: Suppose the Company XYZ is initiating a new project requiring an Initial
Investment of 50 lakh Rupees. Based on the riskiness of project, the proposition of funds raised
through debt and equity, individual cost and taxation rate the estimated WACC is 15%. The
expected life of project is 7 year. Expected Cash Flows from the project will be:-
Year Expected Cash Flows (in Rupees)
1 5 lakhs
2 5 lakhs
3 20 lakhs
Financial Modeling using Excel
P a g e | 11
4 8 lakhs
5 12 lakhs
6 5 lakhs
7 7 lakhs
Note- Expected Cash Flows can be Operating Cash Flow or Capital Free Flow or Free Cash Flow)
Now in order to calculate NPV manually or through Excel Formula the calculation will be done
on Excel Sheet as shown in the screenshot below.
Figure 1.11: Working on NPV Calculation
Like this PV of cash flows for individual years can be determined.
Financial Modeling using Excel
P a g e | 12
Figure 1.12 : Determining the sum of PV of cash flows in NPV calculation
As the NPV is negative, we have to reject the project
The syntax for the NPV function in excel is
NPV function is shown in the screenshot below
Figure 1.13 : Working of NPV formula in Excel
When we write =NPV() in excel it calculated the PV of cash flows from year 1 to β¦β¦.n, since
initial investment happened on year 0 so it was put outside bracket where cash inflows are
represented with β+β sign where cash outflow with β-β sign.
XNPV Function
When the NPV is calculated, the period of receivable of cash flows is assumed to be constant. In
reality it can be different. For uneven period cash flows, XNPV function is used.
NPV(rate,value 1, value 2,β¦)
Financial Modeling using Excel
P a g e | 13
The syntax for XNPV function is:
Illustration 7: The below table shows the cash flows for the following dates. The WACC is 14%.
Year Expected Cash Flows (in Rupees)
27/6/2007 -70000
5/12/2008 12000
2/2/2009 15000
9/5/2011 18000
22/8/2012 21000
1/8/2014 26000
Figure 1.14 : Calculation of NPV of uncertain cash flows based on XNPV formula in Excel
IRR Function
IRR is the minimum return expected on an investment under the condition when βPV of future
cash flows=Initial Investment or at what discount rate NPV=0. To determine IRR manually, trial
and error method and Interpolation is appropriate while in excel IRR function is appropriate.
Excel tries to calculate the IRR value until the result is accurate within 0.00001%. If after 20 trials
it is not able to calculate the accurate value, it will show #NUM error. The syntax for IRR is:
XNPV(rates,values , dates)
IRR (values , guess)
Financial Modeling using Excel
P a g e | 14
Guess is the number that we guess is close to the result of IRR. If guess is omitted, it is assumed
to be 10%. Values are the array or reference to cells that contain number for which we want to
calculate the IRR. Values must contain at least one positive value and one negative value to
calculate the IRR. IRR uses the order of values to interpret the order of cash flows. Please make
sure that the sequence of outflows and inflows in logical, desired manner.
Illustration 8: For example a Capital Budgeting decision requires an initial investment of Rs
70,000 creating a useful life of 5 year generating cash flows of Rs 12,000, Rs 15,000, Rs 18,000,
Rs 21,000 and Rs 26,000 in 5 successive years.
Figure 1.15 Working on IRR in Excel
The IRR comes out to be 0.09.
In the example I have Guess as 15%, irrespective of whether we write guess or not there will be
no change in the final answer.
There are certain drawbacks in the IRR calculation as Internal Rate of Return assumes that the
cash flows from the project are reinvested at the IRR, also mathematical interpretation of IRR get
complicated whenever the series of cash outflows and inflows happens regularly. It leads to
Polynomial Equation creating multiple values of IRR which leads to confusion.
For Example, consider the following cash flows:
Financial Modeling using Excel
P a g e | 15
Year Future cash flows
0 -10,000
1 6,000
2 -2,000
3 14,000
Now, if we want to determine the IRR for this scenario, it can be represented as
10,000 = (6000
1 + πΌπ π ) β (
2000
(1 + πΌπ π )2) + (
14000
(1 + πΌπ π )3)
If we try to solve this Polynomial Equation, we will get multiple values of IRR. So, in any capital
investment decision if there are multiple cash outflows then it would be appropriate to use MIRR.
MIRR
MIRR Returns the internal rate of return where positive and negative cash flows are financed at
different rates. The syntax of MIRR is:
Here βvaluesβ represent the array of the project's cash flows, βfinance_rateβ is the relevant cost
of capital, and βreinvest_rateβ is the rate of return at which the project's cash flows are expected
to be reinvested. Usually WACC= finance rate while reinvest rate can be any appropriate rate of
return depending on the avenues for the investment favourable.
Illustration 9: Consider an example where a company XYZ is evaluating a project that has the
following cash flow associated with it:
Year 0 1 2 3 4 5 6
Cash Flows -120 80 -20 60 80 -40 120
Here we assume finance rate = reinvest rate = cost of capital = 15%. In order to calculate the
MIRR, we have to determine the β of PV of a cash outflows and β FV of all cash inflows. Since
MIRR(values, finance_rate,reinvest_rate)
Financial Modeling using Excel
P a g e | 16
finance rate is used for discounting and reinvest rate for compounding, here both we assume as
same.
Year 0 1 2 3 4 5 6
Cash Flows -120 80 -20 60 80 -40 120
- 15.1228 = 20
(1+15%)2
-22.87 = 40
(1+15%)4
β=120+15.1228+22.87=157.9928
Year 0 1 2 3 4 5 6
Cash Flows -120 80 -20 60 80 -40 120
= 120. (1 + 15%)0 = 120
= 80. (1 + 15%)5 = 160.91
60. (1 + 15%)3 = 91
= 80. (1 + 15%)2 = 106
β of FV of cash inflows= 477 million Rs
Figure 1.20 : Understanding MIRR calculation
Now, use the formula βFV of cash Inflows=β of PV of Cash Outflow. (1 + ππΌπ π )π
477 = 158. (1 + ππΌπ π )π
Financial Modeling using Excel
P a g e | 17
= (477
158) = (1 + ππΌπ π )π
3.0189 = (1 + ππΌπ π )6
log(3.0189) = 6 log(1 + ππΌπ π )
0.47984 = 6 log(1 + ππΌπ π )
0.079974 = log(1 + ππΌπ π )
πππ‘ππππ(. 079974) = 1 + ππΌπ π
1.20219 = 1 + ππΌπ π
ππΌπ π = 20.219%
The same calculation can be done on MS Excel by using MIRR function.
Figure 1.16: MIRR calculation in Excel
Whenever IRR is calculated we assume the future cash flows will occur after every regular interval
of time, in reality probability to get periodical cash inflow is very less, so in case of non periodical
cash flows XIRR function is used in MS Excel.
XIRR Function
XIRR function returns the internal rate of return for a schedule of cash flows that is not necessarily
periodic. The syntax for XIRR function is:
XIRR(values, dates,guess)
Financial Modeling using Excel
P a g e | 18
Illustration 10: The table below gives the cash flows for the respective dates
Year Expected Cash Flows (in Rupees)
27/6/2007 -70000
5/12/2008 12000
2/2/2009 15000
9/5/2011 18000
22/8/2012 21000
1/8/2014 26000
Figure 1.17 : XIRR calculation in Excel
1.6 Bond Mathematics in MS Excel
Debt Instrument brings an obligation to the borrower to pay constant coupon at the regular interval
of time till its maturity. This makes the feature of coupon similar to annuity. So we can use
mathematical application of PV of annuity to solve the bond arithmetic. Coupon is always
calculated on the face value. Based on cash inflows (coupon+ face value), cash outflows (price)
the required rate of return can be determined which is determined as current yield and yield till
maturity (YTM).
Current Yield does not follow time value of money while YTM follows time value of money where
minimum return on investment on debt instrument is determined assuming the debt instrument is
Financial Modeling using Excel
P a g e | 19
holds till its maturity. The calculation of YTM is similar to IRR calculation. Price of the bond
fluctuates based on demand-supply, macroeconomics and microeconomics scenarios around the
issuer of debt instrument. Price at which the debt instruments trades in market is represented as
clean price while the adjustment of accrued interest on the clean price is called dirty price.
Accrued Interest is calculated based on day count convention following by different countries,
Price of the bond is inversely proportional to the YTM where the rate of change of change in price
with respect to change in YTM is determined through Duration. Duration can be Macaulay
Duration and Modified Duration. While the relationship between prices of bond with respect to
YTM is convex in nature instead of linear relationship, which is explained mathematics through
Convexity. Bond Arithmetic in Excel can be simply explained through this example.
Illustration11: Suppose a 12.5%, 6 year bond pays coupon annually matures at Rs 1000. The
YTM on this bond is 15%. Present Value of bond or Price of bond can be done manually through
PV of Annuity (Coupon) +PV of Face Value or in by using PV function in excel.
Coupon is always paid on Face Value. 12.5% on Rs 1000 will be Rs 125.
Financial Modeling using Excel
P a g e | 20
Figure 1.18 : Calculation of Price of bond mathematically in Excel
The same can be calculated by using PV function in MS Excel in one step.
Figure 1.19 : Calculation of Price of bond through PV formula in Excel
Here, rate=YTM, nper=life of bond, pmt=coupon value, fv=face value, type=0 (all case of
annuity), if we put we will price of bond =RS 905.387. Similarly for the same problem if we want
to determine YTM, we can use RATE function.
Figure 1.20 : Determining YTM of bond through RATE formula in Excel
Financial Modeling using Excel
P a g e | 21
Similarly the life of bond is determined through NPER function, value of coupon through PMT
function, For Face Value of Bond we can use FV function.
Now in reality when the bond is trading on Exchange the price at which it is trading is called Clean
Price which can be determined through discounting of Cash Flows. This is determined on actual
basis by using PRICE function in MS Excel. The syntax for PRICE function is
Here settlement date = date on which the bond is sought to be valued
maturity date = date on which the bonds matures
rate = rate at which coupon is paid
Yld=required rate for valuation
Redemption = the redemption value of bond
Frequency = number of coupon in a year
Basis = day count convention to be used.
Illustration 12: In order to value the 11.75% 2016 bond, maturing on April 16, 2016, and on 06th
July, 2014, using day count convention of 30/360 and the required yield of 15% where the coupon
is paid semi annually.
Therefore price function is given as:
= price(16/04/2016,06/07/2014,0.1175,0.15,100,2,4)
When we are writing Date it should in Date format, so better use =DATE function in MS Excel.
Price(settlement, maturity, rate, yld, redemption, frequency, basis)
Financial Modeling using Excel
P a g e | 22
Figure 1.21 : Use of PRICE function in Excel
So, the price comes Rs 95.03. This is the clean price. Now when adjustment of accrued interest is
made on it we will get dirty price. To determine Accrued Interest we need help of Day Count
Convention. There are four popular Day Count Conventions:-
Day Count Conventions Description
30/360 This convention considers each month including February as
having 30 days and year consisting of 360 days.
Actual/360 This convention counts the actual number of days in a month,
but uses 360 as the number of days in a year.
Actual/Actual This convention uses the actual days in the month and the
actual number of days in a year.
Actual/365 This convention uses the actual number of days in a month and
365 days in a year.
When the trading of debt instruments happens, investors buy and sell debt instrument. Transaction
happens on dirty price where calculation of accrued interest is very important. There are certain
excel functions used to compute accrued interest.
Financial Modeling using Excel
P a g e | 23
Financial Modeling using Excel
P a g e | 24
Illustration 13: Consider a hypothetical bond having coupon rate of 9.5%. The bond will mature
on 23rd March 2016. Suppose the coupon is paid semi annually and it follows the day count
convention of 30/360 and we want to do valuation of bond on 06th July, 2014. Then to know the
various information regarding coupons we can use multiple functions in MS Excel.
In order to know the next coupon period COUPNCD function is used. The next coupon period
is 23rd Septemberβ14.
If we want to determine the previous coupon period which would be definitely be six month
old, i.e. 23rd Septemberβ14, the function COUPPCD is used.
Since in the day count convention we assume 360 days in a year. Therefore, if we want to
know how many days left for the next coupon date, COUPDAYSNC function is used.
Similarly in order to determine the number of days from the previous coupon to settlement
can be determined using COUPDAYBS function.
The below screenshot shows all the above four coupon functions:
Financial Modeling using Excel
P a g e | 25
Figure 1.22 : Use of various coupon functions in excel
ACCRINTM Function
Accrued Interest is the compensation the buyer needs to pay to seller over the current price of the
bond to compensate the time value of holding the bond beyond the last coupon date. ACCRINTM
function is used to compute accrued interest. The syntax for ACCRINTM is:
In order to calculate the Accrued Interest we need to know the Issue Date, First Coupon Payment
Date, Face Value, Frequency (the number of times the coupon is paid within in a year), Basis (Day
ACCRINTM( issue, settlement, rate, par, basis)
Financial Modeling using Excel
P a g e | 26
Count Convention), Calculation Method if it is true the MS Excel will calculate the interest from
Issue Date to Settlement Date and if it is false then MS Excel will calculate the interest from
Settlement Date to 1st Interest Date. In most of cases we need to put the Calculation Method as
True.
Illustration 14: Consider a hypothetical bond having coupon rate of 9.5%. The bond will mature
on 23rd March 2016. Suppose the coupon is paid semi annually and it follows the day count
convention of 30/360. We want to do valuation of bond on 06th July, 2014. It is assumed that the
coupons are all paid at maturity. The bond is issued on 13th May 2008 and the first coupon date is
15th May 2008. The face value of the bond is Rs 100
The ACCRINTM function is shown below:
Financial Modeling using Excel
P a g e | 27
Figure 1.23 : Example of Accrued Interest calculation
1.7 Creating a Dynamic Model
Financing Modeling is task of building an abstract representation of a financial decision making
situation. It is a mathematical model designed to represent a financial asset or a portfolio of a
business or a project or any other domain. A financial model starts with preparing a forecast for
the future performance of a firm by identifying historical data on Balance Sheet, Profit and Loss
Statement, Cash Flow Statement etc and then projecting on basis of this the future figures. It helps
an analyst to set assumptions, to analyze the performance and test the sensitivity of data based on
calculation and understanding the impact of change. While creating Financial Model in excel font
color is very important like all hard coded facts or constant is coded in blue color, all formulae
in black, all linkages across spreadsheet should be in green color, the negatives numbers are
represented with brackets. Numbers should be written with 1000 separator and number of
decimals to be represented with number depends on scope of research undergoing.
1.7.1 Model 1: Valuation of an Option Contract
Let us create a Financial Model to value an option contract under Black Scholes Merton Model.
Since Derivative Contract assumed to follow Lognormal Distribution pattern so here I use
Financial Modeling using Excel
P a g e | 28
NORMSDIST Function to determine the cumulative normal distribution value. Now the formula
for Non Dividend Paying Stock under Black Scholes Merton Model is
π1 =ππ (
ππΎ) + (π +
π2
2 ) π‘
πβπ‘
π2 = π1 β πβπ‘
Premium of a call option
πͺ = πΊ Γ π΅(π π) β π΅(π π) Γ π²πβππ
Premium on a Put Option
π· = π΅ Γ (βπ π)π²πβππ β πΊ Γ π΅(π π)
Assume a stock called DLF trading in market at Rs 221.70 on 17 July 2014. Option data for the
stock is available below. Consider a strike price of Rs 230. It is OTM call option and ITM put
option. As per the data, premium on call option is Rs 5.40 and premium on put option is Rs 12.20.
Through Black Scholes Merton Model it will be determined whether the premium is overvalued
or undervalued. ICICI Bank FD Rates is taken as risk free interest for 90 days which is 8.5% per
annum. Standard Deviation is determined by last 3 month historical data. It is 3.724%.
Figure 1.24 : Historical 1 day value of a stock-DLF
Financial Modeling using Excel
P a g e | 29
Figure 1.25 : Historical option data of DLF at different strike prices
Figure 1.26 : Risk free interest rate in the market at varying periods
Financial Modeling using Excel
P a g e | 30
Figure 1.27 : Calculation of Standard Deviation
Formula for Black Scholes Merton Model is already discussed. In MS Excel a Dynamic Model
can be created for the valuation of Option contract.
Financial Modeling using Excel
P a g e | 31
Figure 1.38 : Calculation of option premium based on Black Scholes Model
According to this Model the price at the which trading in market is under valued.
1.7.2 Model-2 Bankruptcy of a Company
Altman Z Score Model was published in 1968 through which it can be determined the probability
that the firm can be bankrupt within 2 years. The Z Score is the linear combination of five ratios
weighted by coefficients.
The original Z score Model is :-
Z=1.2T1+1.4T2+3.3T3+0.6T4+0.99T5
Where T1=Working Capital/Total Assets. Measures the liquid assets in relation to size of
company.
T2=Retained Earning/Total Assets. Measures the profitability that reflects in the company age and
earning power.
T3=EBIT/Total Assets. Measures the Operating Efficiency apart from tax and and leveraging
factors. It recognizes operating earnings as being important to long-term viability.
Financial Modeling using Excel
P a g e | 32
T4=Market Value of Equity / Book Value of Total Liabilities. Adds market dimension that can
show up security price fluctuation
T5=Sales/ Total Assets. Standard measure for total asset turnover (varies greatly from industry to
industry).
Companies with Z score value less than 1.22 is in Distress Zone, while those above 2.9 are in safe
zone.
Figure 1.39 : Accounting Information required for Z-score calculation
Financial Modeling using Excel
P a g e | 33
Figure 1.40 : Calculation under Altman-Z score Model
1.8 Summary
1. A Financial Model is dynamic mathematical tool created to understand the relationship
among the variables of a financial problems so that it can help to answer βwhat ifβ questions
or make projections.
2. Models include dependent variables and independent variables. The variables whose
values are calculated by the model are called calculated variable or dependent variable.
Models are always created to observe how the values of the output variable will change
with changes in values of one or more independent variables.
3. Models include a independent variable called parameter who are independent variable
whose values are provided by creator or user of Model
4. Steps to construct a Financial Model are as follows:-
Financial Modeling using Excel
P a g e | 34
Define the structure of Problem
Define the input and output variables of the Model
Decide who will use the Model and how often
Understanding the Financial and Mathematical Aspects of the Model
Design the Model and create the spreadsheet
Testing, protecting and documenting the model
5. Application of Finance Function in MS Excel
FV Formula- It returns the future value of single set of cash flow or series of cash flows in
the form of annuity
PV formula-It returns the present value of single set of cash flow or series of cash flows in
the form of annuity
PMT formula-It determines the annuity value from future value or present value of cash
flow
IPMT formula- Use in loan amortization schedule to determine the interest period on period
basis on the decreasing principal
PPMT formula- use to determine extra principal paid over the interest because of constant
EMI in a loan amortization schedule
NPV formula- It determines the net present value of investment by deducting the PV of
Cash Outlay on the present value of cash inflows
IRR= It determines the minimum return on investment considering at a rate at which initial
investment would become equal to sum of present value of cash inflows
MIRR formula- It works when IRR results into multiple solutions. Finance Rate and
Reinvestment Rate implied here. Finance rate is used for the multiple cash outlay value to
determine the present value while Reinvestment rate is used for determining the sum total
of future value of cash inflows
XNPV formula-This calculates the NPV of series of uncertain cash flows
XIRR formula-This calculates the IRR of uncertain cash flows
6. Debt Instrument is a financial instrument in which borrower raise funds for a fixed duration
of time from lender whereby paying interest (coupon) after every regular interval of time
and on maturity pays Face Value.
Financial Modeling using Excel
P a g e | 35
7. Price of Bond can be determined through PV formula while YTM can be determined
through Rate formula, Coupon can be determined through PMT formula and Face Value
through FV formula.
8. Similarly along with PV formula for determining Price, in MS Excel βPriceβ formula can
be used.
Here Settlement Date=day on which bond is sought to be valued
Maturity date=day on which bond will mature
Rate=rate at which coupon is paid
Redemption is the redemption value of bond
Frequency is the number of coupon in a year
Basis=day count convention to be used
9. Since bonds are trading in market at clean price while at settlement dirty price is
determined, where dirty price=clean price+ accrued interest
10. Accrued Interest in MS Excel is determined through β=ACCRINTMβ or β=ACCRINTβ
formula
11. ACCRINT is use for coupon paying bond while ACCRINTM is used for zero coupon bond
paying face value at maturity
12. Clean Price is determined through demand and supply mechanism. Along with that changes
in Interest Rate, Government policies towards debt instrument, Inflation rate changes
impact the bond price
13.
Financial Modeling using Excel
P a g e | 36
14. Coupdays function- Return the number of days in a coupon period that contain the
settlement date
15. Coupnum function- Return the number of coupon payable between the settlement date and
maturity date
16. Coupdaysnc function- Return the number of days from the settlement date to next coupon
date
17. Couppcd function- the previous coupon date before the settlement date
18. Coupncd function- Return the coupon date after the settlement date
19. Dynamic Model in MS Excel-Valuation of Option
Normsdist function is used in MS Excel to represent lognormal distribution
Valuation of Option Contract is done through Black Scholes Merton Model and Binomial
Tree Model
For Black Scholes Model, d1 and d2 is determined initially for which N(d1) and N(d2) is
determined through NORMSDIST function in MS Excel or Cumulative Normal
Distribution Table can be used
Based on N(d1) and N(d2) values, premium on call option and premium on put option will
be determined and same can be check through put call parity
Binomial Tree Model is represented as one step Binomial Tree, two step Binomial Tree or
three step Binomial Tree depending upon the volatility.
u=proportionate increase in price of underlying or d=proportionate decrease in price of
underlying is determined through formula, π’ = ππβπ‘ and d= πβπβπ‘
e=euler number in MS Excel is represented with =exp function
20. Bankruptcy of a firm-Altmann Z-score Model
Altmann Z-score Model talks about possibility of a firm get bankrupt within 2 years.
Z=1.2T1+1.4T2+3.3T3+0.6T4+0.99T5
where T1=Working Capital/Total Assets. Measures the liquid assets in relation to size of
company.
Financial Modeling using Excel
P a g e | 37
T2==Retained Earning/Total Assets. Measures the profitability that reflects in the
company age and earning power.
T3=Measures the Operating Efficiency apart from tax and and leveraging factors. It
recognizes operating earnings as being important to long-term viability.
T4=Market Value of Equity / Book Value of Total Liabilities. Adds market dimension that
can show up security price fluctuation
T5=Sales/ Total Assets. Standard measure for total asset turnover (varies greatly from
industry to industry).
1.10 Questions to Discuss
Two Marks Questions
1. What is Financial Model?
2. Define the properties of Financial Model?
3. What are the steps taken to create a Financial Model?
4. What is the application of NORMSDIST in MS Excel?
5. Explain COUPDAYBS function?
6. Explain COUPDAYSNC function?
7. Explain COUPNCD function?
8. Explain COUPNUM function?
9. Explain COUPPCD function?
Five Marks Questions
1. What is the application of day count convention in Bond Mathematics?
2. What are the steps taken to create a Financial Model?
3. What are the application of finance functions in MS Excel?
4. With example explain FV function in MS Excel with and without PMT function?
5. What are the difference between NPV and XNPV function in MS Excel?
6. What are the difference between IRR and XIRR function in MS Excel?
7. How different is calculation of NPV and IRR in MS Excel ?
Financial Modeling using Excel
P a g e | 38
8. How price and ytm of bond is calculated in MS Excel?
Ten Marks Questions
1. With example, define loan amortization schedule using IPMT, PPMT, PMT, Goal Seek?
2. Explain Bond Mathematics in MS Excel using price, ytm, coupon based formulaes,
mduration, accrued interest?
1.10 References:
Khan MY, Jain PK(2008) Financial Management 3rd Edition Mc Graw Hill Publication-
New Delhi
Pandey I.M (2003) Financial Management, Vikas Publishing House-New Delhi
Tija S. J (2004) Building Financial Models- A guide to creating and interpreting
Financial Statements
Beninga S. (2008) Financial Modeling-3rd Edition
Damodaran A. (2008) -Damodaran on Valuation, Wiley Finance-USA
Fabozzi J.F (2008) The Handbook of Fixed Income Securities-8th Edition
Hull C.J (1997)-Options, Futures and Other Derivatives-6th Edition
Websites:
Fixed Income Securities, Understanding trading on debt instruments- last retrieved on
18/11/2014 from http://www.moneycontrol.com/fixed-income/
Financial Modeling-Financial Functions in Excel- last retrieved on 18/11/2014 from-
http://www.excel-easy.com/functions/financial-functions.html
Financial Modeling-Financial Functions in Excel- last retrieved on 18/11/2014 from
http://office.microsoft.com/en-in/excel-help/financial-functions-reference-
HP010342519.aspx
Option Pricing Model, Black Scholes Merton Model on Option Valuation- last retrieved on
18/11/2014 from- http://mitsloan.mit.edu/newsroom/2013-black-scholes-merton.php
Financial Modeling using Excel
P a g e | 39
Video Links:wc
Amir Parmar, (14 October 2013) Microsoft Excel Financial Functions - viewed on
18/11/2014-http://www.youtube.com/watch?v=-6ERqkxlcFY
Hun Kim (15 Feb 2013) Financial Formula PMT function (Calculate Loan Payments)-
viewed on 18/11/2014 http://www.youtube.com/watch?v=WiVU5qeX48Q
Paul Wilmott (21 Feb 2011)-Bond Maths- viewed on 18/11/2014-on Quantitative
Finance-http://www.youtube.com/watch?v=lrRJ70N2Nhc
Financial Modeling using Excel
P a g e | 40
Module II: Sensitivity Analysis Using Excel.
Financial Modeling using Excel
P a g e | 41
Module II: Sensitivity Analysis Using Excel.
Contents
2.1 Objectives-Sensitivity Analysis using MS Excel
2.2 Introduction to Sensitivity Analysis
2.2.1 Understanding one way data table
2.2.2 Understanding two way data table
2.2.3 Understanding Goal Seek
2.3 Scenario Manager Function using MS Ex
2.4 Application of MS Solver
2.5 Summary
2.6 Questions to Discuss
2.7 References
2.1 Objective-Sensitivity Analysis using MS Excel
By the end of this Module, you should be able to:
Understand the significance of Sensitivity Analysis
What IF Analysis using MS Excel-Goal Seek
What IF Analysis using Excel-one way data table
What IF Analysis using Excel-two way data table
Financial Planning Application using Data Table
Analyzing different scenarios using Scenario Manager
Analyzing Mathematical Constraint scenario using Solver
Financial Modeling using Excel
P a g e | 42
2.2 Introduction to Sensitivity Analysis
Sensitivity Analysis is the study of how the uncertainty or deviation in the output of mathematical
model can impacts the different sources of uncertainty in the inputs. The main goal is to gain
insight to find out which assumptions are critical. The process involves various ways of changing
input variables of the model to see the impact on output variables.
In order to understand Sensitivity Analysis we will take an example.
Illustration-1: Suppose Amit want to start a Juice Shop. Before opening the shop suppose he is
curious to analyze how is his profits, Revenue, Variable Costs will depend on the price he will
charge and the unit cost. Most Worksheet Models contain assumptions about certain parameters
or inputs to the model. In this example also inputs includes
a. The price for which glass of juice will be sold
b. The variable cost of producing 1 glass of juice
c. The sensitivity of demand for juice to the price changed
d. The annual fixed cost of running a juice shop
Based on the input assumptions, he can compute the output of interest. In this example, the output
will include:-
a. Annual Profit
b. Annual Revenue
c. Annual Variable Cost
Despite enough research, assumptions about the input values can have errors. For example, the
best guess about the variable cost of producing 1 glass of juice might be Rs 6 but it is possible that
the assumptions can have errors. Sensitivity Analysis determines how a spreadsheetβs output vary
in response to the change in its inputs. For example he want to know if he will change the profit
then how much it will affect the yearly profit, revenue and variable cost. Data Table in MS Excel
will help to find the sensitivity. With a one-way data table, he can determine how changing input
Financial Modeling using Excel
P a g e | 43
will change any number of outputs. With a two-way data table, he can determine how changing
two inputs will change a single output.
Figure 2.1: Example to explain the working of data table
Now suppose he want to know how change in price for example from Rs 12 through Rs 26 in Rs
1 increments affect the annual profits, revenue and variable cost. Because here changing only 1
input, one way data table can solve this problem. In order to set one way data table, he will have
to begin by listing input values in a column.
Representation of one way data table will be like this.
Financial Modeling using Excel
P a g e | 44
Figure 2.2: Data formatting in data table
In order to work on Data Table whole rectangular area will be selected and then Data tab under it
what if analysis then if data table is selected a window will open asking for Row input cells and
column input cells. Enter the data which is independent variable here in this case (Price) of
mentioned in the statement. Price which is mentioned in the statement is Rs 20 per glass. Select
the cell for that and press OK.
Financial Modeling using Excel
P a g e | 45
Figure 2.3: One way Data Table
Financial Modeling using Excel
P a g e | 46
Figure 2.4: Outcome on data table
Now I can measure the sensitivity of change in the price of juice (independent variable), its impact
on Profit, Contribution, Revenue, Variable Cost (dependent variables). If we notice initial column
is creating confusion as after calculation it is showing duplication so we should edit it. If I try to
delete it then whole data will become zero.
Financial Modeling using Excel
P a g e | 47
Figure 2.5: Impact of change on data table
Appropriate solution can be remove the formulas within each cell through Paste Special Value
after copying them so what we can do that go the irrelevant cell, go to format cell or CTRL+1,
then custom then inside that click on General, then under Type write within β β.
So one by one if Amit go the each cell and then go to format cell and then inside that under Name,
Custom and after clicking General then if I write in Type-βProfitβ, then for other cell, βRevenueβ,
then for other cell βVariable Costβ and like this then I will get my data table as
Financial Modeling using Excel
P a g e | 48
Figure 2.6: Data Table
Till now he has changed one variable and see its impact on different dependent variables. Now he
will change two variables Price and Demand and then he will see it impact on Profits.
Figure 2.7: Two way data table-initiation
For this he will use two way data table. For putting random data following any pattern he can use
Fill function under HOME ribbon.
Financial Modeling using Excel
P a g e | 49
Figure 2.8: Adding number in series
Now the objective is to see the impact on profit by changing two variables called demand and price
per unit. For this the first thing what he need to put here is the data of profit at the top end of data
table mentioned under this case.
Financial Modeling using Excel
P a g e | 50
Figure 2.9: Data arrangement under two way data table
Then copy the whole rectangular data, go to Data>>What If Analysis>>Data Table
Financial Modeling using Excel
P a g e | 51
Figure 2.10: Working under two way data table
Financial Modeling using Excel
P a g e | 52
Figure 2.11: Outcome under two way data table
Data mentioned in the row is demand and in column is price per unit, while in row input cell he
would put demand mentioned and in column input cell he put price per unit as mentioned in the
case which he created at the beginning of session about Cost Sheet of Amit Juice Company.
By seeing in this excel sheet he can understand and prove mathematically how the profit get
impacted by the change in demand and price together.
Financial Modeling using Excel
P a g e | 53
Illustration-2: Another parameter which we can use in Sensitivity Analysis is Goal Seek which
help to find the breakeven point. Suppose Amit want to know that how much glasses of juice can
he sell so that he get back my investment. Goal Seek feature in MS Excel enables us to compute a
value for the worksheet input such that it matches with the goal being specified. Goal Seek is used
in finance to determine discount rate or compounding rate in Capital Budgeting, Bond Arithmetic
etc. Suppose in the same example Amit want to know how many juices should he sell in a year so
that he get break even. Consider the same example again,
Figure 2.12: Break-even calculation through Goal Seek
In order to determine the break even he will go to Data>>What If Analysis>>Goal Seek. A
dialogue box will open where in Set cell, he will chose the cell having profit data. At breakeven
the profit would be zero, so in option: To value, put value=0. Please chose the cell having demand
in option βby changing cellsβ. Then press OK.
Financial Modeling using Excel
P a g e | 54
Figure 2.13: Break-even calculation through Goal Seek
Illustration 3: Goal Seek can also help in Financial Planning. Suppose he want to buy a house in
Bangalore and he is considering based on his Financial Strength how much price of house can he
afford in Bangalore. He have a liquid cash of 5 lakh Rupees which he can give for down payment.
He is thinking to take 15 year loan from bank for the remaining periods. As next month is his
wedding so probably after 15 year he will have to plan for his child education. Interest rate on
home loan is 14.25% per annum. By considering all the constraints and his financial obligation
suppose he can pay an EMI of Rs 8000 to the bank for 15 years. Now the question arises, which
will be price range of 2 BHK house which should he start searching. For this he will use Goal Seek
in MS Excel.
Financial Modeling using Excel
P a g e | 55
Figure 2.14: Financial Planning calculation through Goal Seek
Amount of loan would be Price of house-Down Payment. Since of House cell he kept blank so the
Amount of loan is showing -5 lakh Rupees. Monthly Payment can be calculated by PMT function.
Now, the value of Monthly payment will be calculated below and the total interest paid=(EMIx180
periods)-loan value.
Financial Modeling using Excel
P a g e | 56
Figure 2.15: EMI-part of Financial Planning calculation through PMT
It will come -6742.50. But my commitment to pay is Rs 8000 per month. Now I can use Goal Seek
to determine the price of house. MS Excel command to go Goal Seek is Data>>What if
Analysis>>Goal Seek. In Goal Seek under set cell if amit would select Monthly Payment cell , to
value=8000 and by changing cell, in that cell=price of loan. Goal Seek will give the outcome.
Financial Modeling using Excel
P a g e | 57
Figure 2.16: Financial Planning calculation through Goal Seek
In set cell he put cell that containing Monthly Payment data, To value he can put Rs 8000 which
is his ability to pay every month. By changing the cell having empty data of price of car, then press
OK.
Figure 2.17: Result-Financial Planning calculation through Goal Seek
Financial Modeling using Excel
P a g e | 58
Figure 2.18: Financial Planning calculation through Goal Seek
This mean under the given financial condition, he can afford to buy a house of Rs 10 lakh 93
thousand Rupees. Interest rate is very high because of which the value of Rs 8000 per month spread
over 15 years turns to be only Rs 5.93 lakh only. By using one way data table he can analyze the
same situation where he can see the impact of Interest Rate on the Price of House.
Figure 2.19: Financial Planning calculation through Data Table
Financial Modeling using Excel
P a g e | 59
Figure 2.20: Financial Planning calculation through two way data table
2.3 Scenario Manager Function in MS Excel
Scenario Manager in MS Excel helps us to see different perspective of a same event at same
moment instead of changing frame of reference one by one. Consider an example of NPV
calculation.
Imagine Sonal Sharma is the financial manager of Hindustan Seva Product limited (HSPL). HSPL
is considering to set up a plant at Indore. The project staff has developed the following cash flow
forecast for the plant.
Financial Modeling using Excel
P a g e | 60
Figure 2.21: Basic data for Scenario Manager
Now, under this condition suppose Sonal want to know the NPV of the project. She is considering
a WACC of 13%. NPV is the net present value of the project which is determined by βPV of Cash
Inflows-Initial Investment. βPV of Investment is determined by PV of annuity of net cash inflows
of Rs 50 million. PV of annuity is determined by PV function in MS Excel.
=Rs 271.3122 million.
NPV=271.3122-250=Rs 21.3122 million.
Figure 2.22: NPV calculation for Scenario Manager
Now she wants to analyze these data under three scenarios, impacting it effect on NPV.
Figure 2.23: Different scenario for Scenario Manager
Instead of observing them individually through Scenario Manager Sonal can observe all three
behavior at one moment. Before working on Scenario Manager naming of cells should be done as
it will help during working.
Financial Modeling using Excel
P a g e | 61
Figure 2.24: Naming a cell
Spacing is not allowed during naming, space between the words is to be connected by underscore.
If we do not put underscore then MS Excel would show error.
Figure 2.25: Error while naming cells
In order to start working on Scenario Manager, first select all the required cells, then go to
Data<<What If Analysis<<Scenario Manager<<Add<<Mention the name of Scenario( suppose it
Financial Modeling using Excel
P a g e | 62
Pessimistic) When clicked on Add, the window will come which will ask for adding scenario,
mention the name of scenario and put necessary comments and then press ok.
Figure 2.26: Scenario Manager building
Now a dialogue box will open which will ask for necessary entry to be made for the particular
scenario.
Figure 2.27: Scenario building for Scenario Manager-working on scenario
Financial Modeling using Excel
P a g e | 63
Like this fill the data for each scenario. So, created 3 scenario as mentioned by requirement of
Sonal.
Figure 2.28 building for Scenario Manager-observing scenarios
Now by going to any scenario, just click on show, it will highlight the required scenario. To see
all of them together, click on Summary. After clicking on Summary, the Excel will ask for the
destination for the scenario summary. For that highlight a cell where summary need to be
presented. Result cell will be the cell on the impact need to be felt. In this case the result cell will
be the cell on NPV.
Financial Modeling using Excel
P a g e | 64
Figure 2.29: Scenario building for Scenario Manager-result
The result will be like this.
Financial Modeling using Excel
P a g e | 65
Figure 2.30: Scenario building for Scenario Manager-result
2.4 Solver in MS Excel
Optimizing values in an Excel spreadsheet to meet a given objective can be a tough process.
Fortunately, Microsoft Office, solver, a numerical optimization add-in is there to assist in this task.
To install Solver in a system, please go to File Command (MS Excel 2010, MS Excel 2013) or
Office Ribbon button in MS Excel 2007, there go to Excel Options, then Add-Ins and there install
Solver. It will get highlight under Data command in MS Excel.
Financial Modeling using Excel
P a g e | 66
Figure 2.31: Finding Solver in MS Excel
Once we have Solver we can talk about Optimization. Let try to understand the application of
Solver by taking an example. Assume there is company called Galaxy Limited manufacturing two
toy models-Spider Man and Krrish. The Manager of the company want to maximize the production
per dozen per week. Profit on 1 dozen of Spider Man per week is 8$ while the profit on Krrish per
week per dozen is 5$. During production the following constraint are faced by Management.
Constraints are:-
a. Resources are limited to 1000 pounds of plastic as a raw material.
b. 40 hour of production time per week
c. Total production in a week cannot exceed 700 dozens
d. Number of dozens of Spider Man cannot exceed the number of dozens of Krrish by 350
Necessary input considerations are- Spider Man requires 2 pounds of plastic and 3 minute of labor
per dozen while Krrish require 1 pound of plastic and 4 minute of labor per dozen.
Financial Modeling using Excel
P a g e | 67
While solving problems on Linear Programming, first thing what need to define is decision
variables. Here considering the cases, 2 decision variables can be defined X and Y,
Where, X= weekly production level of Spider Man in dozens,
Y=weekly production level of Krrish in dozens.
Objective of any firm is to maximize the profit or minimize the costs. Since in this case the per
dozen profit is mentioned for both products so objective function would be defined as
Objective Function will be= Max=8X + 5Y (Weekly profit)
Constraint can be written mathematically subject to
2X + 1Y<=1000 (Plastic)
3X + 4Y<=2400 (Production Time)
X + Y <= 700 (Total production)
X -Y <=350 (Mix)
Where non negativity restriction are Xj> = 0, j = 1,2 (Non negativity)
This is Linear Programming Approach. This can be solved by Graphical method or by Simplex
Method. In MS Excel this can be solved by Solver function. Consider any value of X and Y, here
in MS Excel assumed value of X=2, Y=1.
Financial Modeling using Excel
P a g e | 68
Figure 2.32: LPP working in MS Excel
By considering the value of X=2, Y=1, write all the equations in some particular cell.
Figure 2.33: Putting constraint under LPP Model
Once written all the equations in a cell, open a Solver from Data in MS Excel. Once Solver get
open, set the objective of a cell as maxima, if cost was mentioned then minima have to get select.
Then in option in Solver where to insert changing cells, select the cells where assumed values of
X and Y was put, then select all constraints specifying limitation.
Financial Modeling using Excel
P a g e | 69
Figure 2.34: Adding constraint in MS Excel-Solver
Financial Modeling using Excel
P a g e | 70
Figure 2.35: Data Entry in MS Excel-Solver
Once all details get filled then click on Solve. Click on ok. The optimal solution can be generated.
Financial Modeling using Excel
P a g e | 71
Figure 2.36: Solver-result
Figure 2.37: Framework of LPP Model
2.5 Summary
Financial Modeling using Excel
P a g e | 72
1. Sensitivity Analysis is the study of how the uncertainty or deviation in the output of
mathematical model can impacts the different sources of uncertainty in the inputs
2. The process involves various ways of changing input variables of the model to see the
impact on output variables.
3. Sensitivity Analysis can be done with Goal Seek, Solver, Scenario Manager, Data Table
4. Goal Seek helps to understand the relationship between input variables with the output
variable where one input variable can be change and it impact on final outcome can be
realized
5. For observing the change in multiple inputs on the output variable, Solver is used where
the sum total of all change on the final value can be realized
6. By defining the value of output variable the impact on any input variable can be observe
through Goal Seek
7. MS Solver is available in Data tab in MS Excel. If not visible it can be install through Excel
Add-ins available in Excel Options
8. Through Solver objective function can be define and it can be set to any optimal value or
maximal or minimal. When the objective is the maximization of profit or revenue, it is set
at maxima while when the objective is minimization of cost, it is set at minimal
9. Objective function is define in the term of decision variable and on which constraint are
written with non negativity restriction. Constraints are mentioned with inequality
10. MS Solver solves scenario mentioned in Linear Programming format which can be solve
mathematically through Graphical Method or through Simplex Method. Graphical Method
can only work for 2 decision variable while through Solver multiple variables can be solved
11. Suppose I want to buy a house in Bangalore but keeping all my constraint and obligation
want to find out what would be the price of house I should look in market to buy. In order
to find the answer of this question I can use Goal Seek or Solver. Mathematically I can
solve this situation by keeping price of house as x then I will find out how much loan I can
take from bank, depending upon my down payment as loan taken=x-down payment.
Financial Modeling using Excel
P a g e | 73
Considering the time period and rate of loan financing the EMI can be determined through
Present Value of Annuity calculation
12. In MS Excel the same can be solved where in price of house any number can be taken
which MS Excel will automatically adjust when Goal Seek command is applied. x cannot
be considered as in MS Excel because in MS Excel x is consider as a character and any
mathematical calculation cannot be performed later on. EMI can be determined in MS
Excel through PMT function. Then Goal Seek can be applied by equating the value of EMI
at x price of loan to desired value of EMI
13.
14. Through Goal Seek and Solver relationship between decision variables and objective
variable can be establish while in order to understand the impact on objective variable with
a change of one particular variable again and again can be observe simultaneously by using
data table.
15. Data table helps to see the impact of volatility in one decision variable on the outcome.
Data table can be one way data table or two way data table. In one way data table impact
of one decision variable on the output is observe while in two way data table, two variables
changing horizontally and vertically can be observe simultaneously.
16.
Financial Modeling using Excel
P a g e | 74
17.
18. Scenario Manager in MS Excel helps us to see different perspective of a same event at
same moment instead of changing frame of reference one by one.
19. In order to start working on Scenario Manager, select the cells on which this application
want to apply, then in data tab<<what-if analysis<<scenario manager can be selected, then
click on add and mention different scenarios
20.
2.6 Questions to Discuss
Two Marks Questions
1. What is Sensitivity Analysis?
2. How data table helps in what-if analysis?
3. How data can be edited inside data table?
4. What is the difference between one way data table and two way data table?
Financial Modeling using Excel
P a g e | 75
5. What is Goal Seek?
6. What is Scenario Manager?
7. What is Solver?
Five Marks Questions
1. How Financial Planning can be discussed through data table and goal seek?
2. Explain the application of Scenario Manager in MS Excel?
3. How Linear Programming problems can be solved in MS Excel?
Ten Marks Questions
1. A paper mill produces two grades of paper namely x and y. Because of raw material
restrictions, it cannot produce more than 400 tons of grade x and 300 tons of grade y in
a week. There are 160 production hours in a week. It requires 0.2 and 0.4 hours to
produce a ton of products x and y respectively with a corresponding profit of RS 200
and Rs 500 per ton. Formulate the above as LPP to maximize the profit and find the
optimum product mix. Solve this through Solver.
2. A furniture company manufactures desks and chairs. There are four departments
namely carpentry, upholstery, painting and varnishing with capacities as given below:
Assuming that raw material are available in adequate quantities and the manufacturer
wishes to know how many desks and chairs he should produce. He enjoys a good
Financial Modeling using Excel
P a g e | 76
market share and contribution from desk is Rs 40 and from chair is Rs 25. Solve it
through Solver.
2.7 References
1. Pandey I.M (2003) Financial Management, Vikas Publishing House-New Delhi
2. Tija S. J (2004) Building Financial Models- A guide to creating and interpreting
Financial Statements
3. Beninga S. (2008) Financial Modeling-3rd Edition
Websites:
1. What If Analysis in Excel-Understanding the use of Goal Seek, Solver, Scenario
Manager- last retrieved on 18/11/2014 from http://www.excel-easy.com/data-
analysis/what-if-analysis.html
2. What If Analysis in Excel-Use of data table, Goal Seek, Scenarios- last retrieved on
18/11/2014 from-http://office.microsoft.com/en-in/excel-help/introduction-to-what-
if-analysis-HA010243164.aspx
Video Links:
1. Codible (25 Aug 2012) How to use Excel Data Table for Financial Planning-
viewed on 18/11/2014- https://www.youtube.com/watch?v=Wcmd0jO1Ngs
2. Aldo Mencaraglia (17 June 2012) How to use Goal Seek Function in Excel-
viewed on 18/11/2014- http://www.youtube.com/watch?v=fcKCUDyF73Y
3. Danny Rocks (21 September 2012) How to use Solver tool in MS Excel-
viewed on 18/11/2014-http://www.youtube.com/watch?v=K4QkLA3sT1o
4. Piyesh Shah (28 Nov 2012) Linear Programming with Excel Solver- viewed on
18/11/2014 https://www.youtube.com/watch?v=RicajFzoenk
Financial Modeling using Excel
P a g e | 77
Module III: Simulation Using Excel.
Financial Modeling using Excel
P a g e | 78
Module III: Simulation Using Excel.
Contents
3.1 Objectives-Simulation using MS Excel
3.2 Introduction to Simulation
3.2.1 Application of Rand(), Rand between, Random Number Generation
3.3 Monte Carlo Simulation using Excel-Determination of Pi
3.4 Simulation of Brownian Motion
3.5 Monte Carlo Simulation-Stock-TCS
3.6 Summary
3.7 Questions to discuss
3.8 Problems to Practice
3.9 References
3.1 Objective-Simulation
By the end of this Module, you should be able to:
Understand the importance of simulation
Application of Random Number in Statistics, Finance
Working on Random Number to understand the derivation of Pi, Brownian Motion
Application of Historical Price concept in conjunction with What If Analysis to understand
the derivation of stock prediction.
Financial Modeling using Excel
P a g e | 79
3.2 Introduction-SIMULATION
Prediction of uncertainty around a decision is very important. Either consider a smallest decision
of where to send kid for studies or big decision like what specialization should I chose after class
X, possibility of outcome is very tough to determine as outcome of every decision depends on lots
of factors. Now consider the decision of investment on Stock Market where objective is to predict
the next movement on a stock or a market where investors use Technical Analysis, Fundamental
Analysis or to observe the movement in Derivative Market. There are numerous tools and software
but predicting market is impossible as it depends on multiple scenario and predicting scenario may
be possible but the probability that each scenario that will impact the next movement is impossible.
Investment Banks around the world use to work on Algorithmic Trading where software in order
to calculate the impact of mood or sentiment of trader analyze the Facebook, Twitter and other
social networking sites. Like this multiple cases Simulation Model helps a lot. In order to work on
Simulation Model which would be replica of situation need to be create. Then testing this model
under multiple cases leads to the possibility of different outcomes.
Simulation is done in Excel using Random Numbers. Rand() is the function which can determine
the Random Numbers between 0 to 1.
Suppose the objective is to determine 10 Random Numbers between 10,000 to 25,000 then Rand
between function can be used. It will randomly generate a number between the given condition.
Every time any changes be made in the worksheet where Randbetween or Rand command is used,
MS Excel will generate a random number. In order to make the number generated constant or free
from formula, paste special+value can be done or a combination short cut keys (Alt+E+S+V)
Financial Modeling using Excel
P a g e | 80
Figure 3.1: Showing Rand between function in MS Excel
Random Numbers with specific conditions can be generated by Random Number Generation
present in Data Analysis under Data Ribbon. If not installed then go to File (MS Excel 2010-2013)
or Office ribbon button in MS Excel 2007 and then Excel Options then Add Ins then Analysis took
pack then under Manage. Click on Go,
Figure 3.2: Installing Analysis Tool Pack
A window will open.
Financial Modeling using Excel
P a g e | 81
Figure 3.3: Add-Ins-Analysis Took Pack
Select Analysis Tool Pak and Solver Add-In and press ok. Then it get visible under Data Analysis.
Figure 3.4: Data Analysis
Financial Modeling using Excel
P a g e | 82
Click on Random Number Generation under Data Analysis to generate Random Numbers
specifying conditions.
Figure 3.5: Random Number Generation-MS Excel
Under this Random Number Generation Window, based on the objective the number of variables
and number of Random Numbers can be inserted under specifying the conditions under Normal,
Binomial, Bernoulli, Poisson, Discrete. If click on Normal Distribution, then mean and standard
deviation of required distribution need to be specify, where for Binomial p-value, while for
Binomial distribution p-value and number of trails etc. Then Output Option can be specified where
the result can be generated in new worksheet or in same worksheet. For example if I like to create
Normal Distribution pattern of 5 variables under that 10 Random Numbers each having mean of
66 and standard deviation of 70 then the output would be like this.
Financial Modeling using Excel
P a g e | 83
Figure 3.6: Normal Distribution-Data Analysis
Let try to create a Simulation Model for Amit Hotel which is near Bangalore International Airport,
which has 40 rooms available for boarding but it can make booking up to 45. On any given day
the hotel does not have bookings less than 36 nor more than 45. Each of the bookings have the
equal probability of occurrence. Hotel also has to accommodate passengers who had the
cancellation at the airport. The cancellation at the airport has the distribution as follows:-
Now the average number of booking on any given day can be calculated as Total Booking=Hotel
Booking+ Cancellations. And in order to determine the percentage of day when the hotel is
overbooked can be determined as by summing up the total number of days when hotel is
overbooked and dividing it by total number of days used in simulation. As the simulation can be
done on MS Excel so let highlight the assumptions mathematically. Then lower limit and upper
Financial Modeling using Excel
P a g e | 84
limit can be specified. Lower limit for number of reservation of 36 would be 0 while the upper
limit would be cumulative frequency. Like this others lower and upper limits for other variables
can be figure out.
Figure 3.7: Working on Business Simulation-Hotel Example
Like this, Figure for cancellation Figure can also be determined.
Figure 3.8: Working on Business Simulation-Hotel Example
Once the data of Reservation and Cancellation is present then Simulation Model can be generated.
In order to work on Simulation Model, let assume the total number of days. Suppose I have taken
14 days. More the number of days taken more accurate answer can be generated. Then a random
number is allotted to each days by using RAND() function. Then relationship between the random
number generated with associated Reservation Figure can be determined by using VLOOKUP
function of MS Excel.
Financial Modeling using Excel
P a g e | 85
Figure 3.9: Working on Business Simulation-Hotel Example
Once the Reservation based on Random Numbers is generated then in similar manner Cancellation
can also be determined by using Vlookup function. Once the Cancellation is determined, then total
reservation can be determined by adding reservation with cancellation.
Financial Modeling using Excel
P a g e | 86
Figure 3.10: Working on Business Simulation-Hotel Example
Like this by changing Random numbers different scenario can be generated. In order to determine
the number the days the Hotel get overbooked, IF function can be used.
Financial Modeling using Excel
P a g e | 87
Figure 3.11: Working on Business Simulation-Hotel Example
Financial Modeling using Excel
P a g e | 88
Figure 3.12: Working on Business Simulation-Hotel Example
3.3 Monte Carlo Simulation- Determination of Pi(Ο)
Rand () is the function through which it can be determined Random Variables between 0 to 1. In
order to determine the value between -1 to +1, the formula can be =1-2 x Rand (). In MS Excel if
this formula is used, it will generate random numbers between -1 to +1. Plot this representation of
1000 variables in x-y scatter diagram. Rectangular representation can be obtained having
length=breadth=2.
Financial Modeling using Excel
P a g e | 89
Figure 3.13: Determination of Pi: Monte Carlo Simulation
Now Pi (Ο)=(circumference of circle)/(diameter of circle), now values of random variables can be
used to set up the equation of circle. Equation of circle=π₯2 + π¦2 = π2. Here consider a circle which
is imposed over the rectangle having a radius of 1. So, the equation will be π₯2 + π¦2 = 1. Now
consider (x,y) is any point on the circle, where mid point of circle is (0,0) whose values can be
determined by considering random variables. Then by using IF function coordinates of circles can
be defined, like abscissa or ordinate of associated x or y value, where only those points are selected
which are within the circle while rest by using IF command made 0.
Financial Modeling using Excel
P a g e | 90
Figure 3.14: Calculation of Pi-Random Variables
Now the area of circle and area of rectangle can be determined by using IF function. Area of circle
is determined by points which are below circle horizon, while all the point are on the part of area
of rectangle. Area of circle is determined by counting the total number of points satisfying the
condition of Equation of Circle. So, value of Ο=ππππ ππ ππππππ
ππππ ππ ππππ‘πππππ where it is multiplied by 4 as
rectangle being Standard rectangle having area of 4 . Value so obtained is 3.132 which is make
more realistic if more data is taken.
3.4 Simulation of Brownian Motion
Brownian Motion is the random motion of the particle suspended in the fluid resulting from the
collision with the quick atoms or molecules in a gas or liquid where the relative velocity of particle
is directly proportional to square root of temperature. The application of Brownian Motion is
relevant study of Random Walk Hypothesis on Stocks.
Financial Modeling using Excel
P a g e | 91
In order to work on Brownian Motion in MS Excel Rand () is used where to derive the value
between -1 to +1 , the formula which is used is 1-2 x Rand (). Suppose there are two variables
called x and y. Relative change is x and y is measured as dx and dy where both imagine at point
0. Then through Simulation their relative change can be determined where each step the change
will be, πβππππ ππ‘ π₯π‘β π π‘ππ = πβππππ ππ‘ (π₯ β 1)π‘β π‘πππ +
ππ₯( πππππ‘ππ£π πβππππ ππ‘ π₯π‘β π π‘ππ). Based on the outcome obtained, the result can be plotted in
xy scatter diagram.
Figure 3.15: Working on Brownian Motion
Since the mechanism works on Random Variables, so my clicking enter the whole mechanism can
be changed and the output will be
Financial Modeling using Excel
P a g e | 92
Figure 3.16: Simulation of Variables in Brownian Motion
This led the foundation of Random Walk Hypothesis which state that future movement of stocks
can never be identified. Although Albert Einstein study on Particle Theory can help to study this
movement. Now the concept of Brownian Motion can be applied on multiple particles together.
Suppose there are 100 particles, each at moment, t=0 showing a position assume as (0,0) while
with the time period will show Random Walk Hypothesis which can be measured through
Simulation by using Rand () function where movement of each particle at each step will follow
the formula, πβππππ ππ‘ π₯π‘β π π‘ππ = πβππππ ππ‘ (π₯ β 1)π‘β π‘πππ +
ππ₯( πππππ‘ππ£π πβππππ ππ‘ π₯π‘β π π‘ππ). After the movement at each time period when scatter diagram
is plotted, the moment of randomness can be observed.
Financial Modeling using Excel
P a g e | 93
Figure 3.17: Showing Brownian Motion at different time periods
Financial Modeling using Excel
P a g e | 94
Figure 3.18: Diagram Representation-Showing Brownian Motion at different time periods
3.5 Monte Carlo Simulation-Stock-TCS
Monte Carlo Simulation can be applied on Stock Market. Considering the return 3 month time
period of TCS, average return and standard deviation can be determined and on applying Monte
Carlo Simulation different possibility of the value of money thinking for investment can be
determined. Assume Salman Khan looking for investment planned to put Rs 1 lakh upfront on
TCS, the best IT stock. Every year he is planning to put Rs 100,000. Historical data about TCS
can be downloaded from www.nseindia.com. Then average return and standard deviation of return
is calculated, then assuming the return on stock follows normal distribution pattern, by using
norminv and rand() formula in excel, the random return is determined and then on based on random
return determined the ending value of investment is determined.
Financial Modeling using Excel
P a g e | 95
Figure 3.18: Working on Monte Carlo Simulation-TCS
After determination of random return , the value of investment at the end of year is determined.
Figure 3.19: Working on Monte Carlo Simulation-TCS
Financial Modeling using Excel
P a g e | 96
By following 1 simulation model , at the end of 25 year if initially Salman Khan start with 1 lakh
Rs and every year if he put annuity of 100000 Rs he will get 24, 23, 945 Rs. By using Data Figure,
multiple scenario can be run together to get better understanding of given experiment.
Financial Modeling using Excel
P a g e | 97
Figure 3.20: Using Data Figure on working on Monte Carlo Simulation-TCS
Financial Modeling using Excel
P a g e | 98
Figure 3.21: Simulation Iteration-TCS
Once the outcome come , based on the input the data can be summarized in the form of Median,
Average, Percentile etc.
Financial Modeling using Excel
P a g e | 99
Figure 3.22: Outcome of Simulation Model on TCS
5% percentile here means that there is 95% chance that at the end of 25 year if I continue investing
1 lakh rupees every year starting 1 lakh rupees now on a stock called TCS there is 95% chance
that I get value more than 23, 99,202 Rs at the end of 25 years.
Statistical Information about the return of the stock can be determined by using Descriptive
Statistics function available in Data Analysis.
Financial Modeling using Excel
P a g e | 100
Figure 3.23: Statistical Analysis on TCS after Monte Carlo Simulation
Financial Modeling using Excel
P a g e | 101
Input Range would be the total return figure about the stock, summary statistics would give all
required information.
Figure 3.24: Outcome-Statistical Analysis on TCS after Monte Carlo Simulation
Returns are negatively skewed and distribution is platykurtic. Normal Distribution can be plotted
by using Data Analysis-Histogram.
Figure 3.25: Data Analysis-Analysis Tool Pak
Financial Modeling using Excel
P a g e | 102
Figure 3.26: Working under Histogram
Input Range would be the return on the stock while in Output Option Cumulative Percentage
should be selected.
Figure 3.27: Outcome-Histogram of Data Analysis
Financial Modeling using Excel
P a g e | 103
Now a graph can be plotted signifying the normal distribution pattern keeping Cumulative
distribution data on x-axis and Frequency is y-axis.
Figure 3.28: Frequency Distribution-Normal Distribution
Similarly the logic of Monte Carlo can be applied on pricing of stock to identify the likely price
of the stock in future. Consider the same stock βTCS. Price of stock as per 27th August 2014 is Rs
2549.55. 3 month volatility is 1.4189%. Daily Volatility can be determined ==3 ππππ‘β π£ππππ‘ππππ¦
β90.
Daily Volatility would be 0.15%. Likely future price will be determined by using NORMINV
function in MS Excel.
Financial Modeling using Excel
P a g e | 104
Figure 3.29: Use of Norminv function to calculate the return
According to Random Walk Hypothesis, the expected change per day is to be 0 where probability
is determined through RAND().
Financial Modeling using Excel
P a g e | 105
Figure 3.30: Average return on stock based on Simulation
One Simulation Model cannot help, by using Data Figure multiple models can be created then it
analyzing them makes sense.
Each outcome of price is the simulated outcome of change after 1000 days. Then Statistical
analysis can be done on this by determining the average, Median, Standard Deviation, Percentile
on these stock price change.
Financial Modeling using Excel
P a g e | 106
3.6 Summary
1. Simulation is the imitation of the operation of a real-world process or system over time.
2. Simulation can be tested on a model which represent key characteristics or behavior of
selected physical or abstract system or process.
3. Simulation can be used to show the eventual real effects of alternative conditions and
courses of action. Simulation is also used when the real system cannot be engaged, because
it may not be accessible, or it may be dangerous or unaccepFigure to engage, or it is being
designed but not yet built, or it may simply not exist.
4. To work on Simulation, we need Random Numbers. Random Numbers can be generated
by using Rand(), Randbetween and through Random Number Generation. Rand() generates
random numbers between 0 and 1. Randbetween creates random numbers between any two
specified limit.
5.
6. Monte Carlo Simulation let us to see all possible outcomes of a decision and work on
accessing the risks, so that better decision making situation can be create for the outcome.
7. Monte Carlo performs risk analysis by building models of possible outcomes by
substituting a range of values-probability distribution, it then calculates results over and
over each time using different sets of random variables.
8. Pi value can be determine through Monte Carlo Simulation. Rand() generates a value
between 0 and 1, if a function = 1 β 2 Γ π πππ( ) ππ π’π ππ, we will get a random number
Financial Modeling using Excel
P a g e | 107
between -1 to +1. If more than 1000 random numbers are generated by using this formula
for two variables and if it is plotted on scatter diagram, a rectangular representation arises
9. Pi= (πππππ’ππππππππ ππ ππππππ)/(ππππππ‘ππ ππ ππππππ), now values of random variables
can be use to set the equation of circle. Equation of circle=π₯2 + π¦2 = π2. Let try to impose
a circle over the rectangle having radius=1. The rectangular representation obtained by 1-
2*Rand() is having length=breath=2
10.
11. Circle can be imposed over rectangular random representation by using IF function in MS
Excel. As we know the equation of circle=π₯2 + π¦2 = π2. Consider a point inside the circle
having coordinate (x,y) where distance of this point from centre (0,0) so, the given equation
is π₯2 + π¦2 = 1
12. Area for the region for which the values less than 1 is considered when π₯2 + π¦2 < 1, that
value would not be considered turn to be 0. (π₯2 + π¦2 > 1)
13. Value of Ο=ππππ ππ ππππππ
ππππ ππ ππππ‘πππππ where it is multiplied by 4 as rectangle being Standard
rectangle having area of 4 . Value so obtained is 3.132 which is make more realistic if more
data is taken.
14. Brownian Motion is the random motion of the particle suspended in the fluid resulting from
the collision with the quick atoms or molecules in a gas or liquid where the relative velocity
of particle is directly proportional to square root of temperature. The application of
Brownian Motion is relevant study of Random Walk Hypothesis on Stocks.
Financial Modeling using Excel
P a g e | 108
15. In order to work on Brownian Motion in MS Excel Rand () is used where to derive the
value between -1 to +1 , the formula which is used is 1-2 x Rand ().
16. Suppose there are two variables called x and y. Relative change is x and y is measured as
dx and dy where both imagine at point 0. Then through Simulation their relative change
can be determined where each step the change will be, πβππππ ππ‘ π₯π‘β π π‘ππ =
πβππππ ππ‘ (π₯ β 1)π‘β π‘πππ + ππ₯( πππππ‘ππ£π πβππππ ππ‘ π₯π‘β π π‘ππ)
17. In order to work on Monte Carlo Simulation on Stocks, NORMINV function is used.
NORMINV function in MS Excel will generate cumulative normal distribution value for a
data having specified mean and standard deviation under certain probability factor.
18. Probability factor will be here generated through RAND() which will generate a random
number between 0 to 1. Daily Mean=0, Daily standard
deviation=πβππππππ π π‘ππππππ πππ£πππ‘πππ
βπ
19. Random Return on the stock can be calculated by NORMINV function. Each day
movement on the stock will be calculate by formula=
βππ π‘ππππππ πππππ Γ (1 + πππππππ£(ππππ( ), 0, (π β ππππππ π
βπ))
20. Once the movement is created and it average can be taken. Then through one way data
Figure similar values can be created which helps to analyze under multiple scenario.
3.7 Questions to Discuss
Two Marks Questions
1. What is Simulation?
2. How Simulation helps in business decision making?
3. How Rand() works?
Five Marks Questions
4. Using Monte Carlo Simulation, demonstrate the calculation of Pi?
Financial Modeling using Excel
P a g e | 109
5. Using Monte Carlo Simulation, demonstrate the Brownian Motion?
6. How Monte Carlo Simulation can be done in MS Excel? Explain with an example?
7. Explain all random number generation tools with examples?
Ten Marks Questions
1. Demonstrate the application of Monte Carlo Simulation on a stock with the statistical tools
calculating the VaR at 95% confidence interval
3.8 Problems to Practice
1. To generate a number between -1 and 1, which formula can be used
a. Rand()
b. 1 β 2 Γ π πππ()
c. 2 β 3 Γ π πππ()
d. 1 + 2 Γ βπ πππ( )
2. What formula is used to determine daily volatility from annual volatility?
a. ππππ’ππ π£ππππ‘ππππ‘π¦
β252
b. (ππππ’ππ π£ππππ‘ππππ‘π¦) Γ β252
c. (ππππ’ππ π£ππππ‘ππππ‘π¦) + β252
d. None of These
3. To put a random number between 0 and 1 which function can be used?
a. Rand()
b. Randbetween
c. Random Number Generation
d. All of them
4. Normal Distribution Pattern can be in the form ofβ¦β¦β¦β¦β¦β¦β¦β¦β¦β¦β¦β¦.
a. Leptokurtic
b. MesoKurtic
c. Platykurtic
Financial Modeling using Excel
P a g e | 110
d. All of These
5. Descriptive Statistics of a data can be determined through β¦β¦β¦β¦β¦β¦β¦β¦β¦.
a. Data Figure c. Solver
b. Data Analysis d. Goal Seek
6. To observe the Brownian Motion on a stock to justify the Random Walk Hypothesis,
which statistical representation help?
a. Histogram
b. Scatter Diagram
c. Line Graph
d. Candle Stick Model
7. What is the short cut key to remove formulaes from worked excel document?
a. Ctrl+C, Alt+E+S+V
b. Ctrl+C, Alt+E+S+F
c. Ctrl+C, Alt+E+S+T
d. Ctrl+C, Alt+E+S+N
8. What is the syntax for the VLOOKUP() function?
a. Vlookup(lookup_value,range_lookup,col_index_num, Figure_array)
b. Vlookup(lookup_value, col_index_num, Figure_array,range_lookup,)
c. Vlookup(lookup_value, Figure_array range_lookup,col_index_num,)
d. Vlookup(lookup_value,Figure_array1,Figure_array2,range_lookup)
9. To use the vlookup function, in the Figure, which column should the lookup_value be
located in?
a. In the left most column
b. In the right most column
c. In the middle column
d. Anywhere
10. To make the content inside the data Figure static so that the change in content in that
cell does not impact the content of other cells. What should we do?
a. Format cell>>Custom>>#,###
b. Format cell>>Custom>>General
Financial Modeling using Excel
P a g e | 111
c. Format cell>>Custom>>0,000,0000
d. Format cell>>Custom>>#,##,0.00(Red)(#,00,#)
Answers
1-a, 2-a, 3-a, 4-d, 5-b, 6-b, 7-a, 8-c, 9-a, 10-b
3.9 References
1. Building Financial Models- A guide to creating and interpreting Financial Statements-
John S.TJIA
2. Pandey I. M, (2003) Financial Management, Vikas Publishing House β New Delhi
3. Financial Modeling-Simon Benninga
Websites:
1. Introduction to Monte Carlo Simulation- http://office.microsoft.com/en-in/excel-
help/introduction-to-monte-carlo-simulation-HA001111893.aspx
2. Random Number in Excel-
http://spreadsheets.about.com/od/excelfunctions/qt/080218_randbetw.htm
Video Links:
1. Monte Carlo Simulation in Excel-Dr Gerard Verschuuren-
http://www.youtube.com/watch?v=UeGncSFijUM
2. Basic Monte Carlo Simulation of a Stock Portfolio-
http://www.youtube.com/watch?v=Q5Fw2IRMjPQ
3. Random Number Generation in MS Excel-Dr Salimian-
http://www.youtube.com/watch?v=rBEMr6CyK7g
Financial Modeling using Excel
P a g e | 112
Module IV: Accounting in Excel
Financial Modeling using Excel
P a g e | 113
Module IV: Accounting in Excel
Contents
4.1 Objectives-Excel in Accounting
4.2 Introduction of Accounting
4.2.1 Understanding Income Statement, Balance Sheet, Cash Flow Statement
4.3 Common Size Financial Statement
4.4 Income Statement Forecasting-TCS-2015
4.5 Balance Sheet Forecasting-TCS-2015
4.6 Analysis of Financial Statement
4.6.1 Margin Ratios-Gross Profit Margin, Operating Profit Margin, Net Profit Margin
4.6.2 Capital Gearing Ratio-Interest Coverage Ratio, PBT/Interest, Debt/Equity Ratio
4.6.3 Price Earning Ratio, Price Book Value Ratio
4.6.4 Return on Investment, Return on Equity
4.7 Summary
4.8 Questions to Practice
4.9 Problems for Practice
4.10 References
4.1 Objective of Excel in Accounting
By the end of this Module, you should be able to:
Framework of construction of Financial Statement in MS Excel
Financial Modeling using Excel
P a g e | 114
Establish the trend of financial figure movement of Y on Y basis
Analyze Financial Statement through MS Excel
Forecast Financial Statement based on trend analysis
Excel in Accounting
4.2 Introduction of Accounting
Accounting is the language of business which measures the business economic performance. It is
the part of the information system of a business enterprise. It provides financial information
concerning the activities of an enterprise to a diverse group of people such as shareholders,
managers, creditors, tax authorities etc. On the basis of purpose the accounting is used, accounting
is used, it is divided into three parts-financial accounting, cost accounting and management
accounting. Financial Accounting is mainly concerned with recording business transactions in the
book of account for the purpose of presenting final accounts to management, shareholders and tax
authorities etc. Cost Accounting is the techniques and processes of ascertaining costs. Management
accounting focuses on the measurement, analysis and reporting of information for internal use by
management.
Income Statement or Profit and Loss statement is the financial statement of company show
companyβs revenue and expenses during a particular period. It indicates how revenues get
transformed into net income. It is the summary of how business incurs its revenue and expenses
through both operating and non operating activities.
Balance Sheet is the snapshot of company financial condition. A standard balance sheet has three
parts-assets, liabilities, ownership equity. Total Asset in a balance sheet is equal to liabilities plus
ownerβs equity. Assets can be Current Asset- Cash and cash equivalent, Account Receivable, Pre-
paid Expenses. Non Current Asset-Property, Plant and Equipment, Investment Property like Real
Estates, Intangible Assets, Biological Assets. Liabilities include Account Payable, Provision for
warrants, Financial Liabilities like Promissory Notes and Corporate Bonds, Deferred Tax
Liabilities and Deferred Tax Assets, Unearned Revenue for the services paid by customers but not
yet provided. Equity includes issued capital and reserves attributable to equity holder of parent
Financial Modeling using Excel
P a g e | 115
company, non controlling interest in equity. Following disclosures can be allowed- number of
shares authorized, issued, fully paid, par value of shares, Reconciliation of share value at the
beginning and end of period, shares reserved for issuance under options and contracts etc.
Cash Flow Statement is the financial statement that shows how changes in balance sheet accounts
and income affect cash and cash equivalent and it can be studied under operating, financial and
investing activities. Cash Flow Statement concerned with the flow of cash in and out of business.
Balance Sheet and Profit and Loss Statement reflects accrual basis accounting used by firms while
Cash Flow Statement which reflects liquidity of company talks about cash and cash equivalent.
Cash flow statement under operating activities includes receipt from the sale of goods and services,
Interest receipt, receipt and payable to employee/supplier. In order to determine cash flow from
operating activities, the following enteries are adjusted to net profit like non cash expenses like
depreciation and amortization, deferred tax, dividend received, any gain or loss associated with
sale of non current asset. Cash Flow from investing activities includes purchase or sale of an asset
like land, building, marketable securities etc , loans made to suppliers or received from customers,
payment related to mergers and acquisitions. While cash flow from financial activities includes
inflow of cash from investors such as banks and shareholders, as well as outflows of cash to
shareholders when dividend is paid, sale or repurchase of companyβs stock, payment of dividend
tax, repayment of debt principal including capital lease.
4.3 Common Size Financial Statement
Common Size Financial Statements usually involve the balance sheet and the income statement.
It is simply created to display the line items on a statement as a percentage of one selected or
common figure. Creating common size financial statements makes it easier to analyze a company
over time and compare it with peers. Using common size financial statements helps investors to
spot trends that raw financial statement may not uncover. The common figure for a common size
balance sheet analysis is total assets. Instead of total assets sum of total liabilities and shareholder
equity which is the Capital Employed can also be taken as a common figure in the balance sheet
analysis. The common size strategy from the balance sheet leads insight into firm capital structure
and compare it with rivals. An investor by looking figures analyze and then on that basis conclude
about prospect of company. It is important to add short-term and long-term debt together and
Financial Modeling using Excel
P a g e | 116
compare this amount to total cash on hand in the current assets section. It lets the investor know
how much of a cash cushion is available, or if a firm is dependent on the markets to refinance debt
when it comes due.
The common figure for Income Statement is top line sales. For cash flow items are also expressed
in term of % sales. This can give insight into number of cash flow items including Capital
Expenditure as a percentage of revenue. Debt issuance is another important figure in proportion to
the amount of annual sales. In order to work on Common Size Balance Sheet in Excel Template I
have considered the stock TCS. Mentioned below is the Standalone Balance Sheet of TCS
comparing the financial figures from 2010 to 2014
Financial Modeling using Excel
P a g e | 117
Figure-4.1 Historical Balance Sheet value of TCS
Here, Total Liabilities=Total Debt+ Net Worth
And Net Worth=Total Share Capital+ Capital Reserve+ Revaluation Reserve
Where Total Share Capital= Equity Share Capital+ Share Application Money+ Preference Share
Capital
Financial Modeling using Excel
P a g e | 118
Figure-4.2 Common Size Balance Sheet value of TCS in term of Capital Employed
Financial Modeling using Excel
P a g e | 119
Figure-4.3 Historical Balance Sheet value of TCS
Here, Total Current Assets=Inventories+ Sundry Debtors + Cash and Bank Balance
Total CA, Loans & Advances= Total CA+ Loans & Advances+ Fixed Deposits
Net Current Assets=Total Current Assets-Total CL & Provisions
Financial Modeling using Excel
P a g e | 120
Figure-4.4: Common Size Balance Sheet value of TCS in term of Total Assets
Financial Modeling using Excel
P a g e | 121
Similarly the similar representation can be done for Profit and Loss Statement.
Financial Modeling using Excel
P a g e | 122
Figure-4.6: Common Size P&L value of TCS in term of % of sales
Financial Modeling using Excel
P a g e | 123
In order to work on forecasting, the first step is to identify the next year total income which is the
sum of Net Sales+ Other Income + Stock Adjustments. I assume Stock adjustments and Excise
Duty will remain zero for next year also as they are 0 from last 2 years. If stock adjustments and
Excise Duty arise also they will not impact the forecasting outcome as such as their values very
less. So the next year movement in Total Income will depend on movement of Net Sales and Other
Income. I assume geometric mean to track the movement .
Figure-4.6: Measuring the growth in sales
Figure-4.7: Measuring the growth in sales
Financial Modeling using Excel
P a g e | 124
Next Year Net sales will be probably== 64,672.93 π₯ (1 + 29.4312%) = 83,742.47399
crores. In order to work on Other Income I considered only last 3 year data as after 2011 there was
significant change in the source of other income and I believe this change will increase in
compounding.
Figure-4.8: Measuring the growth in sales (CAGR Model)
So, the total income for March 2015=3680.7475+ 83, 742.47399=87,423.22145 Crore.
Figure-4.9: Calculation of total income
On the basis of this other accounting figures can be determined. I will take simple average for that.
Financial Modeling using Excel
P a g e | 125
Figure-4.10: Calculating the average of P & L items
Now each Category inputs can be estimated on the basis of % age of total income. Total Income
is already determined.
4.4 Income Statement Forecasting-TCS-March 2015
Financial Modeling using Excel
P a g e | 126
Financial Modeling using Excel
P a g e | 127
Financial Modeling using Excel
P a g e | 128
Figure-4.11: Forecasted FY 2015 accounting data of P&L
4.5 Financial Forecasting-Balance Sheet-TCS
In a Balance Sheet, β of Total Assets= β of total Liabilities, Geometric Mean or Arithmetic Mean
can be used to determine the average of previous movement and on the basis of this, next
movement can be determined.
Figure-4.12: Calculation of CAGR of total liabilities
I assume that the company is not issuing any Preference Share , there Is no revaluation reserve.
Changes can be observed in Equity Share Capital and in Capital Reserve.
Financial Modeling using Excel
P a g e | 129
Financial Modeling using Excel
P a g e | 130
Figure-4.13: Forecasted FY 2015 accounting data of Balance Sheet
Final Consolidated outcome for forecasting is:-
Financial Modeling using Excel
P a g e | 131
Figure-4.14: Forecasted FY 2015 accounting data of Balance Sheet
4.6 Analysis of Financial Statement
Financial Statement Analysis is an important tool to understand the nature the historical
performance by a company. Let understand the company through the perspective of profitability
decision undertaken.
Figure-4.15: Analyzing the calculation of Profit Margin
Financial Modeling using Excel
P a g e | 132
There is no as such differential in movement between Operating Profit Margin and Net Profit
Margin. The differential factor between two is the interest which does not move up significantly
showing company debt value does not change significantly. To prove this historical value of
interest can be taken from P & L account and long term debt can be taken from Balance Sheet.
Figure-4.16: Graph between long term debt and Interest
Since there is there is as such not much differential between operating profit margin and net profit
margin so the factors which leads to volatility in gross profit margin can be analyzed.
Financial Modeling using Excel
P a g e | 133
Figure-4.17: Impact of COGS on Gross Profit
Under Cost of Good Sold, the 4 factors are included:-
1. Raw Material
2. Power and Fuel Cost
3. Employee Cost
4. Other Manufacturing Expenses
Power and Fuel Cost is not significant, probably other three are more significant.
Figure-4.18: Component of COGS
Employee Cost is most important cost factor impacting COGS maximum. Gradually the impact of
non direct expenses can also be highlighted.
Financial Modeling using Excel
P a g e | 134
Liquidity position of the TCS is in better shape as highlight by their Current Ratio and Quick Ratio.
Figure-4.19: Liquidity Ratio across periods
Figure-4.20: Calculation of % of non direct expenses of total expenses
Non Direct Expenses plays a pivotal role in the costing pattern of TCS. Now Debt/Equity Ratio
can be analyzed.
Financial Modeling using Excel
P a g e | 135
Figure-4.21: debt by equity ratio
Similarly, Interest Coverage Ratio can be determined to see the contribution of debt on the profit.
Figure-4.22: Interest Coverage Ratio calculation
Now if I compare Debt/Equity Ratio with PBT/Interest ratio
Financial Modeling using Excel
P a g e | 136
Figure-4.23: Comparison of PBT/Interest and Debt/Equity
Debt/Equity Ratio is keep on increasing while PBT/Interest is volatile where concept on leverage
states that if Return on Investment>Rate of Interest then EPS, ROE goes up significantly. In order
to see the impact of leverage on Return on Investment let find out the relationship.
Figure-4.24: Analyzing ROA, ROE with Rate of Interest
Since always ROI>Rate of interest always so EPS goes up significantly
Financial Modeling using Excel
P a g e | 137
Now, in order to work on Price to Earning Ratio and Price to Book Value ratio, forecasting of the
likely price of TCS stock for March 2015 can be done assuming the growth rate followed in past
will show the similar movement in future.
Figure-4.25: Comparison of PE ratio with Price to Book Value Ratio
If I compare PE ratio with Price to Book Value ratio it seems TCS is too much over valued but
average annual growth rate of 30% justifies this over valuation.
Financial Modeling using Excel
P a g e | 138
4.7 Summary
1. Accounting is the language of business which measures the business economic
performance. It provides financial information concerning the activities of an enterprise to
a diverse group of people such as shareholders, managers, creditors, tax authorities etc
2. Balance Sheet is the snapshot of company financial condition. Standard balance sheet has
three parts-assets, liabilities, ownership equity.
3. Assets can be Current Asset- Cash and cash equivalent, Account Receivable, Pre-paid
Expenses. Non Current Asset-Property, Plant and Equipment, Investment Property like
Real Estates, Intangible Assets, Biological Assets. Liabilities include Account Payable,
Provision for warrants, Financial Liabilities like Promissory Notes and Corporate Bonds,
Deferred Tax Liabilities and Deferred Tax Assets
4. Cash Flow Statement is the financial statement that shows how changes in balance sheet
accounts and income affect cash and cash equivalent and it can be studied under operating,
financial and investing activities
5. Balance Sheet and Profit and Loss Statement reflects accrual basis accounting used by
firms while Cash Flow Statement which reflects liquidity of company talks about cash and
cash equivalent.
6. Cash flow statement under operating activities includes receipt from the sale of goods and
services, Interest receipt, receipt and payable to employee/supplier.
7. Cash flow from financial activities includes inflow of cash from investors such as banks
and shareholders, as well as outflows of cash to shareholders when dividend is paid, sale
or repurchase of companyβs stock, payment of dividend tax, repayment of debt principal
including capital lease.
8. Common Size Financial Statement are simply created to display the line items on a
statement as a percentage of one selected or common figure.
9. Using common size financial statements helps investors to spot trends that raw financial
statement may not uncover.
10. The common figure for a common size balance sheet analysis is total assets or Capital
Employed while for Income Statement is Net Sales.
Financial Modeling using Excel
P a g e | 139
11.
12. In order to start working on forecasting of Financial Figures, estimation of next year Total
Liabilities is important
13. Estimation of Total Liabilities is done through Geometric Mean or through Arithmetic
Mean
14. Once the Total Liabilities is estimated, other financial figures can be determined as already
other financial figures were represented as %
15.
Financial Modeling using Excel
P a g e | 140
16. Like the steps followed to calculate Total Liabilities in Source of Funds in Balance Sheet
similarly Net Sales in Profit and Loss statement, Total Assets in Application of Funds is
estimated
17. Through % of net sales or through % of total assets or % of total liabilities other items in
different years can be estimated, then average of them can be done to get a cumulative
figure which when multiplied with forecasted figure mentioned in above point can create
new estimated financial figure.
18. Once all required financial figures is determined, then the forecasted financial statement
can be complete by following basic accounting principles like at last total assets should be
equal to total liabilities, Total Current Assets=Inventories+ Sundry Debtors + Cash and
Bank Balance, Total CA, Loans & Advances= Total CA+ Loans & Advances+ Fixed
Deposits, Net Current Assets=Total Current Assets-Total CL & Provisions
19. Financial Statement Analysis is an important tool to understand the nature the historical
performance by a company. It is process of reviewing and analyzing a companyβs financial
statement. It is a process of identifying the financial strengths and weakness of the firm by
establishing the relationships between the items of the balance sheet and the profit and loss
statement
20. Ratio is defined as β the indicated quotient of two mathematical expressionsβ , in financial
analysis, a ratio is used as a benchmark for evaluating the financial position and
performance of a firm. Basically all financial ratios are divided into four important
categories:-
a. Liquidity Ratios
b. Leverage Ratios
c. Activity Ratios
d. Profitability Ratios
21. Liquidity Ratio measures the firmβs ability to meet current obligations. Leverage Ratio
shows the proportions of debt and equity in financing the firmβs assets. Activity Ratio
reflects the firmβs efficiency in utilizing its asset. Profitability ratios measures the overall
performance and effectiveness of firm. Liquidity Ratio consist of Current ratio, quick ratio,
Financial Modeling using Excel
P a g e | 141
cash ratio, net working capital ratio. Leverage ratio includes debt ratio debt-equity ratio,
interest coverage ratio. Activity Ratio includes Inventory turnover ratio, Debtor turnover
ratio, Asset Turnover ratio, Current asset turnover ratio, working capital turnover ratio.
Profitability ratio includes gross profit margin, net profit margin, return on investment,
return on equity, price earning ratio
4.8 Questions to Discuss
Two Marks Questions
1. What is Accountancy?
2. Why Accountancy is considered as a language of business?
3. What is Balance Sheet?
4. What is Profit and Loss statement?
5. What is the difference between Cash Flow Statement and Fund Flow Statement?
6. What is Common Size Financial Statement?
7. What is difference between ROI and ROE?
Five Marks Questions
1. Explain with steps the process of Financial Forecasting?
2. What is the need of analysis on Financial Statement? How it will help in decision making
before investment in market?
3. How the projection of Income Statement can be done?
4. What interpretation can be observe after looking at leverage ratios of a company?
5. Explain the relevance of Du-Point Analysis?
Financial Modeling using Excel
P a g e | 142
Ten Marks Questions
1. What is Fundamental Analysis? How understanding of Financial Statement help in
Investment decision? What are the tools helps in understanding the financial figures?
Explain with an example?
4.9 Problems to Practice
1. Which among is not the part of liquidity ratio?
a. Current Ratio c. Quick Ratio
b. Activity Turnover Ratio d. Net working capital ratio
2. Which factor create differential in operating profit margin and net profit margin?
a. Change in Interest Rate
b. Change in Sales and administrative expenses
c. Change in Non cash expenses
d. Change in Net Working Capital
3. Which item is not the part of cost of good sold?
a. Raw Material
b. Sales and Administrative Expenses
c. Power and Fuel Cost
d. Employee Cost
4. If the company share price falls, then its P/E ratio and dividend yield willβ¦β¦β¦β¦β¦β¦..
a. PE ratio and dividend yield both decreases
b. PE ratio decreases, dividend yield no change
c. PE ratio decreases, dividend yield increases
d. Both PE ratio and dividend yield inceases
5. What is the shortcut key of working on Filter?
a. Alt+d+f+f c. Alt+e+f+f
b. Alt+d+e+f d. Alt+f+e+f
6. Net Worth of a company is β¦β¦β¦β¦β¦β¦β¦β¦β¦β¦
a. Total Share Capital+ Capital Reserve+ Revaluation Reserve
Financial Modeling using Excel
P a g e | 143
b. Total Share Capital+ Capital Reserve
c. Capital Reserve+ Revaluation Reserve
d. None of These
7. Which of the following statements are true in relation to common size statement analysis?
a. It examines the changes over time
b. It may concentrate on gross margin percentage
c. It concentrates on different geographic segments of production
d. It may concentrate on relative size of current assets
8. Which ratios is not use to access the capital gearing of an enterprise?
a. Debt Ratio
b. Long term debt to assets ratio
c. Interest coverage ratio
d. Debt to equity ratio
9. Which of the following are not long term solvency ratio?
a. ROI
b. Interest Coverage Ratio
c. Debt Ratio
d. Long term debt to assets ratio
10. Which of the following is true?
a. An unlevered enterprise has ROE higher than ROTA
b. The more an enterprise relies on debt the more control it has on its finance
c. When the enterprise has a ROE greater than its interest rate, ROTA exceeds ROE
d. None of these
Answers
1-b, 2-a, 3-b, 4-c, 5-a, 6-a, 7-a, 8-b, 9-a, 10-c
4.10 References
Financial Modeling using Excel
P a g e | 144
1. Khan MY Jain PK(2008) Financial Management 3rd Edition Mc Graw Hill Publication-
New Delhi
2. Pandey I.M (2003) Financial Management, Vikas Publishing House-New Delhi
3. Building Financial Models- A guide to creating and interpreting Financial Statements-
John S Tjia
4. Financial Modeling-Simon Benninga
Websites:
1. Financial Forecast Analysis- http://office.microsoft.com/en-in/templates/financial-
forecast-analysis-8-years-TC101877397.aspx
2. How to forecast the Income Statement-
http://financialmodelingtutorial.com/how-to-forecast-income-statement/
3. Analysis of Financial Statement- http://www.slideshare.net/MohdAadil/analysis-of-
financial-statements-12201127
Video Links:
1. Forecasting Financial Statements-Shav Van
http://www.youtube.com/watch?v=hRqchLs4mUc
2. Forecasting Financial Statements-Prakash Kumar-
http://www.youtube.com/watch?v=CXwwjBiWPS0
3. Analysis of Financial Statements- https://www.youtube.com/watch?v=x6dd0IHuC98
Financial Modeling using Excel
P a g e | 145
Module V: Excel in Valuation and Portfolio Theory
Financial Modeling using Excel
P a g e | 146
Module V: Excel in Valuation and Portfolio Theory
Contents
5.1 Objectives-Excel in Valuation and Portfolio Theory
5.2 Business Valuation
5.2.1 Approaches on Business Valuation
5.2.2 Discussion on Project Cash Flow
5.2.3 Discussion on Discounted Cash Flow Valuation
5.3 Detailed discussion on FCFE and FCFF items
5.4 Projection of Income Statement
5.5 Understanding Terminal Value of Cash Inflow
5.6 Financial Model-Corporate Valuation
5.7 Risk Analysis on Corporate Valuation
5.8 Excel in Portfolio Management
5.9 Technical Analysis on stock
5.10 Portfolio Optimization using MS Excel-Solver
5.11 Summary
5.12 Question for Practice
5.13 Problems for Practice
5.14 References
Financial Modeling using Excel
P a g e | 147
5.1 Objective-Excel in Valuation and Portfolio Theory
By the end of this Module, you should be able to:
Understand why Business Valuation is so important
What are the different approaches follows to do Business Valuation?
What are the factors on which Project Cash Flows depends?
What are different sources of Project Cash Flows?
How to develop financial models for Corporate Valuation?
Understand different forms of risks involved in Corporate valuation decisions
Represent Portfolio Management applications in MS Excel
5.2 Business Valuation
Business valuation is a series of procedures used to anticipate the economic value of an ownerβs
interest in a business. Valuation is used by firm, their professionals to determine the price they are
willing to pay or receive to effect a sale of a business. The technique of hypothetical conditions
based on assumption that the business will continue forever in its current form (going concern).
Valuation is used by Insiders of a firm like CEO, CFO, Operating Manager to identify
opportunities and it help them in decision making. It is used by Investors like Hedge Funds/Pension
Funds, Insurance Company, Retail Investor before investment or to find out appropriate
Financial Modeling using Excel
P a g e | 148
investment opportunity, it is used by Consultants like Investment Bank, Credit Rating Agencies,
Brokerage House to suggest/recommending their investors the ideal source/avenue for investment.
Valuation help firm during Merger and Acquisition, work on defensive strategies against hostile
takeover, assisting during listing, subsequent offering and de-listing etc.
Approaches of Valuation can be:-
1. Macro Analysis
2. Strategic Analysis
3. Retrospective/ Prospective Analysis
Macro Analysis-
Monetary Policy- Process by which government, Central Bank, Monetary Authority of
country controls the supply of money, availability of money
Fiscal Policy- Government policy that attempt to influence the direction of economy
through the changes in Government taxes, Fiscal allowances
Stages of Business Cycle- Recovery, Early Expansion, Late Expansion, Early Recession,
late Recession
Strategic Analysis-
Industry Life Cycle-Pioneering, Rapid Growth, Mature Growth, Stabilization and Market
Maturity, Deceleration of growth and decline
Porterβs Five Forces- Rivalry among the existing competitors, Threat of new entrant,
Threat of Substitute products, Bargaining Power of Buyers, Bargaining power of Supplier
Retrospective Analysis-
Financial Statement Analysis of Company
Understanding the accounting policies-conservative/aggressive
Financial Modeling using Excel
P a g e | 149
Ratio Analysis of the Company-Liquidity, Operating, Risk and Growth
Ratio Comparison with other companies in the industry/sector
Companies for investment can be growth companies, defensive companies, cyclical companies
and speculative companies. Growth Company is where the management has the ability to
consistently select investments or projects which earn higher returns than required by their risk.
Defensive Companies have earnings that are relatively insensitive to downturn in economy.
Cyclinal Companies tends to follow the business cycle. Speculative Companies have assets that
are risky but the assets are potential to generate very large earnings. Valuation is done on
Enterprise scale or of Equity. For Enterprise valuation, valuation of debt is included.
Value of firm is determined by four factors-it capacity to generate cash flows from assets in place,
the expected growth rate of these cash flows, the length of time it will take the firm to reach stable
growth, and the cost of Capital. In discounted cash flow valuation, estimation of value of asset is
determined by discounting back the expected cash flows on the asset at the rate that reflects the
riskiness. In a sense the intrinsic value of an asset is determined. The value of any asset is a function
of the cash flows generated by that asset, the life of asset, the expected growth in the cash flows,
the riskiness associated with them. In other word, it is present value of the expected cash flows on
the asset. Value of Asset==βππ ππ πΉπππ πΆππ β πΉπππ€π
(1+ππ΄πΆπΆ)π
Free Cash Flow to the Firm=EBIT(1-tax%)+Depreciation-Capital Expenditure-β in Noncash
Working Capital.
Discounted Cash Flow analysis show us the expected value of business by the reference to future
cash flows. Discounted cash flow analysis involves estimating the present value of future cash
flows that business being valued is expected to generate. DCF analysis requires high quality
historic and projected financial information on the business. The quality of financial information
is crucial to DCF valuation-βgarbage inβ¦..garbage outβ.
The particular information required will depend on the nature of company being valued but at the
most basic level, detailed assumptions over the projected period are required for:
Turnover
Operating Margins
Financial Modeling using Excel
P a g e | 150
Interest Charges
Taxation Charges
Depreciation Charges
Capital Expenditure
Working Capital Movements
There is danger of over generalizing in preparing cash flow forecasts-assuming a constant growth
rate after first couple of years. It is important to question the forecast and consider all cyclical,
industry specific and other general or macroeconomic influences. Rather than discounting the cash
flows indefinitely into the future, a terminal value based on company long term growth rate
(perpetual growth rate methodology) or a multiple of final year earnings or cash flows ( exit
multiple methodology), is usually assumed after a period of say five to ten years. The terminal
value represent a high proportion of the overall valuation of business (particularly in a company
pursuing long term growth and investing heavily during the forecast period).
A DCF valuation is only as accurate as the assumptions/ key sensitiveness underlying it and it is
easiest way to establish a margin of error as a principal assumption.
In theory, the choice of discount rate or assessment of internal rate of return will depend critically
on the cost of debt and market risk premium in the country of target, the share price volatility of
the target and the level of debt of an optimal capital structure. However a purchaser would have to
consider other issues, such as his funding cost and value of business to it.
Financial Modeling using Excel
P a g e | 151
Figure 5.1: Discussion on Cash Flows and different ways to understand
DCF valuation focuses on the cash flows generated by one part of the business β the Operating
Assets.
A business generates cash through its daily operations of supplying and selling goods or services.
Some of the cash has to go back into the business to renew fixed assets and support working capital.
If the business is doing well, it should generate cash over and above these requirements. Any extra
cash is free to go to the debt and equity holders. The extra cash is known as Free Cash Flows.
Formulas of FCFF are as per below.
FCFF = NI + D&A β change in WC + Interest * (1-tax rate) β Capex
Projections Project free cash flows over the forecast period (5 - ) 10 years Project enough years to provide for achieving a normalized cash flows
Terminal Value Trading multiples of terminal year Net Profit, EBITDA, EBIT Trading multiples of (terminal year + 1) Net Profit, EBITDA, EBIT βPerpetuity valueβ of cash flows after terminal year
Discount Rate
Present Value
WACC of companies in similar businesses to reflect the relative risk Cost of Equity using CAPM Model
Determine a range of values for the enterprise by discounting the projected free cash flows and terminal values to the present
Adjustments Adjust valuations for all assets & liabilities not accounted for in projections Incremental shares are calculated using treasury stock method
Sensitivity Analysis DCF is sensitive to changes in growth rate & margin assumptions Sensitivity with respect to terminal value and discount rate
Financial Modeling using Excel
P a g e | 152
FCFF = EBITDA * (1- tax rate) + (D&A * tax rate) β change in WC β Capex
Project Cash Flows depends on:-
Industry cycle and competitive structure (operating margins)
Economic cycle
Known significant events
Useful life of asset (e.g. oil well, mine)
Comfort of forecaster
Length of any competitive advantage
Allow enough time to reach a normalised or mature level of cash flows which assumes
constant growth and/or capital needs into perpetuity
While projections become less reliable the further out they go, it may be necessary to go
out up to 10 years or more in order to reach a normalized level of free cash flow.
Sources of Project Cash Flows
The free cash flows from a business can be projected using information about the industry in which
a business operates and information specific to the business. A variety of sources can be used, such
as research reports, S&P industry surveys, industry journals and manuals, and other miscellaneous
sources. DCF analysis is an attempt to look at the companyβs pure operating results free and clear
of extraordinary items, discontinued operations, onetime charges, etc. It is also extremely
important to look at the historical performance of a company or business (margins, growth) to
understand how future cash flows relate to past performance. A companyβs FCFF represents the
return available to both sources of its capital; debt lenders and equity investors. Free cash flows
there reflects the cash generated by the assets of a business, irrespective of how such assets are
financed.
In summary, DCF projections should be based on:
Historical Performance
Company or Management Projections
Industry Estimates
Financial Modeling using Excel
P a g e | 153
Industry data
Macroeconomic data
5.3 Detailed Explanation of FCFF line items
β’ Net Income- Net income is taken directly from the Income statement. It represents the
income available to shareholderβs after taxes, depreciation, amortization, interest expenses
and the payment to preferred dividends
Non-cash items Adjustment to Net Income
Depreciation Addition
Amortization Addition
Losses Addition
Gains Subtraction
Restructuring charges
(expense)
Addition
Reversal of restructuring reserve (in Subtraction
Amortization of bond discount Addition
Amortization of bond premium Subtraction
Deferred taxes Addition
β’ After tax interest- Since interest is tax deductible, after-tax interest is added back to the net
income. Interest cost is a cash flow to one of the stakeholderβs of the firm (debt holders)
and hence, it forms a part of FCFF.
β’ Non Cash Charges- Non Cash Charges includes items that affect net income but do not
involve the payment of cash. Some of the most common non cash items are listed below.
a. Capital Expenditure- Investment in fixed assets is the cash outflow required for the
company to maintain and grow its operations. It is possible that a company acquires
assets without expending cash by using stock or debt . Analyst should review the
Financial Modeling using Excel
P a g e | 154
footnotes, as these asset acquisitions may not have used cash in the past, but may affect
the forecast of future FCFF .
b. Change in Working Capital- The working capital changes that affect FCFF are items
such as Inventories, Accounts Receivables and Accounts Payable. This definition of
working capital excludes cash and cash equivalents and short-term debt (notes payable
and the current portion of long term debt payable). Do not include non-operating
current assets and liabilities, e.g. dividends payable etc.
Example of FCFF and FCFE
Consider the Balance Sheet and Profit & Loss Statement of XYZ Company over two year time
period.
Financial Modeling using Excel
P a g e | 155
Solution= Determination of Change in Net Working Capital= Change in A/C Payable+ Change
in A/C Receivable+ Change in Inventory.=(90-45)+(120-90)+(60-60)=75
Financial Modeling using Excel
P a g e | 156
Then Capital Expenditure =Gross Property, Plant & Equipment-Accumulated Depreciation-
Depreciation
Capital Expenditure=(1200+900)-(570+420)-150=300
Net Borrowing=Change in short term debt + Change in long term debt=(60-30)+(342-300)=72
Then FCFF through EBIT formula will be=EBIT* (1- tax rate) +D β change in WC β Capex
=
FCFF=285*(1-30%)+150-75-300=-25.5
While FCFF through EBITDA formula would be =
FCFF = EBITDA * (1- tax rate) + (D&A * tax rate) β change in WC β Capex
=FCFF=435*(1-30%)+150*30%-75-300=-25.5
Financial Modeling using Excel
P a g e | 157
While FCFE=FCFF-Interest Expenses*(1-tax rate)+Net Borrowing
FCFE=-25.5-45*(1-30%)+72=15
5.4 Projection of Income Statement
Project the operating results and free cash flows of a business as discussed over the forecast period
(typically 5 to 10 years). Project forward enough years to achieve a βnormalisedβ (ex-growth) or
βmatureβ level of cash flows prior to deriving a terminal value. Note that while projections become
less reliable further out, it may still be necessary to go out up to 10 years or more in order to reach
normalised levels. You should be careful to ensure that, for a cyclical business, you should end the
forecast period at a mid point in the cycle. Furthermore, if the company you are forecasting is
expected to have a competitive advantage, your period should be of sufficient length to capture the
entire period of the competitive advantage. A simplified DCF can be created which projects only
the items in the FCFF formula. However, a more rigorous approach pulls such results from a fully
integrated three-statement model. In forecasting future cash flows, you should be aware of the
sensitivity of cash flow streams over the forecast period. The traditional method of discounting
cash flows assumes that cash flows occur at the end of each annual period. It may sometimes be
more accurate to forecast cash flows on the assumption that they fall evenly throughout the years.
5.5 Terminal Value
Terminal Value estimates the perpetuity growth rate and exit multiples of the business at the end
of the forecast period, assuming a normalized level of cash flows. Since DCF analysis is based on
a limited forecast period, a terminal value must be used to capture the value of the company at the
end of the period. The terminal value is added to the cash flow of the final year of the projections
and then discounted to the present day along with all other cash flows.
Terminal values can be calculated based on two methodologies:
Perpetuity value
Exit multiple.
Financial Modeling using Excel
P a g e | 158
Figure 5.2: Series of cash flows with terminal Cash Flows
Terminal Value Calculations-Perpetuity Growth Method
Perpetuity value of normalized terminal cash flow - This approach calculates the value of the
business on the assumption that it will operate into perpetuity. Two perpetuity formulae can be
utilized, both of which should be shown in a DCF analysis. The first method is growing perpetuity,
which is a preferred method. A growing perpetuity assumes that growth of the business will
continue and that the necessary new capital will return more than its cost. Growth requires capital
spending, and thus a growing perpetuity begins with free cash flow rather than EBIT (1 β tax rate).
ππππππππ ππππ’π = (πΉπΆπΉπΉπ + 1
ππ΄πΆπΆ% β ππππ€π‘β%)
n is the final year of the projection period, and g is the nominal growth rate expected into
perpetuity. The nominal growth rate is generally the inflation rate component of the discount plus
an expected real growth (or minus a deflation) in the business. A reasonable range for perpetuity
growth is the nominal GDP growth rate of the country. Under no growth Perpetuity environment,
assumes that a company earns its cost of capital on all new investments into perpetuity. As such,
the level of investment growth is irrelevant because such growth does not affect the value (i.e. the
growth rate is zero and Capital Expenditure is equal to depreciation and amortization). Such a
methodology is appropriate in industries in which competition is expected to eliminate excess
returns, thus driving asset returns to the cost of capital.
ππππππππ ππππ’π = (πΉπΆπΉπΉπ + 1
ππ΄πΆπΆ%)
17 24 24 31 53 89 102 110
425
0
100
200
300
400
500
2008 2009 2010 2011 2012 2013 2014 2015 2016 Annual cashflow Terminal Value
Cash Flows (Rs mn)
Terminal Value
Financial Modeling using Excel
P a g e | 159
5.6 Financial Model-Corporate Valuation
Financial Modeling using Excel
P a g e | 160
Financial Modeling using Excel
P a g e | 161
Financial Modeling using Excel
P a g e | 162
Figure 5.3: Corporate Valuation
Financial Modeling using Excel
P a g e | 163
5.7 Risk Analysis in Corporate Valuation
Free cash flow is a very important concept; it focuses on the amount of cash that owners of a
business can consume without reducing the value of the business. It recognizes that a business
needs to invest in current and long-term assets in order to continue and grow its operations. Thus
free cash flow focuses on the ability of a business to generate cash flows beyond those needed to
invest in such assets as inventories, plant and equipment, advertising, labor, other cost of sales,
research and development, and the like. free cash flow being a primary objective of management
and analytical measurements, net income must too be studied as it can emit signals which cash
flow overlooksβbut its usefulness is more of short-term significance. Valuation Risk is the
financial risk that an asset is over valued and it may not have an appropriate value than expected
and will yield less than when liquidated. Factor contributing to valuation risk is the incomplete
data, market instability, financial modeling uncertainties and poor data analysis. The risk can be
concern for investors, lenders, market regulators, and other people involved in financial markets.
To mitigate this risk it is important to provide transparency and ensure integrity and consistency
of data, models and processes used to process and report calculations within the valuations from
all participants.
DCF Projections-Reality Check
Confront sales growth assumptions with underlying market dynamics
1. Be skeptical of projected sales growth curves that look show dramatic improvements
versus recent actual performance
2. Does the increase in sales reflect a constant market share in an expanding market? If so,
why is the market expanding?
3. Does that assumption agree with industry projections?
4. If it is an expanding market, why will the company be able to maintain a constant market
share? Or does the increase reflect a rising market share in a stagnant market? If yes, why?
Are some firms leaving the industry? Why?
Financial Modeling using Excel
P a g e | 164
Check reasonableness of margins
1. Be clear on the actions or events needed to trigger improvements in margins (or reasons
for decreases in margins)
2. Are the margin levels consistent with the structure of competition in the industry?
3. Any risk of new entrants/substitute products that will drive margins down?
Capital Expenditures
1. Watch out for step-up of production capacity required as sales increase.
2. Is the Capex level sufficient to support the forecasted increase in sales?
3. Factor in the impact of industry trends on Capex (e.g., increased environmental
expenditures, technology changes, etc.)
5.8 Excel in Portfolio Management
Portfolio Management is all about managing different avenues of investment together so as to
maximize the return/risk ratio. It makes sense when ever portfolio is created the different avenues
should be negatively correlated. Degree of Correlation between two variable is measured by
coefficient of correlation. It have value from -1 to +1. For example coefficient of correlation
between two sector like Oil & Gas and FMCG can be determined by considering two stocks like
ONGC and ITC. 3 month historical data of ONGC and ITC is considered.
Financial Modeling using Excel
P a g e | 165
Figure 5.4: Coefficient of Correlation calculation in Excel
Figure 5.5: Coefficient of Determination Calculation
Financial Modeling using Excel
P a g e | 166
Now the nature of relationship of stocks-ITC and ONGC with respect to NIFTY is measured by
Beta which is the factor of Systematic Risk. Beta measures the degree of sensitiveness of relative
change in the price of stock with respect with market. Beta in MS Excel is calculated by slope
function.
Financial Modeling using Excel
P a g e | 167
Figure 5.6: Beta calculation in MS Excel
Financial Modeling using Excel
P a g e | 168
Figure 5.7: Security Market Line
5.9 Technical Analysis of Stocks
Consider a stock called DLF. Historical Price of DLF is taken from 07th May 2014 to 10th July
2014. For the given time period we can determine the Beta of the stock and can represent them in
the form of Candle Stick Charts. For reference on Candle Stick Model, refer www.
Stockcharts.com.
Financial Modeling using Excel
P a g e | 169
Figure 5.7: Historical price movement of DLF
The Candle Stick Representation will be done in MS Excel under Other Charts representation.
Financial Modeling using Excel
P a g e | 170
Figure 5.8: Candle Stick Movement of DLF
5.10 Portfolio Optimization Using MS Excel-Solver Function
Consider I want to create portfolio of 6 stocks. I chose each stock from one sector and the overall
calculation works on assumption of historical data of last 3 month. Suppose I chose the Sector IT,
Banking, Auto, Pharma, FMCG, Manufacturing and the stocks are TCS, SBI, Bajaj Auto, ITC,
Ranbaxy and RIL. Historical data of all companies are taken from www.nseindia.com.
Stocks Sector Return Standard Deviation
TCS IT 0.20163 1.670785
ITC FMCG 0.049662 1.47458776
Ranbaxy Pharma 0.30666 1.5720595564
RIL Manufacturing 0.058973 1.83723243
Bajaj Auto Auto 0.178989 1.679179
SBI Banking 0.2788444 2.3753188
Table 5.9: Description about stocks-part of portfolio
Financial Modeling using Excel
P a g e | 171
Figure 5.10: Average Return and Standard Deviation of different stocks
Now I will try to create variance covariance matrix. In order to work on Variance Covariance
matrix first I will determine excess return on each stock, which is equal is X=return-average return.
Then variance covariance matrix will be determined by = (1
π) ππ‘ . π where ππ‘ = π‘ππππ πππ π ππ π.
MMULT function is used to multiply matrices. First, ππ‘ . π will be determined
Figure 5.11: use of mmult to determine variance covariance matrix
Once we get ππ‘. π then I will try to determine the value of (1
π) β (ππ‘. π), here n=57. So, the
required value in MS Excel is determined by first selecting all the required (6x6) matrix, then using
the division formula, put the required equation and then ctrl+ shift+ enter.
Financial Modeling using Excel
P a g e | 172
Figure 5.12: use of mmult to determine variance covariance matrix
Financial Modeling using Excel
P a g e | 173
Figure 5.13: Working on variance covariance matrix
Financial Modeling using Excel
P a g e | 174
Figure 5.14: Variance covariance matrix
Now I will try to determine the coefficient of correlation among to understand the nature of relationship
between each stocks as covariance does not clarify the extent of relationship. Now
πππππππππππ‘ ππ ππππππππ‘πππ(π₯π¦) = (πππ£πππππππ(π₯π¦)
π π‘ππππππ πππ£πππ‘ππππ₯.π π‘ππππππ πππ£πππ‘ππππ¦)So, first I will
determine π. ππ‘. β=standard deviation.
Financial Modeling using Excel
P a g e | 175
Figure 5.15: Calculation of Standard Deviation Matrix
Figure 5.16: Calculation of Standard Deviation Matrix
Financial Modeling using Excel
P a g e | 176
Figure 5.17: Coefficient of Correlation matrix
By looking at the coefficient of correlation matrix, I can figure out the relationship between the
variables. Like TCS and RIL are negatively correlated so if I am thinking of 2 stock portfolio I
should include them together, like this I can find out other relationships.
Let talks about Portfolio Creation from six stocks under different cases and then I try to measure
the Return and Risk on the portfolio. Return on the portfolio is the βπ π. ππ, where Wi=individual
weight and Ri=individual return or it can be determined by formula =
π‘ππππ πππ π ππ π€πππβπ‘ πππ‘πππ₯. πππ‘π’ππ πππ‘πππ₯
In a portfolio of equally weighted I took equal weight for each stock=(1/6)=16.66666%.. In MS
Excel return on a portfolio can be determined by using SUMPRODUCT function or MMULT
function.
Financial Modeling using Excel
P a g e | 177
Figure 5.18: Calculation of Return matrix
Standard Deviation of the portfolio will be determined by using the formula=
Financial Modeling using Excel
P a g e | 178
Figure 5.19: Calculation of standard deviation of portfolio matrix
Now by using solver I will try to find the what will be the weight of portfolio that will bring the maximum
return for the portfolio at the minimum risk where I put constraint
1. the standard deviation of portfolio should be less than or equal to the standard deviation of ITC
stock having minimal standard deviation among all stocks.
2. Total weight on the portfolio=1
Financial Modeling using Excel
P a g e | 179
Figure 5.20: Working on calculation of Sharpe Ratio
Financial Modeling using Excel
P a g e | 180
Figure 5.20: Working on calculation of Sharpe Ratio with Solver
Financial Modeling using Excel
P a g e | 181
Figure 5.21: Solver to determine optimal portfolio
Financial Modeling using Excel
P a g e | 182
Figure 5.22: Optimal portfolio
5.11 Summary
1. Business valuation is a series of procedures used to anticipate the economic value of an
ownerβs interest in a business. Valuation is used by Insiders of a firm like CEO, CFO,
Operating Manager to identify opportunities and it help them in decision making. It is used
by Investors like Hedge Funds/Pension Funds, Insurance Company, Retail Investor before
investment or to find out appropriate investment opportunity, It is used by Consultants like
Investment Bank, Credit Rating Agencies, Brokerage House to suggest/recommending
their investors the ideal source/avenue for investment. Valuation help firm during Merger
Financial Modeling using Excel
P a g e | 183
and Acquisition, work on defensive strategies against hostile takeover, assisting during
listing, subsequent offering and de-listing etc.
2. Approaches of Business Valuation:-
a. Macro Analysis-
Monetary Policy- Process by which government, Central Bank, Monetary Authority of
country controls the supply of money, availability of money
Fiscal Policy- Government policy that attempt to influence the direction of economy
through the changes in Government taxes, Fiscal allowances
Stages of Business Cycle- Recovery, Early Expansion, Late Expansion, Early Recession,
late Recession
b. Strategic Analysis
Industry Life Cycle-Pioneering, Rapid Growth, Mature Growth, Stabilization and Market
Maturity, Deceleration of growth and decline
Porterβs Five Forces- Rivalry among the existing competitors, Threat of new entrant,
Threat of Substitute products, Bargaining Power of Buyers, Bargaining power of Supplier
c. Retrospective/ Prospective Analysis
Financial Statement Analysis of Company
Understanding the accounting policies-conservative/aggressive
Ratio Analysis of the Company-Liquidity, Operating, Risk and Growth
Ratio Comparison with other companies in the industry/sector
3. Value of firm is determined by four factors-it capacity to generate cash flows from assets
in place, the expected growth rate of these cash flows, the length of time it will take the
firm to reach stable growth, and the cost of Capital
Value of Asset==βππ ππ πΉπππ πΆππ β πΉπππ€π
(1+ππ΄πΆπΆ)π
Financial Modeling using Excel
P a g e | 184
Free Cash Flow to the Firm=EBIT(1-tax%)+Depreciation-Capital Expenditure-β in
Noncash Working Capital.
4. The free cash flows from a business can be projected using information about the industry
in which a business operates and information specific to the business. A variety of sources
can be used, such as research reports, S&P industry surveys, industry journals and manuals,
and other miscellaneous sources.
5. A Companyβs FCFF represents the return available to both sources of its capital; debt
lenders and equity investors. Free cash flows there reflects the cash generated by the assets
of a business, irrespective of how such assets are financed.
6. While working on valuation-a two step or three step life cycle model can be anticipated
with an appropriate discount rate. Weightage Average cost of capital is use as a discount
rate where proper efforts has been put on determination of cost of equity and cost of debt
7. Project Cash Flows depends on:-
Industry cycle and competitive structure (operating margins)
Economic cycle
Known significant events
Useful life of asset (e.g. oil well, mine)
Comfort of forecaster
Length of any competitive advantage
Allow enough time to reach a normalised or mature level of cash flows which assumes
constant growth and/or capital needs into perpetuity
While projections become less reliable the further out they go, it may be necessary to go
out up to 10 years or more in order to reach a normalized level of free cash flow.
8. Discounted Cash Flow analysis show us the expected value of business by the reference to
future cash flows. Discounted cash flow analysis involves estimating the present value of
future cash flows that business being valued is expected to generate
9. Rather than discounting the cash flows indefinitely into the future, a terminal value based
on company long term growth rate (perpetual growth rate methodology) or a multiple of
final year earnings or cash flows ( exit multiple methodology), is usually assumed after a
period of say five to ten years.
Financial Modeling using Excel
P a g e | 185
10.
11. Perpetuity value of normalized terminal cash flow β
ππππππππ ππππ’π = (πΉπΆπΉπΉπ + 1
ππ΄πΆπΆ% β ππππ€π‘β%)
n is the final year of the projection period, and g is the nominal growth rate expected into
perpetuity. The nominal growth rate is generally the inflation rate component of the
discount plus an expected real growth (or minus a deflation) in the business.
12. Under no growth Perpetuity environment, assumes that a company earns its cost of capital
on all new investments into perpetuity. As such, the level of investment growth is irrelevant
because such growth does not affect the value (i.e. the growth rate is zero and Capital
Expenditure is equal to depreciation and amortization).
ππππππππ ππππ’π = (πΉπΆπΉπΉπ+1
ππ΄πΆπΆ%)
13. Free cash flow is a very important concept; it focuses on the amount of cash that owners
of a business can consume without reducing the value of the business.
14. Free cash flow focuses on the ability of a business to generate cash flows beyond those
needed to invest in such assets as inventories, plant and equipment, advertising, labor, other
cost of sales, research and development
15. Valuation Risk is the financial risk that an asset is over valued and it may not have an
appropriate value than expected and will yield less than when liquidated.
16. Factor contributing to valuation risk is the incomplete data, market instability, financial
modeling uncertainties and poor data analysis.
17. Portfolio Management is all about managing different avenues of investment together so
as to maximize the return/risk ratio. It makes sense when ever portfolio is created the
different avenues should be negatively correlated. Coefficient of Correlation between two
17 24 24 31 53 89 102 110
425
0
100
200
300
400
500
2008 2009 2010 2011 2012 2013 2014 2015 2016 Annual cashflow Terminal Value
Cash Flows (Rs mn)
Terminal Value
Financial Modeling using Excel
P a g e | 186
variables-x and sy can be determined by correl function in MS Excel. Regression
Coefficient between two variables-x and y assuming x=independent variable and
y=dependent variable is determined by slope function. Beta is the factor which measures
the degree of sensitiveness of relative movement of a stock with respect to relative
movement in market is in MS Excel is determined by slope function
18. Technical Analysis on the stock can be done in MS Excel by using stock charts where line
graph, candle stick representation can be plotted with volume and without volume
19. Portfolio Optimization using Solver
Solver is the MS Excel based application used to maximize or minimize or finding an
optimal value of a given set of conditions. It is used in Operation Research to work on
Linear Programming Model
Maximumizition of Sharpe Ratio would be the objective under Solver
Variance-Covariance Matrix, Coefficient of Correlation Matrix can be determined through
mmult function in MS Excel
Portfolio standard deviation is determined by using formula =
π πππ‘(ππ‘. β(π£πππππππ β πππ£πππππππ). π)
Return on a portfolio can be determined by using formula
= π‘ππππ πππ π ππ π€πππβπ‘ πππ‘πππ₯. πππ‘π’ππ πππ‘πππ₯
Sharpe Ratio=(πΈπ₯ππππ‘ππ π ππ‘π’ππ ππππππ’π)/(ππ‘ππππππ π·ππ£πππ‘πππ)
5.12 Questions to Discuss
Two Marks Questions
8. What is Business Valuation?
9. What is the need of Business Valuation?
10. What are the approaches of Business Valuation?
11. What is the difference between Free Cash Flow to Equity (FCFE) and Free Cash Flow to
Firm(FCFF)?
12. Explain Risk Analysis in Corporate Valuation?
Financial Modeling using Excel
P a g e | 187
Five Marks Questions
8. How the terminal value of a firm can be determined?
9. How the beta of a stock can be calculated in MS Excel?
10. How representation of Candle Stick Model can be done in MS Excel?
11. How Variance Co-variance matrix can be generate in MS Excel?
12. What is the need of Portfolio Optimization?
13. Explain the application of MMULT function in MS Excel?
Ten Marks Questions
1. Considering certain number of stocks in a portfolio, how could portfolio optimization can
be done through MS Solver?
5.13 Problems to Practice
1. Beta of a stock can be determined by β¦β¦β¦β¦β¦β¦β¦. Formula in MS Excel
a. Correl
b. Covar
c. Slope
d. Std.p
2. Coefficient of Determination which measures the strength of straight line between the
return on stock with respect to return on market lies betweenβ¦β¦β¦β¦β¦β¦β¦.
a. 0 and 1
b. -1 to 1
c. Any positive integer
d. Any natural number
3. Sharpe Ratio is β¦β¦β¦β¦β¦β¦β¦β¦β¦.
βa. πΈπ₯πππ π π ππ‘π’ππ
π ππ π πΉπππ π ππ‘π ππ π ππ‘π’ππ
βb. πΈπ₯πππ π π ππ‘π’ππ
π΅ππ‘π ππ π π‘πππ
Financial Modeling using Excel
P a g e | 188
βc. πΈπ₯πππ π π ππ‘π’ππ
π π‘ππππππ πππ£πππ‘πππ ππ π π‘πππ
βd. π π‘ππππππ πππ£πππ‘πππ ππ π π‘πππ
πΈπ₯πππ π π ππ‘π’ππ ππ π π‘πππ
4. Which mathematical formula in MS Excel would help to find the expected return on a
stock where probabilities and probable return under that probability is mentioned
a. Mmult
b. Combin
c. Sumproduct
d. Concatenate
5. In matrix form, the formula of standard deviation isβ¦β¦β¦β¦β¦β¦β¦..
a. β(π€π‘). β((π2) β πππ£πππππππ). π€π‘
b. β(π€π‘). β((π2) β πππ£πππππππ). π€
c. β(π€π‘). β((π2) β πππ£πππππππ).
d. None of these
6. What is the relationship between Covariance xy with Coefficient of correlation xy?
a. (πππ£πππππππ(π₯π¦)
π π‘ππππππ πππ£πππ‘ππππ₯.π π‘ππππππ πππ£πππ‘ππππ¦)
b. πππ£πππππππ(π₯π¦). ππ₯. ππ¦
c. ππ₯.ππ¦
πππ£πππππππ(π₯π¦)
d. None of these
7. For matrix multiplication, which Excel formula is useds
a. Mmult
Financial Modeling using Excel
P a g e | 189
b. Combin
c. Sumproduct
d. Concatenate
8. In order to maximize the parameters under Sharpe Ratio, which MS Excel function can
be used
a. Goal Seek
b. Data Analysis
c. Data Table
d. Solver
9. Return on day to day basis of a stock can be determined by using formula
a. (π‘ππππ¦ ππππ πππ πππππβπ¦ππ π‘πππππ¦ ππππ πππ πππππ
π¦ππ π‘πππππ¦ ππππ πππ πππππ) Γ 100
b. ππππ (π‘ππππ¦ ππππ πππ πππππ
π¦ππ π‘πππππ¦ ππππ πππ πππππ) Γ 100
c. Both of these
d. None of these
10. What is the short cut for transposing array of data in MS Excel?
a. Ctrl+c, Alt+e+s+v+e
b. Ctrl+c, Alt+e+s+e
c. Both of these
d. None of these
Answers
1-c, 2-a, 3-c, 4-c, 5-b, 6-a, 7-a, 8-d, 9-a, 10-c
a. References:
1. Pandey I. M, (2003) Financial Management, Vikas Publishing House β New Delhi
2. Aswath Damodaran (2008)-Damodaran on Valuation, Wiley Finance-USA
3. Michael C Ehrhardt, Eugene Brigham (2008) Corporate Finance: A Focuseed Approach,
South-Western Publications-USA
Financial Modeling using Excel
P a g e | 190
4. Prasanna Chandra, (2008) Financial Management and Theory and Practice-Mc Graw Hill
Publications-New Delhi
Websites:
1. Analyze and Optimize Portfolio of Assets- http://in.mathworks.com/discovery/portfolio-
optimization.html?nocookie=true
2. Portfolio Optimization Problems-By Burns Statistics-
http://in.mathworks.com/discovery/portfolio-optimization.html?nocookie=true
3. Private Company Valuation- By Aswath Damodaran-
www.stern.nyu.edu/~adamodar/pdfiles/eqnotes/pvt.pdf
Video Links:
1. Portfolio Optimization in Excel-By Colby Wright-
http://www.youtube.com/watch?v=FZyAXP4syD8
2. The Minimum Variance Portfolio and the Efficient Portfolio-
http://www.youtube.com/watch?v=S5VYM0lMb2Y
3. Valuation by Aswath Damodaran-
http://www.youtube.com/watch?v=K_36oYXYESU
Financial Modeling using Excel
P a g e | 191
Financial Modeling using Excel
P a g e | 192