Abrar-ur-Rahman Niloy
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.
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.
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.
Now select the first cell (cell C9 in this case) and write down the following formula.
=SUM(C6:C8)
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.
Then select cell C17 and write down the following formula.
=SUM(C10:C16)
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.
Read More: How to Calculate Cumulative Cash Flow in Excel (with Quick Steps)
Similar Readings
- How to Calculate Payback Period in Excel (With Easy Steps)
- Cash Flow Statement Format in Excel for Construction Company
- Calculate IRR in Excel for Monthly Cash Flow (4 Ways)
- How to Calculate Future Value of Uneven Cash Flows in Excel
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
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.
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
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.
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.
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.
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.
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.
