Cash Flow Statement: Learn to create Cash Flow Statement Template in Excel

What is a Cash Flow Statement?

In financial accounting, a Cash Flow Statement, also known as Statement of Cash Flow, is a financial statement that shows how changes in balance sheet accounts and income affect cash and cash equivalents, and breaks the analysis down to operating, investing, and financing activities. Essentially, the cash flow statement is concerned with the flow of cash in and out of the business. The statement captures both the current operating results and the accompanying changes in the balance sheet. As an analytical tool, the statement of cash flows is useful in determining the short-term viability of a company, particularly its ability to pay bills. International Accounting Standard 7 (IAS 7), is the International Accounting Standard that deals with cash flow statements.

People and groups concerned with cash flow statements include:

• Accounting personnel, who need to know whether the organization will be able to cover payroll and other immediate expenses
• Potential lenders or creditors, who want a clear picture of a company’s ability to repay
• Potential investors, who need to judge whether the company is financially sound
• Potential employees or contractors, who need to know whether the company will be able to afford compensation
• Shareholders of the business.

If you have done financial modelling, you will know that making right cash flow statement is one of the critical tasks in the process of valuation of the company. Making an exact cash flow statement with all line items as per annual report of the company is an excellent idea but it requires a lot of accounting information of the company and as an Analyst, you always lack the information required to make a detailed cash flow statement.

As an Analyst you only need to have the balancing figure of “Cash and Cash equivalents” to tally the balance sheet and to compute the required figure. One need not make detailed cash flow account as per annual report of the company. You can easily figure out the required Cash and Cash equivalents amount using following equation:

Cash and Cash Equivalents = Total Liabilities + Shareholder’s equity “Total Assets (Except Cash and Cash Equivalents)

Today we’ll learn how to create Cash Flow Statement Template for a Real life Company – ‘LinkedIn’.

Let’s get started!

Task 1 in Creating Cash Flow Template: Getting Relevant Data for the Company

• The numbers we need for creating the Cash Flow Statement of LinkedIn can be found in any of its annual reports or filings. There are many sources on the Internet such as the SEC website, the company website etc. where you can find these reports.

Source: LinkedIn

• Download both the PDF file and the Excel file by clicking the links as indicated in the above image
• After downloading Excel file clean the data for P&L account and Balance sheet and make it in a presentable format as below.

Task 2 in Creating Cash Flow Template: Classifying Balance Sheet Items

• Classify each item on the balance sheet under Operating activity, Investing Activity or Financing Activity depending upon how they impact business

Task 3 in Creating Cash Flow Template: Developing the template in Excel

• Type in the balance sheet items as you’ve classified them. Remember that the first item under Operating Activities is the Net Income.

• Just above the parameters, mention the currency and unit of the values you’ll enter into the Balance Sheet. Enter the first year of projection in the next column. In our case, we start from 2010 and extend till 2012

• Thereafter you can change the format of Years
• To do so, select the Fiscal Years and Press ‘Ctrl+1’ and change the format to ‘FY 0’

• Format the title row as shown below. Ensure that you mention the ‘year ending’ above the years

• The Net Income values must be linked from the Income Statement

• Increase in Assets leads to a decrease in cash, hence we take Value in 2011 Value of 2012 as the cash outflow for 2012

• Using similar method calculate the cash outflow for all the operating items under Assets

• Increase in Liabilities leads to an increase in cash, hence we take Value of 2011 as the cash inflow for 2012
• Using similar method, calculate the cash outflow for all the operating items under Liabilities

• Using a similar concept as illustrated, we can calculate the cash inflows due to Investing and Financing Activities.

Task 4 in Creating Cash Flow Template: Calculating Cash Balance

Calculating Total Cash Flow from Operating activities.

Calculating Total Cash Flow from Investing activities


Calculating Total Cash Flow from Financing


Calculating Change in Cash

Sum of Cash flow from Operating, Investing and Financing gives us overall change in Cash balance for that year.

Calculating Final Cash Balance

• Note that Cash at beginning of current year = Cash at ending of previous year

Final Cash Flow Statement

This is our final Cash Flow Statement. Note that we haven’t shown the Cash Flow for 2010. This is because our balance sheet does not have 2009 data. It is a simple matter of extending the formulas otherwise.

In case you’re interested in learning in depth about how to create Cash Flow Statement template and complex financial models for companies, you can join our Financial Modeling course.

Apart from helping companies track the inflow and outflow of cash, the Cash Flow statements can also be used by investors and creditors. Investors can estimate the future cash flows of the company through the Cash Flow statement which helps them in making investment decisions. It also helps them analyze operating, investing, and financial activities in an organization. Cash Flow statement provides an insight into the liquidity status and solvency of a company and the cash-generating ability of the company. Cash Flow statement also helps the debtors and creditors determine the repayment capacity and capability of the company.

If you want to specialize in Accountancy, here are the best professional courses you should opt for:

• US CMA Certified Management Accountant – US CMA course covers Management Accounting as the major domain in accounting. For details on the US CMA course like US CMA course eligibility, US CMA course scope, US CMA course duration, contact our counsellors.
• ACCA Chartered Certified Accountants – The ACCA course is a great combination of general accounting and management accounting. For details on the ACCA course like ACCA course eligibility, ACCA course scope, ACCA course duration, contact our counsellors.
• US CPA – Certified Public Accountant – The CPA course is the most extensive course in accounting and offers lucrative job opportunities in the accounting segment. To know more about the US CPA eligibility, US CPA duration, US CPA fees, contact our counsellors.

Cash Flow Statement in Detail

How to understand and interpret Cash Flow statement

If you have any doubts or queries, feel free to post them below