We are in another round of layoffs in many organizations. This time it is a slowing economy due to rising interest rates. In the past it has been different factors and the result is the same: fewer financial professionals doing the same (or more) work every month. Here are five tips to get those monthly financial reports done in less time.
Structure Excel worksheets so the charts update themselves
To handle all the extra work you are having to take on, you need to get more efficient at updating the regular charts used to create the financial reporting. Structure the worksheets in Excel so that the cells used in the analysis are separated from the cells used to create the chart. This way, the chart data table can adapt to the values from the analysis. This is one of the topics I cover in my free Presentation-Ready Excel Charts that are Easy to Update and Re-Use e-course.
Build charts using parameters so they handle different data
Any time you type in a value when calculating data for a chart it can restrict the chart to a small range of possible values. For example, if you use a calculation that assumes dollar values are in the hundreds of thousands, that calculation might not work if the data is now in the tens of millions. Instead, use functions in Excel such as CEILING.MATH to calculate parameters based on the data for the chart. That way, the chart can automatically adjust to the data.
Leverage the INDIRECT function to create similar charts
When you have to create charts for multiple regions or product lines, leverage the power of the INDIRECT function in Excel to make this easier. As I show in this article and video, you can quickly create a chart and a relevant chart title simply by changing the value in one cell.
Drive explanatory callouts from the chart data table
Instead of manually drawing lines and adding text boxes to charts to direct the viewer to the important part, use callouts that are created from data in the chart data table. In this video I show how to create a CAGR (compound annual growth rate) callout on a column chart. The line and CAGR label are calculated from the chart data table and adjust when the data changes.
Combine formulas and conditional formatting
It is important to highlight where results are above or below a previous value. Instead of changing indicators manually, combine formulas and conditional formatting in Excel to have the cells update automatically. This video shows you an example of creating formulas and setting conditional formatting rules to select the correct direction indicator and color.
These tips come from the content in my customized FinancialViz courses. In these courses I help financial professionals select the right visual to communicate a message and teach the expert-level skills in Excel to create the visuals without having to buy or learn any new tools. Get in touch to discuss a course for your team.
Dave Paradi has over twenty-two years of experience delivering customized training workshops to help business professionals improve their presentations. He has written ten books and over 600 articles on the topic of effective presentations and his ideas have appeared in publications around the world. His focus is on helping corporate professionals visually communicate the messages in their data so they don’t overwhelm and confuse executives. Dave is one of fewer than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel, PowerPoint, and Teams communities. His articles and videos on virtual presenting have been viewed over 4.8 million times and liked over 17,000 times on YouTube.