31.5 Calculate model values in Excel

This section describes how you can feed TP model values to Excel, perform calculations within Excel, then return those calculated values to TreeAge Pro as part of model analysis.

We will use theBilinks tutorial example model BiLink_Calculations_Example.trex to highlight this process. It is connected to the Excel workbook BiLink-worksheet.xls in the same folder.

The TreeAge Pro model contains three variables that drive calculations – NumDays, PerDiem and TaxRate. Both of the inputs NumDays and PerDiem are defined at the strategy nodes (Inpatient and Outpatient) and not the root node.

Now examine the Bilink settings in Tree Preferences.

There are Bilinks going in two directions in the top two sections. The top section sends data from TP to Excel, and the second section returns data from Excel back to TP. The last section "Send analysis results to Excel" is minimised.

Now let’s examine the Excel workbook which the model refers to: BiLink-worksheet.xls.

Within Excel, there are three named cells that receive data from TP: Num_days (C3), Per_diem (C4) and tax_rate (C7). (The named cells are listed in column B to assist in this example). Those values from TP are then used to calculate named cell Cost (C10) within Excel. The value of Cost (C10) is then returned to TP.

Note that the values from TP for Num_days (C3) and Per_diem (C4) are different for each strategy. So the Excel workbook actually calculates the Cost values twice - once for each strategy - then returns the Cost appropriately for each strategy in the TP model.