Automatically identify sensitive variables

  • Thread starter Thread starter daniel morton
  • Start date Start date
D

daniel morton

I have an extensive excel model with a large number of
inputs and formulas. I was wondering if there existed an
add-in or something similar which gores through cell by
cell and determines which input have the greatest impact
on a given output. So for example if you had variables a,
b, c which give you an answer d, the add-in would
automatically rank b for example as having the greatest
coefficient. I am aware of tables but this only works with
2 variables, far short of the 1000 in my model. thanks,
even though this may be the holy grail there may be some
ways of simulating part of it. thanks.

Daniel - la boheme
 
It isn't clear what you want.

Do you want the coefficients x of the relationship
D= x1 * A + x2 * B + x3 * C

or do you know the relationship and want to know the impact on the
result from a change in each of the variables?

If the first, do a regression using LINEST.

If the latter, there is no generalized answer. You need the partial
derivative of D with respective to each of the variables, and *that*
value will depend on where you evaluate the partial derivative.

For a function such as f=1/x + log(y), either x or y can have the
greater impact on f.

In a business perspective, consider the cost of setting up a new
facility. Suppose it is F0 + c * F1, where F0 is a fixed cost, F1 is
the per-unit production cost, c is the quantity produced. Also,
different locations have different F0 and F1 values. Then, whether F0
or F1 is more important depends on how much is produced.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top