II.3. Risk, Simulation and Optimisation using Excel/VBA

Risk Assessment, Monte Carlo Simulation and Optimisation

OBJECTIVES

  • Learn the essential concepts in risk assessment and risk management.
  • Develop the skills to build and run quantitative risk models for general applications in business and finance.
  • Reinforce skills of building and using VBA macros.

DESCRIPTION

Overview. This course covers risk assessment and to risk quantification using Monte Carlo  Simulation in an Excel/VBA environment, as well as the

Pre-requisites. This course requires a good working knowledge of VBA. It is open only to students who have completed the Level II Study Course “Automation and Algorithms with VBA and Macros”.

Practical Work and Exercises. Readers are expected to build simple examples for themselves as they follow the text. The course also contains downloadable data sets or simple models that allow a reader to do this practical work without having to enter large sets of data or repeat previous steps.

KEY TOPICS

  • Risk Assessment. Purpose · Meaning · Benefits · Risk management · Approaches and techniques · Business applications · Uses in finance.
  • Getting Started with Monte Carlo Simulation. Random sampling · Repeated calculations and automation · Creating and running simulations · Results analysis and control
  • Further topics. Probability distributions · Inverse functions · Statistical analysis  · Choice of distributions.
  • Examples of key applications. Cost budgeting · Risk registers · Cash flow risk · Time schedule risk · Project cost-time integration · Random walks and Brownian motion.
  • Portfolio optimisationHow optimisation arises · Methods to find optimal solutions · Applications in general business and in finance · Portfolio risk and return · Model calibration using optimisation · Other optimisation objectives · Value-at-risk · Markowitz model · Analytic and numerical solutions · Huang-Litzenberger · Using Solver in Excel and with VBA. 
  • Introduction to sequential optimisation. Tree-based decision-making and real options· Conditional probability · Bayesian analysis · Value of test-based information.