Financial Modeling
Ask your modeling questions here! Feel free to copy/paste specific sections from filings as appropriate.
Questions/Discussions
Sort by Date ▼ / Top Rated
Register for free or log in at the top right of this page to join the discussion
Re: Cash Circular
It doesn't count if you're on the first floor or have a balcony right underneath you!
However, in all seriousness, we do not advocate jumping out of windows.
So, we've got good news and bad news for you.
Which do you want first? Always the bad of course.
Bad news is that one can have a circula...
It doesn't count if you're on the first floor or have a balcony right underneath you!
However, in all seriousness, we do not advocate jumping out of windows.
So, we've got good news and bad news for you.
Which do you want first? Always the bad of course.
Bad news is that one can have a circular reference for a gazillion reasons, most of them all silly mistakes.
Good news is - based on the information you provided, we can EASILY spot the mistake. If it turns out that you "mistyped" then don't blame our lifetime support!
You mentioned that your interest expense is calc'ed off your Ending balance. This is not correct. It should be off your Average balance. In class, we teach you to toggle between Beginning Balance, which should remove the circ. A circ should only be created when you toggle to Average balance. And of course, this applies to both interest expense and interest income.
So, please try this: calc your interest expense and interest income off of beginning debt and beginning cash balance, respectively. Circ should be gone right away. If not, then your circ problem is due to SOMETHING ELSE in the model. But at least you would have removed this particular faulty circ logic. And obviously, it would be that current year's beginning balance and don't forget to Ctrl+R and propagate across when making corrections.
If this solves your problem, then please save us a drink.
If not, you'll need to tell us more information and you should have another drink. Read More
However, in all seriousness, we do not advocate jumping out of windows.
So, we've got good news and bad news for you.
Which do you want first? Always the bad of course.
Bad news is that one can have a circular reference for a gazillion reasons, most of them all silly mistakes.
Good news is - based on the information you provided, we can EASILY spot the mistake. If it turns out that you "mistyped" then don't blame our lifetime support!
You mentioned that your interest expense is calc'ed off your Ending balance. This is not correct. It should be off your Average balance. In class, we teach you to toggle between Beginning Balance, which should remove the circ. A circ should only be created when you toggle to Average balance. And of course, this applies to both interest expense and interest income.
So, please try this: calc your interest expense and interest income off of beginning debt and beginning cash balance, respectively. Circ should be gone right away. If not, then your circ problem is due to SOMETHING ELSE in the model. But at least you would have removed this particular faulty circ logic. And obviously, it would be that current year's beginning balance and don't forget to Ctrl+R and propagate across when making corrections.
If this solves your problem, then please save us a drink.
If not, you'll need to tell us more information and you should have another drink. Read More
Re: Cash Circular
Many thanks! I will give it a try once this drink (and the few that followed it) have worn off!
Re: Cash Circular
Ok. I tried it with another company. I found a few errors on my part, and implemented your change. Still won't balance. Only off by a few dollars, but close just doesn't work. There is absolutely nothing left for me to check. The core model simply will not balance.The company has no debt, so no inte...
Ok. I tried it with another company. I found a few errors on my part, and implemented your change. Still won't balance. Only off by a few dollars, but close just doesn't work. There is absolutely nothing left for me to check. The core model simply will not balance.The company has no debt, so no interest expense. Interest income is oddly similar to the amount by which my balance sheet is off, yet it appears to be flowing thru the income statement just fine. I'm out of ideas.
Read More
Re: Cash Circular
As an addendum, I have checked all formulas, all signs, and all working capital accounts. Basically, I have checked everything I can check, yet assets still exceed liabilities by an amount equal to interest income.
Re: Cash Circular
Check to see if you have anything on the BS that is referencing interest income. Could be that you "double counted" the interest income somewhere on the BS, on the asset side (since assets > liabilities). Otherwise, check to see that the Cash Flow Statement doesn't also do the same.
Remember the ...
Check to see if you have anything on the BS that is referencing interest income. Could be that you "double counted" the interest income somewhere on the BS, on the asset side (since assets > liabilities). Otherwise, check to see that the Cash Flow Statement doesn't also do the same.
Remember the golden rule: everything on the BS must have a corresponding entry on the CF statement. So if you're not balanced it usually means that you have something moving on the BS that isn't reflected on CF statement. In this case, it sounds the reverse: you might have accidentally included interest income on the CF statement when it's already included in Net Income and thus on the BS via Retained Earnings as well CFO of course. Alternately, you might have accidentally deducted interest income somewhere on the BS in Liabilities or Equity side since your assets exceed liabilities. Read More
Remember the golden rule: everything on the BS must have a corresponding entry on the CF statement. So if you're not balanced it usually means that you have something moving on the BS that isn't reflected on CF statement. In this case, it sounds the reverse: you might have accidentally included interest income on the CF statement when it's already included in Net Income and thus on the BS via Retained Earnings as well CFO of course. Alternately, you might have accidentally deducted interest income somewhere on the BS in Liabilities or Equity side since your assets exceed liabilities. Read More
Re: Cash Circular
Ok, I tried erasing interesting income completely. The idea here was that if it is flowing thru the BS, then the difference would probably disappear. I can redo the SCF and BS, but if it doesn't change anything, well, then I'll just force a balance somewhere.
But, yes, I agree, double counting se...
Ok, I tried erasing interesting income completely. The idea here was that if it is flowing thru the BS, then the difference would probably disappear. I can redo the SCF and BS, but if it doesn't change anything, well, then I'll just force a balance somewhere.
But, yes, I agree, double counting seems to be going on, I have, however, audited the formulas and recalculated them, and it is nowhere to be found. Read More
But, yes, I agree, double counting seems to be going on, I have, however, audited the formulas and recalculated them, and it is nowhere to be found. Read More
Re: Cash Circular
You do not want to force a balance - that would be plugging to balance the model and considered a taboo.
From your other post, it sounds like you got it though.
From your other post, it sounds like you got it though.
Re: Cash Circular
Send us the file. We'll take a look and if we can figure out quickly, we'll let you know.
Email: info@wallst-training.com
Email: info@wallst-training.com
Re: Cash Circular
Sending now. I'll be interested to hear your diagnosis.
Re: Cash Circular
Several observations:
1) you have it set to average balance, so of course there will be circular references. Don't forget the entire explanation at the end of the WMT course about 1 for Beginning and 2 for Average. the GREAT news is that once you flip the switch to 1 for Beginning Balance, the circ...
Several observations:
1) you have it set to average balance, so of course there will be circular references. Don't forget the entire explanation at the end of the WMT course about 1 for Beginning and 2 for Average. the GREAT news is that once you flip the switch to 1 for Beginning Balance, the circs are gone. If you need a refresher on Circular Reference due to average balance or Excel iterations to have Excel help you calculate, please log back into your online account and look for Exhibits course at the bottom of your course list. If you expired out, you can re-sign up for the Exhibits for free. Just go to our website at http://www.wallst-training.com and in the upper right, click on ORDER NOW and just check out at the bottom of the page with just Overview of FM and Exhibits selected (zero cost).
2) your historical balance sheet doesn't balance, most likely because you got lazy and didn't input all significant digits in your balance sheet line items. Please abide by all best practices communicated in our courses, including don't round. Since your 2013 BS doesn't balance, it is guaranteed that your future won't balance. It makes it much harder to audit since it's off due to multiple reasons.
3) please watch out for formatting. you have non-aligned numbers and random bolding on your BS and blue/black (ie Sales on IS) aren't correct. In addition, some of your numbers have zero decimals and others have one decimal and you have random $ where they shouldn't be (ie CFO)
4) For your actual problem of not balancing, here's what we did:
a) Liabilities in 2013 actual are too low by 0.075 so we hard coded that into cell H43 since we need to start clean with a balanced last actual BS. In reality, you will find the reason you don't balance historically and fix it instead of a random cell like we did.
b) Increased number of decimals on our check row so we can see more significant digits.
c) Start going down the BS to see which items change and see if such a change is reflected on CF Statement. Right away we notice the following line items that change but aren't reflected on CF statement:
- LT Investments (row 18)
- A/R, LT (row 19)
- Other Assets (row 21)
d) By inserting these into Working Capital items on CFO in CF Statement, we notice that our check gets closer to zero as we do one at a time.
And then suddenly - VOILA! Magically, you are balanced. Remember the Number ONE reason you don't balance is that you have items on the BS that aren't reflected on CF Statement. Hopefully this is a good reminder to be extremely diligent and super attention to detail when building models.
An addition note: technically, your GW & Intangibles amortization expense from the IS should deduct the GW & Intangibles line and not PPE row. PPE would thus take just Depreciation portion (not including the Amortization, else you double count).
And.. another balanced model. Changing the world by saving lives... WST. Read More
1) you have it set to average balance, so of course there will be circular references. Don't forget the entire explanation at the end of the WMT course about 1 for Beginning and 2 for Average. the GREAT news is that once you flip the switch to 1 for Beginning Balance, the circs are gone. If you need a refresher on Circular Reference due to average balance or Excel iterations to have Excel help you calculate, please log back into your online account and look for Exhibits course at the bottom of your course list. If you expired out, you can re-sign up for the Exhibits for free. Just go to our website at http://www.wallst-training.com and in the upper right, click on ORDER NOW and just check out at the bottom of the page with just Overview of FM and Exhibits selected (zero cost).
2) your historical balance sheet doesn't balance, most likely because you got lazy and didn't input all significant digits in your balance sheet line items. Please abide by all best practices communicated in our courses, including don't round. Since your 2013 BS doesn't balance, it is guaranteed that your future won't balance. It makes it much harder to audit since it's off due to multiple reasons.
3) please watch out for formatting. you have non-aligned numbers and random bolding on your BS and blue/black (ie Sales on IS) aren't correct. In addition, some of your numbers have zero decimals and others have one decimal and you have random $ where they shouldn't be (ie CFO)
4) For your actual problem of not balancing, here's what we did:
a) Liabilities in 2013 actual are too low by 0.075 so we hard coded that into cell H43 since we need to start clean with a balanced last actual BS. In reality, you will find the reason you don't balance historically and fix it instead of a random cell like we did.
b) Increased number of decimals on our check row so we can see more significant digits.
c) Start going down the BS to see which items change and see if such a change is reflected on CF Statement. Right away we notice the following line items that change but aren't reflected on CF statement:
- LT Investments (row 18)
- A/R, LT (row 19)
- Other Assets (row 21)
d) By inserting these into Working Capital items on CFO in CF Statement, we notice that our check gets closer to zero as we do one at a time.
And then suddenly - VOILA! Magically, you are balanced. Remember the Number ONE reason you don't balance is that you have items on the BS that aren't reflected on CF Statement. Hopefully this is a good reminder to be extremely diligent and super attention to detail when building models.
An addition note: technically, your GW & Intangibles amortization expense from the IS should deduct the GW & Intangibles line and not PPE row. PPE would thus take just Depreciation portion (not including the Amortization, else you double count).
And.. another balanced model. Changing the world by saving lives... WST. Read More
Re: Cash Circular
Well, what can I say. Thank you. I did not include the LT items because they were LT, but to is something to consider for the future. Many thanks and, well, I'll make the changes and move along to the rest of the model sans drama.
Thank you once again. I love the company and chucking the model wo...
Well, what can I say. Thank you. I did not include the LT items because they were LT, but to is something to consider for the future. Many thanks and, well, I'll make the changes and move along to the rest of the model sans drama.
Thank you once again. I love the company and chucking the model would have been awful. Read More
Thank you once again. I love the company and chucking the model would have been awful. Read More
I need a step-by-step procedure on how to solve the infinite loop between cash on the balance sheet and on the statement of cash flows before this computer goes thru a window, I go thru a window (I'm only on the 1st floor), or I drop out of this God-forsaken industry altogether. I have worked on this for *days* and cannot stand it anymore. Please reply to this post or send me an e-mail, or perhaps a smoke signal, answering (in detail) my question. I do not care how much of a moron I sound like. I just need *help.*
Now, if you will excuse me, I am going to have a drink. Read More