Personal Cash Flow Forecast

Introduction

As a professional accountant in industry I spent a great deal of my career producing cash flow forecasts; they are an essential tool for ensuring the continued health of a business and to warn of potential problems if certain courses of action were to be followed.

A cash flow forecast for individuals is no less important.

I have created a cash flow forecast for my own personal use that I update on a more or less monthly basis.  By being aware of what lies ahead one can plan, and in doing so, avoid any nasty surprises.

This is my Excel spreadsheet.

Preparation

Before being able to create a cash flow forecast one needs a knowledge of one's income and expenditure.  One instinctively knows the basics, monthly salary, mortgage repayments, rent, etc. but for an accurate cash flow forecast you need to know cash income and expenditure in a fair bit more detail. To allow me to do this I downloaded a year's worth of transactions from my current account as a csv file and then used Excel to sort the data in a number of different sequences, large to small, by payee, by date.  I discovered that about 90% of my expenditure was fairly predictable on a cyclic basis. Cash flow forecasts would be easy if receipts and payments were all on a monthly cycle - but they're not!  I discovered that there were a number of other cycles, mainly associated with payments, but also a few receipts had irregular, non-monthly frequencies.  In summary I found that receipts and payments normally followed one of these cycles:

  • Monthly
  • Quarterly
  • Annually
  • Weekly
  • Fortnightly
  • Four-Weekly
  • Six-Weekly

And types of receipt or payments could be categorised as:

  • Standing Orders
  • Direct Debits
  • Regular card payments
  • Other regular payments
  • Irregular payments

Armed with this information I decided to use three basic periods that could then have a frequency associated with them: Yearly, Monthly and Daily.  e.g. Daily with a frequency of 7 would work for weekly, with 28 would work for four-weekly, and 42 for six-weekly.

The types of receipt or payment would act as a check that I wasn't forgetting anything.

Constructing the Spreadsheet

Open a new blank Excel workbook (to give it its correct name!)

Rename the current tab (or worksheet to give it its correct name) to 'Daily'

Save the workbook in a suitable location with a suitable name, e.g. 'PersonalCashFlow'

It is essential that the following instructions are followed exactly

  1. In cell B2 enter text: Description
  2. Highlight column B and change width to, say, 20
  3. In cell C2 enter text: Amount
  4. In cell D2 enter text: Repeat
  5. In cell E2 enter text: Period
  6. In Cell F2 enter text: Base Date
  7. In cell H2 enter the start date for your cash flow: e.g. 01/04/2023
  8. In cell I2 enter: =H2+1
  9. Copy cell I2 to J2:BR2
  10. Highlight columns H:BR
  11. Double-click right-hand cell edge to set default width
  12. Format columns H:BR as Number > Decimal 2 places, comma separated
  13. Highlight row 2
  14. Format as date
  15. Select A2:BR2: format with top and bottom border
  16. Select column A: format as bold
  17. Select column C: format as right-aligned
  18. Select column C: format as Number > Decimal 2 places, comma separated
  19. Select columns D:F: format as centre-aligned
  20. Select column D: format as Number > Decimal 0 places, comma separated
  21. Select column F: format as date 
  22. In cell A4 enter: Opening Balance
  23. In cell A6 enter text: Income
  24. In cell B7 enter the description of your first source of income, e.g. Salary
  25. In cell C7 enter the regular amount of the first source of income, e.g. 2500 (Receipts are positive, payments are negative.) 
  26. In cell D7 enter the repeat frequency of the first source of income.  This is a number
  27. In cell E7 enter the period of the first source of income, this will be:
    • D = Day
    • M = Monthly
    • Y = Yearly
    • (They are not case sensitive)
  28. In cell F7 enter a 'Base Date' for the income.  e.g. if received four-weekly, enter a date in the past when you have received the payment.  For Monthly receipts and payments it is best to avoid 29th to 31st of the month, either enter the date as 28th of the month, or 1st of the month.  In all cases Base Dates must be before the start date entered in H2.
  29. In cell H7 enter: (this is the most important instruction of all!)
    • =IF(QUOTIENT(DATEDIF($F7, H$2, $E7),$D7)>QUOTIENT(DATEDIF($F7, H$2-1, $E7),$D7),$C7,"")
  30. Highlight cell H7: format a Number > Decimal 2, comma separated
  31. Highlight cell H7 and copy to I7:BR7

Repeat steps 22 to 28 for each source on income, each on its own row.

When all sources of income have been added we need to add the payments. I have chosen to categorise them into the following for ease of checking against my downloaded bank transactions, but this isn't strictly necessary:

  • Standing Orders
  • Direct Debits
  • Regular Card Payments
  • Other Regular Payments

Leave a blank row and enter 'Standing Orders' in column A.

  • For each standing order:
    • In column B, enter a description for the standing order
    • In column C enter the amount for the standing order, remembering that payments are entered as negative
    • In column D enter the repeat frequency
    • In column E enter the period
    • In column F enter a base date
    • Highlight H7:BR7 and copy
    • Highlight Hx (where x is the new row just entered) and paste

Repeat for Direct Debits, Regular Card Payments and Other Regular Payments.

