Cash Flow Analysis in Excel

CASH FLOW COMPONENT

The Cash Flow Statement is the most important statement to focus on in order to understand the financial health of the company. In order to really understand Cash Flows, it is recommended to recalculate the implied historic Cash Flows on an Excel spreadsheet based on the information available in the Income Statement and Balance Sheet.

Cash Flows tell us the inflows and outflows in the company and what the company has gained from selling out a particular asset or invest in net working capital.

The Cash Flow Statement shows the most important feature of a company, its ability to produce cash, if the income statement shows profits it doesn’t mean that company is doing fine financially. A company’s profits are assessed on the basis of cash inflows in the company at the end of the accounting period whether the company has generated real cash or if the cash had to be used to operate the business is not important from an accounting perspective. The best way to analyze the company’s effective cash generation is to recalculate the historic cash flows in Excel in order to avoid a misunderstanding.

Basically, there are three sections of the Cash Flow statement (1) Operating activities (2) Investing activities (3) Financing activities. Operating and investing cash flows show from where the company gets its cash. On the other hand, financing activities show how the company spends its cash.

(1) Operating activities

Operating cash flow shows how much money the company is generating by selling its goods or services. So, the first important aspect is to see the company’s revenue and its net profit whether the company is earning or not.

It is important to see that a company’s revenue is higher than its expenses to determine if it is a favorable condition for a company as well as for investors because it eventually produces net positive cash flow. On the other hand, if a company’s expenses are greater than its revenue then it is an alarming situation for both company and the investors unless the expenses will generate more income in the future.

This means a company’s revenues and expenses should be scrutinized and its revenues should be greater than its expenses. Moreover, it is possible that the company’s operating cash flow is negative in the initial years but it will continue to improve and eventually become positive in case a lot of inventory and receivables are required to operate the business.

Changes in cash flow from operations typically offer a preview of change in the income. It’s a good sign when it goes up. We should watch out for the widening gap between reported earnings and operating cash flow. If net income is higher than cash flow then it means that the company may be speeding or slowing its booking of income or costs. Moreover, working capital is also important in the analysis of operating cash flow which is current assets minus current liabilities. Items included in operating activities are net profit, depreciation/amortization, and changes in working capital.

EXAMPLE: If a company’s revenue is $100’000, its cost of goods sold is $20’000, administering expenses and other expenses are $30’000 then its net profit would be $50’000.  Now assume we have a cash outflow as working capital increases (current assets less cash minus current liabilities less financial debt) of $30’000 and we will deduct those $30’000 from the net profit of 50’000 which results in a remaining $20’000 cash flow from operating activities.

Operating cash flow can also be analyzed as follows. As we record sales on Accrual Accounting Basis with positive net profit but if we are not receiving money at the end due to changes in Net Working Capital, then the business model might not be as good as we thought. Then the implications are to find new ways to reduce receivables and inventory to increase cash flow generation ability.

(2) Investing activities

Cash Flow from investing activities reflects the amount of money the company spends on capital expenditures such as buying new equipment or machines which are longer-term investments. Further, it also includes cash receipts from the sale of fixed assets or financial investments. When a company’s operating cash flow is positive and overall cash flow is negative it doesn’t reflect a bad sign because it could be a result of enormous investment in equipment which is a good thing. Again we have to see investing activities in more than one perspective whether it benefits the company’s operations in the long run regardless of the negative cash flow.

In addition to the investing activities, if a company is investing heavily to generate future cash flow it means that the company’s cash is going out but it will benefit the company in the future. Negative investing activities cash flow is not necessarily a bad thing we have to see whether the company is investing or not. On the other hand, if a company is disposing off assets and incurring losses, and selling its equipment less than its book value. This means the company is a bad performer in disposing of assets because the poor purchase and sale decisions cause the company to incur losses.

In a nutshell, when we analyze investing activities we should see how much amount company is investing to generate future cash inflows if investment results in negative investing cash flow it is not a bad thing because this negative cash flow will be offset in the future. Gains from the eventual sale of assets normally are recorded in the income statement While if a company is selling its equipment more than its book value then again it’s a good thing and result in a positive figure but if a company is selling is equipment less than its book value then it reflects company is incurring losses. Negative investing activities are not a bad sign always and it needs further evaluation before decisions are made on a company’s investing activities. Items included in investing activities. Purchase or sale of assets, Purchase or Sale of Investment products and lending of money, and collection of loans.

