The Cash Planner, the IFMIS, the Spreadsheet, and the Very, Very Simple Objective

Posted by John Gardner 

The principle objective of central government cash flow planning is very, very simple. The aim is to forecast the total liquid cash resource available to the government at a point in time—typically the end of each day for the current month; the end of each week for the next two months; and then the end of each month to the fiscal year-end. This is one single figure per time period; a uni-dimensional time series; the profile of the government bank statement across the year.

It is not the purpose of this piece to go into detailed discussion on the reasons why getting that single number reasonably correct is so difficult for many countries. Neither is it necessary to dwell on the clear sense and benefits for a government to have that single sum held at a single bank account at the central bank—the TSA. It will also not go into why the rest of government—budget departments, spending units, and revenue agencies alike—can rarely understand that there is this one clear objective. Government agencies in many developed, as well as developing, countries often attribute nefarious ulterior motives to these cash flow projections. They believe that the cash manager is given the authority to obtain cash planning data in order to exert secretive powers over the execution of the budget. If provided with true plans of a spending unit, the cash manager will conspire with the budget department to ration the amount of cash available, or somehow manage to control planned procurement policies—steal some of the power of the institution. Such motives would not be very, very simple—but the sole objective of cash planning is.

These thoughts were inspired recently by a query—once again—on the use of complex modern IT systems to produce cash flow plans; and a presentation by an international software house on their public debt and cash management software. Almost every off-the-shelf IFMIS and debt management system offered to central governments makes the claim that it includes a cash management module which can handle each and every requirement of this nature. Should the government, when implementing such a system, purchase, configure, and customize the cash management module to aid in its cash planning? Despite already hearing the howls of consternation from the software houses, generally the answer is likely to be “No”—especially in developing countries.

Owing to the simplicity of the objective, the calculations necessary for devising cash flow projections are also reasonably simple—primarily addition and subtraction, and elementary seasonal trend analysis from historical data. Much of the difficult forecasting of government revenues—using estimates of macroeconomic variables and trends—must be performed by the revenue departments or the macrofiscal unit in the ministry of finance. The timing and amounts of large payments associated with capital project expenditures must be part of project planning and management.

The main complexity for the cash manager lies in obtaining and inputting basic data into the model which are relatively accurate and updated. Ideally, these data should be provided directly by the spending units (and/or the budget department), and the revenue agencies without any additional work by the cash manager. The model then simply subtracts the anticipated outflows and adds the projected inflows to the opening bank balance to obtain the objective aggregate figures. This does not require great computing power or sophistication.

The main work of the cash manager centers on attempting to produce ever greater accuracy in the cash balance forecasts. This is achieved by detailed analysis of the actual outturns of expenditures and revenues against their respective projections. In many countries, it is this stage which determines the granularity required in aggregating the input data. For example, for a line ministry whose expenditures are primarily composed of salary payments, it can be quite sufficient to provide the model with the monthly wage bill for the whole ministry. Variations across and within months would be expected to be too small to be significant unless a major change has taken place—such as a new school opening or an across-the-board salary increase (which should have been notified to the cash manager in advance). Here the granularity is very rough.

Conversely, if a ministry’s expenditures are very volatile across a wide range of goods and services, the granularity needs to be far finer. Items other than salaries may need to be input on a sub-chapter or even line item basis. This allows the cash manager to see where forecast errors have occurred and hopefully avoid these in the future. Elementary seasonal trend analysis from the historical expenditure database can be used on the annual budget line item appropriation to see whether the line ministry is making an effort to produce accurate forecasts rather than simply ‘dividing by 12’. Training, incentives, and sanctions can then be used to ensure that accuracy is increased in the future.

Back to the IT! It can be seen from the above that, for much of the analysis in a developing country, the granularity of the forecast data will need to be fine. In many countries, the annual budget by chapter and sub-chapter of the economic classification should be sufficient to enable detailed error analysis. This is generally available from the budget department at the start of the fiscal year and includes annual budget appropriations, revenue estimates, and debt servicing figures. It is either in Excel format or can be produced and downloaded in that format by the IFMIS.

