Excel Chart & Graph Methods Every Finance Department Should Use
Updated: Nov 16, 2022
Although it was created literally decades ago, Excel is to corporate finance what the crane is to construction; you must have some proficiency in the application to succeed as a finance professional. In the US, more than 60% of businesses use spreadsheets, and worldwide, Excel has over a billion users.
One of Excel’s critical features is its charting and graphing capabilities. Most workers in a finance department will be proficient in a number of graphing and charting methods, but not all will be sufficiently proficient in the best practices. So, let’s learn some tips to unlock Excel’s power for FP&A and beyond.
Know Your Visuals’ Objectives
Before diving into the details of making charts and graphs, FP&A professionals need to nail down good outlining techniques, and to understand how to effectively reach their audience. Below is a brief outline of different presentation methods based on audience type.
Internal audiences include any employee or executive who works in that company. For executives, the purpose of communicating a data-filled presentation is to make updates about business activities, such as an initiative or project. These presentations also serve another purpose; to facilitate decision-making on managing the company’s operations, growing its core business, acquiring new markets and customers, investing in R&D, and other considerations. Knowing the relevant data and information beforehand will guide the decision-makers in making the right choices that will best position the company toward more success.
The category of “External Audience” extends to a company’s clients, whether they are currently involved with an organization’s projects in progress, or are new clients that the company wants to build a relationship with and win new business from. The other external audience is the general public, such as the company’s external shareholders and prospective investors of the company. When it comes to winning new business, the analyst’s presentation will be more promotional and sales-oriented, whereas a project update will contain more specific information for the client, usually with lots of industry jargon.
Audiences for Live and Emailed Presentation
A live presentation contains more visuals and storytelling to connect more with the audience. It must be more precise and should get to the point faster and avoid long-winded speech or text because of limited time. In contrast, an emailed presentation is expected to be read, so it will include more text. Just like a document or a book, it will include more detailed information, because its context will not be explained with a voice-over as in a live presentation. When it comes to details, acronyms, and jargon in the presentation, these things depend on whether your audience are experts or not.
An Excel financial model mathematically represents a company, a financial asset or portfolio of assets/companies. Prepare financial models according to a purpose. Two popular models are:
Discounted Cash Flow or DCF
Financial modeling simulates the financial performance of an asset over 3–10 years. Often, the end game is to provide valuation for a financial asset’s worth. The information enables decision makers to buy, sell, expand, merge assets or to take any other course of action.
As noted above, it is critical to know and understand the purpose of your financial models and keep them simple, auditable and transparent. If you build them upon whim, you will likely end up mixed up with a plethora of formulas, and make poor models. The following sections as a guide from top down are an effective presentation outline:
Assumptions and drivers
Cash flow statement
Charts and graphs
Charting & Graphing Methods
Charts and graphs are the critical, big-picture piece of your presentation. They make any financial analysis readable, easy to follow, and provide what should be an understandable presentation of your data. They are often included in the financial model’s output, which is essential for the key decision-makers in a company.
The decision-makers are made up of executives and managers who usually won’t have enough time to synthesize and interpret data on their own to make sound business decisions. Therefore, it is the job of the analyst to enhance the decision-making process and help guide the executives and managers to create value for the company. When an analyst uses charts, it is important to be aware of what constitutes a bad chart or a good chart, and how to avoid the formerer when presenting your data.
Examples of Good Charts
As for great visuals, you can quickly see what’s going on with the data presentation, saving you time for deciphering their actual meaning. More importantly, great visuals facilitate business decision-making because their goal is to provide persuasive, clear, and unambiguous numeric communication.
For reference, take a look at the example above that shows a dashboard, which includes a gauge chart for growth rates, a bar chart for the number of orders, an area chart for company revenues, and a line chart for EBITDA margins.
To learn the step-by-step process of creating these essential tools in MS Excel, watch our video course titled “Excel Dashboard & Data Visualization.” Aside from what is given in the example below, our course will also teach how you can use other tables and charts to make your financial analysis stand out professionally.
Examples of Poorly Crafted Charts
A bad chart, as you can see below, will give the reader a difficult time to find the main takeaway of a report or presentation, because it contains too many colors, labels, and legends, and general details. It also serves no purpose if a chart, such as a pie chart, is displayed in 3D, as it skews the size and perceived value of the underlying data; this is a useless feature. A bad chart will be hard to follow and understand.
Storytelling with Data, Visuals, and Text
Aside from understanding the meaning of the numbers, a financial analyst must learn to combine numbers and language to craft an effective story. Relying only on data for a presentation may leave your audience finding it difficult to read, interpret, and analyze your data. You must do the work for them, and a good story will be easier to follow. It will help you arrive at the main points faster, rather than just solely presenting your report or live presentation with numbers.
The data can be in the form of revenues, expenses, profits, and cash flow. Simply adding notes, comments, and opinions to each line item will add an extra layer of insight, angle, and a new perspective to the report.
Furthermore, by combining data, visuals, and text, your audience will get a clear understanding of the current situation, past events, and possible conclusions and recommendations that can be made for the future.
Integrating Excel With New Applications
Excel is a must-have skill for any finance department. Financial analysis, modeling and reporting are done in Excel because of its unparalleled capabilities in mathematical calculations, formatting and VBA/macro tools. Many companies have tried to move away from Excel, only to find themselves coming back to it. Organizations do this because they seek to automate more of Excel’s manual tasks, as well as expand their capabilities in data analysis through the integration of FP&A software. Although new resources like FP&A software offer some great advantages, it is wrong and disadvantageous to approach this issue as software vs Excel. You don’t need to force your finance team to only learn new applications, and abandon one that they have potentially used well for decades. You can complement your finance department’s Excel use with a quality software, designed to be used in tandem with the former perennial application.