1.7 Limitations of Data Tables

DataTables are very powerful, but of course have some limitations or restrictions, which include:

• They are required to be placed in the same worksheet (of the workbook) as the input that is to be varied. It is easy to work around this restriction simply by copying the input values into the worksheet containing the Data Table, replacing the original input values by a cell reference to these copied values, so that the copied values become the model input(s).
• They can vary only at most two inputs at the same time. To work around this restriction, multiple input can be varied by creating pre-defined scenarios and creating a DataTable in which the scenario number is to be entered. This requires the use of a lookup function (see later courses), to replace the original model input with values that have been looked-up according to the scenario..
• The model must be a “dynamic” one. Although models generally contain many calculations which alter as the input values are changed, in some models any change in input values also requires that some other routine or sequence of calculations be performed that is not directly contained within the model. As an example, a model may use a date (or a set of dates) as a key input, and each time new dates are used, the model may need to refer to an external data set to find values of other items on those dates (such as the exchange rates to a foreign currency). Thus, one could not simply use a DataTable to run a sensitivity analysis (with dates as the input), since this external process would not be invoked by the process. In such cases (i.e. for “non-dynamic” models), it is typically necessary to automate the process of sensitivity analysis with VBA macros: The macro is an automated process which works through the values of the input to be sensitised, and – for value – it runs the additional routine (e.g. linking to external data) before recalculating the model to give the final output result for that input value, which is then recorded in an Excel range. (This contrasts with a dynamic model in which the final calculations are done by simply recalculating the model (using F9) as soon as any input value is altered). Since this process involves VBA macros to implement, it is covered in a later Level.