Line charts

Line charts are most appropriate for indicating trends. Like column charts, the simplicity of line charts makes them one of the favorites in displaying data. Column and line charts can be used interchangeably to display the same data, but line charts are normally used when there is a connection between the points on the x-axis, such as times or dates on a continuum. Line charts are best used for trending information such as time series, and columns are better for showing comparisons.

In Excel, you also have stacked line and 100-percent stacked line chart options. Sometimes line charts convey information in a more meaningful manner when the data points are marked.

To build a line chart, such as the one shown in Figure 9-7, highlight the data, and simply select the first 2-D Line option from the Charts section of the Insert tab on the Ribbon, as shown in Figure 9-7.

The easiest way to add the labels on the x-axis in the chart in Figure 9-7 is to include the data in row 10 when creating the chart in the first place. To highlight data in nonconsecutive ranges, hold down the Ctrl key while highlighting with the mouse.

Move the chart across so that it isn’t obscuring the data behind it, and add a label.

Take a closer look at the chart you just built. It looks attractive, but which scenario is shown by which line? Grasping the meaning is difficult, particularly if you’re looking at the chart in black and white! Take a moment to put yourself in the viewer’s shoes and see if your message is unambiguously clear. Not really, is it? By putting the legend at the bottom — even if the colors are showing — it’s really difficult to figure out which line is which, so the viewer’s eyes need to go backward and forward trying to understand the meaning.

Instead of using a legend at the bottom, let’s put the series names next to each line so that the chart will be easier to interpret. To do this, follow these steps:

1. Right-click one of the lines with the mouse.

2. Select Add Data Labels and then Add Data Labels again, as shown in Figure 9-8.

   The data values appear. Don’t worry — we’re going to change that.

3. Click the label on the far right-hand side (the one with the value $2,324).

   Make sure that’s the only one that’s been selected; otherwise, it won’t work properly.

4. Right-click the label, and select Format Data Label, as shown in Figure 9-9.

   Note that it must say Label (singular), not Labels (plural), because that would mean the entire series has been selected, which isn’t what you want to do.

5. In the Format Data Label panel on the right side of the screen, check the Series Name box, and uncheck the Value and Show Leader Lines boxes.

   The label “Worst Case” now appears next to the first line.

6. Click the rest of the data labels containing numbers on the line, and delete them one by one.
7. Repeat steps 1 through 6 with the Base Case and Best Case lines on the chart until each of the lines has its scenario label next to it, as shown in Figure 9-10.

8. Adjust the chart sizing as necessary, and move the labels so that each is next to the correct line.

   Don’t mix them up!

9. Remove the legend at the bottom of the chart and remove the gridlines if you wish by clicking them and pressing Delete.

Lining up the labels by hand is quite tricky. If you don’t get them aligned properly, it can look messy. Try holding down the Alt key when you move the label — this will “snap to grid,” which helps with alignment. Note that this method works with other objects such as whole charts, shapes, and images and in other programs, too.

Bar charts

Bar or column charts are one of the most commonly used chart types available in Excel, second only to perhaps the line chart in their use in financial modeling. Bar charts are most useful for comparing unrelated data points graphically. They’re very clear and easy to understand. When shown vertically, bar charts are sometimes called column charts. Horizontal bar charts represent exactly the same information as column charts from a different perspective. Most commonly, bar charts are used to represent time or future projections along the x-axis.

To build a simple bar chart with only one series, highlight the data, and simply select the first 2-D Column option from the Charts section of the Insert tab on the Ribbon.

To build a stacked bar chart, highlight all the data, including the series names and the years, as shown in Figure 9-11 (remember to hold down the Ctrl key to select nonconsecutive ranges), and select the stacked column (the second 2-D Column option) from the Charts section of the Insert tab on the Ribbon. Edit the title.

You may have noticed that there isn’t a lot of difference in terms of the design between a stacked bar chart and a stacked area chart (see the “Changing a line chart to a stacked area chart” sidebar). Which option you choose is a matter of personal preference. Play around with your chart, trying a number of different chart types to see which shows the data best.

