| 25-08-2016 | Lionel Pavey |
Do you still rely on spreadsheets in your daily treasury operations? We have read multiple articles on this subject lately and we decided to ask our community: Why do treasurers still rely on spreadsheets? Yesterday Jan Meulendijks gave us his opinion on the topic. Today expert Lionel Pavey talks about the benefits of using Excel in your company.
Why do companies use Excel?
Cost – it is part of the Microsoft Office Package; low maintenance costs
Use – everyone has some level of proficiency with Excel
Versatile – data can be customized to your own requirements
Simplicity – comes preloaded with over 400 different formulae, though far less than 100 are truly needed for Treasury purposes
Training – most people learn on the job, no need for expensive courses to help people use the software
Flexible – give the same data to different people and see how they uniquely extract the data they need to answer their queries
Compatibility – all relevant data that is present on standalone accounting software etc. can be exported into Excel and adjusted for individual purposes to achieve the desired results
Problems with Excel?
Ignorance – getting staff to comprehend the route from input to output
Errors – not incorporating checks and balances that can highlight discrepancies
Individualism – is the output only for your consumption or is it passed on down through the chain, enhanced and then passed on again?
Disarray – everyone applies different fonts, layouts, conditional formatting. Should be a company policy in place to determine how data is collated and presented
Uncertainty – why do people insist on hiding columns and rows?
Duplication – the same spreadsheet data is present on many PC’s at the same time with subtle but significant differences. Someone has to own the original document
Dedicated BI software – expensive, no value outside of the present company normally, requires regular maintenance, multiple departments have to sign off before it can even be implemented, constant reviews of whether the correct modules are present, system updates
Design Structure – implement a company policy clearly dictating how “shared” spreadsheets are to be designed.
Input Structure – agree who delivers what, to whom, when and in the agreed format
Share results – allow other people to see how their data has been incorporated into the final reports so they can appreciate the significance of their contribution
Ownership – define who owns what part of the process (their level of responsibility) and who owns the spreadsheet
Reports – ensure that the end users clearly define what they require at the start. 10 versions of a spreadsheet before they get what they wanted means they did not know what they wanted or did not communicate clearly
Excel is well known, robust, versatile and understood. For cash flow forecasting 4 or 5 secure “master” spreadsheets can allow for most situations – daily cash flow recording, future cash flow forecasting, agreed budget, capital expenditure plans, funding commitments. These have to be well protected and isolated on the hard drive. Everything is a trade off – nothing will give you 100 per cent accuracy. However, if you can relatively simply design the required spreadsheets then data is always up to date and available when needed. This covers the 80 per cent of the time maxim– the other 20 per cent you will have to work harder to achieve. Excel is not going away – every new versions even more functionality that allows us to achieve the required level of input more easily whilst ensuring that the output can be better analysed and interpreted.
Cash Management and Treasury Specialist