• Sharebar

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.

The WSO Advantage - Investment Banking

Financial Modeling Training

IB Templates, M&A, LBO, Valuation + Learn More.

IB Interview Prep Pack

30,000+ sold & REAL questions Learn More.

Resume Help from Actual IB Pros

Land More IB Interviews. Learn More.

Find Your Perfect IB Mentor

Realistic IB Mock Interviews. Learn More.

Comments (14)

  • CompBanker's picture

    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.

    CompBanker

  • In reply to CompBanker
    duffmt6's picture

    CompBanker:
    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.

    "For I am a sinner in the hands of an angry God. Bloody Mary full of vodka, blessed are you among cocktails. Pray for me now and at the hour of my death, which I hope is soon. Amen."

  • TheKing's picture

    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.

  • calikid3820's picture

    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.

  • frgna's picture

    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.

    if you like it then you shoulda put a banana on it

  • PTS's picture

    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.

  • Solaxun's picture

    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.

  • labanker's picture

    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.

  • In reply to labanker
    TheKing's picture

    labanker:
    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.

  • Going Concern's picture

    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_...

    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 new day brings new hope. The lives we’ve led, the lives we’ve yet to lead. A new day. New ideas. A new you.”

  • Solaxun's picture

    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.

  • Going Concern's picture

    To unlock this content for free, please login / register below.

    Connecting helps us build a vibrant community. We'll never share your info without your permission. Sign up with email or if you are already a member, login here Bonus: Also get 6 free financial modeling lessons for free ($200+ value) when you register!

    “The new day brings new hope. The lives we’ve led, the lives we’ve yet to lead. A new day. New ideas. A new you.”

  • In reply to Solaxun
    Going Concern's picture

    “The new day brings new hope. The lives we’ve led, the lives we’ve yet to lead. A new day. New ideas. A new you.”