Reconciling Cash Flow Statement & Balance Sheet in Model
Just a quick question regarding accounting/modeling. I have been learning to model with BIWS and recently tried to model two companies on my own from scratch. The place I'm having problems with is reconciling the current year BS and cash flow statement. For example, my formula for current year changes in accounts receivable in the cash flow statement is last year's A/R - this year's A/R on the BS. The difference between the years isn't equal to the amount reported on the cash flow statement. I know things like acquisitions can cause these discrepancies, but there weren't any. I read through the notes and couldn't find any explanation or adjustment that needs to be made. I'm thinking this is a somewhat basic accounting question, but it's been 4-5 years since I've been in an accounting class, so I'm a bit rusty.
also interested
If it's a small discrepancy and it's a firm operating in more than one country, it could be an adjustment is being made on the balance sheet due to changes in exchange rates. This would not be seen on the cash flow statement. The change would only be seen on the balance sheet and in the statement of "Other Comprehensive Income"
Thanks for responding. The discrepancies are quite large in some cases. Also, the cash flow statement for this company lists effects of foreign currency just before the change in cash at the bottom. This company in particular also has operating activities such as unrealized gains on foreign exchange, provision for doubtful accounts, provision for sales returns, gain on derivatives, and a few other items I'm not quite sure how to handle. But those I manually entered anyway, so they aren't affecting the problems with tying out the current year. Maybe I picked a company with a little too complicated of financials to try to get the hang of modeling.
Hard to reconcile with just the accounts. In your working capital your may have a number of accruals which will change from one year to the other, but these changes are non cash and would not appear on the cash flow statement. Think also of inventory impairment, provisions for bad debt on receivables....
Gains on derivatives should be either on P&L or other comprehensive income, but is non cash (basically if the company hedges interest or currency, or even commodities, its liabilities will change when prices / rates changes; this is all non cash) . It's nearly impossible to reconcile properly the 3 statements historically. You should start from the latest balance sheet and build forecast to learn how to link the three statements. Only forecast the main accounts, and either do very simple or keep the most esoteric lines constant.
What happens when the Cash Flow sheet does not match the balance sheet? (Originally Posted: 01/28/2018)
I'm working on a model in class but my numbers do not jive with the balance sheet on the cash flow sheet. Can anyone help me because I'm drowning.
Hey maxholden, I'm the WSO Monkey Bot and I am sad to say, but this thread is lonely, so thought I'd post in here to try and help out. Some potential topics that might help:
If we're lucky, maybe I can guilt some users to help you out: pepperamy IESHC collier
If those topics were completely useless, don't blame me, blame my programmers...
Check if you reflected the change in your working capital by netting the differences between operating assets and liabilities (cash goes down if current assets increase and cash goes up if operating liabilities increase; vice versa).
Also check you have D&A subtracted from PPE in the BS while CapEx is added back in although it is shown as negative in your cash flow statement
Thanks, I believe I have done all of this correctly but my numbers do not match the change in cash. Net income was $500,600; Net cash from operations is $508,000; Net Cash from investing ($15000); Net Cash from financing $92,600. Change in cash $585,600. Cash at end of year was $619,000 and cash at beginning of year was $100,000. As you can see its out of wack and my professor is on a glacial pace in responding to my questions. Any advice?
Question about getting historical integrated balance sheet and cash flow statements to foot? (Originally Posted: 04/25/2017)
I'm building a model for a publically traded company. There are several items I'm not sure how to project or make foot for the historical years.
First, there are the "Other" items: other intangible assets, other assets, other current liabilities. If the 10-k or 10-q doesn't give much color on those items, how am I to project them? Do I just have them going as a % of some income statement item?
Second, assuming I use the company depreciation method as mentioned in the 10-K, how do I get the depreciation numbers that my historical depreciation schedules spit out to equal the depreciation numbers from the company's historical financials? It seems like it'd be impossible given that the only detail they give you is that "the general ranges of useful lives are: buildings and improvements = 10-40 years, software = 3-10 years, etc, etc. So do I just use some sort of a "plug" for the historical years?
Third, the debt schedule. Same issue as w/ the depreciation schedule. If they don't give me more detail than that "we use the lower of a), b), and c)," then how can I make the interest rate that my historical debt schedule spits out be equal to that shown in their historical financials? Again, do I just use some sort of plug for the historical years? If so, of what nature?
Fourth, pension fund liabilities and accrued payroll.
Fifth, taxes. I want to make my tax calculation align perfectly with the number that shows up in their historical financials, but I can't. Do hedge fund investors and equity research guys who have to build these models without further internal information from the company just accept having to make a bigass plug for like half of the fucking model?
You have to think that each public company, even the smaller companies, have probably 10 F&A people working on their reports. We have probably 50+ contributors to Ks and Qs at my company. So you're not going to be able to tie it all out, especially on taxes.
For HF/ER, well for ER it's all BS (okay, not all, but a lot). There's certainly fundamental analysis that goes into it and they know how to dig into other assets, D&A, debt, etc better than you do, but as we saw with the MS SNAP report, there is often a lot of BS. HF, OTOH, is a different story but they're forming a thesis that's built around the growth (or lack of) story and making an investment based on a lot more than just a 10k.
"5. LOL. Don't even try, especially if it's a multinational corporate and they have a bunch of DTA/DTL. Just look for a trend and if there isn't one then just pick a tax rate that makes sense."
Correct me if I'm wrong (it's been a few years since I've sat in a classroom and I don't use this stuff daily) but would you not ignore deferred tax assets as they should be non-normal in occurrence (or at least offset by valuation allowances) but look to estimate a likely payoff schedule of deferred tax liabilities using the discounted future CFs and/or trends already set forth in the historical data?
Help with financial modeling excel (Cash flows, BS, IS, etc) (Originally Posted: 06/15/2012)
I need some assistance from you guys who are more familiar with this type of work on how to calculate some of the missing numbers. I have attached a copy of the excel sheet I am working on. Everyone's help is greatly appreciated.
I can't read it.. is there a better way of attaching it?
i pmd you
Send it over please and I'll take a peek
Best frgna
pm me
I pmd you both
you should attach the spreadsheet.
I can't seem to figure out how to attach a spreadsheet
pm me if you dont have answer
can anybody help me with Yield to Matutity curve construction using Cubic spline method ?
Cash flow and Balance Sheet - Financial statement model (Originally Posted: 01/03/2013)
Can someone please help me with this question, I would really appreciate it.
When building a financial statement model, the total cash balance calculated on the cash flow statement (cash from operating + financing + investing activities):
Thanks!
http://www.wallstreetoasis.com/forums/cash-flow-question
Cash flow and Balance Sheet - Cash flow statement (Originally Posted: 01/03/2013)
Can someone please help me with this question, I would really appreciate it.
When building a financial statement model, the total cash balance calculated on the cash flow statement (cash from operating + financing + investing activities):
Thanks!
The cash balance at the bottom (which is the difference in cash balances) must equal #1 - difference in cash balances on BS between the two periods.
Does that make sense?
e.g.
Cash Balances 2011: 20 2012: 40
CFS: CFO, 10 + CFI, 5 + CFF, 5 = 20 net cash flow --> 20 + prior 2011 balance = 40
yes thanks everyone i got it. #1 was correct
question about cash flow integration to balance sheet (Originally Posted: 05/13/2015)
Hi people,
I'd like some help with a question:
I'm analyzing a company's cash flow statement and see that they have a negative change in cash that is larger than the beginning cash balance, so the ending cash balance is a negative number.
That number is linked to the cash amount on my balance sheet projection. I know I'm doing something wrong or missing a step, but I'm using a new model sheet that I just put together and so I'm at a loss.
This is what it looks like right now: (the numbers are from CapIQ)
Total Cash From Financing Activities $(57,705)
Beginning Cash Balance 10,598 Change in Cash (42,930) Ending Cash Balance (32,332) Average Cash Balance (10,867)
Thanks for any input/feedback!
You need to incorporate a cash sweep to your revolver. So instead of showing negative cash, you would have the revolving line increase by that amount which would then result in a $0 cash balance.
Minor point to add; in practice, you would typically set a minimum cash balance value that would, in your case, make your draw down greater than the (32,332). Goes without saying, you could add more bells and whistles to your debt model and make the setup more realistic.
Let me know if you have any more questions.
modeling... when CF & BS sheet items don't match (Originally Posted: 02/10/2015)
first time poster here.
i get it that modeling is more art than science... but any tips on what to do when historical CF & BS items don't match up?
for example, i am looking at a company where on the BS 2012 A/R = 249.3 and 2013 A/R = 261.8. As i see it, that should be a 12.5 use of cash... however, on the CF statement, AR for 2013 is shown as a 55.1M use of cash.
The same company has BS line item of 2012 AP 45.3 and 2013 of 61.9, which should be 16.6 retained cash, yet on the CF statement last year's AP is recorded as 6.4.
any thoughts?
This is a repost I saw a few weeks back from @"Extelleron" who I thought gave a solid response:
There are reasons that change in A/R in CFO would not match the change on the balance sheet. For example, if a company acquires another, the A/R accounts would be merged but the change in A/R resulting from the acquisition would not be embedded in cash flow from operations. Another possible reason could be FX adjustments
Generally for complicated companies it's likely you are going to have many issues recreating the SCF from the balance sheet. You'll run into problems as a result of a) acquisitions, particularly non-cash acquisitions, which may not have any effect on the SCF, b) non-cash investing/financing activities [i.e., buy a building by borrowing directly from the seller], c) FCTAs [fx adjustments], and d) other OCI adjustments [for example, you'd have trouble reconciling the securities account if an unrealized loss had occurred].
thanks whale - makes alot of sense. any thoughts on how to proceed? i am planning on just building my cash flow statement off my model driven assumptions w/o making adjustments to more closely match future CF items with historicals that are distorted by all the items you listed.
Yes, that's fine. The past is the past and doesn't affect the predictive aspect of your model as long as you start with the correct/current balance sheet. Moreover, you should just model the company as you typically would and not worry about making FX adjustments to balance sheet accounts, etc... in the future (unless you have absolute BS/CF granularity at the country level...which I am sure you do not).
as you said, difficult to explain on a forum - but are you saying that you essentially just say something like CFO is X% of net income and skip right to the CFO line w/o worrying about the individual line items?
or now that i re-read what you wrote it sounds more like you find the net impact on the B/S and just consolidate the CFO line items into one number that captures the net B/S change?
@"LIBuck27" Your second interpretation is correct.
Ending Balance A/R - Beginning Balance A/R on the Balance sheet = Net Change in Accounts Receivable on the statement of cash flow. This will show up in the working capital section and once you add it to all of the other items in that section you will have a net CFO number.
Note: I am assuming you are making correct assumptions in forecasting your Ending A/R balance on the balance sheet.
Also, I just re-read your original question - and I suggest that you not attempt to forecast the CF itself. Instead create and Income Statement and Balance Sheet, the statement of cash flow will be the plug to get your from beginning to ending balances.
Debt doesn't match on BS and CF (Originally Posted: 03/12/2017)
I have a model due soon and I'm new to finance. Please help me understand why the BS debt balances don't match the changes in the CF statement for the corresponding balances. Here is the 10-K in question: https://www.sec.gov/Archives/edgar/data/21344/000002134416000050/a20151…
It belongs to Coca Cola.
On the balance sheet the 3 main items are
Loans and notes payable (comprised of commercial paper and short term debt), Current maturities of long terms debt, and Long term debt.
The differences in the accounts from 2014 to 2015 are ($6,001) , ($875), and $9,344 respectively. The sum of the change gives me $2468
versus the cash flow statement which says Issuance of debt is $40,434 and payment of debt is ($37,738). That nets out to $2,696.
There is a discrepancy of $228. I looked through the other line items and couldn't find debt buried anywhere else. Can someone please explain?
Greatly appreciated
Didn't take a look at the SEC filing you linked. But, the difference is probably just due to the financing costs. It will affect the balance sheet amount per FASB.
Thanks so much, but still can you elaborate? I'm sorry, just learning as I do this.
what is FASB?
Where can I find the financing cost? It's been bugging me for 2 days.
Go to Note 10 and figure out the true principal value of the debt (aka, the real amount the company has to repay). What's on the balance sheet is a carrying value net of unamortized discounts from issuing the debt (OID) and other fair value adjustments. You probably won't be able to tie directly back to the CFS though since you won't know the discount accretion schedule.
So for the debt schedule the total debt issused in 2015 is $40,434 is comprised of $25,923 in short term debt issuance and the rest as Long term debt issuance. The paydown is $37,738 with $31,777 for short term and the rest long term. For sake of simplicity lets say they continue this going forward at the same pace. Would that mean to project the balance I can just take the ending balance of last year (which includes the OID) and add the issuance and paydowns? is that ok?
Cupiditate velit ratione error ad eum non voluptatem. Odio nemo ut et voluptate voluptatibus. Non est qui voluptas magnam qui nemo. Atque ut cupiditate qui alias vel.
Ipsam dolor aliquid hic sit. Veniam iusto incidunt enim vel molestiae eum magnam unde. Aliquam assumenda quo est et dignissimos et voluptatem. Repudiandae molestias vero voluptatem aliquid voluptatum unde vel itaque.
Quia nam esse doloremque sunt rerum. Et harum earum quasi et. Vitae ab qui dolor autem sunt. Quae deleniti nesciunt reprehenderit quasi qui rerum vel. Enim quibusdam dolor est beatae.
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...
Similique minima id ipsa ut repudiandae. Et modi aut voluptates. Dolorum ducimus mollitia dolor quos qui sequi at voluptatibus. Non voluptates ducimus molestiae velit laborum exercitationem.
Et in rerum vel magni velit non. Quas saepe excepturi vero commodi. Nostrum eum minima sequi doloribus repudiandae. Ipsa eum adipisci aut id id. Impedit et explicabo ipsum. Dolore fuga distinctio qui ut aperiam dolores.
Ullam eligendi hic non impedit non tenetur ut. In exercitationem velit suscipit id tenetur exercitationem sit illum. Officia illum ea ducimus suscipit voluptates minima. Laudantium corrupti sunt nisi maxime maiores saepe eum blanditiis. Est voluptatem porro animi quaerat nemo voluptas voluptas. Eos consequatur nobis rem nemo est consequatur dolore. A placeat repellat ab aut quis quia sit.
Repellendus mollitia velit sit eaque unde nisi. Dolore fugiat labore aliquam sint dolorem fugiat. Architecto aut tempora repellendus nobis delectus ipsa odit.
Alias assumenda labore distinctio enim veritatis facilis. Ex quidem ut aut ut molestias. Est ab quae quo iure ex. Enim eum mollitia et aut velit placeat et.
Quibusdam praesentium sit dolores cupiditate consequuntur. Perspiciatis error eveniet laborum deserunt numquam. Id optio qui enim adipisci ut consequatur rerum. Et qui voluptatibus et et.