Excel Modeling (Advanced) for Financial Engineering (Supplement to CFA FRM prep)

Excel Modeling (Advanced) for Financial Engineering (Supplement to CFA FRM prep) course experience by Satyadhar Joshi

satyadhar_joshi@yahoo.com

Personally I think Excel is cliche, and too much mundane but one has to do what is to be done.

Below is a video about my course:

VBA for Quant Finance in 10 classes [Ver1]

Introduction: This article talks about the Excel financial modeling I took and about the important topics and interesting areas in the training. I have put in daily analysis of what I learned and pointed out anything that requires a special effort.

Tough areas to model are: Monte carlo Simulations, derivative pricing models, interest rate options, currency derivatives.

Day 1 of Excel finance training:

1. Basics of excel like relative and fixed referencing
2. Areas for excel modeling that are tricky will involve FRA and portfolio
3. Operating leverage and changes as Operating Leverage parameter changes, increasing and decreasing fixed and variable costs and looking at curves
4. Solving Liner Equation with objective and constrain functions, these are the ones of linear programming
5. Portfolio Construction and use of matrix in excel, there is 5 element matrix

Basic prerequisite for excel:

1. For typing 1 to 10 first write ist no. then in 2nd row just add 1 in that cell nd then copy b2 then using shift n down then up to that level where u wanna to reach then do ctrl +v
2. ctrl x to cut, ctrl v to paste it , use ctrl+shift+5 to make it in % terms
3. ctrl page up and crl page down to go and come back to next sheet
4. ctrl+space to select complt collumn , shift+space select row
5. use' f5' to go any cell
6. How to colour,undo and redo,f4 to lock
7. Absolute vs. relative references
8. 'IF' loop, NPV , IRR ,
9. SENSITIVE ANALYSIS. SCENARIO ANALYSIS , (ALT+W+FREEZE)?
10. TRANSPOSE, SUM PRODUCT,MMULT(SHIFT+CTRL+ENTER)
11. f2 to link down things

Day 2 on Excel Finance training:

1. Solver for Linear Modeling
2. Regression: R adjusted, return vs price for beta, regression stat, etc. these are things also important for CFA L2, FRM L1.
3. Beta calculations: Regression, formula, bottom up
4. Monte Carlo simulations
5. SIP Vs FD
6. Story of Multiple IRRs
7. Replicating Data and values

Out of these the beta is a complex issue and so is the regression thing.

Day 3 of Excel Financial Modeling:

1. Mostly about simple Monte Carlo, searching for pie, understanding the multiplication of variables
2. Then sensitivity for Duration and bond, convexity
3. Rest all was simple
4. MC was explained in a very simple way
5. Freq function
6. min max
7. percentile reading greater than 0
8. bin count
9. percent rank command
10. linking sheets ctr pgdown
11. Stat basics like kurtosis, skewness, and other parameters
12. medians and quartiles for Monte Carlo
13. regression vs correlations
14. all types of duration, derivation of formula of duration

Day 4 Financial Modeling Excel:

1. Revision of WACC optimization using solver
2. Different types of Sensitivity Analysis
3. CTR + page up to move between worksheets and referring between various worksheets
4. All functions of data analysis
5. Var and distributions
6. Is Kurtosis fat tails or peekedness 
7. Double effects of things in monte carlo
8. H-Look and V-look, the rows column match and the syntax things are conventional
9. Ratio analysis, there are around 40 ratio given in the CFA book
10. CF from parallel sheets BS and IS
11. Misc

Day 5 Excel Modeling Financial:

1. Using data table for finding scenario analysis, where we have to select 2 variables and then change the things as made in the rows and columns which is then linked to input and then things are linked downwards
2. Z spread using tool called____, and computing it based on spot rates of US treasury
3. Loan amount using
4. IRR using function
5. Du point in Excel
6. Ratio 
7. Common size
8. Crt W Z
9. Ctr W T
10. What if Analysis
11. TCF total Cf and its implication to new cash in the next years balance sheet
12. WACC and its relation to value of company
13. derivation of recievalbe days
14. last year cash + total cash flow = cash in balance sheet this year
15. salves vs turnover 
16. portfolio of 3 stocks and looking at matrix: 1*3 3by3 3*1

Matrix Multiplication

I will discuss in details all the areas especially matrix multiplication, transpose and other formula.

The most important part is to visualize the Covariance matrix, once that is done this become easy.

Summary of First 5 classes of excel finance modeling:

The experience was very good as I had a revision of all major concepts, especially Z spread and use of  Data table to predict 2 variable change in the scenario analysis. Also the matrix part requires a deeper understanding of the Matrix algebra and a revision so that I can apply for Finance.

