loader image

Share:

Share on facebook
Share on twitter
Share on linkedin

5 Tips for better financial reporting in Excel

Our Favorite Tips For Financial Reporting in Excel

In this article I will cover a few helpful tips & tricks to improve your financial reporting and planning with Excel. In more than 20 years in the BI industry I still have yet to see a more versatile solution. All of us are still likely only using a fraction of what this tool can do and often -particular with all the game changing features added in the last few years- not up to its current capabilities. Here are my 5 favorite tips that help many of our clients to use Excel more effectively for financial reporting purposes:

Separate Data & Presentation

This is the essential one, on which I have also written dedicated blog: Excel A BI toy?. If you are using larger volumes of data from other sources available in a structured format, absolutely avoid copy & pasting it directly to Excel. Excel was never meant to be a database. You will only create a lot of maintenance and performance issues. This is 2018 there are fantastic options to establish a live connection to your data in Excel particularly supported by Power Query which allows you to integrate nearly any source under the sun super easily and transform it to your requirements. As opposed to copy and pasting, this approach will give you a dynamic link to your respective data sources and the spreadsheet will on refresh automatically reflecting the current state of the data. When using a flat file import it will either update based on the latest version of the file or with multiple flat files you can configure Power Query to link all files in an upload folder where the data of every new file will be automatically added to your data model.

Excel Formula Views

This one requires the tip before: formula reports require an “OLAP” data source for example Power Pivot (that can be link to nearly any source e.g. relational tables, etc.) when you want to store data locally in the sheet or solutions like SQL server Analysis Services (Tabular or Multi-Dimensional) as well as a variety of Third Party options in a client server configuration.

Formula views can be started from scratch or by converting a pivot table (keep in mind it is to best duplicate the Pivot Table beforehand as otherwise it will be lost). The benefit here is you have a report dynamically linked to your source data with complete flexibility in regards to the layout (asymmetric reports e.g. actual previous year/budget current year) as well as great options to update report structures based on formulas:

5 Tips for better financial reporting in Excel

Excel Pivot Tables

I am still amazed how many finance professionals are not making use of the unparalleled power of the Pivot Table which provides tremendous flexibility for reporting (and with using Acterys equally for planning!). My not so well-known tips here: Make use of drill through (double click on a cell) to get to the underlying records of a cell (unfortunately not supported with DirectQuery yet), use “Quick Explore” to drill into details for another dimension attribute and combine Pivot Tables with Pivot Charts. Here are a few examples:

Microsoft Power Business intelligence Planning Software | Bi Consulting Services | Acterys

Split Screen to work with two Pivot Tables

Quick Explore in Pivot Table
Quick Explore in Pivot Table

Insights Feature in Excel

Write-back on Pivot Table

With the Acterys Add-in you can also “write enable’ these formulas:

Conditional formatting

A great helper particular in conjunction with the Pivot table is conditional formatting (that can even be configured to apply only to particular measure tuples if you are using an OLAP source like Acterys). To help your audience understand data “at a glance and putting it in perspective for example using a bar conditional format will immediately show the user the significance of a cell before you have to consciously look at the number as well as pointing out outliers. My recommendation here: be subtle use colors not too hard on the eye and consider using  the conditional format to point out the type of cell e.g. by data scenario: using a white background color for normal cells and a shading for a different scenario like “budget” (particularly important when you are using Acterys where the users can enter data in the pivot table. This will immediately show them where they should enter)

General Excel Layout tips

The thousands of client spreadsheets that I have seen often contain absolutely crucial insights but more often than not presented in a way that is not necessarily wanting someone to spend more than the absolutely necessary time with them because they are so horribly ugly. Too often I see brash colored, grid nightmares. Here are a few tips:

5 Tips for better financial reporting in Excel

With the titling my tip: use a clear text box click on it and add a dynamic link in the formula bar e.g. =”Financial Results ”&$C$6 to make your title independent of all changes on the sheet and dynamically update according to details selected e.g. in this case by pointing to the cell that contains the time and the company name.

Available in: Dutch French Spanish

Recent Posts

Materialize and Synchronize Power BI with a Data Warehouse “Materialize” is a database concept that refers to storing the result

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.

Power BI offers unparalleled features for analysis and visualization: in this post we explain the quickest way to connect Oracle Netsuite in Power BI Deskop and how you can add comprehensive planning and budgeting.After your account has been setup, click on the “Integration” menu and go to the “Oracle Netsuite” section:

The default visuals in Power BI are not perfectly suited for financial reporting. There are ways to realize more advanced financial reporting requirements using DAX as Managility describes in Income Statement using DAX These, though typically require intermediate knowledge of DAX that many financial professional don’t necessarily have.