Welcome back to the Wall Street Prep Quick Lesson Series!
In this video, we’ll build a cash flow statement given an income statement and balance sheet in Excel. The accounting here is a simplified presentation of how the three major financial statements are inter-related and lays the foundation of financial statement models in investment banking. Many accounting questions that we see time and again in finance interviews are designed to test the understanding explained in this exercise.
Ready to Model? Before diving in to the videos below, be sure to download this lesson’s Excel model template: Cash Flow Statement template.
The Cash Flow Statement, Video 1
The Cash Flow Statement, Video 2
Comments
Why is “Additional purchases of Intangible Assets” excluded from the CapEx in the CF?
Dylan:
Capital expenditures strictly relates to purchases of Property, Plant & Equipment, not Intangible Assets.
Best,
Jeff
Jeff,
Purchase of Intangible asset can be of Capital Expenditure Nature. Most companies invest in Patent, Knowhow and licenses which are written off over period of time and required huge capital for purchase.
Hi, Gaurav, You are correct that regular purchases of intangibles are conceptually similar to capital expenditures. Jeff’s point is that we are using the capex line item here to only mean additions to PP&E. As long as we include purchases of intangibles in CFI under the next line item, or… Read more »
To calculate CapEx to arrive at cash from investing, we subtracted depreciation. But we have already subtracted D&A when we calculated cash from operations. Does that mean that we’re we subtracting depreciation twice (i.e. double counting)? Same question regarding amortization of intangibles when calculating increases in other L/T assets. Thanks!… Read more »
Farouk: We didn’t subtract D&A when calculating cash from operations… it is an add back to net income. The reason why we have to account for it again to figure out the capex is that the PP&E balance will decrease by depreciation… so any increase in PP&E is due to… Read more »
Why notes payables are not taken in calculating CFO in Video 1. Notes payable are also current liabilities. What all Current assets and current liabilities should be taken and what not to be considered while calculating Cash flow from operation.
Gopal
Notes payable are interest-bearing and should be included in cash flows from financing. Working capital items are not considered interest-bearing and evolve over the ordinary course of business so those cash flows are considered operating cash flows.
Best,
Jeff
A very clear and interesting presentation. Thanks!
Thank you for the presentation. Please why is dividend payment coming after the calculation of Net Income?
Job:
Dividends are not considered a company expense so they are not deducted to arrive at net income.
Best,
Jeff
Thank you for the prompt response. I am indeed impressed and grateful.
I understood that if we have an increase in the accounts receivable, means that is a cash that you don’t have recognized as revenue. But this doesn’t mean that the clients are paying later, if we say that we did the DSO and the average payment still the same from… Read more »
Thais:
I’m not 100% sure I understand the question but the client better eventually pay! When they pay cash, of course that would be recognized in the cash flow statement (A/R would decline).
Best,
Jeff
Thanks Jeff for the reply!
But what I am saying is that if the clients are still paying regularly, because the DSO still the same,but we have an increase in the AR due to the fact that the sales are rising. Why should that still impacts the cash flow?
Thais:
If A/R is growing, the revenue the company recognized in net income has not yet been collected as cash (as of a certain period), hence the negative adjustment to net income on the cash flow statement.
Best,
Jeff
Thank you so much for taking the time to create these. Super helpful for me as a senior operator.
You’re very welcome, Jay!
Thank you for the lesson. Quite insightful. I wanted to see an example that has Investments in Subsidiaries and affiliate companies as assets on the balance sheet. I want to know what could make such investment drop or increase
Hi, Lynda,
Investments in affiliates increased by affiliate income and decreased by affiliate dividends. We cover this in more detail in our advanced accounting course.
BB
How does an increase in depreciation increase PP&E? In the video they mention that the increase in PP&E may be attributed to an increase in depreciation, but wouldn’t an increase in depreciation decrease the value of the company’s fixed assets (PP&E)?
Thanks in advance for any help!
Hi, Juan, Matan actually says that depreciation makes PP&E go down (as you know), but when we see an increase in PP&E, before we can calculate how much capex the company spent, we have to know how much that ‘increase’ was affected by depreciation; the more depreciation there was, the… Read more »
Makes sense, thanks!
You’re welcome, Juan!
Not that it changes the lesson being taught here but the 2013 balance sheet doesn’t balance in this example and it’s important that the balance sheet balances in real life. Net assets (assets minus liabilities) should equal equity and it doesn’t.
Hi, Mike, You are absolutely correct that it is required for the B/S to balance. At the end of second video, when the CFS is complete and linked back to the B/S, it does indeed balance at $1074.10 for both assets and liabilities plus equity, which is a sign that… Read more »
On an unrelated note….How did you get the little number 1 and number 2 above and to the side of the words ‘Amortization’ and ‘Assume’?
Val:
Format Cells (Ctrl 1)->Font->Superscript.
Best,
Jeff
Thanks Jeff. I’ll give it a try…
When I compare my results from compiling the CF statement from the Balance Sheet and IS to the CF shown by the firm I get different numbers. Why might that be?
Ivan:
Unfortunately, there is no way for us to tell. I would just re-review the video lessons and compare all of your calculations and numbers to the videos.
Best,
Jeff
Great Explanation! Well simplified. I have 2 questions:- (1) Is your treatment of Deferred Taxes and Goodwill based on IFRS or US GAPP. I’m asking because I am more acquainted with IFRS. (2) Is it always presumed that Investment outflows are made in cash? If an investment is acquired on… Read more »
Val: 1. This example doesn’t really specify, as it’s more of a general lesson, but probably US GAAP. IFRS and US GAAP should be fairly aligned on these two topics. 2. No, not always. If an investment is purchased on credit then that would not impact the cash flow statement… Read more »
Ok. Cool. I did a bit of research and realised that the example is indeed aligned to US GAAP because under IFRS Deferred Tax is either a non-current asset or liability; it’s only in GAAP that you show Deferred Tax as both current and non-current. Hence your example shows the… Read more »
Val:
Yes, that is true with regards to deferred taxes in US GAAP. Having said that, shouldn’t deferred taxes (regardless of current or non-current) typically be reflected as operating activities?
Best,
Jeff
I’m uncertain. There seems to be a difference between IFRS and GAAP here
The IFRS format example I referenced only adjusted for tax charge and taxes paid in the operating activities section. It didn’t use the description ‘Deferred taxes’. Is there more to it?
Val:
It’s probably just different terminology but the same concepts.
Best,
Jeff
Cool.
Best,
Val
Regarding the decision to put “other long term assets” in investing — is this somewhat subjective, or are other Long Term Assets always (or 95%+ of the time) put into investing activities?
It’s somewhat subjective. It’s most likely an investing activity but not always (see Apple’s cash flow statement… they include other non-current/long-term assets in cash from operating activities).
Best,
Jeff
That was great!!! Thank you.
Hi, Where would you place loan interest outflow? I assume that loan interest liability is part of notes payable position and as such is part of financing cash outflow. Would it be correct to first adjust operating cf for interest expense and than show actual interest paid under financing cf?… Read more »
Marko:
1. No, interest expense is considered an operating activity under US GAAP (IFRS is more flexible however).
2. These are considered operating activities under US GAAP.
3. Yes, that is correct.
Best,
Jeff
Jeff, Thank you for quick response. I have one more question regarding fx fain/loss treatment in CF. For example a company from EU has a loan denominated in dollars. EUR depreciates and loan liability in eur increases whereas fx loss is debited in P&L. Increase in loan liability is not… Read more »
Marko:
This is beyond the scope of our quick lesson but here is a link that should help: https://www.gaapdynamics.com/insights/blog/2015/09/15/plug-the-flow-accounting-for-foreign-currency-cash-flows/.
Best,
Jeff
Thank you Jeff.
Best Regards.
Marko:
You too… stay safe!
Best,
Jeff
Thank you
Nathan:
You’re welcome!
Best,
Jeff
Hi, why is goodwill (intangible asset) incorporated in the cashflow? Is it because the increase is due to cash out?
Tariq:
Yes, goodwill and intangibles increase when they are purchased, which is a cash outflow.
Best,
Jeff
Useful tutorials
Abraham:
Thanks!
Cheers,
Jeff
On the first CF video at around 9:51 mark, the instructor says that “if inventory went up, I must have paid cash for it somehow”. But couldn’t you have bought the inventory on account? And hence there would still be the increase in inventories on the balance sheet but the… Read more »
Andrew: Technically yes you are correct. However, accounts payable would also go up, which is a “source” of cash so there would be no bottom-line change to cash from operations. We are just using Inventory increasing as an example of a cash outflow to simply explain why increases to assets… Read more »
What a great course! I learned something very useful. Thank you!
Cindy:
Great to hear!
Cheers,
Jeff
I thought since Depreciation has been added back at the cash from operation section it doesn’t need to be added to the cash from investing. How’s this so?
Chibueze:
Where do you see we are adding back depreciation to cash from investing? We only use depreciation to calculate capital expenditures (since you are given 2 years of PP&E), which is an investing activity.
Best,
Jeff
Really enjoyed working through this! I plan on taking the boot camp so wanted to go through the videos as well. I love that it’s explained in such simply layman terms!
Awesome – great to hear – best of luck!
It was awesome, never learned preparing cashflow with reason as why inflow and outflow.
I was hardly able to tally the statements before. After this I don’t think I will make mistake.
Owesome.
Good way to learn cash flow statment.
i am satisfied.
Very useful lesson. Looking forward to future lessons.
This was definitely in layman’s term and I enjoyed it. The explanation was simple, clear and concise.
This was a great and concise explanation!
The template link, is not working. Any way of getting a new link for the cash flow template?
Trent,
Hope all is well – can you please reach out to [email protected] to report this issue? It works on my end – thanks!
Best,
Haseeb
Well presented in short way.
Hi, I am trying to figure why in the template, you didn’t have any separate row under the income statement for any other income /(expense) which might have been earned (incurred) not as part of the operating income but separately. For. eg., if we need to include “Loss on early… Read more »
We typically include them in “other expenses” below Operating Income b/c items such as the ones you mentioned above are typically considered “non-operating” expenses. Hope this helps!
I can’t wait to get all the video series and put it all to work on my first important deal. It will make me look like an expert once I come into my future client’s door.
Best Wishes
Thanks!
Professionally formulated and well explained.