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.
Recap
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 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.
11 Responses
Ciao Hui,
Collecting Excel tricks under the title “Notable Excel Websites (Non-MVP) Edition” is a brilliant idea…
Thank you in the name of all The FrankensTeam.
On our site there is a box with a picture and text highlighting:
This is a no-MVP site
we think ourselves “bad boys” a bit 🙂
For those who would like to know why our site is a no-MVP site, enough to click on the link:
http://goo.gl/lxDszY
Thank you again!
Thanks a lot
I really enjoyed this (newsletter). I must admit that I rarely read an Excel newsletter (and I subscribe to quite a few) all the way though, but this grabbed my attention and before I realized it, I was engrossed in it. I must also admit that most of this I don’t understand, yet. But, it excites me when I do learn something new in Excel. I can’t wait to see how much of this I can implement into my (constantly-evolving) ‘House Budget’ & ‘Family Medical’ worksheets that I have developed over the past few years! I sure hope to see more of these type of newsletters in the future! Thanks!
Thanks for doing this Hui! I appreciate being included.
I like Tom’s tip a lot. I posted about a tool I wrote to automate this at http://yoursumbuddy.com/tables-edit-query-dialog/
EXCELLENT !
Hui, This post is Superb! More over I have always been a fan of Roberto’s work and have learnt a lot from him.
Here are some of my recent contributions
1. Customising markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/
2. Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/
3. 7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/
4. Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/
5. Adding Direct Legends – http://www.goodly.co.in/customized-scroll-bar-in-excel/
Hope everyone enjoys!
I like the Excel Ninja Menus.
1. Select a cell or range then move till the 4-way cross appears. Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.
2. When you click the fill box on a Date and right click and drag it down, a lot of amazing Date options pop up.
I also brand my Excel to remind myself that I’m awesome. In my personal macro workbook I place the following code.
Private Sub Workbook_Open()
Application.Caption = “SuperKrishna’s Awesomeness”
End Sub
My favorite tip goes along with #17. If you try to copy subtotaled data (and in earlier Excel versions filtered data),when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special. Click Visible cells Only and click OK. Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.
What a great idea, Chandoo! I’d love to be included in your next edition:) Perhaps a VBA exclusive version?
@Ryan
I will review this concept about 6 months out from the original post and be sure to keep your site in mind
Hui…
That sounds great, Hui:) I just realized I gave credit to Chandoo for the idea and I should have attributed it to you.
Sorry about that!