A traditional sensitivity analysis involves testing a limited number of scenarios (e.g. base, upside and downside). Each scenario is a set of predefined inputs. This approach shows the outcomes of the model at various perspectives, but does not give a precise likelihood of a particular result to happen.

In contrast, the Monte Carlo method tests a large number (several hundreds or thousands) of ‘scenarios’ in which the inputs are drawn as random numbers. The results of the model (gross profit, IRR etc.) are also represented by ranges of numbers. Analyzing statistical patterns of those ranges the analyst can determine mathematically the chances of an output being within a specific range or being higher or lower than a certain threshold.

In this publication I am sharing a technique of Monte Carlo analysis in Excel. My approach is based on standard Excel functions and data tables without macros. The accompanying model performs essential Monte Carlo simulation: drawing random numbers of certain distribution types, making correlations, interpreting the outcomes.

Reviews Add a review
No reviews yet

More From Andrei Okhlopkov

Browse our top rated business templates. See All
Inventory Analysis and Management
In this publication I will discuss how to analyze inventory, communicate findings of your analysis and make practical use of…
Price-Volume-Mix Analysis
This set of tools, methods and techniques will help you calculate and depict variances. A special focus is made on…
Measuring and Modelling Seasonality
Many businesses are affected by seasonal demand changes. They cause their revenues, expenses, inventory and resource requirements change through the…
Interim Budgeting (Reforecasting)
Virtually every company develops its annual budgets. The budgets are compiled before the beginning of the financial year of a…
Profit Distribution and Carried Interest Waterfall
This template describes real-world mechanisms of distributing profits between project participants. It starts with modeling a distribution to a preferred…
Descriptive statistics for grouped (weighted) data
Excel has a powerful set of tools to perform statistical analysis, but they apply only to ungrouped data. As an…
See All