By Tim Heng
Excel is a really useful tool for forecasting. Usually, Excel models tend to be deterministic black boxes based on a series of assumptions (inputs) and a set of rules (calculations) that result in a fixed outcome (outputs). You can run different, limited numbers of scenarios and simulations by changing inputs in various combinations. But in most cases, when you want to do some “proper” simulation analysis (think 10,000+ runs based on an input probability distribution), you will usually find an add-in or macro, or even look to a completely different modelling tool.
What if I can show you a simulation tool that’s built into Excel, is easily scalable and can be added to your existing financial models with minimal effort, without the use of VBA or any sort of macros? The latter point is particularly important—there are many issues with VBA compatibility floating around right now as Excel for Mac reintroduces VBA, and as code needs to be updated for 64-bit and newer versions of Excel.
Let me (re)introduce you to the humble data table.
For those who haven’t used data tables in the past, and are looking for a more in-depth view of how they work, check out Mark Horowitz’s fairly comprehensive write-up in the October 2015 newsletter. A data table answers a relatively simple question: “what-if” a target cell had a value X, instead of its current value? If you’re evaluating a business that sells widgets at a price of $10 each, you could quickly assess the profitability at $8 each, or $12 each, or any number of different prices.
How is this relevant for running simulations? Well, consider what the overall process is: generating a random input based on a probability distribution, and calculating a result. If we have a random number, we can feed that into a data table to generate results.
In this simple example, the data table is not doing much using multiple RAND() functions and calculating the resulting die roll. Note that the RAND functions recalculate as a final step after the data table updates, so the input random number in the left column doesn’t relate to the output in the right column. However, if we apply the same logic to the first example, we can see how we can funnel the random numbers through a broader financial model.
There is another trick though. Because the RAND() function is volatile, it is recalculated each time anything in the model is updated. In that way, even if the data table contains no input information, or the target cell is not linked to the calculations (for example, using the NA() function), any RAND() function in the model will recalculate for each data table calculation (i.e., number of rows for row inputs, number of columns for column inputs, or rows x columns for a 2-dimensional data table).
In this instance, the data table is simulating the outputs of the model for 10 sets of input random numbers. If we extended the data table, we could simulate results for, say, 10,000 simulations instead.
Overall, this is a simple and efficient way to run simulations natively in Excel without the use of external add-ins or complicated VBA.
Tim Heng is a Director at SumProduct, specialising in Excel modelling and training services. He can be contacted at tim.heng@sumproduct.com.