Dynamic Charting in Financial Modeling

When you’re creating charts in financial models or reports, you should still follow best practice and try to make your models as flexible and dynamic as you can. You should always link as much as possible in your models, and this goes for charts as well. It makes sense that when you change one of the inputs to your model, this should be reflected in the chart data, as well as the titles and labels.

Building the chart on formula-driven data

Download File 0901.xlsx. Open it and select the tab labeled 9-23 to try it out for yourself.

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.
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. 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.

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.
3. In cell A1, create a heading with a dynamic date.
4. Link the title of the chart to the formula in cell A1.
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.
   Sorting the chart data.

   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.

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.”
   Completed chart with dynamic text box.
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.