EXAMPLE: Purchase of equipment costing $500’000. This amount would be deducted because it is a cash outflow. Another example is the sale of old equipment with a book value of $3’000 but it is sold in the market for $4’000 so the difference of $1000 is the gain/proceeds from the sale of equipment and it would be added because it is a cash inflow.

(3) Financing activities

The financing activities section reflects the cash inflows and outflows by selling stocks and bonds. This section includes the cash inflow by selling stocks and bonds or borrowing loans. Similarly, when a company pays back a loan or pays a dividend payment it will be considered as cash outflow. Financing activities include the transactions of securities and loans.

One of the most effective ways to analyze the financing activities portion of the cash flow is to see how frequently the company is going for new debt or equity. This shows that the company is not generating enough amount of funds to runs its operations and turns to new debt or issuing new equity when the company isn’t generating sufficient cash internally. Positive financing activities cash flow means that the company is flowing in more money than flowing out. This means that company’s assets are increasing and investors are glad to see positive financing activities cash flow.

Further, negative cash flow can be analyzed in two ways and then give results whether it’s a good sign or not. One way is that company is retiring its loan and repurchases stocks and paying out dividends in this case investors would be happy regardless of the negative cash flow because the company is paying dividends and repurchasing its own stocks which creates a positive image of the firm publicly. The second way to analyze negative financing cash flow is to see whether the company is paying dividends or not. If a company is only retiring loan and not doing anything else such as repurchasing stock or payment of dividends to its investors then it would be bad for the company’s reputation and investors would not be happy with that.

EXAMPLE: Issuing 10’000 shares with $2 price of each share. So $20’000 would be added into financing activities because the company has issued the shares and the general public has bought 10’000 shares for $2 per share so it is a cash inflow for the company.

Another example is a company that has paid dividends to its investor’s $1 dividend per share so if the company has issued total of 15000 shares and paid dividends to everyone then the dividend payment would be($1*15’000)= $15’000. This is a cash outflow because the company is paying/giving money to its investors so it would be deducted from financing activities.

The above mentioned is the one way to analyze cash flow statements based on the historic. There are also other ways to analyze cash flow statements such as use ratios to analyze the health of the company.

Most important ratios to analyze cash flow are:

(1) DEBT TO EQUITY RATIO: This is a financial leverage ratio and this ratio tells the proportion/percentage of the company’s debt and equity used to finance its assets.

FORMULA: TOTAL LIABILITIES/SHAREHOLDERS EQUITY

(2) CURRENT RATIO: This is a liquidity ratio that simply estimates the company’s to pay off its short-term obligations.

FORMULA: CURRENT ASSETS/CURRENT LIABILITIES

(3) QUICK RATIO: This is also a liquidity ratio that estimates the company’s ability to pay off short-term obligations without relying on the sales of the inventory because inventory can be sold on credit and it takes several months to collect the money.

FORMULA: CURRENT ASSETS-INVENTORY/CURRENT LIABILITIES

(4) RETURN ON EQUITY: ROE estimates the profitability of the company. It measures the profits generated with the money invested by the shareholders

FORMULA: NET INCOME/SHAREHOLDER’S EQUITY

(5) NET PROFIT MARGIN: It is the proportion that is left after deducting all expenses from sales. This ratio shows the amount of profit a company has generated from its total sales. The higher the net profit the more company retains from its sales.

FORMULA: NET PROFIT/SALES

When you analyze cash flow it should be based on historic rather than projected. It is better that the analysis should be based on historic cash flow statements because historic cash flow reflects the real picture of the firm in the past and it is convenient to compare the present and past of the firm based on the real cash flows of the firm rather than projected. Projected cash flow can give you some idea to some extent but they are not accurate. While the historic is more accurate and its results are valid and easy to interpret because the company has experienced everything in the real and data is also real whether the company has improved or not, whether the company is in growing phase or declining phase. It should be historic because of the validity and accuracy of the results.

Leave a Reply