Basic Course on Excel (5 Classes)

In the above playlist you will find many small videos on Excel.

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

Day 1: Basic prerequisite for excel:

1. For typing 1 to10 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: 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 that required some stat knowledge.

Day 3: Excel Financial Modeling (Monte carlo Sim):

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 durations

Day 4 Financial Modeling Excel (Risk):

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. Co-variance method to calculate the portfolio risk.

Matrix Multiplication

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

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

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.

Exam of Excel financial modeling:

The exam of 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.

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

One on One Customized Training:

qcfinance.in believes in personalized touch so that our clients are completely satisfied with our service. In this regard, we offer One on One Customized Training to our clients.

These Training are provided on requests by our clients & are customized according to their individual needs.

The course structure & timings for these training are highly flexible, classes are scheduled as per the convenience of our clients.

Contact Us for More details: info@learnprogrammingnyc.com

Course on VBA for Financial Engineering:

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

Custom Video Solutions:

In yet another innovation to simplify things for our learners, qcfinance.in has introduced the Custom Video Solutions Options. If a learner has some specific query but don’t have the time to come to live online class, then he/she can ask for custom video solution for the query. qcfinance.in will make a video recording that will be uploaded to YouTube or given directly to the learner in mp4 format for there viewing. A learner can go through the recording as per there convenience. If still doubts exist, he/she can email us  anytime for clarifying them.

Here are some examples of custom video solutions provided by qcfinance.in:

Contact Us: info@learnprogrammingnyc.com

Skype Id: qcfinancein