ryancgarrett
Board Regular
- Joined
- Jun 18, 2011
- Messages
- 122
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:
<tbody>
</tbody>
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?
Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |
Variable 1 | |||||
Variable 2 | |||||
Variable 3 |
<tbody>
</tbody>
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?