How to Create a Personal Cash Flow Statement in Excel

Get FREE Advanced Excel Exercises with Solutions!

The objective of this article is to explain how to create a personal cash flow statement in Excel. Hopefully, after reading this article you will be able to create it all by yourself.


What Is Cash Flow Statement?

A cash flow statement is an important financial tool. The Cash Flow Statement generally shows the cash inflows and cash outflows during a certain period of time. Cash inflow generally refers to the income and outflow refers to the expense. The inflows are presented as positive values and the outflows are presented as negative values.


How to Create a Personal Cash Flow Statement in Excel: Step by Step Procedures

To explain this article, I have taken the following Excel sheet. I have named this Excel sheet dataset. It contains the income and expenses record for a month. The dataset contains 4 columns named Entry Name, Category, Type, and Amount. Here, I have entered negative values for expenses because it refers to outflow. The red colored values are negative values. I will show you step-by-step procedures to create a personal cash flow statement in Excel using this dataset. Let’s see the steps.

Dataset to Create a Personal Cash Flow Statement in Excel


Step-01: Enter Opening Balance in Excel

In this first step, I will show you how you can enter the Opening Balance in a personal cash flow statement in Excel.

  • Firstly, create a section for entering Opening Balance.

Enter Opening Balance in Excel to Create a Personal Cash Flow Statement

  • Then, enter your Opening Balance at the start of the month.

Read More: How to Calculate Annual Cash Flow in Excel


Step-02: Insert Income Category and Amounts

Here, I will insert the information about income. To begin with, I will insert the income Category from the dataset. Then, I will insert the Amount by using the SUMIF function.

  • First, create a section for inserting Income information.

Insert Income Category and Amounts

  • After that, write the Income Category you found from the dataset.

Inserting Income Categories to Create a Personal Cash Flow Statement in Excel

  • Next, select the cell where you want to get the Amount. Here, I selected cell C8.
  • Then, in cell C8 write the following formula.
=SUMIF(Dataset!C5:C22,'Income Category and Amounts'!B8,Dataset!E5:E22)

Using SUMIF function to get Income Amounts to Create a Personal Cash Flow Statement in Excel

  • Afterward, press Enter to get the total Amount for that specific Category.

Here, in the SUMIF function, I selected cell range C5:C22 from the Dataset sheet as range, cell B8 from Income Category and Amounts sheet as criteria, and cell range E5:E22 from the Dataset sheet as sum_range. Now, the formula returns the summation of the values from sum_range that match the criteria.
  • Drag the Fill Handle down to copy the formula.

Dragging Fill Handle to Copy Formula to Create a personal Cash Flow Statement in Excel

  • Finally, you will see that I have copied the formula to the other cell and got the Amount for that specific criterion.

  • Now, create another selection for Total Income.

Making Section for Total Income to Create a Personal Cash Flow Statement in Excel

  • Select the cell where you want the Total Income.
  • Further, write the following formula in that selected cell.
=SUM(C8:C9)

Using SUM function to Calculate Total Income to Create a Personal Cash Flow Statement in Excel

  • After that, press Enter to get Total Income.

Here, in the SUM function, I selected cell range C8:C9 as numbers. Now, the formula returns the summation of the cell range C8:C9.

Read More: How to Calculate Incremental Cash Flow in Excel


Step-03: Calculate Percentage of Income

Now, I will calculate the Percentage of income in the personal cash flow statement to get a more clear idea of the income. The Percentage will show you which Category contributes how much to your Total Income.

  • In the beginning, create a column for the Percentage of income.

Calculate Percentage of Income

  • Now, select the cell where you want to calculate the Percentage.
  • Then, write the following formula.
=C8/$C$10

Calculating Percentage of Income to Create a Personal Cash Flow Statement in Excel

  • Afterward, press Enter to get the Percentage.

Here, the value in cell C8 is divided by the value in cell C10. I used an Absolute Cell Reference for cell C10 so that the formula does not change while using the Autofill.
Note: If you are getting your result in decimal then change the cell format to Percentage and you will get your desired result.
  • Next, drag the Fill Handle down to copy the formula.

  • Consequently, you can see that I have copied the formula to the other cell.

  • After that, select the cell where you want to calculate the total Percentage. Here, I selected cell D10.
  • In cell D10 write the following formula.
=SUM(D8:D9)

  • Finally, press Enter to get the result.

Now, the SUM function returns the summation of the cell range D8:D9.

Read More: How to Calculate Discounted Cash Flow in Excel 


Step-04: Add Expenses Category and Amounts

In this step, I will add information about Expenses to the personal cash flow statement. I will add Categories for expenses and the Amounts.

  • Firstly, create a section for interesting information about Expenses like the following image.

Add Expenses Category and Amounts

  • After that, go back to the Dataset sheet to get the Category.
  • Then, go to the Data tab.
  • Next, select Advanced.

Use of Data Tab to Create a Personal Cash Flow Statement in Excel

  • Consequently, the Advanced Filter dialog box will appear.
  • Select Copy to another location.
  • Secondly, select the range where you have your categories for expenses as the List range.
  • Now, select a cell as Copy to.
  • After that, Check the Unique records only option.
  • Finally, select OK.

Advanced Filter Dialog Box to Create a Personal Cash Flow Statement in Excel

  • Here, you will see that the unique expense categories are copied to your selected location.
  • Select the range.
  • Press Ctrl + C on your keyboard to copy the range.

  • Further, select the cell where you want your expense Category.
  • Press Ctrl + V to paste the copied values.

Pasting Range to Get Expenses Category to Create a Personal Cash Flow Statement in Excel

  • In the following image, you can see that I have copied the categories.

  • Thirdly, select the cell where you want the Amount.
  • Write the following formula in that selected cell.
=SUMIF(Dataset!C5:C22,'Expenses Category and Amounts'!B13,Dataset!E5:E22)

  • Press Enter to get the Amount.

Here, in the SUMIF function, I selected cell range C5:C22 from the Dataset sheet as range, cell B13 from Expenses Category and Amounts sheet as criteria, and cell range E5:E22 from the Dataset sheet as sum_range. Now, the formula returns the summation of the values from sum_range that match the criteria.
  • After that, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells.

  • Subsequently, create a section for Total Expenses.

Creating Section for Total Expenses to Create a Personal Cash Flow Statement in Excel

  • So, select the cell where you want your Total Expenses. Here, I selected cell C23.
  • In cell C23 write the following formula.
=SUM(C13:C22)

  • Lastly, press Enter to get the Total Expenses.

The SUM function will return the summation of the cell range C13:C22.

Read More: How to Forecast Cash Flow in Excel 


Step-05: Determine Percentage of Expenses

Now, I will determine the Percentage of expenses.

  • First, select the cell where you want to calculate the Percentage. Here, I selected cell C13.
  • Secondly, in cell C13 write the following formula.

Determine Percentage of Expenses

  • Thirdly, press Enter.

Determine Percentage of Expenses to Create a Personal Cash Flow Statement in Excel

Here, the value in cell C13 is divided by the value in cell C23.
  • Afterward, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells.

  • Next, select the cell where you want to calculate the total Percentage.
  • Then, write the following formula in that selected cell.
=SUM(D13:D22)

  • After that, press Enter to get the result.

Here, the SUM function returns the summation of the cell range C13:C22.

Read More: How to Calculate Free Cash Flow in Excel


Step-06: Calculate Net Cashflow

In this step, I will calculate the Net Cashflow in Excel.

  • In the beginning, create a section for Net Cashflow.

Calculate Net Cashflow

  • Next, select the cell where you want to calculate Net Cashflow. Here, I selected cell C24.
  • Then, in cell C24 write the following formula.
=SUM(C10,C23)

Calculating Net Cashflow to Create a Personal Cash Flow Statement in Excel

  • Afterward, press Enter.

Here, in the SUM function, I selected cell C10 as the number1 and cell C23 as number2. Now, the function returns the summation of these two values.

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)


Step-07: Determine Closing Balance

In this final step, I will calculate the Closing Balance.

  • First, create another section for Closing Balance.

Determine Closing Balance

  • Then, select the cell where you want the Closing Balance.
  • Next, write the following formula in that selected cell.
=D4+D24

Determining Closing Balance to Create a Personal Cash Flow Statement in Excel

  • After that, press Enter to get the Closing Balance.

Here, the formula returns the value summation in cells D4 and D24.

Read More: How to Calculate Cumulative Cash Flow in Excel


Final Output

In the following picture, you can see the final output of my personal cash flow statement in Excel. You can follow these steps to create your own personal cash flow statement in Excel. Or, you can use this one as a template.

how to create a personal cash flow statement in excel


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

To conclude, I tried to explain how to create a personal cash flow statement in Excel in this article. Here, I explained it in 7 easy steps. I hope this article was helpful for you.


Related Articles


<< Go Back to Excel Cash Flow Formula | Excel Formulas for Finance | Excel for Finance | Learn Excel

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.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo