Course Overview:
This course gives an overview of applying Excel in a most efficient manner for typical calculations in investment analysis and portfolio management. Delegates will be introduced to some of the less widely known but highly useful built-in functions. Spreadsheet templates are used in group exercises to solve case study - like problems. The focus of the course is to deliver practical value for working professionals. The calculations discussed in the course are typical examples encountered in performance measurement, risk management and portfolio management.
Methodology:
The training consists of classroom-based teaching combined with group exercises (four per day).
Who should attend?
The course is not only for specialists but for a wider audience including investment managers, asset management executives of all levels, institutional investors and research analysts.
This course has been designed for the benefit of:
- Research analysts
- Portfolio managers
- Risk managers
- Fund analysts
- Performance analysts
- Quantitative investment analysts
The course assumes a general familiarity with financial markets, instruments and investment portfolios. A basic understanding of statistical and mathematical concepts is an advantage.
Materials:
Delegates will receive colour printouts of all slides and electronic access to Excel spreadsheets used during the course.
Microsoft®, Excel® and Windows® are registered trade marks of MICROSOFT Corporation.
09.15 - 12.30
Background
- The origins of Microsoft Excel (help to understand some of its current features)
- Application versions
- Specifications and limitations
- Basic configuration after installation
Overview Basic Functions
- Cell formulas
- Array formulas
- Solver for numerical search problems
- Pivot Table for data management
- Regression analysis
- Conditional formatting
- Important charts
- Recording macros, introduction to Visual Basic for Applications
12.30 - 13.30 Lunch
13.30 - 17.30
Return Calculations
- Discrete and continuous compounded returns
- Chain-Linking and annualizing returns
- Money-weighted return calculations
- Benchmark-relative statistics: alpha, beta and gamma
Risk Calculations
- Ex post volatility, annualized volatility
- Covariance matrix
- Ex ante and ex post contributions to volatility and tracking error
- Tail and downside dependency: conditional correlation
- Historical VaR and CVaR
- Maximum drawdown, underwater chart
Four group exercises will be solved during the first day
Day Two
09.00 - 12.30
Simulation Methods
- Normally distributed returns
- Normally distributed correlated returns
- Non-normal returns
- Random portfolios
Portfolio Construction
- Mean-variance portfolio optimization with realistic constraints
- Risk parity portfolio construction
- Non-optimized portfolio construction methodologies: deriving allocations from scores
- Expected returns using the Black/Litterman methodology
12.30 - 13.30 Lunch
13.30 - 17.30
Advanced Risk Measurement
- Resampling
- Historical Scenarios
- Confidence intervals for skewness and kurtosis
- Factor analysis of a portfolio: contributions of factors to portfolio risk and return
Four group exercises will be solved during the second day
Review and Conclusions
