I have always had an interest in the practical aspects of financial modelling, especially with respect to forecasting and planning. I remember spreadsheet applications like Supercalc, Multiplan and Viscalc (the forerunners of Lotus and the now-ubiquitous Excel) when they were in their infancy, and what a massive impact they had on finance teams’ productivity and planning capabilities. Go into just about any enterprise and you’ll find models (typically spreadsheets) that are used to simulate many aspects of a business. But there is likely to be a preponderance of revenue/margin models and very few cash planning models, despite the fact that a shortfall of cash is an existential threat to any enterprise.

There are many reasons for this, but I am going to focus on just one: the sheer practicalities of modelling cash flow with a good degree of confidence over anything but the short term. I will discuss the practical, technical aspects of cash flow planning that I have learned working with various technologies over the last 30 years, and how I believe that current technology really is the enabler.

The Reality and Challenges

Anyone who has built a cash flow planning model will be familiar with the challenges:

• Data import and verification of the actual data for the starting point and the run-off into the plan periods.
• Collection and payment profiles of the plan periods arising (mostly) from the planned P&L. The plan is not transactional, so driver assumptions must be used to simulate collections and payments through percentage profiles or debtor/creditor days.
• Calendar profiles need to be applied to elements that are driven more by the balance sheet
• Payroll and related taxes, pension elements etc.
• VAT (applicable rates vary by types of income and expenditure;, on-account payments may be required)
• Rentals, leases, audit fees – all those items in the P&L that may be accrued monthly but that affect the cash quarterly, annually etc.
• The list can seem endless – Capex, bonus payments, corporation tax, dividends, financing – significant cash flows that require additional modelling to reflect the planned P&L and its supporting schedules.
• The model must also take into account the sign conventions used in the P&L and balance sheet since it will typically be part of a wider business model that may be using a management account rather than a trial balance sign convention.
• There is also the question of whether to use all this to produce a balance sheet plan and then derive an indirect cash flow statement or to model the drivers to produce a direct cash flow down to the level of the P&L. 
• To be reliable it must effectively be based on double-entry to ensure that the planned balance sheet does actually balance.

All of this can be, and so often is, modelled in spreadsheets. But the layers of complexity and risk of corruption of the model mean that it remains the purview of a select few individuals with little participation or involvement in operational management. Managing changes to reflect new elements of a business is difficult without time-consuming maintenance, and what-if modelling can be very challenging thanks to complex and dynamic relationships across multiple models that form the core plan. A spreadsheet is not a database, it is a personal productivity tool. Data, structure and business rules are all mixed together in an insecure environment that is prone to corruption. In my view, even the most sophisticated cash flow spreadsheet is no more than a prototype.

A modern database platform with its own calculation engine separates data from a structure in an integrated business model, and it models the relationships between the structures (e.g. revenue/billing affects debtors and VAT). The addition of new entities, account codes, lines of business etc. just represents adding a new structure, which may need new parameters, but the business rules are unaffected. Any requirement for what-if scenarios is facilitated simply through duplicating a plan’s data set and then flexing the necessary parameters to assess the impact. Movements on the planned accounts will automatically impact the relevant balance sheet accounts, and the associated collection/payment profiles are parameter-driven to reflect the cash behaviours that decrease the balances. Overlay all the other “calendar” cash items, and you can deliver a consistent, data-driven balance sheet plan that is wholly consistent with the P&L plan whereby the cash flow is a simple by-product. 

However, having defined the driving relationships between all the planned accounts, it does not have to be limited to the level of the detail of the balance sheet. The cash drivers may be planned at the same level as the P&L, to deliver a direct cash flow output with the collection and payment profiles tuned to each operating plan. Without an appropriate platform, this can add an enormous level of complexity and data to the planning model. Cloud platforms and database technology support scalability and accessibility for fully integrated business models in one, secure place, rather than a multiplicity of duplicated business models that may be inconsistent with each other. Just as the advent of spreadsheets and personal computers did in the 1980s, the creation of multiple scenarios accessible to multiple stakeholders in a collaborative, web-based environment is driving planning capability.

Technology is at, or near, the point where extension kits, rest APIs plus legacy connectivity ensure that seamless integration across applications is part of business as usual. Business structures and data are automatically synchronised with the underlying systems to minimise maintenance and enable rapid turnaround of plans.

Summary

The cloud holds some exciting prospects for the development of cash flow planning. It will bring access to Machine Learning and Predictive Analytics on a scale previously only accessible to data scientists. The volumes of historical data available for deep learning will provide a rich vein of content to produce benchmark forecasts against which businesses will be able to track forecast bias and forecast accuracy, and in the process improve insight into all aspects of business drivers as well as cash.

I believe that cloud technology (whether private or public multi-tenanted) has reached a critical mass whereby it has become the driving force of business software development, as well as a fast-growing de facto standard platform for many organisations. FP&A applications are already exploiting the scalability and capability of cloud platforms to support complex business models, so now is the time to leverage those capabilities for your organisation.