Abrar Rahman Arnob

Over the years, I have often been responsible for preparing financial statements (FS). The situation has almost always been the same. I was given an existing Excel sheet, a trial balance (TB), maybe some supporting documents, and then told to get it done by the end of the day. In the worst cases, it would take me a week to get it done.

During my article-ship days, these FSs were usually one-off and industry-specific. We didn’t really get a chance to create robust, repeatable processes for the task. The only exception was one particular months-long assignment that required multiple statements of similar entities. So, when I entered my first ever job and got assigned to prepare the financial statements, I jumped at the opportunity. I had a bunch of ideas that I wanted to implement. The goal was to create an Excel workbook that could take a TB and automatically create the required reports. I succeeded well enough, so much so that they were using the same workbook and system even after I left.

In my current job, I am still tasked with preparing FSs (among other responsibilities). I have learned a lot and refined my techniques. I want to share here two features and one function that I have used extensively.

Pivot Tables

I am pretty sure this one is obvious.

Pivot Table is an Excel feature that takes a table or range of data and allows you to summarize and analyze it based on various factors, columns or rows. Its utility is obvious once you think about it. A common situation that we find ourselves in when preparing an FS is that multiple TB heads need to be grouped for presentation. Pivot tables excel at these. You can set one mapping that holds the grouping head against each row of the TB. In the pivot table, the data gets automatically grouped by that column. With the help of the GETPIVOTDATA function, now you can just paste the TB data every month and refresh the pivot table to get your report.

Practically, it’s not that simple. Reporting requirements are complex. For example: at one point, I had a setup where I needed around five mapping columns.

Sadly, I haven’t been able to use the pivot table feature as much as I would like. Our requirement was that FS data needed to be traceable back to individual TB heads through using ‘CTRL + [‘ keyboard shortcut. Pivot tables with the GETPIVOTDATA function fail to meet this need. This brings us to the next feature.

Subtotal Tool

The initial data setup is the same. You need mapping columns against each row of your TB.

Excel has a handy tool under the ‘Data’ tab. It is called ‘subtotal.’ It requires you to select a range, specify the columns for subtotal base, and hit ‘Okay.’ Excel automatically creates subtotal and grand-total rows based on your specified columns. So, for example: if you have a set of rows that are mapped to your ‘Cash-in-Hand’ head and another set of rows that are mapped to ‘Inventory’ head, Excel will group these rows together, enter a subtotal column under each group, sum the total, and give you a grand-total column in the last row of the data range.

This allows you to link your notes or balance sheet heads directly to these subtotals. The formulae can be traced all the way back to the original TB heads with little difficulty. The real challenge in this approach is to link all the individual heads manually. It takes some time, but once the workbook is ready, with the help of VLOOKUP function, it is mostly a streamlined and easy process.

Sumifs

But sometimes, you just don’t wanna go through a lot of trouble. You have two mapping columns, and you need the data summarized in a separate sheet. This data will change about a hundred times in one day, and you need to see the summary to make decisions. What do you do?

Well, you could use pivot tables. The problem is that pivot tables keep crashing your Excel workbook if you refresh it too much in a short time.

Well then, sumifs function to the rescue. The function takes a summation range, multiple criteria ranges, and one cell reference for each criteria range. It doesn’t matter where your corresponding rows are in the TB. As long as the mapping columns are populated correctly, you are getting your summary report.

Closing Thoughts

I have always been of the opinion that routine work should be eliminated, and efficiency should be maximized without jeopardizing the ultimate result. Excel is a powerful tool. Proper utilization of built-in functions and tools can streamline a lot of reporting workflow. I am still learning and discovering new approaches to problems that better serve our (an my) specific requirements. It’s a continuous process.

I would be amiss to not mention the fact that my mentors and colleagues have always played an integral role in these endeavors. Their regular inputs and observations have helped me to discover new ways of thinking and doing things. I believe that knowledge sharing and cooperation have always been the core tools to achieving success in a corporate environment, and that they will continue to be so.