This site is intended for health professionals only

At the heart of general practice since 1960

Predict your


to avoid nasty


General practice is likely to be more business orientated in future ­ to compete successfully practices must be able to forecast income and expenditure, writes Dr John Couch

More and more practices are using cash-flow spreadsheets to predict planning for a whole financial year. Large companies often produce cash-flow forecasts several years in advance as an aid to planning. This advance planning is likely to become an increasingly important business tool for practices in coming years.

A major reason is that, just as in dentistry, it is predicted that many GP partnerships will face mergers and acquisitions as private companies are encouraged to enter primary care. A sound financial footing favours the strong in this process. A well-designed cashflow spreadsheet serves many purposes:

·it allows a detailed forecast of income and expenditure month by month

·it allows the latest reconciled bank account balance to be input, showing predicted current account balances in subsequent months. Adverse trends (for instance current account predicted to fall into the red) or advantageous trends (such as rising net profit allowing higher drawings) can then be spotted early and appropriate action taken

·it allows actual income expenditure to be compared with predicted income expenditure, highlighting problem areas for further analysis and/or action

·it allows easy application of a variety of 'what if' scenarios, such as the effects of giving up an external post or taking on extra staff. This facilitates planning.

While it is not difficult to design a financial year cash-flow spreadsheet, it is time-consuming first time round. It may take your practice manager about a week, but once completed, a template can be kept for future use, saving time in subsequent years.

Benefits of forecasting

As I have indicated, in an increasingly competitive business environment, practices must be able to see where they stand and to plan ahead. The following examples highlight some relevant scenarios.

Practice A want to modernise their premises in three years time. They have been told that unless there is a change in policy an improvement grant is unlikely. The estimated cost of the work is currently around £200,000. The five partners look at the option of a bank loan from 2009, costing each partner around £380 per month over 10 years or saving the money from drawings over the next three years.

This would reduce each partner's drawings by £1,110 per month but for three years only. They decide that the former option gives them the best cash-flow and drawings profile.

Practice B want to take on four sessions of GPSI work. Two partners will train up over the next 12 months. To cover their current clinical work the practice will take on a four-session associate GP as soon as possible. The training will cost £4,000. The associate GP will cost £45,000 per year including on-costs.

The GPSI work should eventually bring in around £55,000 per year. They factor these changes into their cash-low over three years and find they will make a net loss of £29,000 in this time with an annual profit of £10,000 thereafter. Drawings would need to be reduced by around £1,500 per partner per year for three years to allow for this.

They decide instead that the other partners will work an extra session a week for the first 12 months before taking on an associate, thereby saving £45,000.

Partnership C is considering buying out Partnership D, a two-partner practice with both partners retiring over the next three years. Partnership D want to sell their premises (current value £300,000) and become non-property owning partners for the remainder of their career.

Notional rent aside, they want parity shares and seniority. Partnership D's building is close to partnership C. The latter has ample room for extension. Partnership C studies D's accounts and enters various scenarios on their cash-flow workbook. They decide the most advantageous option is to amalgamate with practice D, running their premises as a branch surgery for a short time.

The buildings would then be sold on the open market. Meanwhile they will build an extension on their own, providing the PCT agrees to a transfer of notional rent. Each of practice D's partners will be replaced by an associate on their retirement. The cash-flow forecast suggests an extra £20,000 per year net profit per partner in three years' time.

How to set up cash-flow forecast

Expanding a financial year forecast into one spanning several years will involve setting up a workbook. In MS Excel this is relatively easy. Each workbook sheet will represent a financial year. Each sheet should be linked to the next via the end-of-year forecast current account balance. At the end of the financial year the actual balance is then substituted.

Most of the work is quick as it largely involves copying and pasting your template forecast into each workbook sheet. These sheets should then be labelled with the appropriate financial year.

Set up the links with the previous sheet. You will need to be careful in order to make 'what if' scenarios work properly across all the years covered in the workbook.

To do this, copy your original template on to Sheet One (current financial year). Make sure that each 'item' cell in each month's 'forecast' column is linked to the one before. In this way changes to, say, September's forecast staff expenditure will automatically register down subsequent months.

Do not include sub-totals and totals in this linking, or indeed any subsequent linking. Only the 'forecast' current account balance (or 'actual' balance for a past month) should be linked to the following month. Likewise do not link any 'actual' figures which must represent the month to which they apply.

Now copy Sheet One on to Sheet Two, linking the Year Two Month One 'forecast' to the last month of Year One item by item (once again excluding totals and sub-totals). Ensure the current account balance is also linked. Do the same for each subsequent year sheet, linking to the previous sheet as before.

Save a copy of the workbook and then check the formulae across the sheets to make sure your linking works. Once you have made any amendments save the final version and back it up.

Next, assuming you are partway through a financial year, copy and paste actual figures from your current spreadsheet into the relevant sheet of the workbook. If you do not currently have a spreadsheet, enter figures from your books manually. Once again check that figures are being automatically picked up in subsequent sheets.

When it is clear that a regular item has changed, such as after a staff pay rise, enter the new figure in the relevant cell of the next month's 'forecast' column. This will then automatically update all subsequent columns and sheets. If you find any cells with incorrect formulae, change these in the current workbook and the template. Back up after every new entry.

The long-term effect of changes on monthly and longer-term bank balances is vital. But do also explore all the options that this new business tool provides. These will often crystallise, for example, presentations and decision-making.

John Couch is a GP in Ashford, Middlesex

Rate this article 

Click to rate

  • 1 star out of 5
  • 2 stars out of 5
  • 3 stars out of 5
  • 4 stars out of 5
  • 5 stars out of 5

0 out of 5 stars

Have your say