After all rows have been added, leave a couple of blank rows and then enter (let's assume we are on row 70):

  • In column A, enter text: Closing Balance
  • In cell H70 enter: Sum(H4:H69)
  • In cell H70 format with top line and double bottom border.
  • Copy cell H70 to I70:BR70
  • In cell I4 enter: =H70  (or whatever row is your Closing Balance row.)
  • Copy I4 to J4:BR4

In cell H4 enter the opening balance from your banking app.

Of course, not all receipts and payments can be forecast precisely so it will be necessary to enter a couple of rows along the lines of 'Clothing allowance, 'Out of pocket expenses' or 'Sundry weekly payments'.  Over time you will become more accurate in forecasting these payments, but there will always be an element of the unknown and unexpected, e.g. boiler repairs, car repairs.  There is little one can do for these other than always to expect the unexpected!  (But that is a move towards personal budgeting - another subject altogether.)

Highlight the Closing Balance row from columns H:BR

  • Select Conditional Formatting
  • Highlight Cell Rules > Greater Than > Enter 0 and select Green Fill with Dark Green Text > Click OK
  • Repeat, but select Less Than > Enter 0 and select Light Green Fill with Dark Red Text
  • Select cell G3
  • Select View > Freeze Panes > Freeze Panes
  • Select cells H2 and H4
  • Change the background colour to, say, yellow, to indicate that these cells need to be updated whenever you revise the workbook for a new forecast period. 

Summary WorkSheet

We now have our cash flow forecast on a day by day basis, but it doesn't really give a very quick overall view of the forecast.

We will now add a summary sheet to give a bird's eye view of the forecast.

  • Click on the + sign at the bottom of the screen to add a new worksheet
  • Double-click sheet name and re-name it as 'Summary'
  • Select cell A3 and enter text: Week-ending:
  • Select cell C3 and enter: = Daily!H2-1
  • Select cell D3 and enter: = C3+7
  • Copy cell D3 to E3:L3

Return to the Daily sheet.  We are going to 'name' certain ranges to make formulae easier to write and understand.

  • Highlight H2 to BR2
    • From the menu, select Formulas > Define Name > Define Name
    • In the popup box enter 'DateHeadings' in the 'Name' box and leave 'Scope' as 'Workbook'.
  • On the Closing Balance row highlight from column H to BR
    • From the menu, select Formulas > Define Name > Define Name
    • In the popup box enter 'ClosingBalance' in the 'Name' box and leave 'Scope' as 'Workbook'.
  • On the Closing Balance Row, highlight the first 7 cells with closing balance data (H:N)
    • Name the range 'Week1
  • Repeat for columns O:T, naming the range Week2, etc up to BL:BR which will be Week9

Back to the Summary sheet

  • In cell D4 enter: =INDEX(ClosingBalance,1,MATCH(D2,DateHeadings,0))
  • Copy Cell D4 to E4:L4
  • In cell D6 enter: =MIN(Week1)
  • Unfortunately we cannot copy this formula, so it will be necessary to enter the formula into each cell and change the week number manually.

By using named ranges the formulae are easier to type in and as importantly, easier to understand.

We'll now add some conditional formatting to draw the user's attention to negative balances

  • Highlight cells D4:L4
  • Select Conditional Formatting
    Highlight Cell Rules > Greater Than > Enter 0 and select Green Fill with Dark Green Text > Click OK
  • Repeat, but select Less Than > Enter 0 and select Light Green Fill with Dark Red Text
  • Repeat for cells D6:L6

How does the daily formula work?

IF function

The basic part of the formula is an 'if' statement.  'If' statements work along these lines:

If(something is true, do this, otherwise do this)

Notice there are three separate parts, separated by commas.

DATEDIF function

The next Excel function we use is DATEDIF.  This calculates the number of days, months or years between two dates, where the structure of the function is:

DATEDIF(start_date, end_date, unit)

Where unit can be:

  • Y (year)
  • M (month)
  • D (day)

For example =DATEDIF("05/03/2023","30/03/2023","D") will give the result '25'.

Note that the start_date must be before the end_date.  Note also that the units (Y, M, D) could be uppercase or lowercase.

QUOTIENT function

The QUOTIENT function returns the integer portion of a division. Use this function when you want to discard the remainder of a division.

For example

  • =QUOTIENT(10, 3) will give the result '3'
  • =QUOTIENT(11, 3) will give the result '3'
  • =QUOTIENT(12, 3) will give the result '4'

Again, note the number and divisor are separated by a comma.

Breaking down our formula

=IF(QUOTIENT(DATEDIF($F7, H$2, $E7),$D7)>QUOTIENT(DATEDIF($F7, H$2-1, $E7),$D7),$C7,"")

In this part we are looking for the point in time where the quotient of the difference between the 'base date' and the 'current date' (divided by the period, days, months or years) for a particular date compared with the previous day suddenly trips from being one whole number to the next whole number.  This indicates that we are at an 'anniversary date' and therefore we enter the period amount into the cell; otherwise we enter a blank.

Note: I did try simplifying the formula by using MOD, but found that whilst I could produce a simpler formula where the period was Days, it wouldn't work for Months and Years.

References

Powered by Publii