A cash flow statement is an essential financial tool that helps individuals and businesses understand their financials better. A cash flow statement shows the inflow and outflow of cash in a business. It helps you track the amount of cash that your business generates, where the cash comes from, where it goes, and the amount of cash available at the end of the period.

Excel is one of the best tools for creating a cash flow statement. In this article, we present a step-by-step guide on how to make a cash flow statement in Microsoft Excel.

Step 1: Create a new workbook

To create a cash flow statement in Excel, you need to open a new workbook. Click on ‘File’ and then select ‘New Workbook’ to open a blank workbook.

Step 2: Prepare the Excel worksheet

From the blank Excel workbook, create a worksheet for your cash flow statement by renaming the first worksheet to ‘Cash Flow Statement.’ Next, create two columns labelled ‘Cash inflow’ and ‘Cash outflow.’

Step 3: Enter your cash inflows

In the ‘Cash inflow’ column, enter all the cash inflows for the period you are preparing the cash flow statement for. Cash inflows may include sales revenue, loans, capital contributions, and other forms of cash receipt. Enter each cash inflow item in the same row, and ensure to specify the date the cash was received where possible.

Step 4: Enter your cash outflows

In the ‘Cash outflow’ column, enter all the cash outflows you incurred in the period being examined. Cash outflows may include bank loans, rent payments, utility bills, supplier payments, salaries and wages, and more. Enter each cash outflow item in the same row and ensure to specify the date the cash was spent where possible.

Step 5: Calculate the net cash inflow or outflow

From the ‘Cash inflow’ and ‘Cash outflow’ columns, calculate the net cash inflows or outflows for every period. Subtract cash outflows from cash inflows and enter the result in a new column designated as ‘Net Cash Flow.’

Step 6: Calculate the opening and closing cash balance

To calculate the opening cash balance, go through your previous period’s closing cash balance and add the current period’s net cash flow. The opening cash balance is the bottom line of the opening cash balance column. If this is your first period, the opening cash balance will be zero.

After calculating the opening cash balance, calculate the closing cash balance by subtracting the current period’s net cash outflows from the opening cash balance. The closing cash balance is the bottom line of the closing cash balance column.

Step 7: Create a chart

To make the data more visual, create a chart to represent your cash inflows, cash outflows, and net cash flow. Right-click any of the columns and choose a chart that is easy to read.

Step 8: Update your cash flow statement periodically

Remember that a cash flow statement is a dynamic financial statement, and it needs to be updated regularly. You can use the existing cash flow statement workbook as a template and make necessary adjustments. Ensure to update it every month, quarter, or at the end of the financial year.

In conclusion, creating a cash flow statement in Microsoft Excel can seem daunting, but it is a straightforward process. By following the above steps, you can quickly build a cash flow statement that helps you plan and manage your finances better.