Financial Modeling using Microsoft Excel

Financial Modeling using Microsoft® Excel®

Module 1A: Valuation modeling

Part 1 – Introduction to valuation modeling

Understanding integrated financial models

- Basic valuation techniques: accounting-based, market-based and cash-flow based - Revenue versus potential

Scenario analysis

- Dealing with uncertainty: why we look at different scenarios

-Case study 1: defining a worst-case scenario

Working with cash-flows

- Deterministic versus stochastic

- Case study 2: building a cash-flow projection for a fixed-coupon bond

Interest-rates and the price of time

- Various definitions of interest-rates and yield curves - Discounting cash-flows

QUIZ

Part 2 – Putting it together: how to value a company?

Backward-looking accounting-based valuation techniques

- Ratio analysis and key financial analysis metrics - Case study 3: profitability analysis

Forward-looking accounting-based valuation techniques

- Projecting financial ratios

Discounted cash-flows valuation techniques - Modeling stochastic cash-flows

- Case study 4: using Monte-Carlo simulations

QUIZ

Module 1B: Project finance modeling

Part 1 – Profitability analysis

The internal rate of return

- Comparing different projects

Financial Modeling using Microsoft® Excel®

- Comparing different projects

- Case study 5: calculating an internal rate of return Opportunity Cost

- Relative project profitability

- Market penetration and modeling competitors Various measures of profitability

- Cash-flows patterns and uncertainty

- Case study 6: profitability of a Venture-Capital fund investment

QUIZ

Part 2 – Capital usage and diversification

- The various forms of capital - Why companies need capital Capital-based profitability measures

- Risk versus return trade-off

- Return on capital and RAROC measure Contribution of additional risks

- Incremental usage of capital

- Case study 7: understanding diversification

Assessing required capital

- Various measures of risk-based capital

- Case study 8: calculating a Value At Risk

QUIZ

Module 1C: Merger and Acquisition (M&A) modeling

Part 1 – Advanced company valuation

From several values to one valuation

- Comparing the various company valuations

- Using situational factors to increase valuation accuracy Influence of external factors

- Analysts’ recommendations

- Case study 9: impact of credit agency ratings

Analysis synergies

- Market segments overlapping - The effects of correlation

Financial Modeling using Microsoft® Excel®

Part 2 – Debt & equity structures

Financing acquisition deals - Debt-based financing - Equity-based financing The various types of M&A deals

- Equity, cash and equity-related derivatives

- Case study 10: valuing warrants

Capital increases: dilution and accretion effects - Assessing ownership dilution

- Case study 11: calculating ownership dilution

Putting it all together

- Case study 12: iGate-Patni model

QUIZ

Module 2A: Macros for financial modeling

Part 1 – Introduction to the use of macros

Writing and using macros - Writing the macro - Running a macro

- Case study 13: writing a macro to calculate the price of zero-coupon bond

Working with cells and ranges

- Cells and ranges in excel tabs - Working with arrays in VB

- Case study 14: applying a function to an array of numbers

Using VB forms controls for flexible GUI

- Spreadsheet-based input/output v.s form-based

- Case study 15: displaying the result of a calculation in a textbox component of a VB form

QUIZ

Part 2 – Dynamic use of macros and advanced concepts

The various elements of VBA coding - Functions versus Sub

- Using references to spreadsheet data - Public versus Private properties - Use of modules

- Case study 16: defining new types of objects

Financial Modeling using Microsoft® Excel®

- Objects links in the spreadsheet - Function versus Sub macros

- Case study 17: Monte-Carlo simulation macro to price a European equity Call option

QUIZ

Module 2B: Advanced excel functions for financial modeling

Part 1 – Spreadsheet data manipulation

Search functions

- Using the functions Vlookup, Hlookup, Offset and Index

- Case study 18: practical use of the vlookup function

Ranges and names

- How to give a name to a range - Using the excel name manager Introduction to the Excel solver

- Case study 19: calculating a project IRR using the excel solver

- Introduction to coding the Excel solver in VBA

QUIZ

Part 2 – Using excel built-in functions

Statistical functions

- Mean, standard deviation, skewness, kurtosis

- Case study 20: calculating a percentile using the excel function

Probabilistic functions

- Normal distribution functions

- Case study 21: implementing the Black-Scholes pricing model for options

Date and time functions

- Working with date functions

- How to use the exact date & time in VBA code

- Case study 22: using the function “days360” for bond valuation

Text and strings functions

- Functions to combine strings

- Functions to split strings into small parts

Financial Modeling using Microsoft® Excel®

Module 2C: Advanced charting and dash-boarding techniques

Part 1 – Using charts in Excel

- Data ranges

- The various types of charts

- Case study 23: example of Linkedin chart

Charts formatting

- Elements of the chart - Positioning

- Formatting

Advanced charting techniques

- Adding curve-fitting formulas - Controlling chart features from VB

- Case study 24: changing the data ranges from VB

QUIZ

Part 2 – Using tables and automatic formatting

Using tables

- Auto-formatting - Using pivot tables Creating dashboards

- Using summation functions in excel: sum, sumif, sumifs - Defining printing ranges