Reconciling Cash Flow Statement & Balance Sheet in Model

RE Dev's picture
Rank: Orangutan | 266

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.

Comments (50)

Feb 21, 2014

also interested

Feb 21, 2014

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"

Feb 21, 2014

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.

Feb 22, 2014

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.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Feb 22, 2014

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:

  • Matching Historical Balance Sheet and Cash Flow Statement Items Doing a practice valuation, currently trying to match historical balance sheet items to cash flow ... statement items. I've encountered a problem where, historically, changes in the balance sheet are not ... matched by their equivalent items on the cash flow statement- <
  • EBITDA vs. Operating Cash Flow vs. Free Cash Flow from acquisitions. What are Operating Cashflows? Cash Flow from Operations (CFO/OCF) CF from Ops = Net ... Non Cash Working Capital + Non-Cash Charges Why do we need Cash Flow from Operations when we already ... the Cash Flow Statement. Compare Apple's Net Income to their Cash Fl
  • Free Cash Flow to Firm vs. Free Cash Flow to Equity Growth Rates get paid last in the event of bankruptcy. How to discount levered and unlevered free cash flow? When ... the capital structure. When performing a discounted cash flow analysis on levered free cash flow, you ... someon explain why? What is Free Cash Flow? Free cash
  • numbers on cash flow statement dont match? the increase in inventory, increase in AR / AP i got from balance sheet dont match with the ... numbers on the company's cash flow statement?? what are the reasons? and which one should I use (im ... trying to use the indirect method to get the cash flow
  • Cash flow and Balance Sheet- Cash flow statement + financing + investing activities): 1. Must equal cash flow balances inputted on the balance sheet 2. Will ... always exceed cash flow balances inputted on the balance sheet by cash from operating activities 3. Will ... always trail cash flow
  • Question about getting historical integrated balance sheet and cash flow statements to foot? 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 ...
  • Include change in working capital when calculating excess cash flow for cash sweep? capital changes is not removed. I tried building the cash sweep in, but my balance sheet went all off ... sweeps. I have seen examples of when excess cash flow is calculated as follows: EBITDA Less: Cash interest ... Less: Cash taxes Less: Capex Less: Mandatory paydown Excess cash flow for
  • More suggestions...

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...

Feb 22, 2014

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

Feb 22, 2014

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?

Feb 22, 2014

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.

  1. Other items -> straightline it if it doesn't fluctuate, if it does then there should be some info on what it is and you should have a better idea about how to project it.
  2. Good luck. You won't be able to tie this out because it's going to change every year. You can ballpark the useful life for all fixed assets by dividing net PP&E + D / D and that should give you a proxy for useful life. But again, if the company makes any large asset purchase or asset sale/writeoff then it's going to get pretty wonky.
  3. This one you should be able to do by just using some math. It won't be exact but if you don't pay down any principal in a given year then your wtd avg rate would be interest paid / average principal. You can probably play with this and find the rate if there is paydown.
  4. Should be small, just straightline it.
  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.

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.

Feb 22, 2014

"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?

Feb 22, 2014

I can't read it.. is there a better way of attaching it?

Feb 22, 2014

i pmd you

Feb 22, 2014

Send it over please and I'll take a peek

Best
frgna

if you like it then you shoulda put a banana on it

Feb 22, 2014

pm me

Feb 22, 2014

I pmd you both

Feb 22, 2014

you should attach the spreadsheet.

Feb 22, 2014

I can't seem to figure out how to attach a spreadsheet

Feb 22, 2014

pm me if you dont have answer

Feb 22, 2014

can anybody help me with Yield to Matutity curve construction using Cubic spline method ?

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Feb 22, 2014

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

Feb 22, 2014

yes thanks everyone i got it. #1 was correct

Feb 22, 2014

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.

Feb 22, 2014

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.

Feb 22, 2014

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].

Feb 22, 2014

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.

    • 1
Feb 22, 2014
LIBuck27:

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).

Feb 22, 2014

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?

Feb 22, 2014

@"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.

Feb 22, 2014

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.

Feb 22, 2014

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.

Feb 22, 2014

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.

Feb 22, 2014

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?

Feb 22, 2014
Comment
    • 1
Feb 22, 2014