How to Create Cash Flow Projection for 12 Months in Excel

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.

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.

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.

Cash Flow Projection for 12 Months.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!