Excel is still a popular tool when it comes to preparing financials or analysis. However, we often hear financial professionals complaining about how inadequate Excel can be. So why have we not “rid” ourselves of this seemingly “inadequate” tool. This article explores the pros and cons of using spreadsheets. There are a few best practice tips that may be helpful to the vast majority of spreadsheet users.
 
First, Excel has been used synonymously with the term spreadsheet but Excel is a proprietary version of spreadsheets. There is Lotus for those who remember the days of DOS. Lotus was officially retired in 2013. For web apps, there are Google sheets, Yahoo sheets and Zoho sheets to name a few. It is spreadsheets that we really can't rid ourselves of.

What is wrong with spreadsheets?

The first drawback is that spreadsheets are susceptible to human error. Simple data input error can topple the best build model and lead to catastrophic consequences. Spreadsheets are also susceptible to fraudulent manipulations where there is a lack of suitable controls.

Complex models can be difficult to troubleshoot. Linked spreadsheets can be spread across multiple folders or sites (from domestic sites to geographically diverse sites). There is also the risk of links not updating. Spreadsheet model tends to have a personal touch that does not lend itself to be agile nor for collaborative work.

Spreadsheets are not geared for scalability as the volume of data grows with the organisation. They can also be difficult and time-consuming when it comes to data / financial consolidation. Processing speed tends to be inversely proportional to the volume and complexity of models.

Last but not least, spreadsheets alone are not geared for developing analytics.

So why so many of us are still using spreadsheets?

When it comes to simple data entry or quick ad hoc work, spreadsheets are still the favourite. It is simple to use. More importantly, in this digital age, everyone is taught to use spreadsheets during their formal education. There are free online tips and videos on social media about the effective use of spreadsheets, which further promotes its popularity.

Spreadsheets are essentially cheap. Proprietary spreadsheet application such as Excel is part of the Microsoft office suite. Microsoft Excel 2016 introduced Power tools. Power tools enable spreadsheet users to handle a larger volume of data and enhance user experience in creating data visualisation. This appeal to spreadsheet users who have not yet ventured to alternative data visualization tools such as Tableau and Microsoft Power BI. These tools tend to have preference in getting data from the table layout - data stored in rows and columns, which is also the way spreadsheet data is arranged. This is distinguished from data in a Table format which has certain cell range features.

Where do we go from here?

If your company is still using spreadsheets for reporting, modelling and/or planning, here are a few useful best practices

• Keep it simple. Avoid linking worksheets or workbooks externally. If this is really necessary, ensure changes are communicated. Do not create daisy chain. Refer to the source for input as opposed to a cell that references the input.
• Document the objective, processes and procedures as well as change log in the model. Separate summaries, instructions, inputs, drivers, outputs, scenarios, calculations, assumptions, error checks, change log, source data in different tabs.
• Encrypt the model to prevent unauthorized access.
• Standardised naming conventions, model layout and structure. Create a consistent formula for a range through the use of absolute/mixed references. 
• Press Enter after completing entry to a cell instead of clicking away to another cell – this avoids inadvertently incorrect referencing in a formula.
• Mark the final version as Final which prevents further changes and makes the file as read-only.

Now that we have explored the drawbacks of using spreadsheets and why we are still using them, do you think we will eventually move away from spreadsheets in finance?

Disclaimer
Any views or opinions expressed are solely those of the author and do not necessarily represent those of The Warranty Group. The Warranty Group is part of Assurant.

The article was first published in Unit 4 Prevero Blog