Cash flow projection is the disintegration of money that comes in and out of a business. It gives a clear idea of how much cash a business or an organization is left with after a period of time. For small business owners, managing cash flows can be a game changer and cash flow projections help with that. In this article, we will focus on how to create a cash flow projection for 12 months in Excel.

**Table of Contents**Expand

## Overview of Cash Flow Model

A cash flow model is a financial statement that describes the inflows and outflows of cash over a certain period for a specific organization or company. It usually shows how any change in the balance sheet affects the cash and cash equivalents.

All the inflows a company receives while operating are described in that statement and all the corresponding expenses are also considered throughout a specific period.

The statement provides all the data to visualize the overall profit or loss a company is making. Remarkably, the net amount of cash at the end of the concerning time is the beginning balance for the next period. So, from the statement, the company gets the idea of what approach should be made to overcome the loss and what change should be made to increase the cash every time it runs the operation.

## Create Cash Flow Projection for 12 Months in Excel: Step-by-Step Procedures

The main idea to create a cash flow projection format is to calculate cash inflows and outflows. And then evaluate how much cash one is left with after the period. Microsoft Excel, with the help of formulas and functions like **the SUM function**, can easily do that. We have broken down the whole process into easily digestible steps so that anyone can understand the process regardless of the user’s previous experience. In order to understand cash flow projection, follow the steps carefully.

### Step 1: Record Time Intervals

First, enter the time periods. This should be at the top of the cash flow projection format in the Excel spreadsheet. Now let’s assume our cash entries will be at each month. So we need to enter months as time intervals.

- select cell
**C4**and write down “**Jan-22**”. It should look like this with some formats.

- Then, drag the
**Fill Handle**icon up to cell**N4**.

### Step 2: Create Section for Starting Balance

Having a starting balance is essential for every cash flow projection. Let’s have it at the top before the cash flow calculations begin.

- Select cell
**B5**and write down “**Starting Balance**”.

- Let’s format the cells where we can input values. After that, it will look something like this.

### Step 3: Provide All Cash Inflows

Next, prepare cells for all cash inflows that you will provide later. This generally is the money going into business. For the demonstration, we have added three types of cash inflow for our cash flow projection. Here, you can add your own inflow accounts.

### Step 4: Estimate Total Cash Inflows

Now, we need to estimate the total cash inflows. For that, let’s add a section where we can store the values.

- Select cell
**C9**. - Then, write down the following formula.

`=SUM(C6:C8)`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon up to cell**N9**.

**Read More: **How to Create a Retirement Cash Flow Calculator in Excel

### Step 5: Provide All Cash Outflows

In a similar manner, we need to document all of the cash outflows too in the cash flow projection. In the same way, create all the accounts and make space for them in the cash flow projection format in the Excel spreadsheet.

### Step 6: Calculate Total Cash Outflows

Now, we need to estimate the total cash outflows. For that, let’s add a section where we can store the values.

- Select cell
**C17**. - Then, write down the following formula.

`=SUM(C10:C16)`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon up to cell**N17**.

### Step 7: Compute Net Cash Flow

Now that we have the total inflow and outflow of cash in our cash flow projection format, the next logical step would be to record the net cash flow. The net cash flow is the difference between total cash inflow and total cash outflow.

- To compute net cash flow, first, make way for them to be in the cash flow projection format.

- Select cell
**C18**. - Write down the following formula.

`=C9-C17`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon up to cell**N18**.

### Step 8: Calculate Ending Balance

All the calculations now come down to having a final balance at the end of each period. To store the ending balance, prepare a row at the end of the cash flow projection format.

- Select cell
**C19**. - Write down the following formula.

`=C5+C18`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon up to cell**N19**.

### Step 9: Replicate Starting Balance Formula for Rest of Cells

Every period’s ending balance generally should be the starting balance of the next period. Our starting balance hasn’t been modified in such a way yet. So let’s modify those cells now so that the starting balances at the beginning of each period automatically get updated after each month.

- Select cell
**D5**. - Write down the following formula.

`=C19`

- After that, press
**Enter**to apply the formula.

- Then, drag the
**Fill Handle**icon up to cell**N5**.

** **

### Step 10: Verify Cash Flow Projection Format with Data

Now that the cash flow projection format is complete on the Excel spreadsheet, let’s try it out to see if it is working as intended.

- Let’s enter a starting balance of
**$50000**.

- Upon entering the cash inflow amounts, the total cash inflow cells should get automatically updated like this.

- Similarly, entering cash outflow values will automatically update the total cash outflow. This should not only change the row
**17**values but also the rest of the sheet too.

- It is working well so far for entries of the first period. Let’s fill all of the cells out with test values to see if they are working for the whole sheet.
- Finally, we get the following result.

## Things to Remember

- You must consider that there are conditions when the net cash flow becomes negative. At that time, the ending balance will be less than the starting balance.
- One certain month’s ending balance is the starting balance of the next month. So, you must create a link between them.

**Download Practice Workbook**

Download the Practice Workbook Below.

## Conclusion

We hope that you were able to apply the methods that we showed in this tutorial on how to create a cash flow projection for 12 months in Excel. As you can see, there are quite a few steps to achieve this. So carefully follow them to achieve the same result as we have produced here. If you get stuck in any steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please feel free to contact us in the comment box.

**Related Articles**

- How to Create a Real Estate Cash Flow Model in Excel
- How to Create Investment Property Cash Flow Calculator in Excel
- How to Make a Restaurant Cash Flow Statement in Excel

**<< Go Back to Cash Flow Template | Finance Template | Excel Templates**