4 minute read: Discover the key elements of an effective data structure in Excel and learn why it is crucial for finance managers.
Mistakes.
They happen when you are collating and reporting on data using Microsoft Excel or Google sheets. Usually you have a block of numbers on a report and for some reason something goes wrong somewhere and you don't realise or you spend ages summarising the data on other tabs within the workbook.
In a small business you're unlikely to have a team of 'quants' to help you so you will be expected to provide good analysis straight away.
A well-organized data structure is the backbone to being a successful finance manager in a small business. Structing data in the right way will enable you to easily access, analyse, and reconcile data, leading to more accurate reporting and better confidence in the results.
OK, lets have a look at an example by looking at this sales report from the Shapes Company Limited. Lets pretend that you are a first time finance manager at the company and responsible for entering the sales at the end of each month into the report because the owner has asked you to track sales by sales team.
At first glance it looks great. The report shows the total sales for January along with the sales for both Team A and Team B. But what if the owner or sales team is looking to see sales in other ways.
In February, the owner decides to restructure the sales teams so that team A focuses on selling triangles, whilst team B can focus on selling Squares. Ok, so with a bit of restructuring you are now able to present the sales report for February and report that there wasn't much of an improvement:
The common mistake with this method is that the report is both collecting and reporting on data at the same time and that lack of a data structure means that extracting insights or changing reports in future requires a lot of 'locked in effort'.
Another issue with these type of 'locked in' reports is the limited flexibility and scalability of reporting. For example, reporting on just Johns sales, or looking at sales for John, Paul and Ringo together would require a whole new report calculation.
The key is to separate out the data from the report by putting all of the data into a table first and subsequently reporting off that table.
Luckily, Excel (and Google Sheets BTW) have provided a super solution with the tables feature. By putting the key sales information into a table first you will be able to run multiple queries in less time.
There are some great videos on YouTube on how exactly to create and use tables so I'll leave you to check them out separately.
For now I'm going to extract the main sales data from the report and format it as a table in the video below.
Now that the data is in a table format we will be able to run some analysis off of it. We are also going to be able to use one of Excel / Google Sheets super powers: the PivotTable.
Again, there are some great videos on YouTube on how exactly to create and use pivot tables which I encourage you to check out if you are not familiar with what they are and how they can be used. Suffice to say that if you have never used PivotTables you are about to gain a super power!
So lets run a quick report off the table using a PivotTable to get a couple of pieces of analysis. Lets run it for monthly sales for all sales people and products. BTW, don't worry about the fact that this analysis doesn't look anything as good as the original report from a sales presentation point of view - I will address that in my next blog by introducing you to the only three formulas you are going to need for almost all of your reporting needs.
As you can see, not only is it quick to analyse the original data it is also so much more flexible for creating different views of the same information that can help to draw out insights.
There is something else as well. There is a big advantage to using tables over just a range of data. When you use the "Format as Table" feature, Excel applies a predefined table style and applies some enhanced functionality. One of them is that the data is kept organised by keeping it in a dynamic range. If you add rows of data to the bottom of the table, excel dynamically adjusts and that means that as a finance manager you have a control check on things like total sales.
The eagle eyed might have noticed that the total for sales in the PivotTable in February was different to that of the original report. That's because there was an error in the original report and using tables helps to surface this. Other important attributes of a table are:
Table Styles: Excel provides various predefined table styles that you can choose from to apply a consistent and professional look to your data.
There are two longer term reasons why tables in Excel are superior to the default data structure used by most finance managers: