Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Record Time Intervals to Create Cash Flow Projection for 12 Months in Excel

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

Create Section for Starting Balance to Create Cash Flow Projection for 12 Months in Excel

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

Provide All Cash Inflows to Create Cash Flow Projection for 12 Months in Excel


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)

Estimate Total Cash Inflows to Create Cash Flow Projection for 12 Months in Excel

  • After that, press Enter to apply the formula.

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

Calculate Total Cash Inflows to Create Cash Flow Projection for 12 Months in Excel

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.

Provide All Cash Outflows to Create Cash Flow Projection for 12 Months in Excel


Similar Readings


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)

Calculate Total Cash Outflows to Create Cash Flow Projection for 12 Months in Excel

  • After that, press Enter to apply the formula.

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

Estimate Total Cash Outflows to Create Cash Flow Projection for 12 Months in Excel


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

Compute Net Cash Flow to Create Cash Flow Projection for 12 Months in Excel

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

=C5+C18

Calculate Ending Balance to Create Cash Flow Projection for 12 Months in Excel

  • 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

Replicate Starting Balance Formula for Rest of Cells to Create Cash Flow Projection for 12 Months in Excel

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

Verify Cash Flow Projection Format with Data to Create Cash Flow Projection for 12 Months in Excel

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

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo