If you're interested in breaking into finance, check out our Private Equity Course and Investment Banking Course, which help thousands of candidates land top jobs every year.
Overview
The default graphing function in Excel is not good.
Despite being the backbone of every financial model in the world, Excel has a notoriously fickle graphing function that by default will create clumsy and unprofessional outputs.
Many investment banks have dedicated graphics teams or Excel plugins to help optimize this process, but you will still invariably spend many, many hours making graphs look presentable.
In this post, we’re going to cover some of the basics and conventions you should adhere to when creating graphs in Excel. Most people spend their first summer internship learning how to make decent-looking graphs. So, if you can already make good graphs by the time you become an intern, you’ll soon be entrusted with more important tasks.
If you’d like to learn more fundamental finance skills and improve your modeling, you should check out our Valuation and Finance Starter Kit.
Design Principles and Finance Formatting Conventions
Design Principles
Let’s begin by talking about intuitive design principles that you should keep in mind when creating graphs:
Make sure data items are evenly spaced and do not overlap with one another
Be as concise as possible – most people can only focus on 2 or 3 data points at a time
Use color to clearly indicate different segments or categories
Graphs in the same presentation should follow the same scheme (color, font, etc.) in order to improve visual consistency
If you adhere to these principles, you will have a much higher chance of effectively communicating information. Generally speaking, your goal when making a graph is to visually communicate an idea without any further explanation.
Finance Formatting Conventions
Aside from these design principles, it’s also very important to be mindful of the specific formatting conventions that have emerged over time. There are certain formatting conventions that are generally adhered to in all professional finance graphs:
Units are clearly displayed and are thoughtfully selected (e.g., $ in millions)
Title font is larger than other fonts
All font types are the same font
No background gridlines (generally)
No graph outline boxes
Individual data points are visible
Decimals of data points are thoughtfully selected (e.g., don’t need two decimal points for IRR, don’t need decimal points for a two-segment pie graph)
Graph categories are ordered in a thoughtful way
E.g., by descending magnitude, by descending %, alphabetically, etc.
Compound annual growth rate is included where relevant
These conventions might not mean a lot without context, so let’s walk through a couple of examples that illustrate how this will differ in practice.
There are a lot of different rules, which can take time to implement. It can be really helpful to know the best Excel and PowerPoint hotkeys, which will speed up your graph-making tremendously.
Amateur vs. Professional Graph Examples
If you go through any serious investment firm’s presentations, you’ll see that they tend to adhere to the same principles and formatting rules. The best place to learn about finance conventions is to study what the best private equity and hedge funds have publicly published. 10xEBITDA has a good collection of these presentations.
Let’s go over a few common graphs that you will have to make as an investment banking analyst or private equity professional. We’ll take one random graph from Seeking Alpha and compare it to a similar graph made by a professional investment firm.
Example #1: Vertical Bar Graph
I’m not cherry picking here, I literally picked the first graph I could find from Seeking Alpha. This graph compares the revenue between Amazon and Facebook.
What’s wrong with this graph? Well, the background is black, which is distracting, ink-consumptive, and looks bad if you have many other graph colors. Additionally, the total numbers are in billions, but they’re still using millions as units for some reason. Two decimals are shown for the dollar values even though they’re just zeroed out and unearth no new information.
There are also no CAGRs and the designation of TTM is unclear (would be better to do TTM Q2’21 or whatever it actually is). Lastly, the revenue figures between Amazon and Facebook are so different that this isn’t even a very instructive graph. Facebook revenue looks like it barely grows because the magnitudes are that different – it probably would be much better to have it in two side-by-side graphs, where you could also show CAGRs.
This graph is from Pershing Square’s presentation on Starbucks in 2018 (which also turned out to be a great stock pick).
The graph is comparing Starbucks’ U.S. and Rest of World # of stores. It’s clean and easy to tell what numbers refer to what. The units are in an intuitive and understandable format. The CAGRs are displayed so you can make quick implications (e.g., the Rest of World division is growing materially faster than U.S.). The two things being compared are close enough in numeric value that you can actually tell how each one is changing year over year.
If I had to give nitpicky comments, I would say that you could probably color coordinate the bars to the CAGR table. I would probably also retitle the titles to “2010A to 2018E CAGR” because the FY designation isn’t used anywhere else. The overall title (“U.S. & Rest of World Units…”) is also in a hard-to-read font choice, as the bold makes some of the letters a bit too condensed.
Example #2: Financial Summary
I kid you not, this is an actual financial summary output in a top Seeking Alpha post.
To start, this is a direct screenshot (not even an enhanced metafile output) of an Excel workbook. The gridlines are still visible. All of the values have two decimal points even though they’re still just zeroed. Some of the calculated statistics are off to the right, but they’re so far out that you’d need a ruler in order to match it up the figure they correspond to. There’s also inconsistent titling (“Gross Profit” vs. “gross profit”).
This output is a tragedy and a scourge upon our people.
From an analytical perspective, this is also way too much information to try to display in a single chart. I would definitely make different financial summaries for each business.
If you absolutely had to include this information on one output, you’d at least try to reduce the total numbers displayed. That means cutting down all unnecessary digits – I would do zero decimals for both percentages and dollars, as they don’t change the implication of the data. I would also get rid of all of the numbers on the right and just include CAGRs for each row. Lastly, I would also add some colors to titles (e.g., Amazon, Facebook, the year row) in order to separate information and section things more visually.
This is from Marcato’s presentation of Buffalo Wild Wings, which is a pretty clean and professional looking deck.
This is the financial projection summary of Buffalo Wild Wings, the company being pitched. What’s great about this is that there are clear headings and the most important figures are bolded.
You can tell that the author of the graph is pointing your attention to the “% Franchised” row because of the yellow outline box. The long-term % Franchised target is obviously 90%. The decimals are chosen tastefully and relevant statistics are made available. There is a clean dashed line showing what are "actuals" and what are "expected".
Example #3: Line Graph
Let’s look at another Seeking Alpha graph. This is very clearly made using Excel’s default graphing function and has a ton of beginner mistakes.
The dates get cut off after 2019. There are no data labels on any of the data points. The two different lines are the same color and there is no legend, making it virtually impossible to distinguish what is happening. The gridlines don’t do very much to assist in the understanding of the graph. The outline of the box is unnecessary as it doesn’t separate the graph from other diagrams.
We finally come to a report straight out from the Bain Capital Global Private Equity Report, which, in my opinion, is an artistic masterpiece. It should be no surprise that a private equity firm with consulting culture makes the most beautiful and intuitive graphs in the world.
This graph clearly articulates how “Other PE” is growing faster than traditional buyouts. It displays two decades of information and has two clean CAGR lines to show where the trend changes. The colors, decimals and font choices are tasteful and presentable. The title is clear and all of the data is evenly spaced out. The colors also correspond to the colors used in the remainder of the presentation.
Conclusion
In summary, you should see that there are a lot of small differences that separate amateur from professional presentations. It might also be helpful to keep a list of these principles and formatting conventions handy as you make your own graphs.
Additionally, it can be very helpful to keep a couple of professional presentations by your desk so you can reference how the best investment firms make graphs. Similarly, in investment banking, it’s very wise to keep an old pitchbook that your MD already signed off on close by to reference.
Comments