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.

Quick View


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.

Monthly Cash Flow Statement Format in Excel 2

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

Monthly Cash Flow Statement Format in Excel 3

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

=SUM(C7:C10)

Monthly Cash Flow Statement Format in Excel 4

  • 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”.

Monthly Cash Flow Statement Format in Excel 5

  • 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.

=SUM(C14:C17)

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

Monthly Cash Flow Statement Format in Excel 6

  • 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.

Financing Activities

  • 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.

=SUM(C21:C24)

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

Financing Activities 2

  • 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.

=SUM(C11,C18,C25)

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

Monthly Cash Flow Statement Format in Excel 9

  • 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.

=C5+C27

Monthly Cash Flow Statement Format in Excel 10

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

=C29

Manual Way 1

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

Manual Way 2

  • 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.

Practice Dataset for You

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:

  • 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.

Template 1

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

Template 2

  • 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.

Template 3

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.

Practice


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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo