16. Charts for Sensitivity Tables

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

  1. Creating an Initial Chart (00:04)

    Charts can be a good way of presenting the output from a sensitivity analysis table. To create such a chart, the legend entries should be to the left of the data table, and the axis labels should be above or below the data table.

    When we attempt to create a line chart using a sensitivity analysis data table, we find errors with the axis labels and legend entries. This is because these values are interpreted as numbers by Excel, and are included in the dataset. We want them to be interpreted as text instead. To do this, we need to create another sensitivity table.

  2. Creating the Chart Correctly (01:41)

    We don’t want to adjust the original sensitivity table, as this would prevent it from recalculating in the future. Instead, we create a copy of the table. The data is copied from the original table, using Copy, and then Paste Formulas. For the legend entries and axis labels, we use the TEXT function to convert them to the text format. In our case, we use the format “###” for the booking limit, which is a three digit integer, and “##.0%” for the conversion rate, which is a percentage with one decimal place.

    Using the copy of the sensitivity table, we attempt to create a line chart again. In this case, the chart is created correctly.

  3. Formatting the Chart (03:34)

    We use various formatting tools to improve the appearance of the chart. When adding axis titles, we choose not to simply type a title. Instead, we insert a cell reference in the formula bar. This means the axis title will always contain the contents of that cell.

    We then want to create a chart title that updates when a cell in the model is changed. To do this, we add a text string representing the desired title to a blank cell in the workbook. This string combines text, enclosed in double quotes, and cell references. Text and cell references are chained together with ampersands. We then set the chart title to be a reference to the cell where we placed the text string.

Transcript

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.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model