You may also be interested in:

Articles

The Right Tool to Build an Efficient Cash Model

  • By Setareh Golchin
  • Published: 2/2/2016
analysis1Cash models come in different shapes and sizes. For some, a single page spreadsheet may suffice. For others, an elaborate format, layered with in-depth cash flow data is more appropriate. In any case, the accuracy and clarity of the information presented is paramount for efficient cash management. Having experience with manual cash models and treasury management systems (TMS), I’ve learned one must employ a keen eye and pay attention to the detail and quality of the work being provided. Mistakes happen and vigilance is essential no matter the complexity of the reporting system.

In the absence of a TMS, a cash model will invariably be constructed with Excel (or Numbers for Mac). With the use of its various graphic and calculation tools, pivot tables and formulae, a competent writer can use Excel to develop a detailed and complex report.

Creating a cash model

Building a cash model is a reasonably simple affair. The model builder should pay close attention to the accuracy of their formula construction. From simple, well thought-out beginnings, it is quite feasible to expand and create a more sophisticated and complex model encompassing income statements, cash flash statements, balance sheets and so on. A simple cash model can be created in a matter of hours.

In my experience, many cash models are used for many years and hardly get updated. This may be due to lack of Excel experience, which is an area that companies should concentrate on. As we move forward, processes are improved on a daily basis and so do cash models, hence companies who are avoiding the use of TMS should at least invest more in training their staff on Excel or Numbers and more importantly update their cash model every few years if not on a yearly basis.  

I created a cash model using Microsoft Excel for one of the FTSE250 companies that I used to work for. I decided to create a new model due to the fact that the model being used was quite old and hadn’t been updated for many years. Cash flow forecasting is a key management tool in any business, therefore it is vital that the cash model be updated. The model I created provided daily cash flow reporting, as well as weekly, monthly and quarterly forecasts; year-on-year trend and growth estimates; foreign currency valuation; and monthly treasury reporting. The workbook was interlinked with the daily online banking report, which was downloaded on a daily basis in a specific location. The workbook was updated just by hitting F5 and it provided you with the most up-to-date figures.

Building your own cash model can teach you a lot about the company and provide you more information on what the company must do to grow, what it’s worth and what the market expects from it. Therefore, the amount of time spent to build these cash models can often pay for itself many times over, by leading you to better manage the cash flow. Also, using Excel or Numbers permits full control over data, therefore it enables the model-builder to tailor the cash model to the particular needs of the business.
 
Challenges between TMS and Excel/Numbers

Excel/Numbers is one of the most used programs in corporate treasury. But even though it is a great engine for complex cash model reporting, it may not be feasible to serve complex processes, multifaceted database and run audit routines.

TMS are a good solution for larger organizations, especially those that have entities globally. But in my experience working with highly qualified treasurers, they believe Excel serves its purpose and is even more user friendly. Some treasurers also find TMS extremely pricy and unless there is a justifiable reason to migrate to TMS, they reject the idea of implementing these systems.

The major challenge sits with medium-sized companies that have enterprise resource planning (ERP) systems that cover the majority of their business activities. Many companies, apart from using both the TMS and ERP, still have to use Excel/Numbers as a transitional step in order to transfer the data from one system to another. This produces a lot of manual work, even though using a system should streamline the cash management reporting process.

Excel or Numbers may work perfectly alongside TMS. Nevertheless, a company cannot merely rely on them to produce efficient reports, especially when it comes to risk management. So both should rely on one another. Again, this depends on the size of the organization. Using Excel or Numbers solely could cause a lack of accuracy as it requires a lot of manual data entry. There are also security risks when transmitting the file online.

There are a lot of challenges a company could face, but more importantly, strategic goals and what treasury is planning to accomplish should be taken into consideration. Based on this, the treasurer should decide the system requirements or whether they are satisfied using spreadsheets.

Setareh Golchin is a corporate banking executive and a former treasury professional based in London.

A longer version of this article will appear in an upcoming edition of AFP Exchange.
Intermediate Excel Analytics for Treasury and Finance
On November 14 & 15, enhance your analytical capabilities of treasury and finance related applications on Excel. Hands-on examples will cover dashboard and user interaction, data preparation, data summarization and visualization, optimization, forecasting, and automation.
Learn More

Copyright © 2018 Association for Financial Professionals, Inc.
All rights reserved.