Merry Christmas.
I am modeling a retail store operator, and my balance sheet does not balance. All my BS items link to the CS statement. The strange pattern I see is - in year 1, the model is off by 10.6, in year 2 its off by 21.2 and so on.
Any places where I should look? Advice is much appreciated.
| +167 | How to stop feeling like shit for not making it in IB? | 63 | 2h | |
| +121 | If Tik Tok is forced to sell, what banks do you think would be involved in the deal? | 59 | 9h | |
| +85 | Updated LA Banking Scene (2024) | 61 | 3h | |
| +40 | Ranking banks that went under | 24 | 5h | |
| +39 | Burnt Out M&A ASO | 20 | 5h | |
| +36 | Relevance of A-Levels for U.K. London recruiting | 25 | 3h | |
| +33 | A strategy for SA applicants late to the game like myself | 17 | 1h | |
| +27 | Series 79 Help / Tips to Pass The First Attempt | 11 | 2d | |
| +27 | Intern Ettiquette | 4 | 1d | |
| +23 | What are hours like at BBs in London? | 47 | 1d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
First, I'd take how much it's off by and divide it by 2 and see if that number pops up somewhere. Sometimes you just flipped a sign or something.
If that doesn't work, I delete all of the years after the first one that doesn't balance (just on the balance sheet), take the difference of the balance sheet accounts from the first projected year and the last actual (ex. 2016E - 2015A), and compare this to the cash flow statement. It should line up exactly.
If that doesn't work, check out your depreciation schedules and your retained earnings/net income/dividends.
If you still can't find it, you're gonna have to go through it cell by cell
Have a gut feeling something thats supposed to be negative is positive or vice versa
I agree with checking d&a schedule vs. net fixed asset accounts and net income vs. retained earnings accounts, unless your CF is very complicated and you messed it up.
Thanks to all for comments. I am still hitting my head against a wall. Here's what I went back and checked: - forecast (yr1)/2 -> this number didn't match with a yr1 and yr0 difference - verified difference of each BS cell is in CF statement - verified signs - verified DA schedule, net income and retained earnings
I am sure I am missing something, would anyone be willing to do me a favor and give me a set of fresh eyes to look? I have commented my spreadsheet in great detail, due to WSO limitations - please delete spaces below: [ tinyurl . com / oksfjm5 ]
The error is: BS is off by 10.6 in yr1, off by 21.2 in year 2. There is a constant which doubles each year, but I cant seem to find it.
Thanks for the help. Any ideas / pointers / criticism is appreciated.
M194-196 are being kept flat from year 1, should either be 0 or factored into the balance sheet. Deleting those three cells balances
I am having this exact same problem... feel like I've checked everything but can't figure it out. Any way you can help me here?
Thanks very much trader_timmy, I owe you one - I removed those to balance the model.
To enhance the model, are the following steps appropriate: increase in LT investments (say X) is listed in cash from investing, and maps to 1. Increase in Other Assets 2. Decrease in Cash (so cash is CFO+CFF+CFI- X )
Seasons greetings, thanks again.
You dont need to get into that, if you think you'd like to improve capex items, just do something wrt to maintenance/restoration capex vs. new stores capex type of thing.
Stuck on the model.....Cant seem to balance it (Originally Posted: 06/26/2010)
I tried to link the cash flow from the 08 the 09 balance sheet, but I cant get it to balance. If anybody would like to take a look, it would be great. thanks
http://www.megaupload.com/?d=N2LI3544
bump
How come depreciation doesn't affect net income?
haha....why r u doing this?
depreciation isn't always stated in IS, it's likely hidden in COGS
just looked at it quickly.. why is amortization counted in both current assets and LT assets? also, look into additional paid-in capital, i'm not sure how stock based compensation should affect it. Finally, are you sure the hardcoded values in fy2009 are correct?
Financial Model Isn't Balancing (Originally Posted: 06/05/2015)
I am truly an idiot. Found my mistake: beginning balance of SE and PPE was driving off the annual periods, not the ending Q1 2015 periods, so net income and D&A/Capex was being double counted in the next quarterly periods. Thank you to the community members who helped me fix this up.
Balance it through goal seek function.
Lol'd. Or just add an extra row ("Other") which balances the difference. Make sure to have the entire "Other" row reference an external link and make the entire row invisible via ";;;".
@ existor I'm familiar with goal seek but that's not the way I should be doing it.
Thanks @fromtheshadows, that got me a little closer. Now at least I know the balance is off by a relatively flat amount q/o/q
Caught errors in working capital, unfortunately still off now by -22.552 in each period.
i'll take a quick look if i have time, but you should never balance a model via goal seek... wtf?
do you still need help on this or did you figure it out: accountant checking in, i have no idea what this model is for, but statements are my thing?
I think fromtheshadows is onto something. I made the edits to reference the Q1 2015 cash balance and 2015 Q1 interest expense but I am still off by -9.915 in each period. The new model is saved up. Thanks for all the help so far.
The model is just an operating model, trying to build it for practice
The model off by 22.552 according to the excel file
Have you looked at the difference between each line item vs the previous year to see if the difference matches the one on the balance sheet check since it's a constant number?
I haven't looked at the model but if the balance sheet is out of balance, the best way to find out why is comparing the difference between line items from the last year it was in balance to the first year it's not. The difference is driven by the cash flows, so make sure cash flows are moving by the difference and moving in the correct direction.
Care to post the question to this model? Thanks.
Updated model has been posted. Balance is off by -9.915 in each period. I appreciate all the assistance thus far but am still not able to find my error.
NM
Balancing merger model (Originally Posted: 12/23/2013)
I am building a merger model and I have somehow gotten stuck. If you're willing to help me fix it (should be pretty easy), please shoot me a pm.
I'll hook you up with some pdf ebooks for the trouble
I'll take a look
I can take a look.
I can look.
shoot it over if you're still having trouble.
I'm up for having a look
Did you figure this out?
Need some assistance building a model: "Other" accounts and accounts with similar names but different balances (Originally Posted: 01/27/2015)
Hello all, I'm building a model of a company I'm familiar with, for my own edification and as a means to make myself more attractive to banks. I've come across two issues I need some help with. I'm hoping one of the more experienced folks in this forum can provide some insight. Here is my issue:
First, I have liability and asset accounts on the balance sheet market "other". The balances for these accounts seem to vary in an erratic and non-linear way (hence the other, I suppose). However, the magnitude of these balances is non-trivial. Because of the ambiguity and, because of the large balances, I'm tempted to use these accounts to balance the BS statement model. However, this seems a bit sloppy. If I were doing this for work and had access to better resources, I'd assume it would be appropriate to call management and try to understand what exactly these accounts are. However, I do not have such luxuries. So, I'm not sure what I should do here. Can I balance the BS by changing the "other" accounts to be what they need to be, in the absence of other information?
Secondly, the financial statements from the company have Deferred Revenue and Accounts Payable accounts on the Cash Flow Statement and Balance Sheet that are the same in name but, very different in magnitude. It has made linking the two statements difficult. Since I've been unable to reconcile these accounts, I've taken to just projecting their future values by linking them with historical trends and predicted trends for future revenue. In the absence of information, is this acceptable?
I currently see these solutions as the only recourse. But, being rather ignorant of best practices in the industry, I don't want to embarrass myself in front of an MD or a Senior Analyst.
Thank you for whatever help you can provide.
If it is desired, I can provide the model, as well.
I think providing the model would go a long way to help
Need Assistance Balancing a Quarterly Model (Originally Posted: 09/01/2015)
All,
I seem to run into problems often with quarterly models, specifically when it comes to reconciling historical cash flows in the reported quarters. Calpine, the company I am trying to model, has reported Q1 and Q2 2015 financials, which I have entered in. However, I am out of parity in the projection period.
I'm not sure why I can accurately balance annual models and not quarterly, but if anyone has a chance to take a look I'd appreciate any input.
Thank you
Looked at it for a few minutes, couldn't find everything, but found a few
Atque consequatur deserunt totam dolorum ducimus culpa. Quod deserunt labore quis aut temporibus explicabo earum. Et expedita dolor aperiam quaerat harum. Odit quibusdam labore incidunt cupiditate occaecati velit.
Enim repellendus est iure. Dolorum quidem ex sunt sit. Illum optio incidunt ducimus exercitationem. Consequatur quos molestiae dolore commodi natus. Nihil asperiores laudantium officia labore incidunt non. Molestiae voluptatem ratione perferendis id.
Quis omnis quis repudiandae eos qui totam ut. Magni officia repellendus tempore placeat velit ratione. Unde voluptate harum reiciendis explicabo molestias veniam.
Quos quod earum omnis natus. Sunt aut eius reprehenderit dolor. Corporis quia quibusdam temporibus voluptatem sed et. Est aut ullam non nisi.
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...
Quo et aut dolor. Adipisci incidunt ut qui consequatur labore consequuntur non cumque. Ipsam veniam consectetur voluptatibus placeat atque et. Incidunt maiores asperiores optio maiores ab praesentium earum. Dolores repellendus ea praesentium autem.
Ex deserunt eos recusandae ut aliquam atque. Architecto ea voluptatem labore odio sit. Magni ut aut ut non. Quis laborum expedita aut placeat molestiae. Omnis sunt ut facilis est. Eaque at quam deserunt possimus id recusandae aliquam enim.
Similique rerum sint autem aperiam dignissimos iusto in. Aut quasi architecto dolores aliquam officia eos.
Distinctio fugiat accusantium vel non aliquam. Eius aut accusantium est quos fugiat. Cum magni rerum iusto doloribus nisi omnis. Odit officia impedit enim accusamus officiis et mollitia. Saepe dolorem vitae enim et.
Molestiae facilis harum laboriosam dolorem tempora modi nihil tempora. Saepe unde aut non. Doloremque itaque eum quasi et ab harum.
Dolorem deleniti fuga vel temporibus rerum. Eaque suscipit ut aut pariatur autem nihil. Beatae sint praesentium doloremque. Possimus mollitia non nam soluta tempore eos. Beatae at id laudantium nostrum aut placeat. Omnis nesciunt sit ut ut.