Seven years ago, when I first joined a private investment company, I was tasked with creating a financial model for a greenfield project. The results of the financial model were to be presented to the executives and shareholders—the first time for me to do something like that.
Hailing from an audit background, I created a detailed summary page showing almost all of the assumptions, full P&L, balance sheet, and cash flows, charts and graphs for each financial statement line item, and all sorts of explanations. When presenting it to the executives, I could sense their confusion. They tried to understand the rationale behind each piece of information presented on the summary page. The process turned out to be very inefficient and time-consuming for everyone.
That day, I learned that a good financial model is like a good piece of furniture - even though the carpenter spends a lot of time perfecting each detail, the customer sees and appreciates only the final product. Only very few customers would like to see the process in detail. These customers also tend to request and investigate such details themselves. The same applies to financial models. Even when a consultant spends a lot of time creating the model and analysis, we should curb the desire to show all of the effort we put into our work. Rather, we should emphasize important highlights of past performance and future forecasts to make the decision-making process easier for the users.
From my professional experience of making mistakes and finding solutions, I have identified some accessible ways to use financial data visualization that would make models easier to understand.
Understanding the Purpose of the Model
The starting point for building a sound financial model is understanding its purpose by answering the following questions:
- Who will use the financial model? Who is the audience, what is the level of their technical knowledge of Excel and the subject itself, etc.?
- What is the user’s expectation when using or relying on the financial model?
- How will the model help the defined user make the desired decision? What result or KPI is the user looking for (e.g., IRR, NPV, sales volume, etc.)?
The types and complexity of financial models vary significantly from “back of the envelope” calculations to complex financial models calculating very precisely future performance, synergy effects, economies of scale, etc. The type of financial model users could also be quite different – from the Excel guru analyzing your financial model to present the findings to their superiors, to colleagues from other departments who have no financial background. Alberto Mihelcic Bazzana offers tips and detailed guidance on building financial models in his article about advanced financial modeling best practices and explains the different types of models. Understanding who the user of the financial model is will help to make the model user-friendly, which is its ultimate goal.
Financial models can be used to make decisions on providing financing or investment, adding or removing product lines, entering new markets, or more simply, valuing assets or investments using the DCF method. In the majority of cases, the models are used to make some kind of financial and/or strategic decision but sometimes also to plan short term and tactical steps or to even understand working capital volume under different circumstances or scenarios. The specialist preparing the financial model should bear in mind what the decision-maker is trying to achieve or solve using the model. For this reason, financial data visualization is crucial.
Now that it is clear who is going to use the model and what the user expects to achieve with it, the consultant should understand how the model can help. You should understand what the critical assumptions are or what findings are affecting the decision-making process. Is there a specific KPI that is particularly important for decision-making? Are there critical assumptions that would affect the final outcome? This understanding will help the consultant to draw the user’s attention to the right parts of the model, which in turn saves the user “search time” and allows for a critical assessment of the project.
Drawing the User’s Attention to the How Part of the Model
In order to summarize the financial model in a way that allows the user to focus on the major assumptions and findings, the best practice is to use one separate summary page or dashboard. The summary page should offer a logical sequence, although not as a report or memo providing an explanation using sentences and paragraphs. Other techniques should be applied to guide the reader and help them understand the causes and effects.
In her book Storytelling with Data, Cole Knaflic explains in detail how to focus the audience’s attention with the aid of visual clues. Among other techniques, Knaflic suggests using Gestalt Principles of Visual Perception “when it comes to identifying which elements in our visuals are signal (the information we want to communicate) and which might be noise (clutter).” Founded in the early 20th century, the Gestalt School of Psychology first proposed “the principles of grouping” – a theory that implies that the human brain tends to simplify its perception of the surroundings. The external stimulus/object is perceived as a whole rather than a combination of its components. The following principles of grouping are useful to consider when creating the summary page: proximity, similarity, closure, common fate, continuity. Cameron Chapman offers a good explanation of these principles in her blog post explaining the Gestalt principles of design.
We will illustrate these principles with a few examples (the data used in the graphs and tables is for demonstration purposes only):
Proximity and Similarity
The chart compares the target company sales with its competitors. The sales are grouped based on different product categories. Two principles were used to make the chart easier to read and understand: proximity and similarity.
- Proximity: “The law of proximity suggests that objects near each other tend to be viewed as a group.” As sales of similar products are shown close to each other, the reader understands that they are related.
- Similarity: The law of similarity states that humans tend to “group similar items together.” As sales of the target company are given with the same corporate color for different types of products, it becomes obvious that these are somehow connected and belong to one whole.
Continuity and Common Fate
Illustrating the relationship between actual and forecasted numbers could be a useful tool for decision-makers. Continuity and common fate are Gestalt principles that can help to display such a relationship. The Gestalt principle of continuity explains that “points that are connected by straight or curving lines are seen in a way that follows the smoothest path.”
A dashed line was used to show projections of company sales and distinguish future budgets from actual historical numbers. Although there is space between the lines, the reader perceives it as a whole.
The principle of common fate states “that people will group together things that point to or are moving in the same direction.” In this case, the company manages to keep the gross profit margin between 50% and 55%. Sales revenue and gross profit are thus moving in the same direction. It is easier for the reader to perceive and understand such correlation.
According to the Gestalt principle of closure, “if something is missing in an otherwise complete figure,” humans tend to mentally add it. The chart below makes it easier to understand the total sales volume, gross profit, and net profit for the past period. Even though it only shows the gross profit and net profit areas, the reader can “imagine” that the sales area chart starts from zero point and continues above.
It is not necessary to always use graphs and charts for illustrating forecasted results or key assumptions in the financial model. Sometimes (more frequently than you might think), it is better to use a simple table with conditional formatting, or even a single highlighted cell.
These tables provide a sensitivity analysis of the hotel’s sales volume for the stabilization year (in this case, year 4 since the start of operations). The sensitivity analysis is built upon two variables: occupancy and ADR (Average Daily Rate). The management target is to achieve sales between $8m and $9m. By simply drawing a line for the target sales figures, the reader can easily navigate through the table and understand what the main drivers for this target should be.
Dos and Don’ts
There are a few simple boxes to check to make the financial model and the visualization easy to navigate and understand:
- Always include the table of contents or cover page with the relevant legend. A cover page helps the user understand the structure of the financial model. They can easily understand which tab to refer to for specific information (e.g., assumptions, detailed calculations of the financing or other drivers, projected financial statements, summary page, etc.). The legend should specify the units the model is mainly presented in (thousands, millions, USD, EUR, etc.) and the logic behind using different font colors, cell fills, borders, or other markings.
- Once you have a legend in place (and even if you decide not to have it), be consistent with the use of format, colors, alignments, units, and other details throughout the whole model. The formatting rule can also apply to the charts and graphs in the model, but if needed, you should create a separate legend for each chart or graph.
- Don’t misuse the graphs, e.g., by removing 0 point cross-axis or manipulating axis range.
These charts represent the same numbers—hotel occupancy trend for the last 10 years—but the chart on the right has a modified vertical axis. The vertical axis displays the range between 45% and 75%. As a result of this modification, the chart is showing a steep increase starting in 2016. In fact, the year-over-year increase in this period is 2-3% (which is also a good result!). By removing the 0% axis point, the picture is somewhat distorted. If we use more responsible formatting, it becomes obvious that the trend is more “steady” than “increasing.” For the same reasons, avoid using 3D charts. Looking at 3D charts, especially with perspective, makes it often hard to see the real correlation between several sets of data.
Use colors sparingly and consistently in the charts and graphs. You should remember that the purpose of the chart is to draw the user’s attention to a specific part. Instead of having the whole spectrum of rainbow colors, try to distinguish background information from the main message. As Knaflic puts it, “highlight the important stuff, eliminate distractions, and create a clear hierarchy of information”. It’s also important to use the same color and format for the same purpose and be consistent - e.g., if you decide to show historical sales with an orange line and projections with a dashed blue line, use the same colors for illustrating other data as well. This principle was used while creating the graphs used throughout the article. Future projections are presented with a dashed line, unimportant data is given in gray on the charts, and the corporate blue and green are used to draw the readers’ attention to the right part of the charts, while the text in all the charts has the same format, size, and color.
Take into consideration that different colors have different meanings in different cultures and situations. Investigate well the culture of the organization. If possible, use corporate colors.
The pillar of the best practices of data visualization in financial models is to make models and graphs with their purpose in mind. Understand by whom, what for, and how the model and visualization will be used. Always remember to push irrelevant information to the background and emphasize only the important parts of the model. You can create a powerful tool to draw the user’s attention to the right issues and help them ask the right questions and make the right decision. Giving the right direction to the process is a much better feeling than just showing how much time and effort was spent to prepare this tool. For this reason, it is important to utilize financial data visualization techniques.
Understanding the basics
The starting point for building a sound financial model is to understand its purpose by answering the following questions:
Understand how the model and visualization will be used. Push irrelevant information to the background and emphasize only the important parts of the model. You can create a powerful tool to draw the user’s attention to the right issues and help them ask the right questions and make the right decision.
Gestalt Principles of Visual Perception can be used for data visualization “when it comes to identifying which elements in our visuals are signal (the information we want to communicate) and which might be noise (clutter).” The following principles are useful to consider when creating the summary page: proximity, similarity, closure, common fate, continuity.