Menu
Creating Cash Flow Statement by Indirect Method – I
Financial Modeling - 31 comments
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 paramdeep@edupristine.com
Announcement
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
Hello Awesome...
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.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Consolidate data from different excel files (VBA) | Formula Forensics 018. Retrieving the Nth number from a Range which has Gaps. » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
31 Responses to “Creating Cash Flow Statement by Indirect Method – I”
I really enjoyed going through the 'Creating Cash Flow Statement by Indirect Method '. After downloading the P&L and Balance Sheet for Facebook I realized that it would have been more useful to the user if you had also used screenshots of your templates.
Thanks!
@Dinesh: Thanks. In the next tutorial on the series, I would be showing the step by step creation of cash flow statement using screen shots of the template. I hope you would enjoy it!
I love getting your emails for updates and new tricks in Excel. 🙂 However, on this post, the accounting formula is incorrect. The formula should be Assets = Liabilty + Owner's Equity.
Agreed with Nicole...at least with regards to US GAAP. Maybe it's different in India???
Also agree with Chandoo...the cash flow statement is one of the most difficult concepts to master in accounting. I do audit work, and I see CPAs who still can't do a cash flow statement very well. I actually think the indirect method is less difficult than the direct method in some ways. Either way, it's a difficult concept. Thanks for the tutorial, Chandoo!
Thanks John & Nicole for the love. Please note that you should direct it at Paramdeep, our Financial Modeling specialist as he wrote this. 🙂
If I remember my ManAc1 course correctly (that is the dreadful accounting course I did in first year of my MBA), Assets = Liabilities. According to Indian GAAP, we include Owner's equity in liabilities.
But both definitions hold good 🙂
In Indian GAAP as well, Assets = Liabilities + Owner's Equity (which is inclusive of shareholder capital and retained earnings)
I had simply written Assets = Liabilities and in my case the liabilities included outside liabilities (Debt) and own liabilities (Equity). Still the analysis would remain the same. 🙂
I had not included Owner's Equity as a separate item in the equation, since it was making the equation longer and at the same point of time, the steps of analysis were not changing.
Thanks Paramdeep.. That explains why my accounting grades sucked in 2004 and why they are no better in 2012 🙂
Chandoo,
Perhaps my algebra is wrong, I haven't used it in a while, but when you said subtract the two equations, you wrote:
[Cash FY 12 - Cash FY 11 ] + [Other Assets FY 12 – Other Assets FY 11 ] = [Liabilities FY 12 + Liabilities FY 12 ]
Shouldn't the right side of the equals sign be [Liabilities FY 12 - Liabilities FY 11 ] ?
There should be a subtraction, not addition, and the second Liabilities should be FY 11, right?
I could be totally wrong here, I am not winning any math contests with what I know...
@Jay.. you are right. It was a typo. Thank you so much for pointing it out. I have fixed it now.
@Jay: Thanks for pointing out.
@Chandoo: Thanks for updating!
I use the excel template for my cash flow projections. Cash flow projections are an important tool that all businesses should use. I have detailed information on why this is so important at http://www.smartbusinesscashflow.com.
When is the Financial Modeling course?
Can you provide details on price and topics covered?
Thanks!
@Paul: You can get the details from: http://chandoo.org/wp/financial-modeling/
I am really enjoy this post, it makes complex CF statement easy for accounting staff like us. Looking forward for your next post/course..
Thank you so much.
Thanks Kiev. I think you should see the next post in the coming week. Have a nice weekend ahead!
HI Chandoo & paramdeep,
If I joined Financial Modeling Coursed & Project Finance Class with excel school today , shall I get this cash flow statement moduel training get free.
@Anand... yes. You will be able to access the Cashflow module once we upload it. Please visit http://chandoo.org/wp/financial-modeling/ to join us.
Thanks Chandoo
I will Join today evening from my home.
HI
Chandoo
Chandoo
Thanks for sharing links. It is nice learning experience Excel school and Financial Modeling. You and Paramdeep don a great Job.
For many years as a CFO in a small public hospital in Australia we used Excel as a presentation document for our accounts (Have yet to come across Accounting software that handles this very well). One therefore had a balance sheet showing this years result and a comparative for the previous year. Add a few formulas (in a non-output area) to find the differences between the 2 years and hey presto! sum the differences (with the right signs) and you can generate a cashflow which ALWAYS adds up. In Australia under IFRS need to further split into Operating, Investing & Financing but not an issue. The big beauty of all this being that any subsequent adjustments to the your P&L/BS immediately generated a revised Cashflow. One of the best uses of Excel ever (IMO)!
@K Mac: Thanks for sharing your experience. In the next post, I will give pointers to categorizing the cash flows in the next post. I will look forward to your comments on the methodology in the next post!
Hey Chandoo,
How do I sign up for the free course? This is amazing. Also , do you offer courses that deal on new projectsinvestments such as calculating NPV or cash flows for new technology initiatives (SAP , BI etc)? I plan to take it over summer.
Regards,
Krishna
@krishna: The Cash Flow statement module is free for our financial modeling students. If you had registered earlier, we would send you the link to download the material.
We do offer a course on Excel for Project Managers that includes a module on bottom's up planning, where you can estimate the cash flow for an IT project and calculate the NPV, IRR, etc. You can get the details on: http://chandoo.org/wp/training-programs/excel-for-project-managers/
@Paramdeep , thanks a lot. Is this specifically for IT investments only or is this for any investment based projects (Like setting up a factory etc)?
Regards.
Krishna
@Krishna: The concepts are generic in nature! The first case study is based on IT. Then another one is based on manufacturing!
Well this is something I could use. I'm a complete newbie to excel but this should get me started as I'm dealing with cash flow myself. Will certainly be coming back for more tips.
Best,
Earl
http://www.beachcandynow.com
[...] Second, you can create your own invoice. This involves looking for invoice templates, possibly downloading them, and customizing them to your liking. You can also check out my post here about creating an invoice. Also, here’s a very good in-depth article on creating invoices through excel. [...]
Your approach is right because it always help to balance the closing balance of cash in the CF statement to the closing balance in the B/S statement (as long as the two year- B/S balance). But the C/F statement does't exactly represent the cash inflow or outflow in some case. For example when the shareholders raise paid in capital by a fixed asset, the fixed assets account and the shareholder equity increase the same amount, so the Investment C/F and the Financial C/F increase but it is not the actual cash inflow. How to adjust in such case? Do you have any idea?
Hey chandoo,
Can anyone help me answer my question posted on November 30, 2012 please?
Hello
I am looking for a template to generate 5 years P&L,Balance sheet and cash flow to apply for funding for a new project in India
Can you help.please.
Regards
Vijayan
Thanks for this amazing and intriguingblog, it addded vakue to my colleagues as I keep sharing your blogs. Loved the write up.
Also, I write on a similar niche, could please take out some time to share your views on it here: Cash Flow Formulas