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.

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

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.

Read More: How to Create Cash Flow Projection for 12 Months 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.

Read More: How to Create a Real Estate Cash Flow Model 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: How to Create a Retirement Cash Flow Calculator 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.

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

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.

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.

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.

Then select cell C17 and write down the following formula.

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.

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

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.

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.

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.

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.

Then press Enter.

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.

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

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.

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.

We can see that the cash flow projection format is working as intended in Microsoft 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.

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.

Related Articles

• 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