Creating financial reports, the bane of every finance professional’s existence. You spend dozens of hours each month gathering, cleaning, and processing data in Excel to prepare clunky reports for leadership. However, most stakeholders do not have the same level of sophistication with Excel and often find these reports to be confusing. It’s no surprise, your average financial report is both overwhelming and under-informative.
It’s time that financial reporting entered the twenty-first century, by creating reports which are flexible and interactive. We’re here to help! Learn how to make more interactive, informative financial reports which will provide leadership with deeper insights, all while saving you time.
It’s time that financial reporting entered the twenty-first century. We’re here to help! Learn how to make interactive, and informative financial reports.These tips prevent you from repeating the same steps every month, while providing clearer insights to leadership. provide leadership with clearer insights and prevent you from repeating the same steps every month.
Prevent Errors from Breaking Your Financial Report
When creating a formula in Excel, always wrap the formula in the IFERROR function. The IFERROR function allows you to identify a custom result that will appear whenever your formula generates an error. This is useful in situations where your formula may end up dividing by zero, or where a VLOOKUP cannot find a lookup value.
Using Drop-Down Lists to Allow Leadership to Drill Down
A drop-down list allows your audience to select from a list of predefined options, such as countries or regions. You can use drop-down lists to create tables that will display different data based on your selection from the list. A common use is creating comparison tables so the viewer can compare results between different countries or regions. resulting in a more focused and interactive table.
How to create a drop down list:
First select the cell where you want the list to appear. In the ribbon at the top of the screen, click the “Data” tab and select “Data Validation.” Alternatively, you can skip the previous steps by sequentially pressing “ALT” > “A” > “V” > “V”. In the settings tab of the pop-up window, click on the “Allow” drop-down box and choose “List.” Finally, click the “Source” box, and then select your list range. You can also create a predefined list in a separate tab for the list range.
After creating the drop-down list, the cell can be used as a reference in the formulas that populate your table. For example, if you’re using a SUMIFS formula to show revenue by month for various countries, you can point to the dropdown list as a criteria for the SUMIFS search.
This allows the tables to update based on the data associated with the choice in the drop-down list.
Hide Overwhelming Data
As reports get filled with more information, they become increasingly overwhelming to look at. Luckily, Excel allows you to group columns or rows together to be quickly collapsed or expanded. For example, if you want to look at your company’s revenue throughout the year, you can view it per quarter. Or, you can expand a particular quarter to see how your company performed each month in that quarter. It becomes not only easier to read your spreadsheet, but also to access the level of detail that is most beneficial to your viewer.
How to group columns or rows together:
Highlight the rows or columns you wish to group and press sequentially pressing “ALT” > “A” > “G” > “G” (or alternatively, “SHIFT” + “ALT” + “→”). By having the group collapsed by default, you can initially only show top level data. If the viewer is interested in seeing more detail they can expand the group.
Provide Clarity By Displaying the Correct Title
When reports get printed and passed around, it is critical that people can easily identify where the information came from. To solve this you can display the title of your workbook in the first cell of the sheet, this is especially valuable when combined with implementing versions or dates into the file name. You don’t want leadership making decisions based on outdated information. The formulas below allow you to be certain that individuals reading your reports know what document and what version the information came from.
How to add the title of the Excel workbook in cell A1:
Paste the following formula into cell A1 to populate the cell with the file name. This will update whenever you save a new version of the file:
=MID(CELL(“filename”,A1),FIND([“,CELL(“filename”,A1))+1,FIND(“]”, CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1)
How to add the title of the Excel sheet in cell A2:
Paste the following formula into cell A2 to populate the cell with the sheet name. This will also update whenever you change the sheet name:
=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)
Push data into Excel on Demand
One of the most time consuming parts of creating financial reports in Excel is the process of getting the data into Excel. First, you have to download data from various sources. Then, you have to clean the data to ensure it is in a format that you can use. This manual process can take hours. By the time you are finished, the data in the report is already out of date. These issues can be remedied by utilizing a process automation tool such as the Alteryx APA Platform.
With the Alteryx APA Platform, users can automatically connect to various data sources, clean and manipulate data, and push data into existing Excel report templates. This means that you only have to build each Excel report once. An Alteryx workflow can be run to generate a new report with new data, utilizing the existing Excel template, in seconds. Our team has personally used this process to automate the monthly financial reporting process for an international media company, reducing working time from 240 hours to under a minute each month. Learn more about how we did it here.
If you and your team are still spending hours each week preparing financial reports, we can save you time and money. Learn more about Abada Solutions and Alteryx by visiting our website, messaging us on Linkedin, or using the contact box below.
Send us a message!