Some data visualization specialists advise against the use of a stacked column chart because it makes the top columns difficult to compare because the bases don’t start at the same value. I like to see the total amount as well as the breakdown, so although I appreciate that it can sometimes make comparison difficult, I still use stacked bar charts quite a lot when displaying the output of my financial models. You can try using a clustered column, such as the one shown in Figure 9-27, instead — that facilitates a better comparison. But with too many series, clustered columns can quickly become cluttered.

Combo charts

One of my favorite ways of showing different metrics in a single chart is to use a combination of bar and line chart types, which Excel calls a combo chart. I like combo charts because they can convey a lot of information without cluttering the chart. When you want to display as much information as possible in a small amount of space without making the graphic seem cluttered, combo charts are the answer.

You can also show correlations and make a point about cause and effect in your financial model simply and effectively with combo charts. For example, in the chart shown in Figure 9-12, the number of customers is increasing steadily, whereas the cost per customer changes erratically, making the point that just because demand increases, the cost per customer does not see any economies of scale as a result.

The combo chart does not appear on the Ribbon, so to build a combo chart, follow these steps:

1. Highlight the data, including the series titles (by holding down the Ctrl key to highlight nonconsecutive ranges) and select Recommended Chart from the Charts section of the Insert tab on the Ribbon.

   The Insert Chart dialog box appears.

2. Click the All Charts tab.
3. Click the Combo icon at the bottom, and select the Clustered Column – Line on Secondary Axis option, as shown in Figure 9-12.

4. Select the Cost per Customer Secondary Axis check box, as shown in Figure 9-12.

   Its data will now appear on the secondary axis on the right side of the chart.

5. Click OK.
6. Edit the colors and the chart title.

If you want to change the cost per customer to show on the primary axis (on the left instead of the right), select the Forecast Customers check box instead of the Cost per Customer check box in the dialog box shown in Figure 9-12.

When you create the combo chart, the secondary y-axis (on the right-hand side) has automatically defaulted to starting at $2,140. This makes the difference between the years more noticeable, but it can be misleading so you might decide to change the axis to start at zero instead. To do this, double-click the numbers in the secondary axis (or right-click and select Format Axis) and when the Format Axis panel appears, change the Minimum bounds from 2410 to 0, as shown in Figure 9-13. Compare this to Figure 9-12. You can see that the chart has less impact when the axis starts at zero.

Try not to clutter the chart by adding too many series and, as always, look at the chart from your viewers’ perspective and make sure your message is explicitly clear and understandable. In this example, it’s fairly clear which axis contains which value because the secondary axis is formatted with dollars. But to make it even clearer, you might consider adding axis titles, which you can find under chart elements.

Pie charts

Pie charts have also been vilified in recent years because they make it even more difficult than stacked bar charts to compare data. Comparing the sizes of the different “slices” of the pie is extremely difficult. Pie charts aren’t useful for comparison, or for time series. Particularly for dashboards where size is an issue, pie charts take up a lot of space without conveying much information. Pie charts are good, however, for displaying ratios or percentage information, such as market share or penetration. Pie charts are visually appealing, and I tend to use them when comparing only a few categories, such as male versus female.

To build a pie chart, highlight the data, including the series titles and simply select the first 2-D Pie option from the Charts section of the Insert tab on the Ribbon, as shown in Figure 9-14.

Edit the title, and you’re done! Well, not quite. Take a closer look at the chart. Is it really clear which slice is male and which slice is female? Female is shown on the right, and male is on the left, but the data labels are the other way around. This happens sometimes in Excel, and it’s not technically incorrect, but leaving the labels like this make it much more difficult for someone to interpret the meaning of your data. Readers need to look very carefully to see which slice is which.

Take time to check the outputs of the financial model, especially charts to make sure that the meaning and the message you want to get across can be easily interpreted by others.

You can edit this chart so that it’s easier to read and can be viewed properly in black and white by following these steps:

1. Click the chart, and click the Chart Elements button on the right, as shown in Figure 9-15.

2. Check the Data Labels option.

   The value of each category appears on the pie chart.

3. Hover the mouse over the Data Labels option again, and click the arrow that appears to the right.

4. Select More Options.

   The Format Data Labels panel appears at the right.

5. Check the Category Name, Value, and Percentage check boxes.

6. Change the Separator to “(New Line).”

   Each of the labels is put on a separate line.

7. The legend is no longer required, so delete the one at the bottom, and add a chart title.

