Welcome to the Evenstone blog

The place for e-commerce and online retail news.

How do I use my Brightpearl data to create a cash flow forecast?

ring binder with cash flow written on side

Cash flow forecasts are one of the key tools that you need to manage a business.  Even a highly profitable business needs to keep an eye on its cash position so that staff, suppliers and the tax authorities can all be paid on time. A cash flow forecast is indispensible for this.

Brightpearl is a great basis for creating a cash flow forecast – but you need a few Excel tricks to turn the data into something that is quick and easy to use.  Here is a guide to what we think is the best (and quickest) way to get a great start to a cash flow forecast. (Warning – this requires some reasonable Excel skills!)

The first step is to make sure your bank account is reconciled correctly in Brightpearl – you don’t want any stray transactions being included in the cash flow forecast. Need help reconciling in Brightpearl?

Once this is done, filter the general ledger report to cover the dates that you are interested in for the historic data and download this into Excel.

Copy the data so that there are two worksheets containing exactly the same thing.

In the first worksheet, keep only those transactions related to the bank account. (We’ll call this worksheet 1).  In the second worksheet, keep only those transactions NOT related to the bank account. (We’ll call this worksheet 2).  An easy way to do this is use the Data -> Filter function and use the ‘account’ column to select the data that you need to delete.

In worksheet 2, we need to sort the transactions by transaction id (ascending), debit amount (largest to smallest) and then credit amount (largest to smallest).

On worksheet 1 which has the bank account transactions, we need to add an additional column which tells us what that bank account transaction was primarily related to.  To do this, add another column (we usually put a column in to the right of the ‘account’ column) and give it a heading such as ‘account 2’ (or something more original!).  To populate this new column, use the Excel VLOOKUP function to match the transaction id in worksheet 1 with the transaction id in worksheet 2 and return the nominal code from worksheet 2.  Our formula on row 2 looks like this:

=VLOOKUP(A2,’Sheet 2′!A:E,5,FALSE)

A2 is the cell on worksheet 1 that has the transaction id in it

‘Sheet 2’!A:E is the table in worksheet 2 that has the lookup table in it (with the transaction id as the leftmost column)

5 is the position of the data within that table that we want to return

FALSE tells Excel that if it can’t find the transaction id, it mustn’t go and use the one in the following row

This formula needs to be copied to all the bank account transactions in worksheet 1.

You’ll notice that all the ‘PP’ (purchase payment) transactions now show ‘creditors control account’ (or ‘accounts payable’ in the US) as the account 2 value which isn’t that useful for a cash flow forecast – it would be better to see the nominal code that the invoice was originally raised against.  We can pull this in as follows.

On worksheet 2, filter the data by ‘type’ to bring up all the ‘PI’ and ‘PC’ type transactions.  Copy these to another worksheet which we’ll call worksheet 3.  Remove any rows where the account is ‘creditors control account’ or ‘accounts payable’.

Now sort worksheet 3.  Use contact (ascending), debit amount (largest to smallest) and then credit amount (largest to smallest).  Due to the peculiarities of the VLOOKUP function that we’re going to use on this data, we need to have the ‘contact’ column to the left of the ‘account’ column.  Insert a new column to the left of the ‘account’ column and copy the ‘contact’ data across.

Back on worksheet 1, filter it by type ‘PP’ to bring up all the purchase payment transactions that we need to change.

Change the VLOOKUP function for these rows to use a lookup on the contact name in worksheet 3 that we have just created – match the contact in worksheet 1 to the contact in worksheet 3 and return the account.  Our formula looks like this:

=VLOOKUP(G2,’Sheet 3′!E:F,2,FALSE)

Make sure this formula is copied to the remaining ‘PP’ transactions.

Also on worksheet 1, add an additional column which gives us the value of the transaction.  The general ledger download has two columns – debit and credit – but we need to combine this into one.  Simply add an extra column at the end called ‘value’ which is calculated by adding the value in ‘debit’ and subtracting the value in ‘credit’.  Ours looks like this:


Also add an additional column on worksheet 1 that gives you the time slice for which you want to report the transaction.  For example, if you are doing a cash flow forecast month by month, add a column that gives you the month for that transaction (you can use the formula =MONTH(date) for this).   If you are doing it weekly, make that column the week number and so on.

We can now generate the data for the cash flow forecast itself – the easiest way to do this is with a PivotTable.

On a new worksheet, insert a PivotTable.  The data to be used comes from worksheet 1, with its added columns.

When asked to choose the fields to be included in the PivotTable, select the ‘account 2’ field (or whatever name you gave it), the value and the time slice.  The time slice becomes the column for the PivotTable, ‘account 2’ becomes the row for the PivotTable and the value field is the value – this just needs to be changed so that it is summed rather than counted.  This PivotTable should then give you the basic information that you need for the cash flow forecast ‘historic’ section.

Excel won’t allow you to manipulate the PivotTable to any extent, so to make it more readable it is better to copy the data from the PivtoTable to a new worksheet and start work on it there.  You can then rearrange the rows as needed, change any labels to make it more meaningful and add colours where needed.  You can also add a row at the bottom to give you the running bank balance that is calculated from the data above it.

You can now also add in any future payments that Brightpearl doesn’t know about to complete your cash flow forecast.  There are a few things to look at.

– Future sales.  You will be able to see patterns in sales in previous periods, use this and your knowledge of your business to forecast the level of sales in the future.

– Consistent payments going out of the bank account that will carry on into the future (e.g. wages, insurance).  Once you have the PivotTable data you can easily spot patterns across the accounts and the time slices e.g. payments every month for wages, every quarter for VAT etc.  You can put in estimates for what these will be in future months.  Don’t forget to add periodic payments that may not have fallen within the data you selected e.g. Corporation Tax which is usually only paid once a year.

– Supplier payments that haven’t been recorded yet.  Look at your aged creditors (accounts payable) list to see who needs paying when and add those planned payments to the forecast.

– Customer payments that haven’t been received yet.  Look at your aged debtors (accounts receivable) list to see who still owes you money and add those receipts in as well.

If you need any help with creating your cash flow forecast, just get in touch!

Comments are closed.