INTRODUCTION
In pharmacoeconomic analysis, we oftentimes incorporate data from external sources to inform the parameters of our economic models. It’s not uncommon for us to have some degree of uncertainty surrounding the values of our parameters. The size and influence of each uncertainty may influence our conclusions, which can significantly change based on a single parameter. For instance, if a new drug (Drug A) entering the market had an incremental cost-effectiveness ratio (ICER) that was greater than the stakeholder’s willingness-to-pay (WTP) threshold when compared to standard of care (Drug B), lowering Drug A’s price may move the ICER to below the WTP threshold (Figure 1). This could change the conclusion of the study from “Drug A is not cost effective compared to Drug B,” to “Drug A is cost effective compared to Drug B.” Of course, this depends on where the WTP threshold is located on the cost-effectiveness plane, but it highlights the importance of understanding the influence of a single parameter to a pharmacoeconomic model’s conclusion. This type of analysis to understand the influence of a single parameter on the model’s conclusion can be illustrated using a one-way sensitivity analysis.
Multiple one-way sensitivity analyses can be performed and arranged in a way to visualize the parameters with the greatest influence on the study’s outcome using a tornado diagram. The tornado diagram arranges the impact of each one-way sensitivity analyses from largest (top) to smallest (bottom) on the chart (Figure 2).
You can perform multiple one-way sensitivity analysis and arrange them in a tornado diagram using Excel’s Visual Basic Application (VBA) macros. In this tutorial, you’ll be able to create a series of VBA macros to conduct one-way sensitivity analyses and visualize these as a tornado diagram.
MOTIVATING EXAMPLE
We will use a hypothetical decision tree model to perform a series of one-way sensitivity analyses. You can download the Excel file and high resolution images for this tutorial from the GitHub repository for Decision Tree Tutorials here. The example decision tree and its parameters are illustrated in Figure 3.
The base-case ICER comparing Drug A to Drug B is $5875 per one additional Cure achieved (Figure 4). The ICER is located in the Northeast quadrant.
We will create a macro that will replace the Base-case value with the LL
and UL
values (lower limit and upper limit, respectively) for every parameter and re-calculate the ICER each time.
Here is what the final one-way sensitivity analysis table will look like after the macros are run (Figure 5). Notice how the Number
column is not in ascending order. That’s because this is ordered by the Spread
column, which is the absolute difference between the UL_ICER
and LL_ICER
. We sort by the Spread
because it allows for the tornado diagram to place the parameter with the greatest influence on the base-case ICER at the top and the parameter with the least influence at the bottom.
We will create three macros. The first macro will sort the Number
column in ascending order. This helps with the one-way sensitivity analysis calculations. The second macro will perform the one-way sensitivity analyses by replacing the Value
with the LL
or UL
values. The last macro will sort the Spread
in descending order for the tornado diagram.
Note: In this tutorial, the tornado diagram is already generated and mapped to the data on the one-way sensitivity analysis table. I wrote a tutorial on how to create a tornado diagram, which can be accessed here.
To create and edit macros, you will need to make sure that the Developer tab is available on the ribbon panel (Figure 6).
In Excel, go to the File > Options
. In the Options window, select Customize Ribbon and check the box next to Developer in the Main Tabs column (Figure 7).
Once the Develop tab is enabled, we can begin to examine the macros. To open the VBA editor, go to the Develop tab and click on Macros
as seen in Figure 8. There are several macros that we can see under the Macro window. Select the oneway_order
macro and click on Edit
. This will open the VBA editor where we can examine the macro.
The VBA editor has several important features.
In the left panel are the various macros and their location. The right panel contains the VBA code for the macro. The macro controls allow you run (“Play”), pause, or stop the macro.
Note: Normally, we save our Excel file using the *.xlsx
extension. However, when you have an Excel macro, you need to save the file as an *.xlsm
extension. This will allow you to save your macros and enable them later.
VBA Macro 1 – Sorting the one-way sensitivity analysis table
Let’s examine the first macro: oneway_order
The VBA code first selects the sheet, which is labeled as OWSA model
. Within the sheet, it selects the area defined by AM7:AV15
which is the one-way sensitivity analysis table in the Excel spreadsheet. The next part of the code orders the one-way sensitivity analysis table in ascending order based on the values in the Number column which is defined as AM8:AM15
.
Note: Since this is on the same worksheet, we can leave the label of the active sheet as OWSA model
. But if you were using different worksheets, you need to make sure to change the active sheet’s name.
The second part of the code tells the VBA macro to sort according to columns and that the first row contains the headers or column labels.
VBA Macro 2 – Re-calculate the ICERs with the lower and upper limits for each parameter
The second macro performs the one-way sensitivity analysis, and it is labelled as oneway_macro
. Let’s take a close look at the VBA code for this macro. Notice how there is a low
and high
component in this macro. This is due to the LL
and UL
on the parameters table in the decision tree model. The oneway_macro
will replace the base-case values with the value from the LL
and UL
columns in the decision tree model. This will need to be repeated for each parameter. In this decision tree model, there are 8 parameters that we will perform the one-way sensitivity analyses on. For the VBA macro, we will call these 0 to 7.
Figure 11 provides a close inspection of the macro and its elements in relation to the decision tree model in Excel. Notice that this is a loop function. The loop will start at 0 and end at 7 iterations, which is a total of 8 iterations, the same number of parameters in the decision tree. In the VBA macro, this is initiated with For low = 0 to 7
and then repeats with Next low
until it reaches 8 iterations.
By changing these parameters with the LL
and UL
values, the ICER will be re-calculated. Once recalculated, we can update the one-way sensitivity analysis table with the new ICER value. In the VBA macro the re-calculated ICER is in cell AI7
, and its value is imputed into the one-way sensitivity analysis table starting at cell AT8
.
One the VBA macro completes, all 8 parameters will have undergone the one-way sensitivity analyses, and the re-calculated ICERs would be imputed into the one-way sensitivity analysis table; the LL_ICER
and UL_ICER
columns will contain the re-calculated ICERs for the LL
and UL
values from the parameters table.
VBA Macro 3 – Re-sort the one-way sensitivity analysis table using the “Spread” in descending order
Once the ICERs have been re-calculated using the LL
and UL
from the parameters table, we can estimate their influence by calculating the absolute difference between the UL_ICER
and LL_ICER
. We want the absolute difference because we’re interested in visualizing the difference with the low and high values from each parameter.
Here is what VBA Macro 3 looks like:
This is very similar to the VBA Macro 1, which orders the one-way sensitivity analysis table based on the Number
column. The only difference is that instead of using the Number
column, VBA Macro 3 will sort in descending order the one-way sensitivity analysis using the Spread
column, which contains the absolute difference between UL_ICER
and LL_ICER
.
Let’s take a look at the VBA macro closely (Figure 12).
The macro first selects the one-way sensitivity analysis table (AM7:AV15
). It then selects the Spread
column to sort in descending order (AM8:AM15
). Once sorted, the macro will end.
Note: You may notice that the Order:=xlAscending
option may sound counter-intuitive since we are sorting by descending order. The reason why the option has this in xlAscending
order is because the tornado diagram uses a reverse axis on the Format Axis
option (see Figure 13).
Assigning macros
Once the one-way sensitivity analysis table is completed, we can assign these macros to buttons on the worksheet.
We can create buttons using the Shapes function in Excel. Right-click on the shape and select Assign macro
. Create three of these. Figure 14 illustrates the steps to assign macros to each button.
We will assign oneway_order
macro to the first button (Figure 15).
We do the same thing for VBA Macro 2 (Figure 16).
Lastly, we assign the final VBA macro to the “Step 3” button (Figure 17).
Final steps
One all the buttons have been assigned to a VBA macro, we can click on each one in order to repeat the steps of re-calculating the ICER and updating the one-way sensitivity analysis table.
We can also see the changes to the tornado diagram (Figure 18).
At this stage of the tutorial, it’s good practice to inspect your entire Excel file to ensure that no data was accidentally lost or erased during the creating of the macros. Sometimes, if we select the wrong cells, we can overwrite our data. It’s good practice to save different versions of the Excel file so that you can return to your previous work in case something goes wrong.
Conclusions
Excel provides a power tool to generate VBA macros to perform functions iteratively. We leveraged this tool to perform a series of one-way sensitivity analyses which we then used to create a tornado diagram. Since this tutorial used a single worksheet, the process is much easier to code and diagnose. However, as we expand and create more worksheets in our Excel file, we need to be careful to select the proper sheets when using these VBA macros.
Acknowledgements
I used templates of previous one-way sensitivity analyses to build these VBA macros in this tutorial. Admittedly, it has been years since I first began doing this in Excel, and I don’t recall the sources. However, I wanted to acknowledge them as they deserve all the credit for helping me understand and apply these tools to building pharmacoeconomic models. If I find out the sources of these macros, I will post them here. I do know that my courses at the University of Washington was a source for some of the VBA macros for constructing pharmacoeconomic models, and I wanted to acknowledge their work and materials.
References
You can download the Excel file(s) from the GitHub Repository on Decision Tree Tutorials (link).
Disclaimers
This is a work in progress, therefore, there may be updates in the future.
This is for educational purposes only.