Financial Statement Modeling - Managing Circular References Using Iteration

[sykinc]

Hello all,
I am looking for someone with experience building company financial statement models that use circular references to link the 3 financial statements. I am building a 5-year long-range model and basically the model keeps "blowing up" despite the fact that I turned on the iteration ability. Is there a limit to the amount or level of circular references that Excel can handle? I ask this because I am doing a detailed 5-year, monthly model, so there is a tremendous amount of data. Furthermore, the company I am modeling is a asset management company, so most of the income statement is driven off of the balance sheet. The balance sheet is comprised of many types of investment assets (stocks, bonds, loans etc.) that grow with market returns and also change composition over time. These flow into the income statement in many ways: stock returns, income from debt, fees on assets under management, number of employees/costs linked to asset size etc. I am either doing something severely wrong, or Excel has reached a limit on the ability to handle so many variables. If it is not possible or advisable, is there a "work around" to still have these linked together? Please help if you have any advice or experience! Thank you in advance!

 

[benbulloch]

What do you define as "blowing up"? Does the model give you #VALUE errors? These are typical in my models with iteration that blow up. Files-> Options-> Formulas gives the the option of setting iteration to a set number of runs. In particularly fragile models of mine, I find when they "blow-up" (depending on your error) that if I copy the formula with absolute references to an adjacent blank cell than over-write the formula with $100, I can often recover from the #VALUE and then copy the formula back over. You might find that 2-3 formulas cause all your problems when you dig deeper.

I've also started taking iteration out of my models by using macros to copy monthly values from one sheet to another as a cut-n-paste rather than an automatic iteration. This is particularly helpful for me on monthly interest calculations where interest is in the budget and I pay interest on the budget.

 

[sykinc]

Thanks for the response! Sorry, I should have been more clear. The numbers in the nearer-term months of the projection model stabilize for the most part, but continue to "flicker", the outer years display either really large numbers or just "#######" across the cell. I've definitely also experienced the #VALUE issue and delete/re-paste as you say. But the nature of this problem seems different. The model just cannot come to an "optimal" solution it seems. Or, I am doing something fundamentally wrong in the calculations.