Football Field Diagrams

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 M&A analysis. For example, the range in valuation from low and high comparable P/E ratio’s is compared with the range in value from a DCF method with different WACC assumptions. While the name is irritating, the idea of presenting different valuation approaches 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 amount is reasonable. Making a basic football field graph is can be accomplished with the bar chart selection in excel. Making the bar chart with the range of values is pretty simple.  But the problem comes when you put labels on the valuation bars in the chart from different methods. 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 from clicking on the button below walks through the steps for creating a football field diagram from the assumed ranges in values from different methods shown in the screenshot below.  The screenshot shows above illustrates the end of the process that includes the bars and the tricky to implement labels. Subsequent screenshots on this page demonstrate how to get from the values in a table of value ranges to the football field diagram with data labels.  As usual, I have also included a video describing the process.

 

Excel File with Step by Step Description of How to Create Football Field Diagram and Include Labels

 

Once you have the data with the different valuation techniques and the ranges for each technique, the key step is create 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 big deal is to create the increment that is calculated as the difference between the high case and the low case. 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 from the three columns that will not look very good but will be the outline and provide the basis 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 insert 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 that is a stacked bar.  With the stacked bar, that has the low range, the increment and the high range.

The graph in the screenshot above does not look too good.  But don’t worry.  Now you can start cleaning up the graph with the bar chart 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.  To make the right bars into no fill, you select the data series and then clicking on the no fill option.  The result is shown in the screenshot below where there is now the increment and the high range shown.  The “No Fill” option is illustrated on the right hand side of the screenshot below.

You should also click on the upper range and use the no-fill option. 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.   After you select the “Inside End” as shown on the right hand side of the screenshot below, the data labels will look pretty good as illustrated on the screenshot.

 

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.