Search

Data Visualization


The wonderful thing about being a finance professional is that we often work with other numbers driven people. This makes it quite easy to say the following “Hey [insert name], I’ve performed the analysis – please see the attached spreadsheet”, and the spreadsheet may look something like what is shown in Figure 1.

Figure 1: Raw spreadsheet data

The biggest question I often get is “Antoine, I provide all the data to the CEO, yet he gets bogged down in the details and doesn’t look at the big picture. Instead he focuses on decimals, and small movements that should not concern him. What should I do?”. The solution for this question is multi-layered, but at the core, it’s about data visualization. So let’s walk through some tips for presenting data more effectively to help you and your team reach conclusions quickly.


(I’m using Excel version 1807, Office 365. If you are using a different version of Excel, the concepts are still valid, but some of the location references may be different)


Tip #1 – Know your audience


Step one is, know your audience. This step, although intuitive, is often ignored. You will frequently have your model dissected by several parties. Because of this, each party’s needs must be considered independently, and your model should be able to provide each party the ability to reach their own conclusion using the same information. For example, in the data provided above you may have to report this information to your CEO as well as the sales team – if you do, the sales team is probably not interested in knowing the fixed costs per customer since they don’t control that.


Tip #2 – Format your Model


Once your model is done, you should spend at least five minutes to format it. I consider the aesthetics of a model very important because not only does it show an understanding of the underlying data, but it also alleviates the shortness of breath, irritability and general anger I have when I’m given a model that isn’t formatted. In addition, prior to hitting the “X” to quit, my models must pass the “bus test.” As in: “if I get hit by a bus will someone be able to understand what I’ve done?” Figure 2 shows an example of how to format the model.


Figure 2: Formatted spreadsheet data

The formatted model has the exact same information as the one presented in Figure 1. However, things are grouped together differently, creating a more succinct model that an individual can use to analyze the data. Figure 2 also demonstrates a number of tips for presenting the data more effectively.


1) Take out the “gridlines”. To do this, select the “View” Ribbon, and under “Show”, uncheck gridlines. I deeply despise gridlines. As such, I immediately get rid of them using the shortcut “ALT-W-V-G”.


2)Format dates. In the “Inputs” tab I defined the start of the period as “30-Sep-18” the actual date. For every subsequent period I used the function: “=eomonth(start_date,months)”. This function asks you the date that you want it to start and how many months forward (to the end of that month) you want to go. in this case, the formula in D3 is: =eomonth(C3,1). This means that I want cell D3 to be “1” month past cell C3 etc.


3) Use brackets to indicate “negative”. Depending on the company you work for, the IT department may have defaulted your “,” formatting to have negatives in brackets. However, this is not always the case, as the default is not always set that way in Excel. To use brackets,select the negative cells and the use the shortcut “CTRL-1”; the result is that the “Format Cells” box pops up, as shown in Figure 3. (–Note: you can also get there by right clicking the cell and selecting “Format Cells”).

Figure 3: Format Cells pop-up

Once there, select “Custom” (as highlighted), and create your own format. For this, I used the format: “#,##0.00;(#,##0.00)”, which I typed into the dialogue box under “Type:”. Once done, select “OK” and the new format has now been added. The “custom formatting” is very useful for making numbers appear as text, while still maintaining the ability to use that number for discounting/compounding, multiplication, addition etc. For example, if we are looking at the number of years – “Year 1”, “Year 2” etc. - I would write “ “Year” #” under “Custom” “Type:”, and this allows me to show the user “Year 1”, while maintaining the number’s numerical characteristics which allows me to multiply this cell by any number in my model (or use it for discounting/compounding in my valuation models).


4) Bold the specific lines that the user should concentrate on. When your model no longer contains gridlines, the “bolds” tend to stand out – so use those judiciously to highlight specific areas in your model.


5) Use the “=iferror([value_if_true],[value_if_false])” when calculating the growth rate. This formula is especially useful when dividing by zero, as that will cause an unsightly error in your model (the #DIV/0!). The “iferror” formula allows us to tell Excel what to do when there is an error in the formula. In this case, I set the error value to be 0%, as you can see in cells C8,C10,C12, and C14.


Tip #3 – Use Graphics


In my mind, the most important part of presenting your data is doing it in such a manner that is both attractive and understandable to the final user. I recommend having a separate tab in your file called “Dashboard”. In that tab you can have a variety of charts and graphs that help show the results of your analysis in a visually friendly manner.


Let’s take a look at the types of charts we can use to show the data we have. Figure 4 easily shows how we can depict simple data into one-page that can be printed and easily analyzed.

Figure 4: Sample dashboard

I have selected the most relevant sections of the Excel model, such as a breakdown of sales, variable costs, fixed costs and EBITDA. The reader can then see the changes that have happened to get to the 5-month total EBITDA (a bridge). The relationship between Sales and EBITDA per customer is also shown, and finally, a speedometer depicting the number of customers there are at the end of the period completes the analysis.

Likely, not all this information will be critical for your user(s); however, it is your job as the “keeper of data” to create dashboards that are relevant for each party that uses your model. As a result, you may have multiple dashboards for multiple parties that you send out at the end of the month.


Let’s walk through three of the charts in the dashboard shown in Figure 4.


1) Bar chart with a line showing the number of customers. This is a basic chart that can be created using a “Combo” style chart.


