How to Create Cash Flow Projection Format 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 format in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below. You can use it as your personal template too with some modifications.


Step-by-Step Process to Create Cash Flow Projection Format in Excel

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.

Follow these steps to see how you too can easily make a cash flow projection format in Excel.


Step 1: Record Time Intervals

First of all, let’s 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.

For that, select cell C4 and write down “January 2022”. It should look like this with some formats.

Then select the cell again and click and drag them to cell N4. All of the months should be filled up after this.

cash flow projection format in excel

Read More: How to Create Monthly Cash Flow Statement Format in Excel


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.

For that, 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.

cash flow projection format in excel

Read More: Create Cash Flow Statement Format Using Direct Method in Excel


Step 3: Document All Cash Inflows

Now prepare cells for all cash inflows that you will record 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.

After adding these accounts, it should now look like this.

Read More: Create Cash Flow Statement Format with Indirect Method in Excel


Step 4: Calculate Total Cash Inflow

Now is the time to calculate the total cash inflow in the flow projection of the Excel spreadsheet. For that, let’s add a section where we can store the values.

cash flow projection format in excel

Now select the first cell (cell C9 in this case) and write down the following formula.

=SUM(C6:C8)

cash flow projection format in excel

After that, press Enter.

Then select the cell again and click and drag it all the way to the right to replicate the formula for the rest of the cells.

Read More: How to Forecast Cash Flow in Excel (with Detailed Steps)


Step 5: Document 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.

Read More: How to Create Cash Flow Statement Format in Excel


Step 6: Estimate Total Cash Outflow

Now calculate the total cash outflow in the cash flow projection. To do that, first select cells to store values.

cash flow projection format in excel

Then select cell C17 and write down the following formula.

=SUM(C10:C16)

cash flow projection format in excel

After that, press Enter.

Now select the cell again and click and drag the fill handle icon to the right to replicate the formula for the rest of the cells.

cash flow projection format in excel

Read More: How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)


Similar Readimgs


Step 7: Compute Net Cash Flow

Now that we have 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.

Then select cell C18 and write down the following formula.

=C9-C17

cash flow projection format in excel

After that, press Enter.

Now select the cell again. Then click and drag the fill handle icon to the right to fill the rest of the cell with this formula.

cash flow projection format in excel

Read More: How to Calculate Operating Cash Flow in Excel (2 Easy Ways)


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.

Now select cell C19 and write down the following formula.

=C5+C18

cash flow projection format in excel

Then press Enter.

After that, select the cell again. Then click and drag the fill handle icon to the right to fill the rest of the cell with this formula for their respective references.

Read More: How to Prepare Daily Cash Flow Statement Format in Excel


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.

For that, select cell D5 and write down the following formula.

=C19

Then press Enter.

cash flow projection format in excel

After that, select the cell again and click and drag them to the right end of the chart to fill the rest of the cells with this formula.

cash flow projection format in excel

Our cash flow projection format in Excel is now complete at this point.

Read More: How to Create Weekly Cash Flow Statement Format in Excel


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

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

cash flow projection format in excel

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.

cash flow projection format in excel

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.

cash flow projection format in excel

We can see that the cash flow projection format is working as intended in Microsoft Excel.


Conclusion

That concludes our guide on how to create a cash flow projection format in Microsoft Excel. Hopefully, you have understood the grasp of making the cash flow projection format manually in Excel. Of course, you can download the workbook and use it as a template after some modifications. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides and templates like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo