You can create better reports. You don’t have to use complex and borderline incomprehensible tables. If you take just a couple of minutes, you can rethink your approach to creating and designing your financial report. In this article we present 3 excel report templates for the 3 cornerstone financial reports.
Note: If you prefer, you can watch our hour-long webinar that covers these excel report templates here: http://zebrabi.com/report-templates-webinar/.
Ditch the tables and create visual excel report templates
Instead of standard Excel-based tables (normally used by companies across the world) or gaudy charts, you can design eye-catching, easy-to-understand visual reports that drive engagement and deliver value to you and your business.
Regardless of the country or the industry you work in, odds are you have at one point or another created a variance report, a profit and loss statement or a cash flow forecast. These are the classic financial reports used by corporations across the world.
So, without further ado, here are the excel report templates for each of them explained, with recipes how to create them.
Template #1: Variance Report
When you worked with a variance report, you probably used something like this:
This is dull just to look at: An Excel table with some tinted columns and rows and too many numbers that fail to clearly communicate their relationships.
This report is not "wrong", it’s just very hard to read.
With every piece of information having the same visual weight, nothing stands out, so you must read each number individually to understand the information the report tries to convey. There is no way to easily discern the top performers or the laggards that affect your bottom line.
Even when you ask yourself a simple question, like "Are we on the budget?" the information is not readily available or immediately visible.
Luckily, there is a better way.
Use visual elements to expose information
You can take the exact same data set and use a smart data visualization approach to create a report that presents the exact same information and relationships visually.
Let’s take a look at what can be done:
This report has a clear chronological structure with previous year results on the left, actuals in the middle and budget on the right. This puts past data on the left, the present in the middle and the future on the right.
We used two types of charts:
- Bar charts
- Lollipop charts
With just a glance, you can view absolute values and their change on bar charts, while the lollipop charts represent relative growth or decrease in percentages. On the left is the variance to previous year, while the variance to budget is represented on the right.
Color-coding makes things clear
This approach brings visual clarity to the report that is impossible to achieve in a spreadsheet.
Once a user looks at this report, it takes only a couple of seconds to see that even with 10.2% yearly growth, Hungary and Western Europe are the main culprits for missing the budget.
The charts adopt a standardized color-coding system with green showing growth and red showing a decline while the length of the bar represents the value. This makes for a simple, yet effective representation.
However, visual simplicity does not mean that there isn’t plenty of data to delve into right there on the charts.
And this report is not hard to make. The list of ingredients is very short:
1 data set with AC, BU and PY figures
1 single-page template
2 Plus-Minus variance charts (for absolute variances)
2 Lollipop variance charts (for relative variances)
3 column markers
1 meaningful headline (take your time to come up with this one)
4 explanatory comments
Green means good and red means bad. But note that not every increase is good. For example, an increase in costs is bad just as a decrease in revenues is. If you use Zebra BI, you can easily define on each chart whether an increase or decrease is good or bad.
The key characteristic of this report is that the two bar variance charts and the two lollipop variance charts must be scaled, respectively.
Template #2: Profit & Loss Statement
Let’s move on to the Profit & Loss Statement. This is a mainstay of business communication and many companies use a similarly structured spreadsheet-based report with minor variations in design, such as fonts, colors and other details.
This is one of the typical Profit & Loss Excel report templates:
Again, a much more effective report can be created by taking the visual approach to displaying this data.
Instead of looking at numbers and trying to understand how they relate to each other, you can use waterfall charts that clearly show what contributes to revenue and what erodes your profit margins.
This is an example of what a redesigned P&L statement looks like:
Understand what drives or erodes your profit
This report uses the approach used internally by the German software giant SAP, and it is very logical and easy to understand. At its core are three types of charts:
- Waterfall charts
- Bar chart
- Lollipop chart
The first and second columns of waterfall charts show the different types of revenue and costs that make up total profit across actual and previous year. The top section of the chart shows revenue sources and growth that contribute to the overall profit, followed by costs, such as purchases, material and operating expenses and others that reduce the overall profit.
There is no effort involved in understanding this information as it is visually clear and obvious.
The length of the bar in the waterfall chart shows the positive or negative category contribution. The two columns of waterfall charts are then followed by a bar and a lollipop chart, which again show the absolute and relative variances, respectively.
The recipe is again very simple if you know what you’re trying to do:
1 P&L statement for current period
1 P&L statement for the previous year (PY)
1 single-page template
2 waterfall calculation charts
1 variance Plus-Minus chart
1 variance Lollipop chart
1 meaningful headline
3 explanatory comments
Growth can be both good and bad at the same time
Using Zebra BI, you can do some clever things with these charts.
For example, you can set whether increase/decrease in individual value is a positive or negative development. If you look at the variance charts above, you can see that an increase in revenues is marked green, while an increase in costs is marked red.
There is also another visual detail in this chart that you might have missed.
Look at the "Other revenue" and "Material expenses" in the lollipop chart. You can see that the lollipop circle has been replaced by an arrow.
We use this approach when a value is such an outlier that representing it to scale would throw off the visual balance of the chart. These outliers often occur in cases where very small absolute values have very large relative increases or decreases.
Here, we set the outliers to cut off at 100 percent and the value is displayed with the number.
Again, pay attention to scaling. While the bar chart can be extended to take up all available column width, it is imperative we scale it with the two waterfall charts. They all represent the same values in EUR, which means the three charts must be scaled.
This way, the reader gets the correct understanding of the relationship between them.
Template #3: Cash Flow Forecast Report
Cash flow forecasts are a story unto themselves.
They normally have lots of fields for individual months, weeks or even days. They are usually very detailed with planned incomes and expenditures to forecast the actual closing balance.
Spreadsheets like the one shown below are the norm for this report type.
However, since this spreadsheet is clearly not communicating anything, people do sometimes attempt to visualize this information and the results are all over the place.
Just try to do an image search for “cash flow chart” and you’ll see just how difficult it can be to create a chart that is easy to read and understandable.
Here is a better solution:
For visualizing cash flow forecasts, we're again using the waterfall chart, which is perfect as it shows the accumulation or reduction of cash flow within a period.
If you look at the cash flow from operations, you see that each individual month contributes to the total result represented by the gray column.
Our example shows cash flow from 3 different categories and the total net cash flow for the company. This way you see the changes as well as all the factors that contributed and influenced to these changes.
In our example, a large investment in February will significantly impact the net cash flow.
How would you go about making this report? Use this recipe:
1 Cash-flow projection by activity type
4 bridge charts, scaled
1 axis break (optional)
1 difference highlight
1 meaningful headline
2-4 explanatory comments
Cut the axis to ensure proper scaling
While this report is easy to create in Zebra BI, there are some things you should pay attention to.
We recommend you also include the opening and closing balance to the above charts, which results in the following visualization:
By adding the opening and closing balance that are significantly larger than changes, we needed to cut the axis to improve the readability of the charts.
We also included a difference highlight to provide the reader with a bit of additional data.
These three excel report templates show how by adopting a visual and standardized approach to reporting you can convey the same information much more clearly, concisely and effectively. These templates are not about making data pretty but about improving communication.
Here are the five key takeaways to remember from this article:
- Use charts instead of spreadsheets. They will help you present dense information in an easy-to-understand and readable fashion.
- Use bar charts to display absolute values and turn to lollipop charts to show relative variances
- Use a standardized color-coding system with green showing positive developments and red reflecting negative movement – this will help users instantly understand your charts
- Use waterfall charts to visualize a starting value, the positive and negative changes made to that value, and the resulting end value
- Always ensure that the charts that represent the same types of data (for example EUR) are scaled, so that absolute and relative gains or decreases make it clear how they impact the total.
Bonus: Download these excel report templates
If you'd like to download the excel report templates presented in this article, click on the button below.
Note: You have to have Zebra BI installed to be able to use these templates. You can get your 30-day free trial here: http://zebrabi.com/free-trial/