How to Create Monthly Cash Flow Statement Format in Excel

A cash flow statement shows the impact of the balance sheet and income statement on the cash and cash equivalents. Basically, this is relevant to the cash in and out of a particular company. In this article, we will show you how to create a monthly cash flow statement format in Excel in two ways. In the first method, we will create it manually, and in the final method, we will use a Microsoft template to do so.

Download Practice Workbook

2 Handy Approaches to Create Monthly Cash Flow Statement Format in Excel

For better visualization, we will only show three months at a time. However, our template has twelve months in it. Moreover, this is the snapshot of the ultimate output of our monthly cash flow format. Lastly, we have used the direct method to create a monthly cash flow statement.

1. Creating Monthly Cash Flow Statement Format Manually

In this section, we will create a monthly cash flow statement format manually in Excel. We will use the SUM function to find the net cash flows for each segment.

Steps:

• Firstly, type the month names.
• Secondly, input the “Beginning Balance” field.
• Thirdly, type the operating activities, which include:
  • “Cash Receipts from Customers”.
  • “Cash Paid for Inventory”.
  • “Cash Paid for Selling & Administrative Expenses”.
  • “Cash Paid for Income Taxes”.
• Then, we will calculate the net cash flow from operating activities.

• Afterward, insert the values for your company. Everything is set up in the accounting format.

• Then, calculate the cash flow from the operating activities by typing this formula in cell C11.

•  Then, using the Fill Handle, AutoFill this formula for the rest of the cells.

• Now, we will focus on the investing activities of the company.
• So, type the operating activities, which include:
  • “Sale of Property, Plant & Equipment”.
  • “Sale of Long-term Investments”.
  • “Purchase of Property, Plant & Equipment”.
  • “Purchase of Long-term Investments”.
• We will use these values to find the “Net Cash Flow from Investing Activities”.

• Then, type all the values for the corresponding account.
• After that, use the following formula in cell C18 to find the net cash flow from this activity.

• Then, AutoFill the formula into the rest of the cells.

• Then, we will find the net cash flows from the financing activities.
• Type the financing activities that consist of these items:
  • “Issuance of Bonds Payable”.
  • “Issuance of Common Stock”.
  • “Repaying Principal on Bonds Payable”.
  • “Paying Dividends”.
• Again, we will find the net cash flows from the financing activities using these values.

• Then, type all the values for the corresponding account.
• After that, use the following formula in cell C25 to find the net cash flow from this activity.

• Then, AutoFill the formula into the rest of the cells.

• The details of the segments have been concealed for better visualization.
• Then, type this formula in cell C27 to find the net increase or decrease in our cash flow for the month of January 2021.

• Afterward, fill in the formula for the rest of the month.

• Next, we will find the ending cash balance, which we calculate by adding the values of the beginning balance and the net increase in cash flow.
• So, type the following formula into cell C29.

• Now, the beginning balance of February will be the ending balance of January.
• So, type this formula in cell D5 to do that.

• After that, use that formula for the remaining months.

• Lastly, if you have successfully followed our steps, the last three months will look like this. This completes the manual way of creating a monthly cash flow statement format in Excel.

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)

2. Monthly Cash Flow Statement from Office Template

We will use an Office template to create another monthly cash flow statement format in Excel.

Steps:

• To begin with, download this monthly cash flow template from the official Microsoft Office website.

• Next, open this downloaded template. This template has 3 Sheets:
  • “Cash Receipts”.
  • “Cash Paid Out”.
  • “Cash Paid Out (Non P&L)”.
• Now, the first Sheet shows the monthly cash receipts.

• Then, the second Sheet records the amount of cash disbursed.

• Lastly, the final Sheet accounts for the items not included in the income (Profit & Loss) statement.
• In conclusion, we can use this format to keep track of the monthly cash flow statement of a company in Excel.

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

Practice Section

We have removed all the values but kept the formula in a Sheet named “format”. You can input values there and follow along with our guide.

Conclusion

We have shown you 2 quick ways to create a monthly cash flow statement format in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!

Related Articles

• How to Calculate Incremental Cash Flow in Excel (2 Examples)
• Prepare Daily Cash Flow Statement Format in Excel
• How to Apply Discounted Cash Flow Formula in Excel
• Create Cash Flow Statement Format with Indirect Method in Excel
• How to Calculate Payback Period in Excel (With Easy Steps)
• Cash Flow Statement Format in Excel for Construction Company
• How to Calculate Future Value of Uneven Cash Flows in Excel