How to Create Monthly Cash Flow Statement Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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 in Excel (Manual Process)

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)

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

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: Create Cash Flow Statement Format with Indirect Method in Excel


2. Making Monthly Cash Flow Statement Format in Excel 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


Download Practice Workbook


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.


Related Articles


<< Go Back to Cash Flow TemplateFinance TemplateExcel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo