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**Expand

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

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

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

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

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

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

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

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

**If you are getting your result in decimal then change the cell format to**

*Note:***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.

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

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

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

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

- Thirdly, press
**Enter**.

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

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

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

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

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

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

**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**

- How to Draw a Cash Flow Diagram in Excel
- How to Track Cash Flow in Excel (Step-by-Step Guideline)
- How to Calculate Operating Cash Flow Using Formula in Excel
- How to Calculate Payback Period in Excel
- How to Calculate Payback Period with Uneven Cash Flows
- How to Apply Discounted Cash Flow Formula in Excel
- How to Calculate Operating Cash Flow in Excel

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