Creating Cash Flow Statement by Indirect Method – I
If there was a challenge in any Finance 101 course at any B School, I think Creating the Cash Flow Statement would score the max. When I was pursuing my MBA, the biggest enigma for me was to go from the Balance Sheet and Income Statement to the Cash Flow statement via the indirect route. I could never get it right!
There were two challenges, the bigger was obviously unclear concepts plus I didn’t know how to play with Excel! I have worked hard on both and let me show you, how they can be used to get the right cash flow statement in no time!
Actually the concept is simple and excel makes it run on steroids!
The Basic Concept first!
The concept is simple – The basic accounting Equation:
Assets = Liabilities
If you take a look at any balance sheet, it balances (that is where it derives its name from!) and in that balance sheet, this accounting equation would be satisfied.
Derivation from the Basic Concept
If my Balance sheet Balances for two successive years (Lets say, FY 11 and FY 12), then
Assets FY 11 = Liabilities FY 11
Assets FY 12 = Liabilities FY 12
Assets include Cash Assets and all other assets, so we can say –
If I subtract the two, I can say that
[Cash FY 12 – Cash FY 11 ] + [Other Assets FY 12 – Other Assets FY 11 ] = [Liabilities FY 12 – Liabilities FY 11]
Or we can also say
[Cash FY 12 – Cash FY 11 ] = [Liabilities FY 12 – Liabilities FY 11] – [Other Assets FY 12 – Other Assets FY 11 ]
Or the additional cash generated is basically additions in liabilities less addition in assets. This is also quite intuitive as liabilities release cash and assets suck cash!
Using this basic derivation to generate cash flow statement
If you understand the basic concept, I suggest you tighten your belts, and get ready to generate the cash flow statement in a moment:
- Take two years balance sheet
- Ensure that you have the correct numbers in Excel: Build checks, Assets = Liabilities
- Categorize the elements of balance sheet in Operating, Investing and Financing Activities. Remember simple rules, while categorizing:
- You would find the Financing Activities on the liabilities side (Typically Debt and Equity)
- Anything “current” in nature would typically be Operating!
- Investing mostly would be on the assets side of the business
- Find the differences in liabilities and assets
- Remember: Increase in Liabilities increases your cash and increase in assets decreases it
- Take the differences of all assets and liabilities and exclude “Cash” (which is part of your current asset.
- This remainder should define your cash generated
- Try to match some of the differences from P&L
- Difference in retained earnings should be derivable from Profit After Tax (after adjusting for dividends)
- Difference in Accumulated Balance Sheet should be depreciation
- And so on…
Let’s Generate the Cash Flow Statement for Facebook
Facebooking and its IPO is the hot news these days! It recently filed its S1 filing with the regulator (SEC). Facebook is generating (and burning) a lot of cash as well. So let’s try to see how we can create the cash flow statement for Facebook.
- You can download the S1 filing for Facebook from here –
- And input the historical P&L and Balance Sheet
- Run the steps that I have indicated above
- I am sure if you are excel pro, you can do it in a flash (Even if you are not, it should not take long!)
Templates to download
I have created the template for the P&L and Balance Sheet for Facebook for you! You can download the same from here. I recommend that you try to create the cash flow statement by implementing the steps that I showed above.
In the next tutorial, I would show you how the steps can be implemented.
For any queries regarding the cash impact or financial modelling, feel free to put the comments in the blog or write an email to email@example.com
We would be launching a new module in the financial modelling course, which would cover video tutorials on creating the financial modelling course.
The module would have the following:
- 3 hours video sessions on creating the cash flow statement
- 10 step by step templates on creating the cash flow statements
- Coverage of companies across sectors and countries (Apple, Alibaba, Volkswagon, Google, etc.)
- Presentation and notes covering the concepts
The cost of the module on creating the cash flow statement is USD 75. But we are offering it FREE for now. You can enroll for the combination of financial modelling and cash flow modelling course at the old prices till 25th of April, 2012. If you are planning to be a pro in financial modelling, I suggest that you don’t waste any time to enroll for the course
PS: We would be giving access to the new module to all our old students
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Consolidate data from different excel files (VBA)||Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps. »|