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.
| +176 | Analyst: Why is UBS IB doing worse than peers?, UBS CFO: No Comment | 31 | 51m |
| +163 | UBS Tech layoff | 52 | 2d |
| +149 | UBS’s M&A BANKERS ARE DOING TERRIBLY. $200M EXTRA IS BEING CUT | 31 | 1d |
| +129 | Leaked Interview Questions to Recruiting Services | 59 | 1h |
| +65 | How do YOU manage networking requests? | 20 | 16h |
| +60 | Are target prospects getting dumber? | 27 | 1d |
| +34 | MBB Pay Raises | 26 | 2d |
| +22 | US 2027 SA IB worried about background check | 8 | 2h |
| +21 | Earliest you can get fired | 7 | 12h |
| +20 | Career Crossroads | 4 | 42m |
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
Accusantium maiores illum eveniet itaque unde aut. Quia at aut vel eum hic sit soluta. Blanditiis incidunt voluptas minus sapiente consequatur sed. Corrupti molestiae suscipit et qui.
Et non qui et temporibus autem doloremque inventore. Aut voluptates voluptas quae quam. Reprehenderit deserunt error sunt sunt voluptas sint eos odit.
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...
Deserunt dolore id ab eos ad. Quidem eum asperiores sint quos incidunt deserunt. Quia enim ut officiis inventore occaecati recusandae.
Explicabo cumque aut repellendus id omnis quas. Ut asperiores quis voluptatem odit. Et et error atque.