In the latest Bayfield Training Webinar, Sonia Martin-Gutierrez and Danielle Stein Fairhurst present: Building the Financial model dashboard. This webinar focused on summarising your financial model’s outputs using standard Excel skills into a concise, dynamic and visually appealing one-page dashboard report.

Danielle Stein Fairhurst is Principal and Financial Modelling Specialist at Plum Solutions, an international training and consulting company based in Sydney, Australia. Danielle has over 15 years’ experience leading courses in Financial Analysis, Data Analysis, Dashboards and Budgeting and Forecasting in Excel.

Building the financial model dashboard

Danielle’s webinar delivered two primary objectives. Firstly, she explained the usefulness of dashboards in finance, including the tools you can use to build dashboards. This included a case study of a real estate model. Secondly, she explained some of the principles of dashboard design.

Why are dashboards useful in finance?

Living in the age of big data, bombarded continuously by hoards of information, financial analysts are tasked with manipulating data into a useable format from which decisions can be made. Easily digestible and succinct data-backed information has become even more imperative now in an era where people seemingly have shorter attention spans. As Danielle emphasizes, the prevailing attitude amongst many people seems to be that “if you cannot give me information immediately, on a single page, in a format that I can quickly understand and make decisions, then I am not interested.”

One of the best ways of presenting financial information is through the use of a dashboard report. Stephen Feu, often called the father of the modern dashboard, defines a dashboard as “a visual display of the most important information needed to achieve on one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.” Therefore, your task as a financial modeller is to present your model in a format that people can understand and implement actionable decisions with—a dashboard is a fantastic way to do that.

Provide Insights, not just numbers

Presenting financial information can be tricky, especially for people who do not have a financial background. Thus, information must be presented in a way that people can understand. For example, instead of presenting an income statement on excel using gridlines, one could use a bar chart that separately shows each component of the income statement (e.g. Gross revenue, Operating costs, Operating income…etc). The principle behind this is that numbers are useful as quantitative data must back models. However, if the data is incorrectly presented, it can be overwhelming. Consequently, it makes it harder for the reader to decide on your financial model because they may not understand or admit that they are confused.

Dashboard tools & Case study

The general tool for creating dashboards is simple: Excel, Excel, Excel. In ‘Modern excel (2010+)’, there are various add-ons that can be useful. Firstly, to cleanse data, use the PowerQuery add-in. Specifically, PowerQuery allows you to import data from many different sources and then clean, transform, and reshape your data as needed. For example, you can create a query once and then reuse it with a simple refresh.

Moreover, it can import and clean millions of rows into the data model for analysis after. Secondly, to store data, use the PowerPivot add-in. PowerPivot allows you to draw large volumes of data from various sources and create relationships. From there, the data generated can be analyzed efficiently. Finally, to manipulate data visuals, create relationships, and share it with others, use PowerBI. In contrast to Excel, PowerBI operates on a cloud service called “Power BI Service” where you can publish your data. Moreover, this service automatically refreshes your data.

Principles of Dashboard Design

Danielle notes that one of the difficulties of building dashboards is deciding on the design and charts to use. This is because choosing the wrong visual aid or simply defaulting to the most common data visualization type could confuse the viewer or lead to mistaken data interpretation.

There are various types of charts and graphs. The four most common are generally line graphs, histograms, pie charts, and bar graphs. Each type serves its purpose. Line graphs show you how numbers have changed over time. They are used when you have data that is connected and to show trends, such as average night-time temperature in each month of the year. Pie charts show you how a whole is divided into different parts. For example, you might want to show how a budget had been spent on different items in a particular year. Bar graphs are used to show a comparison among different items, or it can display a comparison of items over time. In conclusion, when you are building a dashboard or presenting your financial model’s output, it is essential that you create meaning from your data.