I have an Excel model of medium complexity that I'm trying to do some sensitivity analysis on. The ideal would be to have, for each of a set of inputs, run over a range of possible values and store the resulting output values (so maybe changing one input value at a time, but at the next level would be nice to change multiple ones at once).
I know there are plugins for this – solver.net seems pretty sweet – but I rebel at spending $1000 for what is a conceptually simple issue. For now I am following the manual algorithm of change-the-value, see-the-new-value, copy-paste, repeat. Which sucks.
Do folks have recommended tips/techniques/macros for how to automate this process?
Best Answer
Solver (and Goalseek) is for optimising a situation where variables need to be flexed to produce a given constraint (ie maximise value, minimise cost, solve for a particular number).
Your problem is a more straightfoward sensitivity analysis.
Data Tables are a simple way to flex inputs through a model for simultaneous outputs, 1 way (1 changing variable) and 2 way tables (flexing a table of paired row and column inputs) can be added easily.
This will probably suffice for you.
More advanced data tables with 3 or more inputs
If you want to run scenarios changing 3 or more variables, then you can workaround the 2 way table limit by defining cases 1-10 in the data table, where a selection of 1 may drive a certain combination for variables A,B, C & D, a selection of 2 drives a different combination etc.
The picture below does this. The cell in D10 picks the variables from D14:18 to run through the calculator in D3:D8.
The basic calculator is Cash = Volume * (Revenue-Cost-O/H)-(1* Tax Rate).
The Data Table in C23:D28 shows the output from the 5 scenarios simultaneously (ie 56 for scenario 1, 80 for scenario 2 etc).