Sensitivity Analysis using LAMBDA, MAP and SCAN to create a dynamic table

Hi all,

I have built a 20-year model which calculates a closing reserves balance for each year based on a variety of assumptions.

I am trying to create a sensitivity table using dynamic arrays, where the reserves balance calculated is sensitized based on the inputs (% assumptions which affect revenue / inflows).

My issue, I believe, stems from the fact that Excel doesn't support "arrays of arrays", though I am not 100%. I have managed to use the below formula which shows the different outputs for a given year, however this is then simply dragged across for the rest of the table.

I would like to flesh out the below formula so that it takes into account the rolling balance and I have tried to use SCAN() in different ways, but I keep hitting the same #CALC error.

I have hit a wall trying to find a workaround, ultimately stemming from my lack of understanding of LAMBDA and its helper functions. I am seeking guidance on how to tackle this problem.

Is it possible to create a dynamic sensitivity table using MAP() and SCAN() so that it calculates each year's reserve balance based on all the different sensitivities?

=MAP(

(1+sensitivityArray),

LAMBDA(fleet,

LET(

baseYear,YEAR(INDEX(dateArray2045,1,1)),

t,YEAR(K$3)-baseYear+1,

inflows,fleet2025*fleet^(t)+Model!F$18,

outflows,SUM(Model!F$21:F$22),

open,Model!F$33,

contributions,SUM(Model!F$36:F$40),

withdrawals,SUM(Model!F$44:F$45),

surplus,inflows+outflows,

open+contributions+withdrawals+surplus

)

)

)

Here is what the output looks like over a 5-year period:

||2026|2027|2028|2029|2030|

:-:|:-:|:-:|:-:|:-:|:-:|

|0%|535,279|(1,765,611)|(928,252)|(5,526,149)|(6,903,036)|

|1%|600,016|(1,635,489)|(732,092)|(5,263,291)|(6,572,812)|

|2%|664,754|(1,504,073)|(532,009)|(4,992,508)|(6,229,248)|

|3%|729,491|(1,371,362)|(327,965)|(4,713,643)|(5,871,943)|

|4%|794,228|(1,237,357)|(119,919)|(4,426,536)|(5,500,490)|

|5%|858,965|(1,102,056)|92,166|(4,131,026)|(5,114,472)|