Model breaks down - unable to calc sensitivity tables

[krizziii]

Hi guys,



I have a pretty big problem. I have developed an extensive financial model with a lot of flexibility. The problem is that it takes a lot of time to run the calculations each time, and most importantly; it is nearly impossible to calculate sensitivity tables with it. It takes about 5 minutes to calculate each cell of the sensitivity table… So it is not optimal.



I have no idea where to start searching for the mistake. The model is big, but at the same time not outrageous; it is 6,100 KB. So it has to be some mistakes, not the size creating the problem. It is built as an LBO (if anyone know how that works), so there is one circular reference.



I have put iterative calculations to 100. Every time I calculate the model (F9), it says ITER: 1, 2, 3, 4 etc. down in the right corner, but very slowly. Furthermore, it says Data table: 1 at the same place.

I have used a lot of SUMIF and offset, but no VLOOKUP etc.

Do anyone know where to start? Is there a common mistake here? What could possibly make a model so extremely heavy to calculate?



Best,

K

 

[GraH - Guido]

Offset is volatile, so every manipulation causes a full recalculation. So does each iteration...
I understand it can be difficult to share a sensitive financial model, but if you want pinpointed advice, you could perhaps make a sanitized version of your model. So the data is fake, but the model and the formulae remain real.