a. When selecting the “Combo”, set customers using a “Line” chart, with everything else using a “Column” chart.

b. The Line chart is then “plotted” on the secondary axis.

c. As you select the dates, Excel will default the dates in the chart to be the beginning of the month (October 1, November 1, December 1, January 1, February 1). Instead, right click the axis, go into “Format axis”, and in “Axis Type” select “Text axis”.

d. The rest is just formatting!


2) The waterfall (also known as the bridge). Waterfalls/bridges are great for forcing the user through a “path” to finality.


a. Organize your data as follows:

i. Start with the starting total;

ii. Identify the positive movements;

iii. Identify the negative movements; and

iv. Complete with the ending total (which is the sum of i to iii).


For our example, I organized the data as shown in Figure 5 below.

Figure 5: Formatted data for waterfall chart

b. Select the data (the numbers), go to “Insert” Ribbon, and select “Insert Waterfall Chart” as shown in Figure 6.


Figure 6: Insert Ribbon: Waterfall

c. Once done, “right click” the first data series, and select “Format Data Point”, and under “Series options” select “Set as total”. Do the same for the last data point, format it to look the way you want it, and pat yourself on the back as you have now become an excel-bridge-engineer!


3) The speedometer

The speedometer is a fancy way to present data, in a manner that draws the reader’s attention to a specific metric. In our case above, we want the reader to know whether the number of customers that the company has is “Low”, “Ok”, or “Good”.


a. First, set the bounds for your Speedometer. Figure 7 shows the tab I created to set the bounds.

Figure 7: Setting bounds for Speedometer

i. On the left, the buckets determine the range of each parameter. The max represents the end of the speedometer.

ii. On the right, this determines the value of your pointer (1,525, the number of current customers which is live), the width of your pointer which I’ve set as “45” (pick a number that makes your chart look nice), and the End which is a calculation that is calculated as the sum of the speedometer (0+600+600+600+1800) less the sum of the value and the pointer (1,525 + 45) = 2,030.


b. Select the data (numbers) and in the “Insert” Ribbon, in the Charts group, select the Combo symbol as shown in Figure 8 below.

Figure 8: Insert Ribbon: Combo

c. Select Create Custom Combo Chart as shown in Figure 9.

Figure 9: Insert Ribbon: Combo: Create Custom Combo Chart

d. Figure 10 demonstrates the next step in the process. Pay particular attention to the secondary axis check.

Figure 10: Custom Combo: Setting Series and Secondary Axis

e. Once you select “OK”, remember that Series 1 is the “Speedometer” range, while Series

2 is the “Pointer”.


f. Select the blue shaded area, “right click” and select “Format Data Point” as shown in Figure 11. Select the “Paint” icon and select “No Fill”. Do the same for the grey shaded area.


Figure 11: Formatting Data Point

g. This next step is the trickiest part of the speedometer chart; rotating the chart over so that you can present it the “right way”. As such, select the chart (anywhere) and under the “Format” Ribbon, on the left under “Current Selection” select “Series 2”. Then right click the chart and select “Format Data Series” and under “Series Options” (the bar graph) set the angle of the first slice to be 270 degrees. Next, do the same thing for Series 1. The result is shown in Figure 12.

Figure 12: Rotated Speedometer

h. The next step is to Select the Chart again (select anywhere), go to the “Design” Ribbon and select “Change Chart Type” as shown in Figure 13.


Figure 13: Design Ribbon: Change Chart Type

i. When the combo chart appears, change the “Secondary Axis” from the Series 2 to Series 1. This will allow you to select each data point in the “Doughnut” chart (Series 1) as it is now overlayed over the “Pie” chart as demonstrated in Figure 14.


Figure 14: Changing the Secondary Axis

j. Now that you are able to select each data point separately, select the “Blue” area and set the fill to “No Fill”, and format the others to be “Red”, “Yellow” and “Green”, as shown in Figure 15.

Figure 15: Formatting the parameters for “Low”, “Ok” and “Good”

k. Finally, change back the axis of Series 2 to “Secondary Axis”, press “Ok”, and your speedometer is now done. The rest is just basic formatting!


As a reminder this is just one way to do the speedometer chart – there are lots of additional tutorials that will allow you to see how different people build the same chart. How you create the chart is important, but the message you convey is more important, so don’t pursue a chart if it doesn’t provide you or your firm with any benefit (i.e. just because it looks cool).

As an Excel power user, I love getting questions about how to make the Excel model more appealing. The answer I always give is “Data Visualization”. Similar to my previous article, I always want to convey the fact that Excel is nothing more than a tool – don’t lose sight of your end goal, which is to be able to arrive at conclusions about a specific subject matter. The conclusion is the goal, not having a “good looking” Excel model.


If you would like to hold an Excel workshop for your team, or would like to learn more about the learning solutions we provide, please contact us at info@learningstrategiesgroup.com

112 views