While it is key to keep business agile and adaptable, the inherent changes that come as a by-product are not without challenges. Consequently, the purpose of internal documentation, reporting, and overall processes may need to be re-evaluated. Legacy processes and documentation can hinder the agility required to stay competitive.

But as the saying goes “Rome wasn’t built in a day”. The first step in identifying legacy spreadsheets, systems and processes is to challenge the underlying assumptions of their existence to begin with, as well as to re-assess the range of stakeholders that are involved in or receiving their outputs. This is something that will often change over time, however it’s important to review:

It is essential that every sheet contribute to your process improvement, not just reformatting the data for the sake of it. Establishing the purpose of each sheet as a key piece in the smooth running of the overall process allows you map the way you deliver KPIs systematically, knowing how data flows from (a) collation, (b) processing through to (c) reporting. At this stage you should map which stakeholders need to access what data, at what point in time. A common issue companies face has to do with using one spreadsheet for multiple purposes.

The advantage here is that data is in one place, the disadvantage however has to do with data access, and it may limit the reporting capabilities. Aim to make key data as self-serve as possible for stakeholders. Here are two examples of restructuring your data flow:

• If you identified data errors in your analytics outputs, you may introduce a spreadsheet that fixes those errors as a subsequent step – this is a more reliable workbook for stakeholders to use.
• If you struggle to effectively combine data from various sources, you may introduce an additional sheet in your process that transforms your unstructured raw data into a clean data set for stakeholders to use.

Differentiate between a “working sheet” and a “customer-facing sheet”. The audience’s time is valuable, and stakeholders (both internal and external such as clients) should not need to manipulate data to get what they need. Challenge your assumptions and find exactly what data and in what format your audience needs and aim to deliver that. Present data in a user-friendly manner, focusing on a single source of truth with consolidated charts and tables if necessary.

You don’t want your stakeholders having to merge or synthetise data they receive from you, especially if it’s data from different sources. Similarly, the audience of your reports does not need to ‘see how the sausage is made’, they most likely need insights to inform their decision-making.

Optimize existing data sources if available. Whether a spreadsheet serves a particular purpose at the data collation or data processing stage, avoid circulating multiple sheets with the same data. If optimization is not possible, understand why and ensure only one version is in use.

In line with point 2 – you must ensure that your stakeholders receive the necessary information effectively. Ask yourself if this spreadsheet is a useful element in the overall process – if it is redundant, streamline your process by removing it.

If the purpose it serves is still relevant, ask yourself if there is a better way to compute or present the data. Utilize conditional formatting, charts, graphs, and clear labelling to enhance data presentation. Include time frames, relevant scales, and comparisons where needed.

Some reports are a good idea at the time, but they may be rendered useless for future use(s) for a variety of reasons. When introducing a new spreadsheet in the process, delineate its purpose clearly to make sure reports are easily alterable for future use.

For example, for product or service launches, design reports that require minimal adjustments for subsequent launches such as altering a few dropdowns.

Dropdown lists and pivot tables are underused resources and they are great tools to enhance functionality. Let’s say you are looking to provide your stakeholders with easy-to-use self-serve dashboard reporting. The challenge is that you need to include multiple filters across datasets and using macros is not an option. Add a SUMIFS function and a Dropdown by Postcode/zip code, City, County, for example, and suddenly your data is instantly cleaner. Get creative with data grouping and dropdown options – it takes minutes to set up and it could save you hours in graph and chart design!

If data is manipulated after distribution, collaborate with stakeholders to automate data collation and collection, ensuring their needs are met efficiently. Automating parts of your workflow is key to saving money and to boosting productivity. The caveat here is that process automation requires very careful planning and mindfully understanding internal procedures and the stakeholders’ needs. Otherwise, automation can introduce more problems than it solves. Another caveat and a cautionary tale, it’s important to view automation as a way to make your stakeholders’ lives easier, rather than replacing human effort for the sake of saving money. Understanding the pain points of your internal stakeholders should come first before thinking about technology and tools. Having a “people first” approach to automation is your first step to business success. The second step is to leverage technology to automate repetitive tasks that are otherwise done manually. The quick win here is that automation reduces human error and buys your business back considerable time and, thus, money by improving efficiency, all the while making your teams’ lives easier.

By systematically reviewing and updating internal processes, businesses can maintain the agility needed to thrive competitively even in volatile environments.

More information on Purpose can be found here 8-simple-data-strategy-tips

Would you like to talk to us about ways to synthesise the purpose of your spreadsheets? We’d like to have a chat! Feel free to contact us for a free consultation.