colea logo

Stefan Borenich

Jan 01, 2022

Still using Excel to collect data?

Getting lost in spreadsheet data

Welcome to 2023.

Many large organizations are still using Excel to collect data from their colleagues, subsidiaries or other departments. We at colea want to change this by helping companies to bring their data collection processes to a new level.

There are many scenarios in which people have to collect data inside an organization. Whether it is a typical forecast happening regularly, or a simple one time ad-hoc data request by senior management, the underlying requirements are often the same: Collect data from various entities and merge them into one consolidated report.

Repetitive data collection

Collections for sales forecasts, finance forecasts, planning activities or project reporting are mainly happening regularly - e.g. monthly. For those collections, companies have two options:

Either, they invest a large amount of money in order to implement a proper group wide CPM tool for your forecast and planning activities. Or they implement a collection process with Excel templates in which a central Finance unit prepares individual Excel templates for all departments. These templates would then be prefilled with actual data of an upstream ERP system. Then they would be shared with respective entities via email or shared folders. The colleagues of those entities fill out the templates and return them via email. This process usually has the following disadvantages:

  • You get the filled out spreadsheets too late.
  • They are filled out incorrectly.
  • There are additional columns/rows added.
  • It takes a lot of effort to consolidate them manually or via macro.
  • You may have to consolidate them twice or more when you receive updates.
  • You may have to transfer the consolidated data into another system.
  • You may have to do this every day/week/month/year.

Ad-hoc data collection

The issue with ad-hoc data collection is usually, that you want to get the information as quickly as possible. Results of the analysis of ad-hoc data collections are in many cases a basis for a decision-making process on senior management level.

Therefore, such kind of data is often collected via Excel and manually consolidated, so it can be prepared for Executive Management. This is also not a big problem in case you have only five entities from which you collect data. However, imagine you have 20 entities or more, - then this exercise gets quite difficult, is time-consuming and most of all, is error-prone. And no one wants to have an analysis based on wrong data due to copy/paste mistakes.

colea can help in both scenarios

With colea an employee can build a template in minutes by executing the following steps:

  1. Setup the users of your organization.

  2. Define a template which specifies what data you would like to collect. Use spreadsheet-like widgets in combination with classical form components.

  3. Specify who you want to collect data from. It can be colleagues, subsidiaries, departments or other stakeholders.

  4. Select how frequently you want the data to be collected. Whether it's an ad hoc request which occurs only once, or it is daily, weekly, quarterly, or yearly. You might also want to specify a deadline.

  5. Publish the collection. Your users are informed about the data collection request via email. While users enter data, you can monitor the progress by viewing the submission status dashboard.

  6. Collected data can be consumed at any time and in different ways:

    • Export data to Excel.

    • Connect Excel or Business Intelligence systems (like Tableau or Microsoft Power BI) to colea via Odata.

    • Push data to other platforms like Snowflake

With these six simple steps, the above-mentioned issues of both, ad-hoc and repetitive data collections are addressed. The setup is quick and easy, and it all comes with low costs. So even if you have a well working CPM solution in place, you can still use colea for ad-hoc collections or data that doesn't fit your CPM data structure.

So the next time you're thinking about creating an Excel Template for the purpose of data collection, give us a call.