Monte Carlo Financial Modeling in Excel:

First we need to define all equations and variable and find out the distributions of all variables, and once that is done we need to find out the future movements using Brownian motion. And then the variable which may vary as per our selected distributions. Finally we need to move ahead and draw the distribution of each outcome with its frequency or probability. So again here we need to work on Brownian motions to move into the future areas.

Week 2 Day 1 on Excel Training

1. FCFF tough questions (Requires knowledge about Cash flows, EBITA, why 1-t, different valuations models, etc)
2. Bonds
3. Portfolio of Bonds
4. Asset Liability matching
5. All 4 Valuation models were talked about but not implemented:  FCFF, Multiples, Residual income, Private Company valuations, these will be implemented

Derivation of Bond Convexity

Based on Taylor's theorem. Relation of function with first derivative and 2nd derivative.

Asset Liability Modeling

Matching is a tricky problem but requires no special skills.

FCFF

Capex and other issues, many models like Damodaran exists

Damodaran model was used to convert the Rnd, lease, EBIT normalization etc. A lot is given on his website.

Football field and making the 2 goal posts and finding out each values.

Synergy and modeling for acquisition, these were done in 2 ways, where one was fundamental and other was based on market values.

Multiple based modeling included 3 things:

p/e

p/revenue

p/sales

and finding out our company values using these multiples and which is more relevant etc.

Macro and VB

Macros are linking cell, making macros functions etc where the are just like Visual Basic. Adding graphics like check and buttons etc. Simple things were taught.

Shortcut= alt+f11

We can make the entire setting of number of digits and custom ideas for the cells.

How to use string and take it here and there.

Upcoming:

The things that I find most interesting in this area is VBA and application of VBA. Commands to manage the large data of SQL is another important concept. I think VBA will be done in the upcoming week.

Things that I want to see are hardcore Quantitative finance models.

Exam of Excel fin modeling:

The exam of the this was very rigorous and tested many concepts, let me give you an outline of the exam:

1. Breakeven point, contributions margin, effect of profit depends on the Fixed cost and variable costs and the slope of this line is important. Hence if a firm wants to change its Fixed cost or variable cost it depends on the breakeven point as it takes a qunat approach to the part about our investment on fixes or variable costs.
2. Duration and convexity of a bond and bond portfolio, this is simple, and how to hedge based on the duration, kind of same was seen in FRM exam
3. Projection of BS IS, using common size statement, and how to project future, here we had sales increasing and he had to increase the cogs, etc by the same amount, this is part of corporate finance CFA
4. portfolio management
5. alpha and beta
6. How to lever, unlever and do things to calculate beta and hence find out the cost of equity, where we have to go different industries and it was an imp thing. We need to know RFR and other things, hence beta is an important matters. 
7. But the thing that I enjoyed most is the bidder and target firm, and valuation of synergy, for this we had to calculate the reduced COGS and increased growth rate. But to incorporate all these things into accounting and Income statement is very interesting. In this term, we need to know accounting for merger acquisition, and other things.

Thus these were the main question of the test, and accuracy of 70% was required to do the same questions.

More to come:

1. IPO, merger, LBO and FPO Modeling
2. Black Scholes and binomial methods for  Derivative pricing
3. Binomial trees using Macros
4. Advanced  Bond Valuation advanced
5. Consolidation, merger
6. Taxation effects
7. Loss carry forward
8. Valuation from data of a company from scratch, which is downloading from their website, how to calculate risk free rate in real life
9. Using data from websites of NSE and data collection for modeling, linking models live to real data.
10. We have more models from Damodaran
11. Modeling for corporate control in India
12. ABS, MBS, RMBS, CLO, FSS, WBS in Excel
13. Sector Analysis: How to specialize any area for interview
14. Company Analysis: of ANY BIG ONE
15. Group presentations and making synergy 
16. Report writing skills, like how to apply news paper info to valuation

My course on VBA for Financial Engineering:

http://stockcreditfinancecfa.blogspot.com/2013/02/top-commands-of-vba-used-in-financial.html

Financial Modeling in MATLAB

MATLAB, SAS are advanced tools for modeling used in instruments of structured finance. As we have seen that Excel is good for Equity modeling, it does not hold very good when things become complex. The next Level of Excel is VB which I will be covering later.

I came across this book called financial forecasting where I did read about how I can apply all what I read in FRM L1 in MATLAB and other tools.

As depicted in my credit risk thread, I will here talk about how to simulate some of the major quant Finance aspects in MATLAB.

Written by: Shivgan  Joshi