vinothsv.files.wordpress.com€¦ · web view · 2018-03-19calculate change in net working...
Post on 23-Apr-2018
217 Views
Preview:
TRANSCRIPT
1
Unit I – Introduction to Financial Modelling
1.1 Financial Modelling - Definition
Financial modeling is the task of building an abstract representation (a model) of a real world financial situation. This is a mathematical model designed to represent (a simplified version of) the performance of a financial asset or portfolio of a business, project, or any other investment. Financial modeling is a general term that means different things to different users; the reference usually relates either to accounting and corporate finance applications, or to quantitative finance applications.
1.2 Applications of Financial Modeling
The key Applications of Financial Modeling are Business valuation Scenario planning Capital budgeting Financial statement analysis Project finance Risk Management Portfolio Evaluation Data Representation
1.3 Features of a Financial Model
Unlike simple spreadsheets, a financial model is much more1. It is structured–with a logical flow of assumptions, inputs, calculations and outputs2. It is Dynamic, thus allows the user to change inputs and observe what happens to outputs. This allows for sensitivity analysis.3. It is more Readable as compared to a general spreadsheet 4. It allows for Error Handling5. It should have an objective
1.4 Importance in Financial Model
Remember, in today’s world, collaboration is very important. Multiple people work on the same project. Therefore, it is important for Financial Models to be1. Accurate and Reliable2. Flexible3. User Friendly
Advanced Financial Modelling
2
1.5 Major Do’s and Don’ts in Financial Modelling
For a financial model, some major things to be taken care of are
All items should be in the same font – unless something is a header that needs to attract attention
Decimal points should be same across the entire model Units should be displayed prominently – INR, Million, Nos., % Model objectives should be clearly defined Model inputs, assumptions should be clearly defined Model flow should be clearly defined.
1.6 Defining Model Objectives
It is important to define model objectives first, before embarking upon creating a financial model. Why are we building this model?
Is it for comparing financials? Is it to look at asset allocation? Is it to do risk analysis? Is it to find out some repayment schedule?
1.7 Process Flow for a Model
Assume we are building a valuation model for a company that runs cinema screens, like PVR or Inox. Let us lay down the objectives, inputs, model flow and outputs expected.
Objective – Valuation of Equity (Method: Discounted Cash Flow/Relative Valuation)
InputsCash Flow P & L A/c, Balance
Sheet Revenues, Costs and others
Cost of Capital Rf, Beta, Rm-Rf, Wd, We, Cd and Tax rate
Revenue Estimation – Ticket – Ave.Ticket Price x Seats x Occupancy x Screens x Shows x 365 +
Food & Beverage – People Visiting x % of Eating x Ave. Spend +
Advt – Advt/ Scree + Parking etc.
Process
Revenues Assumptions: P&L, BS, CF, Debt Schedule, Capex Schedule, DCF
Less: Costs Profits -- Cash Flow -- Discount
Advanced Financial Modelling
3
Output: Result
1.8 Identifying Inputs and Variables
We need to define the key values we will need in any model to be able to arrive at the final conclusion. We have already seen examples of this. For example, while doing valuation, we need to identify what are the key inputs and assumptions in our model. How are we projecting those? We also need to mention these, or show them in a different color, so as to make sure that everyone understands what an assumption is, and what data that is given is.
Defining DeliverablesThe Model Output has to be defined, so that we are clear about what is it that we can expect, and where can we find it. May be it could also be shown in a different color, and the model introduction should mention what we are trying to find?
Stress TestingWhen a model has many inputs, they may start affecting the overall flow. It is therefore important to see if changes to one input are not affecting any other functionality anywhere. A model has to be stress tested to see if all linkages are working correctly, or not.
1.9 DocumentationA financial Model is in complete without some documentation about how the model flow moves. Documentation can be done in 3ways1. Using Name Manager – Define Names for the Inputs such that understanding the model becomes easier.2. Using In Cell Comments –Defining comments for any assumption, so that a user can understand why those assumptions have been made.3. Using separate columns for comments, or using an entire sheet for documenting the key assumptions and Model Flow.A combination of the above can also be used.
********
Advanced Financial Modelling
4
Unit II – Portfolio and Options Modelling
2.1 Variance Covariance
Calculating Daily returns for DLF, Eicher & Hindalco:a. In Cell E8 – use the formula = (Today Value – Yesterday Value) / Yesterday Value for
calculating Daily returns – Convert into % and drag to right and down for the entire period
Calculating Daily & Annual Average returns and Standard Deviationsa. In Cell E502, calculate Daily average returns =AVERAGE(E8:E500) and drag it up to
H502b. In Cell E503, calculate Annual returns =(1+E502)^250-1 and drag it up to H503c. In Cell E504, calculate Daily Standard Deviation =STDEV.P(E8:E500) and drag it up to
H504d. In Cell E505, calculate Annual Standard Deviation =E504*SQRT(250) and drag it up to
H505.e. In Cell E506, calculate Daily Variance =E504*E504 and drag it up to H506
Advanced Financial Modelling
5
Calculating Variance and SD for Two Asset Portfolio
If there is a portfolio with 2 assets, we already know the returns and standard deviation of this portfolio. Returns are weighted average, while portfolio variance is given by the following formulaPortfolio Variance = w2A*σ2(RA) + w2B*σ2(RB) + 2*(wA)*(wB)*Cov(RA, RB)
a. Take 2 asset out of the portfolio DLF and Eicher and assume you’re investing 40% with DLF and 60% with Eicher.
b. In CellE510 calculate Portfolio average returns =B508*E503+B509*F503c. Calculate Covariance in Cell E513, F513, E514 and F514.
i. For E513 =COVAR(E8:E500,E8:E500)ii. For F513 =COVAR(E8:E500,F8:F500)
iii. For E514 =COVAR(F8:F500,E8:E500)iv. For F514 =COVAR(F8:F500,F8:F500)
d. Calculate Variance in Cell E517, E517, E518, F518.i. For E517 =E511*C513*E513
ii. For F517 =F511*C513*F513iii. For E518 =C514*E511*E514iv. For F518 =F511*C514*F514
e. Calculate Variance in Cell B517 =SUM(E517:F518) and SD in cell B518 =SQRT(B517)
Portfolio Variance for Three Asset Portfolio
Returns are still weighted average, while portfolio variance is given by the following formula
Portfolio Variance = w2A*σ2(RA) + w2B*σ2(RB) + w2C*σ2(RC) + 2*(wA)*(wB)*Cov(RA, RB) + 2*(wA)*(wC)*Cov(RA, RC) + 2*(wB)*(wC)*Cov(RB, RC)
Assume a portfolio with 3 assets. The weights are 20% for DLF, 35% for Eicher and 45% for Hindalco respectively. Below are the details of Standard Deviations and Correlations.
Advanced Financial Modelling
6
a. Calculate Covariance between DLF,Eicher and Hindalco i. For Cell E529 =COVARIANCE.P(E8:E500,E8:E500)
ii. For Cell F529 =COVARIANCE.P(E8:E500,F8:F500)iii. For Cell G529 =COVARIANCE.P(E8:E500,G8:G500)iv. For Cell E530 =COVARIANCE.P(F8:F500,E8:E500)v. For Cell F530 =COVARIANCE.P(F8:F500,F8:F500)
vi. For Cell G530 =COVARIANCE.P(F8:F500,G8:G500)vii. For Cell E531 =COVARIANCE.P(G8:G500,E8:E500)
viii. For Cell F531 =COVARIANCE.P(G8:G500,F8:F500)ix. For Cell G531 =COVARIANCE.P(G8:G500,G8:G500)
b. Calculate Variance between DLF, Eicher and Hindalcoi. For Cell E534 =$C529*E$527*E529
ii. For Cell F534 =$C529*F$527*F529iii. For Cell G534 =$C529*G$527*G529iv. For Cell E535 =$C530*E$527*E530v. For Cell F535 =$C530*F$527*F530
vi. For Cell G535 =$C530*G$527*G530vii. For Cell E536 =$C531*E$527*E531
viii. For Cell F536 =$C531*F$527*F531ix. For Cell G536 =$C531*G$527*G531
c. Calculate Portfolio Variance in Cell B534 =SUM(E534:G536)d. Calculate Portfolio SD in Cell B535 =SQRT(B534)
Using Solvera. Go to File – Options – Add Ins and Click Go to insert Solver in to your data tab.b. Go to Data Tab – Select Solver – Set objective with Cell K532 (Average of Returns) then
click Minc. Select changing variables as Weights Cells J528:J530d. Add Constraints – J528, J529 and J530 are >= 5%
Advanced Financial Modelling
7
e. Add Constraint – J531 = 100%f. Add Constraint with K532 is >=30% and K536 is <=10% then Click Solve.g. Let you can able to see the changes in the weights as output.
2.2 Portfolio
Normal DistributionIn Finance, returns are expected to be distributed normally. Let us understand the normal distribution first.
Advanced Financial Modelling
8
Assume a stock with expected return 15%, and standard deviation of 20%. How would the normal curve look like?
a. Type Mean value and SD value in Cell B4 and B5b. Calculate Normal Distribution in Cells B10: B13 for Probability of returns less than 15%,
-5%.-25% and 35%; =NORM.DIST(A10,$B$4,$B$5,1)c. Calculate Normal inverse for Probability of returns less than 15%, -5%.-25% and 35%;
=NORM.INV(B10,$B$4,$B$5)d. Present inverse values in G4:G7 for presentation.
2.3 Simulation – Monto Carlo Simulation
Sometimes, we may need to run a simulation to find out if the results that are expected are feasible or not.Take for example an investment product, that says that if you invest Rs100,000 today in an asset with mean of 20% and SD of 18%, you will be given 10lakhs, if the total investment value crosses 8 lakhs at the end of 10years, else you will be given Rs3 lakhs.
a. Type mean value and SD value in Cell B4 and B5b. Type year 1 to 10 from E5:E14 and enter investment value as Rs.1,00,000 in Cell F4c. Calculate Return % in Cell G5; =NORM.INV(RAND(),$B$4,$B$5)d. Calculate returns in F5; =F4*(1+G5) and drag return and rate up to 14th row.e. In cell B11 link the 10th return value = 509,466f. For simulation and 1000 iteration enter number 1 to 1000 from cell A12 to A1011g. To get simulated return, Select A11:B1011 go to Data tab -> Select What-if-Analysis –
Select – Data Table, keep empty the row input cell and column input select cell C11. You will get the data filled up to 1000 iterations
h. Calculate Average Ending Value in Cell B7 =AVERAGE(B12:B1011)i. Calculate Annualized return in Cell B8 by using CAGR formula =(B7/F4)^(1/10)-1
Advanced Financial Modelling
9
2.4 Option Payoff
Options – MeaningOptions are a kind of derivative instruments, where the holder of the option has a right but no obligation to buy or sell an asset. Unlike futures – where the buyer and the seller of the future enter into a contract for executing a transaction on a set date at a set price – the option, as the name signifies, is an option for the holder to decide whether or not he wants to execute the trade.
Option Terminologies: Strike Price/Exercise Price – It is the price at which the option can get exercised, Spot Price – Underlying or current market price Premium – It is the price we pay for the option Option Writer – The entity who sells the option. This entity is said to short the option Option Holder - The entity who buys the option. This entity is said to be Long the option
Type of Options:
Call Option – It is an option that gives the buyer of the option a right to buy the underlying asset at a predetermined price (Strike Price).
Put Option – It is an option that gives the buyer of the option a right to sell the underlying asset at a predetermined price (Strike Price).
Option PayoffsConsider a call option on a stock with an expiry this month. Its details are as mentioned below.Strike Price – Rs.2500; Step – 50, Premium – 25Let us create the payoff matrix for this option
a. Type option type as Call in Cell B4 and Strike Price – Rs.2500; Step – 50, Premium – 25 in cell B5, B6 and B7
b. Create a Payoff Schedule with Underlying Price, Profit/Loss, Premium paid, Total Buyer Payoff, Total Seller Payoff from A9 to E9
c. Enter 2500 in Cell A15 and reduce the value by 50 to the above 5 cells and increase the value of 50 by below 5 cells.
Advanced Financial Modelling
10
d. Enter Premium paid as -25 commonly from Cell C10 to C20e. Use nested IF formula for calculating Profit/Loss =IF($B$4="Call",IF(A10>$B$5,A10-
$B$5,0),IF(A10>$B$5,0,$B$5-A10)) f. Calculate Total Buyer Payoff in Cell D10 =B10+C10 drag up to D20g. Calculate Total Seller Payoff in Cell E10 =-D10 drag up to E20h. Select Underlying Price A10:A20, Total Buyer Payoff D10:D20 and Total Seller Payoff
E10:E20 go to Insert Tab and Insert Line Chart for Presentation.
2.5 Cash Future ConvergenceConvergence is the movement of the price of a futures contract towards the spot price of the underlying cash commodity as the delivery date approaches.
a. Take Future Price & Spot Price from NSEINDIA.Com
b. In Cell B7 type expiry date and in Cell B8 type date from NSE Sitec. Calculate days to expiry in Cell B3 =B7-B8
Advanced Financial Modelling
11
d. Calculate daily implied interest rate in B6 =((B4/B5)^(1/B3)-1) and annual rate in C6 =B6*365
e. Assume Annual Mean is 15% and SD is 18% in Cell F4 & F5f. Calculate Daily Mean in Cell E4 =(1+F4)^(1/250)-1 and In Cell E5 calculate daily SD
=F5/SQRT(250)g. The headers in 10th row Days to Expiry, Future Price, Spot Price, Spot Return h. In 11th row link Days to expiry from B3, Future Price from B4 and Spot Price from B5i. Calculate Spot return in Cell D12 =NORM.INV(RAND(),$E$4,$E$5)j. Calculate Spot Price in Cell C12 =C11*(1+D12)k. Calculate Future Price in Cell B12 =C12*(1+$B$6)^A12l. Select from B12 to D12 drag down to 0 Expiry daysm. Select Days to Expiry, Future Price and Spot price - A10:D45 Go to Insert Tab and Insert
line chart for presentation.
Advanced Financial Modelling
12
Unit III - Financial Modeling for Valuation and FSA
3.1 Forecasting Revenues and Costs
Objective: Forecast revenue and costs for the following data:
2009 2010 2011 2012 2013 2014 2015Year 1 2 3 4 5 6 7Sales 411 488 737 1,174
RM Cost 236 287 459 697 Net Profit 46 86 142 174
Calculate by using any of the following three methods:1. Historical Average2. Trend Analysis - Linear Regression3. Proportion - Cost as % of sales
Method 1: Historical Average
a. Copy the data from sales to Net Profit completely and paste to the CELL down (A12:E14)
b. Go Cell C12 Calculate Growth using the formula = (Current Value – Previous Value)/ Previous Value - > =(C8-B8)/B8. Keep Blank the B12 cell.
c. Copy/Drag the formula for rest of the cells. d. Calculate Average for 3 years growth rate = AVERAGE (C12:E12), Similar for the two
items.e. Go To -> Cell No. F8 and Calculate forecast Value = Previous Cell Value (E8) * 1+
Growth rate (1+F12)) and drag the same for two more years.Follow the same instruction for RM Cost and Profit forecast.
Advanced Financial Modelling
13
Method 2: Trend Analysis - Linear Regression.a. Select the 3 items data for 4 years -> B8: E10 -> Go to Insert -> Line Chart -> Right in
side chart add Trend Line (Linear) and Click Display Equation on the chart - > The Equation appears like this: y = 253.74x + 68.265
b. Go to Cell F8, Calculate forecast value like this (253.74 *5) + 68.265; similarly for other cells by using manual formula.
c. Second method of Forecasting is called as “FORECAST”d. Go To Cell F8 =FORECAST(x, known_y's,known_x's); Use following inputs for getting
forecast values- X = 5 (5th Year), known_y’s (Sales amount from 2009 to 2012 – $B$8:E8),
known_x’s (Year 1 to 4; $B$7:E7). Use same FORECAST function for remaining cells.
Advanced Financial Modelling
14
Method 3: Proportion - Cost as % of sales
a. Go To Cell B12 and Calculate Cost as % of Sales by using the formula = RM Cost (B8) / Sales (B7) and drag the same up to E12.
b. Calculate 4 years values as forecast = Average (B12:E12) and drag the same up to H12.
3.2 Financial Statement Analysis and Projection
Advanced Financial Modelling
15
Revenue Drivers:a. For calculating revenue per car -> Use Assumption sheet -> The Values: Installed
Capacity, Car Sales are already given in the Assumption sheet for the year Financial Year 11 to 15.
b. Link and bring the Revenue from operations Value from Profit and Loss Account -> = ='P&L'!B6 (Gross Revenue from Operations)
c. Calculate Price per Car = Revenue from Operations / Car Sales – Total; =(C14*10^7)/C12. We are using *10^7 for converting value from Million to Crores.
d. Drag both Cell C14 & C15 to G14 & G15.e. Assume the Number of Sales is Same – 1292415 and Price per car is Rs.400,000. f. Calculate Revenue from Operations for FY16 = Rs.400,000 * 1,292,415 ( H15 * H12 /
10^7).
Cost Driversa. Calculate Cost drivers from row 20 onwards.b. Calculating Cost of Material: calculate based on Price per Car -> Go To cell C21 -> Cost
of Material *10 ^7 /Total Car Sales; =(C20*10^7)/C12 drag the same up to G21. Calculate average 3 years as Forecasted Cost of Material per car for FY16; =AVERAGE(E21:G21)
c. Cost of Material for FY16 -> Total Car Sales for FY16 * Average Cost of Material per Car; =H12*H21/10^7
d. Calculating other costs like Purchases of Stock-in-trade, Change in Inventories, Employees Benefits Expenses, other expenses and vehicles/dies for own use are based on % of Sales.
e. Calculation as % of sales – Particular Expenses / Revenue from operations. = =C22/C$14f. Calculation of Expenses is Average for 3 years of % of sales * Revenue from operations.
= H23*$H$14
Working Capital Drivers
Advanced Financial Modelling
16
a. All the working capital items are calculated based on % of Sales.b. Calculation of % of Sales for Working Capital items – Select working capital item value /
Sales present as %
c. Example: Short term Borrowing amount (C40) / Revenue from operations; =C40/C$14. For FY16 take the average of three months (Example for Short Term Borrowings is 1.5%). Do the same for others.
d. Calculating in terms of days’ worth of sales= Percentage of Working Capital item * 365 days. Example for Short term Borrowing =C52*365
Forecasting Working Capital Drivers for FY16a. Working Capital item value for FY16 = % of Sales of particular item * Revenue from
operations.b. Example for Short Term Borrowings FY16 = Revenue from Operations (H14) * % of
Sales of Short Term Borrowing (H52); =H52*H$14.
Advanced Financial Modelling
17
Profit and Loss Account Forecasting/Projectiona. Link revenue from operations value for FY16 from Assumption sheet =Assumptions!H14b. Use previous year(FY15) values of Excise duty, Other operating income and other
income as same for current year(FY16) too.c. For calculating Total Operating Revenue- use the formula Netsales + Other Operating
Revenue; =G8+G9.d. Link the expenses from Assumption Sheet. =Assumptions!H20, =Assumptions!H22,
=Assumptions!H24, =Assumptions!H26, =Assumptions!H28, =Assumptions!H30.e. Calculate sum value as Total Operating Expensesf. For EBIDTA (G24) = Total Operating Revenue (G12) – Total Operating Expenses
(G22).g. EBIDTA Margins = EBITDA / Net Sales. Example for FY11 = =B24/B8. Drag up to
FY16.h. Use Same amount of Depreciation and Finance Cost from FY15 to FY16i. Calculating Tax rate = Tax amount / EBT drag up to FY15. Use FY15 percentage for
FY16. Tax amount for FY16 = Tax rate * EBT for FY16.j. PAT or Net Profit = EBT – Tax
Advanced Financial Modelling
18
CAPEX Schedule:a. Go To Capex Schedule worksheet -> Calculate Gross LT Assets per car Capacity ->
Total Gross Long Term Assets *10^7/Capacity -> =B6*10^7/B10; drag upto FY14.b. Forecasting for FY15 use the same value from FY14.c. Forecasting for FY16 = Link FY15 value of Capacity + 5000 increasing and Gross LT
Assets per capacity become same.d. Calculating Capex = (Capacity FY16 – Capacity FY15) * Gross LT Assets per Car /
10^7; =(G10-F10)*G12/10^7e. Calculating Debt – Assume Debt/Equity ratio is 1:2; = Capex * 1/3; =G14*33%
Balance Sheet Projection
a. Liabilities Projection is the first partb. Share Capital keep same value appearing the FY15c. Reserves & Surplus – FY15 value + PAT or Net Profit of FY16 from P & L
Worksheet; =F9+'P&L'!G37d. Long term Borrowing – FY15 Value + Debt amount from Capex Schedule; =F13+'Capex
Schedule'!G15.
Advanced Financial Modelling
19
e. Remaining Non-current liabilities values take same from FY15 for FY16.
f. All Current Liabilities value link and take from Assumption Sheet. It is already calculated there. =Assumptions!H40, =Assumptions!H41, =Assumptions!H42, =Assumptions!H43
g. Calculate Total Liabilities = Total Shareholder’s Funds + Total Non-Current Liabilities + Total Current Liabilities; =G10+G17+G24
h. Assets classified into two parts Non-Current Assets and Current Assetsi. Total Fixed Assets = FY15 Value – Depreciation from P &L for FY16 + Capex amount
from Capex Schedule for FY16. =F34-'P&L'!G27+'Capex Schedule'!G14.j. Three items Non-Current Investments, Long Term Loans and Advances and Other Non-
Current Assets are same. Link and take it from FY15 to FY16
Advanced Financial Modelling
20
k. Current Assets -> Current Investment Same Value from FY15 to FY16l. Link and take Inventories, Trade Receivables, Short Term Loans and Advances and
Other Current Assets from Assumptions Sheet. = =Assumptions!H45, =Assumptions!H46, =Assumptions!H48, =Assumptions!H49.
m. Link and Take the value of Cash and Bank Balances from Cash Flow Statement. ='Cash Flow'!D14.
n. Total Assets = Total Fixed Assets + Non-Current investment + Long Term Loans and Advances + Other Non-Current Assets + Total Current Assets. =G34+G36+G37+G38+G47
o. Calculate Balance between Total Assets and Total Liabilities = Total Assets (B49) – Total Liabilities (B26).
p. Calculate Change in Net Working Capital for Cash Flow Statement - > Take Non Cash Current Assets (Excluding Current Investments & Cash and Bank Balances) - > =B42+B43+B45+B46. Link and take Current Liabilities from Cell =B24
q. Calculate Net Working Capital = Non Cash Current Assets – Current Liabilities; =B54-B55
r. Calculate Change in Net Working Capital = Current Year Net Working Capital – Previous Year Net Working Capital; =C57-B57 drag up to FY16.
Cash Flow Statement
a. Preparing the Cash Flow Statement, first link the Net Profit from P & L Account; ='P&L'!G37
Advanced Financial Modelling
21
b. Depreciation link and bring from P & L Account; ='P&L'!G27c. Changes in Net Working Capital – link and bring from Balance Sheet Worksheet;
='Balance Sheet'!G58d. Capital Expenditure (CAPEX) – link and bring from Capex Schedule; ='Capex & Debt
Schedule'!G14e. Changes to Debt – link and bring from Capex Schedule - ='Capex & Debt Schedule'!G15f. Calculate Total Cash Flow = Net Profit + Depreciation – Changes in Net Working
Capital – Capital Expenditure + Changes to Debt; =D6+D7-D8-D9+D10g. Link and bring FY15 Cash as opening cash balance for FY16h. Calculate Closing Cash Balance = Total Cash Flow + Opening Cash Balance; =D12+D13i. Link the calculated closing cash balance to Balance Sheet (Cell No. ='Balance Sheet'!
G44) ='Cash Flow'!D14
Ratio Analysis
a. Profitability Ratios calculation: Operating Profit Margin = EBITDA/ Total Operating Revenue; ='P&L'!B24/'P&L'!B12
b. EBIT Margin = EBIT/Total Operating Revenue; ='P&L'!B29/'P&L'!B12c. Net Profit Margin = Net Profit/ Total Operating Revenue; ='P&L'!B37/'P&L'!B12d. Return Rations – Return on Equity = Net profit/Shareholders Funds; ='P&L'!
B37/'Balance Sheet'!B10e. Return on Capital Employed = EBIT / (Shareholder Funds + LT Loans);
='P&L'!B29/('Balance Sheet'!B10+'Balance Sheet'!B13)f. Coverage Ratio - Interest Coverage Ratio = (EBIT / Interest); ='P&L'!B29/'P&L'!B31g. Debt Equity Ratio = Total Debt/Equity; =('Balance Sheet'!B13+'Balance Sheet'!
B20)/'Balance Sheet'!B10h. Solvency/Liquidity Ratios – Current Ratio =Current Assets / Current Liabilities;
='Balance Sheet'!B47/'Balance Sheet'!B24i. Quick Ratio = (Cash + Receivables) / Current Liabilities; =('Balance Sheet'!
B44+'Balance Sheet'!B41+'Balance Sheet'!B43)/'Balance Sheet'!B24
Advanced Financial Modelling
22
j. Turnover Ratios - Inventory Turnover Ratio = Sales / Inventory; ='P&L'!B12/'Balance Sheet'!B42
k. Receivables Turnover Ratio = Sales / Receivables; ='P&L'!B12/'Balance Sheet'!B43l. Total Asset Turnover Ratio = Sales / Total Assets; ='P&L'!B12/'Balance Sheet'!B49m. Fixed Asset Turnover Ratio = Sales / Fixed Assets; ='P&L'!B12/'Balance Sheet'!B34
Advanced Financial Modelling
23
Unit IV - Financial Modeling for Debt and Bonds
4.1 Models for Debt Repayment
Companies that take debt, need to create a schedule to repay it. The repayment has to be modelled in financial models. There can be various ways this debt can be repaid1. The debt could be repaid in equal monthly installments2. The Principal could be repaid in equal amounts over a tenure3. There could be a specific customized schedule for a certain loan4. The loan repayment could be to manage a certain debt service coverage ratio
Method 1: Equal Monthly Installments
a. Based on the following inputs prepare a Debt Repayment Schedule on Equal Installment Method – Principal Rs.150 Crore, Rate – 8%, Period/Tenure – 9 Years.
b. Link and bring beginning principal from Principal – B3 i.e., Rs.150c. Calculate Installment in Cell no B11 by using PMT formula; =PMT(B4,B5,-B3,0) and
link the same amount in to C11 and drag right up to Cell no J11.d. Calculate Interest payment in Cell B10 = Beginning Principal (B8) * Rate (B4);
=$B$4*B8 and drag the same up to cell no J10e. Calculate Principal Repayment in Cell B9 = Installment (B11) – Interest Payment (B10);
=B11-B10 and drag the same up to cell no J9f. Calculate Closing Principal in Cell B12 = Beginning Principal (B8) – Principal
Repayment (B9); =B8-B9 and drag the same up to Cell J12g. Link Closing Balance in Cell B12 with Beginning Principal in Cell C8 and drag the same
up to Cell J8h. Select Principal Repayment and Interest Payment -> Go to -> Insert Tab -> Select 2D
Area Chart for presentation.
Advanced Financial Modelling
24
Method 2: Equal Principal Repayments
a. Based on the following inputs prepare a Debt Repayment Schedule on Equal Principal Repayment Method – Principal Rs.150 Crore, Rate – 8%, Period/Tenure – 9 Years.
b. Link and bring beginning principal from Principal – B3 i.e., Rs.150c. Calculate Equal principal repayment in Cell B8 = 150/9; Principal (B3)/Tenure(B5);
=B3/$B$5. Link the same amount in to C8 and drag the same up to J8.d. Calculate Interest Repayment in cell B9 = Beginning Principal (B7) * Rate (B4). Drag
the formula up to J9e. Calculate Installment in Cell B10 = Principal Repayment + Interest Payment; =B8+B9,
drag the same formula up to J10f. Calculate Closing Principal in Cell B11 = Beginning Principal (B7) – Principal
Repayment (B8); =B7-B8 and drag the same up to Cell J11g. Link Closing Balance in Cell B11 with Beginning Principal in Cell C7 and drag the same
up to Cell J7h. Select Principal Repayment and Interest Payment -> Go to -> Insert Tab -> Select 2D
Area Chart for presentation.
Advanced Financial Modelling
25
Method 3: Custom Repayment Schedule
a. Based on the following inputs prepare a Debt Repayment Schedule on Custom Repayment Schedule Method – Principal Rs.150 Crore, Rate – 8%, Period/Tenure – 9 Years. The bank has asked the repayment for the first 4 years to be 5% each, while the next 5 years to be 16% each.
b. Link and bring beginning principal from Principal – B3 i.e., Rs.150c. Insert new row and name as Custom Payment Schedule. Enter 5% from Cell B11 to E11
and enter 16% from Cell F11 to J11.d. Calculate principal repayment in cell B12 = Beginning Principal (B10) x Custom
payment % (B11). Drag the formula up to cell J12.e. Calculate Interest Repayment in cell B13 = Beginning Principal (B10) * Rate (B5). Drag
the formula up to J13.f. Calculate Installment = Principal Repayment + Interest payment; B12 + B13 and drag the
formula up to J14.g. Calculate Closing Principal in Cell B15 = Beginning Principal (B10) – Principal
Repayment (B12); =B10-B12 and drag the same up to Cell J15h. Link Closing Balance in Cell B15 with Beginning Principal in Cell C10 and drag the
same up to Cell J10.
Advanced Financial Modelling
26
Method 4: Debt Sculpting
Under this method, the company now incorporates how cash flows could define how much money is being repaid. In the earlier methods, whether the company was making money or not, we were creating a repayment schedule. In real life, that may not be practical. So here, we assume that debt payment would depend on how much cash the company has to service this debt. This can be captured by Debt Service Coverage Ratio.
a. Calculate Cash Flow available for Debt Service by using the formula = EBITDA – Tax. Drag the same formula up to cell J12.
b. Link Principal Amount 150 (Cell B4) with B14 in Beginning Principal.c. Calculate Interest Payment in Cell B16 = Beginning Principal (B14)*Rate(B5) drag the
same up to J16d. Calculate Installment in Cell B13 = CFADS (B12) / DSCR (B7) and drag the same up to
J13.e. Calculate Principal Repayment in Cell B15 = Installment – Interest, and drag the same up
to Cell J15f. Calculate Closing Principal in Cell B17 = Beginning Principal (B14) – Principal
Repayment (B15) and drag the same up to Cell J17
Advanced Financial Modelling
27
4.2 Bonds/Fixed Income Securities
Calculating Clean Price and Dirty Price
Advanced Financial Modelling
28
Calculate Full Price and Clean Pricea. Assume a Bond with above information’s and its face value of Rs.100. Calculate full
price and clean priceb. Calculate number of periods to next coupon – Days to next coupon/Days between
coupons; =D11/D12; 104/182 in Cell D13 along with that add 1 in the next cell E13 drag up to G13
c. Fill/Calculate cash flows in Cell D15 – Face Value (Rs.100)*Coupon (10%)/2 – because of semiannual bond drag up to F15 and fill CF + Face Value i.e., 100+5 = 105 in cell G15
d. Calculate Discounted CF in D16 – CF (D15)/(1+Semiannual Yield(D10-4%)^D13 drag the same formula up to G16
e. Calculate Full Price in Cell D18 – sum of Discounted CF(D16:G16)f. Calculate Accrued Interest in Cell D19 – (Days in Since last coupon(C11 – 78)/Days
between Coupon(D12-182))*CF(D15-5)g. Calculate Clean Price in D20 = Full Price(D18)-Accrued Interest(D19)
Calculating Clean Price using Excel Function – Pricea. Type Settlement date as today date in Cell H7, calculate next coupon date - =H7+104,
calculate maturity date - =H9+183+365.b. Calculate Clean Price using Price function = Price(Settlementdate,
Maturitydate,Rate,Yield,Redemption,Frequency,Basis); =PRICE(H7,H8,D8,D9,100,2,1).
Advanced Financial Modelling
29
4.3 Calculation of Duration
Duration is the measure of the price sensitivity to the changes in yield for a bond
Duration of a bond gives us a measure of how much the bond will move for a 100 basis point (1%) movement in yields. An approximate measure can be found by just changing the YTM of a bond slightly and seeing how much difference does the change make to the price. Let us look at an example.Assume a bond with coupon rate of 8%, tenure of 5 years, face value of Rs100 and YTM (expected return) of 7.2%. The current price can be calculated using the NPV formula, or manually on excel.
Advanced Financial Modelling
30
Coupon 8% Years 1 2 3 4 5
Cash Flows
8.0
8.0
8.0
8.0
108.0
Price 103
.26 Discount Rate 7.2%
DCF
7.46
6.96
6.49
6.06 7
6.29
a. Calculate DCF – CF/(1+7.2%)^t; In Cell D8 =D6/((1+$D$7)^D5) drag up to H8b. Calculate Price in B7 = sum of DCF (D8:H8)c. Vo in Cell C13 type 103.26, if Discount rate is 7.1% type against Vdecline i.e. Cell C14 –
103.68, if Discount rate is 7.3% type against Vincrease i.e. in cell C15 - 102.85.d. When Yield decline/Increase by 10 basis points the duration become 4.
Calculating Duration by using MDuration and Duration Functions:
a. Type today date in Cell C3, from Cell D3 = C3 +365 drag up to H3.b. Calculate MDuration in Cell I11 =
MDURATION(Settlementdate,Maturitydate,Coupon,yield,Frequency,Basis); =MDURATION(C3,H3,B5,D7,1,1).
c. Calculate Duration = MDuration value * (1+Discount Rate); =I11*(1+D7)d. Calculate Duration using function cell – I12 =
DURATION(Settlementdate,Maturitydate,Coupon,yield,Frequency,Basis); =DURATION(C3,H3,B5,D7,1,1).
4.4 Calculating Yield and Coupon Functions
a. Type today in settlement dateb. Use COUPDAYS function to find the days in period in Cell B13 =
COUPDAYS(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)
Advanced Financial Modelling
31
c. To find Days in Coupon Period Over use COUPDAYBS function in Cell B14 = COUPDAYBS(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)
d. Calculate Accrued Interest in Cell B15 = =B14/B13*(B7/B9*B6)e. Calculate Clean Price in Cell B11 = Current Market Price – Accrued Interestf. Calculate Yield in Cell B10 = =YIELD (B4,B8,B7,B11/10,B6/10,B9,1) – Note Excel
take Face Value only Rs.100. So, Convert that by divide 10)g. To find Days left in the Coupon Period use COUPDAYSNC function in Cell B17 = =
COUPDASNC(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)h. To find Next Coupon Date use COUPNCD function in Cell B18 = =
COUPNCD(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)i. To find Number of Coupons left use COUPNUM function in Cell B19 = COUPNUM
(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)j. To find Previous Coupon Date use COUPPCD function in Cell B20 = COUPPCD
(Settlement, Maturity, Frequency, Basis); =COUPDAYS(B4,B8,B9,1)
Advanced Financial Modelling
32
Unit V – Case Studies
5.1 Case 1 – Investment Benefit Modelling
You are a financial advisor, who wants to explain to a client that early investing has its benefits.
Your client wants to have Rs 5 crore by the time he retires, that is at the age of 60. He is
currently 25 years old, but plans to save money only after he has a salary of greater than 15
lakhs. His current salary is Rs 2.5 lakhs per annum. Assume rate of return to be 12%, and show
the sensitivity of the returns to the entire problem as well.
You want to showcase that early investing can give him huge benefits, in terms of lower amount
to be invested every year if started early.
Steps:
a. Type 15 in Cell B4 assume the age of the investor is 45 so, he have another 15 years of
service to get retirement
b. Enter Retirement Corpus required by the investor as Rs.5 Crore in B5
c. Enter Expected return as 12% in B6
d. Calculate Annual investment needed by using PMT function in Cell B8 =PMT(B6,B4,0,-
B5)
e. Calculate Monthly investment needed by using PMT function in Cell B9
=PMT(B6/12,B4*12,0,-B5)
f. Link monthly investment needed i.e. B9 with E8 and type horizontally the return
percentages like 10%,11%, 12%, 13%, 14% and 15%
g. List the age 50 to 25 between the cell D9:D14 and period gap for retirement from Cell
E9:E14
h. Select the table E8:K14 go to Data Tab – What-if-Analysis –Data table: Row input is
return rate – B6 and Column input is time frame – B4 then click ok
Advanced Financial Modelling
33
5.2 Case 2 – Income Tax Calculator
You want to create a short tax calculator for Salaried Individuals. You are broadly given the
following data, and will ask the user to input some major parameters about the salary and
investments. Based on this, the taxable income needs to be calculated
Tax Slabs
0 to 250,000 = 0%
250,000 to 500,000 = 5%
500,000 to 10,00,000 = 20%
Above 10,00,000 = 30%
Home Loan Interest Benefit = up to 2 lakhs
Investments in ELSS, Insurance Premium, PPF (80C) = up to 1.5 lakhs
Steps:
a. Enter input details Salary (B12), ELSS(B14), Insurance Premium(B15), PPF(B16)
b. Calculate Total 80C in Cell B17 =SUM(B14:B16)
c. Enter Home Loan taken – Yes/No by using Drop down menu – Data Validation – List –
Select H19&H20 Click ok
d. Enter Interest Rate (B20), Tenure(B21) and Home Loan Amount(B22)
e. Create EMI Schedule A33 to D45 with following headers month, emi, interest, principal
f. Calculate EMI in Cell B34 =PMT(B20/12,B21*12,-B22,0) link with B35 and drag up to
B45; Calculate Interest in Cell C34 – EMI – Principal link the formula in C35 drag up to
C45; Calculate Principal by using PPMT formula in D34
Advanced Financial Modelling
34
=PPMT($B$20/12,A34,$B$21*12,-$B$22,0) link the formula with D35 and drag up to
D45
g. Calculate Interest Component in Cell B24 =IF(B19="Yes",SUM(C34:C45),0)
h. Calculate Taxable Income in Cell B26 =B12-MIN(B17,150000)-MIN(B24,200000)
i. Create Tax Calculation (C5), Incremental Tax Points (D5) and Remaining Table Salary
Headers
j. Under Incremental Tax Points (D6 to D8) enter the following Values – 250,000, 250,000
and 500,000
k. Under Remaining Taxable Salary write the following formulas:
In Cell D6 =B26-D6 In Cell D7 =E6-D7 In Cell D8 =E7-D8
l. Under Tax Calculation
In Cell C6 = 0 In Cell C7=IF(E6>0,IF(E6>D7,B7*D7,B7*E6),0) In Cell C8=IF(E7>0,IF(E7>D8,B8*D8,B8*E7),0) In Cell C9=IF(E8>0,B9*E8,0)
m. Calculate Total Tax in Cell C10 = Sum(C6:C9)
Advanced Financial Modelling
35
5.3 Case 3 – Return Calculation and Radio Button Presentation
Given a table of stocks with Returns and Standard Deviations, we need to
1. Rank them based on Returns
2. Sort them in another table based on ranks
3. Format the entire row in the tables based on a selection of a radio button about the company
market Capitalization.
Steps:
a. Calculate Rank in Cell E6 by using the syntax =RANK.EQ(C6,$C$6:$C$12,0) and drag
it down.
b. Type Rank 1 to 7 in Cell J6:J12 for creating table for proper arrange the do the following:
Stock Name =INDEX($A$6:$E$12,MATCH(O14,$E$6:$E$12,0),1) Market Cap =VLOOKUP(O$15,$A$6:$D$12,MATCH(L$5,$A$5:$D$5,0),0) Return =VLOOKUP(O$15,$A$6:$D$12,MATCH(M$5,$A$5:$D$5,0),0) STD =VLOOKUP(O$15,$A$6:$D$12,MATCH(N$5,$A$5:$D$5,0),0)
c. For Radio Button Creation Go to Developer and insert Check Box of three and rename
the same as LargeCap, MidCap and SmallCap
Advanced Financial Modelling
36
d. Right Click each radio button change color as Blue, Green and Red and Right Click each
radio button – by using format control option link each radio button to next right
cells(example: I6, I8 and I11)
e. For Radio Button presentation – Go to Home Tab – Select Conditional Formatting –
Select New Rule – Select Use a formula to determine which cells to format and write the
following formulas:
=AND($B6="LargeCap",$H$6)
=AND($B6="MidCap",$H$6)
=AND($B6="SmallCap",$H$6)
5.4 Case 4 – Stock Return Modelling & Normal Distribution Curve
Given a table of stocks daily prices
1. Calculate Returns and Statistical Parameters
2. Check how many days returns were positive
3. Check how many days a combination of any two of the stocks gave positive returns
4. Quick test for normality with distributions – discuss the variability of returns of each
Steps:
Calculating Daily returns for DLF, Eicher & Hindalco:b. In Cell E8 – use the formula = (Today Value – Yesterday Value) / Yesterday Value for
calculating Daily returns – Convert into % and drag to right and down for the entire period
Advanced Financial Modelling
37
Calculating Statistical Parameters:a. Calculate following descriptive statistics: Mean =AVERAGE(E6:E498) Median =MEDIAN(E6:E498) Standard Deviation =STDEV.P(E6:E498) Max =MAX(E6:E498) Min =MIN(E6:E498) Range =E503-E504
b. Calculate Number of Positive Return Days =COUNTIF(E6:E498,">0")c. Calculate Total Days =COUNT(E6:E498)d. Calculate Days above mean return =COUNTIF(E6:E498,">"&E500)e. Calculate Days below mean return =E508-E510f. Find out Number of days 2 of the stocks gave positive returns DLF & DLF (K504) =SUMPRODUCT(IF($E$6:$E$498>0,1,0),IF(E$6:E$498>0,1,0)) DLF & EICHER (L504) =SUMPRODUCT(IF($E$6:$E$498>0,1,0),IF(F$6:F$498>0,1,0)) DLF & HINDALCO (M504) =SUMPRODUCT(IF($E$6:$E$498>0,1,0),IF(H$6:H$498>0,1,0)) EICHER & DLF (K505) =SUMPRODUCT(IF($F$6:$F$498>0,1,0),IF(E$6:E$498>0,1,0)) EICHER & EICHER (L505) =SUMPRODUCT(IF($F$6:$F$498>0,1,0),IF(F$6:F$498>0,1,0)) EICHER & HINDALCO (M505) =SUMPRODUCT(IF($F$6:$F$498>0,1,0),IF(H$6:H$498>0,1,0)) HINDALCO & DLF (K506) =SUMPRODUCT(IF($H$6:$H$498>0,1,0),IF(E$6:E$498>0,1,0)) HINDALCO & EICHER (L506) =SUMPRODUCT(IF($H$6:$H$498>0,1,0),IF(F$6:F$498>0,1,0)) HINDALCO & HINDALCO (M506) =SUMPRODUCT(IF($H$6:$H$498>0,1,0),IF(H$6:H$498>0,1,0))
Advanced Financial Modelling
38
a. Enter -3SD, -2SD, -1SD, Mean +1 SD, +2SD and +3SD in Cells C513 to C519b. Link Mean value from Cell E500, F500 & H500 with E516, F516 & H516c. In Cell E515 =E516-E502 drag above two cells and In Cell E517 =E516+E502 drag below two
cells do same for Eicher & Hindalco
a. Find out distribution of DLF – Copy and paset the SD values from C513:E519
b. Use Frequency function for finding days equivalent to SD distribution
=FREQUENCY($E$6:$E$498,$D$523:$D$529) Ctrl + Shift + Enter & drag down
c. Find out percentage in Cells F523:F530 by dividing the distribution days by 493
=E523/493
d. Do the same for Eicher and Hindalco
Advanced Financial Modelling
39
a. Select three stocks % of Frequency Distribution and create a new table in Cells
H542:L550
Advanced Financial Modelling
40
5.5 Case 5 – Constructing Z - Table
Construct a z-table in Excel – and plot a standard normal curve
Steps:
a. Enter a Value 0.0 in Cell A8 then add 0.1 in A9 drag it down up to A28; Enter a Value
0.0 in Cell B7 then add 0.1 in D7 drag it to right up to K7
b. Use Norm Dist function for getting the value in to Cell B8
=NORM.DIST($A8+B$7,0,1,1) drag it to right and then down
c. Copy the entire table and paste it to below in Cell B32, then in Cell B33 change the ‘+’
become ‘-‘ negative values of table will appear
d. Use False Cumulative value create table and plot – enter value -3 in Cell A57 then add
0.1 in the A58 and drag it down up to A117 to get +3
Advanced Financial Modelling
top related