2 Variable Sensitivities with Dynamic Inputs

Hi all!

I'm resorting tou you guys as I'm losing hope at this point.

So I'm trying to create a 2 variables sensitivity using the "what if" analysis. The row variable are 3 scenarios while the column variable are the % ownership in each year of a specific investor.

The issue lies in the fact that these 3 scenarios impact the % ownership as well but I can't seem to capture it via the table, the % seem not to change despite the scenarios (rows) are correctly taken into account in the data table.

I'm getting the same issue across 3 tables (see attached), in the example I've hard coded the % ownership and financials, but these are dynamic in the actual model as the different scenarios (LOW, BASE, HIGH) have an impact on the financials which then impact the valuation of the company which ultimately impact the shareholding structure (growth shares dilute ordinary shares and so on, also these shares vest at different times so the year matters as well).

Sensitivities issue.xlsx

Yes sorry I'm using Office 365

The issue relies in the 3 sensitivity tables from row 75 to row 89. These are all dependent on the shareholding percentages from row 42 to 48, which are already a "product" of the sensitivity table in row 30-39.

These percentages change based on the value of the company (row 25) for each year as each year some of the C shares (row 30-33) vest and start having an economic value, all these then dilute the other share classes (row 34-39).

This implies that in the real model row 76, 81 and 86 should change based on the scenario and each year. However it doesn't seem to be the case, for example: the sensitivity table from row 76 to 79, the results doesn't make sense since in the low scenario the shareholder should be diluted less compared to a low or high scenario, while these % stay the same across the three.