In Excel, Data Tables are employed to showcase various outputs for a given range of different inputs. Widely utilized in financial modeling and analysis, they help evaluate diverse scenarios, particularly amidst uncertainties regarding future outcomes.

The primary use of Data Table lies in conducting sensitivity analysis. Given that financial models depict a best-guess scenario regarding a business's future trajectory, it proves beneficial to analyse the outputs under different scenarios (Optimistic and Pessimistic) by changing the key input assumptions.

In financial models, both Excel Data Tables and VBA (Visual Basic for Applications) Data Tables serve similar purposes but offer different approaches and flexibility. Here's a comparison between the two:

Excel Data Tables

It is a built-in feature, part of the What-If Analysis. Excel offers two types of Data Tables: 1D (one-dimensional) and 2D (two-dimensional).

A 1D Data Table in Excel allows you to explore the impact of one input variable on one or more output variables by varying the input across a single row or column.

A 2D Data Table in Excel allows you to analyze the impact of two input variables on one output variable by varying both inputs across rows and columns.

The shortcut for creating these Data Table is ALT, D, T. This will show you the Data Table dialogue box in which you can select the row and column inputs.   

Overall, while Excel Data Tables are valuable tools for sensitivity analysis and scenario planning, they have certain limitations:

1. Model optimization: Having multiple Data Tables may lead to performance issues and slow down the spreadsheet. If the Excel calculation mode is set to Automatic, the data table will be calculated each time any input is changed which pauses you to do any operation during that time.
2. Inputs placement: The inputs linked to the Data Tables need to be on the same worksheet as the Data Tables otherwise they won’t work.

VBA Data Tables

To overcome the limitations of Excel Data Tables, one can use VBA Data Tables in the models. They work in the following manner.

1. Create a macro to apply the formula in the Data Tables and store the results as values.
2. Assign the macro to a button object. The user can use the button to update the Data Tables if any input is changed.

VBA Data Tables mitigate the issue of model efficiency, and you can also keep your inputs anywhere in the model, as it will only be calculated when you run the VBA / press the button.

Despite some major advantages of VBA Data Tables, there are a couple of drawbacks to it. First, you need to know how to write a VBA code and second, you need to press the button / run VBA to update the results each time to analyse it.

Conclusion

You should thoroughly review while choosing between Excel and VBA Data Tables. If the model is complex and you need multiple Data Tables, then you should go ahead with the VBA Data Tables.

At FAB Analytics, we have expert analysts, who can help you in your scenario and sensitivity analysis requirements without impacting the efficiency of your model.