What to do when Balance Sheet does not balance?
After 3 hours of anxiety, cursing (under the breath) and a pair of strained eyes, I have finally managed to balance my balance sheet.
Background: I am required to do a valuation model for a single business segment based on a fully consolidated model - so I did the usual extraction, reference etc. Also required to conduct some analysis hence the full model of statements etc.
Following my sweet success, I would like to share my personal steps to folks new to full modelling who have a hard time balancing B/S:
1) Find the net difference of Net Assets and Equity.
2) Eyeball the figure with every line item to see if they are exact match (most encounter would be the lack of cumulative effect in the b/s)
3) If exact match found, you should pat yourself at the back and go rectify the mistake.
4) If Step 2 is unsuccessful, start simplifying your model in steps – take out complex assumptions, eliminate different tax/debt structure, and observe how the net difference changes.
5) Once net difference is eliminated when an item/assumption is removed – Voila! You hit the jackpot and you can go home on time (hopefully).
As said, I find these steps useful for amateurs. If you have a better methodology of balancing the B/S in general, do share!
hardcode "TRUE" at the bottom always works
E=A-L. Works every time.
In all seriousness, if my BS doesn't balance it is usually due to me stupidly forgetting to capture some amort of intangibles or financing fees at some point (deduction before pretax income, addback to CFS, decreases the BS account). So I always check those first.
in an internal model i onced received from a very large utility player in Germany, the balance sheet also did not balance, and who ever did that just created a line item called "magic balancing number" to make it work.
If the difference doubles each year, its usually a flipped sign. Thats my first check.
You can also start deleting all lines (through all statements) relating to the same account to see if that makes it balance.
Unfortunately, there are a million things that could have gone wrong. Adding a few things as I referenced this post within the last day or two. Here are a few checks that helped me:
Check the Deferred tax liability / Intangible Assets / PP&E and other formulas that depend on the previous year. I have definitely made the mistake of linking to the change on the cash flow statement without adding that number to the previous year (referencing deferred tax liability specifically here). You probably already know these (PP&E = Beginning PP&E + Capex - Depreciation) and (Intangible assets = Beginning IA - Amortization of IA)
Another solid check is making sure you hit Ctrl+R on all of your formulas. I've gone back to change a formula in the initial projection year and forgot to drag it over to later years. You can eyeball for figures that don't necessarily make sense or have a logical trend. If you find any, you can use Ctrl+` to show the formulas and make sure they are correct
Don't waste too much time on checking growth, etc. assumptions as those wouldn't impact whether the balance sheet balanced. Typically, there is either a formula error in the balance sheet itself or on the cash flow statement
I hope this helps someone out. A few of the other comments defintely helped me. Happy balancing.
Plug the difference into Goodwill
Projected Balance Sheet won't balance. Help? (Originally Posted: 12/02/2017)
hswc, have you checked out these or run a search:
More suggestions...
If those topics were completely useless, don't blame me, blame my programmers...
Works!
Urgent Help Valuation - Can't get Pro forma balance sheet to balance (Originally Posted: 05/23/2016)
HI everyone,
I can't get my to balance, any help would be GREATLY appreciated!
Please find model attached. It might be a small problem i'm missing, but i can't seem to figure it out, in this instance.
Many many thanks if someone can help.
Thanks for looking, actually the numbers start by small outs but it ends up being quite a big. Cheers.
LOL. Depends on how you look at it. Say your are off by one, but its because you fucked up at least two numbers and the final result happens to be a 1.
Balance sheet imbalance impossible to solve (Originally Posted: 10/03/2016)
Hello, Could somebody help me to balance financial statement please ? I am completely lost. Especially about what to put in cash and equivalent.
Best regards, mio
Timing looks perfect for $WLKR $AKDS $XGTI
what about using cash as a plug to balance the model?
Who can help me balance balance sheet? Will pay. (Originally Posted: 08/09/2017)
Having a lot of trouble getting BS to balance from SCF inputs. IF you can help, I will pay, Need ASAP. Thanks!
Would be happy to help, but I think most people are going to pass on this because asking you what SCF takes too much work.
Care to post the Statement of Cash Flows?
Also, https://www.thebalance.com/how-to-prepare-a-statement-of-cash-flows-393…</a">see this site. You're essentially starting with Net Income then showing changes in cash changes in Operating, Investing, and Financing activities.
I'm curious. Post cash flow statement.
Help needed balance sheet dont balance (Originally Posted: 09/10/2016)
Any kind souls? As per topic....if anyone can help me now....i will send the files through pm big thanks in advance...its a small/ short balance sheet
*doesn't
Heya, send it up, I'll try to help
hey can you help me with my model? it’s not balancing.
Shoot em over and ill check why the balance sheet ain't balancing.
Hi, thanks all for replying...very sorry for being late in replying back...its over... got it settled already....thanks all anyways
Hey can you help me? my model is not balancing.
Hey, can you check mine as well? please pm me your email so i can send you the excel file.
New question. Anyone can help me please. Sorry for being a noob.
Is it possible that even if the links between balance sheet and cash flow statement are done properly but from cash flow statement, the ending cash flow (after adding to beginning cash flow), which will flow back to balance sheet is still negative?
Can this be resulted from assumptions (drivers)? After I increase values for drivers, no more negative cash.
Big thanks to whoever help.
Does the ending cash balance on the SCF tie to whats on the balance sheet?
Quick Balance Sheet Unbalanced Favor (Originally Posted: 12/29/2014)
Hey guys,
Long time lurker here. Anyone around to take a look at my model for a case study interview? I can't figure out why my model can't balance :( TIA!
PM me
can't PM yet...not enough bananas. can you PM me and ill reply?
got it. thanks guys for all the help!
Hello buddies, I got this financial model problem, it's a 3 year forecast. the first year balances perfectly but the remaining two years wont. Shows a constant balance when I change Liabilities or Capital but the balance increases when I input a figure in the assets. What could be wrong?
If it is a small error - less than 0.02 - I consider it a rounding error and either ignore or add a couple cents to the other side to make it balance. After that I divide the error by 9. If the result is a whole number it is likely a transposition error. From there it is just a line-by-line check to see what digits were switched.
Intern problems - balancing the balance sheet (Originally Posted: 04/05/2014)
I've been trying to balance this balance sheet for a DCF I'd like to build for IBM, but have been running into some problems. Was wondering if I could get some help from anyone.
Here are some of the questions I have:
I've projected out "short-term financing receivables" and "long-term financing receivables" on the balance sheet and then made the corresponding calculation in the cash flow statement under "receivables". Is this method okay?
-For PP&E on the balance sheet and accumulated depreciation, how do you go about calculating these? I've calculated the PP&E net amount by using the previous year and then subtracting any depreciation and "payments for PP&E" on the cash flow statement. I'm assuming this is the reason my balance sheet isn't balancing.
-And why are the intangible assets going negative in the future years? Am I doing the formula wrong?
Any help would be really appreciated. It's a really simple model. Still learning the process.
bump*
Balancing the Balance Sheet (Originally Posted: 02/22/2018)
My BS is out, major line items reconcile so problem must be prepayments and accruals which are estimates. What can I do?
Estimates shouldn't matter as long as: Assets=Liab+SE
Also, you need to close out your NI and Divs to RE
NI and RE done (no DIVS). I can force my estimates to balance it but it feels wrong as that removes consistency.
If you attach the spreadsheet, maybe we would be better positioned to try and help you find the pain point.
Reconcile the differences in your balance sheet periods with the corresponding year in the CFS. That usually identifies the errors / weeds out any potential linking errors you may have made
Need Help balancing the BS (Originally Posted: 06/25/2015)
I have been looking this over and over and it might just be time to start over. I have attached the balance sheet - let me know if there is any obvious corrections that I am not seeing because I feel pretty dumb right now.
Aut tempore omnis amet quo et occaecati. Autem voluptatibus debitis quis. Ut recusandae autem fuga at enim nesciunt dolorum. Autem aut quis perferendis minima soluta suscipit.
Quo sit veniam illo rem dolores occaecati laudantium. Aliquam distinctio sint delectus quia distinctio tempora. Non ut quisquam est pariatur ut deserunt velit.
Autem similique omnis asperiores voluptatem adipisci et sed. Iure dolorum facilis dicta qui aperiam. Et doloremque alias non dignissimos dicta qui. Odio repellendus omnis molestiae.
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...
Dignissimos iusto amet veritatis error voluptas aut assumenda. Reiciendis recusandae aperiam consectetur facilis vel quo. Beatae voluptatem nisi harum provident. Itaque laborum ad voluptatem veniam neque. Nesciunt quis ut recusandae sit fuga illo.
Et labore ex ducimus. Hic impedit et veniam libero. Magnam nemo voluptatem cum tempore quisquam natus in culpa. Mollitia omnis pariatur sed quisquam.