Presentation (CTI)

OBJECTIVES: The majority of decision analytic models submitted to health technology assessment (HTA) agencies are developed using Microsoft Excel. The approaches commonly used to construct Excel models have not been substantially updated in decades, and studies have found that these models are often slower and less transparent than models built using R or MATLAB. However, Excel was recently upgraded to add support for dynamic array functions. These functions have the potential to modernize how models are developed using Excel, simplifying their construction and improving their transparency and calculation speed. The objective of this study was to review these new functions, and to consider how they may be used to construct decision analytic models.
METHODS: A comprehensive review of all 36 new dynamic array functions was conducted to identify those that may assist in the construction of decision analytic models. An exemplar state transition model was then developed to illustrate the use of these functions.
RESULTS: The LET, SCAN, REDUCE, LAMBDA, RANDARRAY, SEQUENCE, WRAPROWS and WRAPCOLS functions were identified as assisting in the construction of decision analytic models. As illustrated by the exemplar model, these functions allow for probabilistic state transition models to be developed without the need for Visual Basic for Applications (VBA) macros, substantially improving calculation speeds. Indeed, given only a set of input parameters, these functions can be used to calculate the mean discounted incremental net benefit of a treatment, across thousands of Monte Carlo simulations, using a single formula in a single cell. This, in turn, allows for value of information analysis to be conducted without using VBA macros.
CONCLUSIONS: Probabilistic state transition models can be constructed in Excel without using VBA macros, simplifying their development and improving both transparency and calculation speed. This modern approach also reduces the burden faced by HTA agencies when validating models.