Prepare a cashflow forecast

Build a simple cashflow forecast based on the past 12 months' data for your organization in Xero. You'll need to generate Xero's Cash Summary report first, then export it to Microsoft Excel to prepare your cashflow forecast. You'll be able to forecast your monthly cash spend for the next year. The figures will be based on averages across the period and you can easily customize them.

This is one way to prepare a cashflow forecast. We recommend you ask your accountant or bookkeeper for the best method for your organization.

Generate the Cash Summary report in Xero

  1. Go to Reports, then click All Reports.
  2. Under Financial, select Cash Summary.
  3. Enter the following report settings:

    • Date - The latest finalized month
    • Period - 1 month
    • Compare With - Previous 11 Periods
    • Select the Include Sales Tax and Show YTD filters.
  4. Click Update to generate the report in Xero.
  5. At the bottom of the report, click Export and select Excel to download the report in Microsoft Excel format.

Create the cashflow forecast in Microsoft Excel

Ensure you save your worksheet regularly as you follow these steps.

  1. Open the downloaded Cash Summary report in Microsoft Excel.
  2. Select the entire worksheet and unmerge all cells.
  3. Change the name of the 'YTD Actual' column to 'Total'.
  4. Insert a new column to the right of column A and name the column 'Average'.

    Image showing new column inserted.

  5. In the first row which has data, in the 'Average' column, enter a formula to calculate the average of all the months for that row.

    In this example, the 12 months of the year are in columns C to N so in the Sales row, cell B9, we enter the formula =AVERAGE(C9:N9).

    Image

  6. Copy the formula to all data rows below. Don't copy into subtotal or total rows, empty rows, or the Summary section rows.
  7. Copy the whole of your 'Average' column (column B in this example) and paste as values in the same column (Paste Special as Values). This replaces the formulas in this column with just the values.
  8. In the first data row, in the first month column at the left of the worksheet, enter a formula to copy the value from the 'Average' column.

    In this example, in cell C9 we enter the formula: =$B9.

    Image showing Average column value copied.

  9. Copy the formula you just entered and paste it across all remaining month columns in the same row. Don't paste the formula into the 'Total' column.

    In this example we paste =$B9 across columns D to N in row 9.

  10. On the same row, enter a sum formula in the 'Total' column that totals all of the month columns preceding it.

    In this example, in cell O9 we enter the formula =SUM(C9:N9).

    Image showing row total.

  11. Copy the cells in the top data row from the first month column to the 'Total' column (in this example, from C9 to O9). Select all the columns and rows below (excluding subtotal or total rows, empty rows, or the rows in the Summary section), then paste the copied data.
  12. In the Summary section at the bottom of the worksheet, set the second to last month's opening balance to equal the last month's closing balance.

    In this example, in cell M41 we enter the formula =N43.

    Image showing closing balance copied over.

  13. Copy the formula you just entered and paste it across all month columns in the same row. To paste the formula, click the bottom right corner of the cell you've entered the formula in, then drag the + icon across the row. Don't paste the formula into the 'Total' or 'Average' columns.

    In this example we paste the formula across columns L to C in row 41.

  14. On the 'Opening Balance' row in the Summary section, delete the data in the 'Total' column and copy the last month column data into the empty cell.

    In our example, we delete the data from cell O41 and copy the data from cell N41 into it.

    Image showing YTD balance corrected.

  15. Change the column headings to the next financial year and save your forecast.

You can now customize the forecast for your organization's expected business over the next year. This might include:

  • Changing forecast figures for line items which are not evenly distributed throughout the year. For example, asset purchases which might occur only in certain months, or seasonal income and expenses.

  • Adding rows for line items expected next year but which aren't included in this year's figures. Ensure all totals include the figures from new rows.

You might also be interested in