How to Prepare Cash Flow Statement Indirect Method in Excel
Preparing a cash flow statement using the indirect method in Excel can be a straightforward process, especially if you have the necessary data and a clear understanding of the accounting principles involved. The cash flow statement is a vital financial statement that shows the inflow and outflow of cash within a business over a specific period. It helps in understanding the liquidity and financial health of a company. In this article, we will guide you through the steps to prepare a cash flow statement using the indirect method in Excel.
Step 1: Gathering the Data
Before you start preparing the cash flow statement, you need to gather the necessary data. This includes the income statement, balance sheet, and any additional information required to calculate the cash flow. Ensure that you have the following data:
– Net income from the income statement
– Opening and closing balances of the balance sheet
– Changes in current assets and liabilities
– Additional information, such as depreciation, amortization, and gains/losses on the sale of assets
Step 2: Setting Up the Excel Workbook
Create a new Excel workbook and set up the following sheets:
– Income Statement
– Balance Sheet
– Cash Flow Statement
In the Income Statement sheet, list the revenue and expenses, including non-cash items like depreciation and amortization. In the Balance Sheet sheet, list the assets, liabilities, and equity. The Cash Flow Statement sheet will be used to calculate the cash flow.
Step 3: Calculating Net Income
In the Cash Flow Statement sheet, start by entering the net income from the income statement. Net income is the starting point for the indirect method, as it represents the company’s profitability.
Step 4: Adjusting for Non-Cash Items
Next, adjust the net income for non-cash items. Add back depreciation and amortization expenses, as these are non-cash expenses that reduce net income but do not affect cash flow. Subtract any gains or losses on the sale of assets, as these are non-cash items that increase or decrease net income.
Step 5: Adjusting for Changes in Current Assets and Liabilities
Now, it’s time to account for changes in current assets and liabilities. For each current asset and liability, determine the change in balance from the opening to the closing period. For example, if accounts receivable increased, subtract the amount from the net income. If accounts payable increased, add the amount to the net income.
Step 6: Calculating Cash Flow from Operating Activities
After adjusting for non-cash items and changes in current assets and liabilities, you will have the cash flow from operating activities. This represents the cash generated or used by the company’s core operations.
Step 7: Calculating Cash Flow from Investing and Financing Activities
In the Cash Flow Statement sheet, create sections for cash flow from investing and financing activities. List the cash inflows and outflows related to investments and financing, such as the purchase or sale of assets, issuance of debt, and payment of dividends.
Step 8: Summarizing the Cash Flow Statement
Finally, summarize the cash flow statement by adding up the cash flow from operating, investing, and financing activities. The net increase or decrease in cash for the period will be displayed at the bottom of the statement.
Conclusion
Preparing a cash flow statement using the indirect method in Excel is a valuable skill for any finance professional. By following these steps, you can ensure that your cash flow statement is accurate and provides valuable insights into your company’s financial health. Remember to regularly review and update your cash flow statement to stay on top of your company’s liquidity and financial performance.
