16. Charts for Sensitivity Tables
Charts convey our information better than tables of data. In this lesson, we'll create a line chart for our sensitivity table, that updates automatically when the input data changes.
Charts for sensitivity tables
- Charts make insights much easier to grasp than tables of numbers
- However creating charts from sensitivity tables is tricky, primarily due to the axis labels
- To solve this problem, we create a new table and convert axis labels to text format
Process for creating charts from sensitivity tables
1 Create an identical table of data adjacent to sensitivity table
2 Convert axis labels and legend entries to text format using the TEXT function
3 Select new table of data and create line chart
4 Format the chart to suit your needs
5 Link chart title to a cell if you want it to automatically update
CTRL + →: Move to the last cell in the data region
ALT + N , N: Create Line chart
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
ALT + E , S , T: Paste formats
TEXT: Converts a number to the text format
&: Joins multiple text strings together within a cell
Once we're happy with both our sensitivity analysis and scenarios from our pivot table, we can begin to show our results on some charts.
When presenting to your managers or to clients, charts can make your insights and conclusions much easier to grasp than tables and numbers.
In this lesson, we'll create a chart for our first risk analysis table.
When creating charts and models such as this where inputs and assumptions can be changed very quickly, it's critical that your model and charts update automatically when a change is made.
For example, if I change my voucher costs from 210 down to 170, the values in our table change and the chart should change also.
Always test this once you've built your chart.
When creating charts in Excel, we have the legend entries typically to the left of the data and the axis labels on top or below. And our current data table holds this format.
We then normally select both the axis labels and the data and then insert our chart.
And in this case I'll insert a line chart.
But as you can see the legend entries aren't appearing correctly, the axis labels aren't appearing correctly and what's more, we have a blue line that doesn't look like it belongs to this chart at all.
And the problem is our access label and legend entries.
Excel is currently interpreting these as numbers, and therefore part of the dataset.
To fix this problem we need to convert our axis labels and row entries to the text format and then replot our chart. And this will solve our problems.
The easiest way to do this is to delete the existing chart and create a new table of data that's linked to our table.
The only difference being that the axis labels and legend entries will be in the text format and not in the number format.
So I'll start by linking to the cell on the top left with equals and selecting that cell, which is E28.
I'll then copy and paste for the remaining cells with Alt + E, S, F.
Now I'll add my legend entries and my access labels but I'll use the text function to convert these values into text.
So I'll start with 185 and the format will be three hashes. Close the brackets and press Enter.
And our 185 has now been converted to the text format and it's aligned on the left of the cell.
I'll then copy for the remaining booking limits.
Next we'll change the legend entries. And here I'll use the text function again. I'll take in the first value of 18% percent and the format will be hash, hash, dot zero percent, because I'd like one digit after the decimal point.
I'll then close the inverted commas and close the bracket. And again I'll copy for the remaining cells.
Now we can create our chart successfully of this particular data table.
So I'll use the shortcut Alt + N, N, to bring up a Line Chart.
And this time around we have the correct legend entries and we have the correct axis labels. All that's left to do now is some formatting of our chart.
I'll start by making it bigger. I'll remove the horizontal lines and I'll add a title axis. To do this I go to Axis Titles, and I'll select Primary Horizontal Axis Title.
Instead of typing in this title, I'll simply link it to a cell.
So I'll write equals and link it to the cell that says Booking Limit.
I'll also create a chart title and I'll put this above the chart and align it on the left.
For the chart title, I'd like it to say total additional profit for a given voucher cost because the voucher cost determines these particular values.
If I decide to change the voucher cost I'd like the chart title to also update accordingly.
And to do this, I'll simply link it to a cell that has a string of text and the value of the voucher cost.
When we want to link a string of text and a cell reference in one line, we need to write a formula.
We'll include the text in inverted commas.
We'll use an & symbol to link it to the cell and then we'll simply select the cell reference.
That looks pretty good. Now I'll select the chart title and link it to that particular cell.
I'll reduce the font slightly. And now when we change our voucher costs, let's say to 220.
So we'll see a big difference, our chart updates, our table updates and the title updates.
You might be asking yourself at this point, why we needed to create a new table of data at all.
However, if I had changed these values to a text format, the table would have been unable to recalculate our output values if I had changed my assumptions or my inputs in the future.
As a result creating a new table of data with text labels was the next best option.