Dan Mayoh is the founder and Managing Director of Fintega Pty Ltd, a financial modeling consultancy based in Australia. He is a financial modeler and spreadsheet specialist with 15+ years’ professional experience in financial modeling and advisory, with an emphasis on the infrastructure and funds management sectors.
Dan was a lead member of the Question Design Team for many years for the old ModelOff Financial Modeling World Championships competition, and currently assists both the Financial Modeling World Cup and the Financial Modeling Institute in content creation for their competitions and exams. He has been awarded both the Advanced Financial Modeler (AFM) Chartered Financial Modeler (CFM) designations from the Financial Modeling Institute. A keen mathematician and poker player with a degree in Actuarial Studies, Dan also enjoys using Excel to analyse and simulate games of strategy and chance.
Methods for Modelling Stochastic Simulations (Monte-Carlo analysis) and Sensitivity Analysis in Excel – via a Ten-Pin Bowling Example
The first part of this session will use the scoring of a game of Ten-Pin Bowling to demonstrate a few different methods for running stochastic simulations (also known as Monte-Carlo analysis) in Excel, some using VBA and some using only native Excel functions. By assigning input probabilities to all of the different outcomes of a frame of bowling (strike, 9-spare, 9-miss etc) I will then simulate via various modelling methods many full games of bowling and analyse the scores and other output metrics.
The second part of the session will focus on sensitivity analysis and scenario modelling, building upon the results of the first part. I will discuss and demonstrate modelling methods (again with and without VBA) to adjust the original input probabilities of the outcome of a single bowling frame and see what impact this has on the key output metrics, and how we can compare these results to our base case.
Although the object being modelled is only a game, the techniques discussed can carry over to many real-world financial models that we may build in Excel, and can help with analysis anytime there are inputs that behave as random variables or need to be sensitised. And the game makes it more fun!
Excel Virtually Global is an independent conference and is neither sponsored nor approved by Microsoft.