A doughnut chart is just a pie chart with a hole in the center. This difference may not seem significant at first, but this gap in the doughnut hole allows several series to be stacked in the same chart, which you couldn’t do with a pie chart. Although this feature looks visually appealing, it’s often misused and leads to confusing charts. To see this in action, add another series to the chart from earlier chart and overlay the male and female with the split between adult and children, as shown in Figure 9-16. This doesn’t really tell you very much. A Sunburst chart, described later in this chapter, would be more useful in this situation.

Charts in newer versions of Excel

The major change between Excel 2013 and Excel 2016 is the introduction of a number of new charts. With the increased popularity of data visualization and graphic display, Microsoft has kept up with competing business intelligence and data analysis software by making it easier to create popular chart types in Excel. For more information about competing software and changes in Modern Excel, see Chapter 2.

As with most new features of Excel, the new charts aren’t backward compatible. If, for example, you create a Sunburst in Excel 2016, and someone tries to open it in Excel 2013, the chart will simply show as a blank area. So, make sure that your users have the same version of Excel as you do if you’re planning to include newer Excel features such as these charts in your model.

Waterfall charts

Waterfall charts are very useful for displaying the output of financial models because they pull apart the pieces of a stacked chart and show their incremental effect side by side.

Take a look at the example shown in Figure 9-17. Showing an expense breakdown in a pie chart is not very helpful. Too many series are shown, and it’s very difficult to compare each section without the help of the percentages shown in the data labels.

This data is much better shown using a waterfall chart. Not only will you be able to see each of the cost categories side-by-side, but you’ll be able to view the revenue amount and the margin as well for comparison. Download File 0901.xlsx from www.dummies.com/go/financialmodelinginexcelfd. Open it and select the tab labeled 9-18 or open a new workbook and enter the data as shown in Figure 9-18.

To build a waterfall chart, follow these steps:

1. Highlight all the data, including the labels and the margin, and select the Waterfall option from the Charts section of the Insert tab on the Ribbon, as shown in Figure 9-18.
2. Edit the title and change the colors.

3. Change the labels on the x-axis so that they’re orientated horizontally.

   Showing labels at an angle makes the chart far more difficult to read. You may need to change the size of the chart to do this.

4. Remove the legend at the top.

   Everything you need to know from this chart is shown in the labels already.

5. Set the margin amount as the total so that it shows the remainder only, as it does in Figure 9-19, by clicking the bar showing the margin, right-clicking, and selecting the option Set as Total.

   This moves the margin column down so that it shows correctly against the x-axis, as shown in Figure 9-19.

This waterfall chart won’t appear if the file is opened in Excel 2013 or earlier. For instructions on how to build a waterfall chart using a “dummy stack” or up/down bars that can be opened and used in any version of Excel, go to www.plumsolutions.com.au/waterfalls.

Sunburst charts

The stacked doughnut chart shown earlier in this chapter doesn’t display gender and age data very well. If you have a hierarchical relationship within your data, you can use a hierarchical chart such as a Sunburst or Treemap.

To build a Sunburst chart, follow these steps:

1. If necessary, reorganize your data so that it sits within hierarchical categories, as shown in figure 9-20.
2. Highlight all the data, and select the Sunburst option from the Charts section of the Insert tab on the Ribbon.
3. Edit the title and change the colors.
4. Add the colors and edit the data labels, as shown in Figure 9-21.

Treemap charts

A Treemap chart works in exactly the same way as the Sunburst, except that the segments are shown as squares instead of circles. The easiest way to change to a Treemap without having to change your settings again is to right-click the Sunburst, select Change Chart Type, and select the Treemap option, as shown in Figure 9-22.

Although the Sunburst and Treemap show exactly the same data, be sure to try out both types to see which looks best with your data. In this example, the Sunburst looks better visually. If you had a larger number of series, however, the Treemap would probably be easier to understand.

Building the chart on formula-driven data

Take a look at the five-year strategic forecast model that you work on in the “Conveying Your Message by Charting Scenarios” section at the beginning of this chapter. Because the chart you built was based on formulas, the chart will automatically change when the drop-down box is changed. Download File 0901.xlsx from www.dummies.com/go/financialmodelinginexcelfd. Open it and select the tab labeled 9-23 to try it out for yourself, as shown in Figure 9-23.

