Beyond the Spreadsheet

Have you ever navigated, lost, between 20 Excel spreadsheets, linked together, with obscure macros created by an employee who left the company several years ago?

Do you know that feeling, a mixture of panic-fear and deep annoyance when the trainee shifted a column by 3 cells, thus demolishing the entire structure of the data in your beautiful spreadsheets? Do you remember the last time you looked for information in your spreadsheets, information you had to calculate, without knowing how or where to look? Perhaps you’ve also experienced the weariness and pain of having to generate new figures for Powerpoint or reports, cursing this damn spreadsheet that doesn’t do what you want. Perhaps you even remember the day Steve from Accounting moved a file on the server, breaking the entire data chain, and taking all your beautiful dashboards out of service until the problem was identified and solved….

You probably made a face like that, torn between panic and resignation:

Femme interrogative devant son ordinateur

Face of an emerging crisis due to poor data management policy

 

Recognizing the symptoms

These memories, these sensations, are a sign that your data management is not adapted to their quantity and even less to your needs. In a company, data must be at the service of business intelligence, and certainly not be a burden for the employee or worse: a danger for the company. How many hours go to waste each year managing non-homogeneous, scattered, dirty data? How much money is lost because the information was not immediately available? Probably too much. Don’t worry, this situation is neither fixed nor insoluble.

Don’t get me wrong, I don’t criticize spreadsheets. I do not even question their effectiveness in a given context. They simply do not correspond to the use you have of your data. It’s like hammering a nail with a frying pan: it works, but it’s not what the pan is made for, and there’s a risk of collateral damage. Similarly, it would not occur to you to make your flyers and posters under Paint. Data management must therefore be approached in the same way as accounting. While it is possible for a very small company to do its own accounting, it becomes essential to use specialized software and companies as soon as the financial flows are a little more substantial. The same is true for data.

The problem(s) with spreadsheets

Why are spreadsheets no longer suitable from a certain point on? First, they do not constitute a sustainable storage method. As soon as a spreadsheet is editable, users can alter the structure of the data or even the data itself. Even the entire spreadsheet, by the way…. However, data is vital for the company’s operations. Second, a spreadsheet usually requires manual data entry, which is both a colossal waste of time and a risk of incorrect data entry. Third, spreadsheets have the disadvantage that all users have access to the same data. This raises confidentiality issues. Fourth, spreadsheets often produce poor quality, limited graphics. For example, it is difficult to make a map with a spreadsheet. It is also difficult to have homogeneous, vectorized, and professional figures to communicate in reports or presentations. Finally, spreadsheets do not allow data to be processed in a statistically satisfactory way. Spreadsheet data are generally under-exploited.

How to organize your data in an efficient, secure, and professional way? 

1. Storage

Data storage must be independent of data processing. This is essential. Data must be archived, securely, on redundant secure servers with a versioning system to avoid any loss. They must be structured in databases that can be relational (SQL) or not (noSQL). It is not absurd to create separate databases when the data are not linked to each other.

2. Data entry and aggregation

Data entry into databases must be as automated as possible. When automated, data entry is systematic, reliable and instantaneous. This avoids many problems. The downside is that automation require maintenance. When the entry is manual, the user should only be able to add/edit what he/she needs to add/edit. It is unnecessary and dangerous for the user to have access to everything. This requires designing input interfaces that are accessible through a login system and login hierarchy. Thus, it is very easy to partition access to information, and to manage the read/write rights of the data.

3. Separate processes

The production of figures and monitoring must be separated from data storage and entry. Why separate monitoring from data aggregation and storage? You have probably already tried to modify figures on spreadsheets with a lot of content. The spreadsheet becomes unstable and inaccessible for the entire department. When tasks are partitioned, the process of producing the figures first makes a local copy of the data it needs, and only the data it needs. The process can then work on this copy without impacting storage and aggregation in any way.

4. Professionnal Solutions

Specialized tools must be used for the production of professional figures and dashboards. Examples include the R language and its Shiny package, or the Python language and the Dash package. At this stage, it is often advisable to use a specialized company. Once again, just as it is natural to outsource IT maintenance and accounting in a concern for skills and quality, it makes sense to outsource your analytics. Well-structured, secure and reliable data then constitute an infinite playground for the Data Scientist. It is possible to answer any question supported by your data. For example, it is quite possible to calculate mathematically and very precisely forecasts of future sales, etc., and thus to be able to communicate them in a credible way.

Best of all, with well-structured data, a good Data Scientist or a specialized company can even implement custom machine learning models and AIs. This guarantees a precise management of your company.

Summary

If you only have one message to remember from this post, here it is: don’t hesitate to leave your spreadsheets for a more structured and efficient approach to your data. It is certainly an investment in time and money, but the benefits in terms of safety, comfort, and efficiency are immeasurable.

If you see yourself in this post, and you want to go beyond the spreadsheet for a serene and efficient management of your company, do not hesitate to contact us. We will be happy to assist you.