I have a model I developed that is of medium complexity, and I have an interesting problem that I am not sure how to resolve, thought I'd hit you guys up to see if anyone has dealt with this before.
Generally the model is fine, but it is broken on occasion when you change various assumptions (financing case, operating case) - but the break isn't truly a "break". The balance sheet will be off by decimal places, nothing substantial, but enough to flag the error switch. If you screw with the decimal places of generally any number (stub period seems the most easy to play with) the model will balance again. Something weird is going on with the rounding causing the model to unbalance, and I cannot figure out how to rectify it aside from just playing with the decimal places on the stub period every time the model shows an error. Generally this is fine for me, but for others that aren't familiar with it, the issue can cause some confusion.
Thoughts? I'd be happy to send it out if necessary.
Consider upping the number of iterations. (Alt + T + O to bring up options tab, go to Formulas, and set max iterations to 100). If not enough iterations are run, sometimes the balance sheet will not balance by pennies or less.
This.
My only other suggestion would be to add an interim step before calculating the balance check (use a ROUND function or whatever). This is a pretty lazy route though.
Just make it so the check uses the ROUND( ) function and have it round to one or two decimal places. Unless of course the model is off in every period by the same amount, then there's likely a really small linking error somewhere in the model.
I am actually a HUGE fan of Trunc( ) and just keep the number of decimal places you go out to the same throughout the model.
Iterations, manual calc, then just hold down f9 until it works :) If it's still off though there might actually be a bust. Post it somewhere and I'm happy to take a look.
round() for the balance sheet check and use say 4 to 5 decimals. When you run iterations it might get caught as being off by 0.000001 which really is a balanced model.
Comp: I actually did try increasing iterations already, i went all the way up to 100k, or maybe 10k, don't remember... which obviously made the computer crawl and it was still unbalanced. It is not off by pennies, its by a few 100k (model is in mm), but nothing significant, never over this amount.
TheKing: I am 99.9% sure it's not a linking error, the amount is never off by the same size. The error check does use a round function but in any event the rounding error occurs on the first decimal since the variance is never over $1mm.
Its definitely something to do with rounding, I may just have to manually tinker with the stub decimal places whenever I have a bust to get it to work, it will balance that way, it's just not as "clean" as I would like it to be.
If your model is off by a few hundred K then it is most likely not an iterations problem or a floating point problem (Excel approximates decimals which is why you might get 1 x 10^(-12) when you should have zero, especially if you are running circs and have iterations on). But just to make sure it's not an iterations issue, I suggest you break any circs you have and turn iterations off. See how that affects your numbers. If the problem resolves, go back and check how you've linked things up. Excel can work through circular references, but sometimes if you go overboard and have a ridiculously circular reference or too many circs, things can start to break down. Make sure you have your circs down to the bare minimum, and make sure your logic is all buttoned up (e.g. you don't have some dumb circ where your sum range includes the cell in which the SUM takes place). To check this, work through each circ with the "Error - Circular Reference" tracer tool.
If breaking the circ and turning iterations off has no effect, then it's a true balancing issue. Given it's a variable amount each year, I suggest starting your troubleshooting with your cash flow statement / debt schedule. That's often where the problem lurks if it's not a constant amount that's off each year.
And finally. Contrary to what some other posters have mentioned here don't use any rounding functions. Build a clean model.
Let me know if this helps resolve the issue.
Given the size of his imbalance, I agree with this. Try breaking the circular reference (zero out the revolver and recalc, then put the formulas back in and recalc, etc.) Sometimes that does the trick. Otherwise, I am also inclined to believe it's some super minor, but significant enough, linking error in the CF statement.
I've had this shit happen enough times that I found that its' best to just go for a walk outdoors. Getting away from the spreadsheet madness for a few can go a long way towards helping you spot the error.
Sounds like a mountain is being made from a molehill here! I've seen this happen plenty of times. The fact is that excel is not built perfectly, and when you have lots of calcs linked together and flowing all over the place, especially with circular logic, excel sometimes has trouble getting things just right due to its limitations on precision, and more calcs compound the issues. Looks like it can only handle 15 significant figures within its memory juices.
http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
For example, if you type =PI() into excel and increase decimals to the max, it won't give you more precision than 3.14159265358979, which is obviously not Pi by any stretch! The same holds true for all the gears and pistons whirring in the background of the poorly oiled excel machine. Most bros won't even notice this defect as long as they're doing a few simple calculations with nice whole numbers.
If that's not the issue...then there's probably something off with the model.
The reason I know its not an actual model bust is because, as mentioned, if you get it to flag errors and cause the BS to unbalance by toggling various cases (some balance, some don't), you can cause it to re-balance simply by increasing or decreasing a decimal place on the stub period, or for that matter, almost any other number anywhere in the model. I have never seen anything like it. I do appreciate all the feedback and thanks for your answers. If there is a cloud site anybody is aware of that I could post the model that may be the easiest way to illustrate what I'm referring to.
I have a hunch its a non-issue, I just would prefer it to balance without having to screw with decimals, just for my own satisfaction.
If you have circular logic embedded within the model, then it's likely that changing the decimals of a stub period is having the same effect as a circuit breaker being used to turn the circularity off and on, ie force excel to do a fresh recalc of the whole model.
Going Concern - in a moment of supreme idiocy on my behalf, I was pressing shift f9 for some reason and not f9. Shift f9 did not want to work, but f9 rebalances it. Strange as I have not had this issue before with the rounding problems, but just a simple re-calc did indeed fix it. Thanks all.
I won't say "I told you so" or "I can't believe you caused all that commotion"...just end off with a humble 'you're welcome broseph'.
I've had this happen several times. Most of the time it's caused by a cell referencing itself. If there is a roll out component to your model, check there. Also, if you have a complicated D&A section, check there.
While trouble shooting, go back to the balance sheet and hold f9 to see how things are acting after each change.
Doloremque et nihil est dolor qui porro odio. Non quibusdam repellat occaecati consequuntur sed adipisci reprehenderit labore. Omnis sit aliquid nam rerum quod. Debitis est inventore et iusto culpa vel totam.
Eveniet quidem voluptatum et aliquam placeat aut ut. Sint quis aut molestias ut libero unde. Et qui neque rerum unde. Est non eveniet voluptate quas similique.
Asperiores eaque quae omnis vitae et corporis sed. Nobis molestias occaecati optio pariatur.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...