How to Create a Cash Flow Projection for 12 Months in Excel – 10 Steps

Overview of a 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.

Create a cash flow projection format to calculate cash inflows and outflows.

Step 1 – Record Time Intervals

Enter months as time intervals.

• Select C4 and enter “Jan-22”.

• Drag the Fill Handle to cell N4.

Step 2 –  Create a Section for the Starting Balance

• Select B5 and enter the “Starting Balance”.

• Format the cells.

Step 3 – Input All Cash Inflows

Prepare cells for all cash inflows that you will provide later.

Step 4: Estimate the Total Cash Inflows

Add a section to store the values.

• Select C9.
• Enter the following formula.

• Press Enter.

• Drag the Fill Handle to N9.

Read More: How to Create a Retirement Cash Flow Calculator in Excel

Step 5: Input All Cash Outflows

Step 6 – Calculate Total Cash Outflows

Estimate the total cash outflows. Add a section to store the values.

• Select C17.
• Enter the following formula.

• Press Enter.

• Drag the Fill Handle to N17.

Step 7 – Compute the Net Cash Flow

• To compute net cash flow:

• Select C18.
• Enter the following formula.

• Press Enter.

• Drag the Fill Handle to N18.

Step 8 – Calculate the Ending Balance

Prepare a row at the end of the cash flow projection.

• Select C19.
• Enter the following formula.

• Press Enter.

• Drag the Fill Handle to N19.

Step 9 – Replicate the Starting Balance Formula for the Rest of Cells

• Select D5.
• Enter the following formula.

• Press Enter.

• Drag the Fill Handle to N5.

Step 10 – Verify the Cash Flow Projection Format

The cash flow projection format is complete.

• Enter a starting balance of $50000.

• Enter the cash inflow amounts and the total cash inflow cells are automatically updated.

• Enter cash outflow values and the total cash outflow will automatically update.

• This is the output.

Things to Remember

• When the net cash flow becomes negative, the ending balance will be less than the starting balance.
• A month’s ending balance is the starting balance of the next month. Link them.

Download Practice Workbook

Download the Practice Workbook below.

Related Articles

• How to Create a Real Estate Cash Flow Model in Excel
• 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