Football Field Graphs (American Football) to Illustrate Ranges in Value
On this page I walk through how to create a football field diagram. A football field graph is a pretty silly name used to present alternative valuation techniques with different ranges. For example, the range in valuation from a low and a high P/E ratio is compared with the range in value from a DCF method with different WACC assumptions. While the name is irritating, the idea is pretty good because a football field diagram shows that different valuation techniques can be used and that it is naive to believe a single valuation level is reasonable. Making a basic football field graph is can be accomplished with the bar chart selection in excel. This is pretty simple. But the problem comes when you put labels on bars in the chart. The step by step description and the video below demonstrate how you can construct such a flexible football field diagram that includes the valuation amounts as labels on the graphs. Putting labels on the football field involves adding a separate series to make spaces and it can be painful. The good news is that no macros are necessary.
A football field diagram and the link to see how to make a football field diagram is illustrated below:
The file that you can download below walks through the steps for creating a football field diagram from the assumed ranges in values shown in the screenshot below. The screenshot shows the beginning and the end of the process. Subsequent screenshots demonstrate how to get from the values in the table to the football field diagram shown on the right.
Once you have the data, the key step is define a table with the low range, the increment and the high range. The low range is for the data label that goes on the left and the high range is for the data label that goes on the right. The screen shot below demonstrates how to set up this key part of the process of making a little table.
Once you have the table, the next step is to make a graph that will not look very good but will be the outline for the final graph. In the example illustrated on the screenshot below, I have selected the second table from the above screenshot and pressed ALT, F1 to make the graph on the same page. Then I have changed the chart type and made it a bar chart. In the bar chart I select the second option.
The graph in the screenshot above does not look too good. But don’t worry. Now you can start cleaning up the graph and putting in the data labels. The first thing to do is just to make the bars at the left and the right into no fill bars by selecting the data series and then clicking on the no fill option. This is shown in the screenshot below.
Once you have removed the fill and created data labels, click on the data labels and then the labels will appear out of place. To put them in the correct place, format the data labels and then choose the Inside End option as illustrated on the screenshot below.
The screenshot below shows how to adjust the data label for the high end of the data range. Here you should use the inside base when you format the data label as shown on the screenshot below.
Once you have created the data labels and aligned them, you can just change the x-axis to make the range look reasonable. Of course delete the series stuff at the bottom as well.
You need to change some colours and add some labels. The trickiest part is to get the labels You have to do this one bar by one bar and click on each label.