Does Your Director Know Your Company Is Wasting Money Copy Pasting Reports?
Creating reports is a necessary part of working at almost any company. It allows executives, managers, directors and business owners the ability to gain information from multiple departments and then make effective decisions. It is vital that executives have this information so they can make clear decisions. In this data driven world, it is not a competitive advantage to have this information, it is required to survive.
However, there are hundreds of hours wasted by employees spent making reports(which translates to thousands to hundreds of thousands of dollars depending on company size). There are many causes to this. This can be due to copy pasting and bad coding/technical debt. Don’t worry! In this current era of automation and centralized systems there are solutions.
Where Is This Time Wasted?
Copy Pasting
One of the most common methods our team sees departments using to update reports is a copy paste method. Simply pulling the data from the company data source (like a data warehouse, or ERP) and then pasting it into a premade report.
It costs companies thousands of employee hours a year. On top of that, it creates consistency issues as new employees are hired and there is no time to train them. Instead, they are quickly told how to upkeep specific reports but rarely given a full explanation.
The overall costs of using this method can be tens to hundreds of thousands of dollars across a large organization. A one to two hour task every few days can quickly rack up employee hours.
VBA Scripting
Programming of any kind requires a specialist. Often this means high rates per hour. One very common method of automation is to use VBA scripting. This is a timely methodology that requires the technical know how and is very difficult to manage.
VBA is a great solution for automation. However, it is not easy to reproduce. Each excel needs to contain its own set of code. This is not a good practice. Having code in multiple different Excels causes lots of technical debt. Often times, when the original developer leaves, the report will fail. Especially if there needs to be an update. This is because the more files required to maintain, requires more time. Thus, it is very expensive.
Most of the updates will require going into multiple Excels code being updated. Rather than having one centralized code base.
How to fix these problems?
A great idea is to create a centralized reporting system.
This can take on many different forms. Our team has several solutions that can help reduce the costs to create accurate reporting. Some are custom built, others are third party solutions. We only reference two below. However, there are a lot more options besides the two listed below(Tableau and Power BI).
Tableau
Tableau has created a data visualization tool that allows analysts and BI experts to create reports and share them across departments with ease. In the past few years it has created the ability to connect to most of the crucial data sources. This includes most of the RDBMSs like Oracle, MySQL, MSSQL, etc, as well as Hadoop, Salesforce, and a giant list of other data sources.
This ease of connection makes creating reports simple. On top of that, it’s drag and drop feel makes the creation of reports intuitive. With proper requirements, prototypes and final products can be created in hours and days not weeks and months.
Not only is creating reports simple, but updating the data no longer requires a copy paste, or a click of a button. A data analyst can set the report to be updated every minute, every hour, or even live(although, with live you have to be careful how much data is being called each time). The report connects directly to a data source instead of having the static information held prisoner inside its cells.
It even connects to active directory so your team can manage permissions.
Tableau also allows for python to be inserted inside it using TabPy.
Powershell/Python
Powershell and python are just two great scripting languages that can be used in replacement of VBA. The reason to use these two languages over VBA is that it allows teams to create one reporting system that is easy to change, update, and maintain. We discussed the issue of VBA creating code in a single location. Any time this occurs, it is asking for technical debt. A centralized system makes it much easier to update code because there is only one location to update as long as you develop a robust system.
This drastically reduces the time spent by high cost employees on simple reporting projects that could be simplified.
Our team has developed a set of scripts that can take on almost any excel report, with any amount of parameters, sheets, graphs, etc and have a system that can produce it. This helped reduce the amount of time wasted on VBA, power query and copy paste reporting by 80%. The team we worked with no longer had to create a custom script. They just had to add the report metadata to the database, and the system was able to run the report from there. This drastically simplified report requests that came to the BI team. They no longer needed someone to develop a VBA report. Instead, all that was required was the excel template and the SQL queries.
Power BI
Microsoft developed Power BI a decade after Tableau in order to compete with Tableau. Tableau and Power BI have a lot of similar qualities.
It allows easy connections to data systems, easy sharing as the Power BI is connected with Microsoft 365 and easy to create reporting. If you are good with Excel, then it is not to hard to learn all the fancy new bells and whistles.
One limitation of Power BI is that it is only available as a Saas. This does limit it somewhat. However, it also offers a very competitive rate compared to Tableau and is much more user friendly. If your company does not have a strong BI team, but wants to develop easy to maintain reporting. Power BI will save your team hours of time.
If you don’t happen to have a data warehouse yet. Our team can help
In The End
Reports are a necessary part of helping executives make good decisions. However, using methods like copy and paste and VBA scripting are not the most effective way the provide directors the data they need. These methods are not scale-able. Instead, consider creating a more robust system, or utilizing the various reporting systems created by Tableau and Microsoft.
The amount of time saved becomes rapidly apparent with good design!
If you team needs help design good reporting, data warehouses, or metrics, then feel free to reach out. Our team has experience in multiple fields including healthcare, operations, social media, retail, etc.
If you would like to read more articles about data, data science or analytics:
Good Data Science Requires Good Data Quality
How To Survive Corporate Politics As A Data Scientist
The Top 8 Machine Learning Python Libraries
Why Invest In A Data Warehouse