Once in spreadsheet form, the cash manager can readily expand it to the daily, weekly, and monthly timeframe as described above. The historical database can be used to produce seasonal trend profiles for each sub-chapter (or item) which is then overlaid on the annual budget appropriation to produce an initial estimate of the expenditure or revenue for the desired timeframe. This is all achievable automatically at the start of the fiscal year once the spreadsheet model has been devised and populated.

As the year progresses and further information and cash plans are provided to the cash manager by the agencies and the budget department, the spreadsheet model can be updated continuously giving more up-to-date cash balance profiles. The model can readily accommodate ‘what-if’ scenario tests. If MoF senior management, or spending and revenue agencies, need to test the in-year liquidity projections under certain changes or contingencies, this is simply achieved by alteration to the forecast inputs. If the cash manager is involved in active cash management, he can test the results of certain actions to be taken—such as varying the length of maturity for T-bill issues or term deposits and repos. Of particular importance in this category, he can readily simulate the effects of various amounts of buffer liquidity required to be kept in the TSA. This can be a highly complex calculation if done properly—incorporating probabilities of all types of expenditure and revenue variations and fiscal risks. Using a pre-configured simulation model can assist enormously in performing this calculation.

A very important contribution to this updating process can come from the incorporation of expenditure commitment information. Where an IFMIS is recording the expected dates and amounts of future payments for committed contracts, this information can be loaded into the spreadsheet model on a regular basis. A replica of this spreadsheet should also be kept populated with actual outturn figures as these become available—again readily imported from an IFMIS in Excel format. Automated error checking can be run to highlight significant deviations from forecasts and these can then be investigated further to help improve future accuracy.

Should cash planning be built into a debt recording and management system? It is clear that the primary objective of the cash flow planning work—the cash balance—is an aggregate of the entire budget incorporating all revenue and expenditure line items. There is no point in using a dedicated debt recording and management system for this purpose. The debt database is not relevant for the work and would need to be vastly expanded to include a full, updated cash plan. Whilst all the debt systems providers will say that this can be done, it will be highly expensive and inappropriate.

Should cash planning be built into the IFMIS? The IFMIS runs on the operating chart of accounts and therefore has the full budget coverage required. If it has a budget preparation module, it has the functionality to change the elements in the proposed budget to compare and do certain ‘what-if’ tests although this is usually on an annual basis rather than on the necessary daily, weekly or monthly basis. If commitment controls are in place and being efficiently entered into the IFMIS as commitments are made (a very big IF in many countries), this can be a valuable part of the necessary cash planning data. Taken together, however, these factors do not make a cash planning model as described above.

Most, if not all, of the high powered IFMIS solutions have a cash management module but this is by nature highly automated in its operations. Given the current bank balances, it can adjust these for payments made and received through the associated modules and the general ledger. It can produce a forward-looking cash balance profile by adjusting the current cash total for committed payments in the future which it knows about. Different expenditure plans or changes to anticipated revenue inflows cannot be incorporated into such a cash management module, nor can ‘what-if’ tests be run on this automated system. The system must be customized substantially in order to be adapted to the requirements of the cash manager. This again will be extremely costly and is unlikely to meet all requirements either more effectively or more flexibly than a home-grown spreadsheet model.

International experience and, hopefully, the above arguments tend to favor the conclusion that a central government in-year cash flow planning system can be more efficient and more cost-effective if it is purposely built using modern spreadsheet tools rather than a large integrated IT package designed to perform a great many other tasks. Cash planning, and particularly its need to become more accurate over time, are often rather subjective abilities. They must rely heavily on relationships and the capacity to persuade others to make an effort to provide good planning data. These factors are not easily built into huge integrated systems. A spreadsheet can be built with a large degree of flexibility which entails the idea that, for good cash planning, individual forecasts cannot, and should not, be as accurate as accounting standards, but must contain all significant, updated financial cash flow expectations.

There is no need to ask the software salespersons what they think about this issue. What do you think?

Note: The posts on the IMF PFM Blog should not be reported as representing the views of the IMF. The views expressed are those of the authors and do not necessarily represent those of the IMF or IMF policy.