Dynamic sensitivity analysis

[AndrePrevin]

Hi

I'm trying to set up a sensitivity analysis that will constantly update the table values if I enter a variance or change one of the input variables.
I have a table of variables which all impact the NPV of a project. The NPV values are in the table.
The problem is, I have to make the NPV values static every time I change a new variable, otherwise the new change overwrites the NPV values corresponding to the other variables.

For e.g., if I enter -5% to one of the variables in the table, the baseline project NPV (in the table) and the tornado chart values should automatically update.
If I reset that variable to 0 and change another variable, say to +10%, the baseline project NPV (in the table) and the tornado chart values should automatically update.
Etc.

Is this possible? Would greatly appreciate if someone could actually do a mock up for me and send me the Excel file.
I've attached an image of the Excel sheet with my attempt at the sensitivity table and an explanation of what I want.

Thank you

NPV values in red are linked to another cell in the workbook which automatically calculates a new NPV whenever the variance is applied.

Hence, I have to constantly change one variable, take the NPV from that other cell and paste as a value here

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sensitivity analysis
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.