How to Create Modern Multi-Tab Financial Reports with Excel
This article will discuss accelerated, streamlined ways to organize your data in modern multi-tab reports with powerful third party financial reporting tools, like BI360.
I recently had the pleasure of sitting down with Senior Consultant at Solver, Stephanie Gamber, after her popular session at the Annual BI360 User Conference, Focus 2014. Gamber presented on the topic of multi-tab Excel reports, an extremely relevant activity to accountants and CFOs as corporations grow in locations, divisions, or departments. The traditional way of building multi-tab Excel reports can be nearly unmanageable or inaccessible based on the vast size, depending on the number and/or type of tabs that you might employ to break down your data for different analytics. This article will zoom in on the limitations of traditional multi-tab reporting and the powerful options you have for more streamlined financial statement generation.
In discussing multi-tab reporting as we traditionally know it, Gamber uses an example of a company that needs to run 106 instances of the same report for different entities. Because the sample company has 100 locations, they would need to run 100 location-specific financial reports, as well as 5 region-specific consolidated statements for regional managers with all of the locations’ financials within the designated areas included, and a comprehensive, overarching statement for the executives at the corporate headquarters. This process in a product like Management Reporter and its predecessor FRx, which both use a Reporting Tree concept, is arguably straightforward, but the end result is not a reasonably digestible presentation of company data unless the end result is exported to Excel after every time the reports are executed.
Using an older (and in this case, retired) product like FRx, the process can be tedious. First of all, you will close your books in your accounting system, move to FRx, run the reports using a Reporting Tree and then export to Excel to get the desired multi-tab Excel workbook – and do it all over again if there is a change in the GL, and repeat the process every month. In this example, we would run a report with 106 tabs, specified for locations, regions, and the corporate headquarters. You would then check for variances – and make any adjustments to journal entries to ensure that everything is accurate and working for the final workbook of reports. Once you make changes, you have to re-execute the updated 106 tab Profit & Loss (P&L) multi-tab reports, trying to wait patiently as the report replicates. Then, distributing these reports becomes the next obstacle.
There are a couple benefits to large, multi-tab reports, but the disadvantages tend to outweigh the advantages. With a traditional multi-tab report you create using FRx or even right in Excel, you have a single file you can distribute to users, including all of your departments, regions, locations, and/or any other entities you decide. You can use this larger file to reconcile and analyze without running multiple individual reports. However, due to so many tabs, report execution can be pretty sluggish. Any General Ledger changes you make translates to another complete re-execution of the report.
In addition, many FRx and Management Reporter customers don’t want all their users to see all the tabs in the Excel workbook that has all the reports, so the administrator will often manually chop up the workbook into numerous individual Excel files to send to the appropriate end users. Perhaps the most obnoxious obstacle involves file size.
With such a large file size, you are likely to run into some problems with distribution. First of all, when you go to e-mail out your multi-tab reports, you may exceed size limits for delivery, meaning that you have to rely on a shared drive to get the statements to involved parties. Additionally, you might experience memory issues in Excel, given the potentially vast amount of input data. And these massive multi-tab reports will likely be slow to open and navigate through to find relevant data. Finally, the number of worksheets may be confusing to users – or overwhelming for that matter. If it is not easy to access and utilize included data, some users might give up on the (traditional) multi-tab reports. The “old” method of multi-tab reports might be beneficial up to a certain number of worksheets, but in older products, there are major limitations to presentation customization. If you have or plan on eventually having extremely large workbooks with a lot of tabs, alternatives should be considered for a more effective financial reporting process.
With a more modern tool for financial reporting like Solver’s BI360, you can build smarter, streamlined financial statements with multiple tabs that will not slow you down or overwhelm recipients. With ways to roll up the large amounts of data into more succinct categorizations, you will be producing significantly less tabs, while still presenting all of your data in clearer, digestible ways. Because BI360 is a modern, 3rd generation Excel add-in, you are doing all of your report designing right in the spreadsheet program, more specifically you can harness the power of Excel when building formulas or applying conditional formatting, sparklines, charts, and graphs.
Again, we’ll use the company with previously 106 tabs to illustrate the more streamlined options you have for multi-tab reporting. Instead of running the 106 tab report for analysis after closing your books in the accounting system, you can run a “control report,” which allows you to isolate variances and identify any issues quickly. The control report in this example would be a single tab report, listing all locations, with conditional formatting to quickly identify discrepancies. The user could easily isolate variances over a certain percentage by filtering on the data. With a couple mouse clicks, they are able to isolate all accounts with a variance of greater than 10% for further review. They can then investigate if there are any issues in the control report and make any necessary changes to the GL. Once they re-run the control report and verify all adjustments, the accountant can schedule the BI360 Report Publisher for secure, specified distributions of the completed income statement.
Based on each user’s security access, BI360 Report Publisher will automatically send out a different version of the same income statement to each recipient. For example, location managers will receive a single P&L sheet for their location, regional managers will receive 1 multi-sheet P&L workbook with individual sheets for each of the locations in their region, plus a consolidated sheet for the region, and the corporate office will receive a consolidated report with 5 regional sheets behind it. This way, sensitive material securely, concisely, and automatically gets into the right hands without sluggish report processing, a 106-sheet workbook, overwhelming navigation, or file size delivery obstacles.
Stephanie Gamber provided a lot of smart designs for multi-tab financial reporting to meet your specific business needs. For these suggested report designs, the video of her session is available through Solver University, the free-to-customers, ongoing product training and best practices portal. If you are already a customer, you can log into Solver University and search for Multi-tab Reports. Improving your multi-tab reporting processes can save time, energy, and money, but can be confusing if you are used to traditional methods. Solver would be happy to answer questions and generally review BI360’s easy-to-use Reporting solution for collaborative, streamlined decision-making capabilities.
