How to Create a Monthly Cash Flow Statement Format in Excel – 2 Methods

A cash flow statement shows the impact of the balance sheet and income statement on the cash and cash equivalents.

The template has twelve months, but only three months are showcased below.

Method 1 – Creating a Monthly Cash Flow Statement Format in Excel (Manual Process)

Use the SUM function.

Steps:

• Enter month names.
• Enter “Beginning Balance”.
• Enter the operating activities:
  • “Cash Receipts from Customers”.
  • “Cash Paid for Inventory”.
  • “Cash Paid for Selling & Administrative Expenses”.
  • “Cash Paid for Income Taxes”.
• Calculate the net cash flow from operating activities.

• Enter the values. The file is set up in accounting format.

• Calculate the cash flow from the operating activities by entering this formula in C11.

•  Drag down the Fill Handle to autoFill the formula.

Focus on the investing activities of the company.

• Enter the operating activities:
  • “Sale of Property, Plant & Equipment”.
  • “Sale of Long-term Investments”.
  • “Purchase of Property, Plant & Equipment”.
  • “Purchase of Long-term Investments”.
• These values will be used to find the “Net Cash Flow from Investing Activities”.

• Enter the values.
• Use the following formula in C18 to find the net cash flow.

• AutoFill the formula.

To find the net cash flows from the financing activities:

• Enter the financing activities:
  • “Issuance of Bonds Payable”.
  • “Issuance of Common Stock”.
  • “Repaying Principal on Bonds Payable”.
  • “Paying Dividends”.

• Enter the values.
• Use the following formula in C25 to find the net cash flow.

• AutoFill the formula.

(The details of the Segments were hidden).

• Enter this formula in C27 to find the net increase or decrease in cash flow for January 2021.

• Autofill the formula.

Find the ending cash balance, by adding the values of the initial balance and the net increase in cash flow.

• Enter the following formula in C29.

The initial balance of February will be the ending balance of January.

• Use this formula in D5.

• Use the formula for the remaining months.

This is the output.

Read More: Create Cash Flow Statement Format with Indirect Method in Excel

Method 2 – Creating a Monthly Cash Flow Statement Format in Excel from an Office Template

Steps:

• Download this monthly cash flow template from the official Microsoft Office website.

• Open the template. It has 3 Sheets:
  • “Cash Receipts”.
  • “Cash Paid Out”.
  • “Cash Paid Out (Non P&L)”.
• The first Sheet shows monthly cash receipts.

• The second Sheet records the amount of cash disbursed.

• The final Sheet contains the items not included in the income (Profit & Loss) statement.
• Use this format to keep track of the monthly cash flow statement.

Read More: Create Cash Flow Statement Format Using Direct Method in Excel

Download Practice Workbook

Related Articles

• How to Create Cash Flow Statement Format in Excel
• How to Prepare Daily Cash Flow Statement Format in Excel
• Cash Flow Statement Format in Excel for Construction Company
• How to Create Weekly Cash Flow Statement Format in Excel

<< Go Back to Cash Flow Template | Finance Template | Excel Templates