Creating Cash Flow Statement by Indirect Method – II
So what’s the cash with Facebook? Last time, we announced the launch of a new module on getting the cash flow statement in our financial modelling course and also discussed the procedure to create the cash flow statement. I had shared with you the template for financial statements for Facebook. I had asked you to implement the steps and create the cash flow model for Facebook!
We know that the devil is in the details. Last time we discussed the process and in this post, we would implement the cash flow statement for Facebook using the indirect method.
To get the cash flow statement, we simply needed to
- 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
Step I: Taking the Balance Sheet and ensuring that the Assets = Liabilities
We had got the balance sheet last time and ensured that the Assets and liabilities are equal. This is a simple step – You only need to make sure that you can copy the right numbers from the S1 filing! You can get this template from the last post
Step 2: Categorizing the items in Operating (o), Investing (i) and financing (f)
Again, I told you the broad rules – Find the Financing Activities on the liabilities side (Typically Debt and Equity), Anything “current” in nature is Operating and Investing mostly would be on the assets side of the business.
You can get the complete template of categorization from here.
Step 3: We take the differences in Balance sheet items
The main aspect to remember is that we do not take cash (as that is what we are trying to find!) and increase in assets imply cash reduction and increase in liabilities imply cash increase!
Step 4: Ensure that the differences balance (Except Cash)
Ensure that the difference in Cash is same as the difference in Liabilities and Assets in the 2 years
You can get the complete template of differences from here.
Step 5: Final Adjustments to get the cash flow statement
Now that we have all the numbers, we can link to the P&L and get the final numbers
You can get the complete template of Cash Flow Statement from here.
So where does Excel help you in Finance?
I told you that Excel can help you get the cash flow statement in the matter of a few minutes! If you had worked as a banker in old times, you would realize that this is really the process on steroids! It is quick and simple!
If you have been utilizing excel to complete your finance tasks, why don’t you share it with us? Use the blogs and share!
Templates to download
I recommend that you try to create the cash flow statement by implementing the steps that I showed above. The excel sheets are available for your kind reference:
- Income Statement and Balance Sheet for FB
- Categorized Balance Sheet (Indicating Operating, Investing and Financing Cash items)
- Differences as Cash
- Final Cash Flow Statement
For any queries regarding the cash impact or financial modelling, feel free to put the comments in the blog or write an email to firstname.lastname@example.org
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
|« Compare 2 Excel Files using View side by side Mode [Quick Tip]||Creating Customer Service Dashboard in Excel [Part 3 of 4] »|