How to Create a Cash Flow Projection Template in Excel

Step 1 – Record Time Intervals

  • Enter the first time period at the top of the template.

  • Use the Autofill Tool and drag to fill in the other time periods.

cash flow projection format in excel

Read More: How to Create Cash Flow Projection for 12 Months in Excel


Step 2 – Create a Section for the Starting Balance

  • Select an appropriate cell and label it “Starting Balance”.

  • Format the data cells for each period.

cash flow projection format in excel

Read More: How to Create a Real Estate Cash Flow Model in Excel


Step 3 – Add All Cash Inflow Sources

  • Add rows for and label all the Cash Inflow sources you will record data for later.

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


Step 4 – Set Up Cash Inflow Totals

  • Add a row to total the Cash Inflow for each period.

cash flow projection format in excel

  • Select the first Cash Inflow total (cell C9 in this example) and enter the following formula:

=SUM(C6:C8)

cash flow projection format in excel

Press Enter.

  • Use the Autofill Tool and drag to fill in the other cells in the row.


Step 5 – Add All Cash Outflow Sources

  • Add rows for and label all of the Cash Outflow sources.


Step 6 – Set Up Cash Outflow Totals

  • Add a row to total the Cash Outflow for each period.

cash flow projection format in excel

  • Select the first Cash Outflow total (C17 in this example) and enter the following formula:

=SUM(C10:C16)

cash flow projection format in excel

  • Press Enter.

  • Use the Autofill Tool and drag to fill in the other cells in the row.

cash flow projection format in excel


Step 7 – Add and Set Up Net Cash Flow Totals

  • Select the first Net Cash Flow total (C18 in this example) and enter the following formula:

=C9-C17

cash flow projection format in excel

  • Press Enter.

  • Use the Autofill Tool and drag to fill in the other cells in the row.

cash flow projection format in excel


Step 8 – Add and Set Up Ending Balance Totals

  • Add a row for Ending Balance.

  • Select the first Ending Balance total (C19 in this example) and enter the following formula:

=C5+C18

cash flow projection format in excel

  • Press Enter.

  • Use the Autofill Tool and drag to fill in the other cells in the row.


Step 9 – Replicate the Starting Balance Formula

  • Select the Starting Balance for the second period (Cell D5 in this example) and enter the following formula:

=C19

  • Press Enter.

cash flow projection format in excel

  • Use the Autofill Tool and drag to fill in the other cells in the row.

cash flow projection format in excel

The Cash Flow Projection Template is complete.


Step 10 – Verify the Template with Test Data

Enter a Starting Balance of $50000 for the initial time period.

  • Enter amounts for the Cash Inflow sources.
  • Cash Inflow and Net Cash Flow totals should update automatically.

cash flow projection format in excel

  • Repeat for the Cash Outflow sources.
  • Verify every cell in the first column has been updated.

cash flow projection format in excel

  • Repeat for the remaining periods to verify the formulas are all working properly.

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.


Related Articles


<< Go Back to Cash Flow TemplateFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

2 Comments
  1. What about using indirect method from BS for the 5 year forecast?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 12, 2024 at 2:58 PM

      Hello Syarif,

      Thank you for reaching out to us and for your valuable query.

      As this article focuses on creating a cash flow projection format, you have to manually input your projected amounts for all of your cash inflows and outflows.

      To project cash flows for five years using an indirect method, first, you must collect your company’s balance sheets and income statements for two consecutive years. Using two-year data, you have to find cash flows manually for year 1 (base of projection). Next, for projecting cash flows for five years, estimate a percentage of expected growth in cash flow. Multiply each cash flow with your estimated projection of increase or decrease of cash flows. Use Fill Handle to apply the formula for all the years you want to project.

      Hope that the following article will provide you with valuable clues on creating cash flow projections using the indirect method:

      Thanks and Regards,
      Abdullah Al Masud
      ExcelDemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo