Modelling with Multiple Variables

[ryancgarrett]

I have a financial model forecast that is based off a number of variables on an input sheet. I'm trying to create a matrix that shows the following:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[TD]Year 5[/TD]
[/TR]
[TR]
[TD]Variable 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Variable 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Variable 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In each cell I'd like to calculate the break even number for that variable, holding the other two constant. For example, default values for the 3 variables are 5, 25, and 10. In order to break even in year 1, Variable 1 should be 12, if variables 2 and 3 are 25 and 10 respectively. To break even in year two, variable 1 should be 9, etc. etc.

I can fill the whole matrix out using goal seek for the individual cells, but it seems like there should be a way to make the table dynamic. Are there any tools in Excel that can handle this?

 

[kbrummert]

Have you checked out solver? It's like goal seek but you can set it up to solve a range of cells based on target values and constraints. It's pretty flexible and can solve a surprising variety of complex problems.