Once a model has been built, you then need to display its results clearly and concisely to get your message across, via a written report or oral presentation. The output and presentation of the results are just as important as the rest of the model-building process, because there is no point in having a fantastically built model that none of the decision-makers knows about or is able to interpret. This final stage of the model-building process is sometimes not given much focus by modellers, particularly those whose skills are often more analytical than visual.
Many analysts and modellers would agree that public speaking is not their favourite part of the job and whilst it might be fun to imagine your senior management in their underwear, it might be more helpful to be prepared and follow these basic tips.
As the person who knows the model best, financial modellers are often requested to communicate the results of the financial model as a formal presentation to the board or senior management. Understandably, many detail-oriented analysts often find that it is quite a challenge to distil their 20MB financial model that has taken weeks to build into a 10-minute presentation.
Although most senior management may not be interested in seeing the model itself, they might like to see live and changing scenarios. If the model is built correctly, you'll be able to make a single change to the input assumptions and the audience will be able to see the effects of these changing scenarios in real time. Make sure that you test all possible inputs in advance—nothing looks less professional than a #REF! error during a live sensitivity analysis.
Not all presentations require you to use PowerPoint, and it's not always appropriate, but in this kind of environment, where it is necessary to convey lots of information, having the summary tables or charts on a slide behind you while you are speaking will be helpful. It can also help to take the focus off you if you are a little nervous.
Follow these basic rules of presentations:
Most importantly, be prepared for questions regarding the output, inputs, assumptions, or workings of the model. Make sure that you can defend the assumptions that have been made or the way something has been calculated. If you are not comfortable with some of the assumptions, say so up front. The model output is only as good as the assumptions that have gone into it, so you need to make sure that the audience accepts the key assumptions before they will accept the model results.
If you have the opportunity, spend some time with the key audience members in advance and walk them through the assumptions and methodology of the model. They might have some questions or comments that you have not anticipated, and it will give you a chance to make changes or prepare your answers to likely questions in advance.
If you have a 10-minute slot during which you need to convey the high-level output of the model, you probably need about four slides. If we have put together a business case for a new product, for example, outputs for a typical financial model may include:
1. A summary of projected cash flows or profitability. You won't be able to fit more than about 5 years onto a PowerPoint slide, so if you need to show 20 years, just show Years 1, 5, 10, 15, and 20, for example.
2. Key assumptions. Use your judgement here to choose which assumptions are important. Select the ones that either impact the model the most, or that you are most certain of.
3. Scenarios analysis. You'll probably be able to fit only four to five scenarios on a slide comfortably, so choose wisely. At a minimum, display best-case, base-case, and worst-case scenarios. See “Scenarios and Sensitivity Analysis in a Business Case” in Chapter 11 for greater detail on how to create these.
4. Results of sensitivity testing. Show the results of tweaking a single assumption. For example, if we were to get our customer take-up amount wrong by 1 percent, this will cause a $500,000 change to the NPV—and this is something the audience most certainly needs to be aware of.
If you have longer than 10 minutes, you might be able to show the model itself, if the presentation is more of a workshop than a formal presentation. In either case, you should be able to give each person a hardcopy summary report of the first few pages of the model with more detailed numbers than you have been able to include in the PowerPoint presentation. If the model has been well written, each page will form the backup data and source information for the model and can be printed or distributed if the recipient of the summary results requests further information.
Charts are a way to present a series of data in a visual presentation so that patterns of information can be identified. Charts can be a powerful tool for the end user if they are built correctly and help simplify and summarise data.
A common mistake many analysts make is to try to put as much information as possible into one chart in an attempt to make it look impressive. In reality, the chart just looks cluttered and fails to get the message across. Remember that charts are for the purpose of visually presenting information that is easier to digest than the raw data. When creating a chart, balance the amount of information presented along with the formatting. It may be more useful to create two charts, dividing up the information, rather than putting it all on one chart. Consider Figure 12.1.
Is this chart useful? There is so much data presented that the chart has no real value. Also, the type of chart isn't necessarily the best choice. Consider instead Figure 12.2.
With the same set of data, we have created a different chart that gives us much more intelligence, using less of the data. In Figure 12.2, a viewer can see at a glance the trend in revenues and profitability. It also shows the composition of marketing and G&A (general and administrative) expenses month to month. Note that using a stacked bar chart allows the user to see the total expenses, including Cost of Goods Sold (COGS). Compare this to the original line chart, and you can see it is almost impossible to compare the expenses in the line chart, as there is so much interference.
By breaking up the data into two chart types on two separate axes, and limiting the information presented, we have gone from useless and confusing to informative and interesting.
Use the following tips to really make your charts have an impact:
Before looking at how to build charts from a technical perspective, let's first look at which chart to use. The most common types of charts are line and bar charts, but there are many other charting options Excel has to offer, and which chart types will display the data in the best, most visual way really depends on the available data and the message you are trying to convey.
The first step in creating a useful chart is to choose the correct type of chart. Bar charts, pie charts, line charts, areas, scatter plots, donuts, radars, and bubbles are all options. Which one makes the most sense? Note that when displaying the information outputs of a typical financial model, line and bar charts are the predominant types of charts used, but this is not always the case. It's quite a skill to choose the most appropriate chart to display the output information in the best possible way.
Sometimes it's a matter of trial and error. The best way to choose a chart is to try several different charts to get a sense of which one is the best. Fortunately, it's very easy to flick between different chart types in Excel.
Table 12.1 is a brief summary of different chart types and their uses for displaying data graphically. The next section describes in detail what each of these charts does and what sort of data it best displays.
TABLE 12.1 What Type of Chart Best Fits Your Data?
Chart Type | Suitable for … | Application in Financial Models |
Line | Trend and functional relations; useful for spotting trends | Very useful for time series |
Pie | Single series of positive percentages or ratios | Useful, but often overused |
Column | Data point (Y) variation over a short range of values (X). Suitable for comparison of data points | Very useful for comparison |
Bar | Same as column charts | Very useful |
Radar |
Displaying multivariate information for statistical analysis |
Rarely used |
3D | Showing data three-dimensionally instead of two-dimensionally (e.g., cone chart) | Not recommended |
Area | Similar to the line chart; represents variation of data points relative to each other | Sometimes used |
Donut | Stacking multiple pie charts | Rarely used |
Bubble | Enhancing scatter plots with additional variable | Sometimes used |
Take a look at the sales data and the chart options in Figure 12.3. Which chart type would best represent the sales data to show a regional comparison? We need to create a chart to visually display to our readers the output of our analysis, which is to compare the departments to each other. Which would be the best fit to get our message across?
Line charts and area charts are best for showing the connection between the data points, usually in time series, which is not the case, so they are not particularly useful in this situation.
The column or bar chart is best for comparing this kind of information, as we can easily see which area is the highest and which is the lowest. The pie chart shows the proportion and looks visually pleasing, but it is not as good for showing comparisons between the data points.
Figure 12.3 shows six different chart types that are readily available in Excel. Some of these charting options are better choices than others, so let's go into each of these chart types in a little more detail.
We can see in Figure 12.3 that a line chart is not the most appropriate method of displaying these different costs, as it insinuates that there is some connection between the data points.
There are also stacked line and 100 percent stacked line chart options in Excel, as there are with column charts. Sometimes line charts convey information in a more meaningful manner when the data points are marked. Excel offers the line chart with marker options for this purpose.
For detailed instructions on how to build a basic line chart, see the supplementary materials available at www.plumsolutions.com.au/book.
Figure 12.3 showed a one-series situation where we had only four numbers to plot. What if we need to show more data on our chart with multiple series? Figure 12.4 shows expenses that have been split into three types: Staff Costs, Admin, and IT. Let's look at a few ways we can display this data in a chart.
Excel offers multiple variations in column, bar, and line charts, such as the seven listed below. Some of them are very effective in conveying the message, while others may look good but distract from your main point.
Note that including a data table will add more detail to the stacked column chart, and help the reader to compare the values in the upper stacks. See “Handy Charting Hints” later in this chapter for instructions on how to add a data table.
However, interpreting donut charts can become cumbersome if there's too much data. If you want to use this chart, you need to plan it properly and make it as intuitive as possible. Consider Figure 12.5. We want to show the Australian population and see if there is any correlation to land area. This donut chart is interesting but quite difficult to interpret.
Combination Charts Sometimes using the same chart type to represent different data metrics is not very effective. In such cases, using a combination of chart types can be a very powerful solution. One of the most popular representations is the combination of column and line charts. Such combinations can convey a lot of information without cluttering the chart. These are particularly useful in dashboard reporting and other instances where you want to display as much information as possible in a small amount of space.
Quite often we might want to show two different measures, such as number of units and profit, on the same chart. For instructions on how to do this, see “Charting with Two Different Axes and Chart Types” later in this chapter.
Consider Figure 12.6, which also displays the population versus the land area. Whilst not as visually appealing as the donut chart, it is somewhat easier to interpret, and we can see that there is very little correlation between population and land area.
Map Charts An even better way of displaying this data would be to show it on a filled map. Using exactly the same data, we can show it visually on a map as shown in Figure 12.7. It's not necessary to add the land area to this map to show our message, as the area is shown in the chart. Population density is shown by the darker colours, which works in a similar way to the colour scales in conditional formatting.
Bubble Chart Bubble charts are another type of chart to consider if you need to show multiple dimensions, and the data is not location-specific. Bubble charts are an enhancement to scatter charts and can compare three characteristics: not only plotting the x- and y-axis, but also reflecting the bubble size for each category. Detailed instructions can be found later in this chapter for how to build a bubble chart. See Figure 12.8.
The following outlines some of the most commonly used features of charting. Note that in later versions of Excel, a lot of the options that used to appear in the ribbon in earlier versions have now been moved to the chart itself. The Layout tab is no longer in the ribbon, and most of these options have been moved to the chart. You'll notice that when you click on the chart, several new icons appear on the right, as shown in Figure 12.9. These three options are:
All the features in these options can also be accessed by right-clicking the chart or via the Chart Design tab, which will appear in the ribbon once the chart has been clicked upon. This is the same in Excel for Mac, however, this is the only way to make these changes on a Mac because, in the current version, the three options do not appear to the right of the chart when you click on it. Chart changes on a Mac can be made through the Adding Chart Elements on the Chart Design tab in the ribbon, as shown in Figure 12.10.
Displaying data graphically is usually an iterative process, and if you create a chart based on your model data and it doesn't look quite right, it is relatively quick and easy to change the chart type displayed.
To change the chart type, display the Change Chart Type dialog box by right-clicking on the chart area and selecting Change Chart Type from the drop-down menu that appears.
Choose the chart you'd like to display and click OK. Note that you can also change the chart type through the Design tab on the ribbon.
You can also quite easily change the source data for your chart while retaining the original chart type.
In Figure 12.11, the pie chart is based on sales data per region (B2 through to B5). To depict expenses per region (E2 through to E5), you would need to change the source data.
The easiest way to change the source data is to simply click on the chart until the source data is highlighted. We can see in Figure 12.11 that the source data is in columns A and B. Now, just hover the mouse on the blue line to the right of column B, and drag it across to column E as shown in Figure 12.12. This works in any kind of chart where you can see the source data on the same page.
If the data is not on the same page, or if the change is more complex, then this quick tip may not work and so you'll need to do it the long way, with the seven steps that follow.
If you want to change the title of the chart to something more interesting than Expenses, simply click on the title, and type over it.
Remember that if you change the underlying data in the model, the chart will change as well. You'd hope so, as that's really the point of financial modelling.
If you've spent a lot of time getting the axes, colours, and formats the way you want them, you can save this chart as a template to use again and again.
To save the chart template:
To use the chart template on a new or existing chart:
Here are some handy charting hints:
To add the data table, click on the chart to make the Design tab appear and on the Design tab click on the drop-down under Add Chart Element on the far left-hand side. Select Data Table as shown on the left in Figure 12.18, and choose from with or without Legend Keys. Unless you are using Excel for Mac, you can also click on the chart, select the Chart Elements icon (the + symbol), and select the Data Table option, also shown on the right of Figure 12.18.
A common problem in creating charts for modelling is that the size of the source data can change, and cause errors. The problem with specifying a range is that if someone adds data to the right or below the table, it will not be included in the range, and therefore will not show in the chart. Look at the problem shown in Figure 12.19. We have some data used to create a chart. The user has now entered some additional data in row 8, but this has not been included in the chart, because it was created using the static range A1:B7. You can find these templates, along with the accompanying models to the rest of the screenshots in the book, at www.plumsolutions.com.au/book.
There are two ways of making your chart more dynamic:
Let's explore how to create a dynamic named range and include it in a formula or chart. Be warned, it's not for the fainthearted! Before we start, you should be familiar with the concept of named ranges from Chapter 5, and the OFFSET function from Chapter 6.
Let's start by defining an ordinary named range called Exp_data by highlighting the data in the range and typing the name in the Name box in the top left-hand corner. Now, if a user wants to add data to this table (e.g., another expense item in row 8, or another city in column J), this new data will not be included in the range.
To make this named range dynamic, first we need to work out how big the range is. We'll do this using a COUNTA formula in row 1 and column A. This counts the number of cells that are not blank. We can include this in the OFFSET, but use the result of the COUNTA function to specify the number of rows and columns in the range.
Note that the data needs to be a block, with no blank rows or columns, as this could throw the formula off by a row or column, and there needs to be a value in cell A1.
Go to the Name Manager, and edit the named range you have just created. However, instead of putting the absolute range of A1:I8 in the Refers To box, enter the formula
Note that it's important to include the absolute references in the range. This is telling the named range to start at A1 and go as far up and down as there is data (within the range A1:Z5000). We are using the height and width sections of the OFFSET function in this case.
See Figure 12.20. You can now refer to this named range in formulas and charts, knowing that additional data will always be captured within the range.
Dynamic named ranges are helpful when you are creating a chart with a variable number of values. For example, let's say you are creating a chart based on a financial model that contains a variable number of tenants. We know that users are unlikely to need to model more than 5 or 10 tenants in the model, but we've made the model flexible so that users can enter up to 25 tenants, just in case they need that many. If we set up the chart allowing for 25 tenants, it would look perfectly ridiculous. See Figure 12.22.
The chart looks much better if we include only the tenants that actually have data in the chart. See Figure 12.23.
However, using an ordinary chart, we'd need users to go into the source data and change the chart range manually, which would be time-consuming and error-prone. The alternative is to create dynamic named ranges in the source data, and then refer to the named ranges when creating the chart. This way the range (and therefore the labels picked up in the chart) will automatically expand and contract, depending on whether the cells contain data or not. Unused tenancy cells will remain blank and will not be included in the chart. See the following four steps.
Referring to a multi-dimensional range like the one shown in the first example in Figure 12.20 in a chart will not work. To create a chart using this data, you would need to create a separate named range for each series, for example, one for Superannuation, one for Workers Comp, and so on, which somewhat defeats the purpose of having a dynamic named range.
Test the data by adding or deleting tenants, and make sure the chart changes accordingly.
One of the best ways of analysing the output of your model is to plot two different variables on the same chart so that we can understand trends and look for correlations in the data. Sometimes the only way to spot a trend or an anomaly in the model is to look at it graphically. For example, how does our overall revenue compare to headcount? Surely as revenue increases so should headcount, and if it doesn't we need to understand why. What if we compare regions and their number of units sold and profit? Surely the regions with the highest number of units sold should be the most profitable. This may not necessarily be the case, and there's no better way to analyse this than with a chart plotting both pieces of information on two separate axes. Let's take a look at some data, and plot the number of units sold and the profits using two different chart types on two axes.
If we have the data shown in Figure 12.26, we can create a chart to compare units sold and profits. The easiest way to select the ranges is to highlight the data in columns A, B, and D by holding down the Control key, as shown in Figure 12.26. Hold down the Command key instead of Control if you are using Excel for Mac.
Alternatively, you can select the Combo icon directly from the ribbon in the Charts section of the Insert tab.
Bubble charts are a variation of other types of graphs, combining elements from column and scatter charts. They give the user a way to compare values based on the relative size of the bubble, as well as the location within the chart. They are most useful when there are three data points to display.
Let's say you have collected the historical information about your company's profits and number of staff by year as shown in Figure 12.29.
Since you have three data points, it is difficult to represent this as a 2D graph. Consider the following example of how the data could be charted as shown in Figure 12.29. It can be difficult to compare the relativity of the profits and number of staff. Essentially, having all of the information in this format loses the intended purpose.
In this situation, we can consider using a bubble chart. The same data presented in a bubble chart tells a different story. You are able to compare the profits and staff by using the vertical rise of each bubble (profit) and its corresponding y-axis value, along with comparing the staff numbers by looking at the size of the bubbles.
Creating a bubble chart is a bit more involved than a standard bar chart, but the few extra steps are worthwhile. See the following 12 steps.
Your bubble chart should look something like Figure 12.32.
When creating charts in financial models or reports, we should still follow best practice and try to make our models as flexible and dynamic as we can. As discussed in Chapter 3, “Best-Practice Principles of Modelling”, we should always link as much as possible in our models, and this goes for charts as well. It therefore makes sense that when we change one of the inputs in our model, this should be reflected in the chart data, as well as the titles and labels.
Let's do a simple example with 13 steps to see how we can make our charts more dynamic. If you had the temperature data as shown in Figure 12.33, we could create a comparison chart whereby the user could compare the temperature between months in different cities. You can find this template, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.
Try creating some additional commentary in a cell in your sheet, such as the maximum temperature for the selected period. Your formula would be
Add a text box into the chart, by clicking on the Text Box icon in the Text group on the Insert tab in the ribbon, and then link that text box to the single cell in the same way as you linked the chart title above. Your chart should look something like Figure 12.36.
Waterfall charts, also called bridge or stepped charts, have become popular in recent years, particularly when displaying the output of a financial model. A waterfall chart displays very effectively the incremental impact of each period of time or unit. It is a type of bar chart where the value of the second bar generally begins where the first one finished. To illustrate, a waterfall chart might look something like Figure 12.37.
Despite their popularity, waterfall charts were only relatively recently added as a standard chart type in Excel, and to create one in a version prior to Excel 2016 can get very complicated. Please refer to the supplementary content for this chapter, which can be found at www.plumsolutions.com.au/book, for detailed instructions on how to create a waterfall chart in previous versions of Excel using the “dummy stack” and the up/down bars method.
Let's say you have a forecast for cash inflows and outflows for the next six months (see the data list that follows).
Month | Cash |
Jan | $250 |
Feb | $200 |
Mar | $1,000 |
Apr | −$500 |
May | $650 |
Jun | $800 |
You could forecast it as a cumulative bar chart. See Figure 12.38. But we'd like to see the incremental contribution of each month individually, which will add a new dimension to the analysis.
Contrary to popular belief, you don't need fancy software to build a basic waterfall chart. They are really not that difficult to build in Excel and are achieved with a few little tricks, as shown in the following list of four steps.
If you'd like to show the total amount on the waterfall chart, add it by following these further four steps:
There are then a few optional changes you can make:
Presenting the completed model is a part of the model-building process that is typically not done well by most financial modellers. The majority of the focus has been on the building, structure, design, and layout, and on getting accurate calculations and results.
When senior managers or clients commission a financial model to be built, they are less interested in the structure or calculations of the model than they are in the output and the results of scenario analysis and sensitivity testing. Quite often the modeller does not consider how to best present and communicate these results to senior managers or clients.
It's important that at the end of the model-building process, the modeller spends some time thinking about what the viewer of the model needs to see, whether it is a report, a chart, or a table, and how best to communicate both verbally and visually the methodology, assumptions, and results of the model in the clearest and most informative way.
18.226.4.191