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

Overview

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.

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.

=SUM(C6:C8)

Press Enter.

Drag the Fill Handle to N9.

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.

=SUM(C10:C16)

Press Enter.

Drag the Fill Handle to N17.

Step 7 – Compute the Net Cash Flow

Select C18.
Enter the following formula.

=C9-C17

Press Enter.

Drag the Fill Handle to N18.

Step 8 – Calculate the Ending Balance

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

Select C19.
Enter the following formula.

=C5+C18

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.

=C19

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.

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