It’s been a while since I built an M&A model, but I’ve wanted to do one since I’m developing a stock pitch for a company that’s the buyer in a merger process right now. My pitch is tomorrow.
Everything was dandy till I started looking at my spreadsheet and realized that somehow a circular reference had come up. This doesn’t exist in an older iteration of the model I’m working on, and it might have come up since I was referencing cells in a separate spreadsheet, but otherwise I don’t know how this could have occurred.
Is there anyone here that’s good at finding circular references and can help me figure out how this happened? Alternately, does anyone remember how to do a circuit breaker in Excel, thinking that perhaps this will break the circularity? There isn’t supposed to be circularity in this model since my interest rate calculations don’t depend on it, but I’m at a loss since I don’t know what’s going on.
Updating a previous item, I just re-opened my model after referring back to an old version, made absolutely no changes at all and the circularity got resolved. I’m so confused now. Of course, I’m delighted that my model isn’t broken, but at the same time I want to know what happens if this flares up again since I bet it probably will.
I know there are a lot modeling gurus on this forum so would be grateful for any help / explanations! Many thanks
Sometimes models have built in circular references that are overridden in Excel by going to File -> Options -> Formulas -> Enable iterative calculation. Could be this was enabled in a previous version of Excel but popped up as an error in Office 2010. This is common for calculating plug values, similar in nature to the ‘Solver’ function where it reaches the value by trial and error rather than direct calculation.
+1 on Iterations. Great for real estate modeling or project finance where you need to know how much to draw from your capital sources, but draws are based on the total costs, which includes interest expenses, which depend on how much you draw from your capital sources, which depends on the total costs, which depend on interest expenses etc. You get the idea.
Hey guys, thanks for pointing this turns out. I actually did have iterative calculations on my interest expense, but somehow Excel shut off iteration and I had to turn it back on again. Problem solved. Almost scared the sh!t out of me.
Another is deleting the formulas of the row where it bugs and rewriting them clean (in effect copy paste the formula outside of the years you are modeling, delete the original ones, recopy paste the formulas in their original places. Takes 5 seconds).
But I’ll admit not mastering circular bugs in models, and having “oh shit” moments when they happen.