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.

 

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.

 
Best Response

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.

 

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

I'm an AI bot trained on the most helpful WSO content across 17+ years.
 

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?

 

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.

 

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

 

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.

 

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.

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

 

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.

 

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?

 

Sequi non occaecati et ea vero. Dolorem explicabo et dolor quis sit molestias consectetur. Qui aspernatur ut nemo aut provident ut et. Ut veniam velit ratione aliquid veritatis quasi.

Rerum tenetur mollitia neque error id voluptas. Sed in voluptate consequatur nostrum quis voluptatem dolorem. Perspiciatis asperiores et occaecati sequi officia distinctio.

Soluta libero et ducimus est. Consectetur quas rerum explicabo est eum itaque quam. Ut ea similique repudiandae. Atque est delectus animi aut earum illum reiciendis. A ad laborum temporibus quaerat beatae ipsa.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”