fbpx
Search
Close this search box.

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.

Assets_vs_Liabilities

 

 

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 –

Asset_Liabilities_FY11_FY12

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

Chandoo

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:

Written by paramdeep@gmail.com
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

31 Responses to “Creating Cash Flow Statement by Indirect Method – I”

  1. Dinesh says:

    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!

  2. Nicole says:

    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.

    • John O says:

      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!

      • Chandoo says:

        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.

  3. jay maby says:

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

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

  5. Paul says:

    When is the Financial Modeling course?
    Can you provide details on price and topics covered?
    Thanks!

  6. Kiev says:

    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.

  7. ANAND says:

    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.

  8. K Mac says:

    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!

  9. Krishna says:

    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

  10. Earl says:

    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

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

  12. Thuy Nguyen says:

    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?

  13. Thuy Nguyen says:

    Hey chandoo,
    Can anyone help me answer my question posted on November 30, 2012 please?
     

  14. Vijayan says:

    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

  15. Gena Sanford says:

    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

Leave a Reply


« »