Help your clients manage their cash flow with Microsoft Office Accounting

By Gary Simon, UK Chartered Accountant

Right now clients are experiencing a severe downturn and everywhere they turn they are being exhorted to conserve cash or reminded of the old adage “Cash is King.” But few are well equipped to deal with the crisis. The truth is that most small clients find managing their cash flow a challenge. Unless they are market traders used to handling cash on a daily basis, chances are they will quickly lose sight of their current cash position, let alone forecast balances in a month or three months time.

The difficulty is usually down to several factors, chiefly, confusion between profits and cash, high transaction volumes which make it impossible to track their position in their heads and a failure to quantify or take tax liabilities into account. So what can be done to help them?

Some will inevitably turn to spreadsheets to try to keep tabs on their cash position. But modelling cash receipts and payments can be notoriously difficult for the small businessman or woman. Debtor days can be a bit of a mystery for the uninitiated, but even fewer will appreciate the subtleties of projecting receipts based on quotations, successful conversion (work won), orders placed and so on. Similarly, businesses quickly lose track of purchase order commitments, inbound purchase invoices and due dates for payments.

Building spreadsheet formulae that recognise the timing issues and correctly handle sales tax (VAT in the UK) in the process can present a severe test of competence in spreadsheet building as well as accounting principles. The result is that cash flow forecasting in a spreadsheet is beyond the grasp of most small businesses without a great deal of support from a knowledgeable accountant or bookkeeper. Even if the small client is able to overcome the hazards of spreadsheet modelling, the sheer effort needed to manually maintain the model can quickly overwhelm even the most determined individual.

Yet it should be possible to derive a cash flow automatically from an accounting or business system and this is exactly the premise underpinning the cash flow forecasting capability in Microsoft Office Accounting Professional (the functionality is not available in the free Express version of the software). In fact, cash flow forecasting enjoys a central position in the performance dashboard which is an indivisible part of the underlying accounting database.

 

Figure 1:Cash Flow forecasting enjoys a central position in Microsoft Office Accounting’s performance dashboard.

The first step in preparing a forecast is for the client to choose the period over which it wants to predict its cash balances. This is done by choosing the appropriate date range in weeks or months, from the link at the bottom of the Cash Flow pane. The chart shows cash balances on a daily basis (where the forecast period is between one week and one month) and on a monthly basis for periods between one month and a year.

Clicking on Forecast Cash Flow in the same pane reveals the building blocks used to develop the forecast. These are: 1) the Beginning (bank) Balance; 2) the Sales Forecast; 3) Cash Inflows; 4) Expense Forecast; and 5) Cash Outflows. Clicking on each of these tabs in turn shows the detailed basis on which the forecast is calculated. All of these items are generated automatically by the system but most importantly, the client has the capability to add or remove items as appropriate.

The forecast takes into account Known Cash Activity in the forecast period, i.e., the cash flow effect of documents that are already posted in the system between the current date and the end of the forecast range and includes items such as journal entries, checks to vendors, checks to employees and customer receipts.

 

Clicking on the Beginning Balance tab shows all of the system bank accounts that are included in the calculation.

 

Figure 2:The Beginning Balance tab shows all of the bank accounts that are included in the current forecast. One can de-select any choices by un-ticking the relevant check box.

 

The client can de-select certain bank accounts if it is known that they are irrelevant to a forecast, i.e., are not going to change materially over the period of the forecast, for example, Cash on Deposit for a fixed term or in another form of savings. Petty cash might also be eliminated on the basis that cash balances are normally too small to be of concern.

The sales forecast is based on the value of live sales orders, back orders and sales quotes in the system together with the estimated delivery dates created when they were set up. The client can edit these dates individually in the Sales Forecast tab to suggest a more realistic date if it is known. This helps improve the accuracy of the forecast. The amount can also be edited, for example, to reflect a specially negotiated payment schedule on a large invoice.

Editing the dates or amounts in the forecast does not affect the underlying sales order or quote. The client can also remove sales orders and quotes in their entirety, if despite the system held delivery date, they feel sure that no money will be received in the forecast period.

Cash inflows are derived from sales invoices less credit notes due in the forecast period (based on the aged debtors report). Once again clients can edit the amounts receivable and dates based on latest information and the cash inflows will adjust automatically without affecting the underlying accounting entries. As the forecast amounts are changed, the graphical display changes in real time as it recalculates the projected cash flow.

The expense forecast is generated from the value of purchase orders held on the system for goods and overheads and the estimated payment date. As before additional lines can be added, for example where purchase orders have been omitted, or to amend the forecast amounts and dates of orders recorded in the system without affecting the underlying documents.

Cash outflows are more predictable than orders since they are based on suppliers’ invoices entered on the system and other more certain events within a client’s control such as payroll runs and sales tax (VAT) payable. Nevertheless there is the opportunity to add more lines and edit details where necessary.

The obvious benefit of such rich cash flow functionality is that the forecast is automatically derived in front of the client’s eyes. No special knowledge is required to generate accurate results. Discussion with advisers can concentrate on tactics for managing cash flow rather than the mechanics of generating the forecast. It is a win-win situation for all concerned. The advisor can add real business value and the client gets the advice he needs to survive the downturn.

 

Gary Simon is the author of the popular book, “Microsoft Office Accounting to the MAX” which is available from FSN Publishing. Gary is a Microsoft Certified MPAN Consultant and runs training courses in Microsoft Office Accounting.  He is Group Publisher of FSN, a chartered accountant and a Fellow of the British Computer Society.

 

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.