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.

## Download Practice Workbook

You can download the practice workbook from here.

## What Is Cash Flow Statement?

A **Cash Flow Statemen**t 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.

**Read More:** **How to Create Investment Property Cash Flow Calculator 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 (2 Examples)**

### 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 Cash Flow in Excel (7 Suitable Examples)**

**Similar Readings**

**How to Create a Real Estate Cash Flow Model in Excel****Draw a Cash Flow Diagram in Excel (with Easy Steps)****How to Forecast Cash Flow in Excel (with Detailed Steps)****Create Weekly Cash Flow Statement Format in Excel****How to Create Cash Flow Projection Format 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 Track Cash Flow in Excel (Step-by-Step Guideline)**

### 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 Discounted Cash Flow in Excel (with Easy Steps)**

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

**summation**in cells

**D4**and

**D24**.

**Read More:** **How to Calculate Annual Cash Flow in Excel (with Easy Steps)**

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

**Related Articles**

**How to Create Cash Flow Projection for 12 Months in Excel****How to Calculate Operating Cash Flow Using Formula in Excel****Calculating Payback Period in Excel with Uneven Cash Flows****How to Make a Restaurant Cash Flow Statement in Excel****Create a Retirement Cash Flow Calculator in Excel****How to Create a Cash Flow Waterfall Chart in Excel**