If you hide data in your source sheet, it won’t show on the chart. Test this by hiding one of the columns on the Financials sheet and check that the month has disappeared on the chart. You can change the options under Select Data Source so that it displays hidden cells.

Linking the chart titles to formulas

Because all the data is linked to the drop-down box, you can easily create a dynamic title in the chart by creating a formula for the title and then linking that title to the chart. Follow these steps:

1. In cell A1 of this model, change the title to the following: =“Five Year Strategic Forecast Costs for Call Center - “&F1.

   The ampersand (&) serves as a connector that will string text and values from formulas together.

   Instead of the ampersand, you can also use the CONCATENATE function, which works very similarly by joining singular cells together, or the TEXTJOIN function is a new addition to Excel 2016, which will join together large quantities of data.

   When you have the formula in cell A1 working, you need to link the title in the chart to cell A1.

2. Click the title of the chart.

   This part can be tricky. Make sure you’ve only selected the chart title.

3. Click the formula bar.
4. Type = and then click cell A1, as shown in Figure 9-24.

5. Press Enter.

   The chart title changes to show what is in cell A1.

You can’t insert any formulas into a chart. You can only link a single cell to it. All calculations need to be done in one cell and then linked to the title as shown.

Creating dynamic text

Take a look at the monthly budget report shown in Figure 9-25. We’ve already built formulas in columns F and G, which will automatically update as the data changes, and display how we’re going compared to budget. (To see how to build this model, turn to Chapter 7.)

Now you’ll create a chart based on this data, and every time the numbers change, you’ll like to be able to see how many line items are over budget. Follow these steps:

1. Highlight the data showing the account, actual, and budget values in columns B, C, and D, respectively.
2. Select the first 2-D Column option from the Charts section of the Insert tab on the Ribbon to create a clustered column chart, as shown in Figure 9-25.
3. In cell A1, create a heading with a dynamic date (as described in the “Using dynamic dates” sidebar).
4. Link the title of the chart to the formula in cell A1, as shown in the preceding section.

5. Edit the chart so that the titles are horizontally aligned and change the colors.

   This chart will look much better if it’s sorted so that the larger bars are on the left side.

6. Highlight all the data including the headings, and click the Sort button (in the Sort & Filter section of the Data tab in the Ribbon).

   The Sort dialog box appears.

7. Sort by Actual from largest to smallest, as shown in Figure 9-26.

   It’s very easy to mess up formulas when sorting, so be sure that you highlight all the columns from columns A to G before applying the sort.

   Now, add some text commentary to the chart. You can do this by adding commentary in a single cell, which is dynamically linked to values in the model and link the cell to a text box to show the commentary on the chart.

8. In cell A15, create a formula that will automatically calculate how many line items are over budget.

   You can do this with the formula =COUNTA(G3:G12)-COUNT(G3:G12), which calculates how many non-blank cells are in column G. (For more information on how the COUNTA and COUNT functions work, see Chapter 7.)

9. You can see that two line items are over budget, so convert this to dynamic text with the formula =COUNTA(G3:G12)-COUNT(G3:G12)&” Items over Budget” (see Figure 9-27).
10. Insert a text box into the chart by pressing the Text Box button in the Text group on the Insert tab in the Ribbon.

11. Click the chart once.

    The text box appears.

12. Carefully select the outside of the text box with the mouse, just as you did in the last section when linking the chart titles.
13. Now go to the formula bar and type =.
14. Click cell A15 and click Enter.
15. Resize and reposition the text box as necessary.
16. Test the model by changing the numbers so that more items are over budget, and make sure that the commentary in the text box changes.

Charting is an important part of the final stage of the model-building process. Make sure that the key messages from the model’s output are accurately presented by using clear, attractive, and unambiguous charts and tables.

Data visualization is a discipline that is increasingly growing in popularity, and not one that comes naturally to many with an analytical background — like me! Although I readily admit that it doesn’t come naturally to me, I’ve spent a lot of time learning about these principles to improve the models and dashboards that I build for clients. If you can educate yourself on even the most basic principles of visual design and apply them to your work, you and your financial models will have greater credibility and recognition in your organization and amongst your peers.