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.

Download Practice Workbook

Download the Practice Workbook Below.

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.

Step-by-Step Procedures to Create Cash Flow Projection for 12 Months 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. 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.

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.

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

• After that, press Enter to apply the formula.

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

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

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.

Similar Readings

• How to Draw a Cash Flow Diagram in Excel (with Easy Steps)
• Calculating Payback Period in Excel with Uneven Cash Flows
• How to Create Investment Property Cash Flow Calculator in Excel
• Calculate Incremental Cash Flow in Excel (2 Examples)
• How to Prepare Daily Cash Flow Statement Format in Excel

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.

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

• After that, press Enter to apply the formula.

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

Read More: How to Calculate Net Cash Flow in Excel (3 Suitable Examples)

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.

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

• After that, press Enter to apply the formula.

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

Read More: How to Apply Discounted Cash Flow Formula 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.

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

Read More: How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)

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.

Conclusion

We hope that you were able to apply the methods that we showed in this tutorial on how to make 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 of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please feel free to connect us in the comment box.

Related Articles

• How to Calculate Operating Cash Flow Using Formula in Excel
• Track Cash Flow in Excel (Step-by-Step Guideline)
• How to Make a Restaurant Cash Flow Statement in Excel
• Create a Retirement Cash Flow Calculator in Excel
• How to Create a Cash Flow Waterfall Chart in Excel
• How to Create a Real Estate Cash Flow Model in Excel