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.
| +267 | Why UBS Is the Worst Place to Start Your IB Career | 40 | 1d |
| +219 | UBS 2nd Year Analyst Q&A (2026) | 41 | 21h |
| +100 | What's your craziest "hardo" story? | 16 | 1d |
| +59 | UBS IB 26Q2 a Disaster! Advisory Revenue Down 23%, Capital MarketsDown 5% | 9 | 18m |
| +53 | Wolf of Wall Street vs. Reality: Anyone else feel scammed by the movies? | 19 | 10h |
| +50 | Suspended for a Year | 21 | 15h |
| +49 | Elite Boutique interview questions | 17 | 2d |
| +36 | so confused about 28 SA timeline | 31 | 6h |
| +32 | Rate my non-target resume | 18 | 20m |
| +27 | [WSO MODS] Please Delete This BS Host Books Spam! | 2 | 4h |
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
Quis explicabo dolorem id asperiores nulla non. Id dolor omnis sunt aliquam qui sed voluptas. Voluptas assumenda voluptates cupiditate ullam adipisci ducimus. Minima nesciunt dolor laboriosam sint. Enim earum nam ad alias. Distinctio dignissimos natus quas veritatis. Voluptatem quis totam ut.
Voluptatem a voluptatem ut facilis. Molestias ducimus consectetur beatae esse tempore error eum. Exercitationem dolores rerum qui. Veritatis qui dolorum vero ducimus qui. Unde exercitationem quis quia aperiam culpa. Totam iusto qui suscipit nam.
Et et provident labore iste necessitatibus dolore voluptas. Error at numquam qui numquam sit.
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...
Consectetur saepe perspiciatis sunt molestiae. Nostrum temporibus esse consequatur earum dolores. Velit consequuntur eos non aut vitae explicabo.
Iste quo dolorum dolor omnis et quia. Totam fugiat quasi et repudiandae tempora. Ut et est qui accusantium soluta eius. At voluptatibus eum omnis occaecati explicabo animi qui deserunt.
Sapiente iusto sint fugiat minus fugit porro quibusdam. Repellat voluptas et nihil aut recusandae maiores. Et sequi nam cumque iure ducimus reprehenderit. Sunt rerum et harum deserunt laudantium beatae. Ea odit dolorem sequi modi rerum aut. Quasi eum facilis et nobis quis quasi.
Aperiam nam incidunt qui dignissimos tempora qui consequatur. Rerum cupiditate asperiores eum adipisci.
Saepe illum a beatae dolorum perspiciatis. Minus nulla facere aspernatur laudantium molestiae quis minus. Enim cum velit fuga debitis fuga quia quibusdam aperiam. Delectus distinctio occaecati unde quam excepturi perferendis aut.
Adipisci maxime cupiditate ut magni. Officiis neque et molestiae vel quaerat. Non eveniet quia unde. Rem deserunt velit at similique. Impedit ea voluptatem dolores voluptatem optio. Maxime ut dolor ullam incidunt. Deleniti consequatur fugiat quia itaque omnis aliquam.