Using F11 quick charts

Most financial modellers create graphs to visualise data or to check trends. While building finance models, a good modeller will keep asking themselves whether their results are looking sensible. A good financial modeller will regularly chart line items and analyse the resulting trends – even while they are in the middle of building a model.

The important thing is to delete the chart immediately afterwards. Once it has served its purpose – which is for you to confirm that you are generating sensible results – there is little point in keeping it.

Throwing up a quick / temporary chart of line items as you build them is something we recommend you do regularly – it will help you spot trends and pick up on any discontinuities. Don’t convince yourself that you’ll chart all your detailed calculations at the very end of the build process. No one is ever going to do that. Instead, include charting each time you complete a detailed calculation. Make it something that you do before moving on to the next calculation. That’s going to give you a much stronger visual sense of what’s going on in your financial model.

And because you are building your visual knowledge in layers, you are much more likely to be in a good position to explain more complex charts. For example:

What’s causing the dividends (in the blue) to behave differently from its source revenue (in the red)?

What’s causing this project’s retained cash balance to resemble the Loch Ness monster? Post your answers in the comments section below.

So once you’ve bought into the idea of quick / temporary charts, you then can play around with Excel’s chart features. But would you really want to invest a good amount of time in getting the chart just right if you are almost immediately going to delete the results? I will want to spend time on this when I am designing a dashboard, but while I’m building a model I simply want to decide on something quick: columns or lines? The best tool for the job in Excel is Quick Charts – charts that appear in their own worksheet.

Here’s the process:

• select the calculation line item that you wish to chart
• highlight the cells from start to end;
• press F11

This will generate a chart in a worksheet. And if you have yet to play around with your chart settings then you will see a column chart.

Changing the default chart type

To change the default chart type:

1. Select an existing chart and then look for the Design tab, in the Type group and click the Change Chart Type button:

2. Go to the ‘All charts’ tab on the Change Chart Type window that pops up from the previous step:

3. Select the chart sub-type that you want to set as default;

4. Right-click on this chart to open the pop-up menu and select ‘Set as Default Chart’. Make sure you are clicking on the chart image at the top of the tab and not the preview:

5. Click on OK

After following these steps, you can press F11 to create the newly-selected default chart type instead of the column chart.

Deleting the chart

When you have finished analysing your data, you can delete your temporary / quick chart. The keyboard shortcut for doing that is Alt, H, D, S (the standard Excel shortcut for deleting a worksheet). Before you confirm its deletion, make sure that you are standing on the chart sheet. This is not something that you can undo.

Saving your own template

Right-click on an existing F11 quick chart to save it as a template.

Once that template exists you will find it as you go through the “Change Chart Type” procedure described above. To set a template as a default chart, select “Templates” from the left pane showing all the different chart type (e.g. line, bar etc.) options.

Find out more

Would you like to learn more about how to review and improve financial models? You may be interested in our Working with Financial Models Course. Click below for the course agenda, brochure, and more.