How to Create a Personal Cash Flow Statement in Excel

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.

Table of Contents hide

Download Practice Workbook

What Is Cash Flow Statement?

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

Step-01: Enter Opening Balance in Excel

Step-02: Insert Income Category and Amounts

Step-03: Calculate Percentage of Income

Step-04: Add Expenses Category and Amounts

Step-05: Determine Percentage of Expenses

Step-06: Calculate Net Cashflow

Step-07: Determine Closing Balance

Final Output

Conclusion

Download Practice Workbook

You can download the practice workbook from here.

Creating Personal Cash Flow Statement.xlsx

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.

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

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.

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.

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

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.

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

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)

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.

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.

Select the cell where you want the Total Income.
Further, write the following formula in that selected cell.

=SUM(C8:C9)

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.

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.

Now, select the cell where you want to calculate the Percentage.
Then, write the following formula.

=C8/$C$10

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.

Similar Readings

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.

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

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.

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.

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.

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.

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.

Thirdly, press Enter.

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.

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.

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)

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.

Step-07: Determine Closing Balance

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

First, create another section for Closing Balance.

Then, select the cell where you want the Closing Balance.
Next, write the following formula in that selected cell.

=D4+D24

After that, press Enter to get the Closing Balance.

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

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.

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. For more articles stay connected with ExcelDemy. If you have any questions, let me know in the comment section below.

How to Create a Personal Cash Flow Statement in Excel

Download Practice Workbook

What Is Cash Flow Statement?

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

Step-01: Enter Opening Balance in Excel

Step-02: Insert Income Category and Amounts

Step-03: Calculate Percentage of Income

Step-04: Add Expenses Category and Amounts

Step-05: Determine Percentage of Expenses

Step-06: Calculate Net Cashflow

Step-07: Determine Closing Balance

Final Output

Conclusion

Related Articles

Mashhura Jahan

How to Apply Cubic Spline Interpolation in Excel (with Easy Steps)

How to Add Text Prefix with Custom Format in Excel (4 Examples)

How to Create Material Reconciliation Format in Excel

How to Use VLOOKUP Function with Exact Match in Excel

Leave a reply Cancel reply