There are two main methods to calculate CFO in Excel: the direct method and the indirect method. The direct method uses the actual cash inflows and outflows from operating activities, such as cash receipts from customers, cash payments to suppliers, and cash expenses. The indirect method starts with the net income from the income statement and adjusts it for non-cash items, such as depreciation, amortization, and changes in working capital.
Direct Method
The direct method is more straightforward and accurate, but it requires more detailed information about the cash transactions of a company. The general formula for the direct method is:
To calculate CFO using the direct method in Excel, follow these steps:
- Create a layout of the cash flow statement (direct method) with the major categories of cash inflows and outflows, such as cash receipts from customers, cash payments to suppliers, cash payments for operating expenses, etc.
- Enter the amounts of cash inflows and outflows for each category in the corresponding cells. Use negative values for cash outflows.
- Sum up the cash inflows and outflows to get the net cash flow from operating activities.
For example, suppose a company has the following cash transactions for the year 2023:
| Category | Amount |
|---|---|
| Cash receipts from customers | $120,000 |
| Cash payments to suppliers | -$80,000 |
| Cash payments for operating expenses | -$30,000 |
| Cash payments for interest | -$5,000 |
| Cash payments for taxes | -$10,000 |
The cash flow statement (direct method) would look like this in Excel:
| Category | Amount |
|---|---|
| Cash receipts from customers | $120,000 |
| Cash payments to suppliers | -$80,000 |
| Cash payments for operating expenses | -$30,000 |
| Cash payments for interest | -$5,000 |
| Cash payments for taxes | -$10,000 |
| Net cash flow from operating activities | $-5,000 |
The formula in cell B7 is =SUM(B2:B6)
Indirect Method
The indirect method is more commonly used by companies, as it is easier to calculate from the existing financial statements. The indirect method starts with the net income from the income statement and adjusts it for non-cash items and changes in working capital. The general formula for the indirect method is:
To calculate CFO using the indirect method in Excel, follow these steps:
- Create a layout of the cash flow statement (indirect method) with the main components of the formula, such as net income, depreciation and amortization, changes in accounts receivable, changes in accounts payable, changes in inventory, etc.
- Enter the values of net income and non-cash items from the income statement in the corresponding cells. Use positive values for net income and non-cash items that increase CFO, and negative values for non-cash items that decrease CFO.
- Enter the values of changes in working capital from the balance sheet in the corresponding cells. Use positive values for changes in working capital that increase CFO, and negative values for changes in working capital that decrease CFO.
- Sum up the net income, non-cash items, and changes in working capital to get the net cash flow from operating activities.
For example, suppose a company has the following information for the year 2023:
| Category | Amount |
|---|---|
| Net income | $15,000 |
| Depreciation and amortization | $10,000 |
| Gain on sale of equipment | -$2,000 |
| Increase in accounts receivable | -$3,000 |
| Decrease in accounts payable | -$4,000 |
| Increase in inventory | -$5,000 |
The cash flow statement (indirect method) would look like this in Excel:
| Category | Amount |
|---|---|
| Net income | $15,000 |
| Depreciation and amortization | $10,000 |
| Gain on sale of equipment | -$2,000 |
| Increase in accounts receivable | -$3,000 |
| Decrease in accounts payable | -$4,000 |
| Increase in inventory | -$5,000 |
| Net cash flow from operating activities | $11,000 |
The formula in cell B8 is =SUM(B2:B7)
Comparison of Direct and Indirect Methods
Both the direct and indirect methods should yield the same result for CFO, as they are based on the same underlying data. However, they have different advantages and disadvantages. The direct method provides more information about the sources and uses of cash, while the indirect method shows the relationship between net income and CFO. The direct method is more transparent and easier to understand, while the indirect method is more convenient and easier to prepare.
Example of Cash Flow from Operations in Excel
To illustrate how to calculate CFO in Excel using both methods, let’s use a hypothetical scenario of a company that sells widgets. The company has the following financial statements for the year 2023:
| Income Statement | |
|---|---|
| Revenue | $200,000 |
| Cost of goods sold | -$120,000 |
| Gross profit | $80,000 |
| Operating expenses | -$40,000 |
| Operating income | $40,000 |
| Interest expense | -$10,000 |
| Income before taxes | $30,000 |
| Income tax expense | -$9,000 |
| Net income | $21,000 |
| Balance Sheet | 2022 | 2023 |
|---|---|---|
| Assets | ||
| Cash | $10,000 | $15,000 |
| Accounts receivable | $20,000 | $25,000 |
| Inventory | $30,000 | $35,000 |
| Equipment | $50,000 | $40,000 |
| Accumulated depreciation | -$10,000 | -$15,000 |
| Total assets | $100,000 | $100,000 |
| Liabilities and Equity | ||
| Accounts payable | $15,000 | $10,000 |
| Accrued expenses | $5,000 | $5,000 |
| Long-term debt | $40,000 | $30,000 |
| Common stock | $20,000 | $20,000 |
| Retained earnings | $20,000 | $35,000 |
| Total liabilities and equity | $100,000 | $100,000 |
The company also sold some old equipment for $5,000, which had a book value of $7,000. The company paid $10,000 of its long-term debt and $6,000 of dividends to its shareholders.
Using the direct method, the cash flow statement would look like this in Excel:
| Cash Flow Statement (Direct Method) | |
|---|---|
| Cash flows from operating activities | |
| Cash receipts from customers | $195,000 |
| Cash payments to suppliers | -$125,000 |
| Cash payments for operating expenses | -$40,000 |
| Cash payments for interest | -$10,000 |
| Cash payments for taxes | -$9,000 |
| Net cash flow from operating activities | $11,000 |
| Cash flows from investing activities | |
| Cash proceeds from sale of equipment | $5,000 |
| Net cash flow from investing activities | $5,000 |
| Cash flows from financing activities | |
| Cash payments for long-term debt | -$10,000 |
| Cash payments for dividends | -$6,000 |
| Net cash flow from financing activities | -$16,000 |
| Net increase in cash | $-5,000 |
| Cash at beginning of period | $10,000 |
| Cash at end of period | $15,000 |
The formulas in cells B3, B8, B13, B14, and B15 are:
=B2-C2=SUM(B4:B7)=SUM(B9:B12)=B8+B13+B14=B15+B16
Using the indirect method, the cash flow statement would look like this in Excel:
| Cash Flow Statement (Indirect Method) | |
|---|---|
| Cash flows from operating activities | |
| Net income | $21,000 |
| Adjustments to reconcile net income to net cash flow from operating activities | |
| Depreciation and amortization | $5,000 |
| Loss on sale of equipment | $2,000 |
| Increase in accounts receivable | -$5,000 |
| Increase in inventory | -$5,000 |
| Decrease in accounts payable | -$5,000 |
| Net cash flow from operating activities | $13,000 |
| Cash flows from investing activities | |
| Cash proceeds from sale of equipment | $5,000 |
| Net cash flow from investing activities | $5,000 |
| Cash flows from financing activities | |
| Cash payments for long-term debt | -$10,000 |
| Cash payments for dividends | -$6,000 |
| Net cash flow from financing activities | -$16,000 |
| Net increase in cash | $2,000 |
| Cash at beginning of period | $10,000 |
| Cash at end of period | $12,000 |
The formulas in cells B3, B10, B15, B16, and B17 are:
=B2+C2=SUM(B4:B9)=SUM(B11:B14)=B10+B15+B16=B17+B18
As you can see, both the direct and indirect methods give the same result for the net cash flow from operating activities ($11,000) and the net increase in cash ($2,000). However, the direct method shows the actual cash inflows and outflows from each category of operating activities, while the indirect method shows the adjustments to net income for non-cash items and